无法生成指定格式矩阵：求构建带行列总计的分组矩阵函数

需构建R函数，输入数据集df、credit_breaks分箱规则、Rate_Cutpoints1至Rate_Cutpoints6多组分箱规则，基于credit和rate的分箱结果，生成包含Credit_Band与New_Band交叉统计值、行总计（Total_R）、列总计（TotalCols）的矩阵。现有代码已完成分箱、分组统计及行列总计计算，但无法整合成如下目标格式：

Credit_Band              1      2        3        4          5          6       TotalCols
               1      1.78      NA      NA        NA        NA          NA       1.79
               2      1.44      NA      NA        NA        NA          NA       1.44
               3       NA    1.23       NA        NA        NA          NA       1.24
               4       NA       NA     1         NA        NA          NA        1
               5       NA       NA      NA      0.58       NA           NA        0.58
               6       NA       NA      NA      1.25      0.83          1         0.93
Total_R               1.59   1.24      1      0.75     0.83         1

数据构造与分箱参数 #

# 构造数据集
credit <- c(10,20,30,40,10,30,50,70,90,100,25,45,67,87,98,54,34,56,78,23,45,56,12)
rate <- c(1,2,3,4,1,3,5,7,9,10,2,4,6,8,9,5,3,5,7,2,4,5,1)
Marks <- c(9,3,5,6,7,8,9,1,3,10,4,5,6,7,5,4,8,3,5,6,7,8,9)
Points <- c(1,2,3,4,5,6,7,8,9,10,2,3,4,4,5,7,8,3,4,5,6,7,8)
Scale <- c(1,2,3,4,5,6,7,8,9,10,2,3,4,4,5,7,8,3,4,5,6,7,8)
Category <- c('book', 'pen', 'textbook', 'pencil_case','book', 'pen', 'textbook', 'pencil_case','book', 'pen', 'textbook', 'pencil_case','book','pen' ,'pen', 'textbook', 'pencil_case','book', 'pen', 'textbook', 'pencil_case','book', 'pencil_case')
df <- data.frame(credit,rate,Marks,Points,Scale,Category)

# 分箱参数
credit_breaks<-c(0,15,30,45,65,75,1000)
Rate_Cutpoints1<-c(0,1,2,5,7,9,10)
Rate_Cutpoints2<-c(0,3,4,7,8,9,10)
Rate_Cutpoints3<-c(0,1,5,6,8,9,10)
Rate_Cutpoints4<-c(0,1,3,6,7,9,10)
Rate_Cutpoints5<-c(0,2,3,4,8,9,10)
Rate_Cutpoints6<-c(0,3,4,5,7,9,10)

修正后的完整处理代码 #

library(dplyr)
library(reshape2)
library(tidyr)

# 1. 数据分箱处理
df1 <- df %>% 
  mutate(Credit_Band = cut(credit, include.lowest = TRUE, right = TRUE,
                           breaks = credit_breaks, labels = FALSE))

df2 <- df1 %>%
  mutate(New_Band = case_when(
    Credit_Band == 1 ~ cut(rate, Rate_Cutpoints1, labels = FALSE),
    Credit_Band == 2 ~ cut(rate, Rate_Cutpoints2, labels = FALSE),
    Credit_Band == 3 ~ cut(rate, Rate_Cutpoints3, labels = FALSE),
    Credit_Band == 4 ~ cut(rate, Rate_Cutpoints4, labels = FALSE),
    Credit_Band == 5 ~ cut(rate, Rate_Cutpoints5, labels = FALSE),
    Credit_Band == 6 ~ cut(rate, Rate_Cutpoints6, labels = FALSE),
    TRUE ~ NA_integer_
  )) %>%
  as.data.frame()

# 2. 交叉统计计算
summary_results <- df2 %>%
  group_by(Credit_Band, New_Band) %>%
  summarize(dist = n()/nrow(df2),
            count = n(),
            avg_marks = sum(Marks, na.rm = TRUE),
            sum_points = sum(Points, na.rm = TRUE),
            .groups = "drop") %>%
  mutate(final = avg_marks / sum_points)

# 3. 转换为宽格式矩阵
results <- reshape2::dcast(data = summary_results, formula = Credit_Band ~ New_Band,
                           value.var = "final", fill = NA)

# 4. 添加列总计（TotalCols）
total_cols_vals <- df2 %>%
  group_by(Credit_Band) %>%
  summarize(sum_points = sum(Points, na.rm = TRUE),
            avg_marks = sum(Marks, na.rm = TRUE),
            .groups = "drop") %>%
  mutate(TotalCols = avg_marks / sum_points) %>%
  select(Credit_Band, TotalCols)

results <- results %>%
  left_join(total_cols_vals, by = "Credit_Band")

# 5. 添加行总计（Total_R）
total_rows_value <- df2 %>%
  group_by(New_Band) %>%
  summarize(sum_points = sum(Points, na.rm = TRUE),
            avg_marks = sum(Marks, na.rm = TRUE),
            .groups = "drop") %>%
  mutate(final = avg_marks / sum_points) %>%
  pivot_wider(names_from = New_Band, values_from = final, names_prefix = "") %>%
  mutate(Credit_Band = "Total_R", TotalCols = NA)

# 合并行总计到结果矩阵
final_matrix <- bind_rows(results, total_rows_value)

# 6. 格式化输出（保留两位小数）
final_matrix <- final_matrix %>%
  mutate(across(-Credit_Band, ~round(., 2)))

# 查看最终结果
print(final_matrix, row.names = FALSE)

关键调整说明 #

• 用case_when替代嵌套ifelse，代码更简洁易读
• 在group_by后添加.groups = "drop"避免分组残留问题
• 通过left_join将列总计合并到宽格式结果中
• 将行总计转换为宽格式后，用bind_rows合并到主矩阵
• 最后统一格式化数值为两位小数，匹配目标格式

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