pandas dataframe groupby datetime month

Question

Consider a csv file   string date number a string 2 5 11 9 16am 1 0 a string 3 5 11 10 44pm 2 0 a string 4 22 11 12 07pm 3 0 a string 4 22 11 12 10pm 4 0 a string 4 29 11 11 59am 1 0 a string 5 2 11 1 41pm 2 0 a string 5 2 11 2 02pm 3 0 a string 5 2 11 2 56pm 4 0 a string 5 2 11 3 00pm 5 0 a string 5 2 14 3 02pm 6 0 a string 5 2 14 3 18pm 7 0   I can read this in  and reformat the date column into datetime format   b pd read csv  b dat   b  date   pd to datetime b  date   format   m  d  y  I  M p     I have been trying to group the data by month  It seems like there should be an obvious way of accessing the month and grouping by that  But I can t seem to do it  Does anyone know how   What I am currently trying is re-indexing by the date   b index b  date     I can access the month like so   b index month   However I can t seem to find a function to lump together by month

User · Accepted Answer

Managed to do it   b   pd read csv  b dat   b index   pd to datetime b  date   format   m  d  y  I  M p   b groupby by  b index month  b index year     Or   b groupby pd Grouper freq  M       update for v0 21

User · Answer

Slightly alternative solution to  jpp s but outputting a YearMonth string   df  YearMonth     pd to datetime df  Date    apply lambda x    year - month   format year x year  month x month    res   df groupby  YearMonth    Values   sum

User · Answer

One solution which avoids MultiIndex is to create a new datetime column setting day   1  Then group by this column  Normalise day of month df   pd DataFrame   Date   pd to datetime   2017-10-05    2017-10-20    2017-10-01    2017-09-01                         Values    5  10  15  20       normalize day to beginning of month  4 alternative methods below df  YearMonth     df  Date     pd offsets MonthEnd -1    pd offsets Day 1  df  YearMonth     df  Date   - pd to timedelta df  Date   dt day-1  unit  D   df  YearMonth     df  Date   map lambda dt  dt replace day 1   df  YearMonth     df  Date   dt normalize   map pd tseries offsets MonthBegin   rollback   Then use groupby as normal  g   df groupby  YearMonth    res   g  Values   sum      YearMonth   2017-09-01    20   2017-10-01    30   Name  Values  dtype  int64  Comparison with pd Grouper The subtle benefit of this solution is  unlike pd Grouper  the grouper index is normalized to the beginning of each month rather than the end  and therefore you can easily extract groups via get group  some group   g get group  2017-10-01    Calculating the last day of October is slightly more cumbersome  pd Grouper  as of v0 23  does support a convention parameter  but this is only applicable for a PeriodIndex grouper  Comparison with string conversion An alternative to the above idea is to convert to a string  e g  convert datetime 2017-10-XX to string  2017-10   However  this is not recommended since you lose all the efficiency benefits of a datetime series  stored internally as numerical data in a contiguous memory block  versus an object series of strings  stored as an array of pointers

User · Answer

update  2018   Note that pd Timegrouper is depreciated and will be removed  Use instead    df groupby pd Grouper freq  M

[python] pandas dataframe groupby datetime month

Examples related to python

Examples related to pandas

Examples related to datetime

Examples related to pandas-groupby