0

How to convert more than 3 level 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'}])
index level_0 level_1 value
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

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()
nuri
  • 223
  • 2
  • 9
  • Will your `input_dict` only ever have as many nestings/levels as you have shown, or do you need something that would work just as well on an arbitrarily deeply nested dict? – Vin Nov 17 '22 at 08:01
  • @Vin I have more than 2 level nestings, ı didn't expect whole data but maybe 7-8 maximum deep. – nuri Nov 17 '22 at 09:59

2 Answers2

0

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
edd313
  • 1,109
  • 7
  • 20
  • ----> 2 f = list(zip(*zip_longest(*d.keys()), d.values())) TypeError: 'ZipFile' object is not callable :( – nuri Nov 17 '22 at 12:25
  • You are probably overriding the standard library `zip` function with `from zipfile import ZipFile as zip`. See https://stackoverflow.com/questions/60988073/typeerror-zipfile-object-is-not-callable – edd313 Nov 17 '22 at 12:29
  • ı tried your answer but this time gives > ValueError: ZipFile requires mode 'r', 'w', 'x', or 'a' , I found nested loop solution thanks for your solution too. – nuri Nov 17 '22 at 12:47
  • I have copied the code from my answer and it executed fine. The ValueError that you get is still related to the ZipFile object, which is not related to the built-in `zip` function that I am using. https://docs.python.org/3/library/functions.html?highlight=zip#zip I like your solution too! The trick with nested structures is to use recursion. – edd313 Nov 17 '22 at 19:46
0

I found solution, thanks for your comments:(

def nesting_list_convert(in_dict,level=0):
    out_list = []
    for k1, v1 in in_dict.items():
        if isinstance(v1, dict):
            temp_list = nesting_list_convert(v1,level+1)
            for element in temp_list:
                temp_dict = {("level_"+str(level)) : k1}
                temp_dict.update(element)
                out_list.append(temp_dict)
        else:
             out_list.append({("level_"+str(level)) : k1,"value":v1})
return out_list

out_df = pd.DataFrame(nesting_list_convert(input_dict))
out_df = out_df.reindex(sorted(out_df.columns), axis=1)
index level_0 level_1 value
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

This solves 6' nested level of dictionary.

nuri
  • 223
  • 2
  • 9