-1

I have a data as follows -

user_id     post_id
24376261    204506440
98461       204446324
98461       203026202
98461       203031838
311542      204351465
875740      203031838

This data indicates that posts on which a user has comment in a website's article/post. Now, I need to create a matrix with user_id in rows and columns and values will be 1 if these users are connected through a blog post else 0. i.e the output I want will look like this -

user       24376261 98461   311542  875740
24376261    1       0       0       0
98461       0       1       0       1
311542      0       0       1       0
875740      0       1       0       1

How can I do this in R? I tried following Brian method from this question - Adjacency matrix in R But I get an R object of the following class - 

> class(am)
[1] "dgCMatrix"
attr(,"package")

How can I convert this into a data.frame or something that can be exported to R?

Community
  • 1
  • 1
RHelp
  • 815
  • 2
  • 8
  • 23

1 Answers1

1

Here's an approach that gets you your desired output:

tcrossprod(table(mydf))
#           user_id
# user_id    98461 311542 875740 24376261
#   98461        3      0      1        0
#   311542       0      1      0        0
#   875740       1      0      1        0
#   24376261     0      0      0        1
(tcrossprod(table(mydf)) != 0) + 0
#           user_id
# user_id    98461 311542 875740 24376261
#   98461        1      0      1        0
#   311542       0      1      0        0
#   875740       1      0      1        0
#   24376261     0      0      0        1

If you want the result as a data.frame, you can wrap the output in as.data.frame.matrix.

This is assuming that "mydf" is defined as:

mydf <- structure(list(user_id = c(24376261L, 98461L, 98461L, 98461L, 
311542L, 875740L), post_id = c(204506440L, 204446324L, 203026202L, 
203031838L, 204351465L, 203031838L)), .Names = c("user_id", "post_id"), 
class = "data.frame", row.names = c(NA, -6L))
A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
  • Sorry, this doesn't give me the same result as yours. Can you tell me what is in the mydf table? – RHelp Apr 16 '14 at 15:43
  • @RHelp, ***you're*** the one that is supposed to be providing a reproducible example, not the person answering. Nevertheless, I've added an update with what I used as "`mydf`". – A5C1D2H2I1M1N2O1R2T1 Apr 18 '14 at 08:56
  • I have added my data in my question. And thanks for your patience. But this doesn't work on my data set. If I try you solution, I get useri_id and post_id in my rows as well as my columns and all values filling the matrix are 1 in all combinations. – RHelp Apr 18 '14 at 11:01
  • @RHelp, you pasted a *view* of your data, not anything that tells us the structure of your data. Use `dput` to share your data in a reproducible manner. – A5C1D2H2I1M1N2O1R2T1 Apr 18 '14 at 11:49