0

I am trying to make dataframes from all the info for all the symbols separately. i.e. df_ACC,df_SBIN and so on. Write these dataframes to excel in separate sheets naming the sheets according to the symbol being used.

The file generated from the below code is just getting the last dataframe i.e. JUSTDIAL. While I can't find the dataframes other than the last one that also only by df_name and not df_JUSTDIAL which was my intent. 

start_time = time.time()
#The solution to second problem I listed
path = r"C:\Users\xxx\OneDrive\Data\Fut Data"
opt_fut_excel_file = r'\fo_data_' + str(datetime.date.today()) + ".xlsx"
f_path = path+opt_fut_excel_file
writer = pd.ExcelWriter(f_path, engine='xlsxwriter')
fut_data.to_excel(writer,sheet_name='futures', index=False)
#Please add writer.save() at the point where writing to excel is finished.
symbols = ['ACC','SBIN','PNB','JUSTDIAL']
for symbol in symbols:
    print(symbol)
    opt_url1 = 'https://www.nseindia.com/marketinfo/companyTracker/mtOptionKeys.jsp?companySymbol='
    opt_url3 = '&indexSymbol=NIFTY&series=EQ&instrument=OPTSTK&date=-'
    url = opt_url1 + symbol + opt_url3
    raw_page = requests.get(url).text
    soup = bs(raw_page, "lxml")
    if len(raw_page) > 0:
        print(symbol + str(len(raw_page)))
        table = soup.findAll('table')[1]
        test_tab = []
        for row in table.findAll('tr'):
             test_tab.append(([tr.text for tr in row.findAll('td')]))
             # print(len(test_tab))
        try:
            del test_tab[0]
            del test_tab[0]
            del test_tab[0]
            del test_tab[0]
            del test_tab[0]
            del test_tab[0]
            del test_tab[0]
            del test_tab[0]
            del test_tab[0]
            column_names = test_tab[0]
            del test_tab[0]
            # df_name = 'df_' + symbol
            # print(df_name)
            # df_name = pd.DataFrame(test_tab,columns=column_names)
            # opt_fut_excel_file = 'fo_data_' + str(datetime.date.today()) + ".xlsx"
            # df_name.to_excel(opt_fut_excel_file, sheet_name=symbol)
        except:
            print("There was an exception in list del with symbol " + symbol)
             pass
        df_name = 'df_' + symbol
        print(df_name)
        df_name = pd.DataFrame(test_tab, columns=column_names)
        #print(df_name)
        #opt_fut_excel_file = 'fo_data_' + str(datetime.date.today()) + ".xlsx"
        df_name.to_excel(writer, sheet_name=symbol)
    raw_page = 0
#Solution to second problem, this line needs to be there for the file to be saved
writer.save()
print("--- %s seconds ---" % (time.time() - start_time))

----I believe the question to be not a duplicate because-----

The two-fold question is as follows:

1- Why is the above code not generating multiple dataframes with different names?

2- SOLVED - Why are the sheets not getting created while the last one is being generated? @JonClements comment made me get to the answer for this. I have updated the question and code to reflect the same.

Sid
  • 3,749
  • 7
  • 29
  • 62
  • Possible duplicate of [How to save a new sheet in an existing excel file, using Pandas?](https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas) – Lambda Mar 19 '18 at 15:50
  • I don't believe it is a duplicate. I am unable to create multiple dataframes for some reason. – Sid Mar 19 '18 at 16:06
  • What is with all that `del test_tab[0]` stuff... Have you tried running the code either without a try/except or without such as a bare exception which'll catch *everything* - your code is masking what's causing the error there... Anyway - the link above shows how you're supposed to save multiple sheets to the same workbook - you don't appear to have anything similar to that in your code... – Jon Clements Mar 19 '18 at 16:12
  • @JonClements Thanks, that was what I was unclear about. Still dont know how to go about it. – Sid Mar 19 '18 at 16:24
  • 1
    You're always over-writing the workbook - so only the last copy of the workbook exists.... If you look at the example in the link above - you create an instance of a `writer` object, and re-use that instead of a filename... – Jon Clements Mar 19 '18 at 16:27
  • @JonClements Thank you. That was it. Still trying to figure out how to generate multiple `dataframe`'s though. – Sid Mar 19 '18 at 17:15
  • `df_name` is just a variable name, It's not important. If you really care about it, use code `locals()[df_name] = pd.DataFrame(test_tab, columns=column_names)`, but it's not recommended. Your code `df_name = 'df' + symbol` does not named a variable, It assign to it, and the code `df_name = pd.DataFrame(test_tab, columns=column_names)` replaces the assignment. Hope I explain clearly. – Lambda Mar 19 '18 at 20:49

0 Answers0