# Combining lots of DataFrames, best approach?

**URL:** <https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466>\
**Category:** Performance\
**Tags:** question, dataframes\
**Created:** [March 25, 2022, 3:14pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466 "2022-03-25T15:14:11Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [March 25, 2022, 3:14pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/1 "2022-03-25T15:14:11Z")

</div>

I am wondering if there are better ways to append lots of DataFrames together in these two scenarios.

```julia
# dts is a vector of lots of dataframes (>1000)
# here is a MWE version of it
dts = [DataFrame(pid=fill(string(rand(UInt16)),N),id=collect(1:N),pass=rand(("yes","no"),N)) for N in rand(10:50,10)]

# SCENARIO 1
# in this first case, all dataframes have the same schema 
dfmap = reduce((a,b)->vcat(a,b), filter(!isnothing,dts))

# SCENARIO 2
# in this second case, I call a function makenew which returns a transformed dataframe
# output from makenew will not generally have the same schema, so I use the :union option to keep all cols
# if it helps, all columns in dfnew will be Union{Missing, String}
function makenew(df)
    seqdf = unstack(df,"pid","id","pass")
    dropmissing!(seqdf,"pid")
    return seqdf
end
dfnew = mapreduce(makenew,(a,b)->vcat(a,b; cols= :union), filter(!isnothing,dts))

```

It is panel data and each element in `dts` has many rows, so that both `dfmap` and `dfnew` will potentially be very large.

---

<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:** [March 25, 2022, 3:50pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/2 "2022-03-25T15:50:09Z")

</div>

> [@tbeason](#):
>
> `reduce((a,b)->vcat(a,b), filter(!isnothing,dts))`

How is this different from `reduce(vcat, dts)`?

---

<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:** [March 25, 2022, 5:05pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/3 "2022-03-25T17:05:52Z")

</div>

```julia
DataFrame(reduce(append!, Tables.rowtable.(dts)))

```

for

```julia
dts = [DataFrame(pid=fill(string(rand(UInt16)),N),id=collect(1:N),
                pass=rand(("yes","no"),N)) for N in rand(10:50,1000)]

```

```julia
@btime dfmap = reduce((a,b)->vcat(a,b), filter(!isnothing,dts))
  92.453 ms (107418 allocations: 352.46 MiB)

@btime DataFrame(reduce(append!, Tables.rowtable.(dts)))
  1.539 ms (9533 allocations: 2.96 MiB)

```

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [March 25, 2022, 5:06pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/4 "2022-03-25T17:06:10Z")

</div>

as @nilshg commented a recommented pattern is:

```julia
reduce(vcat, your_data_frames)

```

where `your_data_frames` should be already preprocessed (e.g. filtered or mapped). If you use this pattern `reduce` will pre-allocate appropriate data structures.

Note that you can pass to `reduce` the kwargs if you need e.g. to make a union of columns if data frames have different column sets.

---

<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:** [March 25, 2022, 5:13pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/5 "2022-03-25T17:13:39Z")

</div>

could you show how to rewrite using reduce kwargs this expression, please?

```julia
reduce((a,b)->vcat(a,b; cols= :union), dts)

```

---

<div class="post-metadata">

**Author:** ![haberdashPI](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/haberdashpi/32/26337_2.png) [@haberdashPI](https://discourse.julialang.org/u/haberdashPI)\
**Post date:** [March 25, 2022, 5:19pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/6 "2022-03-25T17:19:19Z")

</div>

> [@bkamins](#):
>
> Note that you can pass to `reduce` the kwargs if you need e.g. to make a union of columns if data frames have different column sets.

Is this mentioned somewhere in the DataFrame manual? I know about the optimization for `reduce` but I didn’t realize there was a method for DataFrames that could handle keywords. (It’s in the docstring for `reduce`, I realize, but I don’t see it mentioned anywhere else).

@rocco_sprmnt21: you would write this as

```julia
reduce(vcat, your_data_frames, cols=:union)

```

---

<div class="post-metadata">

**Author:** ![haberdashPI](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/haberdashpi/32/26337_2.png) [@haberdashPI](https://discourse.julialang.org/u/haberdashPI)\
**Post date:** [March 25, 2022, 5:23pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/7 "2022-03-25T17:23:38Z")

</div>

Also it appears that there is no method to handle these kwargs for `mapreduce`, just `reduce`: [https://github.com/JuliaData/DataFrames.jl/issues/3028](https://github.com/JuliaData/DataFrames.jl/issues/3028)

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [March 25, 2022, 5:24pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/8 "2022-03-25T17:24:01Z")

</div>

> [@haberdashPI](#):
>
> Is this mentioned somewhere in the DataFrame manual?

Yes, just get the help on `reduce`:

```julia
  reduce(::typeof(vcat),
         dfs::Union{AbstractVector{<:AbstractDataFrame},
                    Tuple{AbstractDataFrame, Vararg{AbstractDataFrame}}};
         cols::Union{Symbol, AbstractVector{Symbol},
                     AbstractVector{<:AbstractString}}=:setequal,
         source::Union{Nothing, Symbol, AbstractString,
                       Pair{<:Union{Symbol, AbstractString}, <:AbstractVector}}=nothing)

  Efficiently reduce the given vector or tuple of AbstractDataFrames with vcat.

  The column order, names, and types of the resulting DataFrame, and the behavior of cols and source keyword arguments follow the rules specified for vcat of
  AbstractDataFrames.

```

---

<div class="post-metadata">

**Author:** ![haberdashPI](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/haberdashpi/32/26337_2.png) [@haberdashPI](https://discourse.julialang.org/u/haberdashPI)\
**Post date:** [March 25, 2022, 5:26pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/9 "2022-03-25T17:26:48Z")

</div>

Sorry, perhaps my comment wasn’t clear. I see that it’s in the docstring; I was wondering if there is a mention of it in the “manual” part of the documentation. Looks like there is no “concatenation” section though.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [March 25, 2022, 5:27pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/10 "2022-03-25T17:27:59Z")

</div>

Ah - OK. Can you please open an issue, or even better make a PR (if you know what kind of content would be useful for you from a user’s perspective). Thank you!

---

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [March 25, 2022, 6:05pm UTC](https://discourse.julialang.org/t/combining-lots-of-dataframes-best-approach/78466/11 "2022-03-25T18:05:38Z")

</div>

@nilshg Yes I guess I just copied and pasted the anonymous version from the second case and deleted the keyword. Plenty of performance lost because of that in itself.

Given that `reduce(vcat,Vector{DataFrame};kwargs)` seems pretty performant, I’m going to try breaking the `mapreduce` into a `ThreadsX.map` and a `reduce` to see if that helps the second scenario.