[Python] How to convert nested dict with lists as values to pandas dataframe

I am trying to convert the following dict to a dataframe:

city_data = {
'San Francisco': {'x': [1, 2, 3], 'y': [4, 1, 2]},
'Montreal': {'x': [1, 2, 3], 'y': [2, 4, 5]},
'New York City': {'x': [1, 2, 3], 'y': [2, 2, 7]},
'Cincinnati': {'x': [1, 2, 3], 'y': [1, 0, 2]},
'Toronto': {'x': [1, 2, 3], 'y': [4, 7, 3]},
'Ottawa': {'x': [1, 2, 3], 'y': [2, 3, 3]}
}


Such that the dataframe looks like this:

city | x | y
San Francisco | 1 | 4
San Francisco | 2 | 1
San Francisco | 3 | 2
...


Using a solution I found here Unfold a nested dictionary with lists into a pandas DataFrame I tried:

data = city_data

def unroll(data):
if isinstance(data, dict):
for key, value in data.items():
# Recursively unroll the next level and prepend the key to each row.
for row in unroll(value):
yield [key] + row
if isinstance(data, list):
# This is the bottom of the structure (defines exactly one row).
yield data

df = pd.DataFrame(list(unroll(nested_dict)))
df.rename(columns=lambda i: 'col{}'.format(i+1))


However, I end up with this result:



Continue reading...