Skip to main content
 首页 » 编程设计

python之Pandas 在 DataFrame 中插入行小计

2025年01月19日9傻小

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

查看编辑后的代码:

  adsize_sales_data = adsize_sales_second_table.loc[:, ["Placement# Name", "Adsize", "Delivered Impressions", "Clicks", 
                                                              "CTR", "Conversion", "Conversion Rate", "Spend", "eCPA"]] 
 
 
        cols = ["Delivered Impressions", "Clicks", "Conversion", "Spend"] 
 
        adsize_sales_data['Placement# Name'] = adsize_sales_data['Placement# Name'].ffill() 
        grand = adsize_sales_data[cols].sum() 
        grand.loc['Placement# Name'] = 'Grand total' 
 
        adsize_sales_data_new = adsize_sales_data.groupby('Placement# Name')[cols].sum() 
 
        adsize_sales_data_new.index = adsize_sales_data.index.astype(str)+'____' 
 
        adsize_sales_data = (pd.concat([adsize_sales_data.set_index('Placement# Name'), adsize_sales_data_new], keys=('a', 'b')).sort_index(level=1).reset_index()) 
 
        adsize_sales_data['Placement# Name'] = np.where(adsize_sales_data ['level_0'] == 'a', adsize_sales_data['Placement# Name'], 'Total') 
 
        adsize_sales_data = adsize_sales_data.drop('level_0', axis=1) 
 
        adsize_sales_data.loc[len(adsize_sales_data.index)] = grand 
 
        print (adsize_sales_data) 

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

请您参考如下方法:

使用:

#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 

print (df) 
                                      Placement# Name   Adsize          CTR  \ 
0   13.iab units (mobile only) + non-expanding adh...   320x50    0.0119683    
1                                               Total      NaN          NaN    
2   15.iab units (mobile only) + non-expanding adh...   320x50    0.0138741    
3   15.iab units (mobile only) + non-expanding adh...   768x90    0.0271041    
4                                               Total      NaN          NaN    
5                     18.iab units - desktop + mobile  160x600   0.00155927    
6                     18.iab units - desktop + mobile  300x250   0.00797965    
7                     18.iab units - desktop + mobile  300x600   0.00275059    
8                     18.iab units - desktop + mobile   728x90   0.00496391    
9                                               Total      NaN          NaN    
10  4.iab units (mobile only) + non-expanding adhe...   320x50    0.0141497    
11                                              Total      NaN          NaN    
12  5.iab units (mobile only) + non-expanding adhe...   320x50    0.0111654    
13  5.iab units (mobile only) + non-expanding adhe...   768x90    0.0253428    
14                                              Total      NaN          NaN    
15                     6.iab units - desktop + mobile  160x600  7.41895e-05    
16                     6.iab units - desktop + mobile  300x250     0.011838    
17                     6.iab units - desktop + mobile  300x600  0.000259538    
18                     6.iab units - desktop + mobile   728x90   0.00538178    
19                                              Total      NaN          NaN    
20                                        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    2121         17     0.000111202                152875  1091.96  64.2332   
3     152          2     0.000356633                  5608  40.0571  20.0286   
4    2273         19             NaN                158483  1132.02      NaN   
5      37         21     0.000884993                 23729  132.204  6.29545   
6     684         58     0.000676637                 85718  477.572    8.234   
7      34         13      0.00105169                 12361  68.8684  5.29757   
8     403         80     0.000985392                 81186  452.322  5.65403   
9    1158        172             NaN                202994  1130.97      NaN   
10   3840         23     8.47511e-05                271383  1938.45  84.2804   
11   3840         23             NaN                271383  1938.45      NaN   
12   1127          4     3.96287e-05                100937  720.979  180.245   
13    183          0               0                  7221  51.5786        0   
14   1310          4             NaN                108158  772.557      NaN   
15      1          0               0                 13479  75.0973        0   
16    792          0               0                 66903  372.745        0   
17      1          0               0                  3853  21.4667        0   
18    266          0               0                 49426  275.373        0   
19   1060          0             NaN                133661  744.683      NaN   
20  11529        222             NaN           1.03243e+06  6845.46      NaN   
 
#if necessary write to file 
df.to_excel('file.xlsx', index=False) 

编辑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