OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Rearrange rows after comparing the simliar data

  • Thread starter Thread starter Nitin Gaur
  • Start date Start date

Nitin Gaur

I have four columns organized into two pairs. The first pair consists of (Home Team, Away Team), and the second pair is (HomeTeam, AwayTeam). My objective is to align rows where the teams in the first pair exactly match those in the second pair, ensuring they are in the same row. Additionally, when I move data in the columns of the second pair, all corresponding data in subsequent columns should move accordingly.

In the image I shared, in row 4, we have (Tottenham, Southampton) as the first pair. Instead of being in the 4th row, the second pair (Tottenham, Southampton) is currently in the 7th row. To correct this, I need to move the second pair from the 7th row to the 4th row. During this movement, the data in columns (HTHG, First Half Score, HTAG, Home Shot) should also be moved alongside the second pair (HomeTeam, AwayTeam).

It is a bonus if I can get a solution to achieve this using Python as I am learning it and using jupyter notebook. Otherwise, it will also fine if I can get the solution in Google Sheet.

enter image description here

pair_mapping = {(row['HomeTeam'], row['AwayTeam']): idx for idx, row in season_2023_2024.iterrows()}

for idx, row in season_2023_2024.iterrows():
   home_team = row['Home Team']
   away_team = row['Away Team']
   matching_idx = pair_mapping.get((home_team, away_team), None)
   # If a matching row is found and it is not the same as the current row, swap them
   if matching_idx is not None and matching_idx != idx:
       # Swap the rows for the second pair columns
       season_2023_2024.loc[matching_idx, ['HomeTeam', 'AwayTeam', 'HTHG', 'First Half Score', 'HTAG', 'Home Shot']], \
       season_2023_2024.loc[idx, ['HomeTeam', 'AwayTeam', 'HTHG', 'First Half Score', 'HTAG', 'Home Shot']] = \
       season_2023_2024.loc[idx, ['HomeTeam', 'AwayTeam', 'HTHG', 'First Half Score', 'HTAG', 'Home Shot']], \
       season_2023_2024.loc[matching_idx, ['HomeTeam', 'AwayTeam', 'HTHG', 'First Half Score', 'HTAG', 'Home Shot']]

display(HTML(season_2023_2024.to_html(index=False))) ```

Tried this as well:

lookup_table = {}
for i, row in season_2023_2024.iterrows():
   lookup_table[(row['Home_Team'], row['Away_Team'])] = row[['HomeTeam', 'AwayTeam', 'HTHG', 'First_Half_Score', 'HTAG', 'Home_Shot']]

for i, row in season_2023_2024.iterrows():
   key = (row['Home_Team'], row['Away_Team'])
   if key in lookup_table:
       for col in ['HomeTeam', 'AwayTeam', 'HTHG', 'First_Half_Score', 'HTAG', 'Home_Shot']:
           season_2023_2024.at[i, col] = lookup_table[key][col]

Continue reading...

Latest posts