How to convert (x, y, k) to k-matrix with x as rows, y as cols in Pandas / Python?

Question

As per title, I have a Pandas dataframe containing coordinates and a value (z) like:

import pandas as pd
df = pd.DataFrame(
     columns=['x', 'y', 'k'],
     data=((x, y, x * y) for x in range(3) for y in range(3)))

resulting in:

   x  y  k
0  0  0  0
1  0  1  0
2  0  2  0
3  1  0  0
4  1  1  1
5  1  2  2
6  2  0  0
7  2  1  2
8  2  2  4

I want to obtain:

   0  1  2
0  0  0  0
1  0  1  2
2  0  2  4

(where x values are now the rows and y values are now the columns).

What would be the most Pythonic way of doing this with Pandas?

This would be similar to obtaining a dense representation of a matrix from a sparse one. Note: the x and y values could be anything (not necessarily integers that happen to map nicely indexes).

p.s. I know I could do two manual loops, but that's what I am trying to avoid.

Answer 1

You can use pivot:

df.pivot(index='x', columns='y', values='k')
y  0  1  2
x         
0  0  0  0
1  0  1  2
2  0  2  4

And to match desired output you can use:

pd.DataFrame(df.pivot(index='x', columns='y', values='k').values)
   0  1  2
0  0  0  0
1  0  1  2
2  0  2  4