Bring data of each category to a row by pandas

Question

Is there any simple and fast way in pandas to reach from df

d = {'points': ['P1', 'P2', 'Q1', 'Q2', 'Q3'],
     'x': [1, 2, 60 , 61, 62],
     'y': [2, 5, 70 , 71, 72]
    }

pd.DataFrame(data=d, index=[0, 1, 2, 3, 4])

to df

d = {'points': ['all_P', 'all_Q'],
     'x1' : [1,60],
     'y1' : [2,70],
     'x2' : [2,61],
     'y2' : [5,71],
     'x3' : [pd.NA , 62],
     'y3' : [pd.NA , 72]
    }

pd.DataFrame(data=d, index=[0, 1])

Answer 1

You could try with DataFrame.pivot:

# Add new elements (kind of point and index).
df['kind'] = 'all_' + df['points'].str[0]
df['idx'] = df['points'].str[1:]

# Pivot and merge indices levels.
res = df.drop(columns='points').pivot(columns='idx', index='kind')
res.columns = res.columns.map(''.join)

# Sort columns as per result.
print(res.sort_index(axis=1, key=lambda cols: [c[1:] for c in cols]))

         x1    y1    x2    y2    x3    y3
kind                                     
all_P   1.0   2.0   2.0   5.0   NaN   NaN
all_Q  60.0  70.0  61.0  71.0  62.0  72.0