使用Pandas根据行中Primary/Secondary值将列数据插入目标列

Got it, let's work through this problem step by step. The core task is to take values from the Data column and assign them to either a Primary or Secondary column depending on the tag in the Source column. Here's how to do it cleanly with Pandas:

Step 1: Import Pandas and Load Your Excel File #

First, make sure you have Pandas and openpyxl installed (openpyxl handles Excel file reading/writing). Then load your source data:

import pandas as pd

# Load the source Excel file
df = pd.read_excel("source.xlsx")

Step 2: Create Target Columns and Populate Data #

We'll add two new columns (Primary and Secondary) and use conditional logic to fill them with values from Data where the Source matches. For large datasets, using numpy.where is more efficient than apply:

import numpy as np

# Fill Primary column with Data values where Source is "Primary"
df["Primary"] = np.where(df["Source"] == "Primary", df["Data"], pd.NA)
# Fill Secondary column with Data values where Source is "Secondary"
df["Secondary"] = np.where(df["Source"] == "Secondary", df["Data"], pd.NA)

If you prefer a more readable (though slightly slower for big data) approach, you can use apply:

df["Primary"] = df.apply(lambda row: row["Data"] if row["Source"] == "Primary" else pd.NA, axis=1)
df["Secondary"] = df.apply(lambda row: row["Data"] if row["Source"] == "Secondary" else pd.NA, axis=1)

Step 3: (Optional) Clean Up Unneeded Columns #

If you don't need the original Source and Dummy columns anymore, drop them to tidy up the output:

df_cleaned = df.drop(columns=["Source", "Dummy"])

Step 4: Save the Result to Excel #

Finally, export the processed data to a new Excel file:

df_cleaned.to_excel("output.xlsx", index=False)

Example Output #

Using your sample data, the resulting Excel file will look like this:

This keeps each original row intact, with the Data value placed in the correct target column and empty values (marked as <NA>) in the non-matching column.

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