CODEWITHSUNDEEP
pythonpandasdatetimegroup-bypandas-groupby
Vincent L
Vincent L

Reputation: 739

Pandas grouping by start of the month with pd.Grouper

I have a DataFrame with hourly timestamps:

2019-01-01 0:00:00             1
2019-01-01 1:00:00             2
2019-01-11 3:00:00             1
2019-01-21 4:00:00             2
2019-02-01 0:00:00             1
2019-03-05 1:00:00             2
2019-03-21 3:00:00             1
2019-04-08 4:00:00             2

I am using the Pandas Grouper to group and sum the data monthly:

monthly_data = [pd.Grouper(freq='M', label='left')].sum()

Expected output:

2019-01-01 0:00:00             6
2019-02-01 0:00:00             1
2019-03-01 0:00:00             3
2019-04-01 0:00:00             2

Actual output:

2018-12-31 0:00:00             6
2019-01-31 0:00:00             1
2019-02-28 0:00:00             3
2019-03-30 0:00:00             2

How can I get the labels of the groups to be the first element in the group?

Thank you

Upvotes: 6

Views: 3469

Answers (2)

cs95
cs95

Reputation: 402413

Use resample to aggregate on DatetimeIndex:

df.resample('MS').sum()

            value
date             
2019-01-01      6
2019-02-01      1
2019-03-01      3
2019-04-01      2

Upvotes: 2

Andy Hayden
Andy Hayden

Reputation: 375475

Use the freq MS (Month Start), rather than M (Month End).

See dateoffset objects in the docs.

Upvotes: 14

Related Questions