unable to write pandas dataframe to different sheets of excel-CodePudding

while im writing pandas dataframe to excel its throwing below error: FileCreateError: [Errno 2] No such file or directory: '/mnt/new/UTPUT/output_files/new.xlsx'

im using below code to write multiple dataframes to different sheets in same excel:

import pandas as pd
from collections import OrderedDict
from boltons.iterutils import remap
#initialze the excel writer

writer = pd.ExcelWriter('/mnt/new/UTPUT/output_files/new.xlsx', engine='xlsxwriter')
sheets=['0','1','2','3','4']
for item,i in zip(data,sheets):
  #data_new = {k: v if v is not None else '' for k, v in item.items()}
  #removing empty columns in raw data
  drop_none = lambda path, key, value: key is not None and value is not None
  cleaned = remap(item, visit=drop_none)
  new_data=flatten(cleaned)
  #my_df = new_data.dropna(axis='columns', how='all') # Drops columns with all NA values
  #dfFromRDD2 = spark.createDataFrame(new_data)
  pandasDF = pd.DataFrame(data=new_data)
  #pandasDF = dfFromRDD2.toPandas()
  display(pandasDF)

  pandasDF.to_excel(writer, sheet_name =i)
writer.save()

what is the issue with the code? any suggestions

CodePudding user response：

The path is not created yet. You can check whether the path exists; if not, create it

from pathlib import Path
path = r'./mnt/new/UTPUT/output_files/'
if not os.path.exists(path):
    os.makedirs(path)
writer = pd.ExcelWriter('/mnt/new/UTPUT/output_files/new.xlsx', engine='xlsxwriter')   ...

CodePudding user response：

Is your script in the same directory? the folder exists?

You can use an absolute path

writer = pd.ExcelWriter('C:\\path\\new.xlsx', engine='xlsxwriter')

are you using windows?