Python Pandas合并DataFrame时列丢失及重复列合并问题求助

Let's break down how to fix both your issues: aligning columns across CSVs (filling missing ones with 'N/A') and merging duplicate columns into a single column with valid values.

Step 1: Merge Duplicate Columns in Each CSV #

First, we need to handle duplicate columns within individual CSV files (like multiple B columns that pandas renames to B.1, B.2). We'll create a helper function to merge these duplicates into one column, taking the first non-null value from each row across the duplicated columns.

Step 2: Standardize Columns Across All CSVs #

Next, we'll collect all unique column names from all processed CSVs (after merging duplicates). For each CSV, we'll add any missing columns filled with 'N/A' and reorder columns to match the full set, ensuring no columns are lost during concatenation.

Modified Code #

Here's your updated code with these fixes:

import os
import glob
import pandas as pd
from sqlalchemy import create_engine

def merge_duplicate_columns(df):
    """Merge columns with duplicate base names (e.g., B, B.1 → B) by taking first non-null value per row"""
    column_groups = {}
    for col in df.columns:
        # Split column name to get base name (ignore .1, .2 suffixes)
        base_col = col.split('.')[0]
        if base_col not in column_groups:
            column_groups[base_col] = []
        column_groups[base_col].append(col)
    
    merged_df = pd.DataFrame()
    for base_col, cols in column_groups.items():
        if len(cols) == 1:
            merged_df[base_col] = df[cols[0]]
        else:
            # Use backfill to take first non-null value across duplicated columns
            merged_df[base_col] = df[cols].bfill(axis=1).iloc[:, 0]
    return merged_df

def process_file():
    # Assuming cg_list(), sqlcol(), ServerName, Database, Driver are defined elsewhere
    reqd_counters = cg_list()
    parsed_families = os.listdir(Input_dir)
    
    for name in parsed_families:
        if name in reqd_counters:
            folder_path = os.path.join(Input_dir, name)
            all_file_path = glob.glob(os.path.join(folder_path, "*.csv"))
            
            if not all_file_path:
                print(f"No CSV files found in {folder_path}")
                continue
            
            # Step 1: Collect all unique columns after merging duplicates in each CSV
            all_merged_columns = set()
            for file_path in all_file_path:
                df_temp = pd.read_csv(file_path)
                df_temp = merge_duplicate_columns(df_temp)
                all_merged_columns.update(df_temp.columns)
            all_merged_columns = list(all_merged_columns)
            
            # Step 2: Process each CSV file
            dfs = []
            for file_path in all_file_path:
                df = pd.read_csv(file_path)
                
                # Merge duplicate columns in current file
                df = merge_duplicate_columns(df)
                
                # Add missing columns filled with 'N/A'
                for col in all_merged_columns:
                    if col not in df.columns:
                        df[col] = 'N/A'
                
                # Reorder columns to match the full set
                df = df[all_merged_columns]
                
                # Clean up dateTime column (assign back to modify dataframe)
                if 'dateTime' in df.columns:
                    df['dateTime'] = df['dateTime'].str.replace('+04:00', '', regex=False)
                
                dfs.append(df)
            
            # Concatenate all processed dataframes
            df = pd.concat(dfs, ignore_index=True)
            
            # Save to test CSV and load to SQL Server
            df.to_csv(os.path.join(Test_df_csv, name), sep=',', encoding='utf-8', index=False)
            outputdict = sqlcol(df)
            df.to_sql(name, db, if_exists="append", index=False, dtype=outputdict)

if __name__ == "__main__":
    # Initialize database connection
    db = create_engine('mssql+pyodbc://' + ServerName + '/' + Database + "?" + Driver)
    Input_dir = "your_input_directory_path"  # Replace with your actual path
    Test_df_csv = "your_test_output_directory"  # Replace with your actual path
    process_file()

Key Fixes Explained: #

• Duplicate Column Merging: The merge_duplicate_columns function groups columns by their base name (removing .1/.2 suffixes) and combines them into a single column using bfill to pick the first valid value per row.
• Column Standardization: We collect all unique columns from all CSVs, then ensure each CSV has every column (filling missing ones with 'N/A') and reorders columns to match the full set. This prevents column loss and inconsistent ordering during concatenation.
• dateTime Fix: We assign the modified dateTime column back to the dataframe (your original code didn't save the change).

Notes: #

• Make sure cg_list(), sqlcol(), ServerName, Database, Driver, Input_dir, and Test_df_csv are properly defined in your code.
• If you want to replace all NaN values (not just missing columns) with 'N/A', add df = df.fillna('N/A') after concatenation.

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