如何在R中为数据框添加‘Often’与‘Always’行求和的新行

我先通过以下代码生成了一个数据框：

User <-c('User_1','User_2','User_3','User_4','User_5')
Q1 <- c('Never', 'Rarely', 'Sometimes', 'Often', 'Always') 
Q2<- c('Sometimes', 'Rarely', 'Sometimes', 'Often', 'Rarely')
Q3 <- c('Always', 'Rarely', 'Sometimes', 'Always', 'Rarely')
data1 <- data.frame(User, Q1, Q2, Q3)

接着用下面的代码生成了各有序值的百分比数据框：

library(dplyr)
library(tidyr) # pivot_*
facs <- c("Never", "Rarely", "Sometimes", "Often", "Always")
data1 %>%
  pivot_longer(cols = -User) %>%
  mutate(value = factor(value, levels = facs)) %>%
  dplyr::count(name, value) %>%
  pivot_wider(
    id_cols = value, names_from = name, values_from = n,
    values_fill = 0L) %>%
  mutate(across(starts_with("Q"), ~ 100 * . / sum(.)))

得到的结果为：

# # A tibble: 5 × 4
#   value        Q1    Q2    Q3
#   <fct>     <dbl> <dbl> <dbl>
# 1 Never        20     0     0
# 2 Rarely       20    40    40
# 3 Sometimes    20    40    20
# 4 Often        20    20     0
# 5 Always       20     0    40

现在希望添加一行Super Often，该行是Often和Always两行的求和结果，最终效果如下：

#   value        Q1    Q2    Q3
#   <fct>     <dbl> <dbl> <dbl>
# 1 Never        20     0     0
# 2 Rarely       20    40    40
# 3 Sometimes    20    40    20
# 4 Often        20    20     0
# 5 Always       20     0    40
# 6 Super Often  40    20    40

这里提供两种简洁的实现方式：

方法一：直接计算后添加行 #

在原代码末尾使用add_row()函数，直接指定新行的取值（通过提取原数据框中Often和Always行的数值求和）：

library(dplyr)
library(tidyr)
facs <- c("Never", "Rarely", "Sometimes", "Often", "Always")

data1 %>%
  pivot_longer(cols = -User) %>%
  mutate(value = factor(value, levels = facs)) %>%
  dplyr::count(name, value) %>%
  pivot_wider(
    id_cols = value, names_from = name, values_from = n,
    values_fill = 0L) %>%
  mutate(across(starts_with("Q"), ~ 100 * . / sum(.))) %>%
  add_row(
    value = factor("Super Often", levels = c(facs, "Super Often")),
    Q1 = .$Q1[.$value == "Often"] + .$Q1[.$value == "Always"],
    Q2 = .$Q2[.$value == "Often"] + .$Q2[.$value == "Always"],
    Q3 = .$Q3[.$value == "Often"] + .$Q3[.$value == "Always"]
  )

方法二：先求和再合并 #

先生成原百分比数据框，再筛选出需要求和的行计算新行，最后用bind_rows()合并：

library(dplyr)
library(tidyr)
facs <- c("Never", "Rarely", "Sometimes", "Often", "Always")

# 生成原百分比数据框
percent_df <- data1 %>%
  pivot_longer(cols = -User) %>%
  mutate(value = factor(value, levels = facs)) %>%
  dplyr::count(name, value) %>%
  pivot_wider(
    id_cols = value, names_from = name, values_from = n,
    values_fill = 0L) %>%
  mutate(across(starts_with("Q"), ~ 100 * . / sum(.)))

# 计算Super Often行
super_often <- percent_df %>%
  filter(value %in% c("Often", "Always")) %>%
  summarise(
    value = factor("Super Often", levels = c(facs, "Super Often")),
    across(starts_with("Q"), sum)
  )

# 合并数据框得到最终结果
final_df <- bind_rows(percent_df, super_often)
print(final_df)

两种方法都能得到目标结果，方法二更灵活，后续若需调整求和类别，仅需修改filter中的条件即可。

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