Python Aggregate sum over dataframe with conditions

Question

I have a dataframe that looks like this:

stuff datetime value
A 1/1/2019 3
A 1/2/2019 4
A 1/3/2019 5
A 1/4/2019 6
...

I want to create a new dataframe that looks like this:

stuff avg_3 avg_4 avg_5
A 3.4 4.5 5.5
B 2.3 4.2 6.1

where avg_3 is the avg of the last 3 days from today, avg_4 is the avg of the last 4 days from today etc grouped by stuff

How do I do that?

My current code:

df.groupby('stuff').apply(lambda x: pd.Series(dict(
    day_3=(x.datetime > datetime.now() - timedelta(days = 3)).mean(),
    day_7=(x.datetime > datetime.now() -timedelta(days = 7)).mean())))

Thanks in advance

jezrael · Accepted Answer · 2019-01-26 09:58:34Z

1

Create boolean masks before groupby, add new columns by assign and groupby with mean:

m1 = df.datetime >  pd.datetime.now() - pd.Timedelta(days = 3)
m2 = df.datetime >  pd.datetime.now() - pd.Timedelta(days = 7)

df = df.assign(day_3=m1, day_7=m2).groupby('stuff')['day_3','day_7'].mean()

answered Jan 26, 2019 at 9:58

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SuperString Over a year ago

doesnt this just give the mean of the day_3 and day_7 column? shouldn't I need to specify I want it to be applied to the "value" column?

jezrael Over a year ago

@SuperString If remove ['day_3','day_7'] after groupby it count mean also value column. So possible solution should be remove this column afftwt assign like df = df.assign(day_3=m1, day_7=m2). drop('value', axis=1).groupby('stuff').mean()

Collectives™ on Stack Overflow

Python Aggregate sum over dataframe with conditions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related