1

I have created a webscraper that pulls data from thru chrome driver, puts it into a dataframe, and prints all the values. In order to do some trend analysis, I plan to run the code 5 times a day each day. Therefore I want to put the data into excel by creating a new sheet during each cycle.

My data is in a dataframe format. My issue stems from:

Using openpyxl - cannot input the df format referenced in the code below

Using pandas - the data has been getting overwritten in sheet 1. I want each sheet to have a timestamp of when it ran, but this completely overwrites.

So the way I see it, I can either get pandas to add a new workbook during each run cycle (ie adding a new sheet and appending the data there) or I need to figure out a way to get the df into openpyxl format.

from datetime import datetime
import pandas as pd
import numpy as np

path = r"C:\\Users\\Jacob\\Documents\\MyStuff\\weather.xlsx"

now = datetime.now()
j = now.strftime("%m-%d, %H.%M.%S")

x1 = all_weather
df1 = pd.DataFrame(x1)

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df1.to_excel(writer, sheet_name = str(j))
writer.save()
writer.close()

OR

book = load_workbook('C:\\Users\\Jacob\\Documents\\MyStuff\\weather.xlsx')
now = datetime.now()
j = now.strftime("%m-%d, %H.%M.%S")
sheet = book.create_sheet(str(j))
sheet.append(weather_df)

When using openpyxl

TypeError: Value must be a list, tuple, range or generator, or a dict. Supplied value is

When using pandas sheet gets overwritten each time.

VBAINPROGRESS
  • 13
  • 4

1 Answers1

1

I personally recommend using xslxwriter instead of openpyxl.

But you should use pandas to_excel() rather then creating a new sheet with another module and appending a dataframe to that sheet. So it would look more like

weather_df.to_excel("path_to_excel_file.xlsx",sheet_name = "sheet name here")

Amin
  • 908
  • 1
  • 11
  • 23
  • Thanks for your insight here. Unfortunately that leads to my original issue, in which i don't create a new sheet, but instead just overwrite the existing sheet. What I would like is for pandas to add a new sheet and past the info in it, and then save. So the workbook would add 5 new sheets per day. – VBAINPROGRESS Aug 18 '19 at 19:15
  • Not sure if you've solved it, but I found a few other stack overflow posts about this question when I googled "pandas add sheet to existing excel", hopefully this one helps? https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas – Amin Aug 19 '19 at 04:04