Pandas Convert JSON to DataFrame

You can convert JSON to pandas DataFrame by using json_normalize(), read_json() and from_dict() functions. Some of these methods are also used to extract data from JSON files and store them as DataFrame. JSON stands for JavaScript object notation. JSON is used for sharing data between servers and web applications.

In this article, I will cover how to convert JSON to DataFrame by using json_normalize(), read_json() and DataFrame.from_dict() functions.

Key Points –

Use pandas.read_json() to read JSON data directly into a DataFrame.
Ensure that the Pandas library is imported in your Python environment.
Use appropriate methods to read JSON data from a file, URL, or a JSON string.
The orient parameter controls how JSON data is interpreted (e.g., "records", "split", "index").
Use the json_normalize() function to flatten nested JSON structures for easier conversion.
If the JSON data is in line-delimited format, specify lines=True in the reading method.
After conversion, you can save or export the DataFrame to various formats (e.g., CSV, Excel).
Data types of columns can be inferred automatically or set explicitly with the dtype argument.

Quick Examples of Convert JSON to DataFrame

If you are in a hurry, below are some quick examples of how to convert JSON to DataFrame.

Now let’s see with an example. first, create a string that contains JSON.


import pandas as pd
import json
from pandas import json_normalize
data = '''
{
"technologies":
         [
         { "Courses": "Spark", "Fee": 22000,"Duration":"40Days"},
         { "Courses": "PySpark","Fee": 25000,"Duration":"60Days"},
         { "Courses": "Hadoop", "Fee": 23000,"Duration":"50Days"}
         ],
"status": ["ok"]
}
'''
print("Create JSON data:\n", data)

Yields below output.

Pandas Convert JSON String to DataFrame

The json_normalize() function is used to convert the JSON string into a DataFrame. You can load JSON string using json.loads() function. Pass JSON object to json_normalize(), which returns a Pandas DataFrame. To load JSON data, I am using the JSON Python library.


# Use json_normalize() to convert JSON to DataFrame
dict = json.loads(data)
df2 = json_normalize(dict['technologies']) 
print("After converting JSON data to DataFrame:\n", df2)

Yields below output.

Read JSON File into DataFrame

You can convert JSON to Pandas DataFrame by simply using read_json(). Just pass JSON string to the function. It takes multiple parameters, for our case, I am using orient that specifies the format of JSON string. This function is also used to read JSON files into pandas DataFrame.


import pandas as pd
jsonStr = '''{"Index0":{"Courses": "Pandas","Discount": "1200"},
           "Index1":{"Courses": "Hadoop","Discount": "1500"},
           "Index2":{"Courses": "Spark","Discount": "1800"}
          }'''
# Convert JSON to DataFrame Using read_json()
df2 = pd.read_json(jsonStr, orient ='index')
print(df2)

Yields below output.


# Output:
       Courses  Discount
Index0  Pandas      1200
Index1  Hadoop      1500
Index2   Spark      1800

Use DataFrame.from_dict() to Convert JSON to DataFrame

First, load the JSON string to a dict object and then use pd.DataFrame.from_dict(data, orient="index") to create a DataFrame from the dict object where keys from the dict are used as an index. Setting orient param to "columns" creates a DataFrame with keys from data as its column names.


# Use DataFrame.from_dict() 
# To convert JSON to DataFrame
import pandas as pd
import json
from pandas import json_normalize
json_string = '{ "Courses": "Spark", "Fee": 22000,"Duration":"40Days"}'
data = json.loads(json_string)

# Use pandas.DataFrame.from_dict() 
# To Convert JSON to DataFrame
df2 = pd.DataFrame.from_dict(data, orient="index")
print(df2)

Yields below output.


# Output:
               0
Courses    Spark
Fee        22000
Duration  40Days

FAQ on Convert JSON to Pandas DataFrame

Pandas is an open-source data manipulation library in Python that provides data structures and functions for working with structured data. Converting JSON data to a Pandas DataFrame is useful when you have JSON data and need to analyze, clean, or manipulate it using Pandas' powerful tools and functions.

JSON (JavaScript Object Notation) is a lightweight data interchange format. JSON data consists of key-value pairs and is often used to store structured data. For example,
{ "name": "John", "age": 30, "city": "New York" }

You can read JSON data into a Pandas DataFrame using the pd.read_json() function. For example, df = pd.read_json("data.json")

You can convert JSON-like data in a Python dictionary to a DataFrame using pd.DataFrame. For example,
data = { "name": ["John", "Alice", "Bob"], "age": [30, 25, 35] } df = pd.DataFrame(data)

You can export a Pandas DataFrame to JSON using the to_json() method. For example, df.to_json("output.json", orient="records")

Conclusion

In this article, you have learned how to convert JSON to DataFrame by using json_normalize(), read_json() and DataFrame.from_dict() methods and with more examples.

Happy Learning !!

Pandas Convert JSON to DataFrame

Quick Examples of Convert JSON to DataFrame

Pandas Convert JSON String to DataFrame

Read JSON File into DataFrame

Use DataFrame.from_dict() to Convert JSON to DataFrame

FAQ on Convert JSON to Pandas DataFrame

Conclusion

Related Articles

References