Merging, Joining, and Reshaping
Explore how to combine and reshape data using pandas merge, concat, pivot, and melt functions alongside SQL join concepts. Learn when to use inner or left joins, stack similar datasets, and convert data between long and wide formats to prepare data for analysis and reporting.
To answer which subscription plans have the highest refund rate and how much revenue refunds remove, we need fields that live in two tables. The subscriptions table contains user_id, plan_id, and monthly_price. The refunds table contains refund_id, user_id, and refund_amount. Neither table answers the question alone.
A key is a column whose values identify how rows in related tables match. Here, user_id connects a subscription to that user’s refund records. pandas.merge() performs the same basic job as a SQL JOIN: it creates a new DataFrame by matching rows on a shared key. Once the tables are combined, we can group the result by plan_id and calculate plan-level refund totals or rates.
The important decision comes before writing merge(): do we want only subscriptions that have refunds, or do we need every subscription, including those without a refund?
Preserve only matches or all subscriptions
An inner join keeps rows whose user_id appears in both subscriptions and refunds. A left join keeps every row from the left DataFrame, here subscriptions, even when no matching refund exists. For a subscription without a refund, the ...