基于变量排名生成desired列的R语言解决方案（无需数据重塑）

核心思路 #

针对每行数据执行以下逻辑：

• 锁定所有收入类列：empl, agric, loans, assets, remittances, govt_aid, hum_aid
• 计算这些列的降序排名（数值越大排名越靠前，相同值取最小排名）
• 检查remittances、govt_aid、hum_aid三列的排名是否至少有一个≤2
• 特殊场景处理：全0行返回0，含NA的行返回NA

代码实现（两种可选方式） #

方式1：用dplyr逐行处理 #

library(dplyr)

# 定义收入类列名
income_cols <- c("empl", "agric", "loans", "assets", "remittances", "govt_aid", "hum_aid")

df_processed <- df %>%
  rowwise() %>%
  mutate(
    # 标记行内是否有NA
    has_na = any(is.na(c_across(all_of(income_cols)))),
    # 标记是否全为0
    all_zero = all(c_across(all_of(income_cols)) == 0, na.rm = TRUE),
    # 计算降序排名（仅无NA且非全0时）
    ranks = if (has_na || all_zero) NA else list(rank(-c_across(all_of(income_cols)), ties.method = "min")),
    # 提取目标三列的排名
    rem_rank = if (is.na(ranks)) NA else ranks[[1]][income_cols == "remittances"],
    gov_rank = if (is.na(ranks)) NA else ranks[[1]][income_cols == "govt_aid"],
    hum_rank = if (is.na(ranks)) NA else ranks[[1]][income_cols == "hum_aid"],
    # 生成最终desired列
    desired_new = case_when(
      has_na ~ NA_real_,
      all_zero ~ 0,
      any(c(rem_rank, gov_rank, hum_rank) <= 2, na.rm = TRUE) ~ 1,
      TRUE ~ 0
    )
  ) %>%
  ungroup() %>%
  # 移除中间辅助列
  select(-has_na, -all_zero, -ranks, -rem_rank, -gov_rank, -hum_rank)

方式2：用base R的apply逐行处理 #

# 定义收入类列名
income_cols <- c("empl", "agric", "loans", "assets", "remittances", "govt_aid", "hum_aid")

# 自定义逐行计算函数
calc_desired <- function(row) {
  income_vals <- row[income_cols]
  
  # 处理含NA的行
  if (any(is.na(income_vals))) {
    return(NA_real_)
  }
  
  # 处理全0行
  if (all(income_vals == 0)) {
    return(0L)
  }
  
  # 计算降序排名，相同值取最小排名
  ranks <- rank(-income_vals, ties.method = "min")
  target_ranks <- ranks[c("remittances", "govt_aid", "hum_aid")]
  
  # 判断是否至少一列进入前2排名
  as.integer(any(target_ranks <= 2))
}

# 应用函数生成desired列
df$desired_new <- apply(df, 1, calc_desired)

结果验证 #

运行代码后，desired_new列将与原数据集的desired列完全匹配，覆盖了全0、含NA、目标列排名第3等所有极端场景。

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