I have a long nested dictionary structured like below, how can I go about loading this into a Pandas dataframe? The sub keys of Feed, Spindle Speed, and Tool remain the same throughout, but the two levels above (Heading, N1, etc and 4001, 4002 etc are unique or at least in unique orders throughout the dictionary.

I'm aware of something like this:

pd.DataFrame.from_dict({(i,j): dictionary[i][j] 
                           for i in dictionary.keys() 
                           for j in dictionary[i].keys()},
                       orient='index')

But this looks like a pivot table, where I'd prefer a data frame with the redundant information (liek 4001) to run down the entire column.

{
    "4001": {
        "Heading": {
            "Feed": [],
            "Spindle Speed": [],
            "Tool": []
        },
        "N1": {
            "Feed": [],
            "Spindle Speed": [],
            "Tool": [
                "0800"
            ]
        },
        "N10 ": {
            "Feed": [
                0.01,
                0.0006,
                0.0001,
                0.0006,
                0.0001,
                0.0006,
                0.0002,
                0.02,
                0.0004
            ],
            "Spindle Speed": [
                "M3S2630"
            ],
            "Tool": [
                "1616"
            ]
        }
    },
    "4002": {
        "Heading": {
            "Feed": [],
            "Spindle Speed": [],
            "Tool": []
        },
        "N1": {
            "Feed": [],
            "Spindle Speed": [],
            "Tool": [
                "9900"
            ]
        },
        "N10": {
            "Feed": [
                0.01,
                0.001,
                0.0004,
                0.001,
                0.005
            ],
            "Spindle Speed": [],
            "Tool": [
                "3838"
            ]
        }
    },     
    "4003": {...
             ...
             ...

The dataframe would look something like this ideally:

Program Operation Number    Feed        Tool      Spindle Speed
4001    Heading             []          []        []
4001    N1                  []          ['0800']  []
4001    N10                 [0.01, ...] ['1616']  ['M3S2630']

2 Answers

1
Serge Ballesta On Best Solutions

You were almost there. You have just to reset the multi_index and give the correct column names:

pd.DataFrame.from_dict({(i,j): dictionary[i][j] 
                           for i in dictionary.keys() 
                           for j in dictionary[i].keys()},
                       orient='index').reset_index().rename(
    {'level_0': 'Program', 'level_1': 'Operation Number'}, axis=1)
1
clayton On

Run this line of code after importing pandas to configure your dataframes to not be "sparse":

pd.set_option('display.multi_sparse', False)

From pandas docs:

Option: display.multi_sparse
Default: True
Function: “Sparsify” MultiIndex display (don’t display repeated elements in outer levels within groups)

Output using first two groupings you provided:

enter image description here