python - From DataFrame to Nested Dictionary

Question

I want to make a nested dictionary out of DataFrame Values like this:

Input

dfdict={'country': {0: 'USA', 1: 'USA', 2: 'USA', 3: 'USA'},
 'state': {0: 'California', 1: 'California', 2: 'Texas', 3: 'Texas'},
 'city': {0: 'San Francisco', 1: 'Los Angeles', 2: 'Dallas', 3: 'Houston'},
 'attribut a': {0: 87, 1: 57, 2: 1, 3: 138},
 'attribute b': {0: 19, 1: 13, 2: 134, 3: 101},
 'attribute c': {0: 39, 1: 118, 2: 82, 3: 29}}
df=pd.DataFrame(dfdict)

    country state   city    attribut a  attribute b attribute c
0   USA California  San Francisco   87  19  39
1   USA California  Los Angeles 57  13  118
2   USA Texas   Dallas  1   134 82
3   USA Texas   Houston 138 101 29

Expected Output:

defdict={"USA":{"California":{"San Francisco":{"atribute a":87,
                                          "attribute b":19,
                                           "attribute c":39},
                          "Los Angeles":{"atribute a":57,
                                          "attribute b":13,
                                           "attribute c":118}},
            "Texas":{"Dallas":{"Dallas":{"atribute a":1,
                                          "attribute b":134,
                                           "attribute c":82},
                              "Houston":{"atribute a":138,
                                          "attribute b":101,
                                           "attribute c":29}
                              }
                    }
           }
    }

Unfortunately every attempt i've done returns an error. Even from the simplest start like:

dictp=df[["country","state"]].apply(lambda x: {a:b for a,b in x}, axis=1)

What would be the correct way to approach this?

question from:https://stackoverflow.com/questions/65861513/from-dataframe-to-nested-dictionary

Answer 1

Here is a triple-nested for loop that does what you want and that can at least be a starting point for further optimisation. I put .tolist() into the innermost loop, in case there is more than one entry for that city.

outs={}

for i, c in df.groupby('country'):
    if outs.get(i) is None:
        outs[i] = {}
    for j, s in c.groupby('state'):
        if outs[i].get(j) is None:
            outs[i][j] = {}
        for k, city in s.groupby('city'):
            outs[i][j][k] = {
                col: city[col].tolist() for col in city.columns
            }