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
If even one of the cells contains invalid data, the grouping feature will not be enabled. Pivot Table won't allow you to group dates and you will get a cannot group that selection error.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With