Adding To A Nested Dictionary In A Column In Pandas

So, I have a fun issue. I have some data that have a fun nested dictionary that I need to manipulate, but am having trouble. I can do it in pure python, but wanted to do the entire

Solution 1:

One way:

1. Create another list of dict via to_dict('records').
2. zip and iterate over both the list of dict.
3. Update the 1st one with the other to get the desired JSON.

result = []
for i,j in zip(df.Data,df[['Id', 'Timezone']].to_dict('records')):
    for key in i.keys():
        i[key] = {**i[key], **j}
    result.append({'state_data': i})

OUTPUT:

[{'state_data': {'California': {'city': 'San Francisco',
    'pop': '874961',
    'Id': 957643,
    'Timezone': 'Pacific'}}},
 {'state_data': {'New York': {'city': 'New York',
    'pop': '8419000',
    'Id': 973472,
    'Timezone': 'Eastern'}}}]

Complete Example:

df = pd.DataFrame({'Id': {0: 957643, 1: 973472},
 'Timezone': {0: 'Pacific', 1: 'Eastern'},
 'Data': {0: {"California":{"city":"San Francisco","pop":"874961"}},
  1: {"New York":{"city":"New York","pop":"8419000"}}}})

result = []
for i,j in zip(df.Data,df[['Id', 'Timezone']].to_dict('records')):
    for key in i.keys():
        i[key] = {**i[key], **j}
    result.append({'state_data': i})