Pandas Convert JSON to DataFrame

  • Post author:
  • Post category:Pandas
  • Post last modified:March 27, 2024
  • Reading time:14 mins read
You are currently viewing 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.

Related: You can convert Pandas DataFrame to JSON string.

1. 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.


# Below are the quick examples

# Example 1: Use json_normalize() to convert JSON to DataFrame
dict= json.loads(data)
df = json_normalize(dict['technologies']) 

# Example 2: Convert JSON to DataFrame Using read_json()
df2 = pd.read_json(jsonStr, orient ='index')

# Example 3: Use pandas.DataFrame.from_dict() to Convert JSON to DataFrame
dict= json.loads(data)
df2 = pd.DataFrame.from_dict(dict, orient="index")

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 DataFrame

2. 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.

pandas convert JSON DataFrame

3. 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

4. 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

Frequently Asked Questions

What is Pandas, and why would I want to convert JSON to a DataFrame with it?

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.

What is JSON data, and what does it look like?

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,
{ <br/> "name": "John",<br/> "age": 30, <br/> "city": "New York" <br/> }

How do I read JSON data into a Pandas DataFrame?

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

How can I convert JSON data stored in a Python dictionary to a DataFrame?

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

How do I export a Pandas DataFrame back to JSON?

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 !!

References

Tags: , ,

Naveen Nelamali

Naveen Nelamali (NNK) is a Data Engineer with 20+ years of experience in transforming data into actionable insights. Over the years, He has honed his expertise in designing, implementing, and maintaining data pipelines with frameworks like Apache Spark, PySpark, Pandas, R, Hive and Machine Learning. Naveen journey in the field of data engineering has been a continuous learning, innovation, and a strong commitment to data integrity. In this blog, he shares his experiences with the data as he come across. Follow Naveen @ LinkedIn and Medium

Leave a Reply