Daily to weekly and monthly

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

Roll daily revenue up to weeks that start on Monday and to calendar months. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Finance wants revenue by week and by month, in one table. df has day (a date as text) and revenue, and some days are missing. Return one table that stacks two summaries. The rows with period_type = 'week' have period_start set to the Monday of the week, and revenue is the sum for that week. The rows with period_type = 'month' have period_start set to the first day of the month, and revenue is the sum for that month. Write period_start as text in YYYY-MM-DD format. Return period_type, period_start and revenue, sorted by period_type and then period_start, with a clean index. The editor builds df and ends with result = df. Change the last line.

Requirements

  • Convert day to a real date.
  • Find the Monday of each week and the first day of each month.
  • Sum revenue for each week and for each month, and stack the 2 tables with concat.
  • Write period_start as YYYY-MM-DD text, sort, reset the index and store it in result.

Constraints

  • Weeks start on Monday, so Monday is weekday() == 0.
  • The monthly rows use the first day of the month, e.g. 2024-02-01.
  • The result has 5 rows in the sample: 2 months and 3 weeks.

Examples

Input: df day | revenue 2024-01-30 | 10 2024-01-31 | 20 2024-02-01 | 5 2024-02-04 | 15 2024-02-05 | 30 2024-02-07 | 25 2024-02-13 | 40 Output: period_type | period_start | revenue month | 2024-01-01 | 30 month | 2024-02-01 | 115 week | 2024-01-29 | 50 week | 2024-02-05 | 55 week | 2024-02-12 | 40 The week of Monday 2024-01-29 holds Jan 30, Jan 31, Feb 1 and Sunday Feb 4 (50.0) even though it crosses a month boundary; January only counts Jan 30 and 31 (30.0).

Topics: lakebench, pandas, resample, dates, weeks.

More interview problems · All interview problems · Learn data engineering

intermediate

Daily to weekly and monthly

Interview-style drill: Roll daily revenue up to weeks that start on Monday and to calendar months.

`df` has `day` (an ISO date string) and `revenue`, with some days missing. Return one DataFrame that stacks two summaries: - rows with `period_type = 'week'`: `period_start` is the **Monday** of the week and `revenue` is the sum over that week - rows with `period_type = 'month'`: `period_start` is the first day of the month and `revenue` is the sum over that month Write `period_start` as `YYYY-MM-DD` text. Columns: `period_type`, `period_start`, `revenue`. Sort by `period_type`, then `period_start`, reset the index. Assign the DataFrame to `result`.