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?
Advertisement
Answer
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 :