# Reshape long

**URL:** <https://discourse.julialang.org/t/reshape-long/72257>\
**Category:** New to Julia\
**Tags:** question, dataframes, reshaping\
**Created:** [November 29, 2021, 8:14pm UTC](https://discourse.julialang.org/t/reshape-long/72257 "2021-11-29T20:14:54Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Matthew](https://avatars.discourse-cdn.com/v4/letter/m/ed655f/32.png) [@Matthew](https://discourse.julialang.org/u/Matthew)\
**Post date:** [November 29, 2021, 8:14pm UTC](https://discourse.julialang.org/t/reshape-long/72257/1 "2021-11-29T20:14:54Z")

</div>

Hi everyone,

I’m loving Julia but am having some difficulties reshaping my dataframe to a “long” format.

```julia
df = DataFrame(ID = 1:3, 
    sex = [0,1,0], 
    inc80 = [5500,2200,2000], 
    inc81 = incB,
    inc82 = incC, 
    ue80 = [0,1,0],
    ue81 = [1,0,0],
    ue82 = [0,0,1]); 

```

in Stata, I would use:  
`reshape long inc ue, i(id) j(year) `

and return

```julia
    Row │ ID year sex inc ue 
        │ Int64 Int64 Int64 Float64 Int64  
   ─────┼─────────────────────────────────────────────────
     1 │ 1	80	0	5000	0
     2 │ 1	81	0	5500	1
     3 │ 1	82	0	6000	0
     4 │ 2	80	1	2000	1
     5 │ 2	81	1	2200	0
     6 │ 2	82	1	3300	0
     7 │ 3	80	0	3000	0
     8 │ 3	81	0	2000	0
     9 │ 3	82	0	1000	1

```

I’ve come moderately close with:

```julia
sort(stack(df, Not([:ID,:sex])))
---
 Row │ ID sex variable value
     │ Int64 Int64 String Int64
─────┼───────────────────────────────
   1 │ 1 0 inc80 5500
   2 │ 1 0 inc81 6500
   3 │ 1 0 inc82 7500
   4 │ 1 0 ue80 0
   5 │ 1 0 ue81 1
   6 │ 1 0 ue82 0
   7 │ 2 1 inc80 2200
   8 │ 2 1 inc81 3200
   9 │ 2 1 inc82 4200
  10 │ 2 1 ue80 1
  11 │ 2 1 ue81 0
  12 │ 2 1 ue82 0
  13 │ 3 0 inc80 2000
  14 │ 3 0 inc81 3000
  15 │ 3 0 inc82 4000
  16 │ 3 0 ue80 0
  17 │ 3 0 ue81 0
  18 │ 3 0 ue82 1

```

It’s like I want to join each variable together by their respective year for each id.

I’m hoping to find an alternative to [Douglass](https://github.com/jmboehm/Douglass.jl) or even find a quick/dirty way of doing accomplishing this. TIA

---

<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:** [November 29, 2021, 8:25pm UTC](https://discourse.julialang.org/t/reshape-long/72257/2 "2021-11-29T20:25:23Z")

</div>

I think the problem is that you can’t stack multiple variables at once in DataFrames. There is an issue for it [here](https://github.com/JuliaData/DataFrames.jl/issues/2414). Until that’s resolved I think it’s not possible.

It also doesn’t do that automatic promotion to numbers, giving `80`, `81`, and `82`, which is a nice feature of Stata.

So yeah. best bet is probably to do two separate `stack`s and then join them together. Comment on the linked issue to keep track of it, please.

```julia
julia> df = DataFrame(ID = 1:3, 
           sex = [0,1,0], 
           inc80 = [5500,2200,2000], 
           inc81 = [100, 200, 300],
           inc82 = [500, 600, 700], 
           ue80 = [0,1,0],
           ue81 = [1,0,0],
           ue82 = [0,0,1]);

julia> df2 = select(df, :sex, r"inc*")
3×4 DataFrame
 Row │ sex inc80 inc81 inc82 
     │ Int64 Int64 Int64 Int64 
─────┼────────────────────────────
   1 │ 0 5500 100 500
   2 │ 1 2200 200 600
   3 │ 0 2000 300 700

julia> stack(df2, r"inc*")
9×3 DataFrame
 Row │ sex variable value 
     │ Int64 String Int64 
─────┼────────────────────────
   1 │ 0 inc80 5500
   2 │ 1 inc80 2200
   3 │ 0 inc80 2000
   4 │ 0 inc81 100
   5 │ 1 inc81 200
   6 │ 0 inc81 300
   7 │ 0 inc82 500
   8 │ 1 inc82 600
   9 │ 0 inc82 700

```

---

<div class="post-metadata">

**Author:** ![Matthew](https://avatars.discourse-cdn.com/v4/letter/m/ed655f/32.png) [@Matthew](https://discourse.julialang.org/u/Matthew)\
**Post date:** [November 29, 2021, 9:42pm UTC](https://discourse.julialang.org/t/reshape-long/72257/3 "2021-11-29T21:42:36Z")

</div>

Okay! Thanks a lot for the feedback and notes.

I created a very basic function to deal with the simple case provided above. Maybe I can make it more generic…

```julia
function myfun(df, identity, cons, features)
  df2 = select(df, identity, cons, Regex(features[1]));
  df3 = select(df, identity, Regex(features[2]));

  a = stack(df2,variable_name = features[1], Regex(features[1]));
  b = stack(df3, Regex(features[2]), variable_name = features[2], value_name = :val);

  @chain a begin
    groupby(:ID)
    @transform!(ind = 1:length(:sex))
  end;
    

  @chain b begin
    groupby(:ID)
    @transform!(ind = 1:length(:ue))
  end;
    
  res = outerjoin(a,b, on =[:ID,:ind])

  return(res)
  
end

```

---

<div class="post-metadata">

**Author:** ![jmboehm](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jmboehm/32/2263_2.png) [@jmboehm](https://discourse.julialang.org/u/jmboehm)\
**Post date:** [November 29, 2021, 11:06pm UTC](https://discourse.julialang.org/t/reshape-long/72257/4 "2021-11-29T23:06:09Z")

</div>

As an aside, you can expand the expression that Douglass gives you:

```julia
macroexpand(Main, :(Douglass.@d_str("reshape_long :inc :ue, i(:ID) j(:year)") ))

```

If you find a way that’s more performant (or if you come across other problems), I’d appreciate if you filed an issue on the repo. I’m sure there’s lots of low-hanging fruit.

---

<div class="post-metadata">

**Author:** ![Matthew](https://avatars.discourse-cdn.com/v4/letter/m/ed655f/32.png) [@Matthew](https://discourse.julialang.org/u/Matthew)\
**Post date:** [November 30, 2021, 3:15pm UTC](https://discourse.julialang.org/t/reshape-long/72257/5 "2021-11-30T15:15:59Z")

</div>

Posting an attempt of a more general function, though it’s much slower than Douglass:

```julia
function reshape_long(df, id, cons, stubs)

  stub_len = length(stubs);
  df_vector = [DataFrame() for _ in 1:stub_len];

  iter = 1
  for i in 1:length(df_vector)
    if iter ==1

      df_vector[i] = select(df, [id, cons], Regex(stubs[i]))
      iter += 1

    else
      df_vector[i] = select(df, [id], Regex(stubs[i]))

    end
  end

  df_vector2 = [DataFrame() for _ in 1:stub_len];

  for i in 1:length(df_vector2)
    df_vector2[i] = stack(df_vector[i], variable_name = stubs[i], Regex(stubs[i]), value_name = Symbol("value_$i") );

    df_vector2[i] = combine(groupby(df_vector2[i], id), sdf -> sort(sdf), id => eachindex => :index)
  end

  res = [DataFrame() for _ in 1:(stub_len-1)];

  iter = 1
  for i in 1:length(res)

    if iter ==1
      res[i] = outerjoin(df_vector2[i],df_vector2[i+1], on = [id,:index] )
      iter +=1
    else
      res[i] = outerjoin(res[1], df_vector2[i+1], on = [id, :index])
    end

  end

  d = res[end]
  symbs = Symbol(stubs[1])
  d[!, symbs] = getproperty.(match.(r"[0-9]+",d[!, symbs]), :match)

  rename!(d,symbs => :year)
  select!(d, Not([Symbol(stubs[i]) for i in 2:length(stubs) ]))
  rename!(d, [(Symbol("value_$i")=>Symbol(stubs[i])) for i in 1:length(stubs)])
  select!(d, Not(:index, ))

  return(d)

end

```

Example: `reshape_long(df, :ID, :sex, ["inc", "ue"])`