How to concatenate this json object using pandas?-CodePudding

This is my json data:

{location: {city: 'San Francisco', state: 'CA', country: 'USA'}}

I wanna see this:

{location_city: 'San Francisco', location_state: 'CA', location_country: 'USA'}

How to do like this using pandas?

CodePudding user response：

If there is a good reason for using pandas, one approach is to use json_normalize:

import pandas as pd

res = pd.json_normalize( {'location': {'city': 'San Francisco', 'state': 'CA', 'country': 'USA'}}, sep="_")
print(res)

Output

   location_city location_state location_country
0  San Francisco             CA              USA

Or, as an alternative you could write a function to flatten the dictionary:

def flatten_dict(d, sep="_", root=None):
    result = {}
    for key, value in d.items():
        k = f"{root}{sep}{key}" if root else key
        if isinstance(value, dict):
            k = f"{root}{sep}{key}" if root else key
            result.update(flatten_dict(value, root=k))
        else:
            result[k] = value
    return result


res = flatten_dict({'location': {'city': 'San Francisco', 'state': 'CA', 'country': 'USA'}})
print(res)

Output

{'location_city': 'San Francisco', 'location_state': 'CA', 'location_country': 'USA'}

CodePudding user response：

maybe this:

import json

# 1. No pandas

json_data = '{"location": {"city": "San Francisco", "state": "CA", "country": "USA"}}'
json_data = json.loads(json_data)

res = {"{}_{}".format(k, k2): v2 for k, v in json_data.items() for k2, v2 in v.items()}

print(res)
# {'location_city': 'San Francisco', 'location_state': 'CA', 'location_country': 'USA'}


# 2. Use pandas 
print(pd.json_normalize(json_data, sep="_").to_json(orient="records"))
# [{"location_city":"San Francisco","location_state":"CA","location_country":"USA"}]