如何动态清洗Pandas列并将分类转为带对应值的新列？

Hey there! I get why hardcoding categories feels like a dead end—let's build a flexible, dynamic solution that adapts to whatever new categories pop up in your data. Here's how to do it step by step:

1. First, Let's Simulate Your Data #

Since you didn't share the exact raw data, I'll create a sample that mirrors your description (with mixed categories and ranks):

import pandas as pd
import numpy as np

# Sample raw data matching your structure
raw_data = pd.DataFrame({
    "content": [
        "#1 Beauty & Personal Care #3 Men's Foil Shavers",
        "#2 Beauty & Personal Care #1 Paid in Kindle Store",
        "#5 Men's Foil Shavers",
        "#4 Beauty & Personal Care #2 Paid in Kindle Store #6 Men's Foil Shavers",
        "#1 Paid in Kindle Store"
    ]
})

2. Use Regular Expressions to Extract Ranks & Categories #

The key here is to use a regex pattern that reliably pulls out every #[number] [category] pair from each row. We'll then convert these pairs into a structured format that pandas can turn into tidy columns.

import re

# Regex pattern to match "#[rank] [category]" pairs
# Explanation:
# - #(\d+): Captures the numeric rank after #
# - (.*?): Captures the category name (non-greedy, stops at next # or end of line)
# - (?=#|$): Lookahead to stop at the next # or end of string
pattern = r"#(\d+) (.*?)(?=#|$)"

# Function to process each row into a {category: rank} dictionary
def extract_rank_category(row):
    matches = re.findall(pattern, row["content"])
    return {category: int(rank) for rank, category in matches}

# Apply the function to each row and merge the results into the original DataFrame
tidy_df = raw_data.join(raw_data.apply(extract_rank_category, axis=1).apply(pd.Series))

# Replace empty values with NaN (pandas does this by default, but we can make it explicit)
tidy_df = tidy_df.replace({pd.NA: np.nan})

3. What You'll Get #

Running the code above will give you a tidy DataFrame where each category becomes a column, with the corresponding rank as values, and NaN where a row doesn't have that category:

Why This Works Dynamically #

• No hardcoding needed: The regex automatically detects all unique categories across your entire dataset. If a new category like Paid in Kindle Store shows up in some rows, it will get its own column without you changing any code.
• Handles mixed row structures: Rows with 1, 2, or more category-rank pairs are processed consistently.

If your raw data has edge cases (like categories containing #—though your example doesn't), we can tweak the regex, but this should work perfectly for the structure you described.

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