如何在R语言DataFrame底部添加排除指定行的求和行？

问题需求 #

需要在DataFrame底部添加一行名为"Sum"的行，该行对除名为"Average"的行之外的所有行进行求和。

现有数据：

structure(list(newname = c("Manufacturing", "Hitech", "Healthcare", 
"Telecom", "Consumer Non-Durables", "Wholesale & Retail", "Average", 
"Utilities", "Services", "Mining", "Energy", "Consumer Durables", 
"Transportation", "ConstructionRealState"), Average_aqcflow2_1980 = c(0.0288997230442616, 
0.00473850847901422, 0.00488971102785823, 0.00840323152275126, 
0.0190959538907323, 0.0107096900100281, 0.00850912807485486, 
0.00308284189592453, 0.004017425488436, 0.00460641845272515, 
0.0114891264070224, 0.0064953691457694, 0.0035153434828485, 0.000675322125741407
), Average_aqcflow2_1990 = c(0.0269065869435805, 0.0135671840886106, 
0.0111340963669521, 0.0215104218890732, 0.0122076623454889, 0.0116918032736543, 
0.0104761191456682, 0.0163205965658905, 0.00819670938721455, 
0.00402856998096242, 0.00209418113434955, 0.00605775671618596, 
0.00198098445928488, 0.0004929957424395), Average_aqcflow2_2000 = c(0.0305917427040867, 
0.0314788731282408, 0.0197484512800162, 0.0157122610991569, 0.0133167049007056, 
0.0186446766062766, 0.01364500869393, 0.0133738286076718, 0.0104508681409246, 
0.0137874110635133, 0.00275844908745865, 0.00307277644221122, 
0.00347081132380885, 0.000978258637018894), Average_aqcflow2_2020 = c(0.0284892629360334, 
0.0324692474550213, 0.027609908946151, 0.0135453695473018, 0.0103109824448211, 
0.0112245533864386, 0.0117869672440038, 0.0101850560169536, 0.00672132360267508, 
0.00642733960610653, 0.00145444978278222, 0.00188825357237733, 
0.00225031095346272, 0.00065451592192456), Average_capxflow_1980 = c(0.643434694647758, 
0.666385956120257, 0.521821601960193, 0.664079228180719, 0.469371963236091, 
0.56521473192281, 0.715734653452937, 0.669312888488011, 0.761836358046314, 
0.916619246090449, 0.838674518995832, 0.727230791846755, 0.996424567878545, 
0.864143947474453), Average_capxflow_1990 = c(0.462312791721825, 
0.538458058854539, 0.38163873310658, 0.606260403731961, 0.320447407002078, 
0.580271668278576, 0.593077827558847, 0.511009223465175, 0.645892152746318, 
0.985223788192333, 0.777112339777178, 0.560573099681717, 0.887464318925823, 
0.453347772780902), Average_capxflow_2000 = c(0.312191039178838, 
0.345003024008933, 0.281109104604407, 0.49848807485155, 0.240777157227638, 
0.507584566582512, 0.492042373813665, 0.653762365929829, 0.584707714400257, 
0.885795369682392, 0.535837944019047, 0.759871888687288, 0.733697427206183, 
0.0577251831987745), Average_capxflow_2020 = c(0.26812620276161, 
0.230589287727291, 0.168840100993732, 0.367314776341145, 0.207082273753817, 
0.371645411190727, 0.592082324217154, 0.737529139398348, 0.486606395440387, 
0.00606496464207505, 0.710208188236444, 3.32804243167463, 0.62278162517513, 
0.192239417487673), Average_total_capxflow = c(0.427366078561936, 
0.446852757298321, 0.337064381128767, 0.530237963233748, 0.312798672555739, 
0.499445076760726, 0.610884432898405, 0.646195030225428, 0.617540074288915, 
0.641802198428096, 0.737107062382711, 1.51194209148066, 0.810271303545411, 
0.422874937788813), Average_total_aqcflow2 = c(0.0284824459575419, 
0.0198130749738682, 0.0159434489103164, 0.0146198428839008, 0.0136166531654961, 
0.012297650726387, 0.0108277645307654, 0.0103923542182047, 0.00693750973932877, 
0.00637231107392752, 0.00450914450380519, 0.00441630096526052, 
0.00269606140734965, 0.000664140374563966)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -14L))

当前尝试的代码：

library(janitor)
tablacash2 <- tablacash22 %>%
  adorn_totals("row")

解决方案 #

方法一：结合janitor和dplyr实现 #

adorn_totals没有直接排除指定行的参数，可先筛选掉"Average"行生成求和行，再合并回原数据：

library(janitor)
library(dplyr)

# 生成Sum求和行（仅对非Average行计算）
sum_row <- tablacash22 %>%
  filter(newname != "Average") %>%
  adorn_totals("row", name = "Sum") %>%
  slice_tail(n = 1)

# 合并原数据与求和行
tablacash2 <- bind_rows(tablacash22, sum_row)

方法二：用dplyr手动构建求和行 #

无需依赖janitor，直接计算并添加求和行，灵活性更高：

library(dplyr)

# 构建Sum行：对所有非Average行的数值列求和
sum_row <- tablacash22 %>%
  filter(newname != "Average") %>%
  summarise(
    newname = "Sum",
    across(where(is.numeric), sum, na.rm = TRUE)
  )

# 合并到原DataFrame
tablacash2 <- bind_rows(tablacash22, sum_row)

内容的提问来源于stack exchange，提问作者Ayoze Alfageme