CODEWITHSUNDEEP
excelpython-3.xpandas
milikest
milikest

Reputation: 61

How to save a Dataframe into an excel sheet without deleting other sheets?

I am triyng to pull some data from a stock market and saving them in different excel files. Every stock trade process has different timeframes like 1m, 3m, 5m, 15m and so on.. I want to create an excel file for each stock and different sheets for each time frames.

My code creates excel file for a stock (symbol) and adds sheets into it (1m,3m,5m...) and saves the file and then pulls the data from stock market api and saves into correct sheet. Such as ETH/BTC, create the file and sheets and pull "1m" data and save it into "1m" sheet.

Code creates file and sheets, I tested it. The problem is after dataframe is written into excel file it deletes all other sheets. I tried to pull all data for each symbol. But when I opened the excel file only last time frame (1w) has been written and all other sheets are deleted. So please help.

I checked other problems but didn't find the same problem. At last part I am not trying to add a new sheet I am trying to save df to existed sheet.

#get_bars function pulls the data

def get_bars(symbol, interval):
.
.
.
return df

...

timeseries=['1m','3m','5m','15m','30m','1h','2h','4h','6h','12h','1d','1w']

from pandas import ExcelWriter
from openpyxl import load_workbook

for symbol in symbols:
    file = ('C:/Users/mi/Desktop/Kripto/' + symbol + '.xlsx')
    workbook = xlsxwriter.Workbook(file)
    workbook.close()
    wb = load_workbook(file)
    for x in range(len(timeseries)):
        ws = wb.create_sheet(timeseries[x])
        print(wb.sheetnames)
        wb.save(file)
        workbook.close()
    xrpusdt = get_bars(symbol,interval='1m')
    writer = pd.ExcelWriter(file, engine='xlsxwriter')
    xrpusdt.to_excel(writer, sheet_name='1m')
    writer.save()

Upvotes: 1

Views: 5905

Answers (1)

Gangula
Gangula

Reputation: 7334

I think instead of defining the ExcelWriter as a variable, you need to use it in a With statement and use the append mode since you have already created an excel file using xlsxwriter like below 

for x in range(len(timeseries)): 
    xrpusdt = get_bars(symbol,interval=timeseries[x]) 
with pd.ExcelWriter(file,engine='openpyxl', mode='a') as writer: 
    xrpusdt.to_excel(writer, sheet_name=timeseries[x])

And in your code above, you're using a static interval as "1m" in the xrpusdt variable which is changed into variable in this code.

Resources:

Pandas ExcelWriter: here you can see the use-case of append mode
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html#pandas.ExcelWriter

Pandas df.to_excel: here you can see how to write to more than one sheet
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

Upvotes: 1

Related Questions