I have two data sets that are supposed to be the same size but aren't. I need to trim the values from A that are not in B and vice versa in order to eliminate noise from a graph that's going into a report. (Don't worry, this data isn't being permanently deleted!)
I have read the following:
- Selecting columns in R data frame based on those *not* in a vector
- http://www.ats.ucla.edu/stat/r/faq/subset_R.htm
- How to combine multiple conditions to subset a data-frame using "OR"?
But I'm still not able to get this to work right. Here's my code:
bg2011missingFromBeg <- setdiff(x=eg2011$ID, y=bg2011$ID)
#attempt 1
eg2011cleaned <- subset(eg2011, ID != bg2011missingFromBeg)
#attempt 2
eg2011cleaned <- eg2011[!eg2011$ID %in% bg2011missingFromBeg]
The first try just eliminates the first value in the resulting setdiff vector. The second try yields and unwieldy error:
Error in `[.data.frame`(eg2012, !eg2012$ID %in% bg2012missingFromBeg)
: undefined columns selected
Per the comments to the original post, merges / joins are well-suited for this problem. In particular, an inner join will return only values that are present in both dataframes, making the
setdiff
statement unnecessary.Using the data from Dinre's example:
In base R:
Using the dplyr package:
To keep the data as two separate tables, each containing only its own variables, this subsets the unwanted table to only its index variable before joining. Then no new variables are added to the resulting table.
If you really just want to subset each data frame by an index that exists in both data frames, you can do this with the 'match' function, like so:
This is, though, the same as:
Here is a demo:
This will give you what you want:
The error in your second attempt is because you forgot the
,
In general, for convenience, the specification
object[index]
subsets columns for a 2dobject
. If you want to subset rows and keep all columns you have to use the specificationobject[index_rows, index_columns]
, whileindex_cols
can be left blank, which will use all columns by default.However, you still need to include the
,
to indicate that you want to get a subset of rows instead of a subset of columns.Really human comprehensible example (as this is the first time I am using %in%), how to compare two data frames and keep only rows containing the equal values in specific column:
Results: