Search code examples
pythonpandasdataframedictionaryjson-normalize

Python: explode column that contains dictionary

I have a DataFrame that looks like this:

df:

amount    info
12        {id:'1231232', type:'trade', amount:12}
14        {id:'4124124', info:{operation_type:'deposit'}}

What I want to achieve is this:

df:

amount    type     operation_type
12        trade    Nan
14        Nan      deposit

I have tried the df.explode('info') method but with no luck. Are there any other ways to do this?

Solution

  • We could do it in 2 steps: (i) Build a DataFrame df with data; (ii) use json_normalize on "info" column and join it back to df:

    df = pd.DataFrame(data)
out = df.join(pd.json_normalize(df['info'].tolist())[['type', 'info.operation_type']]).drop(columns='info')
out.columns = out.columns.map(lambda x: x.split('.')[-1])

    Output:

       amount   type operation_type
0      12  trade            NaN
1      14    NaN        deposit