Writing excel from dataframes

New to Julia
1

Hi… I am somewhat new to julia and am still learning how to play around with data. I feel more comfortable with excel and want to export my dataframes into an excel sheet.

I followed this template:
XLSX.writetable(
“filename.xlsx”,
sheet_1 = (collect(eachcol(df1), names(df1))),
sheet_2 = (collect(eachcol(df2), names(df2))),
)

I match it up to my previously set data frames but keep getting this error: ERROR: MethodError: no method matching collect(::DataFrames.DataFrameColumns{DataFrame}, ::Vector{String})

What should I do?

2

Welcome! Looks like you have just a tiny typo there, use collect(eachcol(df)) to make the vector of vectors. (Note the package seems to also allow input like XLSX.writetable("filename.xlsx", "sheet_1" => df1, "sheet_2" => df2).)

2 Likes
3

Another syntax you could use when you want to use space character in sheet name and column label metadata (instead of column name) :

 XLSX.writetable(
    "filename.xlsx", 
     var"df 1" = (eachcol(df1), labels(df1)),
     var"df 2" = (eachcol(df2), labels(df2))
 )
4

Thank you! This was helpful but now I received another error “Unsupported datatype Symbol for writing data to Excel file. Supported data types are Union{Missing, Bool, Float64, Int64, Date, DateTime, Time, String} or XLSX.CellValue.” Does this mean I need to change the data type of my data frame itself?

5

Use in-place symbol to string conversion:

[df[!, col] .= string.(df[!, col]) for df in [df1, df2] for col in names(df) if eltype(df[!, col]) == Symbol]; 

XLSX.writetable("filename.xlsx", "sheet_1" => df1, "sheet_2" => df2)
6

Still getting the data type message :frowning:

7

It’s always best to send a minimal working version of your troublesome code so that it can be copied, pasted, and run by others to help. In this case it looked like you have Symbol-typed columns in your DataFrames, and they need to be converted. Another clean way is by using TidierData package macros like df1 = @mutate(df1, x = string(x), y = string(y)) where x, y, and so on are names of the symbol columns. You can tabulate the element types of each column neatly with describe(df1, :eltype).

2 Likes
8

“Unsupported datatype Symbol for writing data to Excel file. Supported data types are Union{Missing, Bool, Float64, Int64, Date, DateTime, Time, String} or XLSX.CellValue.”

This issue is captured in #239.

I have a fix ready to go, but am trying to resolve some other issues too. It may take a little while, but not too long, I hope!

1 Like