conditional replace using match of column/row names from other data frame

Question

I have two data frames:

id <- c("a", "b", "c")
a <- 0
b <- 0 
c <- 0
df1 <- data.frame(id, a, b, c)

  id a b c
1  a 0 0 0
2  b 0 0 0
3  c 0 0 0

num <- c("a", "c", "c")
partner <- c("b", "b", "a")
value <- c("10", "20", "30")
df2 <- data.frame(num, partner, value)

  num partner value
1   a       b    10
2   c       b    20
3   c       a    30

I'd like to replace zeroes in df1 with df2$value in every instance df1$id==df2$num & colnames(df1)==df2$partner. So the output should look like:

a <- c(0, 0, 30)
b <- c(10, 0, 20)
c <- c(0, 0, 0)
df.nice <- data.frame(id, a, b, c)

  id  a  b c
1  a  0 10 0
2  b  0  0 0
3  c 30 20 0

I can replace individual cells with the following:

df1$b[df1$id=="a"] <- ifelse(df2$num=="a" & df2$partner=="b", df2$value, 0)

but I need to cycle through all possible df1 row/column combinations for a large data frame. I suspect this involves plyr and match together, but can't quite figure out how.

Update

Thanks to @MikeH., I've turned to using reshape. This seems to work:

df.nice <- melt(df2, id=c("num", "partner"))
df.nice <- dcast(test.nice, num ~ partner, value.var="value")

to produce this:

  num    a  b
1   a <NA> 10
2   c   30 20

I do need all possible row/column combinations, however, with all represented as zero. Is there a way to ask reshape to obtain rows and columns from another data frame (e.g., df1) or do should I bind those after reshaping?

Do you really need a replace or are you actually just trying to reshape `df2` into the format of `df.nice`? — Mike H., Aug 16 '17 at 14:41
@Mike H. reshape works if all possible row/column combinations are included. Will try now, thanks — hoho, Aug 16 '17 at 14:48
@hoho if you want to use reshape, follow the solution in Henrik's linked duplicate. This will require some set up work though with re-leveling your factors. Something like this would work: `df2$num <- factor(df2$num, levels = unique(c(levels(df1$id), levels(df2$num)))); df2$partner <- factor(df2$partner, levels = unique(c(levels(df1$id), levels(df2$partner)))); df2$value <- as.character(df2$value); reshape2::dcast(df2, num ~ partner, value.var="value", drop = F, fill = 0)` — Mike H., Aug 16 '17 at 15:09
Alternatively, you could try a non-reshape solution like I posted below — Mike H., Aug 16 '17 at 15:09

score 1 · Accepted Answer · answered Aug 16 '17 at 14:56

If you want a replace (rather than a reshape) I think a simple base R solution would be to do:

idxs <- t(mapply(cbind, match(df2$num, df1$id), match(df2$partner, names(df1))))
df1[idxs] <- df2$value

df1
  id  a  b c
1  a  0 10 0
2  b  0  0 0
3  c 30 20 0

Note that I build the row/column combination lookups to replace using the t(mapply(...)). When you select like df1[idxs] this converts to matrix (to select specific row/column combinations) and then converts back to data.frame.

I had to read in your data using stringsAsFactors = FALSE so the values would register properly (instead of numerics).

Data:

df2 <- data.frame(num, partner, value, stringsAsFactors = F)
df1 <- data.frame(id, a, b, c, stringsAsFactors = F)

conditional replace using match of column/row names from other data frame

1 Answers1