python - 使用 Pandas DataFrame 打开 JSON 文件

Question

抱歉这个微不足道的问题:
我有一个 json 文件 first.json我想用 pandas.read_json 打开它:df = pandas.read_json('first.json')给我下一个结果:

我需要的结果是一行以键('name'、'street'、'geo'、'servesCuisine' 等)作为列。我试图改变不同的“orient”参数，但它没有帮助。我怎样才能达到预期的DataFrame格式？
这是我的json文件中的数据:

{
    "name": "La Continental (San Telmo)",
    "geo": {
        "longitude": "-58.371852",
        "latitude": "-34.616099"
    },
    "servesCuisine": "Italian",
    "containedInPlace": {},
    "priceRange": 450,
    "currenciesAccepted": "ARS",
    "address": {
        "street": "Defensa 701",
        "postalCode": "C1065AAM",
        "locality": "Autonomous City of Buenos Aires",
        "country": "Argentina"
    },
    "aggregateRatings": {
        "thefork": {
            "ratingValue": 9.3,
            "reviewCount": 3
        },
        "tripadvisor": {
            "ratingValue": 4,
            "reviewCount": 350
        }
    },
    "id": "585777"
}

Answer 1

你可以试试

with open("test.json") as fp:
    s = json.load(fp)

# flattened df, where nested keys -> column as `key1.key2.key_last`
df = pd.json_normalize(s)

# rename cols to innermost key only (be sure you don't overwrite cols)
cols = {col:col.split(".")[-1] for col in df.columns}
df = df.rename(columns=cols)

输出:

                         name servesCuisine  priceRange currenciesAccepted      id  ...    country ratingValue reviewCount ratingValue reviewCount
0  La Continental (San Telmo)       Italian         450                ARS  585777  ...  Argentina         9.3           3           4         350