Create sparse matrix for two columns in a Pandas Dataframe
<p>I am trying to create a sparse matrix out of a Pandas Dataset (>10Gb)</p>

<p>Assume I have a dataset of the type</p>

<p>Table: Class</p>

<pre><code>    student |teacher
---------------------
0   | abc   |   a
1   | def   |   g
</code></pre>

<p>And I have a list of students </p>

<pre><code>students = [ "abc", "def", "ghi", "jkl","mno"]
</code></pre>

<p>and list of teachers </p>

<pre><code>teachers = ["a","b","c","d","e","f","g"]
</code></pre>

<p>My goal is to create a sparse matrix out of them such that there is a boolean 1 if there is a corresponding relationship between student-teacher in table Class.</p>

<p>The dense matrix should look like this:</p>

<pre><code>    a   b   c   d   e   f   g

abc 1   0   0   0   0   0   0
def 0   0   0   0   0   0   1
ghi 0   0   0   0   0   0   0
jkl 0   0   0   0   0   0   0
mno 0   0   0   0   0   0   0
</code></pre>

<p>Now in my real dataset I have 700K values of students and another 100K values of teachers. </p>

<p>Initially I tried to construct a simple dense matrix and then convert it to a sparse matrix using scipy. However, 700k*100k bytes = ~70GB and as you can realize it didn't work.</p>

<p>So I tried to assign unique values to both students and teachers and then append those values to rows and columns and tried to create a sparse matrix in Coordinate format.</p>

<p>Code:</p>

<pre><code># Get unique value for each student and teacher
dictstudent = {}
count = 0
for i in rows:
    dictstudent[i] = count
    count +=1

dictteacher ={}
count = 0
for i in cols:
    dictteacher[i] = count
    count +=1
</code></pre>

<p>Now that each teacher and student has a numeric number associated with it. Store numeric value of student if it appears in the table class and numeric value of teacher in r and c.</p>

<pre><code>r = []
c = []

for row,col in zip(student,teacher):
    r.append(dictstudent[row])
    c.append(dictteacher[col])

values = [1] * class["student"].size #From the pandas dataframe class
</code></pre>

<p>Then load it to make a sparse matrix</p>

<pre><code>a = sparse.coo_matrix((values,(r,c)),shape=(len(students),len(teachers)))
</code></pre>

<p>This worked fine for my small test dataset. However for my actual large dataset it crashed.</p>

<p>Is there a better way to do this?</p>

<p>You can convert the columns to category type and then use the <code>codes</code> to create the <code>coo_matrix</code> object:</p>

<pre><code>import numpy as np
import string
import random
import pandas as pd
from scipy import sparse

lowercase = list(string.ascii_lowercase)

students = np.random.choice(lowercase, size=[20, 3]).view("&lt;U3").ravel().tolist()
teachers = np.random.choice(lowercase, 8).tolist()

df = pd.DataFrame({"student": [random.choice(students) for _ in range(30)],
                   "teacher": [random.choice(teachers) for _ in range(30)]})

df = df.apply(lambda s:s.astype("category"))
arr = sparse.coo_matrix((np.ones(df.shape[0]), 
    (df.student.cat.codes, df.teacher.cat.codes)))
</code></pre>

<p>You can get the labels by <code>df.student.cat.categories</code> and <code>df.teacher.cat.categories</code>.</p>

python pandas matrix scipy sparse-matrix