Skip to content

Data frame to matrix - revisited

3 messages · Jagz Bell, Jean V Adams, Dennis Murphy

Original
Jagz Bell
# 
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
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:
+ 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)
'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])
'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: