How to convert more than 3 level to N nested dictionary to levelled dataframe?

input_dict = {
                '.Stock': {
                            '.No[0]': '3241512)',
                            '.No[1]': '1111111111',
                            '.No[2]': '444444444444',
                            '.Version': '46',
                            '.Revision': '78'
                          },
                '.Time': '12.11.2022'
             }

what I expect:

import pandas as pd
expected_df = pd.DataFrame([{'level_0': '.Stock', 'level_1': '.No_0', "value": '3241512'},
 {'level_0': '.Stock', 'level_1': '.No_1', "value": '1111111111',},
 {'level_0': '.Stock', 'level_1': '.No_2', "value": '444444444444'},
 {'level_0': '.Stock', 'level_1': '.Version', "value": '46'},
 {'level_0': '.Stock', 'level_1': '.Revision', "value": '78'},
 {'level_0': '.Time',  "value": '12.11.2022'}])

Firsly I need to convert nested dictionary to list of levelled dictionaries, than lastly convert list of dictionaries to dataframe. How can I convert, pls help me!

I've already tried the code below but it doesn't show exactly the right result.

pd.DataFrame(input_dict).unstack().to_frame().reset_index()

CodePudding user response：

You can first flatten your nested dictionary with a recursive function (see "Best way to get nested dictionary items").

def flatten(ndict):
    def key_value_pairs(d, key=[]):
        if not isinstance(d, dict):
            yield tuple(key), d
        else:
            for level, d_sub in d.items():
                key.append(level)
                yield from key_value_pairs(d_sub, key)
                key.pop()
    return dict(key_value_pairs(ndict))

>>> input_dict = {
        '.Stock': {
            '.No[0]': '3241512)',
            '.No[1]': '1111111111',
            '.No[2]': '444444444444',
            '.Version': '46',
            '.Revision': '78'
            },
        '.Time': '12.11.2022'
    }
>>> d = flatten(input_dict)
>>> d
{('.Stock', '.No[0]'): '3241512)',
 ('.Stock', '.No[1]'): '1111111111',
 ('.Stock', '.No[2]'): '444444444444',
 ('.Stock', '.Version'): '46',
 ('.Stock', '.Revision'): '78',
 ('.Time',): '12.11.2022'}

You then need to fill missing levels, as for the last row in your example. You can use zip_longest for the purpose and also stick the values to the last position.

>>> from itertools import zip_longest
>>> d = list(zip(*zip_longest(*d.keys()), d.values()))
>>> d
[('.Stock', '.No[0]', '3241512)'),
 ('.Stock', '.No[1]', '1111111111'),
 ('.Stock', '.No[2]', '444444444444'),
 ('.Stock', '.Version', '46'),
 ('.Stock', '.Revision', '78'),
 ('.Time', None, '12.11.2022')]

Now you can create your dataframe:

>>> pd.DataFrame(d)
    0   1   2
0   .Stock  .No[0]  3241512)
1   .Stock  .No[1]  1111111111
2   .Stock  .No[2]  444444444444
3   .Stock  .Version    46
4   .Stock  .Revision   78
5   .Time   None    12.11.2022

CodePudding user response：

For your exact problem, this colution, based on this anser should work:

data = {}
for k1, v1 in input_dict.items():
    if isinstance(v1, dict):
        for k2, v2 in v1.items():
            data[(k1, k2)] = v2
    else:
        data[(k1, pd.NA)] = v1

df = pd.Series(data).reset_index()

df:

  level_0    level_1             0
0  .Stock     .No[0]      3241512)
1  .Stock     .No[1]    1111111111
2  .Stock     .No[2]  444444444444
3  .Stock   .Version            46
4  .Stock  .Revision            78
5   .Time        NaN    12.11.2022

For dictionaries with more levels you should wrap the cycle into a recursive func.