[R] Data frame to matrix - revisited

[Dennis Murphy]

Hi:

Here are a couple of ways. Since your data frame does not contain a
'c' in ID2, we redefine the factor to give it all five levels rather
than the observed four:

> df <- read.table(textConnection("
+ ID1 ID2 Value
+ a b 1
+ b d 1
+ c a 2
+ c e 1
+ d a 1
+ e d 2"), header = TRUE)
str(df)
> str(df)
'data.frame':   6 obs. of  3 variables:
 $ ID1  : Factor w/ 5 levels "a","b","c","d",..: 1 2 3 3 4 5
 $ ID2  : Factor w/ 4 levels "a","b","d","e": 2 3 1 4 1 3
 $ Value: int  1 1 2 1 1 2

df$ID2 <- factor(df$ID2, levels = letters[1:5])
> str(df)
'data.frame':   6 obs. of  3 variables:
 $ ID1  : Factor w/ 5 levels "a","b","c","d",..: 1 2 3 3 4 5
 $ ID2  : Factor w/ 5 levels "a","b","c","d",..: 2 4 1 5 1 4
 $ Value: int  1 1 2 1 1 2

Now we're good...

# (1) xtabs:
with(df, xtabs(Value ~ ID1 + ID2) + xtabs(Value ~ ID2 + ID1))
   ID2
ID1 a b c d e
  a 0 1 2 1 0
  b 1 0 0 1 0
  c 2 0 0 0 1
  d 1 1 0 0 2
  e 0 0 1 2 0

# (2) acast() in the reshape2 package:
library('reshape2')
v1 <- acast(df, ID1 ~ ID2, value_var = 'Value', drop = FALSE, fill = 0)
v2 <- acast(df, ID2 ~ ID1, value_var = 'Value', drop = FALSE, fill = 0)
v <- v1 + v2
v[v == 0L] <- NA
v
   a  b  c  d  e
a NA  1  2  1 NA
b  1 NA NA  1 NA
c  2 NA NA NA  1
d  1  1 NA NA  2
e NA NA  1  2 NA

HTH,
Dennis


On Tue, Aug 2, 2011 at 10:00 AM, Jagz Bell <jagzbell at yahoo.com> wrote:
> Hi,
> I've tried to look through all the previous related Threads/posts but can't find a solution to what's probably a simple question.
>
> I have a data frame comprised of three columns e.g.:
>
> ID1 ID2 Value
> a b 1
> b d 1
> c a 2
> c e 1
> d a 1
> e d 2
>
> I'd like to convert the data to a matrix i.e.:
>
>  a b c d e
> a n/a 1 2 1 n/a
> b 1 n/a n/a 1 n/a
> c 2 n/a n/a n/a 1
> d 1 1 n/a n/a 2
> e n/a n/a 1 2 n/a
>
> Any help is much appreciated,
>
> Jagz
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>