[SOLVED] Unpivoting a dataframe by splitting column names

Unpivoting a dataframe by splitting column names

I have the following dataframe:

volume_brand1	volume_productX_brand1	amount_brand1	amount_productX_brand1	volume_productX_brand2	amount_productX_brand2
1000	100	10	50	2000	200

etc...

I would like to unpivot this dataframe to look the following:

brand	product	volume	amount
brand1	productX	100	50
brand1	"not product X"	1000	10
brand2	productX	2000	200

etc..

I've tried using pandas' melt function but have not been successful. As every column has to be unpivoted there is no value for id_vars any help would be appreciated!

Solution

Another possible solution:

It renames the columns to create a consistent pattern using str.replace, then stacks the dataframe to convert it from wide to long format.
After stacking, the level_1 column is split into metric, product, and brand, and these columns are concatenated back to the original data.
Finally, the dataframe is pivoted to return it to a wide format, where volume and amount become separate columns, organized by brand and product.

df.columns = df.columns.str.replace(
    r'(volume|amount)_(brand)', r'\1_NotProductX_\2', regex=True)

d = df.stack().reset_index()

(pd.concat([
    d, d['level_1'].str.split('_', expand=True)], axis=1).iloc[:, [5, 4, 3, 2]]
 .set_axis(['brand', 'product', 'metric', 'value'], axis=1)
 .pivot(index=['brand', 'product'], columns='metric', values='value')
 .reset_index())

Output:

metric   brand      product  amount  volume
0       brand1  NotProductX      10    1000
1       brand1     productX      50     100
2       brand2     productX     200    2000