how to groupby a dataframe every two month

user_4410288 0
Viewed 176

I have a dataframe that contains years, months and a score. For example:

df = pd.DataFrame({'year' : [2020, 2020, 2021, 2021], 
               'month': [1, 2, 3, 4],
               'score': [10,20,30,40]})

I would like to group by year and every two months. The dataframe after the group by should contain: the year, two months (e.g. 1-2, 3-4, etc) and the mean score.

I've found in other answers that I can map:

months = { '1' : 'B1',
  '2' : 'B1',
  '3' : 'B2',
  '4' : 'B2',
  '5' : 'B3',
  '6' : 'B3',
  '7' : 'B4',
  '8' : 'B4',
  '9' : 'B5',
  '10' : 'B5',
  '11' : 'B6',
  '12' : 'B6' }
   
df['two_months'] = df['month'].astype(str).map(months)

And then I can group:

df(['year','two_months'])[['score']].mean()

The problem is that then two_months is a string, and I lose the option to sort it as can be done for datetime objects. My question: is there another way to perform this?

pandas-groupby pandas dataframe python
1 Answers

First idea is use some math with subtract 1 and integer division by 2:

s = (df['month'] - 1) // 2 + 1
df0 = df.groupby(['year', s.rename('two_months')])['score'].mean()
print (df0)
year  two_months
2020  1             15
2021  2             35
Name: score, dtype: int64

Or create datetimes and use Grouper:

df['date'] = pd.to_datetime(df[['month', 'year']].assign(day=1))

df1 = df.groupby(['year', pd.Grouper(freq='2MS', key='date')])['score'].mean()
print (df1)
year  date      
2020  2020-01-01    15
2021  2021-03-01    35
Name: score, dtype: int64

If processing Months separately or months periods, there are misisng values for non exist datetimes like:

df['date'] = pd.to_datetime(df[['month', 'year']].assign(day=1))

df2 = df.groupby( pd.Grouper(freq='2MS', key='date'))['score'].mean()
print (df2)
date
2020-01-01    15.0
2020-03-01     NaN
2020-05-01     NaN
2020-07-01     NaN
2020-09-01     NaN
2020-11-01     NaN
2021-01-01     NaN
2021-03-01    35.0
Freq: 2MS, Name: score, dtype: float64

df['per'] = pd.to_datetime(df[['month', 'year']].assign(day=1)).dt.to_period('m')
df3 = df.set_index('per').groupby( pd.Grouper(freq='2M'))['score'].mean()
print (df3)
per
2020-01    15.0
2020-03     NaN
2020-05     NaN
2020-07     NaN
2020-09     NaN
2020-11     NaN
2021-01     NaN
2021-03    35.0
Freq: 2M, Name: score, dtype: float64

For remove NaNs is possible use:

df2 = df2.dropna()
df3 = df3.dropna()
user_29010020
Related