# Excel data to dataframes

**URL:** <https://discourse.julialang.org/t/excel-data-to-dataframes/65264>\
**Category:** Data\
**Tags:** data, dataframes, xlsx, excel\
**Created:** [July 25, 2021, 1:02pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264 "2021-07-25T13:02:39Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [July 25, 2021, 1:02pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/1 "2021-07-25T13:02:39Z")

</div>

I have some data in different columns of an Excel sheet. I want to read the different columns into separate dataframes. I know the the column name/number where the data for each dataframe starts but not number of rows of data they contain. One example of the type of data is shown:  
 ![Excel_example](https://global.discourse-cdn.com/julialang/original/3X/1/a/1a1e6abc74a2e22189716872cb9e4f5355f71313.png)  
I want two data frames in this case. The first one containing SECTOR as a header and containing all data in column A. The second one containing INDIVIDUAL as header with all the data in column C.

How can I do this using XLSX or otherwise?

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 25, 2021, 1:12pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/2 "2021-07-25T13:12:30Z")

</div>

If it’s something you only need to do once, you can use [ClipData.jl](https://github.com/pdeffebach/ClipData.jl) to copy and paste into a DataFrame easily.

If it’s something you need to do programmatically, the solution is XLSX.jl, but I don’t know much about how to work with that package. Hopefully someone else can chime in.

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [July 25, 2021, 1:45pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/3 "2021-07-25T13:45:20Z")

</div>

Thanks. I need to do it programmatically.

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [July 25, 2021, 3:51pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/4 "2021-07-25T15:51:23Z")

</div>

Could you check the following:

```julia
using XLSX, DataFrames

xf = XLSX.readxlsx(filename)
m = xf[1][:]

df = DataFrame(m[2:end,:],:auto)
rename!(df, Symbol.(m[1,:]))

df_list = []
for (h,c) in pairs(eachcol(df))
    if all(ismissing.(c))
        select!(df, Not(h))
    else
        dh = DataFrame(; h => c);
        dh[!,h] = convert.(eltype(dh[!,1]), df[:,h])
        dropmissing!(dh, h)
        push!(df_list, dh)
    end
end

```

It creates one single dataframe `df` and pushes one dataframe per non-empty column into a vector of dataframes:

> **Output:**
>
> ```julia
> julia> df
> 4×2 DataFrame
> Row │ SECTOR INDIVIDUAL 
> │ Any Any        
> ─────┼─────────────────────
> 1 │ IT ONE
> 2 │ FINANCE TWO
> 3 │ missing THREE
> 4 │ missing FOUR
> 
> julia> df_list
> 2-element Vector{Any}:
> 2×1 DataFrame
> Row │ SECTOR  
> │ String  
> ─────┼─────────
> 1 │ IT
> 2 │ FINANCE
> 4×1 DataFrame
> Row │ INDIVIDUAL 
> │ String     
> ─────┼────────────
> 1 │ ONE
> 2 │ TWO
> 3 │ THREE
> 4 │ FOUR
> 
> ```

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 25, 2021, 3:59pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/5 "2021-07-25T15:59:40Z")

</div>

> [@rafael.guerra](#):
>
> ```julia
> df = DataFrame(m[2:end,:])
> 
> ```

Beter to do

```julia
df = DataFrame(m[2:end,:], :auto)

```

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 25, 2021, 4:06pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/7 "2021-07-25T16:06:50Z")

</div>

What are you expecting? It works as I expected.

```julia
julia> df_list = [DataFrame(rand(2,2), :auto) for i in 1:2]
2-element Vector{DataFrame}:
 2×2 DataFrame
 Row │ x1 x2        
     │ Float64 Float64   
─────┼──────────────────────
   1 │ 0.504304 0.338425
   2 │ 0.0633497 0.0394208
 2×2 DataFrame
 Row │ x1 x2         
     │ Float64 Float64    
─────┼──────────────────────
   1 │ 0.899258 0.69782
   2 │ 0.899377 0.00734581

julia> push!(df_list, DataFrame(h = [1, 2, 3]))
3-element Vector{DataFrame}:
 2×2 DataFrame
 Row │ x1 x2        
     │ Float64 Float64   
─────┼──────────────────────
   1 │ 0.504304 0.338425
   2 │ 0.0633497 0.0394208
 2×2 DataFrame
 Row │ x1 x2         
     │ Float64 Float64    
─────┼──────────────────────
   1 │ 0.899258 0.69782
   2 │ 0.899377 0.00734581
 3×1 DataFrame
 Row │ h     
     │ Int64 
─────┼───────
   1 │ 1
   2 │ 2
   3 │ 3

```

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 25, 2021, 4:41pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/9 "2021-07-25T16:41:39Z")

</div>

> [@rafael.guerra](#):
>
> ```julia
> dh = DataFrame(h = c);
> rename!(dh, [h])
> 
> ```

I see. No, you want `DataFrame(; h => c)`. The `Pair` syntax lets you work with names programmatically.

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [July 25, 2021, 4:53pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/11 "2021-07-25T16:53:31Z")

</div>

Thank you for the code. Few questions:

1. Why do the column type is shown as Any when it is string?
2. This code results in same number of rows in all the dataframes, so it add data with `missing` if number of rows in one dataframe is lower than others. The data I have has different number of rows in each column. Is it possible to create this vector of dataframe with different number of rows, ie. if missing value can be excluded?

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 25, 2021, 4:53pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/12 "2021-07-25T16:53:44Z")

</div>

FWIW, I don’t think your answer is particularly compelling.

There are better functions in XLSX to work with this. @mjanun I will try and make an MWE with a solution I think is more elegant soon.

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [July 25, 2021, 4:54pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/13 "2021-07-25T16:54:55Z")

</div>

> [@pdeffebach](#):
>
> I will try and make an MWE with a solution I think is more elegant soon.

Thank you for looking in to it. I will wait for your example.

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [July 25, 2021, 5:27pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/14 "2021-07-25T17:27:16Z")

</div>

@mjanun, see code edited above to meet your requirement.

_ **NB:** supposedly code “not particularly compelling nor elegant”_

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 25, 2021, 5:29pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/15 "2021-07-25T17:29:57Z")

</div>

Here is something I think might be a bit more robust

```julia
julia> using XLSX, DataFrames;

julia> function num_trailing_missing(x)
           n = length(x)
           s = 0
           while true
               n == 0 && break
               !ismissing(x[n]) && break
               s += 1
               n -= 1
           end
           s
       end;

julia> mat = XLSX.readxlsx("testdata.xlsx")[1][:];

julia> inds = [1:1, 3:3]; # You know the columns but not rows

julia> dfs = map(inds) do is
           data = mat[2:end, is]
           nms = mat[1, is]
           df = DataFrame(data, string.(nms))
           min_num_trailing_missings = minimum(num_trailing_missing.(eachcol(df)))
           df = df[1:(end - min_num_trailing_missings), :]
           # narrow the types
           transform(df, names(df) .=> ByRow(identity); renamecols = false)
       end
2-element Vector{DataFrame}:
 2×1 DataFrame
 Row │ SECTOR  
     │ String  
─────┼─────────
   1 │ IT
   2 │ FINANCE
 4×1 DataFrame
 Row │ INDIVIDUAL 
     │ Int64      
─────┼────────────
   1 │ 1
   2 │ 2
   3 │ 3
   4 │ 4

```

Overall this was harder than I thought. I don’t think it’s too different from @rafael.guerra 's answer, actually. However

1. I take advantage of the fact that you know the starting and ending indices
2. I narrow the types of the output so they are no longer `Any`
3. I drop trailing `missing` rather than all `missing` values in the data frame.

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [July 25, 2021, 6:07pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/16 "2021-07-25T18:07:33Z")

</div>

Thank you. One last question, I see that `mat = XLSX.readxlsx("testdata.xlsx")[1][:]` refers to the first sheet in the spreadsheet. How can I specify the sheet name instead e.g. if I want to refer to the sheet called `Data`?

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 25, 2021, 6:42pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/17 "2021-07-25T18:42:18Z")

</div>

Take a look at the docs with `? readxlsx`. You just replace `1` with the name of the sheet, as a `String`.

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [July 25, 2021, 7:11pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/18 "2021-07-25T19:11:53Z")

</div>

> [@pdeffebach](#):
>
> There are better functions in XLSX to work with this

Are there in your response?

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [July 25, 2021, 7:14pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/19 "2021-07-25T19:14:59Z")

</div>

Thanks

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 25, 2021, 7:21pm UTC](https://discourse.julialang.org/t/excel-data-to-dataframes/65264/20 "2021-07-25T19:21:39Z")

</div>

No, when I wrote that I thought `readtable` could allow for subsets of columns, but I guess it only takes in the full sheet.