hoho hoho - 9 months ago 33
R Question

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

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?

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download