Transform pandas dataframe rename columns based on row values-CodePudding

I have the following data frame df_in

   data = [{'s':123, 'x': 5, 'a': 1, 'b': 2, 'c': 3},
        {'s':123, 'x': 22, 'a': 4, 'b': 5, 'c': 6},
        {'s':123, 'x': 33, 'a': 7, 'b': 8, 'c': 9},
        {'s':124, 'x': 5, 'a': 11, 'b': 12, 'c': 3},
        {'s':124, 'x': 22, 'a': 14, 'b': 15, 'c': 16},
        {'s':124, 'x': 33, 'a': 17, 'b': 18, 'c': 19}]

   df = pd.DataFrame(data, columns=['s', 'x', 'a', 'b', 'c'])

and would like to produce df_out where _x needs to be appended to the column names. Later I will index df_out on s and then do df_out.to_dict('index') to produce the desired output I need. I have tried transposing the df_in and then renaming rows with lambda function based on x but having trouble getting the desired df_out. Any help would be great.

Thanks

CodePudding user response：

converting x to str to aid in joining pivot followed by merging of the column labels

df2=df.pivot(index='s', columns='x').reset_index()
df2.columns= [str(col[0] '_'  str(col[1])).strip('_') for col in df2.columns]
df2



    s   a_5 a_22 a_33 b_5 b_22 b_33 c_5 c_22 c_33
0   123   1    4    7   2    5    8   3    6    9
1   124  11   14   17  12   15   18   3   16   19

CodePudding user response：

You can use a pivot:

(df
   .pivot('s', 'x')
   .pipe(lambda d: d.set_axis(d. columns.map(lambda x: '_'.join(map(str, x))), axis=1))
   .reset_index()
 )

Output:

     s  a_5  a_22  a_33  b_5  b_22  b_33  c_5  c_22  c_33
0  123    1     4     7    2     5     8    3     6     9
1  124   11    14    17   12    15    18    3    16    19

CodePudding user response：

Here's another option:

df_out = df.melt(['s','x']).set_index(['s','x', 'variable']).unstack([2,1])['value']
df_out.columns = [f'{i}_{j}' for i, j in df_out.columns]
print(df_out.reset_index())

Output:

     s  a_5  a_22  a_33  b_5  b_22  b_33  c_5  c_22  c_33
0  123    1     4     7    2     5     8    3     6     9
1  124   11    14    17   12    15    18    3    16    19

CodePudding user response：

One option is with pivot_wider from pyjanitor :

# pip install pyjanitor
import janitor
import pandas as pd
df.pivot_wider(index = 's', names_from = 'x')
     s  a_5  a_22  a_33  b_5  b_22  b_33  c_5  c_22  c_33
0  123    1     4     7    2     5     8    3     6     9
1  124   11    14    17   12    15    18    3    16    19