Iterating a groupby datetime over several weeks-CodePudding

I'm trying to group my data by a week that I predefined using to_datetime and timedelta. However, after copying my script a few times, I was hoping there was a way to iterate this process over multiple weeks. Is this something that can be done?

The data set that I'm working with lists sales out sales revenue and spending out by the day for each data source and its corresponding id.

Below is what I have so far but my knowledge of loops is pretty limited due to being self-taught.

Let me know if what I'm asking is feasible or if I have to continue to copy my code every week.

Code

import pandas as pd
from datetime import datetime, timedelta,date


startdate = '2021-09-26'
enddate =  pd.to_datetime(startdate)   timedelta(days=6)


last7 = (df.date >= startdate) & (df.date <= enddate)
df = df.loc[last7,['datasource','id','revenue','spend']]

df.groupby(by=['datasource_name','id'],as_index=False).sum()
df['start_date'] = startdate
df['end_date'] = enddate
df

CodePudding user response：

If I have understood your issue correctly, you are basically trying to aggregate daily data into weekly. You can try following code

import datetime as dt
import pandas as pd

#Get weekend date for each date

df['week_end_date']=df['date'].apply(lambda x: pd.Period(x,freq='W').end_time.date().strftime('%Y-%m-%d'))
#Aggregate sales and revenue at weekly level

df_agg = df.groupby(['datasource_name','id','week_end_date']).agg({'revenue':'sum','spend':'sum'}).reset_index()

df_agg will have all your sales and revenue numbers aggregated by the weekend date for corresponding date.