Pandas - group sales by month-CodePudding

I need help with group sales for employee_ID by month.

with this:

date      |employee_ID |price
2000-01-01|    12      | 300
2000-01-02|    12      | 250

i want make this

date    | employee_ID |total_sales
2000-01 |     12            2

I dont know how to do this with groupby

CodePudding user response：

Use Grouper with GroupBy.size:

df['date'] = pd.to_datetime(df['date'])

df = df.groupby([pd.Grouper(freq='M', key='date'), 'employee_ID']).size().reset_index(name='total_sales')

Or month periods by Series.dt.to_period:

df = df.groupby([df['date'].dt.to_period('m'), 'employee_ID']).size().reset_index(name='total_sales')

CodePudding user response：

An alternative to groupby in this case is to use value_counts:

If the dtype of column date is str:

>>> df.value_counts([df['date'].str[:7], 'employee_ID']) \
      .reset_index(name='total_sales')

      date  employee_ID  total_sales
0  2000-01           12            2

If the dtype of column date is datetime:

>>> df.value_counts([df['date'].dt.strftime('%Y-%m'), 'employee_ID']) \
      .reset_index(name='total_sales')

      date  employee_ID  total_sales
0  2000-01           12            2

You can replace: df['date'].dt.strftime('%Y-%m') by: df['date'].dt.to_period('m') as used by @jezrael.