R: breaking down column based on values of first row

user_19380859 0
Viewed 28

I have a data set of two columns (order_id and product_id) like this:

order_id | product_id

  2  | 33120
  2  | 28985
  2  | 25632
  2  | 78422
  2  | 58874
  3  | 9841
  3  | 11254
  3  | 98746
  3  | 1123
  4  | 1856
  4  | 47896
  4  | 78967

and I would like to create a data frame that has the product_id's in seperate columns based on the order_id. The final result should look like:

2 | 33120 | 28985 | 25632 | 78422 | 58874 | NA

3 | 9841 | 11254 | 98746 | 1123 | NA | NA

4 | 1856 | 47896 | 78967 | NA | NA | NA

The way I did it right now is I made an empty data frame with all NA's (with number of columns 50 because that is the maximum number of product id's per order id) and created a for loop that looped over the order id's to make a subset and then put it in the empty data frame but my data set is to large to do it this way so it would take days.

This is the code now:

baskets <- matrix(data = NA,nrow = 3048562,ncol = 50) # baskets of max 50 products

for (i in order_products_train$order_id) {
  vector <- subset(order_products_train, subset = order_products_train$order_id == i)
  vector <- t(vectors$product_id)
  baskets[i,1:length(vectors)] <- vectors
}

Please help me to find a solution. Thank you in advance!

r
1 Answers

I am not sure how you want to call your column names, but you could do something like this:

library(dplyr)
library(tidyr)
df %>%
  group_by(order_id) %>% 
  mutate(col = row_number(),
         sub_id = row_number()) %>% 
  unite("product_id_order", col, sub_id ,remove = TRUE) %>% 
  spread(product_id_order, product_id)

Output:

# A tibble: 3 × 6
# Groups:   order_id [3]
  order_id `1_1` `2_2` `3_3` `4_4` `5_5`
     <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1        2 33120 28985 25632 78422 58874
2        3  9841 11254 98746  1123    NA
3        4  1856 47896 78967    NA    NA
user_142827140
Related