CODEWITHSUNDEEP
rdataframedplyrpivottidyr
benjasast
benjasast

Reputation: 107

R: pivot_wider() . how to extend it to two columns

I have not been able to find a previous answer that helps me resolve the following problem.

I have a dataframe that looks like the following:

example_df <- tibble::tribble(
  ~ COMPANY, ~ ATTRIBUTE_NAME_1 , ~ ATTRIBUTE_VALUE_1, ~ATTRIBUTE_NAME_2, ~ATTRIBUTE_VALUE_2,
  'ACME', 'P/E' , '200', 'BV' , '200M',
  'Stark Ind', 'P/E' , '300', 'MKT CAP', '3000M')

I would like to pivot it longer into two columns, one named "ATTRIBUTE_NAME" and another "ATTRIBUTE_VALUE" . How can I do so? -- A regular pivot longer does not work. Any help is appreciated.

result_df <- tibble::tribble(
  ~ COMPANY, ~ ATTRIBUTE_NAME, ~ATTRIBUTE_VALUE,
  'ACME', 'P/E', '200',
  'ACME', 'BV', '200M',
  'Stark Ind', 'P/E', '300',
  'Stark Ind', 'MKT CAP', '3000M')

Upvotes: 0

Views: 200

Answers (2)

Phil
Phil

Reputation: 8127

The vignette for pivoting is a useful guide: https://tidyr.tidyverse.org/articles/pivot.html

library(tidyr)

pivot_longer(example_df, -COMPANY, 
             names_to = c(".value", NA),
             names_pattern = "(.*)_(1|2)$")

# A tibble: 4 x 3
  COMPANY   ATTRIBUTE_NAME ATTRIBUTE_VALUE
  <chr>     <chr>          <chr>          
1 ACME      P/E            200            
2 ACME      BV             200M           
3 Stark Ind P/E            300            
4 Stark Ind MKT CAP        3000M

Upvotes: 3

nyk
nyk

Reputation: 680

It takes 2 separate steps. You can try the following:

library(tidyverse)

a <- example_df %>% select(COMPANY, contains("NAME")) %>%
  pivot_longer(contains("NAME"), names_to = "IND", values_to = "ATTRIBUTE_NAME") %>% select(-IND)

b <- example_df %>% select(COMPANY, contains("VALUE")) %>% 
  pivot_longer(contains("VALUE"), names_to = "IND", values_to = "ATTRIBUTE_VALUE") %>% select(-IND)

bind_cols(a, b %>% select(-COMPANY))

Upvotes: 1

Related Questions