如何用Pandas将DataFrame中特定字符串列拆分为对应字段列

Hey Laura, let's break down how to split that messy options column into clean, structured fields. Here's a step-by-step solution that handles edge cases like NaN and values with colons:

1. Understand the Pattern #

Your options column uses a consistent format: [Key:Value][Key:Value].... Each key-value pair is wrapped in square brackets, with keys and values separated by a colon.

2. Write a Parsing Function #

First, create a helper function to convert each string entry into a dictionary. This handles empty/NaN values gracefully:

import pandas as pd

def parse_options_entry(entry):
    # Handle NaN or empty strings
    if pd.isna(entry) or not entry.strip():
        return {}
    
    # Split the entry into individual key-value pairs
    # Strip outer brackets first, then split on "]["
    key_value_pairs = entry.strip('[]').split('][')
    
    # Build a dictionary from each pair
    options_dict = {}
    for pair in key_value_pairs:
        # Split only on the FIRST colon (in case values have colons)
        if ':' in pair:
            key, value = pair.split(':', 1)
            options_dict[key] = value
    
    return options_dict

3. Apply the Function and Expand to Columns #

Use apply() to run the function on every row in the options column, then convert the resulting dictionaries into a new DataFrame. Finally, merge this with your original data:

# Apply the parser to the options column
parsed_options = df['options'].apply(parse_options_entry).apply(pd.Series)

# Merge the parsed columns with the original DataFrame
final_df = pd.concat([df, parsed_options], axis=1)

4. Ensure Target Columns Exist #

If you specifically want only the four fields (Bean status, Type of Roast, Coffee Type, Roaster), use reindex to guarantee these columns are present (even if some entries don't have them):

target_fields = ['Bean status', 'Type of Roast', 'Coffee Type', 'Roaster']
final_df = final_df.reindex(columns=df.columns.tolist() + target_fields)

Example Output #

For your sample data, the final DataFrame will look like this (abbreviated):

This approach is flexible—it works even if some entries have missing keys, and handles values that might contain colons (since we only split on the first colon in each pair).

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