Unpack dictionary within list within a string in Pandas

Question

I have the following problem: within a dataframe I have endless dictionaries, which are in a list, which on the other hand is a string:

How is it possible to unpack these dictionaries into separate columns of my dataframe?

Answer 1

You can use ast.literal_eval, then the str accessor to retrieve the first (and only) list element. Here's an example:

from ast import literal_eval

df = pd.DataFrame({'A': ['[{"a": 1, "b": 2}]', '[{"b": 3, "c": 4}]']})

df = df.join(pd.DataFrame(df.pop('A').apply(literal_eval).str[0].values.tolist()))

print(df)

     a  b    c
0  1.0  2  NaN
1  NaN  3  4.0

Related: Splitting dictionary/list inside a Pandas Column into Separate Columns