根据组和条件重新排列行

Question

我有3个数据帧，每个数据帧都有一个球员列表，按位置（前锋、后卫和守门员）：

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]})

---

然后我为这些选定的玩家创建一个池，如下所示：

dfs = [FWD_df, DEF_df, GKP_df]

pool = pd.concat(dfs)

print (pool)

其中打印：

              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

---

规则：

问题是，我不能让前3名前锋中的球员面对前2名后卫中的任何一名，也不能让前1名门将面对，也就是说，如果他是他的下一个“对手”，就像这样：

             name           team          adversary position  open_price
2            Rhuan       Botafogo         Grêmio      FWD        3.51
...
0    Pedro Geromel         Grêmio       Botafogo      DEF       10.82

---

当这种情况发生时，我需要将最有价值的玩家（最高的“开盘价”）保留在其行位置，并将最便宜的玩家移动到其最后一个分组行索引

在上述情况下，防守球员的成本更高，因此他留在原地，我们将FWD向下移动到其位置的最后一个索引：

             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

---

但是通过这样做，我们现在有了一名前三名的前锋，他将面对一名门将，这也是规则所禁止的：

             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

由于守门员比前锋花费更多，他留在原地，我们将前锋向下移动，就像这样：

             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

我们必须再次这样做：

             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

---

直到我们得到上面的数据帧（即最终池）

---

如何对行进行有条件的重新排序？有人能给我指一下正确的方向吗

Answer 1

你有一个问题，你需要找到一个基于约束的平衡点

我在这里提出了一个解决方案，我们不使用pandas，而是使用纯python列表，因为pandas的开销只会降低解决方案的速度

首先，有一个无限循环的明显可能性。因此，您必须跟踪过去的排列配置，以便知道何时已形成循环并开始循环

我已经创建了一个自定义错误InfeasableError（为了可读性），每当发生这种情况时都会引发该错误。代码的其余部分应该很简单

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()

---

现在，平衡发现函数：

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, [])

---

如果您需要，可以将解决方案导入熊猫：

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