Pandas Convert JSON to DataFrame

  • Post author:
  • Post category:Pandas
  • Post last modified:October 1, 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.

Advertisements

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 –

  • 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 allows you to specify the structure of the JSON data (e.g., records, index, columns).
  • 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).

Related: You can convert Pandas DataFrame to JSON string.

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 DataFrame

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

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

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: , ,

Leave a Reply