Pandas合并900个CSV触发InvalidIndexError问题求助

That error pops up because one or more of your CSV files has duplicate values in the DateTime column. When you use index_col=[0] during reading, pandas sets that column as the index—but if there are duplicates, the index isn't unique, and pd.concat can't handle reindexing non-unique indexes. Here's how to fix this step by step:

Step 1: Identify and Fix Duplicate DateTime Entries #

First, we'll adjust the code to check for duplicates in each CSV before setting the index. This ensures every DataFrame we merge has a unique DateTime index.

Modified Code #

import os
import pandas as pd

os.chdir('E:\\Business\\Economic Indicators')

dfs = []
for filename in os.listdir(os.getcwd()):
    if filename.endswith('csv'):
        # Read the CSV without setting DateTime as index yet
        df = pd.read_csv(filename, parse_dates=['DateTime'])
        
        # Check for duplicate DateTime values
        if df['DateTime'].duplicated().any():
            print(f"⚠️ Found duplicate DateTime entries in {filename} — cleaning up...")
            # Keep the first occurrence of each duplicate DateTime (use keep='last' if you prefer the latest)
            df = df.drop_duplicates(subset=['DateTime'], keep='first')
            
            # Optional: If you need to aggregate duplicate rows instead of dropping, use groupby
            # Example: Take the mean of numeric columns for duplicates
            # df = df.groupby('DateTime').agg({
            #     'Actual': 'mean',
            #     'Consensus': 'mean',
            #     'Previous': 'mean',
            #     'Revised': 'mean'
            # }).reset_index()
        
        # Now set DateTime as the index (it's guaranteed unique now)
        df = df.set_index('DateTime')
        dfs.append(df)

# Merge all DataFrames with outer join and sort descending
finaldf = pd.concat(dfs, axis=1, join='outer').sort_index(ascending=False)

# Optional: Double-check for any remaining duplicate indexes (just in case)
finaldf = finaldf.loc[~finaldf.index.duplicated(keep='first')]

print(finaldf.head())
finaldf.to_csv('finaldf.csv')

Why Your Original Code Failed #

When you used pd.read_csv(f, index_col=[0], parse_dates=[0]), if any CSV had duplicate DateTime values, that DataFrame's index became non-unique. Pandas doesn't allow reindexing operations (like those done during concat) on non-unique indexes, hence the InvalidIndexError. By handling duplicates before setting the index, we ensure every DataFrame has a valid, unique index for merging.

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