如何在 Pandas 数据透视表查询中创建包含日期的字典？

Question

我有一个如下所示的数据框：- 

我需要创建一个 Pandas 数据透视表，它将输出如下表：

也就是说，它将所有小于 20 年 10 月 1 日的日期汇总为逾期，然后正常汇总从 20 年 10 月 1 日起的所有日期。

下面的代码是我到目前为止所想出的。

#!/usr/bin/env python3

import pandas as pd

import numpy as np

# creating a data frame 

df = pd.read_csv("CSVData2.csv") 

table = pd.pivot_table(data=df,index=['Code'], columns=['Process Month'], values = ['Number'], aggfunc=sum)

print(table)

Answer 1

也许这对你有用？# Recreating your dataframe in codedf = pd.DataFrame({'Code':'A1 P2 B3 B3 C4 A1 B3 A1 A1'.split(' '),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Branch':'UW2 RQ2 UW2 UW2 X01 X01 DN9 PE7 PE7'.split(' '),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Process Month':'01-Oct-20 01-Nov-20 01-Sep-20 01-Sep-20 01-Aug-20 01-Oct-20 01-Sep-20 01-Dec-20 01-Sep-20'.split(' '),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Number':[1]*9})#Change string to datetime dtypedf['Process Month'] = pd.to_datetime(df['Process Month'])# Create mask to defined 'Overdue'&nbsp; &nbsp;&nbsp;m = df['Process Month'] < '01-Oct-20'# Output Process Month back as stringdf['Process Month'] = df['Process Month'].dt.strftime('%d-%b-%Y')# Overwriting Process Month with 'OverDue' per mask abovedf.loc[m, 'Process Month'] = 'OverDue'# Creating a crosstab with totalsdf_out = pd.crosstab(df['Code'], df['Process Month'], margins=True, margins_name='Total')df_out.drop('Total', axis=1) #Don't need row Totals column输出：Process Month&nbsp; 01-Dec-2020&nbsp; 01-Nov-2020&nbsp; 01-Oct-2020&nbsp; OverDueCode&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; 1B3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; 3C4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; 1P2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; 0Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; 5