CODEWITHSUNDEEP
pythonpandas
Kactus88
Kactus88

Reputation: 1

How to do a group by aggregation in Python with filter and date transformation

I've got the below dataset and want to sum the values based on vendor and month taken from the date whilst also applying a filter that returns only the first Monday of each month.

date      vendor   value
07/01/19  Amazon   10
07/01/19  Amazon   500
04/02/19  ebay     60
04/03/19  Amazon   130
06/03/19  ebay     20
25/03/19  pcworld  250

I believe pandas would be the best way forward but I'm new to python so wouldn't know.

vendor  month   value
Amazon  1       510
Amazon  3       130
ebay    2       60

Upvotes: 0

Views: 85

Answers (1)

Scott Boston
Scott Boston

Reputation: 153510

You can do it like this:

df['date'] = pd.to_datetime(df['date'], dayfirst=True)
#You data appears to be dayfirst

df_filt = df.where((df['date'].dt.dayofweek == 0) & (df['date'].dt.day < 8)).dropna(how='all')
#Filter out all data whre date isn't on monday nor in the first seven day of a month
df_fil.groupby(['vendor',df_fil['date'].dt.month])['value'].agg('sum').reset_index().rename(columns={'date':'month'})
#groupby with agg

Output:

   vendor  month  value
0  Amazon      1  510.0
1  Amazon      3  130.0
2    ebay      2   60.0

Upvotes: 1

Related Questions