open JSON file with pandas DataFrame

user_11741386 0
Viewed 854

sorry for this trivial question:

I have a json file first.json and I want to open it with pandas.read_json:

df = pandas.read_json('first.json') gives me next result: it must be just 1 row with many columns

The result I need is one row with keys('name', 'street', 'geo', 'servesCuisine' etc.) as columns. I tried to change different"orient" param but it doesn't help. How can I achieve the desired DataFrame format?

This is the data in my json file:

{
    "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"
}
pandas dictionary python json
2 Answers

you can try

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)

output:

                         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
user_50329410

You can read the JSON file with Python command, convert it to dict object, then, hand-pick data items to create a new dataframe from it.

import pandas as pd

# open/read the json data file
fo  = open("test11.json", "r")
injs = fo.read()
#print(injs)
inp_json = eval(injs)  #make it an object

# Or 
# inp_json = your_json_data

# prepare 1 row of data
axis1 = [[inp_json["name"], inp_json["address"]["street"], inp_json["geo"], inp_json["servesCuisine"],
          inp_json["aggregateRatings"]["tripadvisor"]["ratingValue"],
          inp_json["id"],
         ], ] #for data
axis0 = ['row_1', ]  #for index
heads = ["name", "add_.street", "geo", "servesCuisine",
        "agg_.tripadv_.ratingValue", "id", ]

# create a dataframe using the prepped values above
df0 = pd.DataFrame(axis1, index=axis0, columns=heads)


# see data in selected columns only
df0[["name","add_.street","id"]]

                             name  add_.street      id
row_1  La Continental (San Telmo)  Defensa 701  585777
user_21774130
Related