0

I have a dataframe

df = pd.DataFrame({'col1':['a', 'a', 'a', 'b', 'b', 'c', 'c'], 
                   'col2':['a', 'b', 'c', 'b', 'c', 'a', 'c'], 
                   'count':[12, 2, 45, 4, 6, 17, 20]})

  col1 col2 count
0   a   a   12
1   a   b   2
2   a   c   45
3   b   b   4
4   b   c   6
5   c   a   17
6   c   c   20

and I would like to reshape it so I have a pairwise matrix as below (if there is no row combination of col1, col2 in the dataframe above the entry should be 0 or else nan. So it would look like that

     a   b   c          
a   12  NaN  17.0
b   2   4.0  NaN
c   45  6.0  20.0
petezurich
  • 9,280
  • 9
  • 43
  • 57
corianne1234
  • 634
  • 9
  • 23

1 Answers1

1

You are looking for the pivot() function:

df = pd.DataFrame({'col1':['a', 'a', 'a', 'b', 'b', 'c', 'c'], 
                   'col2':['a', 'b', 'c', 'b', 'c', 'a', 'c'], 
                   'count':[12, 2, 45, 4, 6, 17, 20]})

df.pivot(index='col1', columns='col2)

df

     count           
col2     a    b     c
col1                 
a     12.0  2.0  45.0
b      NaN  4.0   6.0
c     17.0  NaN  20.0
Let's try
  • 1,044
  • 9
  • 20