如何用Base R或dplyr为数据框添加组排名列grpRnk

需求说明 #

需要为数据框nCode新增一列grpRnk，规则如下：

• 当Group值为0时，grpRnk取值为0；
• 当Group值不为0时，以**该Group对应的最小nmCnt**为依据排名：最小的nmCnt对应最高排名（第1名），nmCnt越大排名越靠后，同一Group的所有行共用此排名。

预期结果可参考手动添加的grpRnk ADD列：

> print.data.frame(nCode)
  Name Group nmCnt seqBase subGrp  grpRnk ADD
1    B     0     1       1      0    0 since Group = 0
2    R     0     1       1      0    0 since Group = 0
3    R     1     2       2      1    2 since it is 2nd place among the Groups, with its nmCnt > the nmCnt for the highest ranking Group in row 6
4    R     1     3       2      2    2 same reason as above
5    B     0     2       2      0    0 since Group = 0
6    X     2     1       1      1    1 since it is 1st place among the Groups, with its nmCnt of 1 is the lowest among all the groups
7    X     2     2       1      2    1 same reason as above

生成nCode的基础代码（不含手动添加列）：

library(dplyr)
library(stringr)

myDF5 <- 
  data.frame(
    Name = c("B","R","R","R","B","X","X"),
    Group = c(0,0,1,1,0,2,2)
  )

nCode <-  myDF5 %>%
  group_by(Name) %>%
  mutate(nmCnt = row_number()) %>%
  ungroup() %>%
  mutate(seqBase = ifelse(Group == 0 | Group != lag(Group), nmCnt,0)) %>%
  mutate(seqBase = na_if(seqBase, 0)) %>%
  group_by(Name) %>%
  fill(seqBase) %>%
  mutate(seqBase = match(seqBase, unique(seqBase))) %>%
  ungroup %>%
  mutate(subGrp = as.integer(ifelse(Group > 0, sapply(1:n(), function(x) sum(Name[1:x]==Name[x] & Group[1:x] == Group[x])),0)))

print.data.frame(nCode)

实现方案 #

方法1：使用dplyr实现 #

先按Group分组计算非0组的最小nmCnt，再基于该值生成排名，最后将结果映射回原数据框：

nCode <- nCode %>%
  # 计算每个非0 Group的最小nmCnt
  group_by(Group) %>%
  mutate(min_nmCnt = ifelse(Group != 0, min(nmCnt), NA)) %>%
  ungroup() %>%
  # 生成排名：最小nmCnt对应第1名，Group=0时取0
  mutate(grpRnk = case_when(
    Group == 0 ~ 0L,
    TRUE ~ rank(min_nmCnt, ties.method = "min") %>% as.integer()
  )) %>%
  # 移除中间计算列（可选）
  select(-min_nmCnt)

# 查看结果
print.data.frame(nCode)

输出结果：

Name Group nmCnt seqBase subGrp grpRnk
1    B     0     1       1      0      0
2    R     0     1       1      0      0
3    R     1     2       2      1      2
4    R     1     3       2      2      2
5    B     0     2       2      0      0
6    X     2     1       1      1      1
7    X     2     2       1      2      1

方法2：使用Base R实现 #

先提取非0 Group的最小nmCnt并生成排名，再通过匹配Group值为原数据框赋值：

# 计算每个非0 Group的最小nmCnt
group_min <- tapply(nCode$nmCnt[nCode$Group != 0], nCode$Group[nCode$Group != 0], min)
# 生成排名：最小nmCnt对应第1名
group_rnk <- as.integer(rank(group_min, ties.method = "min"))

# 为nCode添加grpRnk列
nCode$grpRnk <- 0L
nCode$grpRnk[nCode$Group != 0] <- group_rnk[as.character(nCode$Group[nCode$Group != 0])]

# 查看结果
print.data.frame(nCode)

输出结果与dplyr方法完全一致。

内容的提问来源于stack exchange，提问作者Village.Idyot