Parse pandas series with a list of dicts into new 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

Answer

Try:

df = pd.concat([df.pop("d").explode().apply(pd.Series).fillna(0), df], axis=1)
print(df[["id", "br", "ba", "r", "name"]].astype(int, errors="ignore"))

Prints:

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