在R中基于多列修正数据框的排名问题

我有一个R语言的DataFrame df，原数据按B、A、C降序排列，D列代表C列基于该分组排序的排名。删除第2、4、7行后，D列的排名不再正确，需要修正为指定的期望输出，试了两段代码都没得到理想结果，求解决办法。

原数据结构 #

structure(list(A = c("10/18", "10/18", "10/18", "10/19", "10/19", 
"10/20", "10/18", "10/18"), B = c("1", "1", "1", "1", "1", "1", 
"2", "2"), C = c("2.3", "2.1", "0.2", "0.7", "0.4", "4.0", "3.0", 
"1.5"), D = c("1", "2", "3", "1", "2", "1", "1", "2")), class = "data.frame", row.names = c(NA, 
-8L))

期望输出结构 #

structure(list(A = c("10/18", "10/18", "10/19", "10/20", "10/18"
), B = c("1", "1", "1", "1", "2"), C = c("2.3", "0.2", "0.4", 
"4.0", "1.5"), D = c("1", "2", "1", "1", "1")), row.names = c(1L, 
3L, 5L, 6L, 8L), class = "data.frame")

尝试过的代码 #

第一段代码 #

df <- df %>%
  group_by(B,A, desc(C)) %>%
  mutate(D = rank(desc(C)))

第二段代码 #

A <- c('10/18', '10/18', '10/18', '10/19', '10/19', '10/20', '10/18', '10/18')
B <- c(rep(c('1'), 6), '2', '2')
C <- c('2.3', '2.1', '0.2', '0.7', '0.4', '4.0', '3.0', '1.5')
D <- c('1','2','3', '1', '2', '1', '1', '2')
df <- data.frame(A,B,C,D)

df <- df[-c(2,4,7),]  %>%
  arrange(B, A, rev(C)) %>%
  mutate(D = row_number(), .by = c(A, B))

问题分析与解决方法 #

问题根源 #

1. 第一段代码错误地将desc(C)加入分组条件，导致每个不同的C值单独成组，无法生成正确的组内排名，分组应仅保留B和A。
2. 第二段代码未将C列转换为数值型：原数据中C是字符类型，字符串排序逻辑与数值排序逻辑不一致（比如字符"4.0"会排在"2.3"之后，但数值上4.0更大），导致排名错误。

正确代码 #

library(dplyr)

# 加载原数据
df <- structure(list(A = c("10/18", "10/18", "10/18", "10/19", "10/19", 
"10/20", "10/18", "10/18"), B = c("1", "1", "1", "1", "1", "1", 
"2", "2"), C = c("2.3", "2.1", "0.2", "0.7", "0.4", "4.0", "3.0", 
"1.5"), D = c("1", "2", "3", "1", "2", "1", "1", "2")), class = "data.frame", row.names = c(NA, 
-8L))

# 删除指定行
df <- df[-c(2,4,7),]

# 修正D列排名
df_fixed <- df %>%
  # 将C列转为数值型，确保排序逻辑正确
  mutate(C = as.numeric(C)) %>%
  # 按B、A分组，组内按C降序排列
  arrange(B, A, desc(C)) %>%
  # 组内生成连续排名
  mutate(D = row_number(), .by = c(B, A)) %>%
  # 将D列转为字符型，匹配期望输出的类型
  mutate(D = as.character(D))

# 输出结果
df_fixed

运行上述代码后，得到的结果与期望输出完全一致。

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