I have a nested dictionary that looks like this:
test_dict = {'header1_1': {'header2_1': {'header3_1': {'header4_1': ['322.5', 330.0, -0.28],
'header4_2': ['322.5', 332.5, -0.26]},
'header3_2': {'header4_1': ['285.0', 277.5, -0.09],
'header4_2': ['287.5', 277.5, -0.12]}},
'header2_2': {'header3_1': {'header4_1': ['345.0', 357.5, -0.14],
'header4_2': ['345.0', 362.5, -0.14]},
'header3_2': {'header4_1': ['257.5', 245.0, -0.1],
'header4_2': ['257.5', 240.0, -0.08]}}}}
There are 4 levels of headers, and each level can have multiple values, e.g., header1_1, header1_2. And once you specify a combination of these headers, you have a list containing 3 values.
I want to get this into a dataframe, so I create a reformed dictionary:
reformed_dict = {}
for outerKey, innerDict in test_dict.items():
for innerKey, innerDict2 in innerDict.items():
for innerKey2, innerDict3 in innerDict2.items():
for innerKey3, values in innerDict3.items():
reformed_dict[(outerKey,
innerKey, innerKey2, innerKey3)] = values
reformed_dict
And the reformed dictionary looks like:
{('header1_1', 'header2_1', 'header3_1', 'header4_1'): ['322.5', 330.0, -0.28],
('header1_1', 'header2_1', 'header3_1', 'header4_2'): ['322.5', 332.5, -0.26],
('header1_1', 'header2_1', 'header3_2', 'header4_1'): ['285.0', 277.5, -0.09],
('header1_1', 'header2_1', 'header3_2', 'header4_2'): ['287.5', 277.5, -0.12],
('header1_1', 'header2_2', 'header3_1', 'header4_1'): ['345.0', 357.5, -0.14],
('header1_1', 'header2_2', 'header3_1', 'header4_2'): ['345.0', 362.5, -0.14],
('header1_1', 'header2_2', 'header3_2', 'header4_1'): ['257.5', 245.0, -0.1],
('header1_1', 'header2_2', 'header3_2', 'header4_2'): ['257.5', 240.0, -0.08]}
Throw that into a dataframe:
df = pandas.DataFrame(reformed_dict)
And it looks like:
header1_1
header2_1 header2_2
header3_1 header3_2 header3_1 header3_2
header4_1 header4_2 header4_1 header4_2 header4_1 header4_2 header4_1 header4_2
0 322.5 322.5 285.0 287.5 345.0 345.0 257.5 257.5
1 330.0 332.5 277.5 277.5 357.5 362.5 245.0 240.0
2 -0.28 -0.26 -0.09 -0.12 -0.14 -0.14 -0.1 -0.08
What I'd like to do is have all the column headers be row headers, and have 3 columns for each combination of headers and I'd name the columns Val1, Val2, Val3.
So I use df.stack() to push the column headers into the rows:
df_1 = df.stack(level=0)
df_2 = df_1.stack(level=0)
df_3 = df_2.stack(level=0)
df_4 = df_3.stack(level=0)
print(df_4)
The result is:
header1_1 header2_1 header3_1 header4_1 322.5
header4_2 322.5
header3_2 header4_1 285.0
header4_2 287.5
header2_2 header3_1 header4_1 345.0
header4_2 345.0
header3_2 header4_1 257.5
header4_2 257.5
1 header1_1 header2_1 header3_1 header4_1 330.0
header4_2 332.5
header3_2 header4_1 277.5
header4_2 277.5
header2_2 header3_1 header4_1 357.5
header4_2 362.5
header3_2 header4_1 245.0
header4_2 240.0
2 header1_1 header2_1 header3_1 header4_1 -0.28
header4_2 -0.26
header3_2 header4_1 -0.09
header4_2 -0.12
header2_2 header3_1 header4_1 -0.14
header4_2 -0.14
header3_2 header4_1 -0.1
header4_2 -0.08
This isn't the layout I was looking for, as I want the 3 values in each list to be on the same row, similar to how they are in the reformed dictionary.
How can I accomplish this?
CodePudding user response：
What you are looking for is MultiIndex.
Convert keys of dict to index and use dict values as data:
keys = reformed_dict.keys()
index = pd.MultiIndex.from_tuples(keys, names=["header1", "header2", "header3", "header4"])
values = [reformed_dict[k] for k in keys]
df = pd.DataFrame(data=values, index=index)
Output:
header1 header2 header3 header4
header1_1 header2_1 header3_1 header4_1 322.5 330.0 -0.28
header4_2 322.5 332.5 -0.26
header3_2 header4_1 285.0 277.5 -0.09
header4_2 287.5 277.5 -0.12
header2_2 header3_1 header4_1 345.0 357.5 -0.14
header4_2 345.0 362.5 -0.14
header3_2 header4_1 257.5 245.0 -0.10
header4_2 257.5 240.0 -0.08
If you want index to be columns:
df = df.reset_index()
Output:
header1 header2 header3 header4 0 1 2
0 header1_1 header2_1 header3_1 header4_1 322.5 330.0 -0.28
1 header1_1 header2_1 header3_1 header4_2 322.5 332.5 -0.26
2 header1_1 header2_1 header3_2 header4_1 285.0 277.5 -0.09
3 header1_1 header2_1 header3_2 header4_2 287.5 277.5 -0.12
4 header1_1 header2_2 header3_1 header4_1 345.0 357.5 -0.14
5 header1_1 header2_2 header3_1 header4_2 345.0 362.5 -0.14
6 header1_1 header2_2 header3_2 header4_1 257.5 245.0 -0.10
7 header1_1 header2_2 header3_2 header4_2 257.5 240.0 -0.08
CodePudding user response：
Try:
test_dict = \
{'header1_1': {'header2_1': {'header3_1': {'header4_1': ['322.5', 330.0, -0.28],
'header4_2': ['322.5', 332.5, -0.26]},
'header3_2': {'header4_1': ['285.0', 277.5, -0.09],
'header4_2': ['287.5', 277.5, -0.12]}},
'header2_2': {'header3_1': {'header4_1': ['345.0', 357.5, -0.14],
'header4_2': ['345.0', 362.5, -0.14]},
'header3_2': {'header4_1': ['257.5', 245.0, -0.10],
'header4_2': ['257.5', 240.0, -0.08]}}}}
#from pprint import pprint
#pprint(test_dict)
from collections import defaultdict
import pandas as pd
dct_N = defaultdict(list)
total_rows = 0
def fillDataFrameDict(dct, level=0):
global dct_N, total_rows
for key, value in dct.items():
if not isinstance(value, dict):
dct_N[f'headerNo_{level 1}'].append(key)
total_rows = 1
dct_N['body'].append(value)
for key_N, value_N in dct_N.items():
dct_N[key_N] = value_N (total_rows-len(value_N))*[value_N[-1]]
else:
dct_N[f'headerNo_{level 1}'].append(key)
fillDataFrameDict(value, level 1)
fillDataFrameDict(test_dict)
df = pd.DataFrame(dct_N)
print(df)
which gives:
headerNo_1 headerNo_2 headerNo_3 headerNo_4 body
0 header1_1 header2_1 header3_1 header4_1 [322.5, 330.0, -0.28]
1 header1_1 header2_1 header3_1 header4_2 [322.5, 332.5, -0.26]
2 header1_1 header2_1 header3_2 header4_1 [285.0, 277.5, -0.09]
3 header1_1 header2_1 header3_2 header4_2 [287.5, 277.5, -0.12]
4 header1_1 header2_2 header3_1 header4_1 [345.0, 357.5, -0.14]
5 header1_1 header2_2 header3_1 header4_2 [345.0, 362.5, -0.14]
6 header1_1 header2_2 header3_2 header4_1 [257.5, 245.0, -0.1]
7 header1_1 header2_2 header3_2 header4_2 [257.5, 240.0, -0.08]
When dealing with deep nested lists or dictionaries usage of recursion for traversing the nested branches is a perfect case where usage of recursion makes sense.
Down the path to the body elements the recursive function above tests the type of dictionary values and stops recursive calls when the value is not a dictionary.
Tracking the level and the number of rows during the recursion calls allows to fill up the columns with appropriate values, so there is equal number of row elements in each column for creating a pandas DataFrame.
The defaultdict(list) dictionary object provided in the collections module (which is part of standard Python installation) creates an empty list as a value and returns it for keys not yet stored in the dictionary. This saves some lines of code necessary to create the first key:value entry in a dictionary in before populating the list with items.