Must-Know Ways to Tabulate JSON in Python

This article will discuss the JSON data format and different ways to tabulate it in Python.

Before we get into the different methods, let’s discuss JSON.

You may have learned to handle primary forms of data in Python through CSV and XLSX files. But data can also be transmitted between a server and the front end. This is where the JSON format comes into play. Web services and APIs (Application Programming Interface) make use of JSON.

What is a JSON?

JSON is used for transmitting serialized and structured data over a network connection. JSON is compatible across multiple Operating Systems and easily editable using text editors.

Structure of a JSON

{
"CEOs":[
    {"firstName":"Sundar", "lastName":"Pichai"},
    {"firstName":"Jeff", "lastName":"Bezos"},
    {"firstName":"Warren", "lastName":"Buffet"}
]
}

• The data is within name/value pairs (firstName , lastName)
• The data is separated using commas ({name:value , name:value})
• The curly brackets hold multiple objects ({object:object, object:object, object:object})

Advantages of Tabular Data View

Let’s look at the following advantages of Tabular Data representation

• It makes the representation of data easy.
• Makes it easy to analyze the data.
• Makes it easy to compare data.
• It saves space and time.
• Tabulated data can be presented in the form of diagrams and graphs

Different Ways To Tabulate JSON in Python

1. Tabulate JSON Using Pandas

With the help of pd.DataFrame() function, we can tabulate JSON data. pd.DataFrame() allows us to create 2D, size-mutable tabular data within Python.

JSON, in certain aspects, is similar to a Python Dictionary. Therefore, we can convert its values into a Pandas Dataframe. pd.DataFrame() converts JSON objects into table-like structures.

In the following program, we are tabulating myFile.json from the working directory.

import pandas as pd
import json

with open("/content/myJSON.json") as myFile:
  fileData = json.load(myFile)
  dataFrame = pd.DataFrame(columns=fileData[0].keys())
  for i in range(len(fileData)):
     dataFrame.loc[i] = fileData[i].values()
     print(dataFrame)
   

Output/Explanation

  userId number             name
0      1     45  Mark Zuckerberg
1      2     46       Larry Page
2      3     47       Bill Gates

For this program, we’ve used JSON and Pandas modules. The JSON package is for handling JSON data in Python. The Pandas module allows us to tabulate JSON data into a “DataFrame”.

myJSON.json is the file that contains the JSON data. Variable myFile stores the JSON data. As we discussed, JSONs are similar to Python dictionaries. Therefore, we sort into key-value pairs. The key is the column name, whereas the value is the value for that field.

2. Tabulate JSON Using from_dict() Function

from_dict() function creates DataFrame objects from dictionaries. Let’s look at the following program:

import pandas as pd
import json

with open("/content/myJSON.json") as myFile:
  fileData = json.load(myFile)
  dataFrame = pd.DataFrame.from_dict(fileData)
  print(dataFrame)

Output/Explanation

   userId  number             name
0       1      45  Mark Zuckerberg
1       2      46       Larry Page
2       3      47       Bill Gates

In this program, we used the same modules as the previous implementation. After the data variable fileData is created, we pass it into .DataFrame.from_dict(fileData). This creates a pandas DataFrame from a dictionary. Therefore, tabular data is created from JSON.

3. Using the tabulate Module

The tabulate module allows us to display tabular data in Python within the console. This module is not part of the Python Standard Library. Install the module manually using PIP. The following tabular types are supported by this module:

• list of lists or another iterable of iterables
• list or another iterable of the dictionary (keys as columns)
• dictionary of iterables (keys as columns)
• two-dimensional NumPy array
• NumPy record arrays (names as columns)
• pandas.DataFrame

By calling tabulate(), we can create tabular data.

print(tabulate({"Employees": ["John", "Adam"], "Role": ["CEO","COO" ]}, headers="keys"))

Output

Role  Name
-----  ------
CEO  John
COO  Adam

By passing "keys" under headerThe keys of the dictionary are used as column names.

FAQs on Python Tabulate JSON

Conclusion

In this article, we have discussed the JSON data format and its uses.

We have also discussed the importance of tabulating data.

The three primary ways to tabulate JSON in Python are :

• Using the Pandas Module
• Using from_dict() function
• Using tabulate module