如何拆分格式不统一的DataFrame列并规范输出结构？

Absolutely, you can get that clean, numbered-column output directly from your original DataFrame—no messy string splits or hardcoding required! The mistake with your initial approach was treating the JSON array in the Users column as raw text instead of parsing it properly. Here's how to do it right:

Step 1: Parse the JSON String into Python Lists #

First, convert the string-formatted JSON arrays in the Users column into actual Python lists of dictionaries. We'll use ast.literal_eval (safe for valid JSON-like strings) for this:

import ast
import pandas as pd

# Your original data
raw_data = {
    "company": ["A", "B"],
    "Users": [
        '[{"Name":"Martin","Email":"name_1@email.com","EmpType":"Full"},{"Name":"Rick","Email":"name_2@email.com","Dept":"HR"}]',
        '[{"Name":"John","Email":"name_2@email.com","EmpType":"Full","Dept":"Sales" }]'
    ]
}
df = pd.DataFrame(raw_data)

# Parse JSON strings to Python lists
df["Users"] = df["Users"].apply(ast.literal_eval)

Step 2: Expand Users into Numbered Columns #

Next, we'll create a helper function to take each list of user dictionaries, normalize them into a structured format, and auto-generate column names with user indices (e.g., Name_1, Email_2). This avoids hardcoding because it dynamically pulls field names from the user dictionaries:

def expand_user_list(user_list):
    # Normalize the list of user dicts into a flat DataFrame
    user_df = pd.json_normalize(user_list)
    # Add a user index (starting at 1) to track each user's position
    user_df["user_num"] = user_df.index + 1
    # Reshape to wide format with numbered column names
    reshaped = user_df.melt(id_vars="user_num", var_name="field", value_name="value")
    reshaped["column_name"] = reshaped["field"] + "_" + reshaped["user_num"].astype(str)
    # Convert to a Series where index is the numbered column name
    return reshaped.set_index("column_name")["value"]

# Apply the function to each row and merge back with original DataFrame
expanded_users = df["Users"].apply(expand_user_list)
final_df = pd.concat([df.drop("Users", axis=1), expanded_users], axis=1)

Final Output #

Your resulting DataFrame will have clean, auto-numbered columns that match the fields present in each user's data (missing fields will show as NaN):

Why This Works Better Than String Splitting #

• Reliable: Parsing JSON properly handles edge cases like extra spaces, nested structures, or varying field order—something string splits can't do consistently.
• Dynamic: Column names are generated automatically based on the actual fields in your user data, so you don't have to update code if new fields are added later.
• Clean: No messy intermediate columns or manual renaming required.

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