How to Tabulate JSON in Python

To tabulate JSON data in Python, use the pd.DataFrame() function. The pd.DataFrame() is a Pandas library function that helps us create two-dimensional, size-mutable, potentially heterogeneous tabular data in Python.

JSON stands for JavaScript Object Notation. It is somewhat similar to the Python dictionary. The JSON data type is mainly used to exchange data between clients and servers in web applications.

JSON object consists of values, and we can convert these values into a table by using a Pandas package. Pandas library provides a function called pd.DataFrame() that will convert these objects into table-like structures.

Python Program to Tabulate JSON using Pandas

You can create a sample.json file in your current working directory, put the data, and then run the program below.

import pandas as pd
import json

with open("/Users/krunallathiya/Desktop/Code/R/sample.json") as f:
  d = json.load(f)
  df = pd.DataFrame(columns=d[0].keys())
   for i in range(len(d)):
     df.loc[i] = d[i].values()
     print(df)

In this program, we imported json and pandas packages. The json package is for working with a json data, and the Pandas package is for converting the json object into a table, which you can call a DataFrame.

We opened a json file containing all the contents that needed to be converted into a table. Next, we have opened the sample.json file. This consists of json objects. 

Then we loaded the json data from the file to a variable called the d. Now, this variable d consists of json objects. In Python, JSON objects are treated as a dictionary. Hence it consists of key-value pairs. Key is the column name, and value is the value for that field.

We created a Data Frame with columns as the json objects keys. The dict.keys() function extracts all the dictionary keys separately. Hence if we execute this function, all the json objects keys will be extracted. These keys are given as the column name for the data frame. 

We traverse the json object and add each row to the data frame. Hence a table is formed we can print the data frame to see the result.

Using from_dict() function

The from_dict() is a Pandas library function that constructs DataFrame from dict of array-like or dicts. 

import pandas as pd
import json

with open("/Users/krunallathiya/Desktop/Code/R/sample.json") as f:
  d = json.load(f)
  df = pd.DataFrame.from_dict(d)
  print(df)

In this program, we imported json and pandas packages. Then, we opened the sample.json file and loaded all the contents from the file to a variable called data.

Now, this data variable contains the json object. This is similar to a dictionary. Hence we can convert this dictionary directly into a data frame. We can do this by using a function called the from_dict(). This function is used to convert the dictionary into a DataFrame. Hence the tabular data will be created from json file.

Complete Program of Tabulate JSON in Python

import json
import pandas as pd

json_data = [
 {'userId': 1,
 'number': 45,
 'name': 'Raj'},
 {'userId': 2,
 'number': 46,
 'name': 'Ram'},
 {'userId': 3,
 'number': 47,
 'name': 'Rahu'}
]

with open("sample.json", "w") as f:
  json.dump(json_data, f)

with open("sample.json", "r") as f:
  print(json.load(f))

with open("sample.json") as f:
  d = json.load(f)
  df = pd.DataFrame(columns=d[0].keys())
  for i in range(len(d)):
    df.loc[i] = d[i].values()
  print(df)

Output

[{'userId': 1, 'number': 45, 'name': 'Raj'}, {'userId': 2, 'number': 46, 'name': 'Ram'}, 
 {'userId': 3, 'number': 47, 'name': 'Rahu'}]

  userId number  name

0   1     45     Raj
1   2     46     Ram
2   3     47     Rahu

Now, you can see that we got the complete tabular data structure from json data in Python.

That’s it for this tutorial.

See also

How to Write JSON File in Python

How to Save Array to File in Python

How to Convert File to String in Python

Leave a Comment