Python, transpose nested values adding None for Missing

Question

One of those days, Can't get me head around this tiny solution. I am looking for generic solution as the length of the key value pairs in parent and child leg could vary.

so data source looks like following

data = {   
    "New York": [
    { "Name": "John", "QTY": 56 },
    { "Name": "Richard", "QTY": 76 }   ],   
    "Texas": [
    { "Name": "John", "QTY": 34 },
    { "Name": "Stephanie", "QTY": 23 }   ],   
    "Florida": [
    { "Name": "Rebecca", "QTY": 28 },
    { "Name": "Richard", "QTY": 43 }   ],   
    "Arizona": [
    {"Name": "Charles", "QTY": 23},
    {"Name": "Anne", "QTY": 31},
    {"Name": "William", "QTY": 44}   ],   
    "New Mexico": [
    {"Name": "Rebecca", "QTY": 56},
    {"Name": "Charles", "QTY": 76},
    {"Name": "Stephen", "QTY": 21},
    {"Name": "William", "QTY": 63}   ],   
    "New Jersey": [
    {"Name": "John", "QTY": 56},
    {"Name": "Richard", "QTY": 76},
    {"Name": "Stephen", "QTY": 45}   ] }

Now I want to re-arrange the the dictionary by Name and add values for each state and add None for missing. here is my code

output = {}

for x, y in data.items():
    for i in y:
        key = i['Name']
        try:
            output[key].update({x: i['QTY']})
        except:
            output[key] = {x: i['QTY']}

print(output)

This gives me result as following

{
  "John": {
    "New York": 56,
    "Texas": 34,
    "New Jersey": 56
  },
  "Richard": {
    "New York": 76,
    "Florida": 43,
    "New Jersey": 76
  },
  "Stephanie": {
    "Texas": 23
  },
  "Rebecca": {
    "Florida": 28,
    "New Mexico": 56
  },
  "Charles": {
    "Arizona": 23,
    "New Mexico": 76
  },
  "Anne": {
    "Arizona": 31
  },
  "William": {
    "Arizona": 44,
    "New Mexico": 63
  },
  "Stephen": {
    "New Mexico": 21,
    "New Jersey": 45
  }
}

What I will like to have is something like following, order is indeed important

{
  "John": {
    "New York": 56,
    "Texas": 34,
    "Florida": None,
    "Arizona": None,
    "New Mexico": None,
    "New Jersey": 56
  },
  .......
  .......
  "Stephen": {
    "New York": None,
    "Texas": None,
    "Florida": None,
    "Arizona": None,
    "New Mexico": 21,
    "New Jersey": 45
  }
}

I was wondering if defautldict from collections can be used. But I am not that savvy with it. Folks, remember I need generic solution as the number of states and number os Names can vary.. Any help is much appreciated.

Answer 1

Here's a straightforward way to do what your question asks:

nameList = []
nameSet = set()
for state, nameQtyList in data.items():
    for nameQty in nameQtyList:
        name = nameQty["Name"]
        if name not in nameSet:
            nameSet.add(name)
            nameList.append(name)
out = {name:{state:None for state in data} for name in nameList}
for state, nameQtyList in data.items():
    for nameQty in nameQtyList:
        out[nameQty["Name"]][state] = nameQty["QTY"]

Explanation:

• walk through the input, compiling names in the sequence of first occurrence in names
• initialize the result out to have the desired structure, with an initial value of None for each QTY
• walk through the input again, updating out with each QTY encountered in the input.