Reshaping ID and STatus by CATegory

Specific Domains Data
1

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

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

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
2

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. So this is being tracked, as far as I can tell.

3

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.

#######  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

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)