Python、Pandas 从数据框创建新数据

Question

原始电子表格有 2 列。我想按照给定的条件(根据月份)选择行，并将它们放入新文件中。

原始文件如下所示:

我正在使用的代码: 导入操作系统 将 pandas 导入为 pd

working_folder = "C:\\My Documents\\"

file_list = ["Jan.xlsx", "Feb.xlsx", "Mar.xlsx"]

with open(working_folder + '201703-1.csv', 'a') as f03:
    for fl in file_list:
        df = pd.read_excel(working_folder + fl)
        df_201703 = df[df.ARRIVAL.between(20170301, 20170331)] 
        df_201703.to_csv(f03, header = True)

with open(working_folder + '201702-1.csv', 'a') as f02:
    for fl in file_list:
        df = pd.read_excel(working_folder + fl)
        df_201702 = df[df.ARRIVAL.between(20170201, 20170231)] 
        df_201702.to_csv(f02, header = True)

with open(working_folder + '201701-1.csv', 'a') as f01:
    for fl in file_list:
        df = pd.read_excel(working_folder + fl)
        df_201701 = df[df.ARRIVAL.between(20170101, 20170131)] 
        df_201701.to_csv(f01, header = True)

结果如下:

我想要做出的改进:

1. 将它们另存为 xlsx 文件而不是 .csv
2. 没有第一个索引列
3. 仅保留 1 行(顶部)标题(现在每个 csv 有 3 行标题)

我怎样才能做到这一点？谢谢。

Answer 1

我认为需要创建DataFrame列表，concat在一起，然后写入文件:

dfs1 = []

for fl in file_list:
    df = pd.read_excel(working_folder + fl)
    dfs1.append(df[df.ARRIVAL.between(20170101, 20170131)] )

pd.concat(dfs1).to_excel('201701-1.xlsx', index = False)

应该通过列表理解来简化什么:

file_list = ["Jan.xlsx", "Feb.xlsx", "Mar.xlsx"]
dfs1 = [pd.read_excel(working_folder + fl).query('20170101 >= ARRIVAL >=20170131') for fl in file_list]

pd.concat(dfs1).to_excel('201701-1.xlsx', index = False)