pythonpandasdataframecalculated-columns

Create a New Column Based on the Value of two Columns in Pandas with conditionals

I have a dataframe with two different columns I need to use to calculate a score:

id Pos Player GW VP Final Drop TournamentPoints
0 1 1 Alessio Bianchi 2 7.0 5.0 NaN
1 2 2 Gianluca Bianco 2 7.0 0.0 NaN
2 3 2 Sara Rossi 1 5.0 0.0 NaN
3 4 2 Gabriele Verdi 1 4.5 0.0 NaN
4 5 2 Luca Gialli 1 3.0 0.0 NaN

Tournament points is calculated from GW and VP with a formula:

df['TournamentPoints'] = ((number_of_players / 10) * (df["VP"] + 1)) + (df['GW'] * x)

Where number_of_players and X are calculated previously.

HoweverI need another step:

  1. add 50 to the row with the highest value in "Final" columns (in this case Alessio Bianchi).
  2. if two rows have the same value in "Final" and it's the highest, only the row with the lowest "Pos" must receive the 50 boost.

Any suggestion? Thanks in advance

I already tried .apply() and .map() methods without success

Solution

  • Assuming Pos is already sorted, you can use idxmax, this will select the first row that has the maximum value:

    df['TournamentPoints'] = ((number_of_players / 10) * (df["VP"] + 1)) + (df['GW'] * x)

df.loc[df['Final'].idxmax(), 'TournamentPoints'] += 50

    If Pos is not sorted:

    df.loc[df.sort_values(by='Pos')['Final'].idxmax(), 'TournamentPoints'] += 50