Filtering Rows from a Dataset Based On the Condition that Values for select Columns match with Another Dataset

[Micaela]

I have a raw data file (data) that I would like to subset based on matching an objective (Objective_Value) to an associated Objective given in a reference table (Analytes) for every AnalyteName.

Here are two example datasets. The actual datasets I am using are much larger and it is essential that all columns are maintained while extraneous rows are removed.

data<- (AnalyteName = c("Boron", "Boron", "Boron", "Arsenic", "Arsenic", "Arsenic", "Copper", "Copper", "Copper"), Result = c(0.6, 0.4, 0.3, 0.3, 0.1, 0.3, 0.6, 0.4, 0.3), Objective_Value=c(100,200,300,100,200,300,100,200,300), SampleDate = c('3/19/2013', '12/6/2011', '6/15/2015', '4/17/2013', '4/5/2011', '2/21/2013', '3/19/2013', '12/6/2011', '6/15/2015'))

Analytes <- (AnalyteName=c("Boron", "Arsenic", "Copper"), Objective=c(100,200,300))

I would like the resulting datafile to look like this:

data<-(AnalyteName = c("Boron", "Arsenic", "Copper"), Result = c(0.6, 0.1, 0.3), Objective_Value=c(100,200,300), SampleDate = c('3/19/2013', '4/5/2011', '6/15/2015'))

I tried to do this by subsetting based on two conditions:

data <- data[(data$AnalyteName == Analytes$AnalyteName) & (data$Objective_Value == Anlaytes$Objective), ]

I think that I may have to write an if or ifelse statement to make this work correctly. Any help is greatly appreciated!

[scottyd22]

It looks like you had a typo in your code.

data <- data[(data$AnalyteName == Analytes$AnalyteName) & (data$Objective_Value == Anlaytes$Objective), ]

data[(data$AnalyteName == Analytes$AnalyteName) & (data$Objective_Value == Analytes$Objective), ]
#> # A tibble: 3 × 4
#>   AnalyteName Result Objective_Value SampleDate
#>   <chr>        <dbl>           <dbl> <chr>     
#> 1 Boron          0.6             100 3/19/2013 
#> 2 Arsenic        0.1             200 4/5/2011  
#> 3 Copper         0.3             300 6/15/2015

Created on 2022-09-16 with reprex v2.0.2.9000