如何使用Pandas将指定结构的DataFrame转换为JSON格式

Got it, let's break down how to convert your specific DataFrame into JSON using Pandas—we'll cover both a basic flat format and a nested grouped structure since you have group and subgroup columns that might make sense to organize hierarchically.

First, let's make sure we're starting with the exact DataFrame you provided:

import pandas as pd

# Initialize the DataFrame as defined
df = pd.DataFrame(
    [
        ['2021-12-14 12:00:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:15:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:15:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:30:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:45:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 13:00:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:30:00','subgroup_3','group_2','Subgroup 3'],
        ['2021-12-14 12:45:00','subgroup_3','group_2','Subgroup 3'],
        ['2021-12-14 13:00:00','subgroup_3','group_2','Subgroup 3'],
    ],
    columns=['timestamp','subgroup','group','name']
)

Option 1: Flat JSON (List of Objects) #

This is the most common format, where each row in the DataFrame becomes a JSON object in a list. Use the orient='records' parameter:

# Convert to flat JSON (each row = JSON object)
flat_json = df.to_json(orient='records', indent=2)
print(flat_json)

Output: #

[
  {
    "timestamp": "2021-12-14 12:00:00",
    "subgroup": "subgroup_1",
    "group": "group_1",
    "name": "Subgroup 1"
  },
  {
    "timestamp": "2021-12-14 12:15:00",
    "subgroup": "subgroup_1",
    "group": "group_1",
    "name": "Subgroup 1"
  },
  // ... remaining rows
]

If you want timestamps in ISO 8601 format (e.g., "2021-12-14T12:00:00.000Z"), add the date_format='iso' parameter:

flat_json_iso = df.to_json(orient='records', indent=2, date_format='iso')

Option 2: Nested JSON (Grouped by group and subgroup) #

If you want to organize the data hierarchically by group → subgroup, we can use Pandas grouping before converting to JSON:

# Create nested JSON grouped by group > subgroup
nested_json = (
    df.groupby(['group', 'subgroup'])
      .apply(lambda x: x[['timestamp', 'name']].to_dict('records'))
      .reset_index()
      .groupby('group')
      .apply(lambda x: x.set_index('subgroup')[0].to_dict())
      .to_json(indent=2)
)
print(nested_json)

Output: #

{
  "group_1": {
    "subgroup_1": [
      {
        "timestamp": "2021-12-14 12:00:00",
        "name": "Subgroup 1"
      },
      {
        "timestamp": "2021-12-14 12:15:00",
        "name": "Subgroup 1"
      },
      // ... remaining subgroup_1 entries
    ]
  },
  "group_2": {
    "subgroup_3": [
      {
        "timestamp": "2021-12-14 12:30:00",
        "name": "Subgroup 3"
      },
      // ... remaining subgroup_3 entries
    ]
  }
}

How this works: #

1. First, we group the DataFrame by group and subgroup, converting each group's timestamp and name columns into a list of objects.
2. Then we re-group by group to nest the subgroup data under each parent group key.

Other Useful orient Options #

Pandas' to_json has several other orientation parameters if you need different structures:

• orient='split': Separates data into columns, index, and data arrays.
• orient='index': Uses row indices as top-level keys.
• orient='columns': Uses column names as top-level keys (default).

Feel free to tweak the grouping logic or orient parameter to match your exact JSON structure requirements!

内容的提问来源于stack exchange，提问作者spectrum