CODEWITHSUNDEEP
pythonpython-3.xpandas
Huzaifa M Aamir
Huzaifa M Aamir

Reputation: 383

Group by Year and Month Panda Pivot Table

I have data like this 

    Date    LoanOfficer     User_Name       Loan_Number
0   2017-11-30 00:00:00 Mark Evans      underwriterx    1100000293
1   2017-11-30 00:00:00 Kimberly White  underwritery    1100004947
2   2017-11-30 00:00:00 DClair Phillips underwriterz    1100007224

I've created df pivot table like this:

pd.pivot_table(df,index=["User_Name","LoanOfficer"],
               values=["Loan_Number"],
               aggfunc='count',fill_value=0,
               columns=["Date"]
                      )

However I need the Date column to be grouped by Year and Month. I was looking at other solutions of resampling the dataframe and then applying the pivot but it only does it for Month and Days. Any help would be appreciated

Upvotes: 6

Views: 18413

Answers (1)

BENY
BENY

Reputation: 323306

You can convert you Date column to %Y-%m , then do the pivot_table

df.Date=pd.to_datetime(df.Date)
df.Date=df.Date.dt.strftime('%Y-%m')
df
Out[143]: 
      Date      LoanOfficer     User_Name  Loan_Number
0  2017-11       Mark Evans  underwriterx   1100000293
1  2017-11   Kimberly White  underwritery   1100004947
2  2017-11  DClair Phillips  underwriterz   1100007224

pd.pivot_table(df,index=["User_Name","LoanOfficer"],
               values=["Loan_Number"],
               aggfunc='count',fill_value=0,
               columns=["Date"]
                      )
Out[144]: 
                             Loan_Number
Date                             2017-11
User_Name    LoanOfficer                
underwriterx Mark Evans                1
underwritery Kimberly White            1
underwriterz DClair Phillips           1

Upvotes: 13

Related Questions