How can get in pandas groupby rolling mean/median with dropping missing values? I.e. the output should drop missing values before calculating mean/median instead of giving me NaN if a missing value is present.
import pandas as pd
t = pd.DataFrame(data={v.date:[0,0,0,0,1,1,1,1,2,2,2,2],
'i0':[0,1,2,3,0,1,2,3,0,1,2,3],
'i1':['A']*12,
'x':[10.,20.,30.,np.nan,np.nan,21.,np.nan,41.,np.nan,np.nan,32.,42.]})
t.set_index([v.date,'i0','i1'], inplace=True)
t.sort_index(inplace=True)
print(t)
print(t.groupby('date').apply(lambda x: x.rolling(window=2).mean()))
gives
x
date i0 i1
0 0 A 10.0
1 A 20.0
2 A 30.0
3 A NaN
1 0 A NaN
1 A 21.0
2 A NaN
3 A 41.0
2 0 A NaN
1 A NaN
2 A 32.0
3 A 42.0
x
date i0 i1
0 0 A NaN
1 A 15.0
2 A 25.0
3 A NaN
1 0 A NaN
1 A NaN
2 A NaN
3 A NaN
2 0 A NaN
1 A NaN
2 A NaN
3 A 37.0
while I want the following for this example:
x
date i0 i1
0 0 A 10.0
1 A 15.0
2 A 25.0
3 A 30.0
1 0 A NaN
1 A 21.0
2 A 21.0
3 A 41.0
2 0 A NaN
1 A NaN
2 A 32.0
3 A 37.0
what I tried
t.groupby('date').apply(lambda x: x.rolling(window=2).dropna().median())
and
t.groupby('date').apply(lambda x: x.rolling(window=2).median(dropna=True))
(both raise exceptions, but maybe there exists something along the lines)
Thank you for your help!
See Question&Answers more detail:
os