Stack per two columns and two measures

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()
)

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top