Transform pandas dataframe rename columns based on row values

Question

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

Naveed · Accepted Answer · 2022-06-12T19:36:46.070

3

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

edited Jun 12 '22 at 19:36

answered Jun 12 '22 at 19:23

Naveed

11,495
2
14
21

@MichelAntonesco, if you create an example that would greatly help. The 0 and 1 before the column 's' is the index and it will be unique. However, if you have another column named 'i', with a value of 1 for all six rows, you can use that as in index alongside 's' in your pivot as follows df2=df.pivot(index=['i','s'], columns='x').reset_index() and the result will have column 'i' with values of 1 showing prior to 's'. However, 0 and 1 still shows, which you can ignore in bringing it into your spreadsheet. Not sure if this answers your question. I hope it does provide some clarification – Naveed Jun 12 '22 at 20:22

score 2 · Answer 2 · answered Jun 12 '22 at 19:21

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

score 0 · Answer 3 · answered Jun 12 '22 at 19:54

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

sammywemmy · Answer 4 · 2022-06-12T23:14:03.673

0

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

edited Jun 12 '22 at 23:14

answered Jun 12 '22 at 22:24

sammywemmy

27,093
4
17
31

Transform pandas dataframe rename columns based on row values

4 Answers4