Import XLSX data

dty · August 5, 2020, 2:57am

using XLSX
Cs=XLSX.readdata("E:\\Julia程序\\Cs.xlsx","Sheet1","A1:C24")

The above is my code for importing xlsx file (), which is the data type of array (any, 2) and cannot be directly operated with matrix. How can I import a file to make it a data type that can be operated with matrix? For example, float64etc.

111

in addition,I try to use the file converted to CSV format and then import it. Although a float file will be generated, there will be a warning

Cb=CSV.read("E:\\Julia程序\\Cb.csv")
Cs=CSV.read("E:\\Julia程序\\Cs.csv")
Pload=CSV.read("E:\\Julia程序\\Pload.csv")
PPV=CSV.read("E:\\Julia程序\\PPV.csv")
PWT=CSV.read("E:\\Julia程序\\PWT.csv")
PDL=CSV.read("E:\\Julia程序\\PDL.csv")
PCL=CSV.read("E:\\Julia程序\\PCL.csv")
PHL=CSV.read("E:\\Julia程序\\PHL.csv")

Cb=convert(Array,Cb)
Cs=convert(Array,Cs)
Pload=convert(Array,Pload)
PPV=convert(Array,PPV)
PWT=convert(Array,PWT)
PDL=convert(Array,PDL)
PDL=convert(Array,PDL)
PDL=convert(Array,PDL)
PCL=convert(Array,PCL)
PHL=convert(Array,PHL)

┌ Warning: `CSV.read(input; kw...)` is deprecated in favor of `DataFrame!(CSV.File(input; kw...))`
└ @ CSV C:\Users\61940\.juliapro\JuliaPro_v1.4.2-1\packages\CSV\hZ1pV\src\CSV.jl:40
┌ Warning: `CSV.read(input; kw...)` is deprecated in favor of `DataFrame!(CSV.File(input; kw...))`
└ @ CSV C:\Users\61940\.juliapro\JuliaPro_v1.4.2-1\packages\CSV\hZ1pV\src\CSV.jl:40
┌ Warning: `CSV.read(input; kw...)` is deprecated in favor of `DataFrame!(CSV.File(input; kw...))`
└ @ CSV C:\Users\61940\.juliapro\JuliaPro_v1.4.2-1\packages\CSV\hZ1pV\src\CSV.jl:40
┌ Warning: `CSV.read(input; kw...)` is deprecated in favor of `DataFrame!(CSV.File(input; kw...))`
└ @ CSV C:\Users\61940\.juliapro\JuliaPro_v1.4.2-1\packages\CSV\hZ1pV\src\CSV.jl:40
┌ Warning: `CSV.read(input; kw...)` is deprecated in favor of `DataFrame!(CSV.File(input; kw...))`
└ @ CSV C:\Users\61940\.juliapro\JuliaPro_v1.4.2-1\packages\CSV\hZ1pV\src\CSV.jl:40
┌ Warning: `CSV.read(input; kw...)` is deprecated in favor of `DataFrame!(CSV.File(input; kw...))`
└ @ CSV C:\Users\61940\.juliapro\JuliaPro_v1.4.2-1\packages\CSV\hZ1pV\src\CSV.jl:40
┌ Warning: `CSV.read(input; kw...)` is deprecated in favor of `DataFrame!(CSV.File(input; kw...))`
└ @ CSV C:\Users\61940\.juliapro\JuliaPro_v1.4.2-1\packages\CSV\hZ1pV\src\CSV.jl:40
┌ Warning: `CSV.read(input; kw...)` is deprecated in favor of `DataFrame!(CSV.File(input; kw...))`
└ @ CSV C:\Users\61940\.juliapro\JuliaPro_v1.4.2-1\packages\CSV\hZ1pV\src\CSV.jl:40

felipenoris · August 5, 2020, 4:57am

Maybe convert(Array{Float64,2}, Cs).

jling · August 5, 2020, 7:24am

the warning is telling you to use the new syntax (this is what ‘deprecation’ means).

dty · August 5, 2020, 7:45am

yes!That’s right. thanks!

dty · August 5, 2020, 8:07am

PHL=DataFrame!(CSV.File("E:\\Julia程序\\PHL.csv"))

sorry，I didn’t really understand the meaning of it. I tried to modify it, but I still reported an error

ERROR: LoadError: UndefVarError: DataFrame! not defined
Stacktrace:
 [1] top-level scope at untitled-00d5839736e1528ded8439f9249e3ead:6
in expression starting at untitled-00d5839736e1528ded8439f9249e3ead:6

nilshg · August 5, 2020, 8:11am

You need to be using DataFrames explicitly if you want to call the DataFrame constructor, i.e.

using CSV, DataFrames

DataFrame(CSV.File("path_to_file.csv"))

dty · August 5, 2020, 8:16am

I “add DataFrame”, so I failed without “s”
Thank you. It’s ok now, and it’s converted to float 64！
The format is as follows

PHL=DataFrame!(CSV.File("E:\\Julia程序\\PHL.csv"))
PHL=convert(Array{Float64,2}, PHL)

nilshg · August 5, 2020, 8:19am

You can abbreviate the second line to Matrix(PHL). Also note that convention in Julia is to use lowercase identifiers for variables, and uppercase identifiers for types (like DataFrame).

dty · August 5, 2020, 8:32am

First of all, thank you for your correction!
After I use matrix (PHL), the generated data frame type cannot be used for matrix operation or multiplication and division with integers. It seems that I need float type.

nilshg · August 5, 2020, 8:41am

What do you mean by “data frame type” after you used Matrix? If your csv is as shown in your first post (i.e. a bunch of floating point numbers like 0.43 0.82, 1.02) then you’ll get an Array{Float64, 2} back from doing Matrix{PHL}.

This array can then be used for the usual mathematical operations in Julia:

julia> using DataFrames

julia> df = DataFrame(rand([0.43, 0.82, 1.02], 5, 5));

julia> df_matrix = Matrix(df) 
5×5 Array{Float64,2}:        
0.43  0.43  0.82  0.82  0.43                               
0.82  0.82  1.02  1.02  1.02                                       
1.02  0.82  1.02  1.02  0.82 
1.02  0.43  0.82  1.02  0.43 
0.43  1.02  0.43  0.82  1.02

julia> df_matrix .* 2     
5×5 Array{Float64,2}:  
0.86  0.86  1.64  1.64  0.86
1.64  1.64  2.04  2.04  2.04 
2.04  1.64  2.04  2.04  1.64 
2.04  0.86  1.64  2.04  0.86 
0.86  2.04  0.86  1.64  2.04

dty · August 5, 2020, 8:51am

using XLSX
using CSV ,DataFrames

Cs=XLSX.readdata("E:\\Julia程序\\Cs.xlsx","Sheet1","A1:C24")
Cs=convert(Array{Float64,2}, Cs)

PHL=DataFrame(CSV.File("E:\\Julia程序\\PHL.csv"))
Matrix(PHL)

This is my code. After I use matrix (PHL), it is still a dataframe file. It seems a little strange

dty · August 5, 2020, 8:53am

I seem to have forgotten to define its name, which leads to this. I’m sorry！

nilshg · August 5, 2020, 8:53am

Matrix() does not operate in place. Again as a convention, functions that do operate in place or change their arguments are denoted by an ! at the end (this is just a naming convention, so you couldn’t just add a ! to some function to make it in-place).

You need to do

PHL = Matrix(PHL)

as in my example above.

I would encourage you to read the Julia documentation to familiarise yourself with the basics of the language.

dty · August 5, 2020, 8:55am

I forgot, this is my problem
Thank you very much for your help!