transform dataframe according to index and labels

user_8074805 0
Viewed 56

I have a dataframe that looks something like this:

ID | TEXT | LABEL|

5  | blab | 0 
5  | blub | 0 
5  | gray | 0 
4  | rose | 1 
4  | work | 1 
4  | app  | 1 
3  | car  | 0 
3  | ink  | 0
1  | pink | 0

And I'm struggling to transform it to look like this:

ID | TEXT | TEXT| TEXT | LABEL|
5  | blab | blub| gray | 0 
4  | rose | work| app  | 1
3  | car  |     |      | 0 
1  | pink |     |      | 0

I have tried df.T and df.pivot() for now but I can't seem to get it right - any help is appreciated.

pandas transform dataframe python-3.x
2 Answers

Try

out = df.groupby(['ID','LABEL']).TEXT.agg(list).apply(pd.Series).reset_index()
Out[491]: 
   ID  LABEL     0     1     2
0   1      0  pink   NaN   NaN
1   3      0   car   ink   NaN
2   4      1  rose  work   app
3   5      0  blab  blub  gray
user_79645270

This is similar to pivoting with two columns. Basically, you need to enumerate the rows within the groups before pivot:

# maybe groupby on `ID` is enough, depending on your data
(df.assign(col=df.groupby(['ID','LABEL']).cumcount())
   .pivot_table(index=['ID','LABEL'], columns='col', 
                values='TEXT', aggfunc='first')
   .add_prefix('TEXT_')
   .reset_index() 
)

Or similarly with set_index().unstack():

(df.set_index(['ID','LABEL', df.groupby(['ID']).cumcount()])
   ['TEXT'].unstack()
   .add_prefix('TEXT_')
   .reset_index() 
)

Output:

col  ID  LABEL TEXT_0 TEXT_1 TEXT_2
0     1      0   pink    NaN    NaN
1     3      0    car    ink    NaN
2     4      1   rose   work    app
3     5      0   blab   blub   gray
user_42384080
Related