如何按Test1.csv的ID列重排Test2.csv行？附Python代码报错排查

Let's work through fixing your code step by step—first addressing the error you're seeing, then ensuring we correctly reorder Test2.csv to match the ID sequence from Test1.csv.

Why You're Getting That Error #

The crash happens at the line item = "S_" + item for two key reasons:

• item here is a pandas Series (the Number column from Test2.csv), which holds numeric float values. You can't directly concatenate a string with a numeric Series like this—pandas doesn't know how to handle that type mismatch.
• Even if the data types lined up, modifying item inside that loop doesn't change the original df2 dataframe. You're working with a copy of the column, not the column itself.

On top of that, your code has a few other issues:

• A typo: input_path1 points to "Test.csv" but your reference file is named Test1.csv.
• df2 = df1.reindex_like(df2) is doing the wrong thing—it would overwrite df2 with values from df1, which isn't what you want.
• f.write() is empty, so you wouldn't save any data to the output file even if the rest worked.

Corrected Working Code #

Here's the fixed code that delivers exactly the output you want. I've included the "S_" prefix logic as a commented line in case you actually need it (your target output doesn't show it, so I left it optional):

#!/usr/bin/python
# -*- coding: utf-8 -*-
import pandas as pd

# Fix the file path typo to match your reference file
input_path1 = "Test1.csv"
input_path2 = "Test2.csv"
output_path = "output.csv"

# Read both CSV files
df1 = pd.read_csv(input_path1, encoding="utf-8")
df2 = pd.read_csv(input_path2, encoding="utf-8")

# Optional: Uncomment if you need to add "S_" prefix to the Number column
# (We convert to string first to avoid type errors)
# df2['Number'] = 'S_' + df2['Number'].astype(str)

# Reorder Test2 to match Test1's ID sequence
# 1. Set ID as the index for easy reordering
# 2. Reindex using Test1's ID list to get the right order
# 3. Reset index to turn ID back into a regular column
df_sorted = df2.set_index('ID').reindex(df1['ID']).reset_index()

# Save the sorted data to output.csv (no extra index column)
df_sorted.to_csv(output_path, index=False, encoding="utf-8")

How This Works #

1. Fix the file path: Corrects the typo so we're reading the right reference file (Test1.csv).
2. Reordering logic:
   • set_index('ID') makes the ID column the index of df2, which lets us use pandas' reindexing feature.
   • reindex(df1['ID']) rearranges df2's rows to exactly match the order of IDs in df1.
   • reset_index() converts the ID index back to a regular column, matching the clean format of your target output.
3. Saving the result: to_csv(index=False) ensures we don't write pandas' internal index to the CSV, keeping your output identical to the example you shared.

When you run this with your sample data, you'll get exactly the output.csv you specified, with rows ordered AA → BB → CC → DD → EE.

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