Monthly transaction stats per country with conditional aggregation
transactions(id, country, state, amount, trans_date) where state is 'approved' or 'declined'. For each (month, country) return four columns: trans_count (all), approved_count, trans_total_amount (all), approved_total_amount. Month is the YYYY-MM of trans_date.
import pandas as pd
def monthly_transactions(transactions: pd.DataFrame) -> pd.DataFrame:
# your code hereWrite the implementation.
Build a month key and approved-only helper columns, then group so one pass yields all four aggregates. Set month = trans_date.dt.strftime('%Y-%m'), add is_approved = (state == 'approved') and approved_amount = amount.where(state == 'approved', 0).
- ✗Dropping declined rows, which breaks the all-transactions totals
- ✗Assuming approved figures equal the totals
- ✗Grouping on the wrong dimensions
- →Why use amount.where(...) instead of filtering the frame?
- →How would you add a declined_count without a second pass?
Compute the approved figures with helper columns so all four aggregates come from one grouping over every row.
import pandas as pd
def monthly_transactions(transactions: pd.DataFrame) -> pd.DataFrame:
t = transactions.copy()
t['month'] = t['trans_date'].dt.strftime('%Y-%m')
t['is_approved'] = (t['state'] == 'approved').astype(int)
t['approved_amount'] = t['amount'].where(t['state'] == 'approved', 0)
return (t.groupby(['month', 'country'])
.agg(trans_count=('id', 'count'),
approved_count=('is_approved', 'sum'),
trans_total_amount=('amount', 'sum'),
approved_total_amount=('approved_amount', 'sum'))
.reset_index())Filtering to approved rows first would lose the all-transactions counts and amounts. Keeping every row and using where / a 0/1 flag lets a single grouping produce both the totals and the approved-only figures.
