Extracting values from two data frames based on a the same ID column

Question

I want to create a data fame from values in two different data frame containing same values in column ID

Input data

df1<- data.frame(ID = c(102, 205, 333, 434, 512), Order1 = c("ball", 
"ball", "pen", "glass", "scale"))
df2 <- data.frame(ID = c(102, 205, 333, 434, 512), ActOrder1 = c("cap", 
"watch", "cup", "pen", "pencil"), ActOrder2 = c("cap", "fan", "phone", 
"NA",  "NA"))

for all the values of ball in the column Order1 of df1, I want to extract the values in the column ActOrder1 & Actorder2 from df2

Output

df3 <- data.frame(ID = c(102, 205), Order1 = c("ball", "ball"), 
ActOrder1 = c("cap", "watch"), ActOrder2 = c("cap", "fan") )

Possible duplicate of [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — camille, Oct 05 '19 at 18:16
I want to extract the values into a new data frame based on a value in column Order1 (ball) of df1. — Aashay Mehta, Oct 05 '19 at 18:21
Additional the values in the column "Order1" "ActOrder1" and "ActOrder2" do not match. But the corresponding value in the column "ID" matches. — Aashay Mehta, Oct 05 '19 at 18:25
Since you asked in comments below for more info on left & right joins, the post I flagged this as a duplicate of has 19 answers giving lots of details on different types of joins. Click the link ^^^ — camille, Oct 05 '19 at 21:38

akrun · Accepted Answer · 2019-10-05T18:42:57.510

1

We can use a left_join with filter

library(dplyr)
left_join(df1, df2, by = 'ID') %>%
   filter(Order1 == 'ball')  %>%
   select(names(df1), starts_with('ActOrder'))

edited Oct 05 '19 at 18:42

answered Oct 05 '19 at 18:22

akrun

874,273
37
540
662

Doesnt produce the required output. – Aashay Mehta Oct 05 '19 at 18:34
@AashayMehta if 'df3' is the expected output, the update gives the expected – akrun Oct 05 '19 at 18:38
Also, what change would I need to make if the df2 had multiple columns and I only wanted to extract values in ActOrder1 and ActOrder2? – Aashay Mehta Oct 05 '19 at 18:40
@AashayMehta Do you still want the 'ID' and 'Order1' columns – akrun Oct 05 '19 at 18:41
Yes, would still want ID and Order1 column in df3 – Aashay Mehta Oct 05 '19 at 18:42
@AashayMehta Please check my `select` statement – akrun Oct 05 '19 at 18:42
Stay tuned for more questions. Also, any link for left and right join would be appreciated. – Aashay Mehta Oct 05 '19 at 18:43
@AashayMehta If the questions are different, please post as a new one. Thanks – akrun Oct 05 '19 at 18:44
@AashayMehta You can check for vignettes from dplyr https://dplyr.tidyverse.org/ – akrun Oct 05 '19 at 18:45
GIVES THE FOLLOWING ERROR WITH ACTUAL DATA SET:- Error in get(as.character(FUN), mode = "function", envir = envir) : object 'f' of mode 'function' was not found – Aashay Mehta Oct 05 '19 at 19:32
@AashayMehta Not clear about the error. Please check your data `str(data)` to see whether the one you showed is similar to that one – akrun Oct 05 '19 at 19:33

Extracting values from two data frames based on a the same ID column

Input data

Output

1 Answers1