Storing appended excel to specific folder

Question

I have appended multiple excel files and I want to store it in a specific folder. How can I specify the file path where it can be stored. I used the following code:

# importing the required modules
import glob
import pandas as pd

# specifying the path to excel files
path = "C:\file path"
 
# xlsx files in the path
file_list = glob.glob(path + "/*.xlsx")
 
# list of excel files we want to merge.
# pd.read_excel(file_path) reads the 
# excel data into pandas dataframe.
excl_list = []
 
for file in file_list:
    excl_list.append(pd.read_excel(file))
 
# concatenate all DataFrames in the list
# into a single DataFrame, returns new
# DataFrame.
excl_merged = pd.concat(excl_list, ignore_index=True)
 
# exports the dataframe into excel file
# with specified name.
excl_merged.to_excel('combined_files.xlsx', index=False)

I have a serial number column. File 1 has S/N 1-10 and File 2 has S/N 1-5. When we append, the combined file has S/N 1-10, then 1-5 and not 1-15. How can I make my S/N 1-15?

Answer 1

Just specify the full path in excl_merged.to_excel(). eg.:

excl_merged.to_excel(r'C:\file path\output_dir\combined_files.xlsx', index=False)

To create a new column 'S/N' from the index, you could do something like:

excl_merged.reset_index(inplace=True)
excl_merged.drop(['index', 'S/N'], axis=1, inplace=True)
excl_merged['S/N'] = excl_merged.index
excl_merged.to_excel(r'C:\file path\output_dir\combined_files.xlsx', index=False)