PART 7 · PRACTICE, PREP FLOW AND REVISION

Interview set: pandas and data wrangling

FreeLesson 74 of 78

The pandas round usually mirrors the SQL round. These five come up in some form in most DA and DE loops.

Q43The top customer by delivered spend in each month.DADEMEDIUM

Approach: Aggregate to (month, customer), then idxmax within each month to pick the row.

import pandas as pd

o = pd.read_csv("orders.csv", parse_dates=["order_date"])
spend = (o[o.status == "delivered"]
         .groupby([o.order_date.dt.to_period("M"), "customer_id"])
         ["amount"].sum().reset_index())
top = spend.loc[spend.groupby("order_date")["amount"].idxmax()]
print(top.to_string(index=False))

Output

order_date  customer_id  amount
   2025-01            1  1198.0
   2025-02            1  1499.0
   2025-03            5  3498.0
   2025-04            1  2397.0

Q44Month-over-month growth in delivered revenue, as a percentage.DAEASY

Approach: Resample to months, then pct_change compares each month with the one before.

import pandas as pd

o = pd.read_csv("orders.csv", parse_dates=["order_date"])
rev = (o[o.status == "delivered"]
       .set_index("order_date")["amount"].resample("MS").sum())
print(pd.DataFrame({"revenue": rev,
                    "mom_pct": (rev.pct_change() * 100).round(1)}))

Output

            revenue  mom_pct
order_date
2025-01-01   1994.0      NaN
2025-02-01   2847.0     42.8
2025-03-01   7291.0    156.1
2025-04-01   6840.0     -6.2

Q45Daily delivered revenue for 1 to 7 April with the missing days as 0, plus a 3-day rolling sum.DADEMEDIUM

Approach: Aggregate per day, reindex onto a full date range, fill zeros, then rolling(3).

import pandas as pd

o = pd.read_csv("orders.csv", parse_dates=["order_date"])
delivered = o[o.status == "delivered"]
daily = delivered.groupby("order_date")["amount"].sum()
days = pd.date_range("2025-04-01", "2025-04-07", freq="D")
daily = daily.reindex(days, fill_value=0)
print(pd.DataFrame({"revenue": daily,
                    "rev_3d": daily.rolling(3, 1).sum()}))

Output

            revenue  rev_3d
2025-04-01      0.0     0.0
2025-04-02   1999.0  1999.0
2025-04-03      0.0  1999.0
2025-04-04      0.0  1999.0
2025-04-05    846.0   846.0
2025-04-06      0.0   846.0
2025-04-07      0.0   846.0

Q46Customers whose every order was delivered.DAEASY

Approach: groupby + all() on a boolean column. A "did all of X" question.

import pandas as pd

o = pd.read_csv("orders.csv")
flag = o.assign(d=o.status.eq("delivered"))
ok = flag.groupby("customer_id")["d"].all()
print(ok[ok].index.tolist())

Output

[2, 5, 6, 7]

Q47For each customer: first order date, number of orders, and days until their second order.DADEMEDIUM

Approach: Sort, number each customer's orders with cumcount, then line up order 0 and order 1 by customer.

import pandas as pd

o = pd.read_csv("orders.csv", parse_dates=["order_date"])
o = o.sort_values(["customer_id", "order_date"])
o["n"] = o.groupby("customer_id").cumcount()      # 0, 1, 2...
first = o[o.n == 0].set_index("customer_id").order_date
second = o[o.n == 1].set_index("customer_id").order_date
print(pd.DataFrame({"first": first.dt.date,
                    "orders": o.groupby("customer_id").size(),
                    "days_to_2nd": (second - first).dt.days}))

Output

                  first  orders  days_to_2nd
customer_id
1            2025-01-10       4         23.0
2            2025-01-15       3         34.0
3            2025-02-10       2         38.0
4            2025-03-01       2         45.0
5            2025-03-12       2         47.0
6            2025-03-22       1          NaN
7            2025-04-05       1          NaN

Practice before you move on

Solve Q43 to Q47 in the Try it editor before you read the solutions.
Pick one question and write the plain Python version of the pandas answer.

Watch the free lesson

The same topic in the Free Lessons library, free with a sign-in.