CODEWITHSUNDEEP
rmatrixreshaper-corrplot
UserYmY
UserYmY

Reputation: 8574

Reshape categorical data that looks like a correlation matrix

I have a df:

cc pair_no
US   1
US   1
DE   2
UK   2
US   3
PL   3
US   4
US   4

I would like to create a matrix as such, basically to count the number of pairs per country combination:

    US  UK  PL DE
US  2
UK  0   0   
PL  1   0    0
DE  0   1    0   0

I have tried using but it does not give the desired results.

Table(df$cc,df$pair_no)

Can anybody help?

Upvotes: 0

Views: 60

Answers (1)

nicola
nicola

Reputation: 24520

You can try:

table(data.frame(cc1=df$cc[c(TRUE,FALSE)],cc2=df$cc[c(FALSE,TRUE)]))
#    cc2
#cc1  DE PL UK US
#  DE  0  0  1  0
#  PL  0  0  0  0
#  UK  0  0  0  0
#  US  0  1  0  2

Data

df<-structure(list(cc = structure(c(4L, 4L, 1L, 3L, 4L, 2L, 4L, 4L
), .Label = c("DE", "PL", "UK", "US"), class = "factor"), pair_no = c(1L, 
1L, 2L, 2L, 3L, 3L, 4L, 4L)), .Names = c("cc", "pair_no"), class = "data.frame", row.names = c(NA, 
-8L))

Upvotes: 2

Related Questions