Pandas pivot or reshape dataframe with NaN

Question

I have this dataframe that i need to pivot or reshape based on the frame col

df = {'frame': {0: 0, 1: 1, 2: 2, 3: 0, 4: 1, 5: 2}, 'pvol': {0: nan, 1: nan, 2: nan, 3: 23.1, 4: 24.3, 5: 25.6}, 'vvol': {0: 109.8, 1: 140.5, 2: 160.4, 3: nan, 4: nan, 5: nan}, 'area': {0: 120, 1: 130, 2: 140, 3: 110, 4: 110, 5: 112}, 'label': {0: 'v', 1: 'v', 2: 'v', 3: 'p', 4: 'p', 5: 'p'}}

Current dataframe

frame   pvol    vvol    area    label
0       NaN     109.8   120     v
1       NaN     140.5   130     v
2       NaN     160.4   140     v
0       23.1    NaN     110     p
1       24.3    NaN     110     p
2       25.6    NaN     112     p

Expected output

frame   pvol    vvol    v_area  p_area
0       23.1    109.8   110     110
1       24.3    140.5   110     110
2       25.6    160.4   112     112

The prefix v and p aren't necessary I just need a way to tell the columns apart

This is how I got it to work, but it seems long. I'm sure there is a better way

for name, tdf in df.groupby('label'):
        df.loc[tdf.index, '{}_area'.format(name)] = tdf['area']

pdf = df[df['label'].eq('p')][['frame', 'label', 'pvol', 'p_area']]
vdf = df[df['label'].eq('v')][['frame', 'vvol', 'v_area']]
df = pdf.merge(vdf, on='frame', how='outer')

Answer 1

Let's try pivot and dropna:

out = df.pivot(index='frame', columns='label').dropna(axis=1)
out.columns = [f'{y}_{x}' for x,y in out.columns]

Output:

       p_pvol  v_vvol  p_area  v_area
frame                                
0        23.1   109.8     110     120
1        24.3   140.5     110     130
2        25.6   160.4     112     140