Pandas Partial Transpose

I want to reformat a dataframe by transeposing some columns with fixing other columns. original data : ID subID values_A -- ----- -------- A aaa 10 B baa

Solution 1:

Use cumcount for counter, create MultiIndex by set_index, reshape by unstack and sort first level of MultiIndex in columns by sort_index. Last flatten it by list comprehension with reset_index:

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

df = df.set_index(['ID', g]).unstack().sort_index(level=1, axis=1)
#python 3.6+
df.columns = [f'{a}_{b+1}' for a, b in df.columns]
#python bellow
#df.columns = ['{}_{}'.format(a, b+1) for a, b in df.columns]
df = df.reset_index()
print (df)
  ID subID_1  values_A_1 subID_2  values_A_2 subID_3  values_A_3
0  A     aaa        10.0     abb        30.0     acc        40.0
1  B     baa        20.0     bbb        60.0     NaN         NaN
2  C     caa        50.0     NaN         NaN     NaN         NaN