Pivot_wider with multiple (parallel) columns

Question

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,
)

You can make use of the awesome functionality to build a spec data frame where you define which column goes where. See here for an example: https://stackoverflow.com/a/61367970/2725773 — deschen, Nov 01 '21 at 20:41
`df %>%pivot_longer(starts_with("WC"), names_to = c("segm", ".value"), names_pattern = "(\\d)(\\d$)")` — Onyambu, Nov 01 '21 at 20:42

score 1 · Accepted Answer · answered Nov 01 '21 at 20:39

1

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.)

answered Nov 01 '21 at 20:39

r2evans

141,215
6
77
149

2

If you wanted to just pull out the last two digits to be "segm" and ".value" you could do a names pattern like `names_pattern = "WC196(\\d)(\\d)"`. Of course this only works if the real columns all start with the exact same pattern like in the example so could be fragile. :) – aosmith Nov 01 '21 at 21:12
good point, that would be indeed the case! – Marcelo Avila Nov 01 '21 at 22:32

Pivot_wider with multiple (parallel) columns

I've tried

Data

1 Answers1