Python新手求助：批量修复Excel并合并DataFrame及报错解决

Hey there! Let's work through your problems one by one. That FileNotFoundError is a common pitfall when dealing with file directories, so we'll start there, then tackle merging your restored files into a Pandas DataFrame.

First: Fixing the FileNotFoundError #

The error happens because os.listdir(file_dir) only returns the filenames (like 20181124_file_01.csv), not the full path to the file. When you try to open filename directly, Python looks for it in your current working directory—not the corrupted_files folder you specified.

Here's the corrected code for restoring your files, with added safeguards to keep the loop running even if one file fails:

import os
import io
import xlwt
from xlwt import Workbook

# Define input and output directories
file_dir = r"C:\Users\Documents\corrupted_files"
output_dir = r"C:\Users\Documents\restored_data"

# Create output directory if it doesn't exist (prevents missing folder errors)
os.makedirs(output_dir, exist_ok=True)

for filename in os.listdir(file_dir):
    # Build full paths for input and output files
    full_input_path = os.path.join(file_dir, filename)
    file_base = os.path.splitext(filename)[0]
    full_output_path = os.path.join(output_dir, f"{file_base}.xlsx")
    
    print(f"Processing {filename}...")
    try:
        # Open the file with its full path (using 'with' ensures proper file closure)
        with io.open(full_input_path, "r", encoding="utf-16") as file1:
            data = file1.readlines()
        
        # Create workbook and sheet
        xldoc = Workbook()
        sheet = xldoc.add_sheet("Sheet1", cell_overwrite_ok=True)
        
        # Write cleaned data to the sheet
        for i, row in enumerate(data):
            # Remove newline characters and split by tabs
            cleaned_row = row.replace("\n", "").split("\t")
            for j, val in enumerate(cleaned_row):
                sheet.write(i, j, val)
        
        # Save the restored Excel file
        xldoc.save(full_output_path)
        print(f"Successfully saved to {full_output_path}\n")
    except Exception as e:
        print(f"Failed to process {filename}: {str(e)}\n")

Key improvements here:

• Used os.path.join() to build full file paths (critical for finding files in the correct directory)
• Added os.makedirs() to auto-create the output folder if it doesn't exist
• Wrapped operations in a try/except block to catch errors without stopping the entire loop
• Used the with statement for file handling (safer and cleaner than manual file closure)

Second: Merging Restored Files into a Pandas DataFrame #

Next, here's code to merge your restored Excel files into a single DataFrame. It supports merging all files or just the first N (like 10, to avoid memory issues):

import pandas as pd

def merge_restored_excels(output_dir, max_files=None):
    # Get list of all restored Excel files
    excel_files = [f for f in os.listdir(output_dir) if f.endswith(".xlsx")]
    
    # Limit to first N files if max_files is specified
    if max_files is not None:
        excel_files = excel_files[:max_files]
    
    df_list = []
    for file in excel_files:
        full_path = os.path.join(output_dir, file)
        try:
            # Read each Excel file into a DataFrame
            df = pd.read_excel(full_path)
            df_list.append(df)
            print(f"Added {file} to merge list")
        except Exception as e:
            print(f"Failed to read {file}: {str(e)}")
    
    # Combine all DataFrames into one
    if df_list:
        combined_df = pd.concat(df_list, ignore_index=True)
        print(f"\nSuccessfully merged {len(df_list)} files into a single DataFrame")
        return combined_df
    else:
        print("\nNo valid Excel files found to merge")
        return None

# Example usage:
# Merge the first 10 restored files
merged_df = merge_restored_excels(output_dir, max_files=10)

# Or merge all restored files (uncomment below)
# merged_df = merge_restored_excels(output_dir)

# Save the combined DataFrame to a new Excel file (optional)
# if merged_df is not None:
#     merged_df.to_excel(r"C:\Users\Documents\combined_restored_data.xlsx", index=False)

Key features of this merging code:

• Automatically filters for .xlsx files in the output directory
• Includes error handling to skip any corrupted restored files
• ignore_index=True ensures the combined DataFrame has a continuous index
• Easy to adjust to merge all files or a subset (great for memory constraints)

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