Pandas Dataframe: move multiple rows with the same column values into new specific rows

Question

Ι get a large datasheet from SAP like the following one

SKU	Warehouse Num	Available Stock	.....
001	1	45
001	2	0
001	3	6
002	1	9
002	2	32
002	3	7

And I want to create a new data frame using pandas that keeps unique SKU numbers and moves available stock from columns to specific rows. The data frame I want to create should look like the one below.

SKU	Warehouse 001	Warehouse 002	Warehouse 003	.....
001	45	0	6
002	9	32	7

I tried using df.pivot_table but couldn't get it working properly. Can anyone help me with that?

Answer 1

This is an application of pivot which is covered extensively in How to pivot a dataframe? so I offer this Community wiki as a place to outline a solution in a way that is easier to understand than the comments. But encourage reading of the linked duplicate to understand fully.

The following pandas functions are useful here:

1. pivot
2. astype
3. str.zfill
4. add_prefix
5. reset_index

---

Complete Working Example:

import pandas as pd

df = pd.DataFrame({'SKU': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 2},
                   'Warehouse Num': {0: 1, 1: 2, 2: 3, 3: 1, 4: 2, 5: 3},
                   'Available Stock': {0: 45, 1: 0, 2: 6, 3: 9, 4: 32, 5: 7}})

# long to wide
new_df = df.pivot(index='SKU', columns='Warehouse Num',
                  values='Available Stock')
# pad columns and index with zeros to 3 places
new_df.columns = new_df.columns.astype(str).str.zfill(3).rename(None)
new_df.index = new_df.index.astype(str).str.zfill(3)
# Add Prefix and return SKU to a column
new_df = new_df.add_prefix('Warehouse ').reset_index()
print(new_df)

new_df:

   SKU  Warehouse 001  Warehouse 002  Warehouse 003
0  001             45              0              6
1  002              9             32              7