pivot wider while keeping the order

Question

I have the following dataset

order_id <- c(2,2,2,3,3,3)
product_name <- c("banana","Garlic Powder","carrots","milk","banana","Coconut Butter")


(df <- data.frame(order_id,product_name))


  order_id   product_name
1        2         banana
2        2  Garlic Powder
3        2        carrots
4        3           milk
5        3         banana
6        3 Coconut Butter

And I want to make this wider where each of the products related to that order_id is a row.

I used the following function

df%>%
  pivot_wider(names_from =product_name,values_from = product_name)

which gives me this

  order_id banana `Garlic Powder` carrots milk  `Coconut Butter`
     <dbl> <chr>  <chr>           <chr>   <chr> <chr>           
        2 banana Garlic Powder   carrots NA    NA              
        3 banana NA              NA      milk  Coconut Butter

But I want it to be in the order that the products are in before widening the dataset, and I don't need to have column names associated with those values. I need something like this

   order_id item1     item2         item3
       2    banana  Garlic Powder   carrots                  
       3    milk    banana          Coconut Butter

How do I go about doing this?

score 0 · Accepted Answer · answered Feb 15 '22 at 00:25

Here is one solution:

library(tidyverse)

order_id <- c(2,2,2,3,3,3)
product_name <- c("banana","Garlic Powder","carrots","milk","banana","Coconut Butter")


df <- data.frame(order_id,product_name) %>% group_by(order_id) %>%
  mutate(id = paste0("item", row_number())) %>%
  pivot_wider(id_cols = order_id,  values_from = product_name, names_from = id)

score 0 · Answer 2 · answered Feb 15 '22 at 00:36

Base R Solution


order_id <- c(2,2,2,3,3,3)
product_name <- c("banana","Garlic Powder","carrots","milk","banana","Coconut Butter")

df <- data.frame(order_id,product_name)

# Create a new variable
df$item <- NA

for(val in unique(df$order_id)){
  df$item[df$order_id==val]<-seq(1,sum(df$order_id==val))
}

# Pivot to wide with base R reshape
reshape(df,
        direction="wide", 
        idvar="order_id", 
        timevar="item")

Output

  order_id product_name.1 product_name.2 product_name.3
1        2         banana  Garlic Powder        carrots
4        3           milk         banana Coconut Butter

pivot wider while keeping the order

2 Answers2

Base R Solution

Output