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

Answer 2

Another option is to create a Series of lists using groupby.agg; then construct a DataFrame:

out = df.groupby('col1')['col2'].agg(list).pipe(lambda x: pd.DataFrame(zip(*x), columns=x.index.tolist()))

Output:

   A  B  C
0  1  2  9
1  4  5  0
2  6  8  7