R/tidyr- Creating New Data Frame Rows Based On Column Values

user_14637381 0
Viewed 48

Consider the following tibble, which contains meal order data. Variable order_item contains the item ordered. However, sometimes an order contains multiple items, as shown in extra_item1/2.

order_nbr  <-  c(1, 2, 3, 4, 5)
order_item <-  c("Burger", "Steak", "Lobster", "Pasta", "Salmon")
order_date <-  c("01-01-2022", "01-02-2022", "01-03-2022", "01-04-2022", "01-05-2022") 
extra_item1 <- c("Fries", "Fries", "Roll", "", "")
extra_item2 <- c("Shake", "Wine", "", "", "")

orders <- tibble(order_nbr, order_item, extra_item1, extra_item2)

Here's what the tibble looks like...

# Start with...
# A tibble: 5 × 5
  order_nbr order_date order_item extra_item1 extra_item2
      <dbl> <chr>      <chr>      <chr>       <chr>      
1         1 01-01-2022 Burger     "Fries"     "Shake"    
2         2 01-02-2022 Steak      "Fries"     "Wine"     
3         3 01-03-2022 Lobster    "Roll"      ""         
4         4 01-04-2022 Pasta      ""          ""         
5         5 01-05-2022 Salmon     ""          ""

It is here that I'd like to process orders somehow such that each extra_item1/2 becomes its own observation in orders.

# Do something...

I know it may be a variation of tidyr's pivot_wider / pivot_longer functions, but I can't quite get there.

# End up with
# A tibble: 10 × 5
   order_nbr order_date order_item extra_item1 extra_item2
       <dbl> <chr>      <chr>      <chr>       <chr>      
 1         1 01-01-2022 Burger     ""          ""         
 2         2 01-02-2022 Steak      ""          ""         
 3         3 01-03-2022 Lobster    ""          ""         
 4         4 01-04-2022 Pasta      ""          ""         
 5         5 01-05-2022 Salmon     ""          ""         
 6         6 01-01-2022 Fries      ""          ""         
 7         7 01-02-2022 Fries      ""          ""         
 8         8 01-03-2022 Roll       ""          ""         
 9         9 01-01-2022 Shake      ""          ""         
10        10 01-02-2022 Wine       ""          ""

Note that the order date of the corresponding extra_item1/2 is maintained with the new rows. The values in extra_item1/2 could be empty strings or NA. Please have a look and make a suggestion. Thanks.

tibble tidyr pivot r
1 Answers

You can stack the df in long format, replace extra item as order item, pivot_wider and unnest

Your example data doesn't provide date

library(tidyr)

cbind(orders[1], stack(orders[-1])) |> 
  transform(ind = replace(ind, values != "", "order_item")) |> 
  pivot_wider(id_cols = order_nbr, names_from = ind, values_from = values, values_fn = list) |> 
  unnest(cols = 2:4) |> 
  type.convert(as.is = TRUE)

  order_nbr order_item extra_item1 extra_item2
       <int> <chr>      <lgl>       <lgl>      
 1         1 Burger     NA          NA         
 2         1 Fries      NA          NA         
 3         1 Shake      NA          NA         
 4         2 Steak      NA          NA         
 5         2 Fries      NA          NA         
 6         2 Wine       NA          NA         
 7         3 Lobster    NA          NA         
 8         3 Roll       NA          NA         
 9         4 Pasta      NA          NA         
10         5 Salmon     NA          NA
user_197300310
Related