Reshaping long format dataframe to wide format according to the number of elements in columns [closed]

I have the following pandas dataframe X in long format:

alt.var group
1       1 
2       1
3       1
4       1
1       2
2       2
1       3
2       3
1       4
1       5
2       5
3       5
1       6

And I would like to change to the following wide format according the number of alternatives in the group:

group 1  2  3  4
1     4  3  2  1
2     4  3  NA NA
3     4  3  NA NA
4     4  NA NA NA
5     4  3  2  NA
6     4  NA NA NA

i.e. I want to create 4 columns (indexed by 1,2,3,4, the names as in the alt.var column) (the maximum number of alternatives in a group) and column i get assigned 5-i if element i exists in group i and NA or null value if element i does not exists in group i.

I have tried to do a little digging in stackoverflow but it doesn’t seem to match any result.

Answer

You can create a val column with 5 – value in alt.var then pivot

out = (df.assign(val=5-df['alt.var'])
       .pivot(index='group', columns='alt.var', values='val'))

print(out)

alt.var    1    2    3    4
group
1        4.0  3.0  2.0  1.0
2        4.0  3.0  NaN  NaN
3        4.0  3.0  NaN  NaN
4        4.0  NaN  NaN  NaN
5        4.0  3.0  2.0  NaN
6        4.0  NaN  NaN  NaN