# Reshaping ID and STatus by CATegory

**URL:** <https://discourse.julialang.org/t/reshaping-id-and-status-by-category/61716>\
**Category:** Data\
**Created:** [May 24, 2021, 10:34am UTC](https://discourse.julialang.org/t/reshaping-id-and-status-by-category/61716 "2021-05-24T10:34:51Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [May 24, 2021, 10:34am UTC](https://discourse.julialang.org/t/reshaping-id-and-status-by-category/61716/1 "2021-05-24T10:34:51Z")

</div>

I would like to see (some of) the different ways in which we can transform the following dataframe

```julia
df=DataFrame(CAT=["A","B","C","A","B","C","D","E","A","C","D"],
        ID=[1,1,1,2,2,2,2,2,3,3,3],
        ST=["q","q","q","d","d","d","d","d","t","t","t"])

```

in order to obtain the following shape

```julia
3×10 DataFrame
 Row │ A_ID A_ST B_ID B_ST C_ID C_ST D_ID D_ST E_ID E_ST
     │ Int64? String? Int64? String? Int64? String? Int64? String? Int64? String?
─────┼────────────────────────────────────────────────────────────────────────────────────────
   1 │ 1 q 1 q 1 q missing missing missing missing
   2 │ 2 d 2 d 2 d 2 d 2 d
   3 │ 3 t missing missing 3 t 3 t missing missing

```

---

<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:** [May 24, 2021, 12:36pm UTC](https://discourse.julialang.org/t/reshaping-id-and-status-by-category/61716/2 "2021-05-24T12:36:16Z")

</div>

> [@rocco\_sprmnt21](#):
>
> ```julia
> df=DataFrame(CAT=["A","B","C","A","B","C","D","E","A","C","D"],
> ID=[1,1,1,2,2,2,2,2,3,3,3],
> ST=["q","q","q","d","d","d","d","d","t","t","t"])
> 
> ```

We don’t currently have this implemented in DataFrames. Multi-column `unstack` calls are not yet supported. The best I can do is `unstack(df, "CAT", "ID")` and `unstack(df, "CAT", "ST")` and `join` the two together.

I _think_ there is an issue in DataFrames for this [here](https://github.com/JuliaData/DataFrames.jl/issues/2148). So this is being tracked, as far as I can tell.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [May 24, 2021, 7:55pm UTC](https://discourse.julialang.org/t/reshaping-id-and-status-by-category/61716/3 "2021-05-24T19:55:42Z")

</div>

Thanks for the suggestions and the reference to the issue where a similar topic is discussed but in a very general way.  
At the moment I was able to find these 2 solutions.

```julia
####### sol 1 #########

id=unstack(df,:CAT,:ID;renamecols=x->Symbol(x,:_ID))
st=unstack(df,:CAT,:ST;renamecols=x->Symbol(x,:_ST))
idst=hcat(id,st)
cols=sort(names(idst))[1:end-2]
select(idst,cols)

###### sol 2########

df1=combine(df,names(df), [:ID,:ST]=>ByRow(string)=>:ID_ST)
udf=unstack(df1,:CAT,:ID_ST)
colsin=names(udf, x->x ∉ ["ID","ST"])
colsout=collect.(zip([string.(cols,pf) for pf in ["_ID","_ST"]]...))
combine(udf,colsin.=>ByRow(x->ismissing(x) ? [missing,missing] : split(x,"")).=>colsout)

```

I believe there are others with the use of the groupby, transform, combine and other functions

```julia
df.idx=df.ID
grp=groupby(df,:CAT)
oj=outerjoin(grp...,on=:idx, makeunique=true)
noj=select(oj, r"ID|ST")
cat=unique(df.CAT)
newnames=sort(append!(string.(cat,"_ID"),string.(cat,"_ST")))
sort(rename(noj,newnames),:A_ID)

```