Using Pandas for JSON to Excel conversion is putting the entire JSON file in 1 Excel Cell

Question

I am making a call to the Google Places Autocomplete API which is returning data in a JSON formatted browser tab.
I use Beautiful Soup to get this data. I then write it to a file and get an unexpected output from pandas.

    newUrl = webUrl+'json?input='+searchString+'&offset=0'+'&components=country:us&key='+apiKey
    li = r"C:/Users/thebr/iCloudDrive/code/python/captones/capstoneData.json"
    r = requests.get(newUrl)
    html_page = r.content
    soup = BeautifulSoup(html_page, 'html.parser')
    newDictionary = json.loads(str(soup))
    out_file = open("python/captones/nydata.json", "w")
    json.dump(newDictionary, out_file)
    out_file.close()
    df = pd.read_json("python/captones/nydata.json")
    df.to_excel('output.xlsx', index=False)

My output puts the entire JSON file into 1 cell. Do I have to add some parameters?
EDIT: Here's an example JSON that the API provides:

{
    "predictions": [{
        "description": "COVID-19 vaccine location - Stony Brook University, Nicolls Road, Stony Brook, NY, USA",
        "matched_substrings": [{
            "length": 5,
            "offset": 0
        }],
        "place_id": "ChIJw-RIqTo_6IkRnW9u_u9x1b8",
        "reference": "ChIJw-RIqTo_6IkRnW9u_u9x1b8",
        "structured_formatting": {
            "main_text": "COVID-19 vaccine location - Stony Brook University",
            "main_text_matched_substrings": [{
                "length": 5,
                "offset": 0
            }],
            "secondary_text": "Nicolls Road, Stony Brook, NY, USA"
        },
        "terms": [{
            "offset": 0,
            "value": "COVID-19 vaccine location - Stony Brook University"
        }, {
            "offset": 52,
            "value": "Nicolls Road"
        }, {
            "offset": 66,
            "value": "Stony Brook"
        }, {
            "offset": 79,
            "value": "NY"
        }, {
            "offset": 83,
            "value": "USA"
        }],
        "types": ["health", "point_of_interest", "establishment"]
    }, {
        "description": "COVID-19 Vaccine Location - Meadowlands Racing &amp; Entertainment, Racetrack Dr, East Rutherford, NJ, USA",
        "matched_substrings": [{
            "length": 5,
            "offset": 0
        }],
        "place_id": "ChIJ5TThhWP4wokRQ9TLrhSPQRQ",
        "reference": "ChIJ5TThhWP4wokRQ9TLrhSPQRQ",
        "structured_formatting": {
            "main_text": "COVID-19 Vaccine Location - Meadowlands Racing &amp; Entertainment",
            "main_text_matched_substrings": [{
                "length": 5,
                "offset": 0
            }],
            "secondary_text": "Racetrack Dr, East Rutherford, NJ, USA"
        },
        "terms": [{
            "offset": 0,
            "value": "COVID-19 Vaccine Location - Meadowlands Racing &amp; Entertainment"
        }, {
            "offset": 64,
            "value": "Racetrack Dr"
        }, {
            "offset": 78,
            "value": "East Rutherford"
        }, {
            "offset": 95,
            "value": "NJ"
        }, {
            "offset": 99,
            "value": "USA"
        }],
        "types": ["health", "point_of_interest", "establishment"]
    }, {
        "description": "COVID-19 Drive-thru Testing at Walgreens, New York 112, Medford, NY, USA",
        "matched_substrings": [{
            "length": 5,
            "offset": 0
        }],
        "place_id": "ChIJVVUVMLpI6IkRb0XzqEXttHQ",
        "reference": "ChIJVVUVMLpI6IkRb0XzqEXttHQ",
        "structured_formatting": {
            "main_text": "COVID-19 Drive-thru Testing at Walgreens",
            "main_text_matched_substrings": [{
                "length": 5,
                "offset": 0
            }],
            "secondary_text": "New York 112, Medford, NY, USA"
        },
        "terms": [{
            "offset": 0,
            "value": "COVID-19 Drive-thru Testing at Walgreens"
        }, {
            "offset": 42,
            "value": "New York 112"
        }, {
            "offset": 56,
            "value": "Medford"
        }, {
            "offset": 65,
            "value": "NY"
        }, {
            "offset": 69,
            "value": "USA"
        }],
        "types": ["pharmacy", "health", "point_of_interest", "store", "establishment"]
    }, {
        "description": "COVID-19 Vaccine Location - Medgar Evers College, Crown Street, Brooklyn, NY, USA",
        "matched_substrings": [{
            "length": 5,
            "offset": 0
        }],
        "place_id": "ChIJqQOySnFbwokRcormV9Q7bvA",
        "reference": "ChIJqQOySnFbwokRcormV9Q7bvA",
        "structured_formatting": {
            "main_text": "COVID-19 Vaccine Location - Medgar Evers College",
            "main_text_matched_substrings": [{
                "length": 5,
                "offset": 0
            }],
            "secondary_text": "Crown Street, Brooklyn, NY, USA"
        },
        "terms": [{
            "offset": 0,
            "value": "COVID-19 Vaccine Location - Medgar Evers College"
        }, {
            "offset": 50,
            "value": "Crown Street"
        }, {
            "offset": 64,
            "value": "Brooklyn"
        }, {
            "offset": 74,
            "value": "NY"
        }, {
            "offset": 78,
            "value": "USA"
        }],
        "types": ["health", "establishment"]
    }, {
        "description": "COVID-19 Drive-Thru Testing at Walgreens, West Main Street, Patchogue, NY, USA",
        "matched_substrings": [{
            "length": 5,
            "offset": 0
        }],
        "place_id": "ChIJVVVlOxpJ6IkRDcNMmDf-qi8",
        "reference": "ChIJVVVlOxpJ6IkRDcNMmDf-qi8",
        "structured_formatting": {
            "main_text": "COVID-19 Drive-Thru Testing at Walgreens",
            "main_text_matched_substrings": [{
                "length": 5,
                "offset": 0
            }],
            "secondary_text": "West Main Street, Patchogue, NY, USA"
        },
        "terms": [{
            "offset": 0,
            "value": "COVID-19 Drive-Thru Testing at Walgreens"
        }, {
            "offset": 42,
            "value": "West Main Street"
        }, {
            "offset": 60,
            "value": "Patchogue"
        }, {
            "offset": 71,
            "value": "NY"
        }, {
            "offset": 75,
            "value": "USA"
        }],
        "types": ["health", "point_of_interest", "establishment"]
    }],
    "status": "OK"
}

Also to add, this is how the output looks after converting to excel:

And this is how it looks using an online json to excel Converter (What I want):

Please, add a sample JSON to reproduce. – Max Pierini Apr 25 '21 at 22:24 — Max Pierini, Apr 25 '21 at 22:24
Added the JSON @MaxPierini – Frank Fiumara Apr 25 '21 at 22:50 — Frank Fiumara, Apr 25 '21 at 22:50

score 0 · Accepted Answer · answered Apr 25 '21 at 23:23

It can help you select only the predictions object and then reading it with pandas. To flatten out more the output you will need additional work. You can look here for an example Conversion from nested json to csv with pandas

import json
import pandas as pd

dic = json.load("python/captones/nydata.json")
dic_predictions = dic["predictions"]
df = pd.read_json(dic_predictions)
df.to_excel('output.xlsx', index=False)

Using Pandas for JSON to Excel conversion is putting the entire JSON file in 1 Excel Cell

1 Answers1