can't save excel sheet using pandas

Question

I working on a college project, and due to some limitations, I'm using python 2.7 as pandas support for python 2 has ended I installed the last released version for python 2 which is pandas 0.24.2.

when I'm trying to execute the following code:

    f = "./attendance_report" + "/" + current_course +"("+time.strftime("%d.%m.%Y_%H.%M")+")"+ "_" + "attendance.xlsx"
    writer = pd.ExcelWriter(f, engine='xlsxwriter')
    sheet_name = 'sheet1'
    
    index = [i+1 for i in range(len(checked_names))]
    indexx = [i+1 for i in range(len(registered_names_current_course))]
    
    df = pd.DataFrame(list(zip(index, checked_names, checked_ID)), columns=['Index','Student Name','Student ID'])
    df2 = pd.DataFrame(list(zip(indexx, registered_names_current_course, registered_ID_current_course)), columns=['index','Student_Name','Student_ID'])
    df.to_excel(writer, sheet_name=sheet_name, startrow = 6, index = False)
    df2.to_excel(writer, sheet_name=sheet_name,startrow = len(df)+10, index = False)
    
    for column in df:
        column_width = max(df[column].astype(str).map(len).max(), len(column))
        col_idx = df.columns.get_loc(column)
        writer.sheets[sheet_name].set_column(col_idx, col_idx, column_width)

    for column in df2:
        column_width = max(df2[column].astype(str).map(len).max(), len(column))
        col_idx = df2.columns.get_loc(column)
        writer.sheets[sheet_name].set_column(col_idx, col_idx, column_width)
        
    workbook = writer.book
    worksheet = writer.sheets[sheet_name]
    worksheet.write(0, 0, 'Attendance report of '+current_course+' class', workbook.add_format({'bold': True, 'color': '#E26B0A', 'size': 14, 'align': 'left'}))
    worksheet.write(2, 0, 'Date: '+time.strftime("%d/%m/%Y"),workbook.add_format({'bold': True}))
    worksheet.write(2, 2, 'Time: '+time.strftime("%r"),workbook.add_format({'bold': True}))
    worksheet.write(4, 1, 'Students present',workbook.add_format({'bold': True}))
    worksheet.write(len(df)+8, 1, 'Student absent',workbook.add_format({'bold': True}))
    
    writer.save()

This code will create an excel shit with some formatting.

But when I'm trying to execute the following code, I'm getting this error message:

File "main.py", line 312, in <module>
    writer.save()
  File "/home/pi/.local/lib/python2.7/site-packages/pandas/io/excel.py", line 1952, in save
    return self.book.close()
  File "/home/pi/.local/lib/python2.7/site-packages/xlsxwriter/workbook.py", line 325, in close
    raise FileCreateError(e)
xlsxwriter.exceptions.FileCreateError: [Errno 2] No such file or directory: './attendance_report/os(08.05.2022_13.37)_attendance.xlsx'

I checked the documentation of pandas 0.24.2 but couldn't find any solution there. I'm very new to pandas and this is the first time I'm using it.

Does anyone have any idea about this? thanks in advance.

Answer 1

You'd better check the directory is created.

import os
os.path.exists("attendance_report")