如何将横向位置数据转为纵向并过滤空值（R语言）

原始数据 #

actor_data <- structure(list(id = c(123L, 456L, 789L, 912L, 235L), name = c("Tom Cruise", 
"Will Smith", "Ryan Reynolds", "Chris Rock", "Emma Stone"), locationid1 = c(5459L, 
NA, 6114L, NA, NA), location1 = c("Paris, France", "", "Brooklyn, NY", 
"", ""), locationid2 = c(NA, 5778L, NA, NA, 4432L), location3 = c("", 
"Dolby Theater", "", "", "Hollywood"), locationid3 = c(NA, 2526L, 
3101L, NA, NA), location3.1 = c("", "London", "Boston", "", ""
), locationid4 = c(6667L, 2333L, 1118L, NA, NA), location4 = c("Virginia", 
"Maryland", "Washington", "", "")), class = "data.frame", row.names = c(NA, 
-5L))

目标需求 #

将数据中横向存储的locationid*与对应location*字段转换为纵向结构，同时排除含NA或空字符串的无效记录，最终得到如下长表：

actor_data_exp <- structure(list(id = c(123L, 123L, 456L, 456L, 456L, 789L, 789L, 
789L, 235L), name = c("Tom Cruise", "Tom Cruise", "Will Smith", 
"Will Smith", "Will Smith", "Ryan Reynolds", "Ryan Reynolds", 
"Ryan Reynolds", "Emma Stone"), locationid = c(5459L, 6667L, 
5778L, 2526L, 2333L, 6114L, 3101L, 1118L, 4432L), location = c("Paris, France", 
"Virginia", "Dolby Theater", "London", "Maryland", "Brooklyn, NY", 
"Boston", "Washington", "Hollywood")), class = "data.frame", row.names = c(NA, 
-9L))

解决方案 #

方法1：使用tidyverse包（推荐） #

先统一列名，让location与locationid的后缀编号一一对应，再通过pivot_longer实现宽表转长表，最后过滤无效记录：

library(tidyverse)

# 统一列名，修正location列的编号对应关系
actor_data_rename <- actor_data %>%
  rename(location2 = location3, location3 = location3.1)

# 转换为长表并过滤无效值
actor_data_exp <- actor_data_rename %>%
  pivot_longer(
    cols = starts_with(c("locationid", "location")),
    names_to = c(".value", "num"),
    names_pattern = "(locationid|location)(\\d+)"
  ) %>%
  filter(!is.na(locationid), location != "") %>%
  select(-num) %>%
  arrange(id)

方法2：使用Base R #

通过匹配locationid与location列的对应关系，生成多个子数据框后合并，再过滤无效记录：

# 定义基础列和位置相关列
id_cols <- c("id", "name")
loc_id_cols <- grep("locationid", names(actor_data), value = TRUE)
loc_cols <- c("location1", "location3", "location3.1", "location4")

# 生成对应的数据框列表并合并
df_list <- Map(function(id_col, loc_col) {
  temp <- actor_data[, c(id_cols, id_col, loc_col)]
  names(temp)[3:4] <- c("locationid", "location")
  temp
}, loc_id_cols, loc_cols)

# 合并后过滤无效记录
actor_data_exp <- do.call(rbind, df_list) %>%
  filter(!is.na(locationid), location != "") %>%
  arrange(id)

验证结果 #

执行以下代码可确认转换结果与目标结构一致：

all.equal(actor_data_exp, structure(list(id = c(123L, 123L, 456L, 456L, 456L, 789L, 789L, 
789L, 235L), name = c("Tom Cruise", "Tom Cruise", "Will Smith", 
"Will Smith", "Will Smith", "Ryan Reynolds", "Ryan Reynolds", 
"Ryan Reynolds", "Emma Stone"), locationid = c(5459L, 6667L, 
5778L, 2526L, 2333L, 6114L, 3101L, 1118L, 4432L), location = c("Paris, France", 
"Virginia", "Dolby Theater", "London", "Maryland", "Brooklyn, NY", 
"Boston", "Washington", "Hollywood")), class = "data.frame", row.names = c(NA, 
-9L)))
# 返回TRUE表示结果匹配

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