如何通过字符串拆分生成新行？Pandas DataFrame数据行拆分求助

Hey there! Let's work through solving this problem where we need to split the @-marked compound content in column B into individual rows, while mapping the corresponding A (ID), C (numerical value) and keeping D fixed as @DI. Here's a step-by-step breakdown:

First, let's confirm our starting data setup:

import pandas as pd

data = {'A':['000','001','002'], 
        'B':['Name0','Name1','Name2 @35 @DI @003 @Name3 @68 @DI'], 
        'C':[27,24,35], 
        'D':['@DI','@DI','@DI']}
df = pd.DataFrame(data)

Core Approach #

We'll process each row to:

1. Keep the original row (cleaned of extra @ content in column B)
2. Extract any hidden entries from column B, map their corresponding A, B, C values, and add them as new rows
3. Keep column D consistent as @DI for all rows

Step-by-Step Implementation #

1. Create a helper function to split rows

This function takes a single row, generates the cleaned original row, and pulls out any additional entries from the compound B column:

def split_compound_row(row):
    # Split column B content using '@' as the delimiter
    parts = row['B'].split('@')
    # The original name is the first segment before any '@'
    original_name = parts[0].strip()
    # Initialize with the original row
    processed_rows = [{'A': row['A'], 'B': original_name, 'C': row['C'], 'D': row['D']}]
    
    # Check if there are extra entries to split (needs at least 4 segments after splitting)
    if len(parts) > 3:
        # Extract the new ID, name, and value from the split segments
        new_id = parts[3].strip()
        new_name = parts[4].strip()
        new_value = int(parts[5].strip())
        # Add the new row to our list
        processed_rows.append({'A': new_id, 'B': new_name, 'C': new_value, 'D': row['D']})
    
    return processed_rows

2. Process all rows and build the result DataFrame

We'll iterate over every row in the original DataFrame, apply our helper function, and collect all processed rows:

# Collect all processed rows into a single list
all_rows = []
for _, row in df.iterrows():
    all_rows.extend(split_compound_row(row))

# Convert the list of rows into the final DataFrame
result_df = pd.DataFrame(all_rows)

3. Check the output

Printing result_df will give you exactly the expected output:

print(result_df)

Output:

A      B   C    D
0  000  Name0  27  @DI
1  001  Name1  24  @DI
2  002  Name2  35  @DI
3  003  Name3  68  @DI

Bonus: Handle Multiple Extra Entries #

If column B has more than one hidden entry (e.g., Name2 @35 @DI @003 @Name3 @68 @DI @004 @Name4 @70 @DI), modify the helper function to loop through all possible entries:

def split_compound_row(row):
    parts = row['B'].split('@')
    original_name = parts[0].strip()
    processed_rows = [{'A': row['A'], 'B': original_name, 'C': row['C'], 'D': row['D']}]
    
    # Loop through split segments starting at index 3, stepping by 3 to catch each new entry
    for i in range(3, len(parts)-1, 3):
        if i + 2 < len(parts):
            new_id = parts[i].strip()
            new_name = parts[i+1].strip()
            new_value = int(parts[i+2].strip())
            processed_rows.append({'A': new_id, 'B': new_name, 'C': new_value, 'D': row['D']})
    
    return processed_rows

This version will split as many hidden entries as exist in column B.

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