matrix representation of pandas data-frame in python

Question

i have a data-frame like

 from   to  Amt
 a      b   100
 a      c   200
 a      d   220
 b      a   250
 b      c   300
 b      d   330
 c      a   100
 c      b   120
 c      d   320
 d      a   211
 d      b   980
 d      c   430

i want to represent it in matrix format like

     a     b     c    d
a    0    100    200  220
b   250    0     300  330
c   100   120    0    320
d   211   980    430   0

How to achieve that..

i have followed Printing Lists as Tabular Data link.But not getting what i was looking for.

David Maust · Accepted Answer · 2015-12-28T06:20:21.370

4

You need to pivot your data. Here is an example.

pivot_df = df.pivot(index='from', columns='to', values='Amt')

For doing fractional calculations before hand, you might use groupby() then transform('sum'). It is similar to a SQL window function sum.

df['sums'] =  df.groupby('from')['amt'].transform('sum')
df['frac'] = df['amt'] / df['sums']
df.pivot(index='from', columns='to', values='frac')

edited Dec 28 '15 at 06:20

answered Dec 28 '15 at 05:35

David Maust

8,080
3
32
36

@David-in place of values='Amt' can i pass some o/p of a function. Actually i need to pass the % of Amt field for each from to corresponding to. – Satya Dec 28 '15 at 05:52
You would probably want to first precompute the value before pivoting. Let me see... I remember a really clever way of doing window functions on a dataframe. Let me see if I can find it. – David Maust Dec 28 '15 at 06:01
Added a way to calculate the `amt / sum(amt over all from)` . http://stackoverflow.com/questions/34457982/pandas-add-unique-count-column/34458096#34458096 – David Maust Dec 28 '15 at 06:12

score 3 · Answer 2 · edited Dec 28 '15 at 06:38

3

You need to pivot the data frame. See http://pandas.pydata.org/pandas-docs/stable/reshaping.html

df.pivot(index="from", columns="to",values="Amt" )

edited Dec 28 '15 at 06:38

Rohan Amrute

764
1
9
23

answered Dec 28 '15 at 05:39

Bob Baxley

3,551
1
22
28

score 2 · Answer 3 · answered Dec 28 '15 at 13:06

You could also achieve that with pivot_table:

df_pivoted = pd.pivot_table(df, index='from', columns='to', fill_value=0)
print(df_pivoted)

      Amt               
to      a    b    c    d
from                    
a       0  100  200  220
b     250    0  300  330
c     100  120    0  320
d     211  980  430    0

matrix representation of pandas data-frame in python

3 Answers3