0

Want to group my data by month-wise or specified date-range. Have nearly 3 months of data with non-unique dates (YYYY-MM-DD), already in pandas 'date' format, like 

Col1 Col2 Date       Val 
12   23   2018-11-20  4
13   78   2018-09-21  4
14   82   2018-10-28  5
15   22   2018-11-10  8
17   35   2018-10-23  9

Want to separate them in Nov(11), Oct(10), Sept(09) wise.

Thanks in advance.

Fakir
  • 139
  • 11

1 Answers1

1

Here the best is create dictionary:

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

d = dict(tuple(df.groupby(df['Date'].dt.month)))
print (d)
{9:    Col1  Col2       Date  Val
1    13    78 2018-09-21    4, 10:    Col1  Col2       Date  Val
2    14    82 2018-10-28    5
4    17    35 2018-10-23    9, 11:    Col1  Col2       Date  Val
0    12    23 2018-11-20    4
3    15    22 2018-11-10    8}

print (d[9])
   Col1  Col2       Date  Val
1    13    78 2018-09-21    4

Or:

d = dict(tuple(df.groupby(df['Date'].dt.strftime('%b'))))
print (d)
{'Nov':    Col1  Col2       Date  Val
0    12    23 2018-11-20    4
3    15    22 2018-11-10    8, 'Oct':    Col1  Col2       Date  Val
2    14    82 2018-10-28    5
4    17    35 2018-10-23    9, 'Sep':    Col1  Col2       Date  Val
1    13    78 2018-09-21    4}

print (d['Nov'])
   Col1  Col2       Date  Val
0    12    23 2018-11-20    4
3    15    22 2018-11-10    8

It is not recommended, but possible create DataFrames by groups:

for i, g in df.groupby(df['Date'].dt.strftime('%b')):
    globals()['df_' + str(i)] =  g

print (df_Nov)
   Col1  Col2       Date  Val
0    12    23 2018-11-20    4
3    15    22 2018-11-10    8

If need aggregate by months:

df.groupby(df['Date'].dt.month).sum()

Or:

df.groupby(df['Date'].dt.strftime('%b')).sum()
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thanks. My explanation was may not be good. I just want to split the dataframe into parts, say 'df_nov', 'df_oct', 'df_sept' from the main dataframe. The one you have suggested summing up the values of all the other columns with respect to these three months (which might be useful later on). – Fakir Aug 21 '19 at 07:59
  • 1
    Tried the 1st one. Perfect! Thank you so much. Will be very handy in various cases also. – Fakir Aug 21 '19 at 08:21