Pandas - rename columns based on cell value

Question

How can I change the name of columns from a dataframe

match_id player venue goals
1        John   home  4
1        Jim    home  0
1        Will   away  0 
1        Dan    away  1
...

based on a specific cell value (from column 'venue'), and end up with:

match_id home_player_1  home_player_1_goals home_player_2 home_player_2_goals away_player_1 away_player_1_goals away_player_2 away_player_2_goals
1        John           4                   Jim            0                    Will        0      Dan     1

is `venue` always `home` or do you have other values as well? — Quang Hoang, Sep 22 '22 at 15:40
Can you please update the data you provide to cover all possible scenarios? — sophocles, Sep 22 '22 at 15:45
Did you already ask this question [here](https://stackoverflow.com/questions/73803930/pandas-dinamically-create-new-columns-based-on-number-of-column-items). — Quang Hoang, Sep 22 '22 at 15:48
If you rename the column, but there're both `'home'` and `'away'` values, how would the column be called? Maybe you want to create different columns, one for each value of `venue`? — Ignatius Reilly, Sep 22 '22 at 15:49

sophocles · Accepted Answer · 2022-09-22T16:35:11.220

I was able to get there with creating two helper columns, which would be your new header structure, and concatenating back two pivoted versions of your dataframe:

cc = df.groupby('venue').cumcount().add(1).astype(str)
 
df = df.assign(
    c = df['venue'] + "_player_" + cc,
    c_goals = df['venue'] + "_player_" + cc + "_goals")

players = df.pivot_table(index='match_id',columns = 'c',aggfunc='first',values='player')
goals = df.pivot_table(index='match_id',columns = 'c_goals',aggfunc='first',values='goals')

out = pd.concat(
    [players,goals]
    ,axis=1).sort_index(axis=1)

>>> print(out)

          away_player_1  away_player_1_goals  ... home_player_2  home_player_2_goals
match_id                                     ...                                   
1                 Will                    0  ...           Jim                    0

[1 rows x 8 columns]

score 0 · Answer 2 · answered Sep 22 '22 at 17:50

A simple loop can also do the trick. Less concise, but still readable

In [32]: df
Out[32]: 
   match_id player venue  goals
0         1   john     h      4
1         1    jim     h      0
2         1   will     a      0
3         1     da     a      1

In [33]: for match in df.match_id.unique():
    ...:   df_m = df.query('match_id==%d' % match)
    ...:   counts = {v:0 for v in df_m.venue.unique()}
    ...:   records = []
    ...:   for i, row in df_m.iterrows():
    ...:     counts[row.venue] += 1
    ...:     count = counts[row.venue]
    ...:     name_record = "%s_player_%d" % (row.venue, count), row.player
    ...:     goal_record = "%s_player_%d_goals" % (row.venue, count), row.goals
    ...:     records += [name_record, goal_record]
    ...:   df_r = pandas.DataFrame(dict(records), index=[0])
    ...:   df_r['match_id'] = match
    ...:

In [34]: df_r
Out[34]: 
  h_player_1  h_player_1_goals h_player_2  ...  a_player_2 a_player_2_goals  match_id
0       john                 4        jim  ...          da                1         1

[1 rows x 9 columns]

Pandas - rename columns based on cell value

2 Answers2