请求部署递归分类算法，生成含完整分类路径的Pandas DataFrame

Got it, let's walk through how to solve this recursive category path problem step by step. The goal is to take your existing DataFrame with parent-child category relationships and split the full hierarchical path into separate columns (one per category level).

Step 1: Set Up the Example DataFrame #

First, let's replicate your sample data so we can work with it directly:

import pandas as pd

# Sample data
data = [
    [1, "25€", "RED_WINE", "WINES", "Red wine from ...."],
    [2, "15€", "BLACK_WINE", "WINES", "Nice black wine"],
    [3, "10€", "WINES", "DRINKS", "Generic white wine..."],
    [4, 10, "WOMEN_TSHIRT", "WOMEN", "Summer women T-Shirt"],
    [5, 10, "WOMEN", "DRESSES", "Women winter dress"]
]

df = pd.DataFrame(data, columns=["ID", "PRICE", "CAT_NAME", "PARENT_CAT_NAME", "TITLE"])

Step 2: Clean Up Numeric Columns (Optional but Useful) #

First, let's convert the PRICE column to a numeric type by stripping the euro symbol:

df["PRICE"] = df["PRICE"].astype(str).str.replace("€", "").astype(float)

Step 3: Build Parent-Child Mapping #

We'll create a dictionary to quickly look up the parent of any category:

cat_parent_map = df.set_index("CAT_NAME")["PARENT_CAT_NAME"].to_dict()

Step 4: Function to Generate Full Category Path #

Instead of using recursion (which can hit stack limits for deep hierarchies), we'll use a loop to build the path from the lowest category up to the root. We'll also add a check for circular references to avoid infinite loops:

def get_full_category_path(cat_name, parent_map):
    path = []
    current_cat = cat_name
    visited_cats = set()  # Prevent circular loops
    
    while True:
        if current_cat in visited_cats:
            print(f"Warning: Circular reference found at category '{current_cat}'")
            break
        visited_cats.add(current_cat)
        
        # Insert at the start to keep root -> child order
        path.insert(0, current_cat)
        
        # Get parent category; if none, we've reached the root
        parent_cat = parent_map.get(current_cat)
        if parent_cat is None:
            break
        
        current_cat = parent_cat
    
    return path

Step 5: Apply Path Function to DataFrame #

Now we'll generate the full path for each row and store it in a new column:

df["CATEGORY_PATH"] = df["CAT_NAME"].apply(lambda x: get_full_category_path(x, cat_parent_map))

Step 6: Split Path into Separate Columns #

Next, we'll split the path list into individual columns for each category level. First, find the longest path to know how many columns we need:

# Get maximum number of category levels
max_levels = df["CATEGORY_PATH"].apply(len).max()

# Create column names for each level (e.g., Category_Level_1, Category_Level_2)
level_columns = [f"Category_Level_{i+1}" for i in range(max_levels)]

# Split the path list into columns
df[level_columns] = pd.DataFrame(df["CATEGORY_PATH"].tolist(), index=df.index)

Step 7: Finalize the DataFrame #

Drop the temporary CATEGORY_PATH column and reorder columns for readability:

final_df = df.drop("CATEGORY_PATH", axis=1)[
    ["ID", "PRICE"] + level_columns + ["TITLE"]
]

print(final_df)

Output #

Here's what the final DataFrame will look like:

ID  PRICE Category_Level_1 Category_Level_2 Category_Level_3                  TITLE
0   1   25.0            DRINKS             WINES          RED_WINE    Red wine from ....
1   2   15.0            DRINKS             WINES        BLACK_WINE      Nice black wine
2   3   10.0            DRINKS             WINES              NaN  Generic white wine...
3   4   10.0            DRESSES             WOMEN      WOMEN_TSHIRT  Summer women T-Shirt
4   5   10.0            DRESSES             WOMEN              NaN    Women winter dress

Key Notes #

• Handling Root Categories: If a parent category doesn't appear as a CAT_NAME in any row (like DRINKS in your sample), the function will stop at that parent and treat it as the root level.
• Circular References: The visited_cats set prevents infinite loops if your data has accidental circular parent-child relationships.
• Scalability: Using a loop instead of recursion makes this solution work for even very deep category hierarchies without hitting Python's recursion limit.

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