From DataFrame to Nested Dictionary

user_14172184 0
Viewed 99

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?

pandas dictionary python
2 Answers

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
            }
user_68172450

Assuming you don't have any duplication present in data(as your expected output contains dictionaries only). Using groupby and iterrows you can get your desired output as follows:

import pandas as pd

#loading df
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'},
 'attribute 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)

#See below code
# created group based on country>state>city and then used sum() to obtain  
# dataframe groupings 

groups = df.groupby(['country','state','city']).sum()

d = {}

#simple iteration over data
for r,grp in groups.iterrows():
  key = grp.name
  country = key[0]
  state = key[1]
  city = key[2]
  attribute_a = grp['attribute a']
  attribute_b = grp['attribute b']
  attribute_c = grp['attribute c']


  if country not in d:
    d[country] = {}

  if state not in d[country]:
      d[country][state] = {}

  if city not in d[country][state]:
    d[country][state][city] = {
        'attribute a': attribute_a,
        'attribute b': attribute_b,
        'attribute c': attribute_c
    }

output:

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