How to pivot a data frame with suffixed columns?-CodePudding

I have a pandas dataframe with the following structure:

Frame     P_1_x  P_1_y  P_2_x  P_2_y ... P_N_x P_N_y
0         1      9      6      2         4     3

And I would like to transform it to:

      x   y
P_1   1   9 
P_2   6   2 
.
.
.
P_N   4   3

Is there any efficient way to do it with pandas?

I tried to use the pandas.wide_to_long() function and multi indexing but I couldn't make it work in my case.

CodePudding user response：

Split the columns around _ and use expand parameter to convert to multiindex, then stack the multiindex columns to reshape:

s = df.set_index('Frame')
s.columns = s.columns.str.rsplit(r'_', 1, expand=True)
out = s.stack(0).droplevel(0)

     x  y
P_1  1  9
P_2  6  2
...
P_N  4  3