# Converting from Wide to Long format

**URL:** <https://discourse.julialang.org/t/converting-from-wide-to-long-format/4811>\
**Category:** Data\
**Tags:** question\
**Created:** [July 12, 2017, 3:37pm UTC](https://discourse.julialang.org/t/converting-from-wide-to-long-format/4811 "2017-07-12T15:37:42Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Juan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan/32/7657_2.png) [@Juan](https://discourse.julialang.org/u/Juan)\
**Post date:** [July 12, 2017, 3:37pm UTC](https://discourse.julialang.org/t/converting-from-wide-to-long-format/4811/1 "2017-07-12T15:37:42Z")

</div>

Hello.

Some time ago I dealt with the problem of converting a large dataset from Wide format to Long format, sometimes called pivoting.  
I did that first steps in R but it’s too slow.

Let’s say we have this toy example in R:

> mydata ← data.frame(ID=1:5, ZA\_1=1:5,  
> ZA\_2=5:1,BB\_1=rep(3,5),BB\_2=rep(6,5),CC\_7=6:2)

> ID ZA\_1 ZA\_2 BB\_1 BB\_2 CC\_7  
> 1 1 5 3 6 6  
> 2 2 4 3 6 5  
> 3 3 3 3 6 4  
> 4 4 2 3 6 3  
> 5 5 1 3 6 2

```julia
ID ZA_1 ZA_2 BB_1 BB_2 CC_7
1 1 5 3 6 6
2 2 4 3 6 5
3 3 3 3 6 4
4 4 2 3 6 3
5 5 1 3 6 2

```

The result is going to be something like this:

```julia
ID measure ZA BB CC
 1 1 1 3 NA
 1 2 5 6 NA
 1 7 NA NA 6
 2 1 2 3 NA
 2 2 4 6 NA
 2 7 NA NA 5
 3 1 3 3 NA
 3 2 3 6 NA
 3 7 NA NA 4
14 1 4 3 NA
14 2 2 6 NA
14 7 NA NA 3
15 1 5 3 NA
15 2 1 6 NA
15 7 NA NA 2

```

There are some variables that will remain as is (here only ID) and some that will be transformed to long format (here all other variables, all ending with \_1, \_2 or \_7).

I need an automated way to do it because I need to apply the method to datasets with thousands of variables.

Uwe Block at stackoverflow wrote an efficient way to do it with R that doesn’t need as much memory the usual two-steps suggested method (first getting a very long format and then reshaping it to the shape we want):

> setDT(mydata)

> # add unique row number to join on later
> 
> # (leave `ID` col as placeholder for all other id.vars)
> 
> mydata[, rn := seq\_len(.N)]

> # define columns to be reshaped
> 
> measure\_cols ← stringr::str\_subset(names(mydata), “\_\d$”)

> # melt with only one id.vars column
> 
> molten ← melt(mydata, id.vars = “rn”, measure.vars = measure\_cols)

> # split column names of measure.vars
> 
> # Note that “variable” is reused to save memory
> 
> molten[, c(“variable”, “measure”) := tstrsplit(variable, “\_”)]

> # coerce names to factors in the same order as the columns appeared in mydata
> 
> molten[, variable := forcats::fct\_inorder(variable)]

> # remove columns no longer needed in mydata _before_ joining to save memory
> 
> mydata[, (measure\_cols) := NULL]

> # final dcast and right join
> 
> result ← mydata[dcast(molten, … ~ variable), on = “rn”]  
> result

> <https://stackoverflow.com/questions/41163500/transform-from-wide-to-long-without-sorting-columns>

What would be the best way to do it in Julia?

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [July 12, 2017, 3:43pm UTC](https://discourse.julialang.org/t/converting-from-wide-to-long-format/4811/2 "2017-07-12T15:43:05Z")

</div>

See `stack` and `melt` in [the DataFrames manual](http://juliastats.github.io/DataFrames.jl/stable/man/reshaping_and_pivoting/#Reshaping-and-Pivoting-Data-1).