Lists into Matrix

Question

I have some data in excel sheet which looks like the following:

User    | Query
u1      | q3   
u2      | q7    
u2      | q7
u3      | q1
u3      | q10
u3      | q8
u4      | q9
u4      | q2

Now I want to cover this data into matrix like:

   q1 q2 q3 q7 q8 q9 q10
u1 0  0  1  0  0  0   0
u2 0  0  0  2  0  0   0
u3 1  0  0  0  1  0   1
u4 0  1  0  0  0  1   0

I don't know what is the name of this sort of matrix but I want to make graph from this matrix. So is there any way to get the matrix from the list mentioned above using any either "R" or "excel?

Consider the top answer in [this post](http://stackoverflow.com/questions/26207850/create-sparse-matrix-from-a-data-frame) to make a sparse matrix object (perhaps that is what you want if there are many zeroes), — Remko Duursma, Dec 24 '16 at 20:02

score 0 · Accepted Answer · answered Dec 24 '16 at 08:49

This is a two-way contingency table.

You first need to read your data into R from excel sheet as a data frame. You may use read.csv or read.table, but as you did not provide a sample of your sheet, I can't produce exact code for this.

Now, suppose the data frame is d, then we can do

xtabs(~ User + Query, d)

#     Query
#User   q1  q10  q2  q3  q7  q8  q9
#  u1    0    0   0   1   0   0   0
#  u2    0    0   0   0   2   0   0
#  u3    1    1   0   0   0   1   0
#  u4    0    0   1   0   0   0   1

or

table(d[[1]], d[[2]])

#       q1  q10  q2  q3  q7  q8  q9
#  u1    0    0   0   1   0   0   0
#  u2    0    0   0   0   2   0   0
#  u3    1    1   0   0   0   1   0
#  u4    0    0   1   0   0   0   1

Thank You Zheyuan Li. your answer help perfectly. appriciate you effort — rafiyz, Dec 24 '16 at 09:37

Lists into Matrix

1 Answers1