Need help figuring out - R functions

Question

I have a customer table with orders information (Table 1) I am trying to figure out a way whether we can reproduce like table 2 below using R?

Table 1

Customer Name gender order        date
1        Tony  M     Pizza        12/20/2019
1        Tony  M     onion rings  12/29/2019
1        Tony  M     Fries        12/30/2019
2        Anna  F     Pizza        1/3/2020
3        Mike  M     Pizza        12/3/2019
3        Mike  M     Fries        1/14/2020

Table 2

customer  repeated_instrument  repeated_instance name  gender  order   date 
1                                                Tony  M                                              
1         order                1                               Pizza   12/20/2019  
1         order                2                               oni_rng 12/29/2020
1         order                3                               Fries   12/30/2019
2                                                Anna  F
2         order                1                               Pizza   1/2/2020
3                                                Mike  M
3         order                1                               Pizza   12/3/2019
3         order                2                               Fries   1/4/2020

Table 1

Table 2

Please have a look at the following link to create a reproducible example: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — monte, Jul 01 '20 at 19:12

Matt · Accepted Answer · 2020-07-06T20:41:13.893

Using dplyr you can do:

library(dplyr)

df %>% 
  group_by(Name) %>% 
  mutate(repeated_instrument = "order",
         repeated_instance = row_number(Customer)) %>% 
  do(rbind(.,c(.$Customer,rep(NA,ncol(df)-1)))) %>% 
  arrange(Customer, repeated_instance) %>% 
  group_by(Customer) %>% 
  mutate(name = ifelse(is.na(order), first(Name), NA),
         gender = ifelse(is.na(order), first(gender), NA)) %>% 
  ungroup() %>% 
  mutate(repeated_instance = ifelse(is.na(order), NA, repeated_instance)) %>% 
  select(Customer, repeated_instrument, repeated_instance, name, gender, order, date) %>% 
  arrange(Customer, name, repeated_instance)

Which gives us:

  Customer repeated_instrument repeated_instance name  gender order       date      
     <dbl> <chr>                           <dbl> <chr> <chr>  <chr>       <chr>     
1        1 NA                                 NA Tony  M      NA          NA        
2        1 order                               1 NA    NA     Pizza       12/20/2019
3        1 order                               2 NA    NA     onion rings 12/29/2019
4        1 order                               3 NA    NA     Fries       12/30/2019
5        2 2                                  NA Anna  F      NA          NA        
6        2 order                               1 NA    NA     Pizza       1/3/2020  
7        3 NA                                 NA Mike  M      NA          NA        
8        3 order                               1 NA    NA     Pizza       12/3/2019 
9        3 order                               2 NA    NA     Fries       1/14/2020

Data:

structure(list(Customer = c(1, 1, 1, 2, 3, 3), Name = c("Tony", 
"Tony", "Tony", "Anna", "Mike", "Mike"), gender = c("M", "M", 
"M", "F", "M", "M"), order = c("Pizza", "onion rings", "Fries", 
"Pizza", "Pizza", "Fries"), date = c("12/20/2019", "12/29/2019", 
"12/30/2019", "1/3/2020", "12/3/2019", "1/14/2020")), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

Need help figuring out - R functions

1 Answers1