Change versus previous row per group

Pandas data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 14 minutes. Part of the Pro drill bank.

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.

df has store, day (an ISO date string) and sales, one row per store and day, not sorted. Add, within each store and in date order: change: sales minus the previous day's sales of the same store pct_change: 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 NULL. Columns: store, day, sales, change, pct_change. Sort by store, then day, reset the index. Assign the DataFrame to result.

Requirements

• Previous row means previous day of the same store.

Constraints

• Sales values are positive.

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