rilkehayden rilkehayden -3 years ago 116
R Question

Isolating rows in a dataframe whose factor levels in two columns do not match those in two columns of another dataframe

I have a simple, correct concordance table (dataframe in R) of states and state codes:

statecodeconcordance<-structure(list(State.Code = structure(1:5, .Label =
c("1", "2", "3", "4", "5"), class = "factor"), State.Name = structure(1:5,
.Label = c("Alabama", "Alaska", "Arizona", "Arkansas", "California"), class
= "factor")), .Names = c("State.Code", "State.Name"), row.names = c(NA,
-5L), class = c("data.table", "data.frame"))


which looks like this:

State.Code State.Name
1 1 Alabama
2 2 Alaska
3 3 Arizona
4 4 Arkansas
5 5 California


and then I have a dataframe with errors in it, in which some states are assigned the wrong state code (plus various unrelated columns):

sampledata<-structure(list(State.Code = structure(c(1L, 2L, 1L, 1L, 1L, 2L,
4L, 3L, 1L, 4L), .Label = c("1", "2", "3", "5"), class = "factor"),
State.Name = structure(c(1L, 1L, 1L, 1L, 1L, 2L, 3L, 3L, 4L, 4L), .Label =
c("Alabama", "Alaska", "Arizona", "California"), class = "factor"),
Unrelated.Data = structure(c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L),
.Label = c("some_data", "more_data", "other_data", "data_squared",
"alternate_data", "yet_more_data", "so_much_data",
"running_out_of_modifiers_for_data", "still_more_data", "last_data"), class
= "factor")), .Names = c("State.Code", "State.Name", "Unrelated.Data"),
class = "data.frame", row.names = c(NA, -10L))


which looks like this:

State.Code State.Name Unrelated.Data
1 1 Alabama some_data
2 2 Alabama more_data
3 1 Alabama other_data
4 1 Alabama data_squared
5 1 Alabama alternate_data
6 2 Alaska yet_more_data
7 5 Arizona so_much_data
8 3 Arizona running_out_of_modifiers_for_data
9 1 California still_more_data
10 5 California last_data


How can I subset the latter dataframe to return only those rows in which the state-code pairs do not match the pairs in the concordance table (i.e., those rows with
Alabama
in
State.Name
but a non-
1
number in
State.Code
,
Alaska
in
State.Name
but a non-
2
number in
State.Code
, etc.)?

Ideal output:

State.Code State.Name Unrelated.Data
2 2 Alabama more_data
7 5 Arizona so_much_data
9 1 California still_more_data

Answer Source

You can use dplyr package:

library(dplyr)
anti_join(sampledata, statecodeconcordance, by = c("State.Code","State.Name"))

#   State.Code State.Name  Unrelated.Data 
# 1          5    Arizona    so_much_data 
# 2          2    Alabama       more_data 
# 3          1 California still_more_data
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download