如何在R语言中计算数据框内行值的占比贡献

问题背景 #

需要计算数据框内行内各值的占比贡献，使用的数据框结构如下：

structure(list(`Row Labels` = c("X1", "X2", "X3", "X4"), `2019-01-01` = c(37, 
36, 45, 53), `2019-02-01` = c(3, 19, 14, 46), `2019-03-01` = c(28, 
2, 28, 28), `2019-04-01` = c(48, 70, 18, 16), `2019-05-01` = c(83, 
71, 58, 26), `2019-06-01` = c(85, 28, 83, 46), `2019-07-01` = c(60, 
20, 12, 77), `2019-08-01` = c(44, 66, 30, 99), `2019-09-01` = c(21, 
14, 31, 21), `2019-10-01` = c(26, 72, 72, 16), `2019-11-01` = c(15, 
96, 23, 100), `2019-12-01` = c(65, 0, 98, 66)), row.names = c(NA, 
-4L), class = c("tbl_df", "tbl", "data.frame"))

编写的代码如下：

Book1 <- read_excel("X:/X/X/X - X/X/Book1.xlsx")

First_Date <- "2019-01-01"
Last_Date <- "2019-12-01"

Book1 <- Book1 %>% 
  mutate(Sum = rowSums(pick(any_of(First_Date):any_of(Last_Date)))) %>% 
  mutate(across(pick(any_of(First_Date):any_of(Last_Date), ~./rowSums(pick(any_of(First_Date):any_of(Last_Date)))),.names = "{.col}_%"))

运行后出现错误：

Error in `mutate()`: ℹ In argument: `across(...)`. Caused by error in `pick()`: ! Formula shorthand must be wrapped in `where()`.

  # Bad   data %>% select(~./rowSums(pick(any_of(First_Date):any_of(Last_Date))))

  # Good   data %>% select(where(~./rowSums(pick(any_of(First_Date):any_of(Last_Date)))))

错误原因 #

你的代码错误出在第二个mutate的across参数里：

• pick()函数仅用于选择列，不支持直接传入公式（~./...）作为参数，你错误地把公式写在了pick()的括号内，导致语法报错。
• 重复调用rowSums(pick(...))会重复计算每行的和，既冗余又降低效率，不如直接复用已经计算好的Sum列。

修正后的代码 #

先计算每行的总和Sum，再在across中直接引用该列计算占比：

Book1 <- read_excel("X:/X/X/X - X/X/Book1.xlsx")

First_Date <- "2019-01-01"
Last_Date <- "2019-12-01"

# 定义日期列范围，避免重复书写
date_cols <- any_of(First_Date):any_of(Last_Date)

Book1 <- Book1 %>% 
  mutate(Sum = rowSums(pick(date_cols))) %>% 
  mutate(across(date_cols, ~ .x / Sum, .names = "{.col}_%"))

更简便的实现方法 #

如果不需要保留Sum列，可直接用c_across在across中计算每行的和，一步完成占比计算：

Book1 <- read_excel("X:/X/X/X - X/X/Book1.xlsx")

First_Date <- "2019-01-01"
Last_Date <- "2019-12-01"

Book1 <- Book1 %>% 
  mutate(across(any_of(First_Date):any_of(Last_Date), 
                ~ .x / rowSums(c_across(any_of(First_Date):any_of(Last_Date))), 
                .names = "{.col}_%"))

如果所有日期列都是数值型，还可以用where简化列选择：

Book1 <- Book1 %>% 
  mutate(across(where(is.numeric), 
                ~ .x / rowSums(c_across(where(is.numeric))), 
                .names = "{.col}_%"))

内容的提问来源于stack exchange，提问作者Mr Pool