I have data that looks something like this:
order_id Product_A Product_B Price_Product_A Price_Product_B
100 Pen Notebook 1.5 3
101 Bag Watch 10 12
I need it to look like this:
order_id product price
100 Pen 1.5
100 Notebook 3
101 Bag 10
101 Watch 12
How can I use stack() and unstack() for this? I used it so fat only for one numeric measure.
There is a convenient function for this, wide_to_long:
pd.wide_to_long(df, ['Product','Price_Product'], i='order_id', j='subtype', sep = '_', suffix = '\D+')
output:
Product Price_Product
order_id subtype
100 A Pen 1.5
101 A Bag 10.0
100 B Notebook 3.0
101 B Watch 12.0
The same can be achieved with melt and unstack, which could be of instructional value. A somewhat tricky bit is to split 'variable' into two parts, the root and the suffix, something wide_to_long helps you with. For your example case this can look like this:
df1 = df.melt(id_vars = 'order_id')
df1['cat'] = df1['variable'].str[:-2] # you may have to tweak this for your actual data
df1['subtype'] = df1['variable'].str[-1:] # you may have to tweak this for your actual data
(df1.drop(columns = 'variable')
.set_index(['order_id','subtype','cat'])
.unstack()
.droplevel(level=0, axis=1)
.reset_index()
)
I would simply create two dataframes: one for product A and one for product B. Then set the column names for both and append them like this:
df1 = df[['order_id', 'Product_A', 'Price_Product_A']]
df2 = df[['order_id', 'Product_B', 'Price_Product_B']]
df1.columns = ['order_id', 'product', 'price']
df2.columns = ['order_id', 'product', 'price']
df = df1.append(df2)
df
Output:
order_id product price
0 100 Pen 1.5
1 101 Bag 10.0
0 100 Notebook 3.0
1 101 Watch 12.0