Construct dataframe from values in nested dictionary

user_8709941 0
Viewed 68

I have a list of lists of dictionaries that I'm trying to convert to a pandas DataFrame but I'm unable to use pandas.DataFrame.from_dict() because I want the value of the 'name' key to be the column header and the value of the 'duration' key to be the row value. Any suggestions on how I can make this work?

[[{'duration': 21.82, 'name': 'ABC'},
{'duration': 3.9, 'name': 'DEF'},
{'duration': 105.78, 'name': 'GHI'},
{'duration': 63.14, 'name': 'JKL'}],
[{'duration': 18.9, 'name': 'ABC'},
{'duration': 56.01, 'name': 'DEF'},
{'duration': 38.36, 'name': 'GHI'},
{'duration': 34.16, 'name': 'JKL'}]]

Desired Output:

    ABC    DEF    GHI     JKL
0  21.82   3.9   105.78   63.14
1  18.9   56.01  38.36    34.16
pandas dictionary python
1 Answers

You can flatten your list of lists via itertools.chain, then pivot your dataframe. The trick here is to use an index which counts cumulatively by your grouper.

from itertools import chain

df = pd.DataFrame(list(chain.from_iterable(L)))

res = df.pivot(index=df.groupby('name').cumcount(), columns='name')
res.columns = res.columns.droplevel(0)  # remove unwanted column level

print(res)

name    ABC    DEF     GHI    JKL
0     21.82   3.90  105.78  63.14
1     18.90  56.01   38.36  34.16
user_92095460
Related