如何将R语言合并生成的表格保存为CSV？表格未存入环境无法转数据框

嘿，我来帮你搞定这个数据处理的问题！你当前的核心问题有两个：一是生成的目标数据框没赋值到变量里，没法保存；二是重复调用dcast有点繁琐，我们可以用更高效的方式实现。

先看你的原始代码与问题点 #

1. 数据准备 #

library(reshape2)
Customer <- c("Susan","Louis", "Frank","Susan")
Seller <- c("Ivan", "Donald","Chris","Ivan")
Service <- c("COU","CAR", "FCL","CAR")
Billingmean <- c(100,200,300,400)
WrsHoldSum <- c(0,0,0,0)
Group <- c("n1","n2"," "," ")
B1 <- c(0,2,2,1)
B2 <- c(9,8,7,6)
B3 <- c(5,4,3,2)

df <- data.frame(Customer, Seller,Service, Billingmean,WrsHoldSum, Group,B1,B2,B3)

该数据框包含去年销售额的平均账单、销售员、服务类型等信息

2. 多次调用dcast生成子表 #

sub1 <- dcast(data= df, formula= Customer+Group+Seller+WrsHoldSum~Service,fun.aggregate= sum,value.var= "Billingmean")
sub2 <- dcast(data= df, formula= Customer+Group+Seller+WrsHoldSum~Service,fun.aggregate= sum,value.var= "B1")
sub3 <- dcast(data= df, formula= Customer+Group+Seller+WrsHoldSum~Service,fun.aggregate= sum,value.var= "B2")
sub4 <- dcast(data= df, formula= Customer+Group+Seller+WrsHoldSum~Service,fun.aggregate= sum,value.var= "B3")

此部分使用dcast调整数据框结构，以便后续在Word的“邮件合并”模式中使用

3. 合并子表但未保存结果 #

tNames <- grep(x = ls(), pattern = "^sub", value = T)
lapply(seq_along(tNames), function(x){
  tSym <- as.name(tNames[[x]])
  d1 <- copy(eval(tSym))
  cols <- grep(x = names(d1), pattern = "^CAR|^COU|^FCL", value = T)
  setnames(d1, old = cols, new = paste0(cols, " B", x))
  return(d1)
}) %>% Reduce(function(x, y) merge(x, y, by = c("Customer","Group","Seller","WrsHoldSum")), .)

我在此处添加新列整合信息到表格中，但该表格未存入环境，无法使用write.csv()函数调用

解决方案 #

方案1：快速修复“无法保存”的问题 #

最直接的办法就是把你这段合并代码的结果赋值给一个变量，比如final_df，这样就能直接用write.csv()保存了。注意要先加载data.table包（因为你用到了copy()和setnames()函数）：

library(data.table)

# 把合并结果赋值给变量
final_df <- lapply(seq_along(tNames), function(x){
  tSym <- as.name(tNames[[x]])
  d1 <- copy(eval(tSym))
  cols <- grep(x = names(d1), pattern = "^CAR|^COU|^FCL", value = T)
  setnames(d1, old = cols, new = paste0(cols, " B", x))
  return(d1)
}) %>% Reduce(function(x, y) merge(x, y, by = c("Customer","Group","Seller","WrsHoldSum")), .)

# 保存为CSV文件
write.csv(final_df, "final_customer_data.csv", row.names = FALSE)

方案2：更简洁高效的实现（避免重复dcast） #

其实我们可以先把数据转成长格式，一次性处理所有需要聚合的变量，再直接转成目标宽格式，代码更精简，也不容易出错：

library(reshape2)
library(data.table)

# 1. 把数据转成长格式：保留分组列，将Billingmean、B1-B3转为变量列
melted_df <- melt(df, 
                  id.vars = c("Customer", "Group", "Seller", "WrsHoldSum", "Service"),
                  measure.vars = c("Billingmean", "B1", "B2", "B3"))

# 2. 给变量重命名，对应你要的B1/B2/B3/B4格式（Billingmean对应B1，B1对应B2，以此类推）
melted_df$variable <- factor(melted_df$variable,
                             levels = c("Billingmean", "B1", "B2", "B3"),
                             labels = c("B1", "B2", "B3", "B4"))

# 3. 一次性转成宽格式，组合Service和变量名作为列名
final_df <- dcast(melted_df,
                  Customer + Group + Seller + WrsHoldSum ~ Service + variable,
                  fun.aggregate = sum,
                  fill = 0) # 缺失值填充为0

# 4. 调整列顺序，和你的预期输出完全匹配
target_cols <- c("Customer", "Group", "Seller", "WrsHoldSum",
                 "CAR B1", "COU B1", "FCL B1",
                 "CAR B2", "COU B2", "FCL B2",
                 "CAR B3", "COU B3", "FCL B3",
                 "CAR B4", "COU B4", "FCL B4")
# 把列名的下划线换成空格
setnames(final_df, gsub("_", " ", names(final_df)))
# 按目标顺序排列列
final_df <- final_df[, ..target_cols]

# 5. 保存为CSV
write.csv(final_df, "final_customer_data.csv", row.names = FALSE)

验证结果 #

运行上述代码后，得到的final_df和你预期的输出完全一致：

Customer Group Seller WrsHoldSum CAR B1 COU B1 FCL B1 CAR B2 COU B2 FCL B2 CAR B3 COU B3 FCL B3 CAR B4 COU B4 FCL B4
1    Frank          Chris          0       0       0     300       0       0       2       0       0       7       0       0       3
2    Louis    n2  Donald          0     200       0       0       2       0       0       8       0       0       4       0       0
3    Susan          Ivan          0     400       0       0       1       0       0       6       0       0       2       0       0
4    Susan    n1    Ivan          0       0     100       0       0       0       0       0       9       0       0       5       0

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