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-string
s 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