CODEWITHSUNDEEP
pythonpandasdictionary
kms
kms

Reputation: 2024

Parse list of dicts from a pandas Series into columns

I have a pandas series containing a list of dictionaries. I'd like to parse the contents of the dicts with some condition and store the results into new columns.

Here's some data to work with:


import pandas as pd

df = pd.DataFrame({'d': [[{'br': 1, 'ba': 1, 'r': 100},
                         {'ba': 1, 'r': 80},
                         {'br': 2, 'ba': 1, 'r': 150},
                         {'br': 1, 'ba': 1, 'r': 90}],
                        [{'br': 1, 'ba': 1, 'r': 100},
                         {'ba': 1, 'r': 80},
                         {'br': 2, 'ba': 1, 'r': 150}]],

                   'id': ['xxas', 'yxas'],

                   'name': [A, B]

                 })

I'd like to parse the contents of each dictionary with some conditional logic. Check for each dicts in the list and name columns as keys br and ba. Get the value of r key as assign as column value. If key is not found, br in this example, assign 0 as the value. Expected output:

id     br  ba  r     name
xxas   1   0   100   A
xxas   0   1   80    A
xxas   2   1   150   A
xxas   1   1   90    A
yxas   1   1   100   B
yxas   0   1   80    B
yxas   2   1   150   B

Upvotes: 1

Views: 56

Answers (1)

tlentali
tlentali

Reputation: 3455

From your DataFrame, we first set the columns id and name as index like so :

df = df.set_index(['id', 'name'])

Then, we can use concat to get the expected result :

pd.concat(df.d.apply(pd.DataFrame).tolist(), keys=df.index).reset_index(level=[0, 1]).reset_index(drop=True).fillna(0).rename(columns={'level_0': 'id', 'level_1': 'name'})

Output :

    id      name    br      ba  r
0   xxas    A       1.0     1   100
1   xxas    A       0.0     1   80
2   xxas    A       2.0     1   150
3   xxas    A       1.0     1   90
4   yxas    B       1.0     1   100
5   yxas    B       0.0     1   80
6   yxas    B       2.0     1   150

Upvotes: 2

Related Questions