Split rows with same ID into different columns python

Question

I want to have a dataframe with repeated values with the same id number. But i want to split the repeated rows into colunms.

data = [[10450015,4.4],[16690019  4.1],[16690019,4.0],[16510069  3.7]]
df = pd.DataFrame(data, columns = ['id', 'k'])  
print(df)

The resulting dataframe would have n_k (n= repated values of id rows). The repeated id gets a individual colunm and when it does not have repeated id, it gets a 0 in the new colunm.

data_merged = {'id':[10450015,16690019,16510069], '1_k':[4.4,4.1,3.7], '2_k'[0,4.0,0]}
print(data_merged)

Answer 1

Try assiging the column idx ref, using DataFrame.assign and groupby.cumcount then DataFrame.pivot_table. Finally use a list comprehension to sort column names:

df_new = (df.assign(col=df.groupby('id').cumcount().add(1))
          .pivot_table(index='id', columns='col', values='k', fill_value=0))

df_new.columns = [f"{x}_k" for x in df_new.columns]

print(df_new)

          1_k  2_k
id                
10450015  4.4    0
16510069  3.7    0
16690019  4.1    4