I want to filter a dataframe after having done a group by on it but am getting a keyerror, here is some example code:
df = pd.DataFrame([
[0, 1, 'm', 5.0], [0, 1, 'm', -7.0],[0, 1, 'm', 9.0],[0, 1, 'm', 32.0],[0, 1, 'm', -11.0],
[0, 6, 'm', -12.0], [0, 6, 'm', 15.0],[0, 6, 'm', -16.0],[0, 6, 'm', -3.0],[0, 6, 'm', 21.0],
[0, 12, 'm', 15.0], [0, 12, 'm', 51.0],[0, 12, 'm', 4.0],[0, 12, 'm', 3.0],[0, 12, 'm', 1.0],
[1, 1, 'm', 5.0], [1, 1, 'm', -7.0],[1, 1, 'm', 9.0],[1, 1, 'm', 32.0],[1, 1, 'm', -11.0],
[1, 6, 'm', -12.0], [1, 6, 'm', 15.0],[1, 6, 'm', -16.0],[1, 6, 'm', -3.0],[1, 6, 'm', 21.0],
[1, 12, 'm', 15.0], [1, 12, 'm', 51.0],[1, 12, 'm', 4.0],[1, 12, 'm', 3.0],[1, 12, 'm', 1.0]
],
columns=['id', 'timeperiod', 'timeperiodtype', 'value'])
df['good'] = df['value'].apply(lambda x: 1 if x>0 else 0)
print(df)
print(df[df['timeperiod']>6])
df = df[['id', 'timeperiod','timeperiodtype','good']][df['timeperiod']>0].groupby(['id','timeperiod','timeperiodtype']).mean()
print(df[df['timeperiod']>6])
I want to avoid using reset_index as in the final code I will have several dataframes of similar shape that I will be aggregating/merging/concatenating.
I am sure I must be missing something obvious.
How can I use the column names to filter the grouped dataframe?
Use DataFrame.loc for filter by condition and by columns names and then for avoid MultiIndex add DataFrame.reset_index or parameter as_index=False:
df = df.loc[df['timeperiod']>0, ['id', 'timeperiod','timeperiodtype','good']].groupby(['id','timeperiod','timeperiodtype']).mean().reset_index()
Or:
df = df.loc[df['timeperiod']>0, ['id', 'timeperiod','timeperiodtype','good']].groupby(['id','timeperiod','timeperiodtype'], as_index=False).mean()
print(df)
id timeperiod timeperiodtype good
0 0 1 m 0.6
1 0 6 m 0.4
2 0 12 m 1.0
3 1 1 m 0.6
4 1 6 m 0.4
5 1 12 m 1.0
print(df[df['timeperiod']>6])
id timeperiod timeperiodtype good
2 0 12 m 1.0
5 1 12 m 1.0
EDIT:
For filter in MuiltiIndex is possible use Index.get_level_values:
df = df.loc[df['timeperiod']>0, ['id', 'timeperiod','timeperiodtype','good']].groupby(['id','timeperiod','timeperiodtype']).mean()
print(df)
good
id timeperiod timeperiodtype
0 1 m 0.6
6 m 0.4
12 m 1.0
1 1 m 0.6
6 m 0.4
12 m 1.0
print(df[df.index.get_level_values('timeperiod')>6])
good
id timeperiod timeperiodtype
0 12 m 1.0
1 12 m 1.0