在迭代过程中对拆分的数据帧列求和

Question

我有一个数据帧“fpd”，它在列['View']中使用

bookuniques = fpd['View'].unique()

fpdict = {elem: pd.DataFrame for elem in bookuniques}

for key in fpdict.keys():
    fpdict[key] = fpd[:][fpd['View'] == key]

数据帧看起来像：

    Product PG Location Row Group   Ph DD                   Pd TC   Variance    
    C4      CL          01.1 OI     OpeningInventory        200     200     
            PU          01.1 OI     OpeningInventory        400     400
            MR          01.1 OI     OpeningInventory        600     600 
            NP          01.1 OI     OpeningInventory        200     200
            PR          01.1 OI     OpeningInventory        400     400 
            PS          01.1 OI     OpeningInventory        600     600 
            PW          01.1 OI     OpeningInventory        200     200 

我试图为每个数据帧单独附加一个sum行。我尝试过在输出到excel的过程中使用

with pd.ExcelWriter('check2.xlsx') as writer:
    for key in fpdict.keys():
        fpdict[key].drop(['View'], axis = 1) 
        fpdict[key].append(fpdict[key].sum(numeric_only = True), ignore_index=True)
        temp = fpdict[key]
        temp.to_excel(writer, sheet_name = key)

不幸的是，这样做会删除索引列[['Product'],['PG'],['Location']]

我希望输出是

        Product PG Location Row Group   Ph DD                   Pd TC   Variance    
        C4      CL          01.1 OI     OpeningInventory        200     200     
                PU          01.1 OI     OpeningInventory        400     400
                MR          01.1 OI     OpeningInventory        600     600 
                NP          01.1 OI     OpeningInventory        200     200
                PR          01.1 OI     OpeningInventory        400     400 
                PS          01.1 OI     OpeningInventory        600     600 
                PW          01.1 OI     OpeningInventory        200     200
                TOTAL                                           2600    2600    

Answer 1

以下是我必须做的假设，因为这在问题中没有明确说明：

• dataframe在Product、PG和Location列上有一个多索引
• 新行将PG=Total和所有其他非数字字段设置为空字符串
• fpdict[key]将删除View列

您必须将代码更改为：

with pd.ExcelWriter('check2.xlsx') as writer:
    for key in fpdict.keys():
        temp = fpdict[key].drop(['View'], axis = 1).reset_index()
        temp.append(fpdict[key].sum(numeric_only = True), ignore_index=True) # add sum row
        temp.iloc[-1] = temp.iloc[-1].fillna(' ')      # replace NaNs with ''
        temp.iloc[-1, 1] = 'TOTAL'
        fpdict[key] = temp.set_index(['Product', 'PG', 'Location'])
        temp.to_excel(writer, sheet_name = key)