将Pandas.read_excel读取的DataFrame追加至新表时遇空DataFrame问题

Hey there! Let's dig into why your DataFrames are showing up empty even though you thought the files loaded successfully. I’ve run into this exact issue before, so here are the most likely fixes to try:

1. Double-check your file paths #

First off, make sure you’re pointing to the right place. If your script isn’t in the same folder as the data1 subdirectory, your relative path might be off.

• Run this to confirm your current working directory:

  import os
  print(os.getcwd())
  

  Ensure data1 is a subfolder of this directory.
• When looping through files, always use os.path.join() to build the full path (avoids issues with slashes across operating systems):

  file_path = os.path.join("data1", filename)
  

2. Test loading a single file first #

Don’t jump into loading all files at once—start with one to isolate the problem. Try loading a.xls directly:

import pandas as pd
df_test = pd.read_excel("data1/a.xls")
print(df_test.shape)  # Shows (rows, columns) to confirm data exists
print(df_test.head())

If this returns an empty DataFrame, the issue is with the file itself, not your loop:

• Hidden/empty rows at the top: If your data starts after a few empty rows, use skiprows to skip them:

  df_test = pd.read_excel("data1/a.xls", skiprows=1)  # Adjust the number based on your file
  

• Wrong sheet: If your XLS has multiple sheets, Pandas defaults to the first one. Check if the data is in another sheet:

  # Load all sheets to inspect their contents
  sheets = pd.read_excel("data1/a.xls", sheet_name=None)
  for sheet_name, sheet_df in sheets.items():
      print(f"Sheet: {sheet_name}, Rows: {len(sheet_df)}")
  

• File format mismatch: Sometimes files saved as .xls are actually .xlsx or corrupted. Try opening the file in Excel to confirm it loads correctly, then specify the engine if needed (e.g., engine="openpyxl" for .xlsx files—Pandas usually auto-detects, but manual override can help).

3. Fix your multi-file loading logic #

If single files load fine, the problem is likely in how you’re combining them. Here’s a foolproof way to load and concatenate all .xls files in data1:

import pandas as pd
import os

folder = "data1"
all_data = []

for file in os.listdir(folder):
    if file.endswith(".xls"):
        full_path = os.path.join(folder, file)
        df = pd.read_excel(full_path)
        # Print a status update to confirm each file is loading data
        print(f"Loaded {file}: {len(df)} rows found")
        all_data.append(df)

# Combine all DataFrames into one
combined_df = pd.concat(all_data, ignore_index=True)

# Verify the final result
print("\nCombined DataFrame Info:")
combined_df.info()
print("\nFirst 5 rows:")
display(combined_df.head())

The key here is printing the row count for each file—this lets you see if any individual file is returning zero rows, which would make the combined DataFrame empty if all files are problematic.

4. Check for odd formatting in your files #

Looking at your sample a.xls data, it seems like you have a proper header row and data rows. But sometimes Excel files have merged cells, hidden columns, or extra spaces that throw Pandas off. Try:

• Opening the file in Excel, selecting all data, and re-saving it (this can fix minor corruption or formatting quirks).
• Using header=0 explicitly to tell Pandas the first row is the header:

  df = pd.read_excel(full_path, header=0)
  

Give these steps a try, and you should be able to track down why your DataFrames are empty!

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