Saving one dataframe to multiple sheets in the same Excel

Question

Have a data frame and based off the sub headers for that data-frame, want to save to different sheets in the same csv file.

Tried writer.

from bs4 import BeautifulSoup as soup
import urllib.request
import pandas as pd
import pandas_datareader as pdr
from pandas import ExcelWriter

list = ['MDB', 'SQ', 'GOOS', 'NVDA', 'AMZN', 'ENPH', 'TEAM', 'TWLO', 'ZM']
#print (list[1])
#xls_path = '/Users/tony/documents/Python_Projects/stock_prices.csv'

writer = pd.ExcelWriter("test_prices.csv", engine='xlsxwriter')


for item in list:
    print (item)

for item in list:
#     earnings = pd.read_html('https://finance.yahoo.com/calendar/earnings?day=2019-06-13&symbol=' + str(item))[0]
#     latest_earnings = earnings[:4]
#     print (latest_earnings)
    print ('\n This is the price for:' + str(item) + '\n')    
    prices = pdr.get_data_yahoo(item)[-30:]
#     writer = prices.ExcelWriter("stock_prices.xlsx", engine='xlsxwriter')

#         def save_xls(xls_path):
#             with ExcelWriter(xls_path) as writer:
#                 for n, df in enumerate(list_dfs):
#                     df.to_excel(writer,'sheet%s' % n)

#         writer.save()

#     writer = ExcelWriter(xls_path)
# #         for n, df in enumerate(df_list):
#     df.to_excel(writer, item)

    print (prices)

#     names = df['name'].unique().tolist()

# writer = pandas.ExcelWriter("MyData.xlsx", engine='xlsxwriter')

# for myname in names:
#     mydf = df.loc[df.name==myname]
    prices.to_excel(writer, item)

writer.save()

Traceback:

ValueError                                Traceback (most recent call last)
<ipython-input-20-f407b2c5f3fe> in <module>()
      9 #xls_path = '/Users/tony/documents/Python_Projects/stock_prices.csv'
     10 
---> 11 writer = pd.ExcelWriter("test_prices.csv", engine='xlsxwriter')
     12 
     13

The issue might be that you are writing to a .csv file. Try to change "test_prices.csv" to '"test_prices.xlsx". Excel does not allow for multiple sheets within a csv file — Ian, Sep 09 '19 at 16:43
also, check this resource for writing multiple sheets using pd.ExcelWriter: https://xlsxwriter.readthedocs.io/example_pandas_multiple.html — Ian, Sep 09 '19 at 16:47
see my answer below, it is definitely possible to save multiple sheets in an excel file with pandas. — JacoSolari, Sep 09 '19 at 16:52

score 1 · Answer 1 · answered Sep 09 '19 at 16:55

CSV, as a file format, assumes one "table" of data; in Excel terms that's one sheet of a workbook.

So you need to output this into an .xlsx file:

from bs4 import BeautifulSoup as soup
import urllib.request
import pandas as pd
import pandas_datareader as pdr
from pandas import ExcelWriter

list = ['MDB', 'SQ', 'GOOS', 'NVDA', 'AMZN', 'ENPH', 'TEAM', 'TWLO', 'ZM']
# print (list[1])
# xls_path = '/Users/tony/documents/Python_Projects/stock_prices.csv'

writer = pd.ExcelWriter("test_prices.xlsx", engine='xlsxwriter')

for item in list:
    print(item)

for item in list:
    print('\n This is the price for:' + str(item) + '\n')
    prices = pdr.get_data_yahoo(item)[-30:]

    prices.to_excel(writer, sheet_name=item)

writer.save()

score 0 · Answer 2 · answered Sep 09 '19 at 16:50

Assuming that pdr.get_data_yahoo(item)[-30:] returns a DataFrame this should save each dataframe obtained from a different item to a different sheet named as the item in the output.xlsx file.

list = ['MDB', 'SQ', 'GOOS', 'NVDA', 'AMZN', 'ENPH', 'TEAM', 'TWLO', 'ZM']   
with pd.ExcelWriter('output.xlsx') as writer:  
    for item in list:
        prices = pdr.get_data_yahoo(item)[-30:]
        prices.to_excel(writer, item)

Saving one dataframe to multiple sheets in the same Excel

2 Answers2