嵌套for循环以基于熊猫中的2级多索引创建多个数据透视表

开始对此感到困惑。我有一个很大的事实发票表。我采用原始数据帧,使用groupby根据一列拆分df。输出是数据帧列表:

list_of_dfs = []
for _, g in df.groupby(df['Project State Name']):
    list_of_dfs.append(g)
list_of_dfs

然后,我使用另一个for循环遍历数据帧列表并执行一个数据透视表聚合。

for each_state_df in list_of_dfs:
    columns_to_index_by = ['Project Issue', 'Project Secondary Issue', 'Project Client Name']
# Aggregating to the Project Level
    table_for_pivots = pd.pivot_table(df, index=['FY Year', 'Project Issue'], values=["Project Key", 'Total Net Amount', "Project Total Resolution Amount", 'Project Budgeted Amount'],
                             aggfunc= {"Project Key": lambda x: len(x.unique()), 'Total Net Amount': np.sum,  "Project Total Resolution Amount": np.mean,
                                       'Project Budgeted Amount': np.mean},
                             fill_value=np.mean)
print(table_for_pivots)

我的问题是,如何使用另一个for循环将数据透视表索引中的第二个元素替换为变量columns_to_index_by中的每个值?输出将是3个数据透视表,其中index = ['FY Year','Project Issue'],index = ['FY Year','Project Secondary Issue',和index = ['FY Year','Project Client Name' ]。谢谢大家!

下载样本df数据的链接在这里:

https://ufile.io/iufv9nma

评论