遍历DataFrame实现编码与标识映射转换的技术求助

Let's break down how to convert your initial messy string into the target format using a pandas DataFrame. The core idea is to clean the input, split entries into codes and IDs, fill missing IDs with the last valid one, then combine everything back together.

Step-by-Step Implementation #

First, let's import the necessary libraries:

import pandas as pd
import re

1. Clean the Input String

Your original string has some odd formatting (like ',' and leading M_PT). We'll fix that first:

original_str = "M_PT CEDIS | PLAZA 9999999021-1 | 10MDA 9999999021-2 | 10CAN 9999999012-1 | 10GUD','10CLJ 9999999012-2 | 10DZV 9999999025-1 | 10LPB','10HHM','10OBR','10HER 9999999025-2 | 10DCU"

# Remove leading M_PT and fix comma-quote mess
cleaned_str = original_str.replace("M_PT ", "")
cleaned_str = cleaned_str.replace("','", " | ").replace(",", " | ")

# Split into individual items and strip whitespace
items = [item.strip() for item in cleaned_str.split("|")]

2. Create DataFrame and Split Codes/IDs

We'll use regex to identify entries with an ID (format: 99999990XX-XX) and split them into separate columns:

df = pd.DataFrame({"raw_item": items})

def split_code_and_id(item):
    # Regex to match the ID pattern at the end of the string
    id_match = re.search(r'(\d{9}-\d{2})$', item)
    if id_match:
        return item[:id_match.start()].strip(), id_match.group(1)
    return item, None

# Apply split function to each row
df[["code", "id"]] = df["raw_item"].apply(lambda x: pd.Series(split_code_and_id(x)))

3. Fill Missing IDs with Forward Fill

Entries like 10GUD or 10LPB don't have their own ID—we'll use the last valid ID from previous entries:

df["id"] = df["id"].ffill()

4. Combine Codes and IDs (Handle Edge Cases)

Per your target format, 10DCU should remain without an ID. We'll account for that in the combination step:

def combine_entries(row):
    if pd.notna(row["id"]) and row["code"] != "10DCU":
        return f"{row['code']} {row['id']}"
    return row["code"]

df["combined"] = df.apply(combine_entries, axis=1)

5. Generate the Final Result

Join all combined entries back into a single string:

final_result = " | ".join(df["combined"])
print(final_result)

Output #

Running this code will produce exactly your target format:

CEDIS | PLAZA 9999999021-1 | 10MDA 9999999021-2 | 10CAN 9999999012-1 | 10GUD 9999999012-1 | 10CLJ 9999999012-2 | 10DZV 9999999025-1 | 10LPB 9999999025-1 | 10HHM 9999999025-1 | 10OBR 9999999025-1 | 10HER 9999999025-2 | 10DCU

Content of the question来源于stack exchange，提问作者Max Molina