CODEWITHSUNDEEP
rstata
Sun
Sun

Reputation: 179

creating a matrix from data

I have a data that has three columns which look like this:

ppl loc1 loc2 
 1   US  CH   
 1   US  KR  
 1   US  CAN 
 1   CAN KR
 1   KR  CH
 1   CH  US
 1   CAN CH
 1   US  US

I want to create a matrix that looks something like this: 

       US CH KR CAN
    US 1  2  1  1  
    CH 2  0  1  1
    KR 1  1  0  0
   CAN 1  1  1  0

Any suggestions would be amazing.

Upvotes: 0

Views: 116

Answers (3)

gaut
gaut

Reputation: 5958

R answer:

You can matrix for numeric vectors only. in your case since you have strings, you should use data.frame like so:

ppl <- rep(1,8)
loc1 <- c("US", "US", "US", "CAN", "KR", "CH", "CAN", "US")
loc2 <- c("CH","KR"  ,"CAN" ,"KR","CH","US","CH","US")

mat <- matrix(ppl, loc1, loc2) #error
df <- data.frame(ppl, loc1, loc2, stringsAsFactors = F)
> df
  ppl loc1 loc2
1   1   US   CH
2   1   US   KR
3   1   US  CAN
4   1  CAN   KR
5   1   KR   CH
6   1   CH   US
7   1  CAN   CH
8   1   US   US

Upvotes: 1

Santiago I. Hurtado
Santiago I. Hurtado

Reputation: 1123

R answer:

This should work: y <- unique(data[,2])

x <- unique(data[,3])
mat <- matrix(NA,length(x),length(y))

for(i in 1:nrow(mat)){
  for(j in 1:rcol(mat)){
    mat[i,j] <- sum(as.numeric(data[,2] == y[j] & data[,3] == x[i]))
  }
}

Upvotes: 1

Nick Cox
Nick Cox

Reputation: 37208

Stata answer: 

clear 
input ppl str3 (loc1 loc2) 
 1   US  CH   
 1   US  KR  
 1   US  CAN 
 1   CAN KR
 1   KR  CH
 1   CH  US
 1   CAN CH
 1   US  US
 end 

 tab loc1 loc2, matcell(whatever) 

 mat li whatever 

whatever[4,4]
    c1  c2  c3  c4
r1   0   1   1   0
r2   0   0   0   1
r3   0   1   0   0
r4   1   1   1   1

See help matrix for adding row and column names.

Upvotes: 1

Related Questions