8-Bit Borges
Reputation: 10033
Pandas - reorder rows based on group and condition
I have 3 dataframes, each one having a list of players, by position (Forwards, Defenders and Goalkeepers):
FWD_df = pd.DataFrame({'name':['Keno','Pepê','Rhuan','Léo Natel','Pedro Raul','Wesley'],
'team':['Atlético-MG','Grêmio','Botafogo','Corinthians','Botafogo','Palmeiras'],
'adversary':['Fluminense','Botafogo','Grêmio','Athlético-PR','Grêmio','Coritiba'],
'position':['FWD', 'FWD', 'FWD', 'FWD', 'FWD', 'FWD'],
'open_price':[15.75, 14.05, 3.51, 5.83, 5.92, 4.25]})
MID_df = pd.DataFrame({'apelido':['Everton Ribeiro','Thiago Galhardo','Thiago Neves','Mateus Vital','Dodi','Zé Gabriel'],
'clube':['Flamengo','Internacional','Sport','Corinthians','Fluminense', 'Internacional'],
'adversário':['Bragantino','Sport','Internacional','Athlético-PR','Atlético-MG','Sport'],
'posicao':['MID', 'MID', 'MID','MID', 'MID', 'MID'],
'preco_open':[10.82, 6.46, 4.96, 8.20, 5.29, 7.88]}).sort_values('preco_open', ascending=False).reset_index(drop=True).copy()
WING_df = pd.DataFrame({'apelido':['Renê','Abner Vinícius','Marcos Rocha','Victor Luis','Fagner','Ramon'],
'clube':['Flamengo','Athlético-PR','Palmeiras','Botafogo','Corinthians', 'Flamengo'],
'adversário':['Bragantino','Corinthians','Coritiba','Grêmio','Athlético-PR','Bragantino'],
'posicao':['WING', 'WING', 'WING','WING', 'WING', 'WING'],
'preco_open':[10.82, 6.46, 4.96, 8.20, 5.29, 7.88]}).sort_values('preco_open', ascending=False).reset_index(drop=True).copy()
DEF_df = pd.DataFrame({'name':['Pedro Geromel','Felipe Melo','Pedro Henrique','Réver','Thiago Heleno','Lucas Veríssimo'],
'team':['Grêmio','Palmeiras','Athlético-PR','Atlético-MG','Athlético-PR', 'Santos'],
'adversary':['Botafogo','Coritiba','Corinthians','Fluminense','Corinthians','Atlético-GO'],
'position':['DEF', 'DEF', 'DEF','DEF', 'DEF', 'DEF'],
'open_price':[10.82, 6.46, 4.96, 8.20, 5.29, 7.88]})
GKP_df = pd.DataFrame({'name':['Jandrei','Jean','Muriel','Diego Cavalieri','Marcelo Lomba','Luan Polli'],
'team':['Athlético-PR','Atlético-GO','Fluminense','Botafogo','Internacional', 'Sport'],
'adversary':['Corinthians','Santos','Atlético-MG','Grêmio','Sport','Internacional'],
'position':['GKP', 'GKP', 'GKP','GKP', 'GKP', 'GKP'],
'open_price':[8.73, 8.88, 5.66, 5.70, 10.62, 4.00]})
Then I create a pool for those selected players, like so:
dfs = [FWD_df, DEF_df, GKP_df]
pool = pd.concat(dfs)
print (pool)
which prints:
name team adversary position open_price
0 Keno Atlético-MG Fluminense FWD 15.75
1 Pepê Grêmio Botafogo FWD 14.05
2 Rhuan Botafogo Grêmio FWD 3.51
3 Léo Natel Corinthians Athlético-PR FWD 5.83
4 Pedro Raul Botafogo Grêmio FWD 5.92
5 Wesley Palmeiras Coritiba FWD 4.25
0 Pedro Geromel Grêmio Botafogo DEF 10.82
1 Felipe Melo Palmeiras Coritiba DEF 6.46
2 Pedro Henrique Athlético-PR Corinthians DEF 4.96
3 Réver Atlético-MG Fluminense DEF 8.20
4 Thiago Heleno Athlético-PR Corinthians DEF 5.29
5 Lucas Veríssimo Santos Atlético-GO DEF 7.88
0 Jandrei Athlético-PR Corinthians GKP 8.73
1 Jean Atlético-GO Santos GKP 8.88
2 Muriel Fluminense Atlético-MG GKP 5.66
3 Diego Cavalieri Botafogo Grêmio GKP 5.70
4 Marcelo Lomba Internacional Sport GKP 10.62
5 Luan Polli Sport Internacional GKP 4.00
RULE:
The thing is I cannot have, from the top 3 forwards, a player facing any of the top 2 defenders nor the top 1 goalkeeper, ie, if he is his next 'adversary', like so:
name team adversary position open_price
2 Rhuan Botafogo Grêmio FWD 3.51
...
0 Pedro Geromel Grêmio Botafogo DEF 10.82
When this happens I need to keep the most valuable player (highest 'open_price') on its row position and move the cheapest one to its last grouped row index.
In the case above the defender costs more, so he stays where he is and we move the FWD down to last index on its position:
name team adversary position open_price
0 Keno Atlético-MG Fluminense FWD 15.75
1 Pepê Grêmio Botafogo FWD 14.05
2 Léo Natel Corinthians Athlético-PR FWD 5.83
3 Pedro Raul Botafogo Grêmio FWD 5.92
4 Wesley Palmeiras Coritiba FWD 4.25
5 Rhuan Botafogo Grêmio FWD 3.51 <---- was index 2, now 5
0 Pedro Geromel Grêmio Botafogo DEF 10.82
1 Felipe Melo Palmeiras Coritiba DEF 6.46
2 Pedro Henrique Athlético-PR Corinthians DEF 4.96
3 Réver Atlético-MG Fluminense DEF 8.20
4 Thiago Heleno Athlético-PR Corinthians DEF 5.29
5 Lucas Veríssimo Santos Atlético-GO DEF 7.88
0 Jandrei Athlético-PR Corinthians GKP 8.73
1 Jean Atlético-GO Santos GKP 8.88
2 Muriel Fluminense Atlético-MG GKP 5.66
3 Diego Cavalieri Botafogo Grêmio GKP 5.70
4 Marcelo Lomba Internacional Sport GKP 10.62
5 Luan Polli Sport Internacional GKP 4.00
But by doing so, now we have a Forward in the top 3 that will face a goalkeeper, which is also forbidden by the RULE:
name team adversary position open_price
2 Léo Natel Corinthians Athlético-PR FWD 5.83
...
0 Jandrei Athlético-PR Corinthians GKP 8.73
And since the goalkeeper costs more than the forward, he stays where he is and we move the forward down, like so:
name team adversary position open_price
0 Keno Atlético-MG Fluminense FWD 15.75
1 Pepê Grêmio Botafogo FWD 14.05
2 Pedro Raul Botafogo Grêmio FWD 5.92
3 Wesley Palmeiras Coritiba FWD 4.25
4 Rhuan Botafogo Grêmio FWD 3.51 <---- was index 5, now 4
5 Léo Natel Corinthians Athlético-PR FWD 5.83 <---- was index 2, now 5
0 Pedro Geromel Grêmio Botafogo DEF 10.82
1 Felipe Melo Palmeiras Coritiba DEF 6.46
2 Pedro Henrique Athlético-PR Corinthians DEF 4.96
3 Réver Atlético-MG Fluminense DEF 8.20
4 Thiago Heleno Athlético-PR Corinthians DEF 5.29
5 Lucas Veríssimo Santos Atlético-GO DEF 7.88
0 Jandrei Athlético-PR Corinthians GKP 8.73
1 Jean Atlético-GO Santos GKP 8.88
2 Muriel Fluminense Atlético-MG GKP 5.66
3 Diego Cavalieri Botafogo Grêmio GKP 5.70
4 Marcelo Lomba Internacional Sport GKP 10.62
5 Luan Polli Sport Internacional GKP 4.00
And again we will have to do this once more:
name team adversary position open_price
0 Keno Atlético-MG Fluminense FWD 15.75
1 Pepê Grêmio Botafogo FWD 14.05
2 Wesley Palmeiras Coritiba FWD 4.25 <---- was index 3, now 2
3 Rhuan Botafogo Grêmio FWD 3.51 <---- was index 4, now 3
4 Léo Natel Corinthians Athlético-PR FWD 5.83 <---- was index 5, now 4
5 Pedro Raul Botafogo Grêmio FWD 5.92 <---- was index 3, now 5
0 Pedro Geromel Grêmio Botafogo DEF 10.82
1 Felipe Melo Palmeiras Coritiba DEF 6.46
2 Pedro Henrique Athlético-PR Corinthians DEF 4.96
3 Réver Atlético-MG Fluminense DEF 8.20
4 Thiago Heleno Athlético-PR Corinthians DEF 5.29
5 Lucas Veríssimo Santos Atlético-GO DEF 7.88
0 Jandrei Athlético-PR Corinthians GKP 8.73
1 Jean Atlético-GO Santos GKP 8.88
2 Muriel Fluminense Atlético-MG GKP 5.66
3 Diego Cavalieri Botafogo Grêmio GKP 5.70
4 Marcelo Lomba Internacional Sport GKP 10.62
5 Luan Polli Sport Internacional GKP 4.00
Until we end up with the dataframe above, ie the final pool.
How do I do this conditional re ordering of rows? Can anyone point me in the right direction here?
Upvotes: 0
Views: 206
Answers (1)
rafaelc
Reputation: 59274
You have a problem in which you need to find an equilibrium based on constraints.
I propose here a solution in which we do not work with pandas, but rather pure python lists because pandas' overhead would only slow the solution down.
First of all, there's an obvious possiblity of an infinite loop. As such, you have to keep track of past arrangement configurations so as to know when you have made a circle and began a loop.
I have created a custom error InfeasableError (for readability) which is raised whenever this happens. The rest of the code should be straightforward.
Setup
class InfeasableError(Exception):
pass
# These are the positions on the lists for team, adv and prices.
TEAM_POS = 2
ADVERSARY_POS = 3
PRICE_POS = 5
fwd = FWD_df.reset_index().values.tolist()
dfs = DEF_df.reset_index().values.tolist()
gkp = GKP_df.reset_index().values.tolist()
mid = MID_df.reset_index().values.tolist()
wig = WING_df.reset_index().values.tolist()
Now, the equilibrium-finding function:
def find_equilibrium(fwd, dfs, gkp, mid, wig, configs):
''' Finds an equilibrium for forward, defense and goalkeeper players.
'''
initial_config = get_config(fwd, dfs, gkp) # Initial configuration
# Below is the logic for FWD-DEF equilibria.
for i, fwd_player in enumerate(fwd[:3]):
for j, def_player in enumerate(dfs[:2]):
if fwd_player[ADVERSARY_POS] == def_player[TEAM_POS]:
if fwd_player[PRICE_POS] < def_player[PRICE_POS]:
del fwd[i]
fwd.append(fwd_player)
else:
del dfs[j]
dfs.append(def_player)
# Below is the logic for FWD-GKP equilibria.
gkp_player = gkp[0]
for i, fwd_player in enumerate(fwd[:3]):
if fwd_player[ADVERSARY_POS] == gkp_player[TEAM_POS]:
if fwd_player[PRICE_POS] < gkp_player[PRICE_POS]:
del fwd[i]
fwd.append(fwd_player)
else:
del gkp[0]
gkp.append(gkp_player)
final_config = get_config(fwd, dfs, gkp)
if initial_config == final_config:
# This means nothing changed. Equilibrium found!
return [*fwd, *dfs, *gkp]
if final_config in configs:
raise InfeasableError("There's no solution for an equilibrium.")
configs.append(final_config)
return find_equilibrium(fwd, dfs, gkp, mid, wig, configs)
def get_config(fwd, dfs, gkp):
''' Returns an ordered configuration of integers.
Each integer represents a unique player, and each sequence represents
a specific configuration of how these players are arranged.
'''
return (fwd[0][0], fwd[1][0], fwd[2][0], dfs[0][0], dfs[1][0], gkp[0][0])
solution = find_equilibrium(fwd, dfs, gkp, mid, wig, [])
The solution comes ready to be imported into pandas, if you need it:
>>> pd.DataFrame(solution)
0 1 2 3 4 5
0 0 Keno Atlético-MG Fluminense FWD 15.75
1 1 Pepê Grêmio Botafogo FWD 14.05
2 5 Wesley Palmeiras Coritiba FWD 4.25
3 2 Rhuan Botafogo Grêmio FWD 3.51
4 3 Léo Natel Corinthians Athlético-PR FWD 5.83
5 4 Pedro Raul Botafogo Grêmio FWD 5.92
6 0 Pedro Geromel Grêmio Botafogo DEF 10.82
7 1 Felipe Melo Palmeiras Coritiba DEF 6.46
8 2 Pedro Henrique Athlético-PR Corinthians DEF 4.96
9 3 Réver Atlético-MG Fluminense DEF 8.20
10 4 Thiago Heleno Athlético-PR Corinthians DEF 5.29
11 5 Lucas Veríssimo Santos Atlético-GO DEF 7.88
12 0 Jandrei Athlético-PR Corinthians GKP 8.73
13 1 Jean Atlético-GO Santos GKP 8.88
14 2 Muriel Fluminense Atlético-MG GKP 5.66
15 3 Diego Cavalieri Botafogo Grêmio GKP 5.70
16 4 Marcelo Lomba Internacional Sport GKP 10.62
17 5 Luan Polli Sport Internacional GKP 4.00
Upvotes: 1
Related Questions
- How to efficiently reorder rows based on condition?
- How to conditionally rearrange row order
- How to groupby column and then reorder columns within groups in python
- Pandas: Reorder values in one column within the groups that are created based on another column
- how to reorder of rows of a dataframe based on values in a column
- How to sort rows in pandas in place for given group?
- Sorting pandas dataframe per group and keep desired order
- group rows in ordered pandas dataframe depending on column values
- Reorder dataframe rows based on column values
- Reorder pandas DataFrame with specific rules