Convert Pandas Dataframe to nested JSON

Convert Pandas Dataframe to nested JSON

You can convert a Pandas DataFrame to nested JSON using the to_dict function along with some additional processing. Here's how you can do it:

Let's assume you have a Pandas DataFrame named df like this:

import pandas as pd data = { "id": [1, 2, 3], "name": ["Alice", "Bob", "Charlie"], "age": [25, 30, 22] } df = pd.DataFrame(data)

Now, let's convert this DataFrame into nested JSON:

def row_to_dict(row): d = {} for col, value in row.iteritems(): if pd.api.types.is_numeric_dtype(df[col]): d[col] = value.item() if pd.notna(value) else None elif pd.api.types.is_bool_dtype(df[col]): d[col] = bool(value) else: d[col] = value if pd.notna(value) else None return d nested_data = [] for _, row in df.iterrows(): nested_data.append(row_to_dict(row)) nested_json = {"data": nested_data} import json nested_json_str = json.dumps(nested_json, indent=2) print(nested_json_str)

In this example, the row_to_dict function iterates through each row of the DataFrame and converts it into a dictionary. It handles different data types such as numeric, boolean, and other types. The resulting dictionaries are stored in a list (nested_data), and then a wrapping dictionary is created with a key like "data".

The json.dumps function is used to convert the final nested dictionary into a nicely formatted JSON string.

Please note that this example covers a basic case. If your DataFrame has more complex nested structures or requires specific handling of data types, you might need to adjust the conversion logic accordingly.

Examples

  1. Convert Pandas DataFrame to nested JSON using to_dict() and json.dumps():

    • Description: Convert a Pandas DataFrame to a nested JSON structure using the to_dict() method to convert the DataFrame to a dictionary, then use json.dumps() to serialize the dictionary to JSON format.
    • Code:
      import pandas as pd import json # Convert DataFrame to nested JSON nested_json = df.to_dict(orient='records') nested_json_str = json.dumps(nested_json)

  2. Convert Pandas DataFrame to nested JSON with hierarchical index:

    • Description: Convert a Pandas DataFrame with a hierarchical index to a nested JSON structure, where each level of the index corresponds to a nested level in the JSON.
    • Code:
      import pandas as pd # Convert DataFrame with hierarchical index to nested JSON nested_json = df.to_json(orient='split')

  3. Convert Pandas DataFrame to nested JSON with specific columns:

    • Description: Convert specific columns of a Pandas DataFrame to a nested JSON structure while excluding other columns.
    • Code:
      import pandas as pd # Convert specific columns to nested JSON nested_json = df[['col1', 'col2']].to_json(orient='records')

  4. Convert Pandas DataFrame to nested JSON with custom formatting:

    • Description: Customize the formatting of the nested JSON output, such as indentation and sorting, while converting a Pandas DataFrame.
    • Code:
      import pandas as pd # Convert DataFrame to nested JSON with custom formatting nested_json = df.to_json(orient='records', indent=4, sort_keys=True)

  5. Convert Pandas DataFrame to nested JSON with date formatting:

    • Description: Format date columns in a specific format while converting a Pandas DataFrame to a nested JSON structure.
    • Code:
      import pandas as pd # Convert DataFrame to nested JSON with date formatting nested_json = df.to_json(orient='records', date_format='iso')

  6. Convert Pandas DataFrame to nested JSON with column orientation:

    • Description: Convert a Pandas DataFrame to a nested JSON structure with columns as keys and corresponding values as nested JSON objects.
    • Code:
      import pandas as pd # Convert DataFrame to nested JSON with column orientation nested_json = df.to_json(orient='columns')

  7. Convert Pandas DataFrame to nested JSON with index orientation:

    • Description: Convert a Pandas DataFrame to a nested JSON structure with index values as keys and corresponding row values as nested JSON objects.
    • Code:
      import pandas as pd # Convert DataFrame to nested JSON with index orientation nested_json = df.to_json(orient='index')

  8. Convert Pandas DataFrame to nested JSON with nested records:

    • Description: Convert a Pandas DataFrame to a nested JSON structure where each row becomes a nested JSON object.
    • Code:
      import pandas as pd # Convert DataFrame to nested JSON with nested records nested_json = df.to_json(orient='records')

  9. Convert Pandas DataFrame to nested JSON with split orientation:

    • Description: Convert a Pandas DataFrame to a nested JSON structure with separate lists for index, columns, and data.
    • Code:
      import pandas as pd # Convert DataFrame to nested JSON with split orientation nested_json = df.to_json(orient='split')

  10. Convert Pandas DataFrame to nested JSON with values as lists:

    • Description: Convert a Pandas DataFrame to a nested JSON structure where each column becomes a list of values in the JSON.
    • Code:
      import pandas as pd # Convert DataFrame to nested JSON with values as lists nested_json = df.to_json(orient='values')

More Tags

email-attachments outputstream nodemon mongo-java mysql-error-1064 tk-toolkit turtle-graphics task google-cloud-logging uilabel

More Python Questions

More Auto Calculators

More Animal pregnancy Calculators

More Genetics Calculators

More Math Calculators