How to add a tibble column based on information from another tibble

Question

I have the following two tibbles:

my_stats_tbl <- tribble(
  ~year,   ~model_id,
  2011,      "132",
  2012,      "145,167",
  2013,      "132,145,167",
  2014,      "132,174",
  2015,      "174,182,183",
  2016,      "183",
  2017,      "191"
)

and

model_tbl <- tribble(
  ~id,      ~name,
  132,      "Race",
  145,      "Out",
  167,      "Lazer",
  174,      "Wow",
  182,      "Super",
  183,      "Tornado",
  191,      "Cloud"
)

I would like to add another column to the first one so that I can list the model names based on the values from the second tibble. This is the expected output should look like this:

my_new_stats_tbl <- tribble(
  ~year,   ~model_id,            ~model_name,
  2011,      "132",                "Race",
  2012,      "145,167",            "Out,Lazer",
  2013,      "132,145,167",        "Race,Out,Lazer",
  2014,      "132,174",            "Race,Wow",
  2015,      "174,182,183",        "Wow,Super,Tornado",
  2016,      "183",                "Tornado",
  2017,      "191",                "Cloud"
)

How can I achieve that?

thanks,

[Separate rows](https://stackoverflow.com/q/13773770/5325862) + [join](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) + [collapse by group](https://stackoverflow.com/q/38514988/5325862) — camille, Nov 21 '19 at 21:17

score 2 · Accepted Answer · answered Nov 21 '19 at 21:22

Here is a solution, following Camille's comment:

library(tidyr)
library(dplyr)
#make tidy
newstats<-my_stats_tbl %>% separate_rows(model_id, sep=",") 
#convert id from character to number
newstats$model_id<-as.integer(newstats$model_id)
#join tables
newstats<-left_join(newstats, model_tbl, by=c("model_id" = "id"))
#make untidy
newstats %>% group_by(year) %>%  summarize(model_id = paste0(model_id, collapse = ","), name=paste0(name, collapse = ",")) 

# # A tibble: 7 x 3
#   year model_id    name             
#   <dbl> <chr>       <chr>            
# 1  2011 132         Race             
# 2  2012 145,167     Out,Lazer        
# 3  2013 132,145,167 Race,Out,Lazer   
# 4  2014 132,174     Race,Wow         
# 5  2015 174,182,183 Wow,Super,Tornado
# 6  2016 183         Tornado          
# 7  2017 191         Cloud

How to add a tibble column based on information from another tibble

1 Answers1