如何循环计算多列表变量的nrow值并保存为DataFrame

我需要从Excel/CSV文件提取数据，统计每位员工在各办公室完成的各类预约数量。目前我靠手动编写重复代码实现，示例如下：

staff = c("smith", "jones", "carter")
office = c("toronto", "oakville", "ottawa")
appointment.type = c("initial", "initial2", "followup")

smith_initial_to = (nrow(appointmentdata[staff == "smith" & office == "toronto" 
   & appointment.type == "initial",])

smith_initial_oak = (nrow(appointmentdata[staff == "smith" & office == "oakville" 
   & appointment.type == "initial",])

smith_initial_ott = (nrow(appointmentdata[staff == "smith" & office == "ottawa" 
   & appointment.type == "initial",])

jones_initial_to = (nrow(appointmentdata[staff == "jones" & office == "toronto" 
   & appointment.type == "initial",])

jones_initial_oak = (nrow(appointmentdata[staff == "jones" & office == "oakville" 
   & appointment.type == "initial",])

jones_initial_ott = (nrow(appointmentdata[staff == "jones" & office == "ottawa" 
   & appointment.type == "initial",])

# 更多重复代码...

df = data.frame(Name = c("smith", "jones", "carter"), TorontoInitial = 
   c(smith_intitial_to, jones_intitial_to, carter_initial_to), 
   OakvilleInitial = c(smith_initial_oak, jones_initial_oak, 
   carter_initial_oak), OttawaInitial = c(smith_initial_ott, 
   jones_initial_ott, carter_initial_ott)) 

我想通过循环实现员工、办公室、预约类型的批量统计，把每次循环结果存为独立变量，最后汇总成一个大DataFrame，但试了几次循环代码都没生效。之前都是手动重复写代码、逐个变量拼接DataFrame，现在求循环实现的方案。

附样本数据：

structure(list(Client.Code = 1:20, Office = c("TORONTO", "TORONTO", 
"TORONTO", "OAKVILLE", "OAKVILLE", "TORONTO", "TORONTO", "TORONTO", 
"TORONTO", "TORONTO", "TORONTO", "TORONTO", "TORONTO", "TORONTO", 
"TORONTO", "OTTAWA", "OTTAWA", "OTTAWA", "OAKVILLE", "OAKVILLE"
), Staff = c("SMITH", "SMITH", "SMITH", "SMITH", "SMITH", "JONES", 
"JONES", "JONES", "JONES", "JONES", "JONES", "JONES", "CARTER", 
"CARTER", "CARTER", "CARTER", "CARTER", "CARTER", "CARTER", "CARTER"
), Appointment.Type = c("INITIAL", "INITIAL", "INITIAL2", "INITIAL", 
"INTIAL2", "INTIAL", "FOLLOWUP", "FOLLOWUP", "INITIAL", "FOLLOWUP", 
"FOLLOWUP", "INITIAL", "INITIAL2", "INITIAL2", "INITIAL", "INITIAL", 
"INITIAL", "FOLLOWUP", "INITIAL", "INITIAL")), row.names = c(NA, 
20L), class = "data.frame")

方法1：基础R循环实现（满足批量生成变量需求） #

首先处理样本数据的大小写不一致、拼写错误问题，避免匹配失败：

# 加载样本数据
appointmentdata <- structure(list(Client.Code = 1:20, Office = c("TORONTO", "TORONTO", 
"TORONTO", "OAKVILLE", "OAKVILLE", "TORONTO", "TORONTO", "TORONTO", 
"TORONTO", "TORONTO", "TORONTO", "TORONTO", "TORONTO", "TORONTO", 
"TORONTO", "OTTAWA", "OTTAWA", "OTTAWA", "OAKVILLE", "OAKVILLE"
), Staff = c("SMITH", "SMITH", "SMITH", "SMITH", "SMITH", "JONES", 
"JONES", "JONES", "JONES", "JONES", "JONES", "JONES", "CARTER", 
"CARTER", "CARTER", "CARTER", "CARTER", "CARTER", "CARTER", "CARTER"
), Appointment.Type = c("INITIAL", "INITIAL", "INITIAL2", "INITIAL", 
"INTIAL2", "INTIAL", "FOLLOWUP", "FOLLOWUP", "INITIAL", "FOLLOWUP", 
"FOLLOWUP", "INITIAL", "INITIAL2", "INITIAL2", "INITIAL", "INITIAL", 
"INITIAL", "FOLLOWUP", "INITIAL", "INITIAL")), row.names = c(NA, 
20L), class = "data.frame")

# 统一大小写，修正拼写错误（样本中的INTIAL是笔误）
appointmentdata$Staff <- tolower(appointmentdata$Staff)
appointmentdata$Office <- tolower(appointmentdata$Office)
appointmentdata$Appointment.Type <- tolower(appointmentdata$Appointment.Type)
appointmentdata$Appointment.Type <- gsub("intial", "initial", appointmentdata$Appointment.Type)

# 定义需要遍历的维度列表
staff_list <- c("smith", "jones", "carter")
office_list <- c("toronto", "oakville", "ottawa")
type_list <- c("initial", "initial2", "followup")

# 创建空列表存储结果，同时生成独立变量
result_list <- list()
for (s in staff_list) {
  for (o in office_list) {
    for (t in type_list) {
      # 统计符合条件的行数
      count <- nrow(appointmentdata[appointmentdata$Staff == s & 
                                      appointmentdata$Office == o & 
                                      appointmentdata$Appointment.Type == t, ])
      # 生成变量名（如smith_toronto_initial）
      var_name <- paste(s, o, t, sep = "_")
      # 将结果存入全局环境（生成独立变量）
      assign(var_name, count, envir = .GlobalEnv)
      # 存入结果列表用于后续汇总
      result_list[[var_name]] <- count
    }
  }
}

# 将结果整理为目标格式的DataFrame
library(tidyr)
result_df <- data.frame(
  variable = names(result_list),
  count = unlist(result_list)
) %>%
  separate(variable, into = c("Name", "Office", "Type"), sep = "_") %>%
  pivot_wider(names_from = c("Office", "Type"), values_from = "count", 
              names_glue = "{toupper(substr(Office, 1, 1))}{substr(Office, 2, nchar(Office))}{toupper(substr(Type, 1, 1))}{substr(Type, 2, nchar(Type))}")

print(result_df)

运行后会自动生成所有类似smith_toronto_initial的独立变量，同时输出汇总后的宽格式DataFrame，和你手动拼接的格式一致。

方法2：dplyr快速实现（无需循环，更高效） #

如果不需要单独生成变量，仅需汇总统计结果，用tidyverse工具更简洁易维护：

library(dplyr)
library(tidyr)

# 数据预处理同上
appointmentdata$Staff <- tolower(appointmentdata$Staff)
appointmentdata$Office <- tolower(appointmentdata$Office)
appointmentdata$Appointment.Type <- tolower(appointmentdata$Appointment.Type)
appointmentdata$Appointment.Type <- gsub("intial", "initial", appointmentdata$Appointment.Type)

# 分组统计+转宽格式
summary_df <- appointmentdata %>%
  group_by(Staff, Office, Appointment.Type) %>%
  summarise(Count = n(), .groups = "drop") %>%
  pivot_wider(names_from = c(Office, Appointment.Type), 
              values_from = Count,
              names_glue = "{toupper(substr(Office, 1, 1))}{substr(Office, 2, nchar(Office))}{toupper(substr(Appointment.Type, 1, 1))}{substr(Appointment.Type, 2, nchar(Appointment.Type))}",
              values_fill = 0) %>%
  rename(Name = Staff)

print(summary_df)

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