Sort With Pivot Table Pandas

I Have a data frame and I want to reorder it. But I could not get desired form of my table df = id_easy latitude longitude 1 45.0714 7.6187 1 45.073

Solution 1:

You can use GroupBy.cumcount for counter and then pivot, also for flatten MultiIndex use f-strings:

df['g'] = df.groupby('id_easy').cumcount()

df = (df.pivot(index='g', columns='id_easy', values=['longitude','latitude'])
        .sort_index(axis=1, level=1))
df.columns = [f'{a}_{b}'for a, b in df.columns]
print (df)
   latitude_1  longitude_1  latitude_2  longitude_2  latitude_3  longitude_3
g                                                                           
0     45.0714       7.6187     45.0946       7.6194     45.0745       7.6152
1     45.0739       7.6195         NaN          NaN     45.0833       7.6145

Or use set_index with unstack (working also with oldier pandas versions):

df['g'] = df.groupby('id_easy').cumcount()

df = df.set_index(['g','id_easy']).unstack().sort_index(axis=1, level=1)
df.columns = [f'{a}_{b}'for a, b in df.columns]
print (df)
   latitude_1  longitude_1  latitude_2  longitude_2  latitude_3  longitude_3
g                                                                           
0     45.0714       7.6187     45.0946       7.6194     45.0745       7.6152
1     45.0739       7.6195         NaN          NaN     45.0833       7.6145