如何汇总多文件指定列并保存？代码仅读取最后一个文件求助

Hey there! I see the issue with your code—let's get it sorted so you can aggregate data from all your Excel files, not just the last one.

The Root Problem #

In your loop, you're using data = all_data.append(df,ignore_index=True) but never updating the original all_data variable. Pandas' append() method is a non-in-place operation: it returns a brand new DataFrame instead of modifying the existing all_data. So after each loop, all_data stays empty, and only the final df gets added right before grouping.

Fixed Code (Option 1: Fixing append() Usage) #

Here's how to adjust your loop to actually build up all_data with every file:

import pandas as pd
import glob as glob
import numpy as np

# Initialize empty DataFrame
all_data = pd.DataFrame()

for f in glob.glob(r'C:\Users\Sarah\Desktop\IDPMosul\Data\2014\09\*.xlsx'):
    df = pd.read_excel(f, index_col=None, na_values=['NA'])
    df['filename'] = f
    # Update all_data by reassigning the result of append()
    all_data = all_data.append(df, ignore_index=True)

# Group and Sum (note: for Pandas >= 0.24, use double brackets to avoid FutureWarning)
result = all_data.groupby(["Date"])[["Families", "Individuals"]].agg(np.sum)

# Save file
file_name = r'C:\Users\Sarah\Desktop\U2014.csv'
result.to_csv(file_name, index=True)

Better Alternative: Use pd.concat() (Recommended) #

append() is deprecated in newer Pandas versions. A cleaner, more efficient approach is to collect all DataFrames in a list first, then concatenate them once at the end:

import pandas as pd
import glob as glob
import numpy as np

# Collect all DataFrames in a list
df_list = []
for f in glob.glob(r'C:\Users\Sarah\Desktop\IDPMosul\Data\2014\09\*.xlsx'):
    df = pd.read_excel(f, index_col=None, na_values=['NA'])
    df['filename'] = f
    df_list.append(df)

# Concatenate all DataFrames at once
all_data = pd.concat(df_list, ignore_index=True)

# Group and Sum
result = all_data.groupby(["Date"])[["Families", "Individuals"]].agg(np.sum)

# Save file
file_name = r'C:\Users\Sarah\Desktop\U2014.csv'
result.to_csv(file_name, index=True)

Key Notes #

• I added double brackets [["Families", "Individuals"]] in the groupby line—this avoids a FutureWarning in newer Pandas versions, as single brackets for multiple columns will be removed.
• Using pd.concat() is faster than repeated append() calls, especially if you have a lot of files, because it minimizes intermediate DataFrame copies.

After running either version, your U2014.csv should contain the summed data from all your Excel files grouped by Date.

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