按年和月分组Pandas Pivot选项卡

Question

我有这样的数据

    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

我创建了这样的数据透视表：

^{pr2}$

但是我需要日期列按年和月分组。我正在寻找其他解决方案，重新采样数据帧，然后应用枢轴，但它只做了一个月和几天。任何帮助都将不胜感激

Answer 1

您可以将日期列转换为%Y-%m，然后执行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