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

user_5037278 0
Viewed 59

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.

pandas transpose python
2 Answers

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
user_128331660

you could do:

df = df.set_index(['Keywords'])
df.columns = df.columns.str.split("_", expand=True)
df.stack(level=1).reset_index()

    Keywords    level_1 IX  LS
0   Glass   Brand1  2   78.0
1   Glass   Brand2  5   100.0
2   Glass   Brand3  120 NaN
3   Wood    Brand1  10  400.0
4   Wood    Brand2  25  700.0
5   Wood    Brand3  10  250.0
user_112051490
Related