Convert name value pairs into new pandas data columns

Question

How do you convert a column in a Python Pandas DataFrame that has one column with name value pairs into additional columns within the same dataframe.

The column (attrs) with the named value pairs has values like :

[{'attr_id': 7, 'val': '4.00'}, {'attr_id': 8, 'val': '2.50'}, {'attr_id': 9, 'val': '1750'}, {'attr_id': 11, 'val': 'false'}, {'attr_id': 10, 'val': 'false'}]
[{'attr_id': 7, 'val': '2.00'}, {'attr_id': 8, 'val': '1.00'}, {'attr_id': 11, 'val': 'false'}, {'attr_id': 10, 'val': 'false'}]
[{'attr_id': 11, 'val': 'false'}, {'attr_id': 10, 'val': 'false'}]

So for the first record, the new columns I am trying to create would be attr_id7, attr_id8, attr_id9, attr_id10, attr_id11 and have values 4.00,2.50,1750,false,false

Considering converting column content into proper Python dictionary and then using something like the answer Splitting dictionary/list inside a Pandas Column into Separate Columns

If these are row-specific, it might be better to unpack the `attr_id` into a column and `val` into a column, though I'm not sure if `val` would have a namespace collision within `pandas` or not, so you may want to proceed with caution on that particular column name — C.Nivs, Nov 19 '18 at 04:07

score 1 · Accepted Answer · answered Nov 19 '18 at 04:20

Maybe something like the below:

import pandas as pd
import numpy as np

l=[[{'attr_id': 7, 'val': '4.00'}, {'attr_id': 8, 'val': '2.50'}, {'attr_id': 9, 'val': '1750'}, {'attr_id': 11, 'val': 'false'}, {'attr_id': 10, 'val': 'false'}],
[{'attr_id': 7, 'val': '2.00'}, {'attr_id': 8, 'val': '1.00'}, {'attr_id': 11, 'val': 'false'}, {'attr_id': 10, 'val': 'false'}],
[{'attr_id': 11, 'val': 'false'}, {'attr_id': 10, 'val': 'false'}],]

d = []
for i in l:
    q={}
    for x in i:
        q['attr_id{}'.format(x['attr_id'])]=x['val']
    d.append(q)

df = pd.DataFrame(d)
print(df)

.

  attr_id10 attr_id11 attr_id7 attr_id8 attr_id9
0     false     false     4.00     2.50     1750
1     false     false     2.00     1.00      NaN
2     false     false      NaN      NaN      NaN

Convert name value pairs into new pandas data columns

1 Answers1