How can I group the following data into by month using pandas:
17/1/2001 800
7/1/2001 1300
2/1/2001 400
1/1/2001 200
25/3/2001 1800
8/3/2001 1300
and then have the following output with first and last days of the month and the corresponding first and last values:
First Last First Last
1/1/2001 17/1/2001 200 800
8/3/2001 25/3/2001 1300 1800
Thanks
try this:
In [102]: res = df.sort_values('date').groupby(df.date.dt.month).agg(['first','last'])
In [104]: res.columns = ['date_first', 'date_last', 'first', 'last']
In [105]: res
Out[105]:
date_first date_last first last
date
1 2001-01-01 2001-01-17 200 800
3 2001-03-08 2001-03-25 1300 1800
or min, max depending on what you want:
In [95]: res = df.groupby(df.date.dt.month).agg(['min','max'])
In [96]: res.columns = ['date_min', 'date_max', 'min', 'max']
In [97]: res
Out[97]:
date_min date_max min max
date
1 2001-01-01 2001-01-17 200 1300
3 2001-03-08 2001-03-25 1300 1800
Use idxmin and idxmax to identify indices for which to grab the appropriate rows.
def get_min(x):
return x.loc[x.date.idxmin(), :]
def get_max(x):
return x.loc[x.date.idxmax(), :]
def app_by_month(df, f):
return df.groupby(df.date.dt.month).apply(f)
df2 = pd.concat([app_by_month(df, f) for f in [get_min, get_max]],
axis=1, keys=['first', 'last']).sort_index(axis=1, level=1)
df2.columns = df2.columns.to_series().str.join('_').values
print df2
first_date last_date first_value last_value
date
1 2001-01-01 2001-01-17 200 800
3 2001-03-08 2001-03-25 1300 1800
