CODEWITHSUNDEEP
pythonpandasdatetimeaggregationweekday
Kael_1993
Kael_1993

Reputation: 15

Sum up Week to Date values in a versatile manner - Pandas

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))]

Upvotes: -3

Views: 70

Answers (1)

Shaido
Shaido

Reputation: 28322

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

Upvotes: 1

Related Questions