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.324363
21ba10.625031
31ca10.589295
41aa20.986638
51ba20.719286
61ca20.112445

here all columns are treated as measures:

stack(DataFrame(rand(3, 2), :auto))
6×2 DataFrame
Rowvariablevalue
StringFloat64
1x10.250625
2x10.139401
3x10.325646
4x20.478307
5x20.732672
6x20.281653

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.407144
21x10.737651
31x10.387624
41x20.699994
51x20.149162
61x20.186025

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.6641630.230088
21b0.5602230.326606
31c0.2958760.27396
y = stack(x)
6×4 DataFrame
Rowidid2variablevalue
Int64CharStringFloat64
11aa10.664163
21ba10.560223
31ca10.295876
41aa20.230088
51ba20.326606
61ca20.27396

standard unstack with a specified key

unstack(y, :id2, :variable, :value)
3×3 DataFrame
Rowid2a1a2
CharFloat64?Float64?
1a0.6641630.230088
2b0.5602230.326606
3c0.2958760.27396

all other columns are treated as keys

unstack(y, :variable, :value)
3×4 DataFrame
Rowidid2a1a2
Int64CharFloat64?Float64?
11a0.6641630.230088
21b0.5602230.326606
31c0.2958760.27396

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

unstack(y)
3×4 DataFrame
Rowidid2a1a2
Int64CharFloat64?Float64?
11a0.6641630.230088
21b0.5602230.326606
31c0.2958760.27396

you can rename the unstacked columns

unstack(y, renamecols=n -> string("unstacked_", n))
3×4 DataFrame
Rowidid2unstacked_a1unstacked_a2
Int64CharFloat64?Float64?
11a0.6641630.230088
21b0.5602230.326606
31c0.2958760.27396
df = stack(DataFrame(rand(3, 2), :auto))
6×2 DataFrame
Rowvariablevalue
StringFloat64
1x10.546573
2x10.272181
3x10.830432
4x20.81598
5x20.34569
6x20.378732

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.