CODEWITHSUNDEEP
pandas
Shubham R
Shubham R

Reputation: 7644

Group and divide a date column monthwise in pandas

i have a dataframe df:

store   date         invoice_count
 A    2018-04-03         2
 A    2018-04-06         5
 A    2018-06-15         5
 B    2018-05-05         2
 B    2018-04-09         5
 C    2018-02-16         6

which contains the invoice_counts(no of invoices generated) of stores for given dates.

I am trying to group them such that i get a month-wise invoice_count for every store.

Expected final dataframe in this format:

store jan_18  feb_18  mar_18  apr_18  may_18  june_18
  A     0       0       0       7       0        5
  B     0       0       0       5       2        0
  C     0       6       0       0       0        0

Is there any way to group dates based on month-wise??

Note: This is a dummy dataframe, the final monthly column names can be in other appropriate format.

Upvotes: 1

Views: 74

Answers (1)

jezrael
jezrael

Reputation: 862831

Use groupby with DataFrameGroupBy.resample and aggregate sum, then reshape by unstack and if necessary add missing columns with 0 by reindex, last change format of datetimes by DatetimeIndex.strftime:

df = (df.set_index('date')
        .groupby('store')
        .resample('m')['invoice_count']
        .sum()
        .unstack(fill_value=0))

df = df.reindex(columns=pd.date_range('2018-01-01', df.columns.max(), freq='m'), fill_value=0)
df.columns = df.columns.strftime('%b_%y')
print (df)
       Jan_18  Feb_18  Mar_18  Apr_18  May_18  Jun_18
store                                                
A           0       0       0       7       0       5
B           0       0       0       5       2       0
C           0       6       0       0       0       0

Upvotes: 3

Related Questions