create df from list of dictionary of dictionaries

Question

Trying create dataframe from what I believe is referred to as a list of dictionary of dictionaries.

list of dictionary of dictionaries:

test_dict = {'name': 'Henry VIII', 'wives': [{'WED': '11JUNE1509', 'spouse': 'TBC', 'marriage': {'name': 'Henry VIII', 'identifier': 'henry'}, 'died': 0.0, 'marriage_lasted': {'yrs': 23.0, 'mth': 11.0, 'days': 12.0}, 'timestamp': '2022-07-30T13:48:44+00:00', 'last_at': '2022-07-30T13:48:44+00:00', 'fetch_at': '2022-07-30T13:51:47+00:00', 'url': 'https://https://en.wikipedia.org/wiki/Wives_of_Henry_VIII', 'husband': 'king henry of england', 'wife': 'catherine of aragon'}, {'WED': '28MAY1533', 'spouse': 'TBC', 'marriage': {'name': 'Henry VIII', 'identifier': 'henry'}, 'died' : 1.0, 'marriage_lasted': {'yrs': 2.0, 'mth': 11.0, 'days': 19.0}, 'timestamp': '2022-07-30T13:51:47+00:00', 'last_at': '2022-07-30T13:51:47+00:00', 'fetch_at': '2022-07-30T13:51:47+00:00', 'url': 'https://https://en.wikipedia.org/wiki/Wives_of_Henry_VIII', 'husband': 'king henry of england', 'wife': 'anne boleyn'}]}

Desired output:

          name  identifier         WED spouse  died   yrs   mth   days                husband                 wife
0   Henry VIII       henry  11JUNE1509    TBC  0.00  23.0  11.0   12.0  king henry of england  catherine of aragon
1   Henry VIII       henry   28MAY1533    TBC  1.00   2.0  11.0   19.0  king henry of england          anne boleyn

However, to date I have been only able to produce the following:

   name  identifier         WED spouse  died  yrs  mth  days                husband                 wife
0   NaN         NaN  11JUNE1509    TBC  0.00  NaN  NaN   NaN  king henry of england  catherine of aragon
1   NaN         NaN   28MAY1533    TBC  1.00  NaN  NaN   NaN  king henry of england          anne boleyn

Below is the code I have created thus far:

df = pd.DataFrame(test_dict['wives'], columns = ['name', 'identifier', 'WED','spouse', 'died', 'yrs', 'mth', 'days', 'husband', 'wife'])
print(df)

However, based on my output, I am having problems accessing name, identifier, yrs, mth, days and can't figure it out.

user_10035985 · Answer

Try:

df = pd.DataFrame(test_dict["wives"])
df = pd.concat(
    [
        df,
        df.pop("marriage").apply(pd.Series),
        df.pop("marriage_lasted").apply(pd.Series),
    ],
    axis=1,
)
cols = "name identifier WED spouse died yrs mth days husband wife".split()

print(df[cols].to_markdown(index=False))

Prints:

name	identifier	WED	spouse	died	yrs	mth	days	husband	wife
Henry VIII	henry	11JUNE1509	TBC	0	23	11	12	king henry of england	catherine of aragon
Henry VIII	henry	28MAY1533	TBC	1	2	11	19	king henry of england	anne boleyn

create df from list of dictionary of dictionaries

1 Answers