Pandas Dataframe To Nested Json in Python
Below are some of the ways in which we can convert Pandas DataFrames into Nested JSON in Python:
Use to_json() method
The most straightforward approach is to use the `to_json` method of pandas, specifying the orientation as ‘records’.
Python3
import pandas as pd # Create a sample DataFrame data = { 'Name' : [ 'Alice' , 'Bob' , 'Charlie' ], 'Age' : [ 25 , 30 , 35 ], 'City' : [ 'New York' , 'San Francisco' , 'Los Angeles' ]} df = pd.DataFrame(data) # Convert DataFrame to nested JSON json_data = df.to_json(orient = 'records' ) print (json_data) |
Output:
[{'Name': 'Alice', 'Age': 25, 'City': 'New York'}, {'Name': 'Bob', 'Age': 30, 'City': 'San Francisco'}, {'Name': 'Charlie', 'Age': 35, 'City': 'Los Angeles'}]
Grouped Data to Json Data
To convert a pandas DataFrame grouped by a column into nested JSON, you can use pandas and Python’s groupby
function.
Python3
# Group DataFrame by 'City' column and convert to nested JSON grouped_data = df.groupby( 'City' ). apply ( lambda x: x[[ 'Name' , 'Age' ]].to_json(orient = 'records' )) # Print the grouped data print (grouped_data) |
Output:
City
Los Angeles {"Name":{"2":"Charlie"},"Age":{"2":35}}
New York {"Name":{"0":"Alice"},"Age":{"0":25}}
San Francisco {"Name":{"1":"Bob"},"Age":{"1":30}}
dtype: object
Using a Custom Function
The code below will create a DataFrame with columns ‘Name’ and ‘Age’, define a custom function to create a nested structure for each row, apply this function to each row of the DataFrame, convert the resulting list of dictionaries into JSON format, and finally print the JSON output with indentation for better readability.
Python3
import json # Define a custom function to create a nested structure def custom_nested_structure(row): return { 'Person' : { 'Name' : row[ 'Name' ], 'Age' : row[ 'Age' ]}} # Apply the custom function to each row of the DataFrame json_data_custom = df. apply (custom_nested_structure, axis = 1 ).tolist() # Convert list of dictionaries to JSON json_output = json.dumps(json_data_custom, indent = 4 ) print (json_output) |
Output:
[
{
"Person": {
"Name": "Alice",
"Age": 25
}
},
{
"Person": {
"Name": "Bob",
"Age": 30
}
},
{
"Person": {
"Name": "Charlie",
"Age": 35
}
}
]
Python Pandas Dataframe To Nested Json
When working with data in Python,Pandas is a popular library for handling tabular data efficiently. Converting a Pandas DataFrame to a nested JSON structure can be necessary for various reasons, such as preparing data for API responses or interacting with nested JSON-based data structures. In this article, we will explore four approaches to achieving this using Pandas.