0

Hi I am hoping someone can help me if possible.

I have a large spreadsheet of data that I have created a 'dictionary of data frames' I am however struggling to now export this to one excel file with each data frame having its own sheet in the excel document. As this could be used by other people I would also like to make the file export flexible ( it will be a clickable exe file)

I have looked at the following posts for help but just cant seem to get my head round it:

Python - splitting dataframe into multiple dataframes based on column values and naming them with those values

Save list of DataFrames to multisheet Excel spreadsheet

My code is as follows:

# Sort the Dataframe
df.sort_values(by = 'Itinerary_Departure_Date')

#Seperate Bookings By Itinerary
df_dict = dict(iter(df.groupby('Itinerary_Departure_Date')))

filepath = filedialog.asksaveasfilename(defaultextension = 'xlsx')

def frames_to_excel(df_dict, path = 'filepath'):
    #Write dictionary of dataframes to separate sheets, within 1 file.

    writer = pd.ExcelWriter(path, engine='xlsxwriter')

    for tab_name, df_dict in df_dict.items():
        df_dict.to_excel(writer, sheet_name=tab_name)

    writer.save()

Fixed it!

Went down a different rabbit hole!

#Seperate Bookings By Itinerary
dict_of_itin = {k: v for k, v in df.groupby('Itinerary_Departure_Date')}

#Chooseemptyexcelfromwhereeversaved
root = tk.Tk()
root.withdraw()

file_path = filedialog.askopenfilename()

book = load_workbook(file_path.replace('\\','/'))
writer = pd.ExcelWriter(file_path, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

for df_name, df in dict_of_itin.items():
    df.to_excel(writer, sheet_name=df_name)

writer.save()

It relies on the person using it saving an empty spreadsheet first wherever they want but then will write to it.

not as elegant but it works! :D

Chris

Chris Barton
  • 1
  • 1
  • This line `for tab_name, df_dict in df_dict.items():` is likely causing problems. Your dictionary is called `df_dict` which is immediately clobbered by the first loop where you overwrite it in the indicated line – Andrew Oct 16 '20 at 14:00
  • What is the error you get? – Mehdi Golzadeh Oct 16 '20 at 14:46
  • MhDG7 - Nothing it just doesnt run in the interpreter (doesnt even load the import for Tkinter) – Chris Barton Oct 16 '20 at 15:29
  • @Andrew I have changed my code slightly but still cant seem to fathom it can you check I have changed what I need to? for tab_name, df1 in df_dict.items(): – Chris Barton Oct 16 '20 at 15:32
  • If you've made changes, you should modify the question, because it's quite hard to read the code you have in your comment. Your main question should also provide additional information such as where your function call is. If the file isn't running in the interpreter, then there may not be an issue with your code at all – Andrew Oct 16 '20 at 15:35
  • Hi @Andrew Apologies pushed enter as was comenting hadnt finished editing. my bad! – Chris Barton Oct 16 '20 at 15:37
  • The docs suggest that the `save` call is redundant as well, not to mention that the methods are not public, so that call probably would fail anyway. Passing the object to the `to_excel` function should work though: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html – Andrew Oct 16 '20 at 15:45
  • hi @Andrew - took a different approach but it works got it working.. thanks for looking and helping :) Chris – Chris Barton Oct 16 '20 at 17:03

0 Answers0