Python, Pandas from data frame to create new data

Question

Original spreadsheets have 2 columns. I want to pick the rows by given criteria (according to months), and put them into new files.

The original files looked like:

The codes I am using: import os import pandas as 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)

The results are like:

Improvements I want to make:

1. Save them as xlsx files instead of .csv
2. Not to have the first index columns
3. Keeping only 1 row (top) headers (now each csv has 3 rows of headers)

How can I do that? Thank you.

Answer 1

I think need create list of DataFrames, concat together and then write to file:

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)

What should be simplify by list comprehension:

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)