# How (best) to transform a huge DataFrame into wide-format

**URL:** <https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [December 3, 2024, 2:14pm UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415 "2024-12-03T14:14:34Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![askvorts](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/askvorts/32/7120_2.png) [@askvorts](https://discourse.julialang.org/u/askvorts)\
**Post date:** [December 3, 2024, 2:14pm UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/1 "2024-12-03T14:14:34Z")

</div>

I have a DataFrame with columns like open, high, low, close, and vol for multiple tickers, e.g.

```julia
df1 = DataFrame(
date_time = repeat(["2024-12-02T14:30:00", "2024-12-02T16:00:00","2024-12-02T17:30:00", "2024-12-02T19:00:00"], outer=2),
ticker = repeat(["AAPL", "IBM"], inner=4),
open = rand(8),
high = rand(8),
low = rand(8),
close = rand(8),
vol = rand(8)*10^5
)

 Row │ date ticker open high low close vol      
     │ String String Float64 Float64 Float64 Float64 Float64  
─────┼───────────────────────────────────────────────────────────────────────────────────
   1 │ 2024-12-02T14:30:00 AAPL 0.689655 0.837242 0.636093 0.0616286 8079.08
   2 │ 2024-12-02T16:00:00 AAPL 0.70835 0.120729 0.922828 0.0278368 98275.5
   3 │ 2024-12-02T17:30:00 AAPL 0.235651 0.170414 0.854302 0.805576 26022.2
   4 │ 2024-12-02T19:00:00 AAPL 0.154155 0.977993 0.744772 0.563214 35684.5
   5 │ 2024-12-02T14:30:00 IBM 0.294663 0.70168 0.213208 0.00785374 61187.7
   6 │ 2024-12-02T16:00:00 IBM 0.740926 0.0221332 0.320625 0.102369 13345.5
   7 │ 2024-12-02T17:30:00 IBM 0.0352852 0.0763372 0.385503 0.998992 53140.9
   8 │ 2024-12-02T19:00:00 IBM 0.397259 0.606574 0.883001 0.180595 94851.4

```

Would like to transform the DataFrame (df1) into wide-format where these metrics for each timestamp are split by :ticker. In pseudo code:

```julia
df2 columns = [:date-time :AAPL_open, :AAPL_high, ... :IBM_close, :IBM_vol]

```

Which would be the best way (the real DataFrame is huge) to achieve this? Than you!

---

<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:** [December 3, 2024, 2:42pm UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/2 "2024-12-03T14:42:59Z")

</div>

```julia
help?> unstack
search: unstack stack unlock unmark instances abstract issticky

  unstack(df::AbstractDataFrame, rowkeys, colkey, value;
          renamecols::Function=identity, allowmissing::Bool=false,
          combine=only, fill=missing, threads::Bool=true)
  unstack(df::AbstractDataFrame, colkey, value;
          renamecols::Function=identity, allowmissing::Bool=false,
          combine=only, fill=missing, threads::Bool=true)
  unstack(df::AbstractDataFrame;
          renamecols::Function=identity, allowmissing::Bool=false,
          combine=only, fill=missing, threads::Bool=true)

  Unstack data frame df, i.e. convert it from long to wide format.

(...)

  Examples
  ≡≡≡≡≡≡≡≡

  julia> wide = DataFrame(id=1:6,
                          a=repeat(1:3, inner=2),
                          b=repeat(1.0:2.0, inner=3),
                          c=repeat(1.0:1.0, inner=6),
                          d=repeat(1.0:3.0, inner=2))
  6×5 DataFrame
   Row │ id a b c d
       │ Int64 Int64 Float64 Float64 Float64
  ─────┼─────────────────────────────────────────
     1 │ 1 1 1.0 1.0 1.0
     2 │ 2 1 1.0 1.0 1.0
     3 │ 3 2 1.0 1.0 2.0
     4 │ 4 2 2.0 1.0 2.0
     5 │ 5 3 2.0 1.0 3.0
     6 │ 6 3 2.0 1.0 3.0

  julia> long = stack(wide)
  18×4 DataFrame
   Row │ id a variable value
       │ Int64 Int64 String Float64
  ─────┼─────────────────────────────────
     1 │ 1 1 b 1.0
     2 │ 2 1 b 1.0
     3 │ 3 2 b 1.0
     4 │ 4 2 b 2.0
     5 │ 5 3 b 2.0
     6 │ 6 3 b 2.0
     7 │ 1 1 c 1.0
     8 │ 2 1 c 1.0
    ⋮ │ ⋮ ⋮ ⋮ ⋮
    12 │ 6 3 c 1.0
    13 │ 1 1 d 1.0
    14 │ 2 1 d 1.0
    15 │ 3 2 d 2.0
    16 │ 4 2 d 2.0
    17 │ 5 3 d 3.0
    18 │ 6 3 d 3.0
                           3 rows omitted

  julia> unstack(long)
  6×5 DataFrame
   Row │ id a b c d
       │ Int64 Int64 Float64? Float64? Float64?
  ─────┼────────────────────────────────────────────
     1 │ 1 1 1.0 1.0 1.0
     2 │ 2 1 1.0 1.0 1.0
     3 │ 3 2 1.0 1.0 2.0
     4 │ 4 2 2.0 1.0 2.0
     5 │ 5 3 2.0 1.0 3.0
     6 │ 6 3 2.0 1.0 3.0

```

---

<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:** [December 3, 2024, 3:58pm UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/3 "2024-12-03T15:58:29Z")

</div>

Is this what you’re looking for?

```julia
dfg=groupby(df1,:ticker)
r(g)=rename(DataFrame(g), (names(g[:,Not(:ticker)]).=>g[1,:ticker]*"_" .*names(g[:,Not(:ticker)]))...)
reduce((h,g)->hcat(r(h),r(g), makeunique=true), dfg )

```

or

```julia
r1(g)=rename(DataFrame(g), (names(g).=>g[1,:ticker]*"_" .*names(g))...)
reduce((h,g)->hcat(r1(h),r1(g)), dfg )

```

---

<div class="post-metadata">

**Author:** ![askvorts](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/askvorts/32/7120_2.png) [@askvorts](https://discourse.julialang.org/u/askvorts)\
**Post date:** [December 3, 2024, 4:00pm UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/4 "2024-12-03T16:00:53Z")

</div>

Thank you for your reply. I am aware of the unstack() method, my question was more about how to apply it to achieve my desired objective.

---

<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:** [December 3, 2024, 4:12pm UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/5 "2024-12-03T16:12:55Z")

</div>

> [@askvorts](#):
>
> ```julia
> df1 = DataFrame(
> date_time = repeat(["2024-12-02T14:30:00", "2024-12-02T16:00:00","2024-12-02T17:30:00", "2024-12-02T19:00:00"], outer=2),
> ticker = repeat(["AAPL", "IBM"], inner=4),
> open = rand(8),
> high = rand(8),
> low = rand(8),
> close = rand(8),
> vol = rand(8)*10^5
> )
> 
> ```

Unstack doesn’t work for multiple columns. So you will need something with a loop, unfortunately.

```julia
julia> reduce(gd; init = DataFrame(date_time = unique(df1.date_time))) do df_l, df_r
           t_r = first(df_r.ticker)
           df_r = select(df_r, Not(:ticker))
           df_r = rename(df_r) do n
               n == "date_time" && return n
               n * "_" * t_r
           end
           leftjoin(df_l, df_r; on = :date_time)
       end
4×11 DataFrame
 Row │ date_time open_AAPL high_AAPL low_AAPL close_AAPL vol_AAPL open_IBM high_IBM low_IBM close_IBM vol_IBM  
     │ String Float64? Float64? Float64? Float64? Float64? Float64? Float64? Float64? Float64? Float64? 
─────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ 2024-12-02T14:30:00 0.503531 0.952858 0.542719 0.643992 82669.2 0.814595 0.872334 0.3809 0.227004 66136.0
   2 │ 2024-12-02T16:00:00 0.943105 0.858138 0.715906 0.484657 91041.3 0.45135 0.522677 0.104943 0.315722 54555.6
   3 │ 2024-12-02T17:30:00 0.190797 0.0412094 0.347572 0.539557 85581.2 0.0269947 0.250749 0.328845 0.482173 22247.9
   4 │ 2024-12-02T19:00:00 0.706489 0.0853058 0.935489 0.470801 74114.2 0.238969 0.921508 0.187803 0.299688 54766.8

```

---

<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:** [December 3, 2024, 5:06pm UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/6 "2024-12-03T17:06:11Z")

</div>

With the `df1` in the OP. The following:

```julia
unstack(
  select(
    stack(df1, Not([:date_time, :ticker])), 
    :date_time, 
    [:ticker, :variable] => ByRow((x,y)->"$(x)_$(y)") => :col, 
    :value),
  :date_time, :col, :value
)

```

gives:

```julia
4×11 DataFrame
 Row │ date_time AAPL_open IBM_open AAPL_high IBM_high AAPL_low IBM_low AAPL_close IBM_close ⋯
     │ String Float64? Float64? Float64? Float64? Float64? Float64? Float64? Float64? ⋯
─────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ 2024-12-02T14:30:00 0.941412 0.207643 0.879879 0.826008 0.775218 0.879673 0.486391 0.293913 ⋯
   2 │ 2024-12-02T16:00:00 0.952736 0.763155 0.542305 0.937118 0.104578 0.26724 0.992486 0.920629
   3 │ 2024-12-02T17:30:00 0.692002 0.959982 0.408699 0.587018 0.777649 0.207722 0.67562 0.57377
   4 │ 2024-12-02T19:00:00 0.639045 0.913077 0.286222 0.274339 0.339372 0.637027 0.564277 0.371248

```

Combining `stack`, `unstack` and `select` can really do most of the pivoting wizardry from Excel and friends.

---

<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:** [December 3, 2024, 5:09pm UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/7 "2024-12-03T17:09:16Z")

</div>

And `unstack` here would work like this:

```julia
reduce((x,y) -> leftjoin(x, y, on = :date_time), 
    unstack(df1[:, ["date_time", "ticker", y]], "ticker", y, 
          renamecols = x -> x*"_"*y) for y ∈ names(df1)[3:end])

```

---

<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:** [December 3, 2024, 5:43pm UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/8 "2024-12-03T17:43:34Z")

</div>

Ugh this is the “gather-spread” pattern that dplyr recommends for this kind of thing. Hopefully we can make a better API for this soon.

---

<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:** [December 4, 2024, 10:14am UTC](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/9 "2024-12-04T10:14:13Z")

</div>

> [@Dan](#):
>
> Combining `stack`, `unstack` and `select` can really do most of the pivoting wizardry from Excel and friends.

a similar approach (exactly reversed, in fact) [for another problem](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431/3)