CODEWITHSUNDEEP
rmatrix
jalapic
jalapic

Reputation: 14202

Adding to a numeric matrix with data from a dataframe

I'm wondering if I am missing a faster way of doing the following, other than doing a laborious loop.

Say I have a matrix like this:

m1 <- structure(c(0, 2, 2, 1, 0, 1, 1, 1, 0), .Dim = c(3L, 3L), .Dimnames = list(
    c("AK", "JW", "SZ"), c("AK", "JW", "SZ")))

#m1
#    AK JW SZ
# AK  0  1  1
# JW  2  0  1
# SZ  2  1  0

Now, I want to add values from the following dataframe. Here you can see individuals listed in 'id1' and 'id2' and a value to add in 'val'. 

dfx <- structure(list(id1 = c("JW", "SZ", "SZ"), id2 = c("AK", "AK", 
"JW"), val = c(1.5, 2.5, 1)), .Names = c("id1", "id2", "val"), row.names = c(NA, 
-3L), class = "data.frame")

#dfx
#  id1 id2 val
#1  JW  AK 1.5
#2  SZ  AK 2.5
#3  SZ  JW 1.0

Each value should be added to the respective cell of the matrix e.g 2.5 should be added to m1[SZ, AK]. However, the same value should be added to the transposed cell, i.e. 2.5 should also be added to m1[AK, SZ].

The resulting matrix should look like:

#    AK  JW  SZ
#AK 0.0 2.5 3.5
#JW 3.5 0.0 2.0
#SZ 4.5 2.0 0.0

This can be achieved for instance by creating a loop that essentially does this for every row of 'dfx':

m1[rownames(m1)=="JW",rownames(m1)=="AK"] <- m1[rownames(m1)=="JW",rownames(m1)=="AK"] + 1.5
m1[rownames(m1)=="AK",rownames(m1)=="JW"] <- m1[rownames(m1)=="AK",rownames(m1)=="JW"] + 1.5

Any ideas/pointers for a better way appreciated:

Upvotes: 1

Views: 63

Answers (3)

IRTFM
IRTFM

Reputation: 263411

Using the cbind(x,y) as an index for assignment is a standard R strategy:

 m1[with(dfx, cbind(id1,id2)) ] <- m1[with(dfx, cbind(id1,id2)) ]+ # the prior values
                                   dfx$val                         # the new values
 m1
#-------------------
    AK JW SZ
AK 0.0  1  1
JW 3.5  0  1
SZ 4.5  2  0
# Step 2 for the transposed addition
m1[with(dfx, cbind(id2,id1)) ] <- m1[with(dfx, cbind(id2,id1)) ]+ 
                                    dfx$val
m1
#---------
    AK  JW  SZ
AK 0.0 2.5 3.5
JW 3.5 0.0 2.0
SZ 4.5 2.0 0.0

Upvotes: 2

Henrik
Henrik

Reputation: 67778

A possible base alternative where the data frame is reshaped to the same dimensions as the matrix. This is achieved by first converting 'id1' and 'id2' to factors with levels picked from 'm1'. Then xtabs is used for reshaping. The result and its transpose are added to 'm1'.

dfx[ , c("id1", "id2")] <- lapply(dfx[ , c("id1", "id2")], function(x) factor(x, levels = rownames(m1)))
m2 <- xtabs(val ~ ., data = dfx)
m1 + m2 + t(m2)

#     AK  JW  SZ
# AK 0.0 2.5 3.5
# JW 3.5 0.0 2.0
# SZ 4.5 2.0 0.0

Upvotes: 1

akrun
akrun

Reputation: 887501

You could try

library(reshape2)
Un1 <- sort(unique(unlist(dfx[,1:2])))
dfy <- expand.grid(id1=Un1, id2=Un1)
m2 <- acast(merge(dfx,dfy, all=TRUE), id1~id2, value.var='val', fill=0)
m2[upper.tri(m2)] <- m2[lower.tri(m2)]
m1+m2
#    AK  JW  SZ
#AK 0.0 2.5 3.5
#JW 3.5 0.0 2.0
#SZ 4.5 2.0 0.0

Or you may try data.table

library(data.table)
 m2 <- acast(setkey(setDT(dfx), id1, id2)[
      CJ(id1=Un1, id2=Un1)], id1~id2, value.var='val', fill=0)
 m2+t(m2)+m1
 #    AK  JW  SZ
 #AK 0.0 2.5 3.5
 #JW 3.5 0.0 2.0
 #SZ 4.5 2.0 0.0

Upvotes: 0

Related Questions