Why the column types of dataframes created by XLSX have always type any

user_14663014 0
Viewed 127

I have some variables in excel, I want to upload them to Julia and estimate a linear model. My variables consist of real numbers however each column in the DataFrame created by XLSX is of type Any.

I was wondering If there is a way for XLSX to assign the correct type to each column.

Thank you

julia xlsx dataframe
2 Answers

Just use infer_eltypes=true here is an example:

julia> DataFrame(XLSX.readtable("file.xlsx",1;infer_eltypes=true)...)
3×3 DataFrame
│ Row │ col1  │ col2    │ col3   │
│     │ Int64 │ Float64 │ String │
├─────┼───────┼─────────┼────────┤
│ 1   │ 1     │ 4.5     │ txt1   │
│ 2   │ 2     │ 5.5     │ txt2   │
│ 3   │ 3     │ 6.7     │ txt3   │
user_99577100

I do not know if it is possible in the reader but here is the way to do it in post-processing:

julia> df = DataFrame(a=Any[1,2], b=Any[1,missing], c=Any[1.0,2.0], d=Any[1.0,missing])
2×4 DataFrame
 Row │ a    b        c    d
     │ Any  Any      Any  Any
─────┼────────────────────────────
   1 │ 1    1        1.0  1.0
   2 │ 2    missing  2.0  missing

julia> transform!(df, names(df) .=> ByRow(identity), renamecols=false)
2×4 DataFrame
 Row │ a      b        c        d
     │ Int64  Int64?   Float64  Float64?
─────┼────────────────────────────────────
   1 │     1        1      1.0        1.0
   2 │     2  missing      2.0  missing
user_12695670
Related