Add a new column for each duplicate variable

Question

I am trying to transform the following dataframe

df = pd.DataFrame ({'ID' : [125,125,130,130,130],
                     'X' : [2,1,2,1,3]})

df

ID     X
125    2
125    1
130    2
130    1
130    3

With new columns with the following criteria (this df is an example of my hole dataset just to illustrate my problem): Add a new column for each duplicate id containing the value of X

df

ID     X  X_1  X_2
125    2   1   NaN
130    2   1    3

Maybe try this one ?https://stackoverflow.com/questions/48338381/transposing-a-column-in-a-pandas-dataframe-while-keeping-other-column-intact-wit — BENY, May 09 '18 at 20:48

score 2 · Accepted Answer · answered May 09 '18 at 20:50

In [30]: (df.assign(col=df.groupby('ID').cumcount())
            .pivot_table(index='ID', columns='col', values='X', fill_value=0)
            .add_prefix('X_')
            .reset_index()
            .rename_axis(None,1))
Out[30]:
      ID  X_0  X_1  X_2
0    125    2    1    0
1    130    2    1    3

Add a new column for each duplicate variable

1 Answers1