Pandas pivot or reshape dataframe with NaN

user_6031995 0
Viewed 123

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')
pandas dataframe python
3 Answers

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
user_42384080

Let us try

s = df.set_index(['frame','label']).unstack().dropna(axis=1)
s.columns=s.columns.map('_'.join)
s
Out[102]: 
       pvol_p  vvol_v  area_p  area_v
frame                                
0        23.1   109.8     110     120
1        24.3   140.5     110     130
2        25.6   160.4     112     140
user_79645270

I feel stupid for posting this after the gems dropped by @BEN_YO and @Quang_Hoang..

df.set_index('label', inplace=True)
d1 = df.loc['v', ['frame', 'vvol', 'area']].rename(columns={'area':'v_area'})
d2 = df.loc['p', ['frame', 'pvol', 'area']].rename(columns={'area':'p_area'})
pd.merge(d1, d2, on='frame')
   frame   vvol  v_area  pvol  p_area
0      0  109.8     120  23.1     110
1      1  140.5     130  24.3     110
2      2  160.4     140  25.6     112
user_21468940
Related