transpose of few columns (not whole data frame) in pandas (opposite of get_dummies)

Question

I need to take the transpose of few columns (not whole data frame).

Keywords     IX_Brand1      IX_Brand2     IX_Brand3     LS_Brand1      LS_Brand2      LS_Brand3
Glass        2               5                          120            78             100
Wood         10              25            10           400            700            250

Desired output would be like this:

Keywords     Brands        IX       LS
Glass        Brand1        2        120
Glass        Brand2        5        78
Glass        Brand3        NaN      100
Wood
.
.
.        

It should be the opposite of get_dummies but so far didn't able to find the solution.

Answer 1

Use pd.wide_to_long:

df1 = (
    pd.wide_to_long(
        df, i='Keywords', j='Brands', stubnames=['IX', 'LS'], sep='_', suffix='\w+')
    .sort_values('Keywords').reset_index()
)

Result:

print(df1)

  Keywords  Brands    IX   LS
0    Glass  Brand1   2.0  120
1    Glass  Brand2   5.0   78
2    Glass  Brand3   NaN  100
3     Wood  Brand1  10.0  400
4     Wood  Brand2  25.0  700
5     Wood  Brand3  10.0  250