CODEWITHSUNDEEP
r
duckman
duckman

Reputation: 747

convert 3 columns data frame to matrix

I have the following input:

A   B   0
A   C   1
B   C   1

Which I would like to convert to a 3x3 matrix:

    A   B   C
A   0   0   1
B   0   0   1
C   1   1   0

How should I do this in R? The above is just to give an idea of what I aim to do. Below is my data. I cut this down to 15 obs but my dataset has 500 and the ticker set are the same for ticker1 and ticker2. Thus I would want an n x n matrix.

ticker1 ticker2 edge
AEPI    AVAV    0.044210322
AEPI    BOKF    0.008021954
AEPI    CMSB    0.001986887
AEPI    RON     0.006777085
AEPI    CAKE    0.045002911
APPL    COH     0.042760407
APPL    SWZA    0.040913242
APPL    DRIV    0.047699102
APPL    CXP     0.014103206
MSCR    FIVN    0.008781475
MSCR    HOS     0.040222645
MSCR    HDSN    0.010454853
MSCR    IPWR    0.003376232
MSCR    KELYB   0.000755429

Upvotes: 0

Views: 867

Answers (2)

CER
CER

Reputation: 889

Another way with igraph and data from @akrun

df1 <- structure(list(v1 = c("A", "A", "B"), v2 = c("B", "C", "C"), 
                      v3 = c(0L, 1L, 1L)), .Names = c("v1", "v2", "v3"), class = "data.frame", row.names = c(NA, -3L))

library(igraph)

g=graph.data.frame(df1,directed=FALSE)
get.adjacency(g,attr='v3',spars=FALSE)

Edit 1

    df2 <- read.table(text="ticker1 ticker2 edge
               AEPI    AVAV    0.044210322
               AEPI    BOKF    0.008021954
               AEPI    CMSB    0.001986887
               AEPI    RON     0.006777085
               AEPI    CAKE    0.045002911
               APPL    COH     0.042760407
               APPL    SWZA    0.040913242
               APPL    DRIV    0.047699102
               APPL    CXP     0.014103206
               MSCR    FIVN    0.008781475
               MSCR    HOS     0.040222645
               MSCR    HDSN    0.010454853
               MSCR    IPWR    0.003376232
               MSCR    KELYB   0.000755429",header=TRUE)

g=graph.data.frame(df2,directed=FALSE)
get.adjacency(g,attr='edge',spars=FALSE)

Upvotes: 1

akrun
akrun

Reputation: 886938

We can use base R methods. If the first two columns are not factor class, convert it to factor with levels specified so that both the columns have the same levels. Then use xtabs to convert it to 'wide' format and change the values in the lower half by adding with the transpose of the 'm1'

df1[1:2] <- lapply(df1[1:2], factor, levels = LETTERS[1:3])
m1 <- xtabs(v3~v1 + v2, df1)
names(dimnames(m1)) <- NULL
m1 + t(m1)
#  A B C
#A 0 0 1
#B 0 0 1
#C 1 1 0

data

df1 <- structure(list(v1 = c("A", "A", "B"), v2 = c("B", "C", "C"), 
v3 = c(0L, 1L, 1L)), .Names = c("v1", "v2", "v3"), class = "data.frame", 
 row.names = c(NA, -3L))

Upvotes: 4

Related Questions