如何在Python中对数据帧进行子集、分组并将数据帧导出为一个excel文件的多个表

Python新手在这里

在下面的数据集中：


import pandas as pd
import numpy as np
data = {'Gender':['M','M','M','M','F','F','F','F','M','M','M','M','F','F','F','F'],
        'Location':['NE','NE','NE','NE','SW','SW','SW','SW','SE','SE','SE','SE','NC','NC','NC','NC'],
        'Type':['L','L','L','L','L','L','L','L','R','R','R','R','R','R','R','R'],
         'PDP':['<10','<10','<10','<10',10,10,10,10,20,20,20,20,'>20','>20','>20','>20'],
         'PDP_code':[1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4],
     'diff':[-1,-1,-1,-1,0,0,0,0,1,1,1,1,3,3,3,3],
     'series':[1,2,4,8,1,2,4,8,1,2,4,8,1,2,4,8],
    'Revenue_YR1':[1150.78,1162.34,1188.53,1197.69,2108.07,2117.76,2129.48,1319.51,1416.87,1812.54,1819.57,1991.97,2219.28,2414.73,2169.91,2149.19],
     'Revenue_YR2':[250.78,262.34,288.53,297.69,308.07,317.7,329.81,339.15,346.87,382.54,369.59,399.97,329.28,347.73,369.91,349.12],
     'Revenue_YR3':[240.18,232.14,258.53,276.69,338.07,307.74,359.16,339.25,365.87,392.48,399.97,410.75,429.08,448.39,465.15,469.33],
     'Revenue_YR4':[270.84,282.14,298.53,306.69,318.73,327.47,369.63,389.59,398.75,432.18,449.78,473.55,494.85,509.39,515.52,539.23],
      'Revenue_YR5':[251.78,221.34,282.53,272.69,310.07,317.7,329.81,333.15,334.87,332.54,336.59,339.97,329.28,334.73,336.91,334.12],
     'Revenue_YR6':[240.18,232.14,258.53,276.69,338.07,307.74,359.16,339.25,365.87,392.48,399.97,410.75,429.08,448.39,465.15,469.33],
     'Revenue_YR7':[27.84,28.14,29.53,30.69,18.73,27.47,36.63,38.59,38.75,24.18,24.78,21.55,13.85,9.39,15.52,39.23],
      'Revenue_YR8':[279.84,289.14,299.53,309.69,318.73,327.47,336.63,398.59,398.75,324.18,324.78,321.55,333.85,339.39,315.52,319.23],
}
df = pd.DataFrame(data,columns = ['Gender','Location','Type','PDP','PDP_code','diff','series',
                                'Revenue_YR1','Revenue_YR2','Revenue_YR3','Revenue_YR4','Revenue_YR5','Revenue_YR6',
                                'Revenue_YR7','Revenue_YR8'])
df.head(5)

我想要一种蟒蛇般的方式来做以下事情：

基于唯一的Location将df子集划分为4个dataframes / lists，得到NE、SW、SE&NC数据帧
聚合所有Revenue_YR列，而GroupBy series和PDP_code列，并将所有聚合的数据帧(NE、SW、SE和NC(导出为一个xlsx文件的多张

我的尝试

### this code returns output of 1 df instead of 4 dfs, I need help aggregating each of the 4 dataframes and export them to 4 sheets of 12312021_output.xlsx
for i, part_df in df.groupby('Location'): 
    part_df.groupby(['series','PDP_code'])[['Revenue_YR1', 'Revenue_YR2','Revenue_YR3', 
        'Revenue_YR4', 'Revenue_YR5', 'Revenue_YR6', 'Revenue_YR7']].mean().unstack().style.background_gradient(cmap='Blues').to_excel('12312021_output.xlsx')

请分享您的代码。

您可以使用pandas.ExcelWriter和您的循环(为了可读性，我稍微改进了一下(：

import pandas as pd
with pd.ExcelWriter("output.xlsx") as writer:
    cols = df.filter(like='Revenue_YR').columns
    
    for g, d in df.groupby('Location'):
        (d.groupby(['series','PDP_code'])[cols].mean().unstack()
          .style.background_gradient(cmap='Blues')
         ).to_excel(writer, sheet_name=g)

相关内容

最新更新

热门标签：