integrating 2 lists and a data farme in RR

Question

Please could you advise on the R code I could use in order to do the following operation :

1. I have 2 lists of "genome coordinates" : a list is composed by numbers that represent genome coordinates;

let's say list N:

n1    
n2    
n3    
n4

and a list M:

m1     
m2     
m3     
m4     
m5

2. and a dataframe C, where for some pairs of coordinates (n,m) from the lists above, we have a numerical intensity;

for example:

n1; m1; 100 
n1; m2; 300

The question would be : what is the most efficient R code I could use in order to integrate the list N, the list M, and the dataframe C, in order to obtain a dataframe with:

• list N as the columns names
• list M as the rows names
• the values in the cells of N * M, corresponding to the numerical values in the data frame C.

A little example would be :

     n1  n2  n3 n4 
m1  100  -   -   - 
m2  300  -   -   - 
m3   -   -   -   - 
m4   -   -   -   - 
m5   -   -   -   -

Answer 1

You can do this using spread in the tidyr package, making sure to keep all values of n and m that appear in the two lists, even if they don't appear in C:

library(tidyr)
## Replicating the data
listN = list("n1","n2","n3","n4","n5")
listM = list("m1","m2","m3","m4","m5")
C = data.frame(n=c("n1","n2","n3"),m=c("m1","m2","m3"),I=c(100,300,400))
   n  m   I
1 n1 m1 100
2 n2 m2 300
3 n3 m3 400

## Defining factor levels of C using listM and listN, and keeping unused levels when doing the spread
C$n = factor(C$n,levels=unlist(listN))
C$m = factor(C$m,levels=unlist(listM))
res = spread(C,key="n",value="I",drop=F)

This returns:

   m  n1  n2  n3 n4 n5
1 m1 100  NA  NA NA NA
2 m2  NA 300  NA NA NA
3 m3  NA  NA 400 NA NA
4 m4  NA  NA  NA NA NA
5 m5  NA  NA  NA NA NA

Answer 2

I'm sure someone has a cleaner way to implement the following, and I would be happy to know. Your data is not really MWE ready: see How to make a great R Reproducible Example. However, given the data "as-is" and assuming you don't really want the first row titled 'n1'. The following solution requires reshape2 package:

N=c('n1','n2','n3','n4')
M=c('m1','m2','m3','m4','m5')
C=data.frame(
  X1=c('n1','n1'),
  X2=c('m1','m2'),
  C=c(100, 300)
)

We've defined the data as above. Now let's merge it together.

X = merge(N, M)

Let's add NAs to the data.frame so that it will be blanked when we define the items.

C$C <- NA

C$C <- C[which(C$X1 %in% N & C$X2 %in% M),'C']

D = merge(N, M, all=TRUE)
names(D) <- c('X1','X2')
names(X) <- c('X1','X2')

E = merge(D, C, all = TRUE, by=c('X1', 'X2'))

library(reshape2)
reshape2::dcast(E, X2 + C ~ X1, drop=FALSE, value.var='C')

Hopefully this will help you out until someone else can explain it a little better.

EDIT: Since @Lamia beat me to it, I compared the system.time values for the examples. . . and @Lamia's answer is 0.01 +/- 0.032 over ten reps on my machine.

Answer 3

We could use a indexing method

m1 <- matrix(0, length(listM), length(listN), dimnames = list(unlist(listM), unlist(listN)))
m1[cbind(match(as.character(C$m), rownames(m1)), 
                  match(as.character(C$n), colnames(m1)))] <- C$I
m1
#    n1  n2  n3 n4 n5
#m1 100   0   0  0  0
#m2   0 300   0  0  0
#m3   0   0 400  0  0
#m4   0   0   0  0  0
#m5   0   0   0  0  0

data

listN <- list("n1","n2","n3","n4","n5")
listM <- list("m1","m2","m3","m4","m5")
C <- data.frame(n=c("n1","n2","n3"),m=c("m1","m2","m3"),I=c(100,300,400))