如何动态将含latestPosts.n.location字段的宽表转为长表？

问题 #

现有如下R数据集：

testdata <- structure(list(id = c(723L, 621L, NA, NA, NA, NA, NA, NA, NA), 
    fullName = c("Will Smith", "Chris Rock", "", "", "", "", 
    "", "", ""), latestPosts.0.locationId = c(212928653L, 34505L, 
    NA, NA, NA, NA, NA, NA, NA), latestPosts.0.locationName = c("Miami", 
    "Atlanta", "", "", "", "", "", "", ""), latestPosts.1.locationId = c(1040683L, 
    20326736L, NA, NA, NA, NA, NA, NA, NA), latestPosts.1.locationName = c("New York", 
    "London", "", "", "", "", "", "", ""), latestPosts.2.locationId = c(NA, 
    215307317L, NA, NA, NA, NA, NA, NA, NA), latestPosts.2.locationName = c("", 
    "Paris", "", "", "", "", "", "", ""), latestPosts.3.locationId = c(1147378L, 
    34505L, NA, NA, NA, NA, NA, NA, NA), latestPosts.3.locationName = c("Seattle", 
    "Atlanta", "", "", "", "", "", "", ""), latestPosts.4.locationId = c(1147378L, 
    NA, NA, NA, NA, NA, NA, NA, NA), latestPosts.4.locationName = c("Seattle", 
    "", "", "", "", "", "", "", ""), latestPosts.5.locationId = c(238334931, 
    9432076525, NA, NA, NA, NA, NA, NA, NA), latestPosts.5.locationName = c("San Francisco", 
    "Brooklyn", "", "", "", "", "", "", ""), latestPosts.6.locationId = c(881699386L, 
    NA, NA, NA, NA, NA, NA, NA, NA), latestPosts.6.locationName = c("San Diego", 
    "", "", "", "", "", "", "", ""), latestPosts.7.locationId = c(NA, 
    234986797L, NA, NA, NA, NA, NA, NA, NA), latestPosts.8.locationId = c(1147378, 
    9021444765, NA, NA, NA, NA, NA, NA, NA), latestPosts.8.locationName = c("Seattle", 
    "Cleveland", "", "", "", "", "", "", ""), latestPosts.9.locationId = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA), latestPosts.9.locationName = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA), latestPosts.10.locationId = c(408631288L, 
    234986797L, NA, NA, NA, NA, NA, NA, NA), latestPosts.10.locationName = c("Portland", 
    "Orlando", "", "", "", "", "", "", ""), latestPosts.11.locationId = c(52043757619, 
    34505, NA, NA, NA, NA, NA, NA, NA), latestPosts.11.locationName = c("Nashville", 
    "Atlanta", "", "", "", "", "", "", "")), class = "data.frame", row.names = c(NA, 
-9L))

需要将该宽表转为长表，要求：

• 当任意latestPosts.n.locationId或latestPosts.n.locationName（n为数字）非空或非NA时，将其转为一行
• 最终输出结构参考如下：

testdata_exp <- structure(list(id = c(723L, 724L, 725L, 726L, 727L, 728L, 729L, 
730L, 731L, 621L, 622L, 623L, 624L, 625L, 626L, 627L, 628L, 629L
), fullName = c("Will Smith", "Will Smith", "Will Smith", "Will Smith", 
"Will Smith", "Will Smith", "Will Smith", "Will Smith", "Will Smith", 
"Chris Rock", "Chris Rock", "Chris Rock", "Chris Rock", "Chris Rock", 
"Chris Rock", "Chris Rock", "Chris Rock", "Chris Rock"), locationId = c(212928653, 
1040683, 1147378, 1147378, 238334931, 881699386, 1147378, 408631288, 
52043757619, 34505, 20326736, 215307317, 34505, 9432076525, 234986797, 
9021444765, 234986797, 34505), locationName = c("Miami Beach, Florida", 
"Starbucks", "University of Evansville", "University of Evansville", 
"Downtown Evansville", "Garden Of The Gods", "University of Evansville", 
"Phi Gamma Delta - Epsilon Iota", "Nashville Pride", "University of the South", 
"Riverview Camp For Girls", "Chattanooga, Tennessee", "University of the South", 
"Grand Sirenis Riviera Maya Resort", "", "Sleepyhead Coffee", 
"Sewanee, Tennessee", "University of the South")), class = "data.frame", row.names = c(NA, 
-18L))

同时需注意：

1. latestPosts.n.locationId或latestPosts.n.locationName的数量不固定，无法提前确定
2. 可能存在有locationId但无对应locationName的情况（如本例中的latestPosts.7.locationId）

解决方案 #

可以使用tidyverse包中的pivot_longer函数实现动态宽转长，步骤如下：

1. 加载必要的包：

library(tidyverse)

2. 执行宽转长操作：

testdata_long <- testdata %>%
  # 过滤原始数据中id和fullName均为空/NA的无效行
  filter(!is.na(id) | fullName != "") %>%
  # 动态匹配所有latestPosts开头的列，拆分为数字标识和字段类型
  pivot_longer(
    cols = matches("^latestPosts\\.\\d+\\.location(Id|Name)$"),
    names_to = c("n", ".value"),
    names_pattern = "^latestPosts\\.(\\d+)\\.location(Id|Name)$",
    values_drop_na = FALSE
  ) %>%
  # 过滤掉locationId和locationName都为空/NA的行
  filter(!(is.na(locationId) & (is.na(locationName) | locationName == ""))) %>%
  # 按用户分组，基于原始id递增生成新id
  group_by(fullName) %>%
  mutate(id = first(id) + row_number() - 1) %>%
  ungroup() %>%
  # 调整列顺序至目标格式
  select(id, fullName, locationId, locationName)

代码说明： #

• matches("^latestPosts\\.\\d+\\.location(Id|Name)$")：通过正则表达式动态匹配所有符合格式的列，无需提前指定n的范围
• names_pattern：将列名拆分为数字n和字段类型（Id/Name），.value参数指定把字段类型作为新列名
• 两次过滤：先清理原始数据中的无效空行，再清理转换后无有效位置信息的行
• 重置id：按用户分组，以原始id为基础递增生成新id，匹配示例输出的格式

运行后即可得到符合要求的长表。

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