R create a matrix of occurrences from a table

Question

I have a data set which contains the list of users and corresponding articles consulted like:

A_ID<-c(111,116,111,112,112,114,116,113,114,111,114,116,115,116)
U_ID<-c(221,221,222,222,223,223,223,224,224,225,225,225,226,226)
df_u_a<-data.frame(U_ID,A_ID)

I want to build a matrix that show me how many occurrences I have per user like

I want to get the same output if I have duplicates, for example, the following should be counted as user 226 has accessed article 116, not like user 226 has accessed article 116 twice:

A_ID<-c(116,116)
U_ID<-c(226,226)
df_u_a<-data.frame(U_ID,A_ID)

I tried the function matrix, but it seems like I'm just getting a table:

m<-as.matrix(df_u_a)
m

Maybe I mont using the matrix function correctly or there is another function I should use. Could you please someone advise on how to get a matrix in R as in the image above.

https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format — jogo, Mar 19 '18 at 12:23

score 3 · Accepted Answer · answered Mar 19 '18 at 12:18

3

After using table, you could simply convert to logical, i.e.:

myTab <- table(df_u_a)
myTab[] <- as.integer(as.logical(myTab))

answered Mar 19 '18 at 12:18

RolandASc

3,863
1
11
30

What's the purpose of the second line? As @tyluRp already mentioned, `table(df_u_a)` reproduces OPs expected output. – Maurits Evers Mar 19 '18 at 12:22
Thanks RolandASc!! – Selrac Mar 19 '18 at 12:25
2

@MauritsEvers the second line is needed, because the OP gives an example indicating that he does not want a count, but rather a binary map – RolandASc Mar 19 '18 at 12:32
@RolandASc Ah I see; well spotted, should've read more carefully. +1 – Maurits Evers Mar 19 '18 at 12:42

score 0 · Answer 2 · answered Mar 19 '18 at 13:04

Here is one-line solution using reshape2::dcast:

library(reshape2);
dcast(df_u_a, U_ID ~ A_ID, length);
#  U_ID 111 112 113 114 115 116
#1  221   1   0   0   0   0   1
#2  222   1   1   0   0   0   0
#3  223   0   1   0   1   0   1
#4  224   0   0   1   1   0   0
#5  225   1   0   0   1   0   1
#6  226   0   0   0   0   1   1

R create a matrix of occurrences from a table

2 Answers2