How to get a string from column names and melt multiple columns in python?

Question

I have this table and I need to melt away this table to be like the expected table where I need to get the point name (a and b) from the column name and let the bq and progress columns melt.

type    bq a    bq b    progress a    progress b
P        1       1          1             2
Q        2       3          4             2
R        2       1          1             2

The expected result is as below:

type     point      bq    progress
P         a         1        1
P         b         1        2
Q         a         2        4
Q         b         3        2
R         a         2        1
R         b         1        2

How can do it in python?

Answer 1

Try this:

df = pd.DataFrame({'type':['p','q','r'],
                  'bq a':['1','2','2'],
                  'bq b':['1','3','1'],
                  'progress a':['1','4','1'],
                  'progress b':['2','2','2']})

df_bq = pd.melt(df, id_vars =['type'], value_vars =['bq a','bq b'])
df_bq.columns = ['type','point','bq']
df_bq['point'] = df_bq['point'].apply(lambda x:x.replace('bq ',''))
df_bq.sort_values(by = 'point')

df_p = pd.melt(df, id_vars =['type'], value_vars =['progress a','progress b'])
df_p.columns = ['type','point','progress']
df_p['point'] = df_p['point'].apply(lambda x:x.replace('progress ',''))
df_p.sort_values(by = 'point')

df_concat = pd.concat([df_bq, df_p['progress']], axis=1)
df_concat

Result :