Pandas aggregate by year and month and sum other column

user_10754437 0
Viewed 110

Having the following code:

import pandas as pd
data = {
    'x': ['2019-07-29', '2019-07-30', '2019-07-31', '2019-08-01', '2019-08-02', '2019-08-03'],
    'y': [4, 6, 4, 4, 6, 7]
}
df = pd.DataFrame(data = data, columns = ['x', 'y'])
df

This will output:

    x   y
0   2019-07-29  4
1   2019-07-30  6
2   2019-07-31  4
3   2019-08-01  4
4   2019-08-02  6
5   2019-08-03  7

Is it possible to group the dates in column x by year and month and sum the amounts in x and place the result in a new dataframe? Like so:

    x   y
0   2019-07  13
1   2019-08  17
python-datetime pandas python-3.x python
2 Answers

Use pd.to_datetime to convert x to pandas datetime. Then groupby on Series.dt.year and Series.dt.month:

In [181]: df.x = pd.to_datetime(df.x)
In [194]: df = df.groupby([df.x.dt.year, df.x.dt.month]).agg(sum).rename_axis(['year', 'month']).reset_index().rename(columns={'y':'sum'})

In [195]: df
Out[195]: 
   year  month  sum
0  2019      7   14
1  2019      8   17
user_58208140

Something like this might work:

>>> df.groupby(df['x'].str[:-3])['y'].sum()
x
2019-07    14
2019-08    17
user_108587860
Related