Make a table from 2 columns
<p>I'm fairly new on Python.</p>

<p>I have 2 columns on a dataframe, columns are something like: </p>

<pre><code>db = pd.read_excel(path_to_file/file.xlsx)
db = db.loc[:,['col1','col2']]

col1  col2
C     4
C     5
A     1
B     6
B     1
A     2
C     4
</code></pre>

<p>I need them to be like this:</p>

<pre><code>   1 2 3 4 5 6
A  1 1 0 0 0 0
B  1 0 0 0 0 1
C  0 0 0 2 1 0
</code></pre>

<p>so they act like rows and columns and values refer to the number of coincidences.</p>

<p>Say your columns are called <code>cat</code> and <code>val</code>:</p>

<pre><code>In [26]: df = pd.DataFrame({'cat': ['C', 'C', 'A', 'B', 'B', 'A', 'C'], 'val': [4, 5, 1, 6, 1, 2, 4]})

In [27]: df
Out[27]: 
  cat  val
0   C    4
1   C    5
2   A    1
3   B    6
4   B    1
5   A    2
6   C    4
</code></pre>

<p>Then you can <code>groupby</code> the table hierarchicaly, then unstack it:</p>

<pre><code>In [28]: df.val.groupby([df.cat, df.val]).sum().unstack().fillna(0).astype(int)
Out[28]: 
val  1  2  4  5  6
cat               
A    1  2  0  0  0
B    1  0  0  0  6
C    0  0  8  5  0
</code></pre>

<p><strong>Edit</strong></p>

<p>As IanS pointed out, 3 is missing here (thanks!). If there's a range of columns you must have, then you can use</p>

<pre><code>r = df.val.groupby([df.cat, df.val]).sum().unstack().fillna(0).astype(int)

for c in set(range(1, 7)) - set(df.val.unique()):
    r[c] = 0
</code></pre>

python pandas group-by aggregate multiple-columns