Htykuut

Hi I am kind of new to python, but I have a dataframe like this:

ID  NAME        NAME1      VALUE

1   Sarah       orange     5

1   Roger       apple      3

2   Amy         pineapple  2

2   Kia         pear       8

I want it like this:

ID NAME   NAME1     VALUE  NAME   NAME1  VALUE

1  Sarah  orange     5     Roger  apple   3

2  Amy    pineapple  2     Kia    pear    8

I am using pandas but not sure how I can achieve this and write to a csv. Any help would highly appreciated! Thanks!

Use set_index with cumcount for MultiIndex, reshape by unstack, sort MulitIndex by second level by sort_index and last flatten it by list comprehension with reset_index:

df = df.set_index(['ID',df.groupby('ID').cumcount()]).unstack().sort_index(axis=1, level=1)

#python 3.6+

df.columns = [f'{a}_{b}' for a, b in df.columns]

#python bellow 3.6

#df.columns = ['{}_{}'.format(a,b) for a, b in df.columns]

df = df.reset_index()

print (df)

   ID NAME_0    NAME1_0  VALUE_0 NAME_1 NAME1_1  VALUE_1

0   1  Sarah     orange        5  Roger   apple        3

1   2    Amy  pineapple        2    Kia    pear        8