In this post we are going to cover joins, groupby and reshaping in pandas: merges that multiply rows, anti-joins, named aggregation, agg vs transform, pivot and melt, and window-style work like running totals. These are 7 real Python interview questions for data engineers, asked the way they come up on a real project, and the last one is a scenario where two systems disagree on the monthly sales total and you reconcile them in Python.
Joins, groupby and reshaping are the pandas version of the SQL you already write every day. Interviewers ask about them because the mistakes are quiet: a merge that doubles the rows, an inner join that drops orphans, an average computed at the wrong grain. They want to hear how you catch those before the numbers reach a report.
Every Python step below runs on the sample data, and the output under each step is the real result.
Part 4 of the Real Time Python for Data Engineers Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: After a merge, the row count went from one thousand to five thousand. Why, and how do you catch it?
- Question 2: You need to find orders that have no matching customer. How do you do an anti-join in pandas?
- Question 3: The business wants total, count and average per region in one table. How do you use groupby with agg?
- Question 4: Each order needs its share of the customer's total. What is the difference between agg and transform?
- Question 5: The report needs months as columns. How do you pivot and unpivot in pandas?
- Question 6: Your manager asks for each customer's latest order and a running total. How do you do window-style work in pandas?
- Scenario 7: Two systems disagree on the monthly sales total. How do you reconcile them in Python?
- The full script
The short answer
Put validate= on every merge so a non-unique key raises instead of multiplying rows, use how='left_anti' (pandas 3) for anti-joins and indicator=True for reconciliation. agg reduces to one row per group, transform keeps the original shape, pivot_table and melt reshape, and window-style work is sort, then groupby with cumsum, cumcount or tail.
Keep that in mind. Every question below is one piece of it.
Let's create some sample data
The script builds small DataFrames: six orders, a customer master with three customers, a CRM extract that keeps address history, eight sales lines with a region and an order date from August to October 2026, and two versions of the same orders for the scenario, one from the order app and one from the finance ledger.
import pandas as pd
pd.set_option('display.width', 60)
# Q1 and Q2: orders, the customer master, and a CRM extract that keeps address history
orders = pd.DataFrame({'order_id': [101, 102, 103, 104, 105, 106],
'cust_id': [1, 2, 1, 3, 4, 2],
'amount': [120, 80, 200, 50, 75, 40]})
customers = pd.DataFrame({'cust_id': [1, 2, 3],
'name': ['Ali', 'Sara', 'John']})
crm = pd.DataFrame({'cust_id': [1, 1, 1, 2, 2, 3],
'city': ['Dallas', 'Austin', 'Houston',
'Denver', 'Boulder', 'Miami'],
'is_current': [False, False, True, False, True, True]})
# Q3 to Q6: sales lines with region and order date
sales = pd.DataFrame({
'order_id': [201, 202, 203, 204, 205, 206, 207, 208],
'cust_id': [1, 2, 1, 3, 2, 1, 3, 2],
'region': ['East', 'West', 'East', 'East',
'West', 'East', 'East', 'West'],
'order_date': pd.to_datetime(
['2026-08-05', '2026-08-12', '2026-09-03', '2026-09-15',
'2026-10-01', '2026-10-02', '2026-10-08', '2026-10-11'],
format='%Y-%m-%d'),
'amount': [100, 200, 100, 150, 100, 200, 50, 100],
})
sales['month'] = sales['order_date'].dt.strftime('%Y-%m')
# Q7: the order app and the finance ledger, for the same two months
app = pd.DataFrame({'order_id': [301, 302, 303, 304, 305, 306],
'month': ['2026-09'] * 3 + ['2026-10'] * 3,
'amount': [120.00, 80.50, 45.25, 300.00, 99.75, 60.00]})
ledger = pd.DataFrame({'order_id': [301, 302, 303, 304, 306],
'month': ['2026-09'] * 3 + ['2026-10'] * 2,
'amount': [120.00, 80.50, 45.25, 300.00, 65.00]})Question 1: After a merge, the row count went from one thousand to five thousand. Why, and how do you catch it?
A merge returns one row for every matching pair of keys. If the key isn't unique on the right side, each left row repeats once per match. With repeats on both sides, that is a many-to-many join, and the rows multiply.
Let's see the same thing on a small sample: the orders joined to the CRM extract.
joined = orders.merge(crm, on='cust_id', how='left')
print(len(orders), 'orders ->', len(joined), 'rows')
print(crm['cust_id'].value_counts())6 orders -> 12 rows
cust_id
1 3
2 2
3 1
Name: count, dtype: int64Six orders become 12 rows. value_counts shows why: customer 1 is in the extract 3 times and customer 2 is there 2 times, because the extract keeps address history.
Let's catch it with the validate argument. many_to_one says the right side must be unique on the key.
orders.merge(crm, on='cust_id', how='left',
validate='many_to_one')pandas.errors.MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
Duplicates in right:
cust_id
1
1
2 ...pandas checks it and raises a MergeError instead of quietly multiplying the rows: Merge keys are not unique in right dataset; not a many-to-one merge. The message even lists the first duplicate keys.
Then fix the cause by joining at the right grain. Let's keep only the current address, one row per customer.
current = crm[crm['is_current']]
joined = orders.merge(current, on='cust_id', how='left',
validate='many_to_one')
print(len(orders), 'orders ->', len(joined), 'rows')6 orders -> 6 rowsvalidate passes: 6 orders -> 6 rows.
I put validate= on every merge, and check that a left join returns as many rows as the left side. If not, stop the load.
Question 2: You need to find orders that have no matching customer. How do you do an anti-join in pandas?
Since pandas 3, merge has the anti-join built in: how='left_anti' gives you the left rows with no match.
orphans = orders.merge(customers[['cust_id']], on='cust_id',
how='left_anti')
print(orphans) order_id cust_id amount
4 105 4 75One orphan: order 105, for customer 4, who isn't in the customer table.
The classic way, and the one for older pandas, is a left merge with indicator=True. It adds a _merge column that says both, left_only or right_only, so you also get the counts.
check = orders.merge(customers[['cust_id']], on='cust_id',
how='left', indicator=True,
validate='many_to_one')
print(check['_merge'].value_counts())_merge
both 5
left_only 1
right_only 0
Name: count, dtype: int645 orders match and 1 is left_only.
For a single key column, isin is short too: the orders whose customer ID is not in the customer list.
known = orders['cust_id'].isin(customers['cust_id'])
print(orders[~known]) order_id cust_id amount
4 105 4 75Same answer, order 105.
The indicator still wins for reconciliation, because an outer merge with the indicator shows what's missing on each side in one pass. That is how="outer" plus indicator=True.
Orphans are a data quality signal. Count them on every load, and route them to a late-arriving queue or an unknown customer row, instead of letting an inner join drop them silently.
Question 3: The business wants total, count and average per region in one table. How do you use groupby with agg?
Group by the region, then use named aggregation in agg. Each keyword is the output column, and its value is a pair: the source column and the function name, as a string like "sum", "count" or "mean".
Let's also count distinct customers with nunique. And as_index=False keeps the region as a normal column, ready to load into a table.
summary = (sales.groupby('region', as_index=False)
.agg(total=('amount', 'sum'),
orders=('order_id', 'count'),
customers=('cust_id', 'nunique'),
avg_amount=('amount', 'mean'))
.round(2))
print(summary) region total orders customers avg_amount
0 East 600 5 2 120.00
1 West 400 3 1 133.33East has 600 from 5 orders and 2 customers, an average of 120.00. West has 400 from 3 orders and 1 customer, an average of 133.33.
Two things trip people up:
countskips nulls, whilesizecounts every row, so pick the one you mean.- Pass function names as strings:
"sum", notnp.sum. In pandas 3,aggno longer replaces a NumPy function likenp.sumwith the pandas method.
Round only for display, never before you aggregate.
Question 4: Each order needs its share of the customer's total. What is the difference between agg and transform?
agg reduces. transform keeps the shape.
Let's start with agg.
print(sales.groupby('cust_id')['amount'].agg('sum'))cust_id
1 400
2 400
3 200
Name: amount, dtype: int64One row per group: three customers, with totals of 400, 400 and 200.
Now transform. It returns one value for every original row, the group's total repeated on each row of that group, with the same index. So we can divide straight away, with no merge back.
by_cust = sales.groupby('cust_id')['amount']
shares = sales[['order_id', 'cust_id', 'amount']].assign(
cust_total=by_cust.transform('sum'))
shares['share'] = shares['amount'] / shares['cust_total']
print(shares.sort_values(['cust_id', 'order_id'])) order_id cust_id amount cust_total share
0 201 1 100 400 0.25
2 203 1 100 400 0.25
5 206 1 200 400 0.50
1 202 2 200 400 0.50
4 205 2 100 400 0.25
7 208 2 100 400 0.25
3 204 3 150 200 0.75
6 207 3 50 200 0.25All eight rows are still there. Customer 1's total is 400. The order of 200 has a share of 0.50, and the two orders of 100 have 0.25 each.
That is the pandas version of a SQL window function: SUM(amount) OVER (PARTITION BY cust_id).
transform is how I add group totals, averages or counts as columns, without losing the detail rows.
Question 5: The report needs months as columns. How do you pivot and unpivot in pandas?
Use pivot_table to go wide and melt to go back to long.
Let's pivot: the region as the index, the month as the columns, the amount as the values, and sum as the aggregation. fill_value=0 fills the months with no sales.
wide = sales.pivot_table(index='region', columns='month',
values='amount', aggfunc='sum',
fill_value=0)
print(wide)month 2026-08 2026-09 2026-10
region
East 100 250 250
West 200 0 200East has 100, 250 and 250 for August to October. West has 200, 0 and 200.
Why pivot_table and not pivot? pivot only reshapes, so it needs exactly one value per region and month. Let's try it.
sales.pivot(index='region', columns='month',
values='amount')ValueError: Index contains duplicate entries, cannot reshapeEast has two orders in September, so pivot raises ValueError: Index contains duplicate entries, cannot reshape. pivot_table aggregates them.
To go back to long format, use melt. The region stays as the ID column, the month column names become a month column, and the values become the amount.
long = (wide.reset_index()
.melt(id_vars='region', var_name='month',
value_name='amount'))
print(long) region month amount
0 East 2026-08 100
1 West 2026-08 200
2 East 2026-09 250
3 West 2026-09 0
4 East 2026-10 250
5 West 2026-10 200Six rows. Be careful with the zero: West had no September sales, but the wide table filled a 0, and melt turns it into a real row, West 2026-09 0.
In a pipeline, keep the data long, and pivot only at the end, for the report.
Question 6: Your manager asks for each customer's latest order and a running total. How do you do window-style work in pandas?
In SQL you'd use window functions. In pandas, it is sort, then groupby, then a function that keeps every row. Sort by customer and order date first, because cumulative functions follow the row order.
Let's add a running total with cumsum on the grouped amount, and a row number with cumcount() + 1.
ordered = sales.sort_values(['cust_id', 'order_date'])
grp = ordered.groupby('cust_id')['amount']
ordered = ordered.assign(running_total=grp.cumsum(),
order_no=grp.cumcount() + 1)
print(ordered[['cust_id', 'order_id', 'amount',
'running_total', 'order_no']]) cust_id order_id amount running_total order_no
0 1 201 100 100 1
2 1 203 100 200 2
5 1 206 200 400 3
1 2 202 200 200 1
4 2 205 100 300 2
7 2 208 100 400 3
3 3 204 150 150 1
6 3 207 50 200 2Customer 1 goes 100, 200, 400. Customer 2 goes 200, 300, 400. Customer 3 goes 150, 200. And shift() gives the previous row, like LAG in SQL.
For the latest order, let's take the last row per customer with tail(1).
latest = ordered.groupby('cust_id').tail(1)
cols = ['cust_id', 'order_id', 'order_date', 'amount']
print(latest[cols]) cust_id order_id order_date amount
5 1 206 2026-10-02 200
7 2 208 2026-10-11 100
6 3 207 2026-10-08 50Orders 206, 208 and 207. For moving averages, run rolling inside transform, so the result lines up with the rows.
Sort explicitly, with a tie breaker, every time. groupby keeps the row order inside each group, so your sort is what defines the window.
Scenario 7: Two systems disagree on the monthly sales total. How do you reconcile them in Python?
Start at the grain where they disagree. Let's sum each system by month, put the two series side by side, and compute the difference.
app_m = app.groupby('month')['amount'].sum()
gl_m = ledger.groupby('month')['amount'].sum()
totals = pd.DataFrame({'app': app_m, 'ledger': gl_m})
totals['diff'] = (totals['app'] - totals['ledger']).round(2)
print(totals) app ledger diff
month
2026-09 245.75 245.75 0.00
2026-10 459.75 365.00 94.75September matches: 245.75 on both sides. October is off by 94.75: 459.75 in the app and 365.00 in the ledger.
Then drill down to the key. An outer merge on the order ID, with the indicator, and validate='one_to_one', puts every order from both systems on one row. We keep the rows that exist on one side only, that moved to another month, or where the amounts differ in cents.
both = app.merge(ledger, on='order_id', how='outer',
suffixes=('_app', '_gl'), indicator=True,
validate='one_to_one')
diff = (both['amount_app'] - both['amount_gl']).round(2)
one_side = both['_merge'] != 'both'
moved = both['month_app'] != both['month_gl']
breaks = both[one_side | (diff != 0) | moved]
cols = ['order_id', 'amount_app', 'amount_gl', '_merge']
print(breaks[cols]) order_id amount_app amount_gl _merge
4 305 99.75 NaN left_only
5 306 60.00 65.0 bothTwo orders explain it. Order 305 is left_only: it is only in the app, and the ledger never got it. Order 306 is 60.00 in the app, but 65.0 in the ledger.
99.75 missing, minus the 5.00 extra in the ledger, is exactly the 94.75 gap. When the breaks add up to the difference, you're done.
In a real project, check the usual causes:
- Duplicates on one side.
- Time zone cutoffs at month end.
- Late or reversed postings.
Compare money rounded to cents, and save the break report, so finance can see every difference.
Key points to remember
- validate= on every merge; check the row count after a left join
- Anti-join: how="left_anti" in pandas 3, or indicator=True
- Named aggregation: agg(total=("amount", "sum"))
- agg reduces, transform keeps the shape; pivot_table and melt
- Windows: sort, groupby, cumsum / tail; reconcile month, then order
The full script
Here is everything in one script: the sample data and every step from this post, in order. Copy it and run it top to bottom.
Show the full Python script (sample data and every step)
# ========================================================================
# Python Interview Q&A #4 - Joins, groupby and reshaping in pandas
# TechBrothersIT. Sample data is invented for teaching.
# Every step below was run with Python 3.14.6 and pandas 3.1.0; the outputs in the video and the blog
# post are the real outputs. Run it in an empty folder: it writes small sample files.
# ========================================================================
# Step 0: sample data
import pandas as pd
pd.set_option('display.width', 60)
# Q1 and Q2: orders, the customer master, and a CRM extract that keeps address history
orders = pd.DataFrame({'order_id': [101, 102, 103, 104, 105, 106],
'cust_id': [1, 2, 1, 3, 4, 2],
'amount': [120, 80, 200, 50, 75, 40]})
customers = pd.DataFrame({'cust_id': [1, 2, 3],
'name': ['Ali', 'Sara', 'John']})
crm = pd.DataFrame({'cust_id': [1, 1, 1, 2, 2, 3],
'city': ['Dallas', 'Austin', 'Houston',
'Denver', 'Boulder', 'Miami'],
'is_current': [False, False, True, False, True, True]})
# Q3 to Q6: sales lines with region and order date
sales = pd.DataFrame({
'order_id': [201, 202, 203, 204, 205, 206, 207, 208],
'cust_id': [1, 2, 1, 3, 2, 1, 3, 2],
'region': ['East', 'West', 'East', 'East',
'West', 'East', 'East', 'West'],
'order_date': pd.to_datetime(
['2026-08-05', '2026-08-12', '2026-09-03', '2026-09-15',
'2026-10-01', '2026-10-02', '2026-10-08', '2026-10-11'],
format='%Y-%m-%d'),
'amount': [100, 200, 100, 150, 100, 200, 50, 100],
})
sales['month'] = sales['order_date'].dt.strftime('%Y-%m')
# Q7: the order app and the finance ledger, for the same two months
app = pd.DataFrame({'order_id': [301, 302, 303, 304, 305, 306],
'month': ['2026-09'] * 3 + ['2026-10'] * 3,
'amount': [120.00, 80.50, 45.25, 300.00, 99.75, 60.00]})
ledger = pd.DataFrame({'order_id': [301, 302, 303, 304, 306],
'month': ['2026-09'] * 3 + ['2026-10'] * 2,
'amount': [120.00, 80.50, 45.25, 300.00, 65.00]})
# Step 1: A many-to-many merge multiplies the rows
joined = orders.merge(crm, on='cust_id', how='left')
print(len(orders), 'orders ->', len(joined), 'rows')
print(crm['cust_id'].value_counts())
# Step 2: validate='many_to_one' catches it
# This step is EXPECTED TO FAIL: it shows the error the video explains.
try:
orders.merge(crm, on='cust_id', how='left',
validate='many_to_one')
except Exception as e:
print(type(e).__name__ + ":", e)
# Step 3: Join at the right grain: one current row per customer
current = crm[crm['is_current']]
joined = orders.merge(current, on='cust_id', how='left',
validate='many_to_one')
print(len(orders), 'orders ->', len(joined), 'rows')
# Step 4: Anti-join in pandas 3: how='left_anti'
orphans = orders.merge(customers[['cust_id']], on='cust_id',
how='left_anti')
print(orphans)
# Step 5: Left merge with indicator=True: counts per match type
check = orders.merge(customers[['cust_id']], on='cust_id',
how='left', indicator=True,
validate='many_to_one')
print(check['_merge'].value_counts())
# Step 6: Single key: isin is short too
known = orders['cust_id'].isin(customers['cust_id'])
print(orders[~known])
# Step 7: Named aggregation per region
summary = (sales.groupby('region', as_index=False)
.agg(total=('amount', 'sum'),
orders=('order_id', 'count'),
customers=('cust_id', 'nunique'),
avg_amount=('amount', 'mean'))
.round(2))
print(summary)
# Step 8: agg reduces: one row per customer
print(sales.groupby('cust_id')['amount'].agg('sum'))
# Step 9: transform keeps the shape: share of the customer total
by_cust = sales.groupby('cust_id')['amount']
shares = sales[['order_id', 'cust_id', 'amount']].assign(
cust_total=by_cust.transform('sum'))
shares['share'] = shares['amount'] / shares['cust_total']
print(shares.sort_values(['cust_id', 'order_id']))
# Step 10: pivot_table: months as columns
wide = sales.pivot_table(index='region', columns='month',
values='amount', aggfunc='sum',
fill_value=0)
print(wide)
# Step 11: pivot cannot handle duplicate region and month pairs
# This step is EXPECTED TO FAIL: it shows the error the video explains.
try:
sales.pivot(index='region', columns='month',
values='amount')
except Exception as e:
print(type(e).__name__ + ":", e)
# Step 12: melt: back to long format
long = (wide.reset_index()
.melt(id_vars='region', var_name='month',
value_name='amount'))
print(long)
# Step 13: Running total and row number per customer
ordered = sales.sort_values(['cust_id', 'order_date'])
grp = ordered.groupby('cust_id')['amount']
ordered = ordered.assign(running_total=grp.cumsum(),
order_no=grp.cumcount() + 1)
print(ordered[['cust_id', 'order_id', 'amount',
'running_total', 'order_no']])
# Step 14: Latest order per customer
latest = ordered.groupby('cust_id').tail(1)
cols = ['cust_id', 'order_id', 'order_date', 'amount']
print(latest[cols])
# Step 15: Compare the monthly totals side by side
app_m = app.groupby('month')['amount'].sum()
gl_m = ledger.groupby('month')['amount'].sum()
totals = pd.DataFrame({'app': app_m, 'ledger': gl_m})
totals['diff'] = (totals['app'] - totals['ledger']).round(2)
print(totals)
# Step 16: Drill down to the orders that break
both = app.merge(ledger, on='order_id', how='outer',
suffixes=('_app', '_gl'), indicator=True,
validate='one_to_one')
diff = (both['amount_app'] - both['amount_gl']).round(2)
one_side = both['_merge'] != 'both'
moved = both['month_app'] != both['month_gl']
breaks = both[one_side | (diff != 0) | moved]
cols = ['order_id', 'amount_app', 'amount_gl', '_merge']
print(breaks[cols])
Part 5: Performance and Memory
The sample data on this page is invented for teaching. Every output is the real output of the code above it, run with Python 3.14.6 and pandas 3.1.0. Part of the Real Time Python for Data Engineers Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the official Python and library documentation for anything you quote in an interview.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.