How to Load and Manipulate JSON Files with Pandas

Introduction

JSON stands for JavaScript Object Notation, a standard text-based format (a lightweight format) used to store and transport data on the web. The JSON format is similar to the dictionary format in Python. The JSON module is a built-in module that provides us with various methods and functions that helps us to deal with the JSON data.

Pandas library is an open-source (free to use) library that is built on top of another very useful Python library i.e., NumPy library. Now for reading the JSON data, we have the read_json() function of the Pandas module. The read_json() function is used for converting JSON data into a Pandas's DataFrame.

What is a JSON File? Why is it Used in Pandas?

JSON stands for JavaScript Object Notation. It is a basic or standardized design used for the transportation and storage of data. It is a plain text written in JavaScript Object Notation which is language-independent. JSON is a lightweight data interchange format. JSON is a self-describing format, and it is easy to understand.

JSON is easily readable by both machines and humans. Previously, XML was used to store data, but XML was less human-readable and also not as user-friendly as JSON. That is why JSON is used, as it is easy and fast to access and also manipulated as it only contains texts. JSON is a file format that stores data in the format of key-value pair, which is similar to the object properties of JavaScript and dictionaries in Python. The syntax for writing the key-value pair in a JSON file is writing them inside double quotes separated by a colon(:).

Now before learning about reading JSON files in pandas, and the relationship among JSON, Pandas, and CSV files, let us first get an introduction to CSV files.

CSV stands for Comma Separated Values, which is nothing but a plain text file containing some list of values. CSV files are frequently used in data exchange, data storage, etc. The data in the database is often stored in CSV files for exchange.

Example of CSV file:

One of the main features of CSV files is that data exportation and data importation in other programs is easier. Just like JSON files, CSV files are also easily human-readable, and we can view the CSV files using text editors like Notepad, Notepad++, etc. We can also open the CSV files using Excel, Google Sheets, LibreOffice Calc, etc.

Now, let us understand how we can perform reading JSON files in pandas.

Python has a rich variety of modules, packages, and libraries, making it so popular among other programming and scripting languages like C, C++, JavaScript, etc. One such popular library is the Pandas library. The Pandas library is an open-source (free to use) library that is built on top of another very useful Python library i.e. NumPy library. The Pandas package helps us work with large data sets (or data frames) in rows and columns. We generally use the Pandas package to deal with the CSV (Excel) files. The prime reason for the Pandas package's popularity is its easy importing feature and easy data analyzing data feature. Pandas module is quite fast and comes in very handy because of its high performance and productivity.

As we have understood earlier, huge data files are usually stored in JSON format. So, we can read those big JSON data using the Pandas module. We can use the read_json() function to do so. Let us first take a sample JSON data, and then let us use the read_json() function to read the JSON data.

Sample JSON File

Let us take a sample JSON data of the exercise of a human stored in a JSON file.

We can use the read_json() function of the JSON module to read the data using the Pandas module.

Example:

Output:

Refer to the next section for more details about the read_json() function.

JSON Module Functions

We can also read the JSON data using the JSON module in Python. Let us learn about some of the functions of the JSON module. The JSON module is a built-in Python module. So, before using the JSON module function, we must import it using import json in our Python program or script.

Let us look at some of Python's most commonly used JSON module functions.

1. load(): The load() function is used to deserialize or convert the JSON data into its corresponding Python object. The load() function takes the files object as a parameter and then parses the JSON data. The load() function populates the Python dictionary with the provided data and then returns the dictionary.
2. loads(): The loads() function is also used to deserialize or convert the JSON data into its corresponding Python object. The loads() function is usually used with the JSON strings, so if we have JSON strings, we can parse them using the loads() method. The loads() method does not take the file path, but it takes the entire content of the files in the form of a string. The loads() function uses the fileobject.read() in the backend to return the content of the file in dictionary format.
3. dump(): The dump function in Python is a method found in the JSON module. The dump function in Python is mainly used when we want to store and transfer objects (Python objects) into a file in the form of JSON. The dump function operates quite efficiently. The dump function in Python takes a large variety of arguments. We need to provide the object that needs to be dumped into the file in JSON format as well as the name of the file in which we want to dump our object. The dump function in Python returns a string object (<class 'str'>) which can be used to print the dumped data.
4. dumps(): The dumps function in Python is used when the objects are in string format, and it is mainly used in the case of parsing and printing. The dumps function in Python does not require the JSON file name in which we want to dump our data. The dumps function in Python works two times slower than the dump function. The dumps function in Python directly writes the data into the JSON file.

Understanding the read_json() Function

The read_json() is a function in the Pandas library that helps us read JSON data or JSON data files. We can pass the path of the JSON file or we can pass a Python dictionary (as it is similar to JSON) to read it. The read_json() function takes a JSON file and returns a DataFrame that we can print further. The DataFrame in Pandas is nothing but the representation of a table consisting of rows and columns. We can also pass the URL (location) of the JSON file if the file is present on the remote servers.

Syntax of the read_json() function is:

Let us now learn about the parameters of the read_json() function.

• path_or_buf: It resembles the path of the JSON file, whether on the local server or the remote server. We can also pass a valid JSON string here.
• orient: It resembles the expected JSON string format. The default value of the orient parameter is None.
• typ: It resembles the type of object to be recovered. The default value of the typ parameter is frame.
• convert_axes: The convert_axes parameter is used to convert the aces to the proper dtypes. The default value of the convert_axes parameter is None.
• encoding: The encoding parameter resembles the encoding to be used to decode the py3 bytes. The default value of the encoding parameter is None.
• encoding_errors: The encoding_errors parameter resembles how the encoding errors are treated if they are generated. The default value of the encoding_errors parameter is strict.
• lines: The lines parameter resembles if the file has to be read as a JSON object per line or not. The default value of the line parameter is False.
• chunksize: The chuncksize parameter returns an object of JsonReader for object iteration. The default value of the chunksize parameter is None.
• nrows: The nrows parameter resembles the number of lines from the line-delimited jsonfile that has to be read. It is an optional parameter.

The read_json() function returns a data frame or a series.

The read_json() method helps us to perform a function such as:

• It helps in reading a simple JSON file from our local storage.
• It also helps in reading a simple JSON file from remote storage using a URL.
• It helps us in reading JSON data and converting it into a Python dictionary.
• It also helps in flattening the nested list from a JSON object.
• The read_json() function also flattens the nested list and dictionary from the JSON object.
• The read_json() function can also help us in extracting a value from a deeply nested JSON file.

Please refer to the next section for more details about parameters and examples.

How to Load the JSON into a DataFrame

Let us now learn how we load JSON data into a DataFrame. As we have learned that JSON data is nothing but a dictionary in Python and DataFrame can be easily constructed from JSON data. Let us take two scenarios in which we have a JSON file present on the local server and a JSON file present on the remote server. We have already seen the example of reading JSON data in the above section. Now we will take JSON files and try to read them and load them into a DataFrame.

Reading JSON From Local File

For reading JSON data present in a local file, we can use the read_json() function of the Pandas library and then pass the path of the file as the parameter to the read_json() function. Refer to the example provided below for more clarity.

For this example, we have a JSON file, namely data.json present in our local system in the same folder. If we have the file present in some other folder then we can pass the path of the file.

The data.json file contains data of the students of a school. Let us look at the data first before getting into the program.

Code:

Output:

Now, if we want to check whether the result obtained is a DataFrame or not, we can check its type or we can use another function named data_frame.info() which will return us detailed information of the result.

The result obtained on running the data_frame.info() command is:

The result obtained on running the type(data_frame) command is:

Reading JSON from a URL

For reading JSON data present in the remote file, we can use the read_json() function of the Pandas library and then pass the URL of the file as the parameter to the read_json() function. Refer to the example provided below for more clarity.

For this example, we have a JSON file present in a remote server. So, we can provide the URL of the file into the function as a parameter to get the DataFrame as returned result.

Output:

Similar to the previous example, if we want to check whether the result obtained is a DataFrame or not, we can check its type, or we can use another function named the data_frame.info() function, which will return us detailed information of the result. We can also check the type of the returned DataFrame using the type() function.

How to Read a JSON File in Python

Let us take the example of the speed of cars on various data and try to create a DataFrame out of it.

Output:

Want to Explore Further? Scaler Data Science Course Delivers In-Depth Insights to Become a Skilled Data Scientist. Enroll Now!