So every day I have to pull a report that gets data for yesterday’s metrics, forecasted Saturday’s metrics, and Week to date sums.
I want to write a Python code that automates the process.
I have most of the code done but I’m having issues with the week-to-date sums.
I want it to be versatile cause the sum will increase each day of the week. So if I run the code tomorrow, it will take into account all values from Sunday to Tuesday. If I run it on Friday, it will be Sunday to Thursday.
The data looks like this (dummy data):
| Location | Metric | 2024-09-04 | 2024-09-05 | 2024-09-06 | … | 2024-09-14 |
|---|---|---|---|---|---|---|
| ABC1 | Sales | 123 | 12 | 14 | 15 | |
| ABC1 | Profit | 15 | 10 | 45 | 43 |
I want it to look like this:
| Location | Metric | Yesterday | Saturday | Week to Date |
|---|---|---|---|---|
| ABC1 | Sales | 12 | 15 | 100 |
| ABC1 | Profit | 10 | 43 | 120 |
So it doesn’t matter when I run the code, it will be versatile enough to adapt to the dates/days.
I have the data for Yesterday and Saturday showing properly. But the sum is giving me 0s for WtD for every value.
So it looks more like this for every row:
| Location | Metric | Yesterday | Saturday | Week to Date |
|---|---|---|---|---|
| ABC1 | Sales | 12 | 15 | 0 |
| ABC1 | Profit | 10 | 43 | 0 |
df = pd.read_excel("Data.xlsx")
date_col = df.columns[3:]
df.columns = df.columns[:3].tolist() + pd.to_datetime(date_col, errors='coerce').tolist()
df.columns = df.columns[:3].tolist() + [d.date() for d in df.columns[3:]]
today = datetime.today().date()
yesterday = (datetime.today() - timedelta(days=1)).date()
week_start = today - timedelta(days=today.weekday())
saturday = week_start + timedelta(days=5)
if yesterday in df.columns:
df_yesterday = df[['Location','Metric', yesterday]]
if saturday in df.columns:
df_saturday = df[['Location','Metric',saturday]]
sunday = yesterday - timedelta(days=yesterday.weekday() + 1)
wtd = df.columns[(df.columns >= pd.Timestamp(sunday)) & (df.columns <= pd.Timesstamp(yesterday))]
3
One possible solution is to use melt to get the dates in a single column.
df = df.melt(id_vars=['Location', 'Metric'], value_name='value', var_name='date')
df['date'] = pd.to_datetime(df['date']).dt.date
This gives a result as follows (note that the dates are changed slightly from the example data):
Location Metric date value
0 ABC1 Sales 2024-09-09 123
1 ABC1 Profit 2024-09-09 15
2 ABC1 Sales 2024-09-10 12
3 ABC1 Profit 2024-09-10 10
4 ABC1 Sales 2024-09-11 14
5 ABC1 Profit 2024-09-11 45
6 ABC1 Sales 2024-09-14 15
7 ABC1 Profit 2024-09-14 43
Now, we can compute the dates as before, and filter the data together with groupby to get the wanted aggregations:
today = datetime.today().date() # 2024-09-11
yesterday = today - timedelta(days=1) # 2024-09-10
next_saturday = today - timedelta(days=today.weekday() - 5) # 2024-09-14
sunday = today - timedelta(days=today.weekday() + 1) # 2024-09-08
df_y = df[df['date'] == yesterday].groupby(['Location', 'Metric'])['value'].sum()
df_s = df[df['date'] == next_saturday].groupby(['Location', 'Metric'])['value'].sum()
df_ws = df[(df['date'] > sunday) & (df['date'] < today)].groupby(['Location', 'Metric'])['value'].sum()
Finally, we merge the resulting Series into a DataFrame:
df = pd.DataFrame({'Yesterday': df_y, 'Saturday': df_s, 'Week-to-date': df_ws}).reset_index()
Result:
Location Metric Yesterday Saturday Week-to-date
0 ABC1 Profit 10 43 25
1 ABC1 Sales 12 15 135
2