# Read DataFrame from Excel file skipping one line with units and "cleaning-up" column names

**URL:** <https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528>\
**Category:** New to Julia\
**Tags:** dataframes, xlsx\
**Created:** [May 21, 2024, 7:42pm UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528 "2024-05-21T19:42:47Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![mjmat](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@mjmat](https://discourse.julialang.org/u/mjmat)\
**Post date:** [May 21, 2024, 7:42pm UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/1 "2024-05-21T19:42:47Z")

</div>

I suspect this has been asked before, but I can’t seem to find an answer to this (apparently) simple problem.  
Let’s assume I have an Excel file with m columns, and the following rows:

- 1st row: variable names
- 2nd row: variable units
- rows 3 to n: data (real numbers in my case)

The only way I found to do this is to read the whole data, and then delete the row with units, but it seems a bit inefficient, and also it results in all columns in the dataframe having the data type “Any” while they should be Float.

```julia
using DataFrames, XLSX
df = DataFrame(XLSX.readtable("myfile.xlsx", "mysheet"))
df = df[2:end,:] # Remove 1st row, which has unit strings 

```

Is there a “cleaner” way to do it? Or, at least, is there a simple way to “tell” the dataframe that all variables are real numbers?

Additional question, let’s assume one of the column names is “var A” (with a space). I know it’s acceptable in Julia, but I really prefer when DataFrame column names can be used as symbols. I would like the name “cleaned up”, i.e. changed to e.g. var\_A, so that I can do things like x = df.var\_A

Thanks!

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [May 21, 2024, 8:23pm UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/2 "2024-05-21T20:23:09Z")

</div>

Couldn’t see any beautiful way to do this, but going through the requirements, this is possible:

```julia
julia> using XLSX, DataFrames, Unitful

julia> dss = XLSX.readdata("TestTable.xlsx", "Sheet1", "A1:C4")
4×3 Matrix{Any}:
   "var A" "var B" "var C"
   "m" "m/s" "kg"
 10.2 11.2 1000.0
 50.0 60.5 2000.0

julia> DataFrame(
  uparse.(dss[2,:]) .* eachcol(dss[3:end,:]), 
  replace.(dss[1,:],Ref(' '=>'_')))
2×3 DataFrame
 Row │ var_A var_B var_C     
     │ Quantity… Quantity… Quantity… 
─────┼───────────────────────────────────
   1 │ 10.2 m 11.2 m s^-1 1000.0 kg
   2 │ 50.0 m 60.5 m s^-1 2000.0 kg

```

---

<div class="post-metadata">

**Author:** ![mjmat](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@mjmat](https://discourse.julialang.org/u/mjmat)\
**Post date:** [May 21, 2024, 8:53pm UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/3 "2024-05-21T20:53:34Z")

</div>

Thanks, using unitful does seem much better than discarding units as I proposed, but it’s also more complex - in my simple case all variables have the same units so discarding them seems OK.

For the “clean-up” part, I was hoping for something more general, similar to Matlab’s `matlab.lang.makeValidName()`

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [May 21, 2024, 9:30pm UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/4 "2024-05-21T21:30:13Z")

</div>

> [@mjmat](#):
>
> in my simple case all variables have the same units so discarding them seems OK.

```julia
using DataFrames, XLSX
df = DataFrame(XLSX.readtable("mh.xlsx","Foglio1";header=false,first_row=5,column_labels=vec(XLSX.readdata("mh.xlsx",1,"C3:E3"))))

#the excel table
#2# C	D	E
#3# v1	v2	v3
#4# m	kg	s
#5# 1	2	3
#6# 2	5	8
#7# 3	8	13
#8# 4	11	18
#9# 5	14	23
#10# 6	17	28

julia> df = DataFrame(XLSX.readtable("mh.xlsx","Foglio1";header=false,first_row=5,column_labels=vec(XLSX.readdata("mh.xlsx",1,"C3:E3"))))
6×3 DataFrame
 Row │ v1 v2 v3  
     │ Any Any Any
─────┼───────────────
   1 │ 1 2 3
   2 │ 2 5 8
   3 │ 3 8 13
   4 │ 4 11 18
   5 │ 5 14 23
   6 │ 6 17 28

```

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [May 21, 2024, 11:47pm UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/5 "2024-05-21T23:47:26Z")

</div>

Since all the data are floating points and of the same unit, you might also consider using a matrix. It is semantically appropriate, and might allow a plethora of matrix based analyses.

```julia
julia> Float64.(XLSX.readdata("TestTable.xlsx", "Sheet1", "A3:C4"))
2×3 Matrix{Float64}:
 10.2 11.2 1000.0
 50.0 60.5 2000.0

```

Note the range in the Sheet discards the names. The column names can be read separately.

---

<div class="post-metadata">

**Author:** ![mjmat](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@mjmat](https://discourse.julialang.org/u/mjmat)\
**Post date:** [May 22, 2024, 12:26am UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/6 "2024-05-22T00:26:47Z")

</div>

Thank you, calling `vec()` around the call to get column names is a nice trick.  
By experimenting with this, I came up with this to get the variable names in a first pass, not knowing the number of columns in advance:

```julia
colnames = collect(skipmissing(XLSX.readdata("filename.xlsx", "sheetname","A1:XFD1" ))) # XFD1 corresponds to column 16384, the maximum in Excel

```

An then I can skip lines to read only the data rows and use the collected names

```julia
df = DataFrame(XLSX.readtable("filename.xlsx", "sheetname", header=false, first_row=3, column_labels=colnames)) # assuming data starts on row 3

```

---

<div class="post-metadata">

**Author:** ![mjmat](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@mjmat](https://discourse.julialang.org/u/mjmat)\
**Post date:** [May 22, 2024, 12:32am UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/7 "2024-05-22T00:32:52Z")

</div>

Thanks, it’s true that matrices are useful. In my case there is also a time stamp, so a DataFrame is more convenient, in my first message I forgot to mention that.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [May 22, 2024, 7:24am UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/8 "2024-05-22T07:24:21Z")

</div>

some ideas to make the expressions valid for slightly more general situations

```julia
julia> data=XLSX.readdata("mh.xlsx",1,"A1:AAA100");

julia> fr,fc=findfirst(!ismissing, data).I
(3, 3)

#julia> lc=findlast(!ismissing, data[fr,:])
#5

julia> lc=findnext(ismissing, data[fr,:],fc)-1
5

julia> mh=XLSX.readdata.("mh.xlsx",1,Base.splat(XLSX.CellRef).(Base.product(fr:fr+1,fc:lc)))
2×3 Matrix{String}:
 "v1" "v2" "v3"
 "m" "kg" "s"

julia> colnames=join.(eachcol(mh),'[').*']'
3-element Vector{String}:
 "v1[m]"
 "v2[kg]"
 "v3[s]"

```

```julia
julia> df = DataFrame(XLSX.readtable("mh.xlsx","Foglio1";header=false,first_row=fr+2,column_labels=colnames))
6×3 DataFrame
 Row │ v1[m] v2[kg] v3[s] 
     │ Any Any Any
─────┼──────────────────────
   1 │ 1 2 3
   2 │ 2 5 8
   3 │ 3 8 13
   4 │ 4 11 18
   5 │ 5 14 23
   6 │ 6 17 28

```

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [May 22, 2024, 9:02am UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/9 "2024-05-22T09:02:36Z")

</div>

> [@mjmat](#):
>
> For the “clean-up” part, I was hoping for something more general, similar to Matlab’s `matlab.lang.makeValidName()`

`using CSV; rename!(df, CSV.normalizenames(names(df)))`

(would maybe be a good PR to XLSX.jl to have this there as well)

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [May 22, 2024, 9:08am UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/10 "2024-05-22T09:08:05Z")

</div>

I’ve made and issue [here](https://github.com/felipenoris/XLSX.jl/issues/260).

---

<div class="post-metadata">

**Author:** ![Nathan\_Boyer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nathan_boyer/32/14825_2.png) [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)\
**Post date:** [May 22, 2024, 2:57pm UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/11 "2024-05-22T14:57:04Z")

</div>

> [@mjmat](#):
>
> I would like the name “cleaned up”, i.e. changed to e.g. var\_A, so that I can do things like x = df.var\_A

Just so you know, you can still use dot syntax with string names:

```julia-repl
julia> x = df."Name with Spaces"
5-element Vector{Int64}:
 1
 2
 3
 4
 5

```

---

<div class="post-metadata">

**Author:** ![mjmat](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@mjmat](https://discourse.julialang.org/u/mjmat)\
**Post date:** [August 24, 2024, 6:14pm UTC](https://discourse.julialang.org/t/read-dataframe-from-excel-file-skipping-one-line-with-units-and-cleaning-up-column-names/114528/12 "2024-08-24T18:14:23Z")

</div>

It comes a bit late, but thanks, I didn’t know that and it’s really cool.