How to pivot dataframe without changing index and returning all columns from previous operation

Question

I have a table :

When I try to pivot in python:

df.pivot(columns = 'Type', values = 'Value')

it returns only columns from Type column with values but columns Col1-Col5 do not appear in my dataframe

In Power Query it's achieved very simple, I just choose column I want to pivot and values for this column:

And after this operation I get following result:

How do I achieve the same result using pd.pivot ?

Thanks!

Answer 1

In no way using pd.pivot. There is the pd.pivot_table function made for this case:

table = pd.pivot_table(df, values='Value', index=['Col1','Col2','Col3','Col4','Col5'], 
columns=['Type'], aggfunc=np.sum)

Answer 2

You should be able to use pivot_table() to do this, same steps as outlined in this answer

res = df.pivot_table(values='Value', index=['Col1','Col2','Col3','Col4','Col5'], columns='Type')

This creates a multi-index dataframe, which you can then flatten:

res.reset_index(inplace=True)
res.columns.name = None