Merge CSVs and build revenue pivot with pandas
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Take-home Project
You receive four CSVs and must replicate an Excel VLOOKUP + PivotTable workflow using Python/pandas.
CSV samples:
customers.csv
customer_id,signup_date,channel
1,2025-06-02,Organic
2,2025-06-10,Ads
3,2025-07-05,Referral
orders.csv
order_id,customer_id,order_date,total_amount
101,1,2025-06-12,120
102,2,2025-06-28,80
103,1,2025-07-15,200
104,3,2025-08-02,50
refunds.csv
order_id,refund_amount
102,20
104,50
targets.csv
month,channel,revenue_target
2025-06,Organic,100
2025-06,Ads,120
2025-07,Organic,150
2025-08,Referral,60
Task: Write pandas code to (a) robustly load these CSVs (assume one file path may initially fail—gracefully retry or fall back without crashing), (b) compute net_revenue per order = total_amount - COALESCE(refund_amount,0), (c) roll up to monthly net revenue by channel for months 2025-06 through 2025-08 based on order_date, (d) left-join the result to targets.csv on (month, channel) to compute target_gap = net_revenue - revenue_target (treat missing targets as 0), and (e) produce a pivot table with index=month (YYYY-MM), columns=channel, values=net_revenue, plus an additional similarly-shaped table for target_gap. Ensure date parsing is correct, missing refunds are handled, and channels with no orders in a month still appear with 0 in the pivot.
Quick Answer: This question evaluates proficiency in data manipulation and ETL using pandas, covering robust file I/O, join/merge semantics, null/coalescing behavior, date parsing, aggregation, and pivoting.
