Python Create Table From JSON

JSON or JavaScript Object Notation is one of the most popular data exchange formats. It is commonly used in APIs and NoSQL databases due to its simplicity and readability.

However, it is not as straightforward when it comes to analyzing JSON. Hence, in this tutorial, we will learn how to convert a JSON file into a Pandas table.

Sample Data

The first step is to have the JSON data we wish to parse. We have selected a simple JSON file containing astronomy information for a specific city for this tutorial.

Sample data is as shown:

    "country": "United Kingdom,"
    "state": "England",
    "city": "London",
    "latitude": 51.466652350000004,
    "longitude": -0.09686637642617651,
    "date": "2022-04-13",
    "current_time": "03:12:55.044",
    "sunrise": "06:09",
    "sunset": "19:53",
    "sun_status": "-",
    "solar_noon": "13:01",
    "day_length": "13:44",
    "sun_altitude": -23.19751117067553,
    "sun_distance": 1.4988500851835912E8,
    "sun_azimuth": 35.781559107335625,
    "moonrise": "15:43",
    "moonset": "05:28",
    "moon_status": "-",
    "moon_altitude": 20.615536932562232,
    "moon_distance": 387894.3437906608,
    "moon_azimuth": 266.5048405334666,
    "moon_parallactic_angle": 34.5669393631715

Save the JSON file as astronomy_simple.json

Read JSON With Pandas

We will use Pandas to read the JSON file and convert it into a table.

Start by importing pandas:

# import pandas
import pandas as pd

Next, we will read the JSON file using the read_json function. This allows us to convert a JSON string to a pandas object as shown:

pd_object = pd.read_json('astronomy_simple.json', typ='series')

Once we have the JSON file converted into a pandas object, we can convert it into a pandas DataFrame as shown:

df = pd.DataFrame(pd_object)

Finally, to print the data in a tabular format, use the display func as shown:


This should return:


This short article describes a simple method to convert a JSON file into a table using Pandas.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list