CODEWITHSUNDEEP
pythonpandas
imantha
imantha

Reputation: 3838

Pandas How to group by month and year using dt

I am just wondering how to group by both year and month using pandas.series.dt.

The code below groups by just year, but how would I add a further filter to group by month as well. 

Data = {'Date':['21.10.1999','30.10.1999','02.11.1999','17.08.2000','09.10.2001','14.07.2000'],'X': [10,20,30,40,50,60],'Y': [5,10,15,20,25,30]}

df = pd.DataFrame(Data)

#Convert to pandas date time
df['Date'] = pd.to_datetime(df['Date'])

#Obtain dataframe dtypes
print(df.dtypes)

print(df)
print(df.groupby(df['Date'].dt.year).sum())

Upvotes: 2

Views: 11568

Answers (2)

jezrael
jezrael

Reputation: 863166

am just wondering how to group by both year and month using pandas.series.dt.

You can pass Series.dt.year and Series.dt.month with rename to groupby, new columns are not necessary:

print(df.groupby([df['Date'].dt.year.rename('y'), df['Date'].dt.month.rename('m')]).sum())
          X   Y
y    m         
1999 2   30  15
     10  30  15
2000 7   60  30
     8   40  20
2001 9   50  25

Another solutions:

If use DataFrame.resample or Grouper then are added all missing datetimes between (what should be nice or not):

print(df.resample('MS', on='Date').sum())

print(df.groupby(pd.Grouper(freq='MS', key='Date')).sum())

Or convert datetimes to month periods by Series.dt.to_period:

print(df.groupby(df['Date'].dt.to_period('m')).sum())
          X   Y
Date           
1999-02  30  15
1999-10  30  15
2000-07  60  30
2000-08  40  20
2001-09  50  25

Upvotes: 6

Alex
Alex

Reputation: 1126

df.assign(yr = df['Date'].dt.year, mnth = df['Date'].dt.month).groupby(['yr', 'mnth']).sum()

    Out[1]:

            X   Y
yr  mnth        
1999    2   30  15
        10  30  15
2000    7   60  30
        8   40  20
2001    9   50  25

Upvotes: 1

Related Questions