如何用Pandas DataFrame基于vc_no与Type匹配Excel列值并插入数据

Got it, let's tackle this Excel data matching and filling task step by step. Here's a practical approach using Python's pandas library—super efficient for this kind of tabular data operation, plus an alternative using built-in Excel functions if you prefer no code.

First, let's clarify the rules to avoid confusion:

• Use vc_no + Type (Primary/Secondary) as the unique matching key
• Map source data to target columns based on Type:
  • When Type is Secondary, fill values into vc_no1 and c_result1
  • When Type is Primary, fill values into vc_no2 and c_result2

This is ideal if you have large datasets or need to repeat the task regularly.

1. Install Dependencies #

If you haven't already, install the required libraries:

pip install pandas openpyxl

openpyxl handles reading/writing modern .xlsx Excel files.

2. Full Code Implementation #

import pandas as pd

# 1. Load source and target Excel files
source_df = pd.read_excel("your_source_file.xlsx")
target_df = pd.read_excel("your_target_file.xlsx")

# 2. Split source data by Type and rename columns to match target
# Replace "source_result_column" with the actual column name in your source file
primary_data = source_df[source_df["Type"] == "Primary"].rename(
    columns={"vc_no": "vc_no2", "source_result_column": "c_result2"}
)
secondary_data = source_df[source_df["Type"] == "Secondary"].rename(
    columns={"vc_no": "vc_no1", "source_result_column": "c_result1"}
)

# 3. Merge secondary data into target table
target_df = pd.merge(
    target_df,
    secondary_data[["vc_no", "vc_no1", "c_result1"]],
    on="vc_no",
    how="left"  # Keep all rows from target, fill matched values
)

# 4. Merge primary data into target table
target_df = pd.merge(
    target_df,
    primary_data[["vc_no", "vc_no2", "c_result2"]],
    on="vc_no",
    how="left"
)

# 5. Preserve existing non-NULL values in target (if needed)
# If your target already has some filled values, use combine_first to keep them
target_df["c_result1"] = target_df["c_result1"].combine_first(target_df.pop("c_result1_x"))
target_df["c_result2"] = target_df["c_result2"].combine_first(target_df.pop("c_result2_x"))

# 6. Save the filled target file
target_df.to_excel("filled_target_file.xlsx", index=False)

Key Notes #

• Replace your_source_file.xlsx, your_target_file.xlsx, and source_result_column with your actual file names and column labels.
• If vc_no has different names in source and target, use left_on and right_on in the merge function, e.g.:

  pd.merge(target_df, secondary_data, left_on="target_vc_no", right_on="source_vc_no", how="left")
  

• The how="left" parameter ensures no rows are lost from the target file—unmatched rows will stay as NULL/NaN.

If you prefer using Excel directly, use XLOOKUP (or INDEX+MATCH for older Excel versions):

For c_result1 (Secondary Type) #

In the first empty cell of c_result1, enter:

=XLOOKUP(A2&"Secondary", SourceSheet!$A:$A&SourceSheet!$B:$B, SourceSheet!$C:$C, "NULL")

• A2: The vc_no cell in your target sheet
• SourceSheet!$A:$A: vc_no column in your source sheet
• SourceSheet!$B:$B: Type column in your source sheet
• SourceSheet!$C:$C: The column with values to fill into c_result1

For c_result2 (Primary Type) #

Use this formula:

=XLOOKUP(A2&"Primary", SourceSheet!$A:$A&SourceSheet!$B:$B, SourceSheet!$C:$C, "NULL")

For Older Excel Versions (No XLOOKUP) #

Use the INDEX+MATCH combination:

=INDEX(SourceSheet!$C:$C, MATCH(A2&"Secondary", SourceSheet!$A:$A&SourceSheet!$B:$B, 0), 1)

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