Python groupby - change column values based on conditions in other columns

user_11876316 0
Viewed 477

I wanna groupby the column 'group' first. Then change the values in the result column based on the conditions in the result and rank columns.

This is what I have now:

import pandas as pd
import numpy as np

group = ['g1','g1','g1','g1','g1','g2','g2','g2','g2','g2','g2']
rank = ['1','2','3','4','5','1','2','3','4','5','6']
result = ['1','4','2','4','4','1','4','4','2','4','4']

df = pd.DataFrame({"group": group, "rank": rank, "result": result})

    group   rank    result
0   g1        1       1
1   g1        2       4
2   g1        3       2
3   g1        4       4
4   g1        5       4
5   g2        1       1
6   g2        2       4
7   g2        3       4
8   g2        4       2
9   g2        5       4
10  g2        6       4

In the same group, I wanna change the result from 4 to 6 when the rank is greater than the rank of result = 2
For example: in g1, the rank of result = 2 is 3. So the result of rank 4 & 5 will be 6.
in g2, the rank of result = 2 is 4. So the result of rank 5 & 6 will be 6.

In this case, my desired output will be:

    group   rank    result
0   g1        1       1
1   g1        2       4
2   g1        3       2
3   g1        4       6
4   g1        5       6
5   g2        1       1
6   g2        2       4
7   g2        3       4
8   g2        4       2
9   g2        5       6
10  g2        6       6

I haven't got any idea the best way to achieve this. Can anyone help?

Thanks in advance!

pandas-groupby pandas dataframe python-3.x python
2 Answers

Use Series.where for replace rank to NaN for rows matched by 2 in result and then use GroupBy.transform for repeat values per groups by GroupBy.first, last compare for greater by Series.gt and set value 6 in DataFrame.loc:

#convert to integers for correct compare values greater like '10'
df[['rank','result']] = df[['rank','result']].astype(int)

s = df['rank'].where(df['result'].eq(2)).groupby(df['group']).transform('first')

df.loc[df['rank'].gt(s), 'result'] = 6
print (df)
   group  rank  result
0     g1     1       1
1     g1     2       4
2     g1     3       2
3     g1     4       6
4     g1     5       6
5     g2     1       1
6     g2     2       4
7     g2     3       4
8     g2     4       2
9     g2     5       6
10    g2     6       6
user_29010020

This will do the trick

import pandas as pd
import numpy as np

group = ['g1','g1','g1','g1','g1','g2','g2','g2','g2','g2','g2']
rank = ['1','2','3','4','5','1','2','3','4','5','6']
result = ['1','4','2','4','4','1','4','4','2','4','4']

df = pd.DataFrame({"group": group, "rank": rank, "result": result})

def changeDf(x):
    df_gp = df[df['group'] == x['group']]
    rank_of_2 = df_gp.loc[df_gp['result'] =='2', 'rank'].values[0]
    if int(x['rank']) > int(rank_of_2):
        return '6'
    else:
        return x['result']

df['result'] = df.apply(changeDf, axis=1)
print(df)
user_144426870
Related