合并不同列结构的CSV文件：Pandas实现及列序调整需求

Hey there! Let's resolve that column ordering mess you're seeing when combining your sensor CSV files. The core issue here is that when you concatenate DataFrames with different column sets, Pandas doesn't preserve a custom order automatically—we need to explicitly define it after merging. Here's a tailored solution:

Step-by-Step Solution Code #

import pandas as pd

# Initialize list to store individual file DataFrames
li = []
# Replace with your actual file paths
filepaths = ["Home_HeatSensor_AA.CSV", "Office_HeatSensor_BB.CSV", "Ship_ElevationSensor_XXYY.CSV"]

for filename in filepaths:
    # Read each CSV with explicit date parsing for consistency
    df = pd.read_csv(
        filename,
        index_col=None,
        header=0,
        parse_dates=["Time"],  # Explicitly target the Time column for datetime parsing
        infer_datetime_format=True
    )
    li.append(df)

# Combine all DataFrames - columns will align by name, missing values become NaN
combined_df = pd.concat(li, axis=0, ignore_index=True)

# Define your exact desired column sequence
desired_columns = [
    "Time",          # First column as requested
    "AA", "AB", "BB",# Heat sensor columns
    "XX", "XY", "YY",# Elevation sensor columns
    "Site", "Type"   # Last two columns as requested
]

# Reorder the combined DataFrame to match your target structure
final_df = combined_df[desired_columns]

# Save the final merged CSV (remove index=False if you want a row index)
final_df.to_csv("main_combined.csv", index=False)

Key Improvements & Explanations #

• Explicit Datetime Parsing: Instead of letting Pandas guess which columns to parse, we explicitly specify the Time column. This ensures consistent datetime handling across all your sensor files.
• Smart Concatenation: pd.concat() automatically matches columns by name, so rows from the home/office sensors will have NaN for XX/XY/YY, and ship sensor rows will have NaN for AA/AB/BB—exactly what you need for missing column values.
• Forced Column Order: By defining desired_columns and reindexing the combined DataFrame with it, we guarantee Time stays first, your sensor columns follow in the order you want, and Site/Type land at the end every time.

Optional: Fill Missing Values #

If you want to replace NaN values with a default (like 0 for sensor readings), add this line before saving:

# Replace NaNs with 0 - adjust the value to fit your use case
final_df.fillna(0, inplace=True)

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