Pandas：将逗号分隔列按顺序分组拆分为两列并展开行

Hey there! Let's work through how to split your Postcodelist into the paired P1/P2 columns you need, keeping the Uid aligned correctly. Here's a step-by-step solution using pandas:

Step 1: Set up the sample DataFrame #

First, let's replicate your input data to test the solution:

import pandas as pd

df = pd.DataFrame({
    'Uid': [1, 2, 3, 4],
    'Postcodelist': [
        'NE11 7HS,NE5 8MN,NE1 7UJ,NE14 8YU',
        'LS6 8PJ',
        'M6 7JH,M14 1HF',
        'B17 8KA,LE5 7UZ,LE9 9GF'
    ]
})

Step 2: Define a function to split postcodes into pairs #

We'll create a helper function that takes a comma-separated postcode string, splits it into a list, and groups the entries into pairs (with None for single leftover entries):

def split_postcode_pairs(postcode_str):
    # Split string into individual postcodes and trim whitespace
    postcodes = [p.strip() for p in postcode_str.split(',')]
    pairs = []
    # Iterate through the list in steps of 2
    for i in range(0, len(postcodes), 2):
        p1 = postcodes[i]
        # Assign p2 if there's a next entry, else None
        p2 = postcodes[i+1] if (i+1) < len(postcodes) else None
        pairs.append((p1, p2))
    return pairs

Step 3: Apply the function and expand into rows #

We'll use apply to generate pairs for each row, then explode to turn each pair into its own row:

# Generate pairs for each Uid
df['Pairs'] = df['Postcodelist'].apply(split_postcode_pairs)
# Explode the list of pairs into separate rows
expanded_df = df.explode('Pairs').reset_index(drop=True)

Step 4: Split pairs into P1 and P2 columns #

Finally, we'll split the tuple of pairs into separate columns and clean up unnecessary columns:

# Convert the tuple column into P1 and P2
expanded_df[['P1', 'P2']] = pd.DataFrame(expanded_df['Pairs'].tolist(), index=expanded_df.index)
# Drop original columns we don't need anymore
final_df = expanded_df.drop(columns=['Postcodelist', 'Pairs'])

# Optional: Replace None with 'Null' string if needed
final_df['P2'] = final_df['P2'].fillna('Null')

print(final_df)

Expected Output #

Running this code will give you exactly the format you requested:

Uid         P1         P2
0    1  NE11 7HS  NE5 8MN
1    1   NE1 7UJ  NE14 8YU
2    2   LS6 8PJ       Null
3    3    M6 7JH  M14 1HF
4    4   B17 8KA  LE5 7UZ
5    4   LE9 9GF       Null

This approach handles all edge cases: single postcodes, even numbers of postcodes, and odd numbers of postcodes (with the last one paired with Null).

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