CODEWITHSUNDEEP
pythonpandasdataframe
Fred
Fred

Reputation: 240

How to group by month and year from a specific range?

The data have reported values for January 2006 through January 2019. I need to compute the total number of passengers Passenger_Count per month. The dataframe should have 121 entries (10 years * 12 months, plus 1 for january 2019). The range should go from 2009 to 2019.

The data

I have been doing:

df.groupby(['ReportPeriod'])['Passenger_Count'].sum()

But it doesn't give me the right result, it gives

Wrong result

Upvotes: 0

Views: 129

Answers (2)

WindCheck
WindCheck

Reputation: 426

Try this:

df.index = pd.to_datetime(df["ReportPeriod"], format="%m/%d/%Y")
df = df.groupby(pd.Grouper(freq="M")).sum()

Upvotes: 1

BENY
BENY

Reputation: 323226

You can do

df['ReportPeriod'] = pd.to_datetime(df['ReportPeriod'])
out = df.groupby(df['ReportPeriod'].dt.strftime('%Y-%m-%d'))['Passenger_Count'].sum()

Upvotes: 2

Related Questions