在R中合并列结构不同的CSV文件，保留首文件列顺序

Hey there! Handling large CSV datasets with mismatched columns is a common pain point, but we can solve this cleanly using Python's pandas library—perfect for your 10k-column use case. Here's a step-by-step breakdown tailored to your needs:

Step 1: Read the CSV Files #

First, we'll load both datasets. For large files, adding low_memory=False helps avoid dtype warnings that can pop up with many columns.

import pandas as pd

# Load datasets
df1 = pd.read_csv("dataset1.csv", low_memory=False)
df2 = pd.read_csv("dataset2.csv", low_memory=False)

Step 2: Identify Shared Columns (Preserving df1's Order) #

We need to keep only the columns that exist in both datasets, and maintain the column order from your first dataset (since you don't want to reorder those 10k columns!).

# Get columns that are present in both datasets, in df1's original order
shared_columns = [col for col in df1.columns if col in df2.columns]

Step 3: Filter and Align the Second Dataset #

Trim df2 to only the shared columns, and make sure their order matches df1 exactly—this ensures the merge doesn't mix up columns accidentally.

# Keep only shared columns in df2, reorder to match df1
df2_filtered = df2[shared_columns]

Step 4: Merge the Datasets #

Now concatenate the two DataFrames. This stacks the rows from df2 onto df1, keeping the column structure intact.

# Combine the datasets
merged_df = pd.concat([df1, df2_filtered], ignore_index=True)

Step 5: Save the Result #

Finally, export the merged data back to a CSV, skipping the index column since we don't need it.

merged_df.to_csv("merged_dataset.csv", index=False)

Example Walkthrough (Matching Your Sample Data) #

If your datasets look like this:

Dataset 1 (dataset1.csv):
Brand,Year,Model,Price
Ford,2010,Taurus,5K
Toyota,2015,Yaris,4K

Dataset 2 (dataset2.csv):
Brand,Year,Model,Color,Location,Price
Chevrolet,2013,Spark,Dark Gray,PHI,2K

Running the code above will produce a merged CSV with:
Brand,Year,Model,Price
Ford,2010,Taurus,5K
Toyota,2015,Yaris,4K
Chevrolet,2013,Spark,2K

Handling Extra Large Files (Memory Tips) #

If your 10k-column datasets are too big to fit in memory, you can process them in chunks:

• Read df1 in chunks to get the shared columns first
• Then read df2 in chunks, filter each chunk to shared columns, and append to the output file incrementally

Here's a quick snippet for chunked processing:

# Get shared columns first by reading a small chunk of df2
df2_sample = pd.read_csv("dataset2.csv", nrows=1)
shared_columns = [col for col in df1.columns if col in df2_sample.columns]

# Write df1 to output first
df1.to_csv("merged_dataset.csv", index=False)

# Process df2 in chunks and append
chunk_size = 10000
for chunk in pd.read_csv("dataset2.csv", chunksize=chunk_size, low_memory=False):
    chunk_filtered = chunk[shared_columns]
    chunk_filtered.to_csv("merged_dataset.csv", mode="a", header=False, index=False)

This way, you don't have to load the entire dataset into memory at once.

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