How to export multiple dataframe to multiple excel worksheets (in a loop)?


#1

I’m trying to export the results of my optimization problem to excel. I have tested the function below that works very well but I have to pass all the data frames once a time.

XLSX.writetable("report.xlsx", REPORT_A=( DataFrames.columns(df1), DataFrames.names(df1) ), REPORT_B=( DataFrames.columns(df2), DataFrames.names(df2) ))

However, I have many data frames and I’d like to pass them to excel, to different worksheets inside a loop. I have tried to push data frames into the file I have created but it didn’t work. .

ERROR: LoadError: AssertionError: Output file report.xlsx already exists.

Anyone know if it’s possible ? In python, you could make something like that: https://stackoverflow.com/questions/35707723/writing-multiple-pandas-dataframes-to-multiple-excel-worksheets
Thanks in advance.


#2

Presumably one of the things you are looking for is a way to convert Dicts or NamedTuples to keyword arguments. This can be done with splatting e.g.

sort(A; (dims=1,)...)  # with named tuple in 0.7
sort(A; Dict(:dims=>1)...)  # with Dict

Now if you want you can create Dicts in a loop and pass them to XLSX.writetable.


#3

Thanks for your answer but I cannot see how to apply this splat concept :frowning: Let’s suppose I have a dictionary of dataframes

dict_dt["New York"] =  df1
dict_dt["Los Angeles"] = df2
.
.
.

XLSX.writetable cannot to be called inside a loop and requires that I send all the dataframe once a time.
Are you suggesting I have to create another dictionary that keep the values of the arguments(REPORT_A,REPORT_B) and then I will be able to send this dictionary to XLSX.writetable?


#4

I’m a little hazy on what exactly you are trying to do but I was imagining something like

dict = Dict{Symbol,DataFrame}()
for x ∈ somelist
    dict[getname(x)] = getdf(x)
end
XLSX.writetable(fname; dict...)

This way you can do essentially anything you want inside the loop and then call writetable when you are ready to create a file. You needn’t worry about performance as very little actual copying is being done when you populate the Dict. I assume you’ve already looked through the XLSX documentation to see if it’s possible to create file objects before you write them, but it might also be look reading the source code for writetable.


#5

Thanks again.

XLSX.writetable(fname; dict…)

The problem I see in this line above is that I cannot just send dict because the arg needs the name of the sheet, the information about names and columns separately :frowning: . Like bellow:

XLSX.writetable("report.xlsx", REPORT_A=( DataFrames.columns(df1), DataFrames.names(df1) ))

so I cannot see how to send it separately once I have a dictionary that contains my whole set of data frames…

I’m a little hazy on what exactly you are trying to do but I was imagining something like
I’m trying to do exactly the same thing in this code, but in Julia :confused:

import pandas as pd
#initialze the excel writer
writer = pd.ExcelWriter('MyFile.xlsx', engine='xlsxwriter')

#store your dataframes in a  dict, where the key is the sheet name you want
frames = {'sheetName_1': dataframe1, 'sheetName_2': dataframe2,
        'sheetName_3', dataframe3}

#now loop thru and put each on a specific sheet
for sheet, frame in  frames.iteritems(): # .use .items for python 3.X
    frame.to_excel(writer, sheet_name = sheet)

#critical last step
writer.save()

#6

Something like this?

dict = Dict(:sheet1=>(df1.columns, names(df1)),
            :sheet2=>(df2.columns, names(df2)),
            :sheet3=>(df3.columns, names(df3)))
XLSX.writetable(filename; dict...)

You can splat any type of Dict you want, if you need the values to be tuples, you can just put tuples in it.


#7

yeahh, exactly that!! But it still doesn’t work :frowning: . I got the following error :

ERROR: LoadError: MethodError: no method matching writetable(::String, ::Pair{String,Tuple{Array{Any,1},Array{Symbol,1}}}, ::Pair{S
tring,Tuple{Array{Any,1},Array{Symbol,1}}}, ::Pair{String,Tuple{Array{Any,1},Array{Symbol,1}}})

I guess it’s because I have to send a following that looks like that:

XLSX.writetable(filename; name_sheet = (df1.columns, names(df1)) ...)

but i can’t find the right syntax :confused:


#8

The syntax is as described above, you need to call the function as

XLSX.writetable(filename; dict...)

Where the dict... expands your dictionary in the function call.
This is actually really handy and I didn’t know this worked, the function definition is here - might be helpful if the XLSX.jl docs included this use case as well!

To expand on the examples in the XLSX readme:

using DataFrames, XLSX

df1 = DataFrames.DataFrame(COL1=[10,20,30], COL2=["Fist", "Sec", "Third"])
df2 = DataFrames.DataFrame(AA=["aa", "bb"], AB=[10.1, 10.2])    

# writetable example from XLSX README:
XLSX.writetable("report.xlsx", 
    REPORT_A=(DataFrames.columns(df1), DataFrames.names(df1)), 
    REPORT_B=(DataFrames.columns(df2), DataFrames.names(df2)))

# Using splatting and a dict, this can be written as:
d = Dict(:REPORT_A=>(df1.columns, names(df1)), :REPORT_B=>(df1.columns, names(df1)))    
XLSX.writetable("report2.xlsx"; d...)   

#9

Thank you very much :slight_smile: