CODEWITHSUNDEEP
pythonpandas
ForsakenPlague
ForsakenPlague

Reputation: 39

grouped by rolling means pandas

I need to calculate a rolling mean across data which is grouped by day. 

ID  BC_DT       BB_3M_DEFAULT_PROB  REGION
AA  27-Mar-18   0                   Chicago
BB  27-Mar-18   0.000002            Chicago
CC  27-Mar-18   0.000003            Chicago
DD  27-Mar-18   0.000006            Chicago
AA  28-Mar-18   0                   Dallas
BB  28-Mar-18   0                   New York
CC  28-Mar-18   0.000005            Chicago
DD  28-Mar-18   0.000004            Kansas City
AA  29-Mar-18   0.000002            Chicago
BB  29-Mar-18   0.000002            Chicago
CC  29-Mar-18   0.000002            Kansas City
DD  29-Mar-18   0.000005            Chicago
AA  30-Mar-18   0.000002            Kansas City
BB  30-Mar-18   0.019309            New York
CC  30-Mar-18   0.000004            Dallas
DD  30-Mar-18   0.000007            Chicago
AA  31-Mar-18   0.000002            Dallas
BB  31-Mar-18   0.000003            Dallas

#Set BC_DT to datetime format.
df_0['BC_DT'] = pd.to_datetime(df_0['BC_DT'])
#Set BC_DT to be index.
df_1 = df_1.set_index('BC_DT')
#Sort index so we're in chronological order.
df_1 = df_1.sort_index(axis=0, ascending=True)
#Calculate a daily mean grouping by day.
df_1['3M_DailyMean'] = df_1.groupby(df_1.index)['BB_3M_DEFAULT_PROB'].mean()

Here's where I get tripped up. 

df_1['3M_SMA'] = df_1.groupby(df_1.index)['BB_3M_DEFAULT_PROB'].rolling(10, center=False).mean().reset_index(0, drop=True)

I'm trying to group by date, then create a rolling mean with a span of 10 days. Anyone see where I'm tripping up?

I'm expecting to have the rolling mean operate over days, so each date should have the same 'mean.' 

2017-01-01         NaN
2017-01-02         NaN
2017-01-02         NaN
2017-01-02         NaN
2017-01-02         NaN
2017-01-02         NaN
2017-01-02         NaN
2017-01-02         NaN
2017-01-02         NaN
2017-01-02         NaN
2017-01-02    0.001851
2017-01-02    0.001592
2017-01-02    0.001592
2017-01-02    0.001593
2017-01-02    0.001592
2017-01-02    0.001592
2017-01-02    0.001592
2017-01-02    0.000003
2017-01-02    0.000005
2017-01-02    0.000005
2017-01-02    0.000005
2017-01-02    0.000004
2017-01-02    0.000004

Upvotes: 1

Views: 737

Answers (1)

Tyler K
Tyler K

Reputation: 338

Try creating a new dataframe and also try using pd.TimeGrouper.

# index is a datetime
df.set_index('BC_DT', inplace=True)

# group by day
groups  = df.groupby(pd.TimeGrouper('D'))

# new dataframe
df2 = pd.DataFrame()

# dataframe with days and sum of 'BB_3M_DEFAULT_PROB' per day
df2['daily_sum'] = groups['BB_3M_DEFAULT_PROB'].sum()

# add rolling mean to dataframe
df2['rolling_mean'] = df2['BB_3M_DEFAULT_PROB'].rolling(10,center=False).mean()

Upvotes: 1

Related Questions