Reshaping DataFrames

Reshaping DataFrames#

using DataFrames

Wide to long#

x = DataFrame(id=[1, 2, 3, 4], id2=[1, 1, 2, 2], M1=[11, 12, 13, 14], M2=[111, 112, 113, 114])
4×4 DataFrame
Rowidid2M1M2
Int64Int64Int64Int64
11111111
22112112
33213113
44214114

first pass measure variables and then id-variable

stack(x, [:M1, :M2], :id)
8×3 DataFrame
Rowidvariablevalue
Int64StringInt64
11M111
22M112
33M113
44M114
51M2111
62M2112
73M2113
84M2114

add view=true keyword argument to make a view; in that case columns of the resulting data frame share memory with columns of the source data frame, so the operation is potentially unsafe. Optionally, you can rename columns.

stack(x, ["M1", "M2"], "id", variable_name="key", value_name="observed")
8×3 DataFrame
Rowidkeyobserved
Int64StringInt64
11M111
22M112
33M113
44M114
51M2111
62M2112
73M2113
84M2114

if second argument is omitted in stack , all other columns are assumed to be the id-variables

stack(x, Not([:id, :id2]))
8×4 DataFrame
Rowidid2variablevalue
Int64Int64StringInt64
111M111
221M112
332M113
442M114
511M2111
621M2112
732M2113
842M2114

you can use index instead of symbol

stack(x, Not([1, 2]))
8×4 DataFrame
Rowidid2variablevalue
Int64Int64StringInt64
111M111
221M112
332M113
442M114
511M2111
621M2112
732M2113
842M2114

if stack is not passed any measure variables by default numeric variables are selected as measures

x = DataFrame(id=[1, 1, 1], id2=['a', 'b', 'c'], a1=rand(3), a2=rand(3))
stack(x)
6×4 DataFrame
Rowidid2variablevalue
Int64CharStringFloat64
11aa10.837301
21ba10.634367
31ca10.8232
41aa20.00373594
51ba20.0706931
61ca20.564692

here all columns are treated as measures:

stack(DataFrame(rand(3, 2), :auto))
6×2 DataFrame
Rowvariablevalue
StringFloat64
1x10.533911
2x10.546493
3x10.334054
4x20.61924
5x20.638288
6x20.77814

duplicates in key are silently accepted

df = DataFrame(rand(3, 2), :auto)
df.key = [1, 1, 1]
mdf = stack(df)
6×3 DataFrame
Rowkeyvariablevalue
Int64StringFloat64
11x10.721462
21x10.326511
31x10.525868
41x20.522718
51x20.870842
61x20.450046

Long to wide#

x = DataFrame(id=[1, 1, 1], id2=['a', 'b', 'c'], a1=rand(3), a2=rand(3))
3×4 DataFrame
Rowidid2a1a2
Int64CharFloat64Float64
11a0.5001920.0168908
21b0.4845260.514774
31c0.8853940.0592305
y = stack(x)
6×4 DataFrame
Rowidid2variablevalue
Int64CharStringFloat64
11aa10.500192
21ba10.484526
31ca10.885394
41aa20.0168908
51ba20.514774
61ca20.0592305

standard unstack with a specified key

unstack(y, :id2, :variable, :value)
3×3 DataFrame
Rowid2a1a2
CharFloat64?Float64?
1a0.5001920.0168908
2b0.4845260.514774
3c0.8853940.0592305

all other columns are treated as keys

unstack(y, :variable, :value)
3×4 DataFrame
Rowidid2a1a2
Int64CharFloat64?Float64?
11a0.5001920.0168908
21b0.4845260.514774
31c0.8853940.0592305

all columns other than named :variable and :value are treated as keys

unstack(y)
3×4 DataFrame
Rowidid2a1a2
Int64CharFloat64?Float64?
11a0.5001920.0168908
21b0.4845260.514774
31c0.8853940.0592305

you can rename the unstacked columns

unstack(y, renamecols=n -> string("unstacked_", n))
3×4 DataFrame
Rowidid2unstacked_a1unstacked_a2
Int64CharFloat64?Float64?
11a0.5001920.0168908
21b0.4845260.514774
31c0.8853940.0592305
df = stack(DataFrame(rand(3, 2), :auto))
6×2 DataFrame
Rowvariablevalue
StringFloat64
1x10.715445
2x10.982159
3x10.0145082
4x20.0191701
5x20.0387196
6x20.088657

unable to unstack when no key column is present

try
    unstack(df, :variable, :value)
catch e
    show(e)
end
ArgumentError("Duplicate entries in unstack at row 2 for key () and variable x1. Pass `combine` keyword argument to specify how they should be handled.")

unstack fills missing combinations with missing, but you can change this default with fill keyword argument.

df = DataFrame(key=[1, 1, 2], variable=["a", "b", "a"], value=1:3)
3×3 DataFrame
Rowkeyvariablevalue
Int64StringInt64
11a1
21b2
32a3
unstack(df, :variable, :value)
2×3 DataFrame
Rowkeyab
Int64Int64?Int64?
1112
223missing
unstack(df, :variable, :value, fill=0)
2×3 DataFrame
Rowkeyab
Int64Int64Int64
1112
2230

This notebook was generated using Literate.jl.