按年和月分组的Panda数据透视表

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

我已经创建了如下的数据透视表：

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

然而，我需要按年份和月份对日期列进行分组。我看了其他重新采样数据框架并应用枢轴的解决方案，但它仅适用于月份和天数。任何帮助将不胜感激。

- Huzaifa M Aamir

Answer 1

你可以将日期列转换为%Y-%m格式，然后使用进行操作。

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

- BENY