Saturday, June 22, 2024
HomeMachine LearningThe way to Convert JSON Information right into a DataFrame with Pandas

The way to Convert JSON Information right into a DataFrame with Pandas

How to Convert JSON Data into a DataFrame with PandasHow to Convert JSON Data into a DataFrame with Pandas
Picture by Writer | DALLE-3 & Canva


In the event you’ve ever had the prospect to work with information, you’ve got most likely come throughout the necessity to load JSON recordsdata (brief for JavaScript Object Notation) right into a Pandas DataFrame for additional evaluation. JSON recordsdata retailer information in a format that’s clear for folks to learn and likewise easy for computer systems to grasp. Nevertheless, JSON recordsdata can generally be difficult to navigate by. Due to this fact, we load them right into a extra structured format like DataFrames – that’s arrange like a spreadsheet with rows and columns.

I’ll present you two other ways to transform JSON information right into a Pandas DataFrame. Earlier than we talk about these strategies, let’s suppose this dummy nested JSON file that I am going to use for instance all through this text.

"books": [
"title": "One Hundred Years of Solitude",
"author": "Gabriel Garcia Marquez",
"reviews": [
"reviewer": {
"name": "Kanwal Mehreen",
"location": "Islamabad, Pakistan"
"rating": 4.5,
"comments": "Magical and completely breathtaking!"
"reviewer": {
"name": "Isabella Martinez",
"location": "Bogotá, Colombia"
"rating": 4.7,
"comments": "A marvelous journey through a world of magic."
"title": "Issues Fall Aside",
"writer": "Chinua Achebe",
"evaluations": [
"reviewer": {
"name": "Zara Khan",
"location": "Lagos, Nigeria"
"rating": 4.9,
"comments": "Things Fall Apart is the best of contemporary African literature."


The above-mentioned JSON information represents a listing of books, the place every guide has a title, writer, and a listing of evaluations. Every evaluate, in flip, has a reviewer (with a reputation and placement) and a score and feedback.


Methodology 1: Utilizing the json.load() and pd.DataFrame() features


The best and most easy method is to make use of the built-in json.load() perform to parse our JSON information. It will convert it right into a Python dictionary, and we are able to then create the DataFrame instantly from the ensuing Python information construction. Nevertheless, it has an issue – it might probably solely deal with single nested information. So, for the above case, when you solely use these steps with this code:

import json
import pandas as pd

#Load the JSON information

with open('books.json','r') as f:
information = json.load(f)

#Create a DataFrame from the JSON information

df = pd.DataFrame(information['books'])



Your output would possibly appear like this:

json.load() outputjson.load() output

Within the evaluations column, you possibly can see the whole dictionary. Due to this fact, in order for you the output to seem accurately, you need to manually deal with the nested construction. This may be completed as follows:

#Create a DataFrame from the nested JSON information

df = pd.DataFrame([
'title': book['title'],
'writer': guide['author'],
'reviewer_name': evaluate['reviewer']['name'],
'reviewer_location': evaluate['reviewer']['location'],
'score': evaluate['rating'],
'feedback': evaluate['comments']
for guide in information['books']
for evaluate in guide['reviews']


Up to date Output:
json.load() outputjson.load() output

Right here, we’re utilizing checklist comprehension to create a flat checklist of dictionaries, the place every dictionary accommodates the guide info and the corresponding evaluate. We then create the Pandas DataFrae utilizing this.

Nevertheless the difficulty with this method is that it calls for extra guide effort to handle the nested construction of the JSON information. So, what now? Do we’ve got another possibility?

Completely! I imply, come on. Provided that we’re within the twenty first century, dealing with such an issue with no resolution appears unrealistic. Let’s have a look at the opposite method.


Methodology 2 (Beneficial): Utilizing the json_normalize() perform


The json_normalize() perform from the Pandas library is a greater technique to handle nested JSON information. It mechanically flattens the nested construction of the JSON information, making a DataFrame from the ensuing information. Let’s check out the code:

import pandas as pd
import json

#Load the JSON information

with open('books.json', 'r') as f:
information = json.load(f)

#Create the DataFrame utilizing json_normalize()

df = pd.json_normalize(
meta=['title', 'author'],



json.load() outputjson.load() output

The json_normalize() perform takes the next parameters:

  • information: The enter information, which generally is a checklist of dictionaries or a single dictionary. On this case, it is the info dictionary loaded from the JSON file.
  • record_path: The trail within the JSON information to the data you need to normalize. On this case, it is the ‘evaluations’ key.
  • meta: Extra fields to incorporate within the normalized output from the JSON doc. On this case, we’re utilizing the ‘title’ and ‘writer’ fields. Word that columns in metadata normally seem on the finish. That is how this perform works. So far as the evaluation is worried, it would not matter, however for some magical purpose, you need these columns to seem earlier than. Sorry, however you need to do them manually.
  • errors: The error dealing with technique, which will be ‘ignore’, ‘increase’, or ‘warn’. We’ve got set it to ‘increase’, so if there are any errors in the course of the normalization course of, it’ll increase an exception.


Wrapping Up


Each of those strategies have their very own benefits and use circumstances, and the selection of technique is dependent upon the construction and complexity of the JSON information. If the JSON information has a really nested construction, the json_normalize() perform may be the most suitable choice, as it might probably deal with the nested information mechanically. If the JSON information is comparatively easy and flat, the pd.read_json() perform may be the simplest and most easy method.

When coping with giant JSON recordsdata, it is essential to consider reminiscence utilization and efficiency since loading the entire file into reminiscence won’t work. So, you may need to look into different choices like streaming the info, lazy loading, or utilizing a extra memory-efficient format like Parquet.


Kanwal Mehreen Kanwal is a machine studying engineer and a technical author with a profound ardour for information science and the intersection of AI with medication. She co-authored the book “Maximizing Productiveness with ChatGPT”. As a Google Technology Scholar 2022 for APAC, she champions variety and tutorial excellence. She’s additionally acknowledged as a Teradata Variety in Tech Scholar, Mitacs Globalink Analysis Scholar, and Harvard WeCode Scholar. Kanwal is an ardent advocate for change, having based FEMCodes to empower ladies in STEM fields.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments