Convert dict with variable length list of dicts to pandas dataframe

Question

I have a dictionary with as values a list of dictionaries and these lists are of variable length. I have tried a lot, but can't get the data properly transformed to a Pandas dataframe.

The data looks like this:

{key1: [{'column5': 40, 'column1': 1, 'column2': 6, 'column3': 170, 'column4': 300}], 
key2: [{'column5': 6, 'column1': 33, 'column2': 5, 'column3': 76, 'column4': 13}], 
key3: [{'column5': 7, 'column1': 44, 'column2': 2, 'column3': 67, 'column4': 13}, {'column5': 45, 'column1': 400, 'column2': 100, 'column3': 12, 'column4': 145}]}

I want to get a frame like this:

      column1  column2 column3   ..
key1  1        6       170
key2  33       5       76
key3  33       2       67
key3  400      100     12
 .
 .

I either get errors like 'Arrays must all be of same length' when using pd.DataFrame.from_records and when using orient=index, the data is still as a dictionary placed in the dataframe. Some of the things I've tried:

df = pd.DataFrame.from_dict(a, orient='index')
df.transpose() //Data is not properly placed in the dataframe

df = pd.DataFrame.from_records(dataset, orient='index') //Data is not properly placed in the dataframe

df = pd.DataFrame.from_records(dataset) //Gives error about length of arrays

df = pd.DataFrame.from_dict(dataset).T //Gives error about length of arrays

How should I go about this? Thanks a lot!

Can you post your desired dataframe? Also your dictionary is not defined correctly — user3483203, Jul 20 '18 at 07:19
I've edited the original post, hopefully it is more clear now. If not, please let me know. — Stefan1993, Jul 20 '18 at 07:29
Can you please just post the desired dataframe as text, formatted as you want, not a description of it? — user3483203, Jul 20 '18 at 07:30
https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — user3483203, Jul 20 '18 at 07:31
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/176390/discussion-between-stefan1993-and-user3483203). — Stefan1993, Jul 20 '18 at 07:42

user3483203 · Accepted Answer · 2018-07-20T07:56:30.787

Setup

dct = {'key1': [{'column5': 40, 'column1': 1, 'column2': 6, 'column3': 170, 'column4': 300}],'key2': [{'column5': 6, 'column1': 33, 'column2': 5, 'column3': 76, 'column4': 13}],'key3': [{'column5': 7, 'column1': 44, 'column2': 2, 'column3': 67, 'column4': 13}, {'column5': 45, 'column1': 400, 'column2': 100, 'column3': 12, 'column4': 145}]}

Using a list comprehension to slightly restructure your input dataset:

pd.DataFrame([{'key': k, **i} for  k, v in dct.items() for i in v])

   column1  column2  column3  column4  column5   key
0        1        6      170      300       40  key1
1       33        5       76       13        6  key2
2       44        2       67       13        7  key3
3      400      100       12      145       45  key3

To help better understand why this works, here is the list of dictionaries created by the list comprehension:

[{'key': 'key1', 'column5': 40, 'column1': 1, 'column2': 6, 'column3': 170, 'column4': 300}, {'key': 'key2', 'column5': 6, 'column1': 33, 'column2': 5, 'column3': 76, 'column4': 13}, {'key': 'key3', 'column5': 7, 'column1': 44, 'column2': 2, 'column3': 67, 'column4': 13}, {'key': 'key3', 'column5': 45, 'column1': 400, 'column2': 100, 'column3': 12, 'column4': 145}]

Convert dict with variable length list of dicts to pandas dataframe

1 Answers1