CODEWITHSUNDEEP
rdataframe
RalfB
RalfB

Reputation: 583

R: Multiple Comparison across dataframes

I have two dataframes in R (the orginal data is few 100k lines long and has 100s of categories):

dfBase = data.frame(category=c(1,1,1,2,2,2), id=c(10000, 500, 8000, 500,8000,10000), rank=c(1,2,3,1,2,3))
dfTest = data.frame(category=c(1,1,1,2,2,2), id=c(500, 10000, 8000, 10000, 8000, 500), rank=c(1,2,3,1,2,3))

and all I want to do it to substitute the id with the rank of the baseline, only if the two conditions (category and id) match. I have this code:

dfTest$category[dfBase$category == dfTest$category & dfBase$id == dfTest$id] <- dfBase$rank

I get the error:

number of items to replace is not a multiple of replacement length

However, I am having the same dimensions in both dataframes. Some of the values are substituted, but some are being skipped. I know too little about R to make sense of this, so I hope you can help me out.

Upvotes: 0

Views: 83

Answers (2)

akrun
akrun

Reputation: 887891

Create a logical index as you did in the post.

 indx <- dfBase$category==dfTest$category & dfBase$id==dfTest$id

Then, use that index on both lhs and rhs part of the <-. 

 dfTest$category[indx] <- dfBase$rank[indx]
 dfTest
 #    category    id rank
 #1        1   500    1
 #2        1 10000    2
 #3        3  8000    3
 #4        2 10000    1
 #5        2  8000    2
 #6        2   500    3

If you have multiple columns to compare (>2), you could use Reduce

v1 <- c('category', 'id')
indx1 <- Reduce(`&`, lapply(v1, function(x) dfBase[,x]==dfTest[,x]))
dfTest$category[indx1] <- dfBase$rank[indx1]

Benchmarks

Using the original datasets

df1 <- dfBase[rep(1:nrow(dfBase),1e5),]
df2 <- dfTest[rep(1:nrow(dfTest),1e5),]

f1 <- function() {df2$category <- ifelse(df1$category==df2$category &
                        df1$id==df2$id, df1$rank, df2$category)}

 f2 <- function() { indx <- df1$category==df2$category & df1$id==df2$id
                   df2$category[indx] <- df1$rank[indx]
                 }  

 library(microbenchmark)
 microbenchmark(f1(), f2(), unit="relative")
 #Unit: relative
 #expr      min       lq     mean   median       uq      max neval
 #f1() 3.746782 3.967783 3.698517 3.850763 3.435416 2.224864   100
 #f2() 1.000000 1.000000 1.000000 1.000000 1.000000 1.000000   100

Upvotes: 1

user4004829
user4004829

Reputation:

Try:

dfTest$category<-ifelse(dfBase$category==dfTest$category & dfBase$id==dfTest$id, dfBase$rank, dfTest$category)

Upvotes: 1

Related Questions