0

I have a dataframe like below:

A   B
254 1.2
254 0.9
253 2
253 1.8
253 1.9
252 1.5
251 1.2
251 1.7
251 1.8
251 1.6
250 0.9
250 0.95
250 1.1
250 1.11
250 1

I need this dataframe to be transformed as:

254 253 252 251 250

1.2 2   1.5 1.2 0.9
0.9 1.8     1.7 0.95
    1.9     1.8 1.1
            1.6 1.11
                1

Where values 254, 253 etc are now the column names.

I have tried dataframe.T, dataframe.pivot and dataframe.melt techniques but I do not get the desired dataframe as shown above.

Please advise how can this be done.

morelloking
  • 193
  • 1
  • 3
  • 11

1 Answers1

1

You can add a helper column and pivot:

(df.assign(index=df.groupby('A').cumcount())
   .pivot(index='index', columns='A', values='B')
   .iloc[:, ::-1]
   .rename_axis(index=None, columns=None)
)

output:

       254  253  252  251   250                        
0      1.2  2.0  1.5  1.2  0.90
1      0.9  1.8  NaN  1.7  0.95
2      NaN  1.9  NaN  1.8  1.10
3      NaN  NaN  NaN  1.6  1.11
4      NaN  NaN  NaN  NaN  1.00

Or using pivot_table you can pass the index directly:

(df.pivot_table(index=df.groupby('A').cumcount(), columns='A', values='B')
   .sort_index(axis=1, ascending=False)
   .rename_axis(columns=None)
)
mozway
  • 194,879
  • 13
  • 39
  • 75