R tidyr::pivot_longer处理多组平行列实现宽表转长表

问题描述 #

我有如下宽格式R数据框df，存储企业分区域经营数据：其中列WC19600、WC19610存储区域信息，WC19601、WC19611存储销售额数据，列名的倒数第二位代表segment level（细分层级）。

# A tibble: 2 x 6
  NAME      ISIN         WC19600       WC19610           WC19601   WC19611
  <chr>     <chr>        <chr>         <chr>               <dbl>     <dbl>
1 APPLE     US0378331005 United States Other Foreign   109197000 125010000
2 MICROSOFT US5949181045 United States Other countries  83953000  84135000

我需要将其转换为如下长格式，每行对应单个企业单个细分层级的数据：

# A tibble: 4 x 5
  NAME      ISIN          segm region            sales
  <chr>     <chr>        <dbl> <chr>             <dbl>
1 APPLE     US0378331005     0 United States 109197000
2 APPLE     US0378331005     1 Other Foreign 125010000
3 MICROSOFT US5949181045     0 United States  83953000
4 MICROSOFT US5949181045     1 United States  84135000

我尝试了两次调用tidyr::pivot_longer分别转换区域和销售额列，但得到的输出出现了笛卡尔积导致的冗余数据，请问如何单次调用pivot_longer完成转换，得到预期结果？

原有尝试代码 #

df %>% 
  tidyr::pivot_longer(
    c(WC19600, WC19610),
    names_pattern = "WC196(\\d)0", 
    names_to = "segm",
    values_to = "region"
  ) %>% 
  tidyr::pivot_longer(
    c(WC19601, WC19611),
    names_pattern = "WC196(\\d)1", 
    names_to = "segm",
    values_to = "sales", 
    names_repair = "minimal"
  )

# 错误输出
# A tibble: 8 x 6
  NAME      ISIN         segm  region          segm      sales
  <chr>     <chr>        <chr> <chr>           <chr>     <dbl>
1 APPLE     US0378331005 0     United States   0     109197000
2 APPLE     US0378331005 0     United States   1     125010000
3 APPLE     US0378331005 1     Other Foreign   0     109197000
4 APPLE     US0378331005 1     Other Foreign   1     125010000
5 MICROSOFT US5949181045 0     United States   0      83953000
6 MICROSOFT US5949181045 0     United States   1      84135000
7 MICROSOFT US5949181045 1     Other countries 0      83953000
8 MICROSOFT US5949181045 1     Other countries 1      84135000

示例数据集 #

# 输入数据
df <- tibble::tribble(
  ~NAME,          ~ISIN,        ~WC19600,          ~WC19610,  ~WC19601,  ~WC19611,
  "APPLE", "US0378331005", "United States",   "Other Foreign", 109197000, 125010000,
  "MICROSOFT", "US5949181045", "United States", "Other countries",  83953000,  84135000
)
# 预期结果
expected <- tribble(
  ~NAME, ~ISIN, ~segm, ~region, ~sales,
  "APPLE","US0378331005",0,"United States",109197000,
  "APPLE","US0378331005",1,"Other Foreign",125010000,
  "MICROSOFT", "US5949181045", 0, "United States", 83953000,
  "MICROSOFT", "US5949181045", 1, "United States", 84135000,
)

解决方案 #

直接使用pivot_longer的.value特殊标识即可单次完成转换，无需多次调用：

library(tidyr)
library(dplyr)

result <- df %>% 
  pivot_longer(
    cols = starts_with("WC196"),
    names_pattern = "WC196(\\d)(\\d)",
    names_to = c("segm", ".value"),
    names_transform = list(segm = as.integer)
  ) %>% 
  rename(region = `0`, sales = `1`)

代码说明 #

• names_pattern正则将列名拆分为两个捕获组：第一个捕获组对应倒数第二位的细分层级编号，第二个捕获组对应最后一位的字段类型标识（0为区域、1为销售额）
• .value是names_to参数的特殊标识，代表第二个捕获组的取值将作为新的列名，自动把不同类型的值拆分到对应列，避免多次pivot产生笛卡尔积
• names_transform将segm字段转换为整数类型，和预期结果格式保持一致
• 最后一步重命名自动生成的0、1列为语义更清晰的region和sales

运行上述代码得到的结果和预期输出完全一致。

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