Skip to content
Advertisement

Transform matrix adjacency to Source, Target, Value in CSV file (python)

I have this CSV file, with adjacency matrix:

|    | Happy | Love | Fat | Ugly | Trust | 
|-------|---|---|---|---|---|
| Happy | 0 | 2 | 1 | 0 | 1 |
| Love  | 2 | 0 | 1 | 0 | 1 |
| Fat   | 1 | 1 | 0 | 0 | 0 |
| Ugly  | 0 | 0 | 0 | 0 | 1 |
| Trust | 1 | 1 | 0 | 1 | 0 | 

I want to transform to a new csv file, with the values of sorce, target, and value. For example:

|Source | Target | Value
|-------|---|---|
| Happy | Love | 2 |
| Happy  | Fat | 1 | 
| Happy   | Ugly | 0 | 

Thanks!

Advertisement

Answer

If you replace the zeros with nan, you can use stack to achieve this.

import pandas as pd
import numpy as np


df = pd.DataFrame({'Happy': {'Happy': 0, 'Love': 2, 'Fat': 1, 'Ugly': 0, 'Trust': 1},
 'Love': {'Happy': 2, 'Love': 0, 'Fat': 1, 'Ugly': 0, 'Trust': 1},
 'Fat': {'Happy': 1, 'Love': 1, 'Fat': 0, 'Ugly': 0, 'Trust': 0},
 'Ugly': {'Happy': 0, 'Love': 0, 'Fat': 0, 'Ugly': 0, 'Trust': 1},
 'Trust': {'Happy': 1, 'Love': 1, 'Fat': 0, 'Ugly': 1, 'Trust': 0}})

df = df.replace(0,np.nan).stack().reset_index()
df.columns = ['Source','Target','Value']

Output

   Source Target  Value
0   Happy   Love    2.0
1   Happy    Fat    1.0
2   Happy  Trust    1.0
3    Love  Happy    2.0
4    Love    Fat    1.0
5    Love  Trust    1.0
6     Fat  Happy    1.0
7     Fat   Love    1.0
8    Ugly  Trust    1.0
9   Trust  Happy    1.0
10  Trust   Love    1.0
11  Trust   Ugly    1.0
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement