How to Tabulate JSON in Python

Introduction

In this article, we will be discussing how we can tabulate JSON in Python.

Before we get into the coding part, let’s discuss some of the points regarding JSON and its use cases.

What is JSON?

JSON stands for JavaScript Object Notation is a lightweight data-interchange format.

It is both human and machine-readable often used for serializing structured data and exchanging it over a network, typically between server and web application.

What is Tabular data?

Tabular data is the data format that is organized in a table with rows and columns. For example, most data that is saved in an excel spreadsheet is considered tabular data.

Some of the advantages of tabular data are:

  • It helps in simplifying the raw data.
  • A large amount of data can be easily confined in a data table.
  • Data tables are frequently used for statistical analysis like calculation of central tendency, dispersion, etc.

Therefore, there will be a situation while dealing with Python where we want to tabulate JSON data in order to find out the pattern and quickly get an overview of the data.

In such cases, a tabular format comes into use. Below is an example that presents a JSON object(containing two elements inside the list) in tabular form.

tabulate JSON in Python

Python provides a built-in module called “json” to work with JSON files. To retrieve the JSON object from the “.json” files we have to use json.load() function.

After loading that file, Python treats those objects as a Python dictionary that consists of a series of key-value pairs.

 

Different ways to tabulate JSON

Here in this article, we will be discussing three different ways of tabulating JSON data.

  1. Using the pandas library
  2. Using tabulate library
  3. Creating a custom function

Throughout the article will be considering the below JSON file to be tabulating in all the methods.

cars_json

Let’s create a function that will fetch this file so that we can call it on different methods as:

import json


def fetch_json():
    """function to fetch json objects in Python from file"""
    with open('cars.json', 'r') as f:
        json_object = json.load(f)
    return json_object

Output:

cars json python

 

1. Using the pandas library

pandas is an open-source, high-performance, easy-to-use data structure and data analysis tool for Python.

We have to install the library as it is not the Python standard library using pip.

$ pip install pandas

We can tabulate JSON using pandas in two ways: pd.DataFrame() and pd.DataFrame.from_dict() functions.

pandas DataFrame() function is used to present data in a two-dimensional data structure or a table with rows and columns.

By pd.DataFrame()

import pandas as pd

def using_pd_dataframe():
    """function to tabulate json using pd.DataFrame"""

    get_json = fetch_json() # fetch cars.json file

    df = pd.DataFrame(columns=get_json[0].keys())

    for i in range(len(get_json)):
        df.loc[i] = get_json[i].values()

    print(df)

# call function
using_pd_dataframe()

 

By pd.DataFrame.from_dict()

def using_pd_dataframe_dict():
    """function to tabulate json using pd.DataFrame.from_dict()"""

    get_json = fetch_json() # fetch cars.json file

    df = pd.DataFrame.from_dict(get_json)

    print(df)

# call function
using_pd_dataframe_dict()

When we execute both above functions, we get the same outputs:

Tabulate Output JSON Python

 

2. Using tabulate library

tabulate is the Python third-party library that can be used as an alternative to pandas in order to tabulate JSON format data.

To install the library, in the terminal type:

$ pip install tabulate

Example

from tabulate import tabulate


def using_tabulate_library():
    """function to tabulate json using tabulate library"""
    get_json = fetch_json() # fetch cars.json file

    table = tabulate(get_json, headers="keys")

    print(table)

using_tabulate_library()

Output:

Car Name     Car Model    Car Price
-----------  -----------  -----------
Honda City   City         20,000 USD
Honda City   City         20,000 USD
Range Rover  V5           250,000 USD
Toyota       Toyota V1    15,000 USD

Advantage

The advantage of using this library is that there is more than one way to format a table in plain format. For that, we have to pass an extra argument named tablefmt defines how the table is formatted.

Some of the supported table formats are:

  • “plain”,
  • “simple”,
  • “github”,
  • “grid”,
  • “simple_grid”,
  • “rounded_grid”,
  • “heavy_grid”,
  • “mixed_grid”, etc.

Let’s apply “github” as a table format.

table = tabulate(get_json, headers="keys", tablefmt="github")

Output:

| Car Name    | Car Model   | Car Price   |
|-------------|-------------|-------------|
| Honda City  | City        | 20,000 USD  |
| Honda City  | City        | 20,000 USD  |
| Range Rover | V5          | 250,000 USD |
| Toyota      | Toyota V1   | 15,000 USD  |

 

3. Creating a custom function

We can create our own custom function to tabulate the data.

def creating_custom_func():
    """function to tabulate using custom function"""
    get_json = fetch_json() # fetch cars.json file

    headers = list(get_json[0].keys())
    
    for column_name in headers:
        print(column_name, end="\t")
    print("\n")
    for item in get_json:
        rows = list(item.values())
        for row in rows:
            print(row, end="\t")
        print("\n")


# call function here
creating_custom_func()

Output:

Car Name        Car Model       Car Price

Honda City      City    20,000 USD

Honda City      City    20,000 USD

Range Rover     V5      250,000 USD

Toyota  Toyota V1       15,000 USD

Here in the above function, first, we fetch the JSON data and extract the headers only in a list and print them separated by a tab.

After that, we loop through the list and retrieve only values from the dictionary and print them.

Note: Right this function works for the list containing a dictionary as an element only. To make it robust for all the values, we have to edit it according to our use cases.

 

Conclusion

KUDOS, you have completed all the possible methods that you can use to tabulate JSON in Python.

Here, in this article, we discussed the JSON and how to work with it in Python using json module. We have gone through the importance of tabular format data and implemented three methods of creating it.

If you have any questions, leave them in the comments. I’ll try my best to get back to you.

Take care and Happy Coding!

 

Check out our other posts(You may it)

How to Remove Image Background in Python(Freely)

How to Handle Null Values in Pandas

How to Create Word Cloud in Python

How to use Sys Module in Python

 

Reference

JSON official docs

Pandas library

Tabulate library

JSON module

Leave a Comment