基于Pandas DataFrame构建含products列表的嵌套JSON/字典

Got it, let's tweak that recursive function to build the exact nested structure you need—with cat1→cat2→cat3 as nested keys, and a products list holding the name/color/amount details at the deepest level.

First, let's break down the adjustment: the original function keeps grouping columns until only one remains, but we need to stop at the last category column (cat3) and convert the remaining product columns into a list of dictionaries under a products key.

Here's the modified working code:

import pandas as pd

df = pd.DataFrame({
    'cat1': ['A', 'A', 'A', 'B', 'B', 'C', 'C', 'C'],
    'cat2': ['BB', 'BB', 'BC', 'BB', 'BB', 'BB', 'BC', 'BC'],
    'cat3': ['CC', 'CC', 'CD', 'CD', 'CD', 'CC', 'CD', 'CE'],
    'name': ['P1', 'P2', 'P3', 'P1', 'P4', 'P1', 'P3','P6'],
    'color': ['red', 'blue', 'green', 'green', 'yellow', 'red', 'blue', 'blue'],
    'amount': [132, 51, 12, 421, 55, 11, 123, 312]
})

def recur_dictify(frame):
    # Check if we've reached the product columns (name, color, amount)
    if set(frame.columns) == {'name', 'color', 'amount'}:
        # Convert each row to a dictionary and wrap in a 'products' list
        return {
            'products': frame.apply(lambda row: row.to_dict(), axis=1).tolist()
        }
    # If category columns remain, group by the first one and recurse
    grouped = frame.groupby(frame.columns[0])
    d = {k: recur_dictify(g.iloc[:, 1:]) for k, g in grouped}
    return d

# Generate the nested structure
result = recur_dictify(df)
print(result)

Key Changes Explained #

• Termination Check: We added a condition to detect when we're working with the three product columns. At this point, we convert every row in the group to a dictionary, collect them into a list, and attach it to the products key.
• Unchanged Recursion Flow: For all other cases (when category columns are still present), the function behaves just like the original—grouping by the first column and recursing on the remaining columns for each group.

Example Output Snippet #

The resulting structure will match your expected nested format. For the A category, it looks like this:

{
  'A': {
    'BB': {
      'CC': {
        'products': [
          {'name': 'P1', 'color': 'red', 'amount': 132},
          {'name': 'P2', 'color': 'blue', 'amount': 51}
        ]
      }
    },
    'BC': {
      'CD': {
        'products': [{'name': 'P3', 'color': 'green', 'amount': 12}]
      }
    }
  },
  # ... rest of the nested structure follows
}

If you need to convert this directly to JSON, just use the json module:

import json
json_output = json.dumps(result, indent=2)
print(json_output)

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