How can I create a matrix in python from multiple key/value pairs?

user_16937181 0
Viewed 53

I would like to generate a matrix for my data analysis. At current I have a CSV file with multiple key value pairs which is looking similar to this (just a bit smaller than the original data set).

      PL1           AL1           BL2
      ABCGD  41     BAHDG   13    HANDD   57
      KANSL  35     ABCGD   15    NAHDK   36

In the end I would like to have a matrix looking like this with 0 entries for everything that is not a hit:

       PL1     AL1    BL2
ABCGD   41     15      0
KANSL   35     0       0
BAHDG    0     13      0
HANDD    0      0      57
NAHDK    0      0      36

I'm still fairly new to python and not quite sure how to approach this problem. I have transferred my CSV file already to a pandas dataframe and tried generating an array with numpy but the outcome wasn't quite looking how I expected it to be.

I hope you can help me to solve this problem. Thanks!

pandas dataframe numpy arrays python
1 Answers

You're looking for a pivot table. But first you need to melt the data and split the key/value pair on the whitespace.

import pandas as pd

df = pd.DataFrame({'PL1': {0: 'ABCGD  41', 1: 'KANSL  35'},
 'AL1': {0: 'BAHDG   13', 1: 'ABCGD   15'},
 'BL2': {0: 'HANDD   57', 1: 'NAHDK   36'}})


df = df.melt()

df[['key','value']] = df.value.str.split('\W+', expand=True)

df.pivot(index='key',columns='variable',values='value')

Output

variable  AL1  BL2  PL1
key                    
ABCGD      15  NaN   41
BAHDG      13  NaN  NaN
HANDD     NaN   57  NaN
KANSL     NaN  NaN   35
NAHDK     NaN   36  NaN
user_47183500
Related