0

I have a set of files I need to clean and save into one excel file. I want the sheet names in the finished Excel file to be the same as the file names. I was able to get the file names and save the data into each sheet in an earlier version of my code but I’m having trouble adding the file name as the sheet name.

writer = pd.ExcelWriter('Data.xlsx')
df_list= []
dir_b = r'/Desktop/MyProjects'

sheet_tabs = []
file_names = os.listdir(dir_b)
for line in file_names:
    if not line.startswith('.'):
        if re.search('\.csv', line): 
            string = line.split('.')[0]
            sheet_tabs.append(string)

for csv in sorted(Path(dir_b).glob('*.csv')):
    df = pd.read_csv(csv)
    df.rename(columns = lambda x: x.strip(), inplace = True)
    car_column = df.pop('car')
    df.insert(9, 'car', car_column)
    df.drop_duplicates(keep = 'first', inplace = True)
    df_list.append(df)

df_dict = dict.fromkeys(sheet_tabs, df_list)

for key, values in df_dict.items():
    values.to_excel(writer, sheet_name = key, index = False)
writer.save()
Nimantha
  • 6,405
  • 6
  • 28
  • 69
youaresoconfused
  • 31
  • 1
  • 6
  • Does this answer your question? [How to rename the sheet name in the spread-sheet using Python?](https://stackoverflow.com/questions/39540789/how-to-rename-the-sheet-name-in-the-spread-sheet-using-python) – Irfanuddin Jan 16 '22 at 07:42
  • You should set the title for each sheet as you create it. – Charlie Clark Jan 17 '22 at 08:27

0 Answers0