Skip to content
Advertisement

To add new columns that identify the value position by each column with loop

I have Dataframe:

teamId      pts xpts    
Liverpool   82  59
Man City    57  63
Leicester   53  47
Chelsea     48  55

And I’m trying to add new columns that identify the team position by each column

I wanna get this:

teamId      pts xpts №pts №xpts    
Liverpool   82  59   1    2
Man City    57  63   2    1
Leicester   53  47   3    4
Chelsea     48  53   4    3

I tried to do something similar with the following code, but to no avail. The result is a list

df = [df.sort_values(by=i, ascending=False).assign(new_col=lambda x: range(1, len(df) + 1)) for i in df.columns]

Advertisement

Answer

Use DataFrame.rank with DataFrame.add_prefix and add new DataFrame to original DataFrame by DataFrame.join:

c = ['pts','xpts']

df = df.join(df[c].rank(method='dense', ascending=False).astype(int).add_prefix('N'))
print (df)
      teamId  pts  xpts  Npts  Nxpts
0  Liverpool   82    59     1      2
1   Man City   57    63     2      1
2  Leicester   53    47     3      4
3    Chelsea   48    55     4      3

Another idea with for loop and f-strings for new columns names:

c = ['pts','xpts']

for x in c:
    df[f'N{x}'] = df[x].rank(method='dense', ascending=False).astype(int)
print (df)
      teamId  pts  xpts  Npts  Nxpts
0  Liverpool   82    59     1      2
1   Man City   57    63     2      1
2  Leicester   53    47     3      4
3    Chelsea   48    55     4      3
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement