Pandas在DataFram中插入行小计

Question

enter image description here目标： 我有一个pandas数据框，如下图所示，其中有多个列，我希望获得少数列的小计，并在Placement#Name处写下“Total”。在

数据帧：enter image description here

我的尝试：

**adsize_sales_second_table.loc["Grand Total"] = pd.Series(adsize_sales_second_table.loc
                                                             [:, ["Delivered Impressions",
                                                                  "Clicks",
                                                                  "Conversion", "Spend"]].sum(),
                                                    index=["Delivered Impressions", "Clicks", "Conversion", "Spend"]**
                                                             )

这是在最后添加一行，无法计算以填充小计： enter image description here

预期输出： 我本以为输出如下 enter image description here

查看编辑过的代码：

^{pr2}$

它现在给出的值错误。enter image description here

Answer 1

使用：

#specify columns to sum
cols = ["Delivered Impressions", "Clicks", "Conversion", "Spend"]
#replace NaNs by forward filling
df['Placement# Name'] = df['Placement# Name'].ffill()
#count grand total
grand = df[cols].sum()
grand.loc['Placement# Name'] = 'Grand total'
print (grand)

#get subtotal by aggregation sum 
df1 = df.groupby('Placement# Name')[cols].sum()
#change sum for correct order
df1.index = df1.index + '____'
#join to original, sort by second level of MultiIndex
df = (pd.concat([df.set_index('Placement# Name'), df1], keys=('a', 'b'))
        .sort_index(level=1)
        .reset_index())

#change values to total
df['Placement# Name'] = np.where(df['level_0'] == 'a', df['Placement# Name'], 'Total')
#remove column
df = df.drop('level_0', axis=1)
#add grand total
df.loc[len(df.index)] = grand

---

^{pr2}$

编辑1：

cols = ["Delivered Impressions", "Clicks", "Conversion", "Spend"]

df['Placement# Name'] = df['Placement# Name'].ffill()
grand = df[cols].sum()
grand.loc['Placement# Name'] = 'Grand total'
print (grand)

df1 = df.groupby('Placement# Name')[cols].sum()
df1.index = df1.index + '____'

#create empty DataFrame
df2 = pd.DataFrame(index=df1.index + '__')
df = pd.concat([df.set_index('Placement# Name'), df1, df2], keys=('a', 'b', 'c')).sort_index(level=1).reset_index()

#get output by 2 conditions
m1 = df['level_0'] == 'a'
m2 = df['level_0'] == 'c'
df['Placement# Name'] = np.select([m1, m2], [df['Placement# Name'], np.nan], default='Total')
df = df.drop('level_0', axis=1)
df.loc[len(df.index)] = grand

---

print (df)
0   13.iab units (mobile only) + non-expanding adh...   320x50    0.0119683   
1                                               Total      NaN          NaN   
2                                                 NaN      NaN          NaN   
3   15.iab units (mobile only) + non-expanding adh...   320x50    0.0138741   
4   15.iab units (mobile only) + non-expanding adh...   768x90    0.0271041   
5                                               Total      NaN          NaN   
6                                                 NaN      NaN          NaN   
7                     18.iab units - desktop + mobile  160x600   0.00155927   
8                     18.iab units - desktop + mobile  300x250   0.00797965   
9                     18.iab units - desktop + mobile  300x600   0.00275059   
10                    18.iab units - desktop + mobile   728x90   0.00496391   
11                                              Total      NaN          NaN   
12                                                NaN      NaN          NaN   
13  4.iab units (mobile only) + non-expanding adhe...   320x50    0.0141497   
14                                              Total      NaN          NaN   
15                                                NaN      NaN          NaN   
16  5.iab units (mobile only) + non-expanding adhe...   320x50    0.0111654   
17  5.iab units (mobile only) + non-expanding adhe...   768x90    0.0253428   
18                                              Total      NaN          NaN   
19                                                NaN      NaN          NaN   
20                     6.iab units - desktop + mobile  160x600  7.41895e-05   
21                     6.iab units - desktop + mobile  300x250     0.011838   
22                     6.iab units - desktop + mobile  300x600  0.000259538   
23                     6.iab units - desktop + mobile   728x90   0.00538178   
24                                              Total      NaN          NaN   
25                                                NaN      NaN          NaN   
26                                        Grand total      NaN          NaN   

   Clicks Conversion Conversion Rate Delivered Impressions    Spend     eCPA  
0    1888          4     2.53566e-05                157750  1126.79  281.696  
1    1888          4             NaN                157750  1126.79      NaN  
2     NaN        NaN             NaN                   NaN      NaN      NaN  
3    2121         17     0.000111202                152875  1091.96  64.2332  
4     152          2     0.000356633                  5608  40.0571  20.0286  
5    2273         19             NaN                158483  1132.02      NaN  
6     NaN        NaN             NaN                   NaN      NaN      NaN  
7      37         21     0.000884993                 23729  132.204  6.29545  
8     684         58     0.000676637                 85718  477.572    8.234  
9      34         13      0.00105169                 12361  68.8684  5.29757  
10    403         80     0.000985392                 81186  452.322  5.65403  
11   1158        172             NaN                202994  1130.97      NaN  
12    NaN        NaN             NaN                   NaN      NaN      NaN  
13   3840         23     8.47511e-05                271383  1938.45  84.2804  
14   3840         23             NaN                271383  1938.45      NaN  
15    NaN        NaN             NaN                   NaN      NaN      NaN  
16   1127          4     3.96287e-05                100937  720.979  180.245  
17    183          0               0                  7221  51.5786        0  
18   1310          4             NaN                108158  772.557      NaN  
19    NaN        NaN             NaN                   NaN      NaN      NaN  
20      1          0               0                 13479  75.0973        0  
21    792          0               0                 66903  372.745        0  
22      1          0               0                  3853  21.4667        0  
23    266          0               0                 49426  275.373        0  
24   1060          0             NaN                133661  744.683      NaN  
25    NaN        NaN             NaN                   NaN      NaN      NaN  
26  11529        222             NaN           1.03243e+06  6845.46      NaN