[R] Conversion to Adjacency Matrix

[hadley wickham]

>    I have data in the following form:
>
> ID COUPON0 COUPON1 COUPON2 COUPON3
>   1       1    1000    1001    1002
>   2       2      NA      NA      NA
>   3    1000    1003      NA    1004
>   4    1001      NA    1005      NA
>   5    1002      NA      NA      NA
> 12    1003      NA      NA    1006
>   7    1005      NA      NA      NA
>   8    1004    1007      NA      NA
>   9    1006      NA      NA      NA
> 26    1007      NA      NA      NA
>
>    I would like to convert this into an adjacency matrix like the following:

Here's one solution:

a <- data.frame(
 ID = c(1, 2, 3, 4, 5, 12, 7, 8, 9, 26),
 COUPON0 = c(1, 2, 1000, 1001, 1002, 1003, 1005, 1004, 1006, 1007),
 COUPON1 = c(1000, NA, 1003, NA, NA, NA, NA, 1007, NA, NA),
 COUPON2 = c(1001, NA, NA, 1005, NA, NA, NA, NA, NA, NA),
 COUPON3 = c(1002, NA, 1004, NA, NA, 1006, NA, NA, NA, NA)
)
names(a) <- tolower(names(a))

# Make a look up table from coupon to id
coupontoid <- a$id
names(coupontoid) <- a$coupon0

# Convert a to a more normal (in the sense of
# database normalisation) form
# (many other ways to do this)
library(reshape)
am <- melt(a, id=c("id", "coupon0"))
# Remap coupon number to id
map <- data.frame(src=am$id, dest=coupontoid[as.character(am$value)])
# Convert to adjacency matrix
xtabs(~ src + dest, map)

# Force all levels to display
map$src <- factor(map$src, levels=unique(a$id))
map$dest <- factor(map$dest, levels=unique(a$id))
xtabs(~ src + dest, map)

Regards,

Hadley