convert list of uneven dictionaries to pandas dataframe

Question

Given this list of dictionaries

[{'Empire:FourKingdoms:': {'US': '208', 'FR': '96', 'DE': '42', 'GB': '149'}}, 
 {'BigFarmMobileHarvest:': {'US': '211', 'FR': '101', 'DE': '64', 'GB': '261'}}, 
 {'AgeofLords:': {'US': '00', 'JP': '00', 'FR': '00', 'DE': '00', 'GB': '00'}}, 
 {'BattlePiratesHQ:': {'US': '00', 'JP': '00', 'FR': '00', 'DE': '00', 'GB': '00'}},
 {'CallofWar:': {'US': '00', 'JP': '00', 'FR': '00', 'DE': '00', 'GB': '00'}}, 
 {'Empire:AgeofKnights:': {'US': '00', 'JP': '00', 'FR': '00', 'DE': '00', 'GB': '00'}}, 
 {'Empire:MillenniumWars:': {'US': '00', 'JP': '00', 'FR': '00', 'DE': '00', 'GB': '00'}}, 
 {'eRepublik:': {'US': '00', 'JP': '00', 'FR': '00', 'DE': '00', 'GB': '00'}}, 
 {'GameofEmperors:': {'US': '00', 'JP': '00', 'FR': '00', 'DE': '00', 'GB': '00'}}, 
 {'GameofTrenches:': {'US': '00', 'JP': '00', 'FR': '00', 'DE': '00', 'GB': '00'}}]

and this list of row names:

['Name', 'country', '30/08/2019']

How could I arrive at this DataFrame:

        Name:    Empire:FourKingdoms  BigFarmMobileHarvest  AgeofLords     ...
0    Country:    US  FR  DE  GB       US  FR  DE  GB        US JP FR DE GB
1 30/08/2019:    208 96  42  149      211 101 64  261       00 00 00 00 00 ...

each Country and 30/08/2019 value would have its own cell in the DataFrame. But they should be placed under each Game. Not sure if this is possible when dicts are different lengths.

My initial idea was to get the dicts out of the list, convert to DataFrame (somehow) in the desired way, and add the row names later. I'm thinking some transposing has to find place.

Another idea is to make dict keys column names and go from there.

Eventually, this would have to be printed to an excel sheet.

I looked at previous questions, but not sure if it could apply in my case.

score 2 · Accepted Answer · edited Nov 03 '19 at 21:17

You can do it as follows:

# transform your dictionary to be flat
# so entries like 'Empire:FourKingdoms:'
# become values of key 'Name'
l2= list()
for d in l:
    for name, dct in d.items():
        dct= dict(dct)
        dct['Name']= name
        l2.append(dct)

# create a dataframe from these dictionaries
df= pd.DataFrame(l2)
# I saw you had a date in your example, so I guess you want to
# add rows from time to time
df['Date']= '30/08/2019'

# create an index based on Date and Name (the columns the data
# is aligned to) then unstack it to make Name the second
# level of the column index, swap the two levels, so Name
# is on top and finally resort the index, so the countries
# are grouped below the Name (instead of still having everything
# sorted for country so the Names appear for each country
# separately)
df.set_index(['Date', 'Name']).unstack(1).swaplevel(axis='columns').sort_index(axis=1)

The rsult looks like:

Out[1]: 
Name       AgeofLords:                 BattlePiratesHQ:          ... GameofTrenches:         eRepublik:                
                    DE  FR  GB  JP  US               DE  FR  GB  ...              GB  JP  US         DE  FR  GB  JP  US
Date                                                             ...                                                   
30/08/2019          00  00  00  00  00               00  00  00  ...              00  00  00         00  00  00  00  00

Holy moly, it actually worked. I didn't think it would be possible. — doomdaam, Sep 01 '19 at 09:09
That was a hell of a tricky question. I too was thinking in similar lines. — Vishnudev Krishnadas, Sep 01 '19 at 09:17
@Vishnudev I have more where they came from :) Pandas is truly wonderful and so complex at times. — doomdaam, Sep 01 '19 at 10:59
Now that you rocked this question @jottbe, mind taking a look at my next question regarding writing the result to an existing excel sheet https://stackoverflow.com/questions/57745818/save-dataframe-to-next-row-in-existing-excel-sheet — doomdaam, Sep 01 '19 at 12:24

convert list of uneven dictionaries to pandas dataframe

1 Answers1