Pandas melt multiple columns-CodePudding

Input Dataframe

    id  P1  x1  y1  P2  x2  y2  P3  x3  y3
0   _1  a   1   aa  b   2   bb  c   3   cc
1   _2  d   4   dd  e   5   ee  f   6   ff

I would like to transform it to get below output

    id  Probability code    number  name
0   -1  P1            a       1       aa
1   -1  P2            b       2       bb
2   -1  P3            c       3       cc
3   -2  P1            d       4       dd
4   -2  P2            e       5       ee
5   -2  P3            f       6       ff

I appreciate your guidance

CodePudding user response：

You can use wide_to_long to unpivot the dataframe rename and transformation:

(pd.wide_to_long(df, stubnames=['P', 'x', 'y'], i='id', j='Probability')
   .rename(columns={'P': 'code', 'x': 'number', 'y': 'name'})
   .reset_index()
   .assign(Probability = lambda x: 'P'   x.Probability.astype(str)))

   id Probability code  number name
0  _1          P1    a       1   aa
1  _2          P1    d       4   dd
2  _1          P2    b       2   bb
3  _2          P2    e       5   ee
4  _1          P3    c       3   cc
5  _2          P3    f       6   ff

CodePudding user response：

Another option with pivot_longer from pyjanitor:

# pip install pyjanitor
import pandas as pd
import janitor

(df
.pivot_longer(index = 'id', 
              names_to = ('.value', 'Probability'), 
              names_pattern = r"(\D)(\d)", 
              sort_by_appearance=True)
.rename(columns={'P':'code', 'x':'number', 'y':'name'})
.assign(Probability = lambda df: 'P'   df.Probability)
)

   id Probability code  number name
0  _1          P1    a       1   aa
1  _1          P2    b       2   bb
2  _1          P3    c       3   cc
3  _2          P1    d       4   dd
4  _2          P2    e       5   ee
5  _2          P3    f       6   ff