CODEWITHSUNDEEP
pythonexcelpandasnumpyxlsxwriter
Thomas Philips
Thomas Philips

Reputation: 1089

Writing Pandas Dataframes as well as numpy arrays to a common Excel File

Using Python 3.6 and Pandas 0.22 I have created a pandas dataframe IRdaily with time series of daily changes in various interest rates. I can easily write the Pandas dataframe to Excel using the commands:

writer = pd.ExcelWriter(outFileName)
IRdaily.to_excel(writer, 'Daily Changes')

But if I now compute the eigenvalue decomposition (both the vector of eigenvalues and the matrix of eigenvectors are numpy ndarrays) and try to write them to the same worksheet I get an error:

covD  = deltaIRdaily.cov()
eigValD, eigVecD = np.linalg.eigh(covD)
eigValD.to_excel(writer, 'Daily Changes', startcol = 15) 
eigVecD.to_excel(writer, 'Daily Changes', startrow = 15, startcol = 15) 


Traceback (most recent call last):
  File "c:\Documents\Python\FedInterestRateDataExtract.py", line 58, in <module>
    eigValD.to_excel(writer, 'Daily Changes', startcol = 15)
AttributeError: 'numpy.ndarray' object has no attribute 'to_excel'

How can I write ndarrays to a specified row and column in an existing worksheet in an open workbook? It's being done correctly with Pandas dataframes, so I'm guessing that there must be a simple way in which to write the results of any analysis of the data held in a Pandas dataframe.

Many thanks in advance for your advice and assistance

Thomas Philips

Upvotes: 1

Views: 7961

Answers (1)

fuglede
fuglede

Reputation: 18211

First, note that to_excel is a method on DataFrame, so it should not be too surprising that it fails when invoked on an np.ndarray.

Regarding the actual problem, it's quite a roundabout way of doing it, but since you already have a pd.ExcelWriter set up, I would suggest simply turning the two arrays into DataFrames, doing instead something that amounts to

pd.DataFrame(eigValD).to_excel(writer, 'Daily Changes', startcol=15, header=False, index=False)

A more direct way would be to just use xlsxwriter directly.

Upvotes: 4

Related Questions