Most efficient way to find shared members of a list inside a dataframe?

Hello experts: I’m looking at so-called ‘COVID-19 bubbles’ inside pro cycling – I’ve compiled a list of riders for each team and a list of each race they’ve done. There are about 30 riders per team, and there have been a few dozen races after the sport started up again in July.

I’m stumped right now on how to proceed with analyzing the data or if this structure is even the right approach.

My end goal is to have a sort of Venn diagram of which riders raced together the most, one for each team, to visualize if they stuck to these bubbles (eg eight riders doing the same six races, and a different group of eight riders doing a different list of races, etc.)

Feel free to tag if duplicate/inappropriate etc. But a hand up would be appreciated!

My dataframe looks like such for one team:

{'Races': {'Alexandre Geniez': ['Paris-Chauny ',
   'Paris-Camembert',
   'Tour Poitou-Charentes en Nouvelle Aquitaine',
   'National Championships France - Road Race',
   'Mont Ventoux Dénivelé Challenge',
   'Faun-Ardèche Classic',
   'Tour des Alpes Maritimes et du Var',
   'Tour de La Provence',
   'Grand Prix Cycliste la Marseillaise'],
  'Alexis Gougeard': ['BinckBank Tour',
   'Paris-Chauny ',
   'Paris-Camembert',
   "Grand Prix d'Isbergues - Pas de Calais",
   'Tour Poitou-Charentes en Nouvelle Aquitaine',
   'Bretagne Classic - Ouest-France',
   'National Championships France - Road Race',
   'Tour du Limousin - Nouvelle Aquitaine',
   'Tour de Pologne',
   'Paris - Nice',
   'Le Samyn',
   'Omloop Het Nieuwsblad Elite',
   'Vuelta a Andalucia Ruta Ciclista Del Sol',
   'Clasica de Almeria',
   'Etoile de Bessèges'],
  'Andrea Vendrame': ["Giro d'Italia",
   'Paris-Camembert',
   'Tirreno-Adriatico',
   'Tour Poitou-Charentes en Nouvelle Aquitaine',
   'Bretagne Classic - Ouest-France',
   'National Championships Italy - Road Race',
   'VOO-Tour de Wallonie',
   'Milano-Sanremo',
   'Milano-Torino',
   'Strade Bianche',
   'UAE Tour',
   'Trofeo Laigueglia',
   'Cadel Evans Great Ocean Road Race',
   'Race Torquay',
   'Santos Tour Down Under'],
  'Anthony Jullien': ['De Brabantse Pijl - La Flèche Brabançonne',
   "Ronde de l'Isard",
   'Mont Ventoux Dénivelé Challenge'],
  'Antoine Raugel': ['Brussels Cycling Classic'],
  'Aurélien Paret-Peintre': ["Giro d'Italia",
   'Skoda-Tour de Luxembourg',
   'Tour du Doubs',
   'National Championships France - Road Race',
   'Critérium du Dauphiné',
   "Tour de l'Ain",
   'Paris - Nice',
   'Royal Bernard Drome Classic',
   'Faun-Ardèche Classic',
   'Tour de La Provence',
   'Etoile de Bessèges'],
  'Axel Domont': ['Paris-Chauny ',
   'Tirreno-Adriatico',
   'Tour du Limousin - Nouvelle Aquitaine',
   'Il Lombardia',
   'Tour de Pologne',
   'Race Torquay',
   'Santos Tour Down Under'],
  'Ben Gastauer': ["Giro d'Italia",
   'World Championships - Road Race',
   'Skoda-Tour de Luxembourg',
   'Tour du Doubs',
   'National Championships Luxembourg - Road Race',
   'Tour du Limousin - Nouvelle Aquitaine',
   'National Championships Luxembourg - ITT',
   'Il Lombardia',
   'Tour de Pologne',
   'Royal Bernard Drome Classic',
   'Faun-Ardèche Classic',
   'Tour des Alpes Maritimes et du Var',
   'Trofeo Laigueglia',
   'Cadel Evans Great Ocean Road Race',
   'Race Torquay',
   'Santos Tour Down Under'],
  'Benoît Cosnefroy': ['De Brabantse Pijl - La Flèche Brabançonne',
   'Liège-Bastogne-Liège',
   'La Flèche Wallonne',
   'Tour de France',
   'European Continental Championships - Road Race',
   'National Championships France - Road Race',
   'Critérium du Dauphiné',
   "La Route d'Occitanie - La Dépêche du Midi",
   'Paris - Nice',
   'Royal Bernard Drome Classic',
   'Faun-Ardèche Classic',
   'Tour des Alpes Maritimes et du Var',
   'Etoile de Bessèges',
   'Grand Prix Cycliste la Marseillaise'],
  'Clément Champoussin': ['De Brabantse Pijl - La Flèche Brabançonne',
   'Liège-Bastogne-Liège',
   'La Flèche Wallonne',
   'Paris-Chauny ',
   'Skoda-Tour de Luxembourg',
   'Tour du Doubs',
   'Il Lombardia',
   "Tour de l'Ain"],
  'Clément Chevrier': ['De Brabantse Pijl - La Flèche Brabançonne',
   'Paris-Chauny ',
   'Paris-Camembert',
   'Tour du Doubs',
   'National Championships France - Road Race',
   'Tour du Limousin - Nouvelle Aquitaine',
   'Mont Ventoux Dénivelé Challenge',
   'UAE Tour',
   'Trofeo Laigueglia',
   'Cadel Evans Great Ocean Road Race',
   'Race Torquay',
   'Santos Tour Down Under'],
  'Clément Venturini': ['BinckBank Tour',
   'Tour de France',
   'National Championships France - Road Race',
   'VOO-Tour de Wallonie',
   "La Route d'Occitanie - La Dépêche du Midi",
   'Le Samyn',
   'Kuurne-Bruxelles-Kuurne',
   'Omloop Het Nieuwsblad Elite',
   'Vuelta a Andalucia Ruta Ciclista Del Sol',
   'Clasica de Almeria',
   'Etoile de Bessèges',
   'Grand Prix Cycliste la Marseillaise'],
  'Dorian Godon': ['De Brabantse Pijl - La Flèche Brabançonne',
   'Liège-Bastogne-Liège',
   'La Flèche Wallonne',
   'Paris-Camembert',
   "Grand Prix d'Isbergues - Pas de Calais",
   'Tour Poitou-Charentes en Nouvelle Aquitaine',
   'Bretagne Classic - Ouest-France',
   'National Championships France - Road Race',
   'National Championships France - ITT',
   'Tour de Pologne',
   'Strade Bianche',
   'Le Samyn',
   'Kuurne-Bruxelles-Kuurne',
   'Omloop Het Nieuwsblad Elite',
   'Tour des Alpes Maritimes et du Var',
   'Trofeo Laigueglia',
   'Volta a la Comunitat Valenciana'],
  'François Bidard': ["Giro d'Italia",
   'Paris-Camembert',
   'Skoda-Tour de Luxembourg',
   'Tour Poitou-Charentes en Nouvelle Aquitaine',
   'National Championships France - Road Race',
   'Tour du Limousin - Nouvelle Aquitaine',
   'UAE Tour',
   'Tour de La Provence',
   'Etoile de Bessèges',
   'Grand Prix Cycliste la Marseillaise'],
  'Geoffrey Bouchard': ["Giro d'Italia",
   'Tirreno-Adriatico',
   'Tour du Limousin - Nouvelle Aquitaine',
   'Il Lombardia',
   'Tour de Pologne',
   'UAE Tour',
   'Trofeo Laigueglia',
   'Cadel Evans Great Ocean Road Race',
   'Race Torquay',
   'Santos Tour Down Under'],
  'Harry Tanfield': ["Grand Prix d'Isbergues - Pas de Calais",
   'Tour Poitou-Charentes en Nouvelle Aquitaine',
   'Tour de Pologne',
   'Le Samyn',
   'Grand Prix Cycliste la Marseillaise'],
  'Jaakko Hänninen': ["Giro d'Italia",
   'Tirreno-Adriatico',
   'Il Lombardia',
   "Tour de l'Ain",
   'UAE Tour',
   'Tour de La Provence'],
  'Julien Duval': ['BinckBank Tour',
   'Paris-Camembert',
   "Grand Prix d'Isbergues - Pas de Calais",
   'Brussels Cycling Classic',
   'European Continental Championships - Mixed Relay TTT',
   'Bretagne Classic - Ouest-France',
   'National Championships France - Road Race',
   'VOO-Tour de Wallonie',
   'Milano-Sanremo',
   'Milano-Torino',
   'Strade Bianche',
   'Le Samyn',
   'Kuurne-Bruxelles-Kuurne',
   'Omloop Het Nieuwsblad Elite',
   'Tour de La Provence',
   'Volta a la Comunitat Valenciana'],
  'Lawrence Naesen': ['BinckBank Tour',
   'National Championships Belgium - Road Race',
   'Skoda-Tour de Luxembourg',
   'Brussels Cycling Classic',
   'Bretagne Classic - Ouest-France',
   'VOO-Tour de Wallonie',
   'Milano-Sanremo',
   'Milano-Torino',
   'Strade Bianche',
   'Le Samyn',
   'Kuurne-Bruxelles-Kuurne',
   'Omloop Het Nieuwsblad Elite',
   'Vuelta a Andalucia Ruta Ciclista Del Sol',
   'Clasica de Almeria',
   'Volta a la Comunitat Valenciana'],
  'Mathias Frank': ['De Brabantse Pijl - La Flèche Brabançonne',
   'Liège-Bastogne-Liège',
   'La Flèche Wallonne',
   'Tirreno-Adriatico',
   'Il Lombardia',
   "Tour de l'Ain",
   'Royal Bernard Drome Classic',
   'Faun-Ardèche Classic',
   'Volta a la Comunitat Valenciana'],
  'Mikaël Cherel': ['Liège-Bastogne-Liège',
   'La Flèche Wallonne',
   'Tour de France',
   'National Championships France - Road Race',
   'Critérium du Dauphiné',
   'Mont Ventoux Dénivelé Challenge',
   "La Route d'Occitanie - La Dépêche du Midi",
   'Paris - Nice',
   'Faun-Ardèche Classic',
   'Tour des Alpes Maritimes et du Var',
   'Tour de La Provence',
   'Grand Prix Cycliste la Marseillaise'],
  'Nans Peters': ['World Championships - Road Race',
   'Tour de France',
   'National Championships France - Road Race',
   'Critérium du Dauphiné',
   "Tour de l'Ain",
   'Paris - Nice',
   'Royal Bernard Drome Classic',
   'Faun-Ardèche Classic',
   'Tour des Alpes Maritimes et du Var',
   'Tour de La Provence'],
  'Oliver Naesen': ['BinckBank Tour',
   'World Championships - Road Race',
   'National Championships Belgium - Road Race',
   'Tour de France',
   'European Continental Championships - Road Race',
   'VOO-Tour de Wallonie',
   'Milano-Sanremo',
   'Milano-Torino',
   'Strade Bianche',
   'Paris - Nice',
   'Kuurne-Bruxelles-Kuurne',
   'Omloop Het Nieuwsblad Elite',
   'Vuelta a Andalucia Ruta Ciclista Del Sol',
   'Clasica de Almeria',
   'Volta a la Comunitat Valenciana'],
  'Pierre Latour': ['Tour de France',
   'National Championships France - Road Race',
   'Critérium du Dauphiné',
   'Mont Ventoux Dénivelé Challenge',
   "La Route d'Occitanie - La Dépêche du Midi",
   'Paris - Nice',
   'Royal Bernard Drome Classic',
   'Etoile de Bessèges'],
  'Quentin Jauregui': ['Liège-Bastogne-Liège',
   'La Flèche Wallonne',
   'Paris-Chauny ',
   "Grand Prix d'Isbergues - Pas de Calais",
   'Tour du Doubs',
   'Brussels Cycling Classic',
   'Bretagne Classic - Ouest-France',
   'National Championships France - Road Race',
   'Tour du Limousin - Nouvelle Aquitaine',
   'Tour de Pologne',
   'UAE Tour',
   'Etoile de Bessèges',
   'Grand Prix Cycliste la Marseillaise'],
  'Romain Bardet': ['De Brabantse Pijl - La Flèche Brabançonne',
   'Tour de France',
   'National Championships France - Road Race',
   'Critérium du Dauphiné',
   "La Route d'Occitanie - La Dépêche du Midi",
   'Paris - Nice',
   'Royal Bernard Drome Classic',
   'Tour des Alpes Maritimes et du Var',
   'Santos Tour Down Under'],
  'Silvan Dillier': ['BinckBank Tour',
   'World Championships - Road Race',
   'Tirreno-Adriatico',
   'Tour Poitou-Charentes en Nouvelle Aquitaine',
   'VOO-Tour de Wallonie',
   'National Championships Switzerland - ITT',
   'Kuurne-Bruxelles-Kuurne',
   'Omloop Het Nieuwsblad Elite',
   'Vuelta a Andalucia Ruta Ciclista Del Sol',
   'Clasica de Almeria'],
  'Simon Verger': ["Grand Prix d'Isbergues - Pas de Calais",
   'National Championships France - ITT'],
  'Stijn Vandenbergh': ['BinckBank Tour',
   'Paris-Chauny ',
   'National Championships Belgium - Road Race',
   "Grand Prix d'Isbergues - Pas de Calais",
   'Brussels Cycling Classic',
   'Bretagne Classic - Ouest-France',
   'VOO-Tour de Wallonie',
   'Milano-Sanremo',
   'Milano-Torino',
   'Vuelta a Andalucia Ruta Ciclista Del Sol',
   'Clasica de Almeria',
   'Volta a la Comunitat Valenciana'],
  'Tony Gallopin': ["Giro d'Italia",
   'Skoda-Tour de Luxembourg',
   'Tour du Doubs',
   'National Championships France - Road Race',
   'Critérium du Dauphiné',
   'Mont Ventoux Dénivelé Challenge',
   "La Route d'Occitanie - La Dépêche du Midi",
   'Volta a la Comunitat Valenciana']}}

Answer

Consider a pandas solutions by migrating your dictionary with concat and then run a self-join (to use SQL speak) on itself avoiding reverse duplicates for final count of rider pairs with groupby:

Data

# BUILD DATA FRAME FROM DICTIONARY
riders_df = pd.concat([pd.DataFrame({'rider': k, 'race': r}) for k, r in data['Races'].items()],
                      ignore_index=True)

riders_df.head()
#               rider                                         race
# 0  Alexandre Geniez                                 Paris-Chauny
# 1  Alexandre Geniez                              Paris-Camembert
# 2  Alexandre Geniez  Tour Poitou-Charentes en Nouvelle Aquitaine
# 3  Alexandre Geniez    National Championships France - Road Race
# 4  Alexandre Geniez              Mont Ventoux Dénivelé Challenge

riders_df.tail()
#              rider                                       race
# 305  Tony Gallopin  National Championships France - Road Race
# 306  Tony Gallopin                      Critérium du Dauphiné
# 307  Tony Gallopin            Mont Ventoux Dénivelé Challenge
# 308  Tony Gallopin  La Route d'Occitanie - La Dépêche du Midi
# 309  Tony Gallopin            Volta a la Comunitat Valenciana

Self join

# MERGE ON ITSELF AND AVOID REVERSE DUPLICATES
match_races_df = (pd.merge(riders_df, riders_df, how='inner', on='race', suffixes = ['', '_'])
                    .query("rider > rider_")
                 )

match_races_df.head()
#                   rider           race            rider_
# 7       Alexis Gougeard  Paris-Chauny   Alexandre Geniez
# 14          Axel Domont  Paris-Chauny   Alexandre Geniez
# 15          Axel Domont  Paris-Chauny    Alexis Gougeard
# 21  Clément Champoussin  Paris-Chauny   Alexandre Geniez
# 22  Clément Champoussin  Paris-Chauny    Alexis Gougeard

match_races_df.tail()
#                   rider                                        race             rider_
# 2038      Tony Gallopin             Volta a la Comunitat Valenciana      Oliver Naesen
# 2039      Tony Gallopin             Volta a la Comunitat Valenciana  Stijn Vandenbergh
# 2045      Oliver Naesen  National Championships Belgium - Road Race    Lawrence Naesen
# 2048  Stijn Vandenbergh  National Championships Belgium - Road Race    Lawrence Naesen
# 2049  Stijn Vandenbergh  National Championships Belgium - Road Race      Oliver Naesen

Aggregation

# AGGREGATE DATA BY RIDER PAIRS                    
agg_df = match_races_df.groupby(['rider', 'rider_'], as_index=False)['race'].count()

# SHOW RIDERS WITH SEVEN OR MORE SHARED RACES
agg_df.loc[agg_df['race'] >= 7]
#                  rider                  rider_  race
# 37   Clément Venturini         Alexis Gougeard     7
# 43        Dorian Godon         Alexis Gougeard     8
# 65   Geoffrey Bouchard         Andrea Vendrame     7
# 68   Geoffrey Bouchard            Ben Gastauer     8
# 93        Julien Duval         Alexis Gougeard     7
# 100       Julien Duval            Dorian Godon     9
# 110    Lawrence Naesen       Clément Venturini     7
# 114    Lawrence Naesen            Julien Duval    11
# 134      Mikaël Cherel        Benoît Cosnefroy    10
# 146        Nans Peters  Aurélien Paret-Peintre     7
# 148        Nans Peters        Benoît Cosnefroy     7
# 157        Nans Peters           Mikaël Cherel     7
# 163      Oliver Naesen       Clément Venturini     7
# 165      Oliver Naesen            Julien Duval     8
# 166      Oliver Naesen         Lawrence Naesen    11
# 175      Pierre Latour        Benoît Cosnefroy     7
# 186   Quentin Jauregui         Alexis Gougeard     7
# 214      Romain Bardet        Benoît Cosnefroy     8
# 243     Silvan Dillier           Oliver Naesen     7
# 259  Stijn Vandenbergh            Julien Duval     8
# 260  Stijn Vandenbergh         Lawrence Naesen    10
# 262  Stijn Vandenbergh           Oliver Naesen     8