Change versus previous row per group

Pandas data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 14 minutes. Free to practice.

Per store, the day-over-day change and percent change in sales. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

To track how a store is doing, you compare each day with the day before. df has store, day (a date as text) and sales, 1 row for each store and day, in no particular order. For each store, in date order, add 2 columns. change is sales minus the previous day's sales of the same store. pct_change is that change as a percentage of the previous day's sales, rounded to 2 decimals. The first day of each store has no previous day, so both are empty (NaN). Return store, day, sales, change and pct_change, sorted by store and then day, with a clean index. The editor builds df and ends with result = df. Change the last line. Use groupby("store") with diff() and pct_change().

Requirements

• Sort by store and day first.
• Add change with groupby("store")["sales"].diff().
• Add pct_change with pct_change() * 100, rounded to 2 decimals, inside each store.
• Return the 5 columns with a clean index in result.

Constraints

• The calculation must restart for each store.
• The first day of each store has NaN in both new columns.
• A store with 1 day only has NaN in both columns.

Examples

Input: df store | day | sales s2 | 2024-06-02 | 150 s1 | 2024-06-02 | 120 s1 | 2024-06-01 | 100 s2 | 2024-06-01 | 200 s1 | 2024-06-03 | 90 s3 | 2024-06-01 | 50 Output: store | day | sales | change | pct_change s1 | 2024-06-01 | 100 | NULL | NULL s1 | 2024-06-02 | 120 | 20 | 20 s1 | 2024-06-03 | 90 | -30 | -25 s2 | 2024-06-01 | 200 | NULL | NULL s2 | 2024-06-02 | 150 | -50 | -25 s3 | 2024-06-01 | 50 | NULL | NULL s1 goes 100, 120, 90: changes +20 and -30. s2 goes 200 to 150: -50 (-25%). The first row of s2 and s3 have no previous day.

Topics: lakebench, pandas, diff, pct_change, groupby.

More interview problems · All interview problems · Learn data engineering