Using pivot_longer to re-shape data from wide to long

Question

I want to re-shape this data frame using pivot_longer -

# A tibble: 5 x 7
  PizzaNumber Topping_1 Category_1  Topping_2 Category_2  Topping_3 Category_3 
        <int> <fct>     <fct>       <fct>     <fct>       <fct>     <fct>      
1           1 cheese    vegetarian  ham       carnivorous tomato    vegetarian 
2           2 spinach   vegetarian  tomato    vegetarian  NA        NA         
3           3 pineapple vegetarian  cheese    vegetarian  ham       carnivorous
4           4 cheese    vegetarian  tomato    vegetarian  NA        NA         
5           5 beef      carnivorous NA        NA          NA        NA

Into the following long format -

# A tibble: 11 x 3
   PizzaNumber Topping   Category   
         <int> <fct>     <fct>      
 1           1 cheese    vegetarian 
 2           1 ham       carnivorous
 3           1 tomato    vegetarian 
 4           2 spinach   vegetarian 
 5           2 tomato    vegetarian 
 6           3 pineapple vegetarian 
 7           3 cheese    vegetarian 
 8           3 ham       carnivorous
 9           4 cheese    vegetarian 
10           4 tomato    vegetarian 
11           5 beef      carnivorous

Can somebody help me with the code to achieve this? So far my attempts have resulted in a jumbled mess.

Code for wide data frame is as follows -

> dput(widedata)
structure(list(PizzaNumber = c(1L, 1L, 1L, 2L, 2L, 3L, 3L, 3L, 
4L, 4L, 5L), Topping = structure(c(2L, 3L, 6L, 5L, 6L, 4L, 2L, 
3L, 2L, 6L, 1L), .Label = c("beef", "cheese", "ham", "pineapple", 
"spinach", "tomato"), class = "factor"), Category = structure(c(2L, 
1L, 2L, 2L, 2L, 2L, 2L, 1L, 2L, 2L, 1L), .Label = c("carnivorous", 
"vegetarian"), class = "factor")), class = "data.frame", row.names = c(NA, 
-11L))

kmacierzanka · Answer 1 · 2020-06-28T09:21:37.033

We can do this with a combination of dplyr, tidyr and purrr:

library(dplyr) # 1.0.0
library(tidyr) # 1.1.0
library(purrr) # 0.3.4

widedata %>% group_by(PizzaNumber) %>% nest() %>%
        mutate(
                Topping_Category = map(data, function(data) {
                        as.list(paste(data[, c(1, 3, 5)], data[, c(2, 4, 6)], sep = "_"))
                })) %>% select(-data) %>%
        unnest_longer(Topping_Category, indices_include = FALSE) %>%
        ungroup() %>%
        separate(Topping_Category, c("Topping", "Category"), sep = "_") %>%
        mutate(Topping = na_if(Topping, "NA"), Category = na_if(Category, "NA")) %>%
        filter(Topping != is.na(Topping))

# A tibble: 11 x 3
   PizzaNumber Topping   Category   
         <dbl> <chr>     <chr>      
 1           1 cheese    vegetarian 
 2           1 ham       carnivorous
 3           1 tomato    vegetarian 
 4           2 spinach   vegetarian 
 5           2 tomato    vegetarian 
 6           3 pineapple vegetarian 
 7           3 cheese    vegetarian 
 8           3 ham       carnivorous
 9           4 cheese    vegetarian 
10           4 tomato    vegetarian 
11           5 beef      carnivorous

By the way, the data you've provided using dput(widedata) is actually your desired long format.

Here is the actual starting widedata:

structure(list(PizzaNumber = c("1", "2", "3", "4", "5"), Topping_1 = c("cheese", 
"spinach", "pineapple", "cheese", "beef"), Category_1 = c("vegetarian", 
"vegetarian", "vegetarian", "vegetarian", "carnivorous"), Topping_2 = c("ham", 
"tomato", "cheese", "tomato", NA), Category_2 = c("carnivorous", 
"vegetarian", "vegetarian", "vegetarian", NA), Topping_3 = c("tomato", 
NA, "ham", NA, NA), Category_3 = c("vegetarian", NA, "carnivorous", 
NA, NA)), row.names = c(NA, -5L), class = c("tbl_df", "tbl", 
"data.frame"))

score 0 · Answer 2 · answered May 29 '21 at 03:21

With pivot_longer you can do -

tidyr::pivot_longer(df, cols = -PizzaNumber, 
                    names_to = '.value', 
                    names_pattern = '(\\w+)_\\d+', 
                    values_drop_na = TRUE)

#  PizzaNumber Topping   Category   
#   <chr>       <chr>     <chr>      
# 1 1           cheese    vegetarian 
# 2 1           ham       carnivorous
# 3 1           tomato    vegetarian 
# 4 2           spinach   vegetarian 
# 5 2           tomato    vegetarian 
# 6 3           pineapple vegetarian 
# 7 3           cheese    vegetarian 
# 8 3           ham       carnivorous
# 9 4           cheese    vegetarian 
#10 4           tomato    vegetarian 
#11 5           beef      carnivorous

Using pivot_longer to re-shape data from wide to long

2 Answers2