The Pandas Question That Ninety Percent of Candidates Fumble

import pandas as pd

df = pd.DataFrame({
    'order_id':    [101, 102, 103, 104, 105],
    'customer_id': ['C1', 'C1', 'C2', 'C2', 'C2'],
    'amount':      [200, 800, 100, 100, 300]
})

totals = df.groupby('customer_id')['amount'].sum()
print(totals)
# customer_id
# C1    1000
# C2     500

df['total'] = totals
print(df['total'])
# 0   NaN
# 1   NaN  ... every row is NaN
# Index of df is 0..4, index of totals is C1, C2.
# No labels match, so nothing is assigned.

import pandas as pd

totals = (df.groupby('customer_id', as_index=False)['amount']
            .sum()
            .rename(columns={'amount': 'customer_total'}))

out = df.merge(totals, on='customer_id', how='left')
out['share'] = out['amount'] / out['customer_total']
print(out)

#    order_id customer_id  amount  customer_total  share
# 0       101          C1     200            1000   0.2
# 1       102          C1     800            1000   0.8
# 2       103          C2     100             500   0.2
# 3       104          C2     100             500   0.2
# 4       105          C2     300             500   0.6

# Correct. But four steps for a one-line idea.

import pandas as pd

df['share'] = df['amount'] / df.groupby('customer_id')['amount'].transform('sum')
print(df)

#    order_id customer_id  amount  share
# 0       101          C1     200    0.2
# 1       102          C1     800    0.8
# 2       103          C2     100    0.2
# 3       104          C2     100    0.2
# 4       105          C2     300    0.6

# Sanity check: shares per customer add to 1
print(df.groupby('customer_id')['share'].sum())
# C1    1.0
# C2    1.0

import pandas as pd

g = df.groupby('customer_id')['amount']

# Difference from the customer's average order
df['vs_avg'] = df['amount'] - g.transform('mean')

# Number of orders the customer has, on every row
df['n_orders'] = g.transform('size')

# Flag the customer's largest order
df['is_max'] = df['amount'] == g.transform('max')

# Keep only customers with 3+ orders (filter)
loyal = df.groupby('customer_id').filter(lambda x: len(x) >= 3)
print(loyal['customer_id'].unique())   # ['C2']

import pandas as pd

df = pd.DataFrame({
    'customer_id': ['C1', 'C1', 'C2', 'C2', 'C2'],
    'order_date':  pd.to_datetime(['2025-01-05', '2025-02-10',
                                   '2025-01-02', '2025-01-20', '2025-03-01']),
    'amount':      [200, 800, 100, 100, 300]
})

# Rank within customer, biggest order = 1
df['rank_in_cust'] = (df.groupby('customer_id')['amount']
                        .rank(method='dense', ascending=False))

# Running total by date within customer
df = df.sort_values(['customer_id', 'order_date'])
df['running_spend'] = df.groupby('customer_id')['amount'].cumsum()
print(df)

import pandas as pd

# Days since previous order (NaN for the first order)
df = df.sort_values(['customer_id', 'order_date'])
df['days_since_prev'] = (df.groupby('customer_id')['order_date']
                           .diff()
                           .dt.days)

# Average gap per customer
print(df.groupby('customer_id')['days_since_prev'].mean())

# Top 2 orders per customer
top2 = (df.sort_values('amount', ascending=False)
          .groupby('customer_id')
          .head(2)
          .sort_values(['customer_id', 'amount'], ascending=[True, False]))
print(top2)

GROUP-TO-ROW PRACTICE (one value per original row)

1. Share of each order in its customer's total
2. Share of each product in its category's sales
3. Difference of each salary from the dept average
4. Flag each student's best subject score
5. Count of orders per customer, on every row
6. Rank products by sales within each region
7. Running total of sales per region by month
8. Days since previous visit per patient
9. Top 3 products per category
10. Keep only cities with total sales above 10000

For each: agg, transform, rank, cumsum, diff,
head or filter?

import pandas as pd

sales = pd.DataFrame({
    'region':   ['North', 'North', 'North', 'South', 'South'],
    'category': ['Tech', 'Tech', 'Home', 'Tech', 'Home'],
    'product':  ['Laptop', 'Mouse', 'Lamp', 'Phone', 'Chair'],
    'month':    pd.to_datetime(['2025-01-01', '2025-02-01',
                                '2025-01-01', '2025-01-01', '2025-02-01']),
    'sales':    [50000, 2000, 3000, 30000, 7000]
})

# Q2 answer to check against:
sales['cat_share'] = (sales['sales'] /
    sales.groupby('category')['sales'].transform('sum'))
print(sales)

SAY THIS SHAPE SENTENCE FOR EACH ANSWER

'The input has one row per ______.
 A plain aggregate would give one row per ______.
 I need one value per ______, so I use ______.'

Then one check:
'To verify, I will ______
 (e.g. confirm shares sum to 1 per group,
  or that rank 1 is the largest amount).'

Record yourself. If the sentence is unclear,
the interviewer will find the code unclear too.