Pandas pivoting specific dataframe structure-CodePudding

I need some advice on pivoting a table using Python/Pandas

I have a dataframe similar to this:

df = {'hh': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],
      'id': ['1', '1', '2', '2', '1', '1', '2', '2'],
      'var': ['height', 'gender', 'height', 'gender', 'height', 'gender', 'height', 'gender'],
      'val': ['180', 'm', '172', 'f', '181', 'm', '170', 'f']}

      hh     id     var     val
0     a      1      height  180
1     a      1      gender  m
2     a      2      height  172
3     a      2      gender  f
4     b      1      height  181
5     b      1      gender  m
6     b      2      height  170
7     b      2      gender  f

And my dataframe should look like this:

index     gender     height
a1        m          180
a2        f          172
b1        m          181
b2        f          170

Where the index is the 'hh' and 'id' variables

I have tried the following:

pivot = df.pivot_table(index=['hh', 'id'], columns='var', values='val')

However I only end up with the index and no columns.

Any suggestions would be appreciated!

CodePudding user response：

df['idx'] = df['hh']   df['id']

df.pivot('idx', columns='var', values='val')

var     gender  height
idx         
a1  m   180
a2  f   172
b1  m   181
b2  f   170