python更改数据框指定位置的数据,在python数据框中调换一列

I have the following:

Index ID speed _avg_val

245 1 10 30.5

246 1 2 25.1

I want to transpose the column ID and then have the following:

ID (Index) speed _avg_val speed_y _avg_val_y

1 10 30.5 2 25.1

I tried to use this method Transposing one column in python pandas with the simplest index possible but could not get this to work with multiple columns.

解决方案

I think you can first remove column Index, then add column ID to index, unstack and sort second level of MultiIndex in columns by sort_index:

print (df)

Index ID speed _avg_val

0 245 1 10 30.5

1 246 1 2 25.1

df = df.drop('Index', axis=1)

.set_index('ID', append=True)

.unstack(0)

.sort_index(axis=1, level=1)

#remove MultiIndex from columns

df.columns = ['_'.join((col[0], str(col[1]))) for col in df.columns]

print (df)

speed_0 _avg_val_0 speed_1 _avg_val_1

ID

1 10 30.5 2 25.1

If there is more values in ID column, need use cumcount:

print (df)

Index ID speed _avg_val

0 245 1 10 30.5

1 246 1 2 25.1

2 245 2 5 37.5

3 246 2 28 28.1

4 246 2 27 23.0

df = df.drop('Index', axis=1)

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

df = df.set_index(['ID', 'g']).unstack(fill_value=0).sort_index(axis=1, level=1)

df.columns = ['_'.join((col[0], str(col[1]))) for col in df.columns]

print (df)

speed_0 _avg_val_0 speed_1 _avg_val_1 speed_2 _avg_val_2

ID

1 10 30.5 2 25.1 0 0.0

2 5 37.5 28 28.1 27 23.0