-1

I have 2 data frame that I want to compare. (I've already asked this question here, but am phrasing it differently to make it more efficient: How to find differences in elements of 2 data frames based on 2 unique identifiers)

df1<-data.frame(DS.ID=c(123,214,543,325,123,214),OP.ID=c("xxab","xxac","xxad","xxae","xxaf","xxaq"),P.ID=c("AAC","JGK","DIF","ADL","AAC","JGR"),date="20121111")

> df1
   DS.ID OP.ID P.ID     date
1   123  xxab  AAC 20121111
2   214  xxac  JGK 20121111
3   543  xxad  DIF 20121111
4   325  xxae  ADL 20121111
5   123  xxaf  AAC 20121111
6   214  xxaq  JGR 20121111

df2<-data.frame(DS.ID=c(123,214,543,325,123,214),OP.ID=c("xxab","xxac","xxad","xxae","xxaf","xxaq"),P.ID=c("AAC","JGK","DIF","ADL","AAC","JGS"),date="20121110")

> df2
  DS.ID OP.ID P.ID     date
1   123  xxab  AAC 20121110
2   214  xxac  JGK 20121110
3   543  xxad  DIF 20121110
4   325  xxae  ADL 20121110
5   123  xxaf  AAC 20121110
6   214  xxaq  JGS 20121110

The unique id is based on the combination of the DS.ID and the OP.ID, so that DS.ID can be repeated but the combination of DS.ID and OP.ID will not. I want to find the instances where P.ID changes. Also, the combination of DS.ID and OP.ID will not necessarily be in the same row.

So, first I'd rbind to create a dataframe, then I'd want to melt with dcast. I'd want to end up with columns of DS.ID and OP.ID as the unique ID, and then columns of both dates wit the values for each one.

df12         <- rbind.fill(df1,df2)
Community
  • 1
  • 1
John M.
  • 323
  • 2
  • 5
  • 13

1 Answers1

2

If all you want is to compare when there is a difference in P.ID, you can just merge by the two common columns, and then compare:

# Convert from factor to character.
df1$P.ID<-as.character(df1$P.ID)
df2$P.ID<-as.character(df2$P.ID)

# Merge 
compare.df<-merge(df1,df2,by=c('DS.ID','OP.ID'))
# Find differences.
compare.df[compare.df$P.ID.x!=compare.df$P.ID.y,]
#   DS.ID OP.ID P.ID.x   date.x P.ID.y   date.y
# 4   214  xxaq    JGR 20121111    JGS 20121110
nograpes
  • 18,623
  • 1
  • 44
  • 67
  • merge should have `all=TRUE` to account for the rows in both data frames that have no match at all. – topchef Nov 11 '13 at 21:36
  • @topchef Perhaps. I thought that the point was to determine only those rows where the `DS.ID` and `OP.ID` appeared in both tables, but the `P.ID` was different. If the user desired to also determine those situations where the `DS.ID` and `OP.ID` did not appear in both tables, you would be correct, but then I would also have to deal with the `NA` in the comparison. – nograpes Nov 12 '13 at 14:17