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.

One place to start might be to gather all of the required keys to begin with, then use `dict.get(i)` rather than `dict[i]`, as `get` can return a `None` if the key is not found — G. Anderson, Dec 12 '22 at 17:44

Pranav Hosangadi · Answer 1 · 2022-12-12T17:55:08.777

In your code, instead of:

try:
    output[key].update({x: i['QTY']})
except:
    output[key] = {x: i['QTY']}

do

try:
    output[key].update({x: i['QTY']})
except KeyError:
    output[key] = defaultdict(none_generator, {x: i['QTY']})

Here, none_generator is a function that takes no arguments and returns `None:

def none_generator(): pass

Note: You shouldn't be using a bare except: clause anyway. You know your output[key].update(...) line will produce a KeyError if all is working as expected, so only catch a KeyError (See What is wrong with using a bare 'except'? for more details)

Now, each value of your output dictionary is itself a defaultdict, and getting a non-existent key of these defaultdicts will return `None:

output = {'John': defaultdict(<function none_generator at 0x12256d6c20>, {'New York': 56, 'Texas': 34, 'New Jersey': 56}), 'Richard': defaultdict(<function none_generator at 0x12256d6c20>, {'New York': 76, 'Florida': 43, 'New Jersey': 76}), 'Stephanie': defaultdict(<function none_generator at 0x12256d6c20>, {'Texas': 23}), 'Rebecca': defaultdict(<function none_generator at 0x12256d6c20>, {'Florida': 28, 'New Mexico': 56}), 'Charles': defaultdict(<function none_generator at 0x12256d6c20>, {'Arizona': 23, 'New Mexico': 76}), 'Anne': defaultdict(<function none_generator at 0x12256d6c20>, {'Arizona': 31}), 'William': defaultdict(<function none_generator at 0x12256d6c20>, {'Arizona': 44, 'New Mexico': 63}), 'Stephen': defaultdict(<function none_generator at 0x12256d6c20>, {'New Mexico': 21, 'New Jersey': 45})}

print(output["John"]["Florida"]) prints None

If you want there to be None values explicitly at the keys in the dictionary that do not exist, you're going to have to populate the output dictionary first. Then, remake all dictionaries with their missing values set to None:

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


for person, person_vals in output.items():
    output[person] = {state: person_vals.get(state, None) for state in data}

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

Yep, populating set of values for all states needed to be done. Thank you so much — Ozzie Ghani, Dec 12 '22 at 18:08

score 1 · Accepted Answer · answered Dec 12 '22 at 18:07

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.

Python, transpose nested values adding None for Missing

2 Answers2