Pivot_wider with multiple (parallel) columns-CodePudding

Say I have the following data (df) that contains data for companies segmented regionally. df is a wide format where WC19600, WC19610 describe de location and WC19601, WC19611 contains sales data. The penultimate digit in those variables indicate the 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

I aim to have the data in a longer format such as

# 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

I've tried

I've tried along the following lines, but I actually need to pivot it only once and merge the output on the segment level and have two columns on desc

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

Data

# input data
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
)

# aimed results
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,
)

CodePudding user response：

library(dplyr)
library(tidyr)
pivot_longer(
  df, -c(NAME, ISIN),
  names_pattern = "(.*)([0-9])$", names_to = c("segm", ".value")
) %>%
  rename(region = "0", sales= "1")
# # A tibble: 4 x 5
#   NAME      ISIN         segm   region              sales
#   <chr>     <chr>        <chr>  <chr>               <dbl>
# 1 APPLE     US0378331005 WC1960 United States   109197000
# 2 APPLE     US0378331005 WC1961 Other Foreign   125010000
# 3 MICROSOFT US5949181045 WC1960 United States    83953000
# 4 MICROSOFT US5949181045 WC1961 Other countries  84135000

(You can add %>% mutate(segm = gsub(".*(.)$", "\\1", segm)) to clean up segm, if you need/want.)