CODEWITHSUNDEEP
pythonpandaspanel
G. Deg
G. Deg

Reputation: 628

Convert an nxn matrix to a pandas dataframe

I have an n by n data in csv in the following format

-   A   B   C   D
A   0   1   2   4
B   2   0   3   1
C   1   0   0   5
D   2   5   4   0
...

I would like to read it and convert to a 3D pandas dataframe in the following format:

Origin Dest Distance
A      A    0
A      B    1
A      C    2

...

What is the best way to convert it? In the worst case, I'll write a for loop to read each line and append the transpose of it but there must be an easier way. Any help would be appreciated.

Upvotes: 0

Views: 1390

Answers (1)

Zero
Zero

Reputation: 76917

Use pd.melt()

Assuming, your dataframe looks like

In [479]: df
Out[479]:
   -  A  B  C  D
0  A  0  1  2  4
1  B  2  0  3  1
2  C  1  0  0  5
3  D  2  5  4  0

In [480]: pd.melt(df, id_vars=['-'], value_vars=df.columns.values.tolist()[1:],
   .....:         var_name='Dest', value_name='Distance')
Out[480]:
    - Dest  Distance
0   A    A         0
1   B    A         2
2   C    A         1
3   D    A         2
4   A    B         1
5   B    B         0
6   C    B         0
7   D    B         5
8   A    C         2
9   B    C         3
10  C    C         0
11  D    C         4
12  A    D         4
13  B    D         1
14  C    D         5
15  D    D         0

Where df.columns.values.tolist()[1:] are remaining columns ['A', 'B', 'C', 'D']

To replace '-' with 'Origin', you could use dataframe.rename(columns={...})

pd.melt(df, id_vars=['-'], value_vars=df.columns.values.tolist()[1:],
        var_name='Dest', value_name='Distance').rename(columns={'-': 'Origin'})

Upvotes: 1

Related Questions