Pivot table based on the first value of the group in Pandas

Question

Have the following DataFrame:

I'm trying to pivot it in pandas and achieve the following format:

Actually I tried the classical approach with pd.pivot_table() but it does not work out:

pd.pivot_table(df,values='col2', index=[df.index], columns = 'col1')

Would be appreciate for some suggestions :) Thanks!

Answer 1

You can use pivot and then dropna for each column:

>>> df.pivot(columns='col1', values='col2').apply(lambda x: x.dropna().tolist()).astype(int)
col1  a  b  c
0     1  2  9
1     4  5  0
2     6  8  7