Problems writing to Excel in pandas

Question

Have the following annoying problem with an ipython script using the to_excel function in Pandas: At the end of the script I want to write results to an Excel file. If I include a path e.g "~/", the script breaks down with the enclosed error messages. If I just input a file name the script runs and produce the new file in the current folder. Is this a bug or am I doing something wrong? The code is as follows:

path=dir+file_name
print "Writing to %s " % path
opt_design.to_excel(path, sheet_name='sheet1',index=False)
print "Finished!"``

The runtime error is:

---------------------------------------------------------------------------
IOError                                   Traceback (most recent call last)
<ipython-input-1-b9e6707b5b6c> in <module>()
 43 path=dir+file_name
 44 print "Writing to %s " % path
---> 45 opt_design.to_excel(path, sheet_name='sheet1',index=False)
 46 print "Finished!"
 47 

/Library/Python/2.7/site-packages/pandas-0.11.1.dev_fcced51_20130617-py2.7-macosx-10.8- intel.egg/pandas/core/frame.pyc in to_excel(self, excel_writer, sheet_name, na_rep, float_format, cols, header, index, index_label, startrow, startcol)

1494                                  startrow=startrow, startcol=startcol)
1495         if need_save:
-> 1496             excel_writer.save()
1497 
1498     def to_stata(self, fname, convert_dates=None, write_index=True, encoding="latin-1",

/Library/Python/2.7/site-packages/pandas-0.11.1.dev_fcced51_20130617-py2.7-macosx-10.8-intel.egg/pandas/io/excel.pyc in save(self)

 351         Save workbook to disk
 352         """
 --> 353         self.book.save(self.path)
 354 
 355     def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):

/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/openpyxl/workbook.pyc in save(self, filename)

 212             save_dump(self, filename)
 213         else:
 --> 214             save_workbook(self, filename)

/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/openpyxl/writer/excel.pyc in save_workbook(workbook, filename)

 153     """
 154     writer = ExcelWriter(workbook)
 --> 155     writer.save(filename)
 156     return True
 157 

/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/openpyxl/writer/excel.pyc in save(self, filename)

 135     def save(self, filename):
 136         """Write data into the archive."""
 --> 137         archive = ZipFile(filename, 'w', ZIP_DEFLATED)
 138         self.write_data(archive)
 139         archive.close()

/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/zipfile.pyc in init(self, file, mode, compression, allowZip64)

 750             modeDict = {'r' : 'rb', 'w': 'wb', 'a' : 'r+b'}
 751             try:
 --> 752                 self.fp = open(file, modeDict[mode])
 753             except IOError:
 754                 if mode == 'a':

IOError: [Errno 2] No such file or directory: '~/CloudStation/test1.xlsx'

Answer 1

Python doesn't expand the home directory for you. You have a couple of options:

1. os.path.expanduser(the_path)
2. os.path.join(os.environ.get('HOME'), the_path)

Here's an example

In [17]: os.path.expanduser('~/a_path_wth_tilde')
Out[17]: '/home/phillip/a_path_wth_tilde'