Reading data with multiple column headers

Question

I have a csv file with 2 column headers, like so:

tibble(brand_1 = c("region_1", 12, 4),
       brand_2 = c("region_1", 8, 10),
       brand_3 = c("region_2", 7, 12))

      brand_1  brand_2  brand_3 
      <chr>    <chr>    <chr>   
    1 region_1 region_1 region_2
    2 12       8        7       
    3 4        10       12

What's the best way to transform that into long format, like so:

# A tibble: 6 x 3
  brand   region   value
  <chr>   <chr>    <dbl>
1 brand_1 region_1    12
2 brand_1 region_1    14
3 brand_2 region_1     8
4 brand_2 region_1    10
5 brand_3 region_2     7
6 brand_3 region_2    12

Maurits Evers · Accepted Answer · 2019-09-09 10:36:15Z

2

Not necessarily pretty but it gets the job done

library(tidyverse)
df %>%
    magrittr::set_colnames(paste(names(.), .[1, ], sep = ";")) %>%
    filter(row_number() > 1) %>%
    gather(key, value) %>%
    separate(key, c("brand", "region"), sep = ";")
## A tibble: 6 x 3
#  brand   region   value
#  <chr>   <chr>    <chr>
#1 brand_1 region_1 12
#2 brand_1 region_1 4
#3 brand_2 region_1 8
#4 brand_2 region_1 10
#5 brand_3 region_2 7
#6 brand_3 region_2 12

Explanation: The idea is to concatenate the two column headers, then reshape the data from wide to long, and finally to separate the concatenated header entries with separate.

Sample data

df <- tibble(brand_1 = c("region_1", 12, 4),
       brand_2 = c("region_1", 8, 10),
       brand_3 = c("region_2", 7, 12))

edited Sep 9, 2019 at 10:36

answered Sep 9, 2019 at 10:34

Maurits Evers

51k4 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shinobi_Atobe Over a year ago

Thanks, I had a similar idea using something like df %>% t() %>% data.frame() %>% rownames_to_column() %>% gather(a, b, X2:X3) but it feels pretty horible

Collectives™ on Stack Overflow

Reading data with multiple column headers

1 Answer 1

Sample data

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample data

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related