Search code examples
pythonpandasdataframeunpivot

Modify matrix df format


Having a df as:

    14       15         16
14  10.1166  18.2331    65.0185
15  18.2331  6.664      57.5195
16  65.3499  57.851     20.9907

What is a different more efficient way to modify the df to look as

   a   b   c
0  14  14  10.1166
1  14  15  18.2331
2  14  16  65.0185
3  15  14  18.2331
4  15  15  6.664 
etc.

I wrote this code, but i dont like the fact that i need to use a loop for it.

for row in tt.index:
    row_vals = tt[tt.index==row]
    col_vals = row_vals.T
    col_vals['from_zone'] = row
    col_vals['to_zone'] = tt.index
    col_vals['travel_time'] = col_vals[row].astype('int')
    col_vals = col_vals.drop(row, axis=1)
    travel_data = pd.concat([travel_data,col_vals])

Solution

  • In [58]: df.stack().reset_index().rename(columns={'level_0':'a','level_1':'b',0:'c'})
    Out[58]:
        a   b        c
    0  14  14  10.1166
    1  14  15  18.2331
    2  14  16  65.0185
    3  15  14  18.2331
    4  15  15   6.6640
    5  15  16  57.5195
    6  16  14  65.3499
    7  16  15  57.8510
    8  16  16  20.9907
    

    Step by step:

    In [59]: df.stack()
    Out[59]:
    14  14    10.1166
        15    18.2331
        16    65.0185
    15  14    18.2331
        15     6.6640
        16    57.5195
    16  14    65.3499
        15    57.8510
        16    20.9907
    dtype: float64
    
    In [60]: df.stack().reset_index()
    Out[60]:
       level_0 level_1        0
    0       14      14  10.1166
    1       14      15  18.2331
    2       14      16  65.0185
    3       15      14  18.2331
    4       15      15   6.6640
    5       15      16  57.5195
    6       16      14  65.3499
    7       16      15  57.8510
    8       16      16  20.9907