I have a DataFrame with columns like open, high, low, close, and vol for multiple tickers, e.g. 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…

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, colke…

Is this what you’re looking for? 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 r1(g)=rename(DataFrame(g), (names(g).=>g[1,:ticker]*"_" .*names(g))...) reduce(…

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.

[image] askvorts: 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…

With the df1 in the OP. The following: unstack( select( stack(df1, Not([:date_time, :ticker])), :date_time, [:ticker, :variable] => ByRow((x,y)->"$(x)_$(y)") => :col, :value), :date_time, :col, :value ) gives: 4×11 DataFrame Row │ date_time AAPL_open IBM_open …

And unstack here would work like this: 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])

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.

[image] 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

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

General Usage

rocco_sprmnt21 December 4, 2024, 10:14am 9

a similar approach (exactly reversed, in fact) for another problem

Topic		Replies	Views
New DataFrame whose columns are values of a column and grouped by another column General Usage dataframes	2	352	May 14, 2021
Pivot a dataframe to wide format with values in multiple columns New to Julia question , dataframes	15	1958	September 10, 2020
How to reshape wide to long? General Usage	13	1084	June 24, 2022
Unstack using row numbers Data question , dataframes	5	788	August 16, 2021
Best practice to unstack a dataframe with a lot of columnes? General Usage question , dataframes	5	1308	March 11, 2020

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

Related topics