Modify data frame with condition based on another data frame in R

Question

I have two data frames:

df1 <- data.frame(Q1 = rep(c(0, 1), 3),
                  Q2 = rep(c(1, 0), 3),
                  Q3 = rep(1, 6))

df2 <- data.frame(Q1 = rep(c(0, 1, 2), 2),
                  Q2 = rep(c(2, 1, 0), 2),
                  Q3 = c(rep(1, 3), rep(2, 3)))

> df1
  Q1 Q2 Q3
1  0  1  1
2  1  0  1
3  0  1  1
4  1  0  1
5  0  1  1
6  1  0  1

> df2
  Q1 Q2 Q3
1  0  2  1
2  1  1  1
3  2  0  1
4  0  2  2
5  1  1  2
6  2  0  2

Now, I want to create a new data frame based on these two data frames, and fill the entries based on the following conditions:

new entry	condition
6	`if(df1 == 1 & df2 == 2)`
5	`if(df1 == 1 & df2 == 1)`
4	`if(df1 == 1 & df2 == 0)`
3	`if(df1 == 0 & df2 == 0)`
2	`if(df1 == 0 & df2 == 1)`
1	`if(df1 == 0 & df2 == 2)`

My problem is that I don't know how to run through both data frames at the same time to modify them or create a new data frame. My poor attempt looks like:

df3 <- modify(df1, function(x) ifelse(x == 1 & df2[x] == 2, 6,
                               ifelse(x == 1 & df2[x] == 1, 5,
                               ifelse(x == 1 & df2[x] == 0, 4,
                               ifelse(x == 0 & df2[x] == 0, 3,
                               ifelse(x == 0 & df2[x] == 1, 2, 1))))))

I know that this would never work, but this is the best I could manage. Does anyone know how to solve this? Thanks a lot!

score 0 · Answer 1 · answered Oct 27 '22 at 08:23

res <- matrix(nrow = nrow(df1), ncol = ncol(df1))
res[df1 == 1 & df2 == 2] <- 6
res[df1 == 1 & df2 == 1] <- 5
res[df1 == 1 & df2 == 0] <- 4
res[df1 == 0 & df2 == 0] <- 3
res[df1 == 0 & df2 == 1] <- 2
res[df1 == 0 & df2 == 2] <- 1
print(res)

     [,1] [,2] [,3]
[1,]    3    6    5
[2,]    5    2    5
[3,]    1    4    5
[4,]    4    1    6
[5,]    2    5    6
[6,]    6    3    6

score 0 · Accepted Answer · answered Oct 27 '22 at 08:28

You should use the dataframe names directly:

df3 <- ifelse(df1 == 1 & df2 == 2, 6,
              ifelse(df1 == 1 & df2 == 1, 5,
                     ifelse(df1 == 1 & df2 == 0, 4,
                            ifelse(df1 == 0 & df2 == 0, 3,
                                   ifelse(df1 == 0 & df2 == 1, 2, 1)))))

Otherwise, here' s a systematic solution in base R:

#Create new dataframe from df1 and df2 pasted element by element
df <- mapply(paste, df1, df2)

#Create a lookup table, 
l <- setNames(1:6, paste(rep(0:1, each = 3), c(2:0, 0:2)))
# 0 2 0 1 0 0 1 0 1 1 1 2 
#   1   2   3   4   5   6

#Match values of df and the lookup 
df[] <- l[match(df, names(l))]

#Convert to dataframe
as.data.frame(df)

#   Q1 Q2 Q3
# 1  3  6  5
# 2  5  2  5
# 3  1  4  5
# 4  4  1  6
# 5  2  5  6
# 6  6  3  6

Modify data frame with condition based on another data frame in R

2 Answers2