Joining R dataframe on multiple separable (concatenated) values in one cell

user_13908901 0
Viewed 23

I am trying to find a way to do a join on two dataframes but instead of a straight match ( test<-inner_join(blood, behavior, by = c("IDy" = "IDx")) ) I want to be able to have '1|3|546' be separated into individual elements and match on the join (like '042' %in% '1|2|3|042'). The only way I can think of is to create 3 additional lines with each value on a new line (1, 2, 3, 042) but I'll rather keep all in one cell. I'm not sure if this is a crazy idea.

# Creating behavior dataframe
IDx <- c('1|3|546', '1|2|3|983', '1|2|3|042', '952', '853', '061')
diet <- c("veg", "pes", "omni", "omni", "omni", "omni")
exercise <- c("high", "low", "low", "low", "med", "high")
behavior <- data.frame(IDx, diet, exercise)

# Creating blood dataframe
IDy <- c('983', '952', '853', '061', '042', '581', '249', '467', '841', '546')
blood_levels <- c(43543, 465, 4634, 94568, 850, 6840, 5483, 66452, 54371, 1347)
blood <- data.frame(IDy, blood_levels)
dataframe inner-join r join
1 Answers

We can use separate_rows from tidyr to split up the rows by the delimiter and then use inner_join

library(dplyr)
library(tidyr)
behavior %>%
   separate_rows(IDx) %>% 
   inner_join(blood, by = c("IDx" = "IDy"))

-output

# A tibble: 6 x 4
#  IDx   diet  exercise blood_levels
#  <chr> <chr> <chr>           <dbl>
#1 546   veg   high             1347
#2 983   pes   low             43543
#3 042   omni  low               850
#4 952   omni  low               465
#5 853   omni  med              4634
#6 061   omni  high            94568
user_37322710
Related