0

I have the following data frames

l1=data.frame(c1=c("drug1","drug4","drug2","drug1","drug3","drug9"),c2=c("headache","lung cancer","lymphoma","sprain","depression","ptsd"))

l2=data.frame(x1=c("bone cancer","sprain","lung cancer","headache","ptsd","bipolar","lymphoma","depression"),
              group=c("cancers","pain","cancers","pain","mental disorder","mental disorder","cancers","mental disorder"))

I want to map the features in c2 of l1(the first dataframe) to x1 of l2(second dataframe) such that a new column is created which contains the proper group (according to group column in l2) of each feature in c2(first column l1) The output should look something like this `

l3= data.frame(col11=c("drug1","drug4","drug2","drug1","drug3","drug9"),col2=c("headache","lung cancer","lymphoma","sprain","depression","ptsd"),
               col3=c("pain","cancers","cancers","pain","mental disorder","mental disorder"))

` Both the dataframes contain a large number of rows Thanks!

Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
niks1
  • 15
  • 3
  • `l1 %>% left_join(l2, by = c("c2" = "x1"))`, with perhaps a little renaming at the end. And I can't tell what type of join you want. `left_join` will keep all the rows from `l1`, `right_join` will keep all the rows from `l2`, `inner_join` will discard any rows that don't have a match, `full_join` will keep all rows from both whether or not they have a match. – Gregor Thomas Oct 27 '22 at 05:47
  • @GregorThomas i want to keep both the columns in l1 and only the group column from l2. Such that the new dataframe should contain c1(drug), c2(symptoms) and group which are mapped according to the second dataframe, – niks1 Oct 27 '22 at 06:51
  • Yes, the above code keeps the correct columns. The question of the type of join is not which columns to keep, but which rows. If all `l1$c2` values have a match in `l2$x1` and vice versa, then all the joins will do the same thing. But if there are values in `l1$c2` that don't have matches, or `l2$x1` values that don't have matches, what do you want to happen? `left_join` will keep all the `l1$c2` values no matter what, and add in the appropriate values from `l2` whenever they match. `inner_join` will only keep rows with matches. Etc. – Gregor Thomas Oct 27 '22 at 13:25
  • The different types of join a very general data wrangling concepts and this terminology is used in many languages. [This is a good resource to help understand](https://www.w3schools.com/sql/sql_join.asp). – Gregor Thomas Oct 27 '22 at 13:29

0 Answers0