Большой туториал по обработке спортивных данных на python

в 20:42, , рубрики: pandas, python, анализ данных, векторизация, визуализация данных, Восстановление данных, парсинг сайтов, скраппинг, спорт, статистика

Большой туториал по обработке спортивных данных на python - 1

Последние пару лет в свободное время занимаюсь триатлоном. Этот вид спорта очень популярен во многих странах мира, в особенности в США, Австралии и Европе. В настоящее время набирает стремительную популярность в России и странах СНГ. Речь идет о вовлечении любителей, не профессионалов. В отличие от просто плавания в бассейне, катания на велосипеде и пробежек по утрам, триатлон подразумевает участие в соревнованиях и системной подготовке к ним, даже не будучи профессионалом. Наверняка среди ваших знакомых уже есть по крайней мере один “железный человек” или тот, кто планирует им стать. Массовость, разнообразие дистанций и условий, три вида спорта в одном – все это располагает к образованию большого количества данных. Каждый год в мире проходит несколько сотен соревнований по триатлону, в которых участвует несколько сотен тысяч желающих. Соревнования проводятся силами нескольких организаторов. Каждый из них, естественно, публикует результаты у себя. Но для спортсменов из России и некоторых стран СНГ, команда tristats.ru собирает все результаты в одном месте – на своем одноименном сайте. Это делает очень удобным поиск результатов, как своих, так и своих друзей и соперников, или даже своих кумиров. Но для меня это дало еще и возможность сделать анализ большого количества результатов программно. Результаты опубликиваны на трилайфе: почитать.

Это был мой первый проект подобного рода, потому как лишь недавно я начал заниматься анализом данных в принципе, а также использовать python. Поэтому хочу рассказать вам о техническом исполнении этой работы, тем более что в процессе то и дело всплывали различные нюансы, требующие иногда особого подхода. Здесь будет про скраппинг, парсинг, приведение типов и форматов, восстановление неполных данных, формирование репрезентативной выборки, визуализацию, векторизацию и даже параллельные вычисления.

Объем получился большой, поэтому я разбил все на пять частей, чтобы можно было дозировать информацию и запомнить, откуда начать после перерыва.

Перед тем как двинуться дальше, лучше сначала прочитать мою статью с результатами исследования, потому как здесь по сути описана кухня по ее созданию. Это займет 10-15 минут.

Прочитали? Тогда поехали!

Часть 1. Скраппинг и парсинг

Дано: Сайт tristats.ru. На нем два вида таблиц, которые нас интересуют. Это собственно сводная таблица всех гонок и протокол результатов каждой из них.

Большой туториал по обработке спортивных данных на python - 2

Большой туториал по обработке спортивных данных на python - 3

Задачей номер один было получить эти данные программно и сохранить их для дальнейшей обработки. Так получилось, что я был на тот момент плохо знаком с веб технологиями и поэтому не знал сразу как это сделать. Начал соответственно с того, что знал – посмотреть код страницы. Это можно сделать использую правую кнопку мыши или клавишу F12.

Большой туториал по обработке спортивных данных на python - 4

Меню в Chrome содержит два пункта Просмотр кода страницы и Посмотреть код. Не самое очевидное разделение. Естественно, они дают разные результаты. Тот, что Посмотреть код, как раз и есть то же самое, что и F12 — непосредственно текстовое html-представление того, что отображено в браузере, поэлементно.

Большой туториал по обработке спортивных данных на python - 5

В свою очередь Просмотр кода страницы выдает исходный код страницы. Тоже html, но никаких данных там нет, только названия скриптов JS, которые их выгружают. Ну ладно.

Большой туториал по обработке спортивных данных на python - 6

Теперь надо понять, как с помощью python сохранить код каждой страницы в виде отдельного текстового файла. Пробую так:

import requests

r = requests.get(url='http://tristats.ru/')
print(r.content)

И получаю… исходный код. Но мне то нужен результат его исполнения. Поизучав, поискав и поспрашивав, я понял, что мне нужен инструмент для автоматизации действий браузера, например — selenium. Его я и поставил. А также ChromeDriver для работы с Google Chrome. Далее использовал его следующим образом:

from selenium import webdriver
from selenium.webdriver.chrome.service import Service

service = Service(r'C:ChromeDriverchromedriver.exe')
service.start()
driver = webdriver.Remote(service.service_url)
driver.get('http://www.tristats.ru/')
print(driver.page_source)
driver.quit()

Этот код запускает окно браузера и открывает в нем страницу по заданному url. В результате получаем html код уже с вожделенными данными. Но есть одна загвоздка. В полученном результате только 100 записей, а всего гонок почти 2000. Как же так? Дело в том, что изначально в браузере отображаются лишь первые 100 записей, и только если прокрутить до самого низа страницы, загружаются следующие 100, и так далее. Стало быть, надо реализовать прокрутку программно. Для этого воспользуемся командой:

driver.execute_script("window.scrollTo(0, document.body.scrollHeight);")

И при каждом прокручивании будем проверять, изменился ли код загруженной страницы или нет. Если он не изменился, для надежности проверим несколько раз, например 10, то значит страница загружена целиком и можно остановиться. Между прокрутками установим таймаут в одну секунду, чтобы страница успела загрузиться. (Даже если не успеет, у нас есть запас – еще девять секунд).

А полностью код будет выглядеть так:

from selenium import webdriver
from selenium.webdriver.chrome.service import Service
import time

service = Service(r'C:ChromeDriverchromedriver.exe')
service.start()
driver = webdriver.Remote(service.service_url)
driver.get('http://www.tristats.ru/')
prev_html = ''
scroll_attempt = 0

while scroll_attempt < 10:
    driver.execute_script("window.scrollTo(0, document.body.scrollHeight);")
    time.sleep(1)

    if prev_html == driver.page_source:
        scroll_attempt += 1
    else:
        prev_html = driver.page_source
        scroll_attempt = 0 

with open(r'D:trisummary.txt', 'w') as f:
    f.write(prev_html)

driver.quit()

Итак, у нас есть html файл со сводной таблицей всех гонок. Нужно его распарсить. Для этого используем библиотеку lxml.

from lxml import html

Сначала находим все строки таблицы. Чтобы определить признак строки, просто смотрим html файл в текстовом редакторе.

Большой туториал по обработке спортивных данных на python - 7

Это может быть, например, «tr ng-repeat=’r in racesData’ class=’ng-scope’» или какой – то фрагмент, который больше не встречается ни в каких тегах.

with open(r'D:trisummary.txt', 'r') as f:
    sum_html = f.read()

tree = html.fromstring(sum_html)
rows = tree.findall(".//*[@ng-repeat='r in racesData']")

затем заводим pandas dataframe и каждый элемент каждой строки таблица записываем в этот датафрейм.

import pandas as pd

rs = pd.DataFrame(columns=['date','name','link','males','females','rus','total'], index=range(len(rows))) #rs – races summary

Для того, чтобы разобраться, где спрятан каждый конкретный элемент, нужно просто посмотреть на html код одного из элементов наших rows в том же текстовом редакторе.

<tr ng-repeat="r in racesData" class="ng-scope">
  <td class="ng-binding">2015-04-26</td>
    <td>
      <img src="/Images/flags/24/USA.png" class="flag">
      <a href="/rus/result/ironman/texas/half/2015" target="_self" class="ng-binding">Ironman Texas 70.3 2015</a>
    </td>
    <td>
      <a href="/rus/result/ironman/texas/half/2015?sex=F" target="_self" class="ng-binding">605</a>
      <i class="fas fa-venus fa-lg" style="color:Pink"></i>
      /
      <a href="/rus/result/ironman/texas/half/2015?sex=M" target="_self" class="ng-binding">1539</a>
      <i class="fas fa-mars fa-lg" style="color:LightBlue"></i>
    </td>
    <td class="ng-binding">
      <img src="/Images/flags/24/rus.png" class="flag">
      <!-- ngIf: r.CountryCount == 0 -->
      <!-- ngIf: r.CountryCount > 0 --><a ng-if="r.CountryCount > 0" href="/rus/result/ironman/texas/half/2015?country=rus" target="_self" class="ng-binding ng-scope">2</a>
      <!-- end ngIf: r.CountryCount > 0 -->
      / 2144
  </td>
</tr>

Здесь проще всего захардкодить навигацию по дочерним элементам, их не так много.

for i in range(len(rows)):
    rs.loc[i,'date'] = rows[i].getchildren()[0].text.strip()
    rs.loc[i,'name'] = rows[i].getchildren()[1].getchildren()[1].text.strip()
    rs.loc[i,'link'] = rows[i].getchildren()[1].getchildren()[1].attrib['href'].strip()
    rs.loc[i,'males'] = rows[i].getchildren()[2].getchildren()[2].text.strip()
    rs.loc[i,'females'] = rows[i].getchildren()[2].getchildren()[0].text.strip()
    rs.loc[i,'rus'] = rows[i].getchildren()[3].getchildren()[3].text.strip()
    rs.loc[i,'total'] = rows[i].getchildren()[3].text_content().split('/')[1].strip()

Вот что получилось в итоге:

date event link males females rus total
0 2020-07-02 Ironman Dubai Duathlon 70.3 2020 /rus/result/ironman/dubai-duathlon/half/2020 835 215 65 1050
1 2020-02-07 Ironman Dubai 70.3 2020 /rus/result/ironman/dubai/half/2020 638 132 55 770
2 2020-01-29 Israman Half 2020 /rus/result/israman/israman/half/2020 670 126 4 796
3 2019-12-08 Ironman Indian Wells La Quinta 70.3 2019 /rus/result/ironman/indian-wells-la-quinta/hal... 1590 593 6 2183
4 2019-12-07 Ironman Taupo 70.3 2019 /rus/result/ironman/taupo/half/2019 767 420 3 1187
... ... ... ... ... ... ... ...
1917 1994-07-02 ITU European Championship Eichstatt Olympic 1994 /rus/result/itu/european-championship-eichstat... 61 0 2 61
1918 1993-09-04 Challenge Almere-Amsterdam Long 1993 /rus/result/challenge/almere-amsterdam/full/1993 795 32 1 827
1919 1993-07-04 ITU European Cup Echternach Olympic 1993 /rus/result/itu/european-cup-echternach/olympi... 60 0 2 60
1920 1992-09-12 ITU World Championship Huntsville Olympic 1992 /rus/result/itu/world-championship-huntsville/... 317 0 3 317
1921 1990-09-15 ITU World Championship Orlando Olympic 1990 /rus/result/itu/world-championship-orlando/oly... 286 0 5 28


Сохраняем этот датафрейм в файл. Я использую pickle, но это может быть csv, или что-то еще.

import pickle as pkl

with open(r'D:trisummary.pkl', 'wb') as f:
    pkl.dump(df,f)

На данном этапе все данные имеют строковый тип. Конвертировать будем позже. Самое главное, что нам сейчас нужно, это ссылки. Их будем использовать для скраппинга протоколов всех гонок. Делаем его по образу и подобию того, как это было сделано для сводной таблицы. В цикле по всем гонкам для каждой будем открывать страницу по ссылке, прокручивать и получать код страницы. В сводной таблице у нас есть информация по общему количеству участников в гонке – total, будем ее использовать для того, чтобы понять до какого момента нужно продолжать скроллить. Для этого будем прямо в процессе скраппинга каждой страницы определять количество записей в таблице и сравнивать его с ожидаемым значением total. Как только оно будет равно, значит мы доскроллили до конца и можно переходить к следующей гонке. Так же поставим таймаут – 60 сек. Ели за это время мы не добираемся до total, переходим к следующей гонке. Код страницы будем сохранять в файл. Будем сохранять файлы всех гонок в одной папке, а называть их по имени гонок, то есть по значению в колонке event в сводной таблице. Чтобы не было конфликта имен, нужно чтобы все гонки имели разные названия в сводной таблице. Проверим это:

df[df.duplicated(subset = 'event', keep=False)]

date event link males females rus total
450 2018-07-15 A1 Шлиссельбург Sprint 2018 /rus/result/a1/шлиccельбург/sprint/2018-07-15 43 15 47 58
483 2018-06-23 A1 Шлиссельбург Sprint 2018 /rus/result/a1/шлиccельбург/sprint/2018-06-23 61 15 76 76
670 2017-07-30 3Grom Кленово Olympic 2017 /rus/result/3grom/кленово/olympic/2017-07-30 249 44 293 293
752 2017-06-11 3Grom Кленово Olympic 2017 /rus/result/3grom/кленово/olympic/2017-06-11 251 28 279 279

Что ж, в сводной таблице есть повторения, причем, даты, и количества участников (males, females, rus, total), и ссылки разные. Нужно проверить эти протоколы, здесь их немного, так что можно сделать это вручную.

Большой туториал по обработке спортивных данных на python - 8

date event link males females rus total
450 2018-07-15 A1 Шлиссельбург Sprint 7 2018 /rus/result/a1/шлиccельбург/sprint/2018-07-15 43 15 47 58
483 2018-06-23 A1 Шлиссельбург Sprint 6 2018 /rus/result/a1/шлиccельбург/sprint/2018-06-23 61 15 76 76
670 2017-07-30 3Grom Кленово Olympic 7 2017 /rus/result/3grom/кленово/olympic/2017-07-30 249 44 293 293
752 2017-06-11 3Grom Кленово Olympic 6 2017 /rus/result/3grom/кленово/olympic/2017-06-11 251 28 279 27

Теперь все названия уникальны, запускаем большой майнинг-цикл:

service.start()
driver = webdriver.Remote(service.service_url)
timeout = 60

for index, row in df.iterrows():
    try:
        driver.get('http://www.tristats.ru' + row['link'])     
        start = time.time()

        while True:
            driver.execute_script("window.scrollTo(0, document.body.scrollHeight);")
            time.sleep(1)
            race_html = driver.page_source
            tree = html.fromstring(race_html)
            race_rows = tree.findall(".//*[@ng-repeat='r in resultsData']")

            if len(race_rows) == int(row['total']):
                break
            if time.time() - start > timeout:
                print('timeout')
                break

        with open(os.path.join(r'D:triraces', row['event'] +  '.txt'), 'w') as f:
            f.write(race_html)

    except:
        traceback.print_exc()

    time.sleep(1)
    
driver.quit()

Это долгий процесс. Но когда все настроено и этот тяжелый механизм начинает вращение, один за другим добавляя файлики с данными, наступает чувство приятного волнения. В минуту загружается всего примерно по три протокола, очень медленно. Оставил крутиться на ночь. На все понадобилось около 10 часов. К утру была закачана бoльшая часть протоколов. Как это обычно бывает при работе с сетью, на нескольких случился сбой. Быстро докачал их повторной попыткой.

Большой туториал по обработке спортивных данных на python - 9

Итак, мы имеем 1 922 файла общим объемом почти 3 GB. Круто! Но обработка почти 300 гонок закончилась таймаутом. В чем же дело? Выборочно проверяем, оказывается, что действительно значение total из сводной таблицы и количество записей в протоколе гонки, которые мы проверяли, могут не совпадать. Это печально, потому что непонятно в чем причина такого расхождения. То ли это из-за того, что не все финишируют, то ли какой-то баг в базе. В общем первый сигнал о неидеальности данных. В любом случае, проверяем те, в которых количество записей равняется 100 или 0, это самые подозрительные кандидаты. Таких оказалось восемь. Закачиваем их заново под пристальным контролем. Кстати, в двух из них реально по 100 записей.

Ну что ж, все данные у нас. Переходим к парсингу. Опять же в цикле будем пробегать по каждой гонке, читать файл и сохранять содержимое в pandas DataFrame. Эти датафреймы объединим в dict, в котором ключами будут названия гонок – то есть значения event из сводной таблицы или названия файлов с html кодом страниц гонок, они совпадают.

rd = {} #rd – race details

for e in rs['event']:
    place = []
    ... sex = [], name=..., country, group, place_in_group, swim, t1, bike, t2, run
    result = []

    with open(os.path.join(r'D:triraces', e + '.txt'), 'r')
        race_html = f.read()

    tree = html.fromstring(race_html)
    rows = tree.findall(".//*[@ng-repeat='r in resultsData']")

    for j in range(len(rows)):
        row = rows[j]
        parts = row.text_content().split('n')
        parts = [r.strip() for r in parts if r.strip() != '']
        place.append(parts[0])

        if len([a for a in row.findall('.//i')]) > 0:
            sex.append([a for a in row.findall('.//i')][0].attrib['ng-if'][10:-1])
        else:
            sex.append('')

        name.append(parts[1])

        if len(parts) > 10:
            country.append(parts[2].strip())         
            k=0
        else:
            country.append('')
            k=1

        group.append(parts[3-k])
        ... place_in_group.append(...), swim.append ..., t1, bike, t2, run
        result.append(parts[10-k])

    race = pd.DataFrame()
    race['place'] = place
    ... race['sex'] = sex, race['name'] = ..., 'country', 'group', 'place_in_group', 'swim', ' t1', 'bike', 't2', 'run'
    race['result'] = result

    rd[e] = race

with open(r'D:tridetails.pkl', 'wb') as f:
    pkl.dump(rd,f)

place sex name country group place in group swim t1 bike t2 run result
0 1 M Reed, Tim AUS MPRO 1 24:34 1:07 2:13:46 1:49 1:23:17 4:04:33
1 2 M Van Berkel, Tim AUS MPRO 2 24:34 1:05 2:13:47 1:53 1:27:17 4:08:36
2 3 M Baldwin, Nicholas SEY MPRO 3 26:31 0:59 2:14:06 1:54 1:25:36 4:09:06
3 4 M Polizzi, Alexander AUS MPRO 4 23:21 1:12 2:14:53 1:54 1:31:16 4:12:36
4 5 M Chang, Chia-Hao TWN M18-24 1 25:18 1:34 2:23:38 2:13 1:29:01 4:21:44
5 6 M Rondy, Guillaume FRA M35-39 1 27:51 1:26 2:21:53 2:29 1:35:19 4:28:58
6 7 F Steffen, Caroline SUI FPRO 1 26:52 1:01 2:24:54 2:10 1:34:17 4:29:14
7 8 M Betten, Sam AUS MPRO 5 23:30 1:26 2:18:24 1:57 1:45:07 4:30:24
8 9 M Gallot, Simon FRA M30-34 1 27:50 1:33 2:20:15 2:13 1:45:22 4:37:13
... ... ... ... ... ... ... ... ... ... ... ... ...
524 525 M Santos, Alfredo PHI M65-69 2 50:42 4:23 3:52:10 10:32 3:36:11 8:33:58
525 526 F Escober, Eula PHI F18-24 5 47:07 3:50 4:43:44 3:41 2:59:45 8:38:07
526 527 M Belen, Virgilio Jr. PHI M45-49 76 47:05 5:49 3:48:18 11:21 3:46:06 8:38:39
527 528 M Kunimoto, Kilhak GUM M70-74 2 40:32 2:50 3:53:37 6:45 4:01:36 8:45:20
528 529 M Sumicad, Siegfred PHI M50-54 54 59:10 4:38 4:11:55 6:35 3:23:45 8:46:03
529 530 M Gomez, Paul PHI M45-49 77 50:02 6:29 4:07:58 7:24 3:41:41 8:53:34
530 531 M Ramos, John Raymund PHI M25-29 26 43:44 3:04 4:21:13 5:56 3:45:10 8:59:07
531 532 F De Guzman, Clouie Anne PHI F30-34 9 52:29 3:16 4:03:02 7:01 3:56:39 9:02:27
532 533 F Samson, Maria Dolores PHI F45-49 17 48:56 4:21 4:16:34 6:26 3:47:06 9:03:23
533 534 M Salazar, Richard PHI M40-44 107 42:19 4:02 4:30:36 6:39 3:39:51 9:03:27

Помимо таблицы с результатами участников, html файл каждой гонки содержит еще и дату, название и место проведения соревнования. Дата и название уже есть в сводной таблице, а вот локации нет. Считаем эту информацию из html файлов и добавим в новую колонку в сводной таблице.

for index, row in rs.iterrows():
    e = row['event']       
    with open(os.path.join(r'D:triraces', e + '.txt'), 'r') as f:
        race_html = f.read()

    tree = html.fromstring(race_html)
    header_elem = [tb for tb in tree.findall('.//tbody') if tb.getchildren()[0].getchildren()[0].text == 'Дата'][0]
    location = header_elem.getchildren()[1].getchildren()[1].text.strip()
    rs.loc[index, 'loc'] = location

event date loc male female rus total link
0 Ironman Dubai Duathlon 70.3 2020 2020-07-02 Dubai, United Arab Emirates 835 215 65 1050 ...
1 Ironman Dubai 70.3 2020 2020-02-07 Dubai, United Arab Emirates 638 132 55 770 ...
2 Israman Half 2020 2020-01-29 Israel, Eilat 670 126 4 796 ...
3 Ironman Indian Wells La Quinta 70.3 2019 2019-12-08 Indian Wells/La Quinta, California, USA 1590 593 6 2183 ...
4 Ironman Taupo 70.3 2019 2019-12-07 New Zealand 767 420 3 1187 ...
5 Ironman Bahrain 70.3 2019 2019-12-07 Manama, Bahrain 858 214 38 1072 ...
6 Ironman Western Australia 2019 2019-12-01 Busselton, Western Australia 940 229 1 1169 ...
7 Ironman Mar del Plata 2019 2019-12-01 Mar del Plata, Argentina 506 66 3 572 ...
8 Ironman Cozumel 2019 2019-11-24 Cozumel, Mexico 1158 395 12 1553 ...
9 Ironman Arizona 2019 2019-11-24 Tempe, Arizona, USA 1697 633 3 2330 ...
10 Ironman Xiamen 70.3 2019 2019-11-10 Xiamen, China 897 170 14 1067 ...

Сохраняем. В новый файл.

with open(r'D:trisummary1.pkl', 'wb') as f:
    pkl.dump(df,f)

Часть 2. Приведение типов и форматирование

Итак, мы скачали все данные и поместили их в датафреймы. Однако все значения имеют тип str. Это относится и к дате, и к результатам, и к локации, и ко всем остальным параметрам. Необходимо привести все параметры к соответствующим типам.

Начнем со сводной таблицы.

event date loc male female rus total link
0 Ironman Dubai Duathlon 70.3 2020 2020-07-02 Dubai, United Arab Emirates 835 215 65 1050 ...
1 Ironman Dubai 70.3 2020 2020-02-07 Dubai, United Arab Emirates 638 132 55 770 ...
2 Israman Half 2020 2020-01-29 Israel, Eilat 670 126 4 796 ...
3 Ironman Indian Wells La Quinta 70.3 2019 2019-12-08 Indian Wells/La Quinta, California, USA 1590 593 6 2183 ...
4 Ironman Taupo 70.3 2019 2019-12-07 New Zealand 767 420 3 1187 ...
5 Ironman Bahrain 70.3 2019 2019-12-07 Manama, Bahrain 858 214 38 1072 ...
6 Ironman Western Australia 2019 2019-12-01 Busselton, Western Australia 940 229 1 1169 ...
7 Ironman Mar del Plata 2019 2019-12-01 Mar del Plata, Argentina 506 66 3 572 ...
8 Ironman Cozumel 2019 2019-11-24 Cozumel, Mexico 1158 395 12 1553 ...
9 Ironman Arizona 2019 2019-11-24 Tempe, Arizona, USA 1697 633 3 2330 ...
10 Ironman Xiamen 70.3 2019 2019-11-10 Xiamen, China 897 170 14 1067 ...
... ... ... ... ... ... ... ... ...

Дата и время

event, loc и link оставим как есть. date конвертируем в pandas datetime следующим образом:

rs['date'] = pd.to_datetime(rs['date'])

Остальные приводим к целочисленному типу:

cols = ['males', 'females', 'rus', 'total']
rs[cols] = rs[cols].astype(int)

Все прошло гладко, никаких ошибок не возникло. Значит все OK — cохраняемся:

with open(r'D:trisummary2.pkl', 'wb') as f:
    pkl.dump(rs, f)

Теперь датафреймы гонок. Поскольку все гонки удобнее и быстрее обрабатывать разом, а не по одной, соберем их в один большой датафрейм ar (сокращение от all records) с помощью метода concat.

ar = pd.concat(rd) 

ar содержит 1 416 365 записей.

Теперь конвертируем place и place in group в целочисленное значение.

ar[['place', 'place in group']] = ar[['place', 'place in group']].astype(int))

Далее, обработаем колонки с временными значениями. Будем приводить их в типу Timedelta из pandas. Но чтобы конвертация прошла успешно, нужно правильно подготовить данные. Можно видеть, что некоторые значения, которые меньше часа идут без указания того самого часка.

place sex name country group place in group swim t1 bike t2 run result
0 1 M Dejan Patrcevic CRO M40-44 1 29:03 2:50 2:09:17 1:37 1:22:06 4:04:51
1 2 M Lukas Krpec CZE M35-39 1 29:00 2:40 2:07:01 1:48 1:25:48 4:06:15
2 3 M Marin Koceic CRO M40-44 2 27:34 2:09 2:12:13 1:30 1:27:19 4:10:44

Нужно его добавить.

for col in ['swim', 't1', 'bike', 't2', 'run', 'result']:
    strlen = ar[col].str.len()
    ar.loc[strlen==5, col] = '0:' + ar.loc[strlen==5, col]
    ar.loc[strlen==4, col] = '0:0' + ar.loc[strlen==4, col]

Теперь времена, все еще оставаясь строками, выглядят так:

place sex name country group place in group swim t1 bike t2 run result
0 1 M Dejan Patrcevic CRO M40-44 1 0:29:03 0:02:50 2:09:17 0:01:37 1:22:06 4:04:51
1 2 M Lukas Krpec CZE M35-39 1 0:29:00 0:02:40 2:07:01 0:01:48 1:25:48 4:06:15
2 3 M Marin Koceic CRO M40-44 2 0:27:34 0:02:09 2:12:13 0:01:30 1:27:19 4:10:44

Конвертируем в Timedelta:

for col in ['swim', 't1', 'bike', 't2', 'run', 'result']:
    ar[col] = pd.to_timedelta(ar[col])

Пол

Идем дальше. Проверим что в колонке sex есть только значения M и F:

ar['sex'].unique() 

Out: ['M', 'F', '']

На самом деле там еще пустая строка, то есть пол не указан. Посмотрим сколько таких случаев:

len(ar[ar['sex'] == '']) 

Out: 2538

Не так много — хорошо. В дальнейшем мы попытаемся еще уменьшить это значение. А пока оставим колонку sex как есть в виде строк. Сохраним результат, перед тем как перейти к более серьезным и рискованным преобразованиям. Для того, чтобы сохранять преемственность между файлами, преобразуем объединенный датафрейм ar обратно в словарь датафреймов rd:

for event in ar.index.get_level_values(0).unique():
    rd[event] = ar.loc[event]

with open(r'D:tridetails1.pkl', 'wb') as f:
    pkl.dump(rd,f)

Кстати, за счет преобразования типов некоторых колонок размеры файлов уменьшились с 367 KB до 295 KB для сводной таблицы и с 251 MB до 168 MB для протоколов гонок.

Код страны

Теперь посмотрим страну.

ar['country'].unique()

Out: ['CRO', 'CZE', 'SLO', 'SRB', 'BUL', 'SVK', 'SWE', 'BIH', 'POL', 'MK', 'ROU', 'GRE', 'FRA', 'HUN', 'NOR', 'AUT', 'MNE', 'GBR', 'RUS', 'UAE', 'USA', 'GER', 'URU', 'CRC', 'ITA', 'DEN', 'TUR', 'SUI', 'MEX', 'BLR', 'EST', 'NED', 'AUS', 'BGI', 'BEL', 'ESP', 'POR', 'UKR', 'CAN', 'IRL', 'JPN', 'HKG', 'JEY', 'SGP', 'BRA', 'QAT', 'LUX', 'RSA', 'NZL', 'LAT', 'PHI', 'KSA', 'SEY', 'MAS', 'OMA', 'ARG', 'ECU', 'THA', 'JOR', 'BRN', 'CIV', 'FIN', 'IRN', 'BER', 'LBA', 'KUW', 'LTU', 'SRI', 'HON', 'INA', 'LBN', 'PAN', 'EGY', 'MLT', 'WAL', 'ISL', 'CYP', 'DOM', 'IND', 'VIE', 'MRI', 'AZE', 'MLD', 'LIE', 'VEN', 'ALG', 'SYR', 'MAR', 'KZK', 'PER', 'COL', 'IRQ', 'PAK', 'CZK', 'KAZ', 'CHN', 'NEP', 'ISR', 'MKD', 'FRO', 'BAN', 'ARU', 'CPV', 'ALB', 'BIZ', 'TPE', 'KGZ', 'BNN', 'CUB', 'SNG', 'VTN', 'THI', 'PRG', 'KOR', 'RE', 'TW', 'VN', 'MOL', 'FRE', 'AND', 'MDV', 'GUA', 'MON', 'ARM', 'F.I.TRI.', 'BAHREIN', 'SUECIA', 'REPUBLICA CHECA', 'BRASIL', 'CHI', 'MDA', 'TUN', 'NDL', 'Danish(Dane)', 'Welsh', 'Austrian', 'Unknown', 'AFG', 'Argentinean', 'Pitcairn', 'South African', 'Greenland', 'ESTADOS UNIDOS', 'LUXEMBURGO', 'SUDAFRICA', 'NUEVA ZELANDA', 'RUMANIA', 'PM', 'BAH', 'LTV', 'ESA', 'LAB', 'GIB', 'GUT', 'SAR', 'ita', 'aut', 'ger', 'esp', 'gbr', 'hun', 'den', 'usa', 'sui', 'slo', 'cze', 'svk', 'fra', 'fin', 'isr', 'irn', 'irl', 'bel', 'ned', 'sco', 'pol', 'SMR', 'mex', 'STEEL T BG', 'KINO MANA', 'IVB', 'TCH', 'SCO', 'KEN', 'BAS', 'ZIM', 'Joe', 'PUR', 'SWZ', 'Mark', 'WLS', 'MYA', 'BOT', 'REU', 'NAM', 'NCL', 'BOL', 'GGY', 'ISV', 'TWN', 'GUM', 'FIJ', 'COK', 'NGR', 'IRI', 'GAB', 'ANT', 'GEO', 'COG', 'sue', 'SUD', 'BAR', 'CAY', 'BO', 'VE', 'AX', 'MD', 'PAR', 'UM', 'SEN', 'NIG', 'RWA', 'YEM', 'PLE', 'GHA', 'ITU', 'UZB', 'MGL', 'MAC', 'DMA', 'TAH', 'TTO', 'AHO', 'JAM', 'SKN', 'GRN', 'PRK', 'NFK', 'SOL', 'Sandy', 'SAM', 'PNG', 'SGS', 'Suchy, Jorg', 'SOG', 'GEQ', 'BVT', 'DJI', 'CHA', 'ANG', 'YUG', 'IOT', 'HAI', 'SJM', 'CUW', 'BHU', 'ERI', 'FLK', 'HMD', 'GUF', 'ESH', 'sandy', 'UMI', 'selsmark, 'Alise', 'Eddie', '31/3, Colin', 'CC', 'Индия', 'Ирландия', 'Армения', 'Болгария', 'Сербия', 'Республика Беларусь', 'Великобритания', 'Франция', 'Гондурас', 'Коста-Рика', 'Азербайджан', 'GRL', 'UGA', 'VAT', 'ETH', 'ASA', 'PYF', 'ATA', 'ALA', 'MTQ', 'ZZ', 'CXR', 'AIA', 'TJK', 'GUY', 'KR', 'PF', 'BN', 'MO', 'LA', 'CAM', 'NCA', 'ZAM', 'MAD', 'TOG', 'VIR', 'ATF', 'VAN', 'SLE', 'GLP', 'SCG', 'LAO', 'IMN', 'BUR', 'IR', 'SY', 'CMR', 'GBS', 'SUR', 'MOZ', 'BLM', 'MSR', 'CAF', 'BEN', 'COD', 'CCK', 'TUV', 'TGA', 'GI', 'XKX', 'NRU', 'NC', 'LBR', 'TAN', 'VIN', 'SSD', 'GP', 'PS', 'IM', 'JE', '', 'MLI', 'FSM', 'LCA', 'GMB', 'MHL', 'NH', 'FL', 'CT', 'UT', 'AQ', 'Korea', 'Taiwan', 'NewCaledonia', 'Czech Republic', 'PLW', 'BRU', 'RUN', 'NIU', 'KIR', 'SOM', 'TKM', 'SPM', 'BDI', 'COM', 'TCA', 'SHN', 'DO2', 'DCF', 'PCN', 'MNP', 'MYT', 'SXM', 'MAF', 'GUI', 'AN', 'Slovak republic', 'Channel Islands', 'Reunion', 'Wales', 'Scotland', 'ica', 'WLF', 'D', 'F', 'I', 'B', 'L', 'E', 'A', 'S', 'N', 'H', 'R', 'NU', 'BES', 'Bavaria', 'TLS', 'J', 'TKL', 'Tirol"', 'P', '?????', 'EU', 'ES-IB', 'ES-CT', 'КГЫ', 'SOO', 'LZE', 'Могилёв', 'Гомель', 'Минск', 'Самара', 'Гродно', 'Москва']

412 уникальных значений.

В основном страна обозначается трехзначным буквенным кодом в верхнем регистре. Но как видно, далеко не всегда. На самом деле существует международный стандарт ISO 3166, в котором для всех стран, включая даже те, которых уже не существует, прописаны соответствующие трехзначные и двузначные коды. Для python одну из реализаций этого стандарта можно найти в пакете pycountry. Вот как он работает:

import pycountry as pyco

pyco.countries.get(alpha_3 = 'RUS')

Out: Country(alpha_2='RU', alpha_3='RUS', name='Russian Federation', numeric='643')

Таким образом проверим все трехзначные коды, приведя к верхнему регистру, которые дают отклик в countries.get(…) и historic_countries.get(…):

valid_a3 = [c for c in ar['country'].unique() if pyco.countries.get(alpha_3 = c.upper()) != None or pyco.historic_countries.get(alpha_3 = c.upper()) != None])

Таких оказалось 190 из 412. То есть меньше половины.
Для остальных 222 (их список обозначим tofix) сделаем словарь соответствия fix, в котором ключом будет оригинальное название, а значением трехзначный код по стандарту ISO.

tofix = list(set(ar['country'].unique()) - set(valid_a3))

В первую очередь проверим двузначные коды с помощью pycountry.countries.get(alpha_2 = …), приведя к верхнему регистру:

for icc in tofix: #icc -invalid country code
    if pyco.countries.get(alpha_2 = icc.upper()) != None:
        fix[icc] = pyco.countries.get(alpha_2 = icc.upper()).alpha_3
    else:
        if pyco.historic_countries.get(alpha_2 = icc.upper()) != None:
            fix[icc] = pyco.historic_countries.get(alpha_2 = icc.upper()).alpha_3

Затем полные имена через pycountry.countries.get(name = …), pycountry.countries.get(common_name = …), приведя их к форме str.title():

for icc in tofix:
    if pyco.countries.get(common_name = icc.title()) != None:
        fix[icc] = pyco.countries.get(common_name = icc.title()).alpha_3
    else:
        if pyco.countries.get(name = icc.title()) != None:
            fix[icc] = pyco.countries.get(name = icc.title()).alpha_3
        else:
            if pyco.historic_countries.get(name = icc.title()) != None:
                fix[icc] = pyco.historic_countries.get(name = icc.title()).alpha_3

Таким образом сокращаем число нераспознанных значений до 190. Все еще достаточно много:
['URU', '', 'PAR', 'SUECIA', 'KUW', 'South African', 'Гомель', 'Austrian', 'ISV', 'H', 'SCO', 'ES-CT', Гондурас', 'GUI', 'BOT', 'SEY', 'BIZ', 'LAB', 'PUR', 'Республика Беларусь', 'Scotland', 'Азербайджан', 'Минск', 'TCH', 'TGA', 'UT', 'BAH', 'GEQ', 'NEP', 'TAH', 'ica', 'FRE', 'E', 'TOG', 'MYA', 'Болгария', 'Danish (Dane)', 'SAM', 'TPE', 'MON', 'ger', 'Unknown', 'sui', 'R', 'SUI', 'A', 'GRN', 'KZK', 'Wales', 'Москва', 'GBS', 'ESA', 'Bavaria', 'Czech Republic', '31/3, Colin', 'SOL', 'SKN', 'Франция', 'MGL', 'XKX', 'WLS', 'MOL', 'FIJ', 'CAY', 'ES-IB', 'BER', 'PLE', 'MRI', 'B', 'KSA', 'Великобритания', 'Гродно', 'LAT', 'GRE', 'ARU', 'КГЫ', 'THI', 'NGR', 'MAD', 'SOG', 'MLD', '?????', 'AHO', 'sco', 'UAE', 'RUMANIA', 'CRO', 'RSA', 'NUEVA ZELANDA', 'KINO MANA', 'PHI', 'sue', 'Tirol"', 'IRI', 'POR', 'CZK', 'SAR', 'D', 'BRASIL', 'DCF', 'HAI', 'ned', 'N', 'BAHREIN', 'VTN', 'EU', 'CAM', 'Mark', 'BUL', 'Welsh', 'VIN', 'HON', 'ESTADOS UNIDOS', 'I', 'GUA', 'OMA', 'CRC', 'PRG', 'NIG', 'BHU', 'Joe', 'GER', 'RUN', 'ALG', 'Сербия', 'Channel Islands', 'Reunion', 'REPUBLICA CHECA', 'slo', 'ANG', 'NewCaledonia', 'GUT', 'VIE', 'ASA', 'BAR', 'SRI', 'L', 'Могилёв', 'J', 'BAS', 'LUXEMBURGO', 'S', 'CHI', 'SNG', 'BNN', 'den', 'F.I.TRI.', 'STEEL T BG', 'NCA', 'Slovak republic', 'MAS', 'LZE', 'Коста-Рика', 'F', 'BRU', 'Армения', 'LBA', 'NDL', 'DEN', 'IVB', 'BAN', 'Sandy', 'ZAM', 'sandy', 'Korea', 'SOO', 'BGI', 'Индия', 'LTV', 'selsmark, Alise', 'TAN', 'NED', 'Самара', 'Suchy, Jorg', 'SLO', 'SUDAFRICA', 'ZIM', 'Eddie', 'INA', 'Ирландия', 'SUD', 'VAN', 'FL', 'P', 'ITU', 'ZZ', 'Argentinean', 'CHA', 'DO2', 'WAL']

Можно заметить, что среди них все еще много трехзначных кодов, но это не ISO. Что же тогда? Оказывается, что существует еще один стандарт – олимпийский. К сожалению, его реализация не включена в pycountry и приходится искать что-то еще. Решение нашлось в виде csv файла на datahub.io. Поместим содержимое этого файла в pandas DataFrame под названием cdf.

official name short name iso2 iso3 ioc
0 NaN Taiwan TW TWN TPE
1 Afghanistan Afghanistan AF AFG AFG
2 Albania Albania AL ALB ALB
3 Algeria Algeria DZ DZA ALG
4 American Samoa American Samoa AS ASM ASA
5 Andorra Andorra AD AND AND
6 Angola Angola AO AGO ANG
7 Anguilla Anguilla AI AIA AIA
8 Antarctica Antarctica AQ ATA NaN
9 Antigua and Barbuda Antigua & Barbuda AG ATG ANT
10 Argentina Argentina AR ARG ARG

ioc – Intenational Olympic Committee (IOC)

len(([x for x in tofix if x.upper() in list(cdf['ioc'])]))

Out: 82

Среди трехзначных кодов из tofix нашлось 82 соответствующих IOC. Добавим их в наш словарь соответствия.

for icc in tofix:
    if icc.upper() in list(cdf['ioc']):
        ind = cdf[cdf['ioc'] == icc.upper()].index[0]
        fix[icc] = cdf.loc[ind, 'iso3']

Осталось 108 необработанных значений. Их добиваем вручную, иногда обращаясь за помощью в Google.
{'BGI': 'BRB', 'WAL': 'GBR', 'MLD': 'MDA', 'KZK': 'KAZ', 'CZK': 'CZE', 'BNN': 'BEN', 'SNG': 'SGP', 'VTN': 'VNM', 'THI': 'THA', 'PRG': 'PRT', 'MOL': 'MDA', 'FRE': 'FRA', 'F.I.TRI.': 'ITA', 'BAHREIN': 'BHR', 'SUECIA': 'SWE', 'REPUBLICA CHECA': 'CZE', 'BRASIL': 'BRA', 'NDL': 'NLD', 'Danish (Dane)': 'DNK', 'Welsh': 'GBR', 'Austrian': 'AUT', 'Argentinean': 'ARG', 'South African': 'ZAF', 'ESTADOS UNIDOS': 'USA', 'LUXEMBURGO': 'LUX', 'SUDAFRICA': 'ZAF', 'NUEVA ZELANDA': 'NZL', 'RUMANIA': 'ROU', 'sco': 'GBR', 'SCO': 'GBR', 'WLS': 'GBR', 'Индия': 'IND', 'Ирландия': 'IRL', 'Армения': 'ARM', 'Болгария': 'BGR', 'Сербия': 'SRB', 'Республика Беларусь': 'BLR', 'Великобритания': 'GBR', 'Франция': 'FRA', 'Гондурас': 'HND', 'Коста-Рика': 'CRI', 'Азербайджан': 'AZE', 'Korea': 'KOR', 'NewCaledonia': 'FRA', 'Czech Republic': 'CZE', 'Slovak republic': 'SVK', 'Channel Islands': 'FRA', 'Reunion': 'FRA', 'Wales': 'GBR', 'Scotland': 'GBR', 'Bavaria': 'DEU', 'Tirol"': 'AUT', 'КГЫ': 'KGZ', 'Могилёв': 'BLR', 'Гомель': 'BLR', 'Минск': 'BLR', 'Самара': 'RUS', 'Гродно': 'BLR', 'Москва': 'RUS'}

Но даже ручное управление не решает проблему полностью. Остается 49 значений, которые уже невозможно интерпретировать. Вероятно, большая часть из этих значений – просто ошибки в данных.

unfixed = [x for x in tofix if x not in fix.keys()]

Out: ['', 'H', 'ES-CT', 'LAB', 'TCH', 'UT', 'TAH', 'ica', 'E', 'Unknown', 'R', 'A', '31/3, Colin', 'XKX', 'ES-IB','B','SOG','?????','KINO MANA','sue','SAR','D', 'DCF', 'N', 'EU', 'Mark', 'I', 'Joe', 'RUN', 'GUT', 'L', 'J', 'BAS', 'S', 'STEEL T BG', 'LZE', 'F', 'Sandy', 'DO2', 'sandy', 'SOO', 'LTV', 'selsmark, Alise', 'Suchy, Jorg' 'Eddie', 'FL', 'P', 'ITU', 'ZZ']

У этих ключей в словаре соответствия значением будет пустая строка.

for cc in unfixed:
    fix[cc] = ''

Напоследок добавим в словарь соответствия коды, которые являются валидными, но записаны в нижнем регистре.

for cc in valid_a3:
    if cc.upper() != cc:
        fix[cc] = cc.upper()

Теперь пришло время применить найденные замены. Чтобы сохранить начальные данные для дальнейшего сравнения копируем колонку country в country raw. Затем используя созданный словарь соответствия исправляем в колонке country значения, которые не соответствуют ISO.

for cc in fix:
    ind = ar[ar['country'] == cc].index
    ar.loc[ind,'country'] = fix[cc]

Здесь, конечно, не обойтись без векторизации, в таблице почти полтора миллиона строк. Но по словарю делаем цикл, а как иначе? Проверяем, сколько записей изменено:

len(ar[ar['country'] != ar['country raw']])

Out: 315955

то есть более 20% от общего количества.

ar[ar['country'] != ar['country raw']].sample(10)

place sex name country group place in group ... country raw
285 286 M Albaek, Mads Orla DNK M30-34 63 ... DEN
1288 1289 M Benthien, Andreas DEU M40-44 198 ... GER
490 491 M Lontok, Joselito PHL M50-54 18 ... PHI
145 146 M Mathiasen, Keld DNK M45-49 16 ... DEN
445 446 M Palm, Francois ZAF M25-29 48 ... RSA
152 153 M Muller, Johannes DEU M35-39 19 ... GER
764 765 F Woscher Sylvia DEU F55-59 8 ... GER
2182 2183 M Kojellis, Holger DEU M40-44 258 ... GER
1293 1294 M Zweer, Waldemar DEU M25-29 117 ... GER
747 748 M Petersen, Mathias DNK M25-29 79 ... DE

len(ar[ar['country'] == ''])

Out: 3221

Таково количество записей без страны или со страной неформата. Количество уникальных стран сократилось с 412 до 250. Вот они:
['', 'ABW', 'AFG', 'AGO', 'AIA', 'ALA', 'ALB', 'AND', 'ANT', 'ARE', 'ARG', 'ARM', 'ASM', 'ATA', 'ATF', 'AUS', 'AUT', 'AZE', 'BDI', 'BEL', 'BEN', 'BES', 'BGD', 'BGR', 'BHR', 'BHS', 'BIH', 'BLM', 'BLR', 'BLZ', 'BMU', 'BOL', 'BRA', 'BRB', 'BRN', 'BTN', 'BUR', 'BVT', 'BWA', 'CAF', 'CAN', 'CCK', 'CHE', 'CHL', 'CHN', 'CIV', 'CMR', 'COD', 'COG', 'COK', 'COL', 'COM', 'CPV', 'CRI', 'CTE', 'CUB', 'CUW', 'CXR', 'CYM', 'CYP', 'CZE', 'DEU', 'DJI', 'DMA', 'DNK', 'DOM', 'DZA', 'ECU', 'EGY', 'ERI', 'ESH', 'ESP', 'EST', 'ETH', 'FIN', 'FJI', 'FLK', 'FRA', 'FRO', 'FSM', 'GAB', 'GBR', 'GEO', 'GGY', 'GHA', 'GIB', 'GIN', 'GLP', 'GMB', 'GNB', 'GNQ', 'GRC', 'GRD', 'GRL', 'GTM', 'GUF', 'GUM', 'GUY', 'HKG', 'HMD', 'HND', 'HRV', 'HTI', 'HUN', 'IDN', 'IMN', 'IND', 'IOT', 'IRL', 'IRN', 'IRQ', 'ISL', 'ISR', 'ITA', 'JAM', 'JEY', 'JOR', 'JPN', 'KAZ', 'KEN', 'KGZ', 'KHM', 'KIR', 'KNA', 'KOR', 'KWT', 'LAO', 'LBN', 'LBR', 'LBY', 'LCA', 'LIE', 'LKA', 'LTU', 'LUX', 'LVA', 'MAC', 'MAF', 'MAR', 'MCO', 'MDA', 'MDG', 'MDV', 'MEX', 'MHL', 'MKD', 'MLI', 'MLT', 'MMR', 'MNE', 'MNG', 'MNP', 'MOZ', 'MSR', 'MTQ', 'MUS', 'MYS', 'MYT', 'NAM', 'NCL', 'NER', 'NFK', 'NGA', 'NHB', 'NIC', 'NIU', 'NLD', 'NOR', 'NPL', 'NRU', 'NZL', 'OMN', 'PAK', 'PAN', 'PCN', 'PER', 'PHL', 'PLW', 'PNG', 'POL', 'PRI', 'PRK', 'PRT', 'PRY', 'PSE', 'PYF', 'QAT', 'REU', 'ROU', 'RUS', 'RWA', 'SAU', 'SCG', 'SDN', 'SEN', 'SGP', 'SGS', 'SHN', 'SJM', 'SLB', 'SLE', 'SLV', 'SMR', 'SOM', 'SPM', 'SRB', 'SSD', 'SUR', 'SVK', 'SVN', 'SWE', 'SWZ', 'SXM', 'SYC', 'SYR', 'TCA', 'TCD', 'TGO', 'THA', 'TJK', 'TKL', 'TKM', 'TLS', 'TON', 'TTO', 'TUN', 'TUR', 'TUV', 'TWN', 'TZA', 'UGA', 'UKR', 'UMI', 'URY', 'USA', 'UZB', 'VAT', 'VCT', 'VEN', 'VGB', 'VIR', 'VNM', 'VUT', 'WLF', 'WSM', 'YEM', 'YUG', 'ZAF', 'ZMB', 'ZWE']

Теперь никаких отклонений. Сохраняем результат в новый файл details2.pkl, предварительно преобразовав объединенный датафрейм обратно в словарь датафреймов, как это было сделано ранее.

Локация

Теперь вспомним, что упоминание о странах также есть и в сводной таблице, в колонке loc.

event date loc males females rus total link
0 Ironman Dubai Duathlon 70.3 2020 2020-07-02 Dubai, United Arab Emirates 835 215 65 1050
1 Ironman Dubai 70.3 2020 2020-02-07 Dubai, United Arab Emirates 638 132 55 770
2 Israman Half 2020 2020-01-29 Israel, Eilat 670 126 4 796
3 Ironman Indian Wells La Quinta 70.3 2019 2019-12-08 Indian Wells/La Quinta, California, USA 1590 593 6 2183
4 Ironman Taupo 70.3 2019 2019-12-07 New Zealand 767 420 3 1187
5 Ironman Bahrain 70.3 2019 2019-12-07 Manama, Bahrain 858 214 38 1072
6 Ironman Western Australia 2019 2019-12-01 Busselton, Western Australia 940 229 1 1169
7 Ironman Mar del Plata 2019 2019-12-01 Mar del Plata, Argentina 506 66 3 572
8 Ironman Cozumel 2019 2019-11-24 Cozumel, Mexico 1158 395 12 1553
9 Ironman Arizona 2019 2019-11-24 Tempe, Arizona, USA 1697 633 3 2330

Его тоже нужно привести к стандартному виду. Здесь немного другая история: не видно ни ISO, ни олимпийских кодов. Все описано в достаточно свободной форме. Через запятую перечислены город, страна и другие составляющие адреса, причем в произвольном порядке. Где-то страна на первом месте, где-то на последнем. pycountry тут уже не поможет. А записей много – на 1922 гонки 525 уникальных локаций (в исходном виде).

Но и тут нашелся подходящий инструмент. Это geopy, а именно geolocator Nominatim. Работает вот так:

from geopy.geocoders import Nominatim

geolocator = Nominatim(user_agent='triathlon results researcher')
geolocator.geocode('Бирюзовая Катунь, Алтай, Россия', language='en')

Out: Location(Бирюзовая Катунь, Ая – Бирюзовая Катунь, Айский сельсовет, Altaysky District, Altai Krai, Siberian Federal District, Russia, (51.78897945, 85.73956296106752, 0.0))

По запросу в произвольной форме выдает структурированный ответ – адрес и координаты. Если задать язык, как здесь – английский, то что сможет — переведет. Нам в первую очередь нужно стандартное название страны для последующего перевода в код ISO. Оно как раз стоит на последнем месте в свойстве address. Поскольку geolocator каждый раз отправляет запрос на сервер, процесс этот не быстрый и для 500 записей занимает несколько минут. К тому же бывает, что ответ не приходит. В этом случае иногда помогает повторный запрос. В моем с первого раза ответ не пришел на 130 запросов. Большую часть из них удалось обработать двумя повторными попытками. Однако 34 названия обработать не так и не удалось даже несколькими дальнейшими повторными попытками. Вот они:
['Tongyeong, Korea, Korea, South', 'Constanta, Mamaia, Romania, Romania', 'Weihai, China, China', 'д. Толвинка, Брянская обл.', 'Odaiba Marin Park, Tokyo, Japan, Japan', 'Sweden, Smaland, Kalmar', 'Cholpon-Ata city, Resort Center "Kapriz", Kyrgyzstan', 'Luxembourg, Region Moselle, Moselle', 'Chita Peninsula, Japan', 'Kraichgau Region, Germany', 'Jintang, Chengdu, Sichuan Province, China, China', 'Madrid, Spain, Spain', 'North American Pro Championship, St. George, Utah, USA', 'Milan Idroscalo Linate, Italy', 'Dexing, Jiangxi Province, China, China', 'Mooloolaba, Australia, Australia', 'Nathan Benderson Park (NBP), 5851 Nathan Benderson Circle, Sarasota, FL 34235., United States', 'Strathclyde Country Park, North Lanarkshire, Glasgow, Great Britain', 'Quijing, China', 'United States of America , Hawaii, Kohala Coast', 'Buffalo City, East London, South Africa', 'Spain, Vall de Cardener', 'Россия, пос. Метлино Озерский городской округ', 'Asian TriClub Championship, Hefei, China', 'Taizhou, Jiangsu Province, China, China', 'Россия, Москва, СЦП «Крылатское»', 'Buffalo, Gallagher Beach, Furhmann Blvd, United States', 'North American Pro Championship | St. George, Utah, USA', 'Weihai, Shandong, China, China', 'Tarzo - Revine Lago, Italy', 'Lausanee, Switzerland', 'Queenstown, New Zealand, New Zealand', 'Makuhari, Japan, Japan', 'Szombathlely, Hungary']

Видно, что во многих присутствует двойное упоминание страны, и это на самом деле мешает. В общем пришлось вручную обработать эти оставшиеся названия и для всех были получены стандартные адреса. Далее из этих адресов я выделил страну и записал эту страну в новую колонку в сводной таблице. Поскольку, как я уже сказал работа с geopy не быстрая, я решил сразу сохранить координаты локации – широту и долготу. Они пригодятся позже для визуализации на карте.

event date loc country latitude longitude ...
0 Ironman Dubai Duathlon 70.3 2020 2020-07-02 Dubai, United Arab Emirates United Arab Emirates 25.0657 55.1713 ...
1 Ironman Dubai 70.3 2020 2020-02-07 Dubai, United Arab Emirates United Arab Emirates 25.0657 55.1713 ...
2 Israman Half 2020 2020-01-29 Israel, Eilat Israel 29.5569 34.9498 ...
3 Ironman Indian Wells La Quinta 70.3 2019 2019-12-08 Indian Wells/La Quinta, California, USA United States of America 33.7238 -116.305 ...
4 Ironman Taupo 70.3 2019 2019-12-07 New Zealand New Zealand -41.5001 172.834 ...
5 Ironman Bahrain 70.3 2019 2019-12-07 Manama, Bahrain Bahrain 26.2235 50.5822 ...
6 Ironman Western Australia 2019 2019-12-01 Busselton, Western Australia Australia -33.6445 115.349 ...
7 Ironman Mar del Plata 2019 2019-12-01 Mar del Plata, Argentina Argentina -37.9977 -57.5483 ...
8 Ironman Cozumel 2019 2019-11-24 Cozumel, Mexico Mexico 20.4318 -86.9203 ...
9 Ironman Arizona 2019 2019-11-24 Tempe, Arizona, USA United States of America 33.4255 -111.94 ...
10 Ironman Xiamen 70.3 2019 2019-11-10 Xiamen, China China 24.4758 118.075 ...

После этого с помощью pyco.countries.get(name = ‘...’).alpha_3 искал страну по названию и выделял трехзначный код.

event date loc country latitude longitude ...
0 Ironman Dubai Duathlon 70.3 2020 2020-07-02 Dubai, United Arab Emirates ARE 25.0657 55.1713 ...
1 Ironman Dubai 70.3 2020 2020-02-07 Dubai, United Arab Emirates ARE 25.0657 55.1713 ...
2 Israman Half 2020 2020-01-29 Israel, Eilat ISR 29.5569 34.9498 ...
3 Ironman Indian Wells La Quinta 70.3 2019 2019-12-08 Indian Wells/La Quinta, California, USA USA 33.7238 -116.305 ...
4 Ironman Taupo 70.3 2019 2019-12-07 New Zealand NZL -41.5001 172.834 ...
5 Ironman Bahrain 70.3 2019 2019-12-07 Manama, Bahrain BHR 26.2235 50.5822 ...
6 Ironman Western Australia 2019 2019-12-01 Busselton, Western Australia AUS -33.6445 115.349 ...
7 Ironman Mar del Plata 2019 2019-12-01 Mar del Plata, Argentina ARG -37.9977 -57.5483 ...
8 Ironman Cozumel 2019 2019-11-24 Cozumel, Mexico MEX 20.4318 -86.9203 ...
9 Ironman Arizona 2019 2019-11-24 Tempe, Arizona, USA USA 33.4255 -111.94 ...
10 Ironman Xiamen 70.3 2019 2019-11-10 Xiamen, China CHN 24.4758 118.075 ...

Дистанция

Еще одно важное действие, которое нужно сделать на сводной таблице – для каждой гонки определить дистанцию. Это пригодится нам для вычисления скоростей в дальнейшем. В триатлоне существует четыре основных дистанции – спринт, олимпийская, полужелезная и железная. Можно видеть, что в названиях гонок как правило есть указание на дистанцию – это Слова Sprint, Olympic, Half, Full. Помимо этого, у разных организаторов свои обозначения дистанций. Половинка у Ironman, например, обозначается как 70.3 – по количеству миль в дистанции, олимпийская – 5150 по числу километров (51.5), а железная может обозначаться как Full или, вообще, как отсутствие пояснений – например Ironman Arizona 2019. Ironman – он и есть железный! У Challenge железная дистанция обозначается как Long, а полужелезная – как Middle. Наш российский IronStar обозначает полную как 226, а половинку как 113 – по числу километров, но обычно слова Full и Half тоже присутствуют. Теперь применим все эти знания и пометим все гонки в соответствии с ключевыми словами, присутствующими в названиях.

sprints = rs.loc[[i for i in rs.index if 'sprint' in rs.loc[i, 'event'].lower()]]
olympics1 = rs.loc[[i for i in rs.index if 'olympic' in rs.loc[i, 'event'].lower()]]
olympics2 = rs.loc[[i for i in rs.index if '5150' in rs.loc[i, 'event'].lower()]]
olympics = pd.concat([olympics1, olympics2])
#… и так далее

rsd = pd.concat([sprints, olympics, halfs, fulls]) 

В rsd получилось 1 925 записей, то есть на три больше, чем общее число гонок, значит какие-то попали под два критерия. Посмотрим на них:

rsd[rsd.duplicated(keep=False)]['event'].sort_index()

event date loc country latitude longitude ...
38 Temiradam 113 Half 2019 2019-09-22 Казахстан, Актау KAZ 43.6521 51.158 ...
38 Temiradam 113 Half 2019 2019-09-22 Казахстан, Актау KAZ 43.6521 51.158 ...
65 Triway Olympic Sprint 2019 2019-09-08 Россия, Ростов-на-Дону RUS 47.2214 39.7114 ...
65 Triway Olympic Sprint 2019 2019-09-08 Россия, Ростов-на-Дону RUS 47.2214 39.7114 ...
82 Ironman Dun Laoghaire Full Swim 70.3 2019 2019-08-25 Ireland, Dun Laoghaire IRL 53.2923 -6.13601 ...
82 Ironman Dun Laoghaire Full Swim 70.3 2019 2019-08-25 Ireland, Dun Laoghaire IRL 53.2923 -6.13601 ...

Действительно, так и есть. В первой паре в названии Temiradam 113 Half 2019 есть упоминание и Half и 113. Но это не противоречие, они оба идентифицировались как половинки. Далее — Triway Olympic Sprint 2019. Здесь действительно можно запутаться – есть и Olympic и Sprint. Разобраться можно, посмотрев на протокол с результатами гонки.

place sex name country group place in group swim t1 bike t2 run result
0 1 M Хисматуллин Роман RUS MМужчины 1 00:12:21 00:00:31 00:34:13 00:00:25 00:21:49 01:09:19
1 2 M Диков Александр RUS MМужчины 2 00:12:21 00:00:28 00:34:15 00:00:26 00:23:07 01:10:38
2 3 M Горлов Дмитрий RUS MМужчины 3 00:14:20 00:00:37 00:35:48 00:00:34 00:22:16 01:13:35

Лучшее время – 1:09. Значит это спринт. Удалим эту запись из списка олимпийских.

olympics.drop(65)

Точно так же поступим с пересекающимися Ironman Dun Laoghaire Full Swim 70.3 2019

place sex name country group place in group swim t1 bike t2 run result
0 1 M Brownlee, Alistair GBR MPRO 1 00:23:19 00:02:18 02:21:19 00:01:55 01:11:42 04:00:33
1 2 M Smales, Elliot GBR MPRO 2 00:24:47 00:02:09 02:29:26 00:01:48 01:12:47 04:10:57
2 3 M Bowden, Adam GBR MPRO 3 00:23:24 00:02:18 02:32:09 00:02:06 01:13:49 04:13:46

Здесь лучшее время 4:00. Это характерно для половинки. Удаляем запись с индексом 85 из fulls.

fulls.drop(85)

Теперь запишем информацию о дистанции в основной датафрейм и посмотрим, что получилось:

rs['dist'] = ''

rs.loc[sprints.index,'dist'] = 'sprint'
rs.loc[olympics.index,'dist'] = 'olympic'
rs.loc[halfs.index,'dist'] = 'half'
rs.loc[fulls.index,'dist'] = 'full'

rs.sample(10)

event place sex name country group place in group ... country raw group raw
... 566 M Vladimir Kozar SVK M40-44 8 ... SVK MOpen 40-44
... 8 M HANNES COOL BEL MPRO 11 ... BEL MPRO M
... 445 F Ileana Sodani USA F45-49 4 ... USA F45-49 F
... 227 F JARLINSKA Bozena POL F45-49 2 ... POL FK45-49
... 440 F Celine Orrigoni FRA F40-44 6 ... FRA F40-44 F
... 325 M Vladimir Eckert SVK M40-44 6 ... SVK MOpen 40-44
... 139 F ATRASZKIEWICZ Magda POL F40-44 2 ... POL FK40-44
... 18 M Marijn de Jonge NLD MPRO 18 ... NED Mpro
... 574 M Luca Andalo ITA M40-44 9 ... ITA MOpen 40-44
... 67 M URBANKIEWICZ Aleksandra POL M35-39 1 ... POL MK35-39

Проверим, что не осталось непокрытых записей:

len(rs[rs['dist'] == ''])

Out: 0

И проверим наши проблемные, двусмысленные:

rs.loc[[38,65,82],['event','dist']]

event dist
38 Temiradam 113 Half 2019 half
65 Triway Olympic Sprint 2019 sprint
82 Ironman Dun Laoghaire Full Swim 70.3 2019 half

Все нормально. Сохраняем в новый файл:

pkl.dump(rs, open(r'D:trisummary5.pkl', 'wb'))

Возрастные группы

Теперь вернемся к протоколам гонок.

Мы уже проанализировали пол, страну и результаты участника, и привели их к стандартному виду. Но еще остались еще две графы – группа и, собственно, само имя. Начнем с групп. В триатлоне принято делить участников по возрастным группам. Также часто выделяется группа профессионалов. По сути, зачет идет в каждой такой группе отдельно — награждаются первые три места в каждой группе. По группам же идет квалификационный отбор на чемпионаты, например, на Кону.

Объединим все записи и посмотрим какие вообще группы существуют.

rd = pkl.load(open(r'D:tridetails2.pkl', 'rb'))
ar = pd.concat(rd)
ar['group'].unique()

Оказалось, что групп огромное количество – 581. Сотня случайно выбранных выглядит так:
['MSenior', 'FAmat.', 'M20', 'M65-59', 'F25-29', 'F18-22', 'M75-59', 'MPro', 'F24', 'MCORP M', 'F21-30', 'MSenior 4', 'M40-50', 'FAWAD', 'M16-29', 'MK40-49', 'F65-70', 'F65-70', 'M12-15', 'MK18-29', 'MМ50up', 'FSEMIFINAL 2 PRO', 'F16', 'MWhite', 'MOpen 25-29', 'FПараатлет', 'MPT TRI-2', 'M16-24', 'FQUALIFIER 1 PRO', 'F15-17', 'FSEMIFINAL 2 JUNIOR', 'FOpen 60-64', 'M75-80', 'F60-69', 'FJUNIOR A', 'F17-18', 'FAWAD BLIND', 'M75-79', 'M18-29', 'MJUN19-23', 'M60-up', 'M70', 'MPTS5', 'F35-40', "M'S PT1", 'M50-54', 'F65-69', 'F17-20', 'MP4', 'M16-29', 'F18up', 'MJU', 'MPT4', 'MPT TRI-3', 'MU24-39', 'MK35-39', 'F18-20', "M'S", 'F50-55', 'M75-80', 'MXTRI', 'F40-45', 'MJUNIOR B', 'F15', 'F18-19', 'M20-29', 'MAWAD PC4', 'M30-37', 'F21-30', 'Mpro', 'MSEMIFINAL 1 JUNIOR', 'M25-34', 'MAmat.', 'FAWAD PC5', 'FA', 'F50-60', 'FSenior 1', 'M80-84', 'FK45-49', 'F75-79', 'M<23', 'MPTS3', 'M70-75', 'M50-60', 'FQUALIFIER 3 PRO', 'M9', 'F31-40', 'MJUN16-19', 'F18-19', 'M PARA', 'F35-44', 'MParaathlete', 'F18-34', 'FA', 'FAWAD PC2', 'FAll Ages', 'M PARA', 'F31-40', 'MM85', 'M25-39']

Посмотрим какие из них самые многочисленные:

ar['group'].value_counts()[:30]

Out:
M40-44 199157
M35-39 183738
M45-49 166796
M30-34 154732
M50-54 107307
M25-29 88980
M55-59 50659
F40-44 48036
F35-39 47414
F30-34 45838
F45-49 39618
MPRO 38445
F25-29 31718
F50-54 26253
M18-24 24534
FPRO 23810
M60-64 20773
M 12799
F55-59 12470
M65-69 8039
F18-24 7772
MJUNIOR 6605
F60-64 5067
M20-24 4580
FJUNIOR 4105
M30-39 3964
M40-49 3319
F 3306
M70-74 3072
F20-24 2522

Можно видеть, что это группы по пять лет, отдельно для мужчин и отдельно для женщин, а также профессиональные группы MPRO и FPRO.

Итак, нашим стандартом будет:

ag = ['MPRO', 'M18-24', 'M25-29', 'M30-34', 'M35-39', 'M40-44', 'M45-49', 'M50-54', 'M55-59', 'M60-64',  'M65-69', 'M70-74', 'M75-79', 'M80-84', 'M85-90', 'FPRO', 'F18-24', 'F25-29', 'F30-34', 'F35-39', 'F40-44',   'F45-49', 'F50-54', 'F55-59', 'F60-64', 'F65-69', 'F70-74', 'F75-79', 'F80-84', 'F85-90']
#ag – age group

Этим множеством покрывается почти 95% всех финишеров.

Разумеется, нам не удастся привести к этому стандарту вообще все группы. Но мы поищем те, что похожи на них и приведем хотя бы часть. Предварительно приведем к верхнему регистру и удалим пробелы. Вот что нашлось:
['F25-29F', 'F30-34F', 'F30-34-34', 'F35-39F', 'F40-44F', 'F45-49F', 'F50-54F', 'F55-59F', 'FAG:FPRO', 'FK30-34', 'FK35-39', 'FK40-44', 'FK45-49', 'FOPEN50-54', 'FOPEN60-64', 'MAG:MPRO', 'MK30-34', 'MK30-39', 'MK35-39', 'MK40-44', 'MK40-49', 'MK50-59', 'MМ40-44', 'MM85-89', 'MOPEN25-29', 'MOPEN30-34', 'MOPEN35-39', 'MOPEN40-44', 'MOPEN45-49', 'MOPEN50-54', 'MOPEN70-74', 'MPRO:', 'MPROM', 'M0-44"']

Преобразуем их к нашим стандартным.

fix = { 'F25-29F': 'F25-29', 'F30-34F' : 'F30-34', 'F30-34-34': 'F30-34', 'F35-39F': 'F35-39', 'F40-44F': 'F40-44', 'F45-49F': 'F45-49', 'F50-54F': 'F50-54', 'F55-59F': 'F55-59', 'FAG:FPRO': 'FPRO', 'FK30-34': 'F30-34',      'FK35-39': 'F35-39', 'FK40-44': 'F40-44', 'FK45-49': 'F45-49', 'FOPEN50-54': 'F50-54', 'FOPEN60-64': 'F60-64', 'MAG:MPRO': 'MPRO', 'MK30-34': 'M30-34', 'MK30-39': 'M30-39', 'MK35-39': 'M35-39', 'MK40-44': 'M40-44', 'MK40-49': 'M40-49', 'MK50-59': 'M50-59', 'MМ40-44': 'M40-44', 'MM85-89': 'M85-89', 'MOPEN25-29': 'M25-29', 'MOPEN30-34': 'M30-34', 'MOPEN35-39': 'M35-39', 'MOPEN40-44': 'M40-44', 'MOPEN45-49': 'M45-49', 'MOPEN50-54': 'M50-54', 'MOPEN70-74': 'M70- 74', 'MPRO:' :'MPRO', 'MPROM': 'MPRO', 'M0-44"' : 'M40-44'}

Применим теперь наше преобразование к основному датафрейму ar, но предварительно сохраним изначальные значения group в новою колонку group raw.

ar['group raw'] = ar['group']

В колонке group оставим только те значения, которые соответствуют нашему стандарту.

Теперь можно оценить наши старания:

len(ar[(ar['group'] != ar['group raw'])&(ar['group']!='')])

Out: 273

Совсем немного на уровне полутора миллионов. Но ведь не узнаешь, пока не попробуешь.

Выборочные 10 выглядят так:

event place sex name country group place in group ... country raw group raw
... 566 M Vladimir Kozar SVK M40-44 8 ... SVK MOpen 40-44
... 8 M HANNES COOL BEL MPRO 11 ... BEL MPRO M
... 445 F Ileana Sodani USA F45-49 4 ... USA F45-49 F
... 227 F JARLINSKA Bozena POL F45-49 2 ... POL FK45-49
... 440 F Celine Orrigoni FRA F40-44 6 ... FRA F40-44 F
... 325 M Vladimir Eckert SVK M40-44 6 ... SVK MOpen 40-44
... 139 F ATRASZKIEWICZ Magda POL F40-44 2 ... POL FK40-44
... 18 M Marijn de Jonge NLD MPRO 18 ... NED Mpro
... 574 M Luca Andalo ITA M40-44 9 ... ITA MOpen 40-44
... 67 M URBANKIEWICZ Aleksandra POL M35-39 1 ... POL MK35-39

Сохраняем новую версию датафрейма, предварительно преобразовав его обратно в словарь rd.

pkl.dump(rd, open(r'D:tridetails3.pkl', 'wb'))

Имя

Теперь займемся именами. Посмотрим выборочно 100 имен с разных гонок:

list(ar['name'].sample(100))

Out: ['Case, Christine', 'Van der westhuizen, Wouter', 'Grace, Scott', 'Sader, Markus', 'Schuller, Gunnar', 'Juul-Andersen, Jeppe', 'Nelson, Matthew', 'Забугина Валерия Геннадьевна', 'Westman, Pehr', 'Becker, Christoph', 'Bolton, Jarrad', 'Coto, Ricardo', 'Davies, Luke', 'Daniltchev, Alexandre', 'Escobar Labastida, Emmanuelle', 'Idzikowski, Jacek', 'Fairaislova Iveta', 'Fisher, Kulani', 'Didenko, Viktor', 'Osborne, Jane', 'Kadralinov, Zhalgas', 'Perkins, Chad', 'Caddell, Martha', 'Lynaire PARISH', 'Busing, Lynn', 'Nikitin, Evgeny', 'ANSON MONZON, ROBERTO', 'Kaub, Bernd', 'Bank, Morten', 'Kennedy, Ian', 'Kahl, Stephen', 'Vossough, Andreas', 'Gale, Karen', 'Mullally, Kristin', 'Alex FRASER', 'Dierkes, Manuela', 'Gillett, David', 'Green, Erica', 'Cunnew, Elliott', 'Sukk, Gaspar', 'Markina Veronika', 'Thomas KVARICS', 'Wu, Lewen', 'Van Enk, W.J.J', 'Escobar, Rosario', 'Healey, Pat', 'Scheef, Heike', 'Ancheta, Marlon', 'Heck, Andreas', 'Vargas Iii, Raul', 'Seferoglou, Maria', 'chris GUZMAN', 'Casey, Timothy', 'Olshanikov Konstantin', 'Rasmus Nerrand', 'Lehmann Bence', 'Amacker, Kirby', 'Parks, Chris', 'Tom, Troy', 'Karlsson, Ulf', 'Halfkann, Dorothee', 'Szabo, Gergely', 'Antipov Mikhail', 'Von Alvensleben, Alvo', 'Gruber, Peter', 'Leblanc, Jean-Philippe', 'Bouchard, Jean-Francois', 'Marchiotto MASSIMO', 'Green, Molly', 'Alder, Christoph', 'Morris, Huw', 'Deceur, Marc', 'Queenan, Derek', 'Krause, Carolin', 'Cockings, Antony', 'Ziehmer Chris', 'Stiene, John', 'Chmet Daniela', 'Chris RIORDAN', 'Wintle, Mel', 'Борисёнок Павел', 'GASPARINI CHRISTIAN', 'Westbrook, Christohper', 'Martens, Wim', 'Papson, Chris', 'Burdess, Shaun', 'Proctor, Shane', 'Cruzinha, Pedro', 'Hamard, Jacques', 'Petersen, Brett', 'Sahyoun, Sebastien', "O'Connell, Keith", 'Symoshenko, Zhan', 'Luternauer, Jan', 'Coronado, Basil', 'Smith, Alex', 'Dittberner, Felix', 'N?sman, Henrik', 'King, Malisa', 'PUHLMANN Andre']

Все сложно. Встречаются самые разные варианты записей: Имя Фамилия, Фамилия Имя, Фамилия, Имя, ФАМИЛИЯ, Имя и т. д. То есть разный порядок, разный регистр, где-то есть разделитель — запятая. Так же существует немало протоколов, в которых идет кириллица. Там тоже нет единообразия, и могут встречаться такие форматы: “Фамилия Имя”, “Имя Фамилия”, “Имя Отчество Фамилия”, “Фамилия Имя Отчество”. Хотя на самом деле отчество встречается и в латинском написании. И здесь, кстати, встает еще одна проблема – транслитерация. Еще надо отметить, что даже там, где отчества нет, запись может не ограничиваться двумя словами. Например, у латиноамериканцев имя плюс фамилия обычно состоят из трех или четырех слов. У голландцев бывает приставка Van, у китайцев и корейцев тоже составные имена обычно из трех слов. В общем, надо как-то распутать весь этот ребус и по максимуму стандартизировить. Как правило внутри одной гонки формат имени одинаков для всех, но даже здесь встречаются ошибки, которые мы, однако, обрабатывать не будем. Начнем с того, что сохраним существующие значения в новой колонке name raw:

ar['name raw'] = ar['name']

Абсолютное большинство протоколов на латинице, поэтому первым делом я хотел бы сделать транслит. Посмотрим, какие вообще символы могут входить в имя участника.

set( ''.join(ar['name'].unique()))

Out: [' ', '!', '"', '#', '&', "'", '(', ')', '*', '+', ',', '-', '.', '/', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ':', ';', '>', '?', '@', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '[', '\', ']', '^', '_', '`', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '|', 'x7f', 'xa0', '¤', '¦', '§', '', '«', 'xad', '', '°', '±', 'µ', '¶', '·', '»', 'Ё', 'І', 'А', 'Б', 'В', 'Г', 'Д', 'Е', 'Ж', 'З', 'И', 'Й', 'К', 'Л', 'М', 'Н', 'О', 'П', 'Р', 'С', 'Т', 'У', 'Ф', 'Х', 'Ц', 'Ч', 'Ш', 'Щ', 'Ы', 'Ь', 'Э', 'Ю', 'Я', 'а', 'б', 'в', 'г', 'д', 'е', 'ж', 'з', 'и', 'й', 'к', 'л', 'м', 'н', 'о', 'п', 'р', 'с', 'т', 'у', 'ф', 'х', 'ц', 'ч', 'ш', 'щ', 'ъ', 'ы', 'ь', 'э', 'ю', 'я', 'ё', 'є', 'і', 'ў', '–', '—', '‘', '’', '‚', '“', '”', '„', '†', '‡', '…', '‰', '›', '']

Чего тут только нет! Помимо собственно букв и пробелов, еще куча разных диковинных символов. Из них допустимыми, то есть присутствующими не по ошибке, можно считать точку ‘.’, дефис ‘-’ и апостроф “’”. Помимо этого, было замечено что во многих немецких и норвежских именах и фамилиях присутствует знак вопроса ‘?’. Они, судя по всему, заменяют здесь символы из расширенной латиницы – ‘?’, ‘a’, ‘o’, ‘u’,? и др. Вот примеры:
Pierre-Alexandre Petit, Jean-louis Lafontaine, Faris Al-Sultan, Jean-Francois Evrard, Paul O'Mahony, Aidan O'Farrell, John O'Neill, Nick D'Alton, Ward D'Hulster, Hans P.J. Cami, Luis E. Benavides, Maximo Jr. Rueda, Prof. Dr. Tim-Nicolas Korf, Dr. Boris Scharlowsk, Eberhard Gro?mann, Magdalena Wei?, Gro?er Axel, Meyer-Szary Krystian, Morten Halkj?r, RASMUSSEN S?ren Balle

Запятая, хоть и встречается очень часто, является всего лишь разделителем, принятым на определенных гонках, поэтому тоже попадет в разряд недопустимых. Цифры тоже не должны появляться в именах.

bs = [s for s in symbols if not (s.isalpha() or s in " . - ' ? ,")] #bs – bad symbols

bs

Out: ['!', '"', '#', '&', '(', ')', '*', '+', '/', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ':', ';', '>', '@', '[', '\', ']', '^', '_', '`', '|', 'x7f', 'xa0', '¤', '¦', '§', '', '«', 'xad', '', '°', '±', '¶', '·', '»', '–', '—', '‘', '’', '‚', '“', '”', '„', '†', '‡', '…', '‰', '›', '']

Временно уберем все эти символы, чтобы узнать в скольких записях они присутствуют:

for s in bs:
    ar['name'] = ar['name'].str.replace(s, '')

corr = ar[ar['name'] != ar['name raw']]

Таких записей 2 184, то есть всего 0.15% от общего количества – очень мало. Взглянем выборочно на 100 из них:

list(corr['name raw'].sample(100))

Out: ['Scha¶ffl, Ga?nter', 'Howard, Brian &', 'Chapiewski, Guilherme (Gc)', 'Derkach 1svd_mail_ru', 'Parker H1 Lauren', 'Leal le?n, Yaneri', 'TencA, David', 'Cortas La?pez, Alejandro', 'Strid, Bja¶rn', '(Crutchfield) Horan, Katie', 'Vigneron, Jean-Michel.Vigneron@gmail.Com', 'МОШКОВxa0Иван', 'Telahr, J†rgen', 'St”rmer, Melanie', 'Nagai B1 Keiji', 'Rinc?n, Mariano', 'Arkalaki, Angela (Evangelia)', 'Barbaro B1 Bonin Anna G:Charlotte', 'Ra?esch, Ja¶rg', "CAVAZZI NICCOLO\'", 'D„nzel, Thomas', 'Ziska, Steffen (Gerhard)', 'Kobilica B1 Alen', 'Mittelholcz, Bala', 'Jimanez Aguilar, Juan Antonio', 'Achenza H1 Giovanni', 'Reppe H2 Christiane', 'Filipovic B2 Lazar', 'Machuca Ka?hnel, Ruban Alejandro', 'Gellert (Silberprinz), Christian', 'Smith (Guide), Matt', 'Lenatz H1 Benjamin', 'Da¶llinger, Christian', 'Mc Carthy B1 Patrick Donnacha G:Bryan', 'Fa¶llmer, Chris', 'Warner (Rivera), Lisa', 'Wang, Ruijia (Ray)', 'Mc Carthy B1 Donnacha', 'Jones, Nige (Paddy)', 'Sch”ler, Christoph', 'НЕДОШИТОВxa0Дмитрий', 'Holthaus, Adelhard (Allard)', 'Mi;Arro, Ana', 'Dr: Koch Stefan', 'МОШКОВxa0Юрий', 'ЦУБЕРАxa0Максим', 'Ziska, Steffen (Gerhard)', 'Albarracaxadn Gonza?lez, Juan Francisco', 'Ha¶fling, Imke', 'Johnston, Eddie (Edwin)', 'Mulcahy, Bob (James)', 'Gottschalk, Bj”rn', 'ГУЩИНxa0Дмитрий', 'Gretsch H2 Kendall', 'Scorse, Christopher (Chris)', 'Kiel‚basa, Pawel', 'Kalan, Magnus', 'Roderick "eric" SIMBULAN', 'Russell;, Mark', 'ROPES AND GRAY TEAM 3', 'Andrade, H?¦CTOR DANIEL', 'Landmann H2 Joshua', 'Reyes Rodraxadguez, Aithami', 'Ziska, Steffen (Gerhard)', 'Ziska, Steffen (Gerhard)', 'Heuza, Pierre', 'Snyder B1 Riley Brad G:Colin', 'Feldmann, Ja¶rg', 'Beveridge H1 Nic', 'FAGES`, perrine', 'Frank", Dieter', 'Saarema¤el, Indrek', 'Betancort Morales, Arida–y', 'Ridderberg, Marie_Louise', 'ЗАЙЦЕВxa0Андрей', 'Ka¶nig, Johannes', 'W Van(der Klugt', 'Ziska, Steffen (Gerhard)', 'Johnson, Nick26', 'Heinz JOHNER03', 'Ga¶rg, Andra', 'Maruo B2 Atsuko', 'Moral Pedrero H1 Eva Maria', 'КУТАСОВxa0Сергей', 'MATUS SANTIAGO Osc1r', 'Stenbrink, Bja¶rn', 'Wangkhan, Sm1.Thaworn', 'Pullerits, Ta¶nu', 'Clausner, 8588294149', 'Castro Miranda, Josa Ignacio', 'La¶fgren, Pontuz', 'Brown, Jann ( Janine )', 'Ziska, Steffen (Gerhard)', 'Koay, Sa¶ren', 'Ba¶hm, Heiko', 'Oleksiuk B2 Vita', 'G Van(de Grift', 'Scha¶neborn, Guido', 'Mandez, A?lvaro', 'Garcaxada Fla?rez, Daniel']

В итоге, после долгих исследований, было решено: все буквенные символы, а также пробел, дефис, апостроф и знак вопроса оставить как есть, запятую, точку и символ ‘xa0’ заменить на пробелы, а все остальные символы заменить на пустую строку, то есть просто удалить.

ar['name'] = ar['name raw']

for s in symbols:
    if s.isalpha() or s in " - ? '":        
        continue        
    if s in ".,xa0":
        ar['name'] = ar['name'].str.replace(s, ' ')       
    else:
        ar['name'] = ar['name'].str.replace(s, '')

Затем избавимся от лишних пробелов:

ar['name'] = ar['name'].str.split().str.join(' ')
ar['name'] = ar['name'].str.strip() # на всякий случай

Посмотрим, что получилось:

ar.loc[corr.index].sample(10)

place sex name country ... name raw
63 64 M Curzillat B MARANO Annouck GJulie FRA ... Curzillat B1 MARANO Annouck G:Julie
425 426 M Naranjo Quintero Cndido ESP ... Naranjo Quintero, C‡ndido
1347 1348 F Chang Margaret Peggy USA ... Chang, Margaret (Peggy)
790 791 M Gonzalez Ruben PRI ... Gonzalez`, Ruben
1562 1563 M Garcia Hernandez Elias MEX ... Garcia Hernandez/, Elias
50 51 M Reppe H Christiane DEU ... Reppe H2 Christiane
528 529 M Ho Shihken TWN ... Ho, Shih—ken
819 820 M Elmously A R Abdelrahman EGY ... Elmously, A.R. (Abdelrahman)
249 250 F boyer Isabelle THA ... `boyer, Isabelle
744 745 M Garcaa Morales Pedro Luciano ESP ... Garca¬a Morales, Pedro Luciano

Было так же замечено, что существуют имена, полностью состоящие из знаков вопроса.

qmon = ar[(ar['name'].str.replace('?', '').str.strip() == '')&(ar['name']!='')] #qmon – question mark only names

Таких 3 429. Выглядит это примерно так:

place sex name country group place in group ... country raw group raw name raw
818 819 M ???? ??? JPN M45-49 177 ... JPN M45-49 ????, ???
1101 1102 M ?? ?? JPN M50-54 159 ... JPN M50-54 ??, ??
162 163 M ? ?? CHN M30-34 22 ... CHN M30-34 ?, ??
1271 1272 F ???? ???? JPN F50-54 15 ... JPN F50-54 ????, ????
552 553 M ??? ?? JPN M25-29 30 ... JPN M25-29 ???, ??
423 424 M ??? ???? JPN M55-59 24 ... JPN M55-59 ???, ????
936 937 F ?? ?? JPN F50-54 7 ... JPN F50-54 ??, ??
244 245 M ? ?? KOR M50-54 30 ... KOR M50-54 ?, ??
627 628 M ? ? CHN M40-44 94 ... CHN M40-44 ?, ?
194 195 M ?????? ????? RUS 188 ... RUS M ?????? ?????

Наша цель приведения имен к одному стандарту состоит в том, чтобы одинаковые имена выглядели одинаково, а разные по-разному. В случае с именами, состоящими из одних лишь знаков вопроса, они отличаются лишь количеством символов, но это не дает полной уверенности, что имена с одинаковым количеством действительно одинаковы. Поэтому заменим их все на пустую строку и не будем рассматривать в дальнейшем.

ar.loc[qmon.index, 'name'] = ''

Всего записей, где имя – пустая строка получилось 3 454. Не так много — переживем. Теперь, когда мы избавились от ненужных символов, можно перейти к транслитерации. Для этого сперва приведем все к нижнему регистру, чтобы не делать двойную работу.

ar['name'] = ar['name'].str.lower()

Далее создаем словарик:

trans = {'а':'a', 'б':'b', 'в':'v', 'г':'g', 'д':'d', 'е':'e', 'ё':'e', 'ж':'zh', 'з':'z',  'и':'i', 'й':'y', 'к':'k', 'л':'l', 'м':'m', 'н':'n', 'о':'o', 'п':'p', 'р':'r', 'с':'s', 'т':'t', 'у':'u', 'ф':'f', 'х':'kh', 'ц':'ts', 'ч':'ch', 'ш':'sh', 'щ':'shch', 'ь':'', 'ы':'y', 'ъ':'', 'э':'e', 'ю':'yu', 'я':'ya', 'є':'e', 'і': 'i','ў':'w','µ':'m'}

В него также попали буквы из так называемой расширенной кириллицы — 'є', 'і', 'ў', которые используются в белорусском и украинском языках, а также греческая буква 'µ'. Применяем преобразование:

for s in trans:
    ar['name'] = ar['name'].str.replace(s, trans[s])

Теперь из рабочего нижнего регистра переведем все в привычный формат, где имя и фамилия начинаются с большой буквы:

ar['name'] = ar['name'].str.title()

Посмотрим, что получилось.

ar[ar['name raw'].str.lower().str[0].isin(trans.keys())].sample(10)

place sex name ... country raw name raw
99 100 M Nikolay Golovkin ... RUS Николай Головкин
95 96 M Maksim Vasilevich Chubakov ... RUS Максим Васильевич Чубаков
325 326 F Ganieva Aygul ... RUS Ганиева Айгуль
661 662 M Maksut Nizamutdinov ... RUS Максут Низамутдинов
356 357 F Kolobanova Svetlana ... RUS Колобанова Светлана
117 118 M Guskov Vladislav ... RUS Гуськов Владислав
351 352 M Kolesnikov Dmitriy ... RUS Колесников Дмитрий
92 93 M Kuznetsov Oleg ... RUS Кузнецов Олег
50 51 M Khoraykin Maksim ... RUS Хорайкин Максим
6 7 M Brylev Aleksey ... RUS Брылев Алексей

Проверим напоследок уникальные символы:

set( ''.join(ar['name'].unique()))

Out: [' ', "'", '-', '?', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J','K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z']

Все правильно. В итоге исправления коснулись 1 253 882 или 89% записей, количество уникальных имен снизилось с 660 207 до 599 186, то есть на 61 тысячу или почти на 10 %. Здорово! Cохраняем в новый файл, предварительно переведя объединение записей ar обратно в словарь протоколов rd.

pkl.dump(rd, open(r'D:tridetails4.pkl', 'wb'))

Теперь надо восстановить порядок. То есть что бы все записи имели вид – Имя Фамилия или Фамилия Имя. Какой именно – предстоит определить. Правда помимо имени и фамилии в некоторых протоколах записаны еще и отчества. И может так получиться, что один и тот же человек в разных протоколах записан по-разному – где-то с отчеством, где-то без. Это будет мешать его идентифицировать, поэтому попробуем удалить отчества. Отчества у мужчин обычно имеют окончание «вич», а у женщин — «вна». Но есть и исключения. Например – Ильич, Ильинична, Никитич, Никитична. Правда таких исключений очень мало. Как уже было отмечено формат имен в рамках одного протокола можно считать постоянным. Поэтому, чтобы избавиться от отчеств, нужно найти гонки, в которых они присутствуют. Для этого надо найти суммарное количество фрагментов «vich» и «vna» в колонке name и сравнить их с общим количеством записей в каждом протоколе. Если эти числа близки, значит отчество есть, а иначе нет. Искать строгое соответствие неразумно, т.к. даже в гонках, где записывают отчества могут принимать участие, например, иностранцы, и их запишут без него. Случается и так, что у участник забыл или не захотел указывать свое отчество. С другой стороны, есть ведь и фамилии оканчивающиеся на «vich», их много в Белоруссии, и других странах с языками славянской группы. К тому же мы сделали транслит. Можно было заняться этим анализом до транслитерации, но тогда есть шанс упустить протокол, в котором есть отчества, но изначально он уже на латинице. Так что все нормально.

Итак, будем искать все протоколы, в которых число фрагментов «vich» и «vna» в колонке name больше 50% от общего числа записей в протоколе.

wp = {} #wp – with patronymic 

for e in rd:
    nvich = (''.join(rd[e]['name'])).count('vich') 
    nvna = (''.join(rd[e]['name'])).count('vna')
    if nvich + nvna > 0.5*len(rd[e]):
        wp[e] = rd[e]

Таких протоколов 29. Вот один из них:

place sex name country ... name raw
0 1 M Yaroslav Stanislavovich Pavlishchev RUS ... Ярослав Станиславович Павлищев
1 2 M Vladimir Vasilevich Perezhigin RUS ... Владимир Васильевич Пережигин
2 3 M Vladislav Evgenevich Litvinchuk RUS ... Владислав Евгеньевич Литвинчук
3 4 M Sergey Gennadevich Gavrilenko RUS ... Сергей Геннадьевич Гавриленко
4 5 M Ivan Markovich Markin RUS ... Иван Маркович Маркин
5 6 M Nikolay Evgenevich Sokolov RUS ... Nikolay Evgenevich Sokolov
6 7 M Aram Pavlovich Kukhtiev RUS ... Арам Павлович Кухтиев
7 8 M Andrey Anatolevich Andreev RUS ... Андрей Анатольевич Андреев
8 9 M Denis Valerevich Bulgakov RUS ... Денис Валерьевич Булгаков
9 10 M Aleksandr Ivanovich Kuts RUS ... Александр Иванович Куць

Причем интересно, что, если вместо 50% взять 20% или наоборот 70% — результат не изменится, будут все те же 29. Значит мы сделали правильный выбор. Соответственно, меньше 20% — эффект фамилий, больше 70% — эффект отдельных записей без отчеств. Проверив страну с помощью сводной таблицы, оказалось, что 25 из них в России, 4 в Абхазии.

Движемся дальше. Будем обрабатывать только записи с тремя составляющими, то есть те, где есть (предположительно) фамилия, имя, отчество.

sum_n3w = 0 # sum name of 3 words
sum_nnot3w = 0 # sum name not of 3 words

for e in wp: 
    sum_n3w += len([n for n in wp[e]['name'] if len(n.split()) == 3])
    sum_nnot3w += len(wp[e]) - n3w

Таких записей большинство – 86 %. Теперь те, в которых три составляющих разделим на колонки name0, name1, name2:

for e in wp:
    ind3 = [i for i in rd[e].index if len(rd[e].loc[i,'name'].split()) == 3]
    rd[e]['name0'] = ''
    rd[e]['name1'] = ''
    rd[e]['name2'] = ''
    rd[e].loc[ind3, 'name0'] = rd[e].loc[ind3,'name'].str.split().str[0]
    rd[e].loc[ind3, 'name1'] = rd[e].loc[ind3,'name'].str.split().str[1]
    rd[e].loc[ind3, 'name2'] = rd[e].loc[ind3,'name'].str.split().str[2]

Вот как теперь выглядит один из протоколов:

name name0 name1 name2 ... name raw
0 Lekomtsev Denis Nikolaevich Lekomtsev Denis Nikolaevich ... Лекомцев Денис Николаевич
1 Ivanov Andrey Aleksandrovich Ivanov Andrey Aleksandrovich ... Иванов Андрей Александрович
2 Ivanov Evgeniy Vasilevich Ivanov Evgeniy Vasilevich ... Иванов Евгений Васильевич
3 Setepov Vladislav ... Сетепов Владислав
4 Mishanin Sergey Yurevich Mishanin Sergey Yurevich ... Мишанин Сергей Юрьевич
5 Baranov Andrey Aleksandrovich Baranov Andrey Aleksandrovich ... Баранов Андрей Александрович
6 Nakaryakov Dmitriy Valerevich Nakaryakov Dmitriy Valerevich ... Накаряков Дмитрий Валерьевич
7 Tretyakov Dmitriy Valentinovich Tretyakov Dmitriy Valentinovich ... Третьяков Дмитрий Валентинович
8 Kuznetsov Stanislav Vladimirovich Kuznetsov Stanislav Vladimirovich ... Кузнецов Станислав Владимирович
9 Dubrovin Maksim Sergeevich Dubrovin Maksim Sergeevich ... Дубровин Максим Сергеевич
10 Karpov Anatoliy Sergeevich Karpov Anatoliy Sergeevich ... Карпов Анатолий Сергеевич

Здесь, в частности, видно, что запись из двух составляющих не была обработана. Теперь для каждого протокола нужно определить в какую колонку попало отчество. Варианта всего два – name1, name2, потому что на первом место оно стоять не может. После того как определили, соберем новое имя уже без него.

for e in wp:    
    n1=(''.join(rd[e]['name1'])).count('vich')+(''.join(rd[e]['name1'])).count('vna')
    n2=(''.join(rd[e]['name2'])).count('vich')+(''.join(rd[e]['name2'])).count('vna')

    if (n1 > n2):
        rd[e]['new name'] = rd[e]['name0'] + ' ' + rd[e]['name2']
    else:
        rd[e]['new name'] = rd[e]['name0'] + ' ' + rd[e]['name1']

name name0 name1 name2 new name name raw
0 Gorik Pavel Vladimirovich Gorik Pavel Vladimirovich Gorik Pavel Горик Павел Владимирович
1 Korobov Oleg Anatolevich Korobov Oleg Anatolevich Korobov Oleg Коробов Олег Анатольевич
2 Pavlishchev Yaroslav Stanislavovich Pavlishchev Yaroslav Stanislavovich Pavlishchev Yaroslav Павлищев Ярослав Станиславович
3 Fedorov Nikolay Nikolaevich Fedorov Nikolay Nikolaevich Fedorov Nikolay Фёдоров Николай Николаевич
4 Medvedev Andrey Aleksandrovich Medvedev Andrey Aleksandrovich Medvedev Andrey Медведев Андрей Александрович
5 Popov Sergey Eduardovich Popov Sergey Eduardovich Popov Sergey Попов Сергей Эдуардович
6 Dumchev Andrey Viktorovich Dumchev Andrey Viktorovich Dumchev Andrey Думчев Андрей Викторович
7 Trusov Mikhail Vladimirovich Trusov Mikhail Vladimirovich Trusov Mikhail Трусов Михаил Владимирович
8 Demichev Yuriy Anatolevich Demichev Yuriy Anatolevich Demichev Yuriy Демичев Юрий Анатольевич
9 Pushkin Boris Sergeevich Pushkin Boris Sergeevich Pushkin Boris Пушкин Борис Сергеевич
10 Lando Aleksandr Borisovich Lando Aleksandr Borisovich Lando Aleksandr Ландо Александр Борисович

Теперь аккуратно присвоим новое имя основной колонке name, там, где оно непустое, и удалим вспомогательные колонки.

for e in wp:
    ind = rd[e][rd[e]['new name'].str.strip() != ''].index
    rd[e].loc[ind, 'name'] = rd[e].loc[ind, 'new name']
    rd[e] = rd[e].drop(columns = ['name0','name1','name2','new name'])

place sex name country ... name raw
0 1 M Yaroslav Pavlishchev RUS ... Ярослав Станиславович Павлищев
1 2 M Vladimir Perezhigin RUS ... Владимир Васильевич Пережигин
2 3 M Vladislav Litvinchuk RUS ... Владислав Евгеньевич Литвинчук
3 4 M Sergey Gavrilenko RUS ... Сергей Геннадьевич Гавриленко
4 5 M Ivan Markin RUS ... Иван Маркович Маркин
5 6 M Nikolay Sokolov RUS ... Nikolay Evgenevich Sokolov
6 7 M Aram Kukhtiev RUS ... Арам Павлович Кухтиев
7 8 M Andrey Andreev RUS ... Андрей Анатольевич Андреев
8 9 M Denis Bulgakov RUS ... Денис Валерьевич Булгаков
9 10 M Aleksandr Kuts RUS ... Александр Иванович Куць
10 11 M Aleksandr Lando RUS ... Александр Борисович Ландо

Ну вот и все. Мы отредактировали 2 035 записей. Неплохо. Сохраняемся.

pkl.dump(rd, open(r'D:tridetails5.pkl', 'wb'))

Теперь нужно привести имена к одному порядку. То есть нужно чтобы во всех протоколах сначала шло имя потом фамилия, или наоборот – сначала фамилия, потом имя, тоже во всех протоколах. Зависит от того каких больше, сейчас мы это выясним. Ситуация слегка осложняется тем, что полное имя может состоять более чем из двух слов даже после того, как мы убрали отчества.

ar['nwin'] = ar['name'].str.count(' ') + 1 # nwin – number of words in name
ar.loc[ar['name'] == '','nwin'] = 0
100*ar['nwin'].value_counts()/len(ar)

Количество слов в имени Количество записей Доля записей (%)

Количество слов в имени Количество записей Доля записей (%)
2 1285270 90.74426
3 102220 7.217066
4 22420 1.582925
0 3454 0.243864
5 2385 0.168389
6 469 0.033113
1 80 0.005648
7 57 0.004024
8 5 0.000353
10 4 0.000282
9 1 0.000071

Конечно, подавляющее большинство (91%) — это два слова – просто имя и фамилия. Но записей с тремя и четырьмя словами тоже очень много. Посмотрим национальную принадлежность таких записей:

ar[ar['nwin'] >= 3]['country'].value_counts()[:12]

Out:
ESP 28435
MEX 10561
USA 7608
DNK 7178
BRA 6321
NLD 5748
DEU 4310
PHL 3941
ZAF 3862
ITA 3691
BEL 3596
FRA 3323

Что ж, на первом месте это Испания, на втором – Мексика, испаноязычная страна, дальше США, где тоже исторически очень много латиноамериканцев. Бразилия и Филиппины – тоже испанские (и португальские) имена. Дания, Нидерланды, Германия, ЮАР, Италия, Бельгия и Франция – другое дело, там просто иногда идет какая-нибудь приставка к фамилии, поэтому и слов становится больше, чем два. Во всех этих случаях, однако, обычно само имя состоит из одного слова, а фамилия из двух, трех. Конечно, из этого правила есть исключения, но их мы уже не будем обрабатывать. Для начала для каждого протокола нужно определить, какой там все-таки порядок: имя-фамилия или наоборот. Как это сделать? Мне пришла в голову следующая идея: во-первых, разнообразие фамилий обычно гораздо больше, чем разнообразие имен. Так должно даже в рамках одного протокола. Во-вторых, длина имени обычно меньше, чем длина фамилии (даже для несоставных фамилий). Воспользуемся комбинацией этих критериев, чтобы определить предварительный порядок.

Выделим первое и последнее слово в полном имени:

ar['new name'] = ar['name']
ind = ar[ar['nwin'] < 2].index
ar.loc[ind, 'new name'] = '. .' # фиктивная запись, чтобы применить str.split() для всей колонки
ar['wfin'] = ar['new name'].str.split().str[0] #fwin  – first word in name
ar['lwin'] = ar['new name'].str.split().str[-1]#lfin – last word in name

Преобразуем объединенный датафрейм ar обратно в словарь rd, для того чтобы новые колонки nwin, ns0, ns попали в датафрейм каждой гонки. Далее определим количество протоколов с порядком “Имя Фамилия” и количество протоколов с обратным порядком согласно нашему критерию. Будем рассматривать только записи, где полное имя состоит из двух слов. Заодно сохраним имя (first name) в новой колонке:

name_surname = {}
surname_name = {}

for e in rd:
    d = rd[e][rd[e]['nwin'] == 2]

    if len(d['fwin'].unique()) < len(d['lwin'].unique()) and len(''.join(d['fwin'])) < len(''.join(d['lwin'])):
        name_surname[e] = d
        rd[e]['first name'] = rd[e]['fwin']

    if len(d['fwin'].unique()) > len(d['lwin'].unique()) and len(''.join(d['fwin'])) > len(''.join(d['lwin'])):
        surname_name[e] = d
        rd[e]['first name'] = rd[e]['lwin']

Получилось следующее: порядок Имя Фамилия – 244 протокола, порядок Фамилия Имя – 1 508 протоколов.

Соответственно будем приводить к тому формату, который встречается чаще. Сумма получилась меньше, чем общее количество, потому что мы проверяли на выполнение двух критериев одновременно, да еще и со строгим неравенством. Остались протоколы, в которых выполняется только один из критериев, или возможно, но маловероятно имеет место равенство. Но это совершенно неважно, так как формат определен.

Теперь, полагая, что мы определили порядок с достаточно высокой точностью, не забывая при этом что не со стопроцентной, воспользуемся этой информацией. Найдем самые популярные имена из колонки first name:

vc = ar['first name'].value_counts()

возьмем те, что встречались более ста раз:

pfn=vc[vc>100] #pfn – popular first names

таких оказалось 1 673. Вот первые сто из них, расположены по убыванию популярности:
['Michael', 'David', 'Thomas', 'John', 'Daniel', 'Mark', 'Peter', 'Paul', 'Christian', 'Robert', 'Martin', 'James', 'Andrew', 'Chris', 'Richard', 'Andreas', 'Matthew', 'Brian', 'Patrick', 'Scott', 'Kevin', 'Stefan', 'Jason', 'Eric', 'Christopher', 'Alexander', 'Simon', 'Mike', 'Tim', 'Frank', 'Stephen', 'Steve', 'Andrea', 'Jonathan', 'Markus', 'Marco', 'Adam', 'Ryan', 'Jan', 'Tom', 'Marc', 'Carlos', 'Jennifer', 'Matt', 'Steven', 'Jeff', 'Sergey', 'William', 'Aleksandr', 'Sarah', 'Alex', 'Jose', 'Andrey', 'Benjamin', 'Sebastian', 'Ian', 'Anthony', 'Ben', 'Oliver', 'Antonio', 'Ivan', 'Sean', 'Manuel', 'Matthias', 'Nicolas', 'Dan', 'Craig', 'Dmitriy', 'Laura', 'Luis', 'Lisa', 'Kim', 'Anna', 'Nick', 'Rob', 'Maria', 'Greg', 'Aleksey', 'Javier', 'Michelle', 'Andre', 'Mario', 'Joseph', 'Christoph', 'Justin', 'Jim', 'Gary', 'Erik', 'Andy', 'Joe', 'Alberto', 'Roberto', 'Jens', 'Tobias', 'Lee', 'Nicholas', 'Dave', 'Tony', 'Olivier', 'Philippe']

Теперь, используя это список, будем пробегать по всем протоколам и сравнивать, где больше совпадений – в первом слове из имени или в последнем. Будем рассматривать только имена, состоящие из двух слов. Если совпадений больше с последним словом, значит порядок правильный, если с первым, значит обратный. Причем здесь мы уже более уверены, значит можно использовать эти знания, и к начальному списку популярных имен с каждым проходом будем добавлять список имен их очередного протокола. Предварительно отсортируем протоколы по частоте появления имен из начального списка, чтобы избежать случайных ошибок и подготовить более обширный список к тем протоколам, в которых совпадений мало, и которые будут обрабатываться ближе к концу цикла.

sbpn = pd.DataFrame(columns = ['event', 'num pop names'], index=range(len(rd))) # sbpn - sorted by popular names

for i in range(len(rd)):
    e = list(rd.keys())[i]
    sbpn.loc[i, 'event'] = e
    sbpn.loc[i, 'num pop names'] = len(set(pfn).intersection(rd[e]['first name']))

sbnp=sbnp.sort_values(by = 'num pop names',ascending=False)
sbnp = sbnp.reset_index(drop=True) 

event num pop names
0 Ironman World Championship 70.3 2016 811
1 Ironman World Championship 2019 781
2 Ironman World Championship 70.3 2015 778
3 Ironman Mallorca 70.3 2014 776
4 Ironman World Championship 2018 766
5 Challenge Roth Long 2019 759
... ... ...
1917 Challenge Gran Canaria Olympic 2019 0
1918 Challenge Gran Canaria Middle 2017 0
1919 Challenge Forte Village-Sardinia Sprint 2017 0
1920 ITU European Cup Kuopio Sprint 2007 0
1921 ITU World Cup Madeira Olympic 2002 0

tofix = []

for i in range(len(rd)):
    e = sbpn.loc[i, 'event']

    if len(set(list(rd[e]['fwin'])).intersection(pfn)) > len(set(list(rd[e]['lwin'])).intersection(pfn)):
        tofix.append(e)
        pfn = list(set(pfn + list(rd[e]['fwin'])))
    else:
        pfn = list(set(pfn + list(rd[e]['lwin'])))

Нашлось 235 протоколов. То есть примерно столько же, сколько получилось в первом приближении (244). Для уверенности выборочно просмотрел первые три записи из каждого, убедился, что все правильно. Также проверил, что первый этап сортировки дал 36 ложных записей из класса Имя Фамилия и 2 ложный из класса Фамилия Имя. Просмотрел по три первых записи из каждого, действительно, второй этап сработал отлично. Теперь, собственно, осталось исправить те протоколы, где обнаружен неправильный порядок:

for e in tofix:
    ind = rd[e][rd[e]['nwin'] > 1].index
    rd[e].loc[ind,'name'] = rd[e].loc[ind,'name'].str.split(n=1).str[1] + ' ' +                   rd[e].loc[ind,'name'].str.split(n=1).str[0]

Здесь в сплите мы ограничили количество кусков с помощью параметра n. Логика такая: имя – это одно слово, первое в полном имени. Все остальное – фамилия (может состоять из нескольких слов). Просто меняем их местами.

Теперь избавляемся от ненужных колонок и сохраняемся:

for e in rd:
    rd[e] = rd[e].drop(columns = ['new name', 'first name', 'fwin','lwin', 'nwin'])

pkl.dump(rd, open(r'D:tridetails6.pkl', 'wb'))

Проверяем результат. Случайная десятка исправленных записей:

place sex name country group ... name raw
188 189 M Azhel Dmitriy BLR ... Дмитрий Ажель
96 97 M Bostina Cristian ROU ... Cristian Bostina
1757 1758 M Lowe Jonathan AUS M30-34 ... Jonathan LOWE
599 600 M Baerwald Manuel DEU ... Manuel BAERWALD
657 658 M Krumdieck Ralf DEU ... Ralf KRUMDIECK
354 355 F Knapp Samantha USA F30-34 ... Samantha Knapp
375 376 M Rintalaulaja Mika FIN M40-44 ... Mika Rintalaulaja
1304 1305 M Dee Jim USA M50-54 ... Jim DEE
178 179 M Halibert Greg FRA ... GREG HALIBERT
2740 2741 F Comia Marissa USA F45-49 ... Marissa COMIA

Всего исправлено 108 тысяч записей. Количество уникальных полных имен сократилось с 598 до 547 тысяч. Отлично! С форматированием закончили.

Часть 3. Восстановление неполных данных

Теперь перейдем к восстановлению пропущенных данных. А такие есть.

Страна

Начнем со страны. Найдем все записи, в которых не указана страна:

arnc = ar[ar['country'] == ''] #arnc – all records with no country

Их 3 221. Вот случайные 10 из них:

event place sex name country group ... country raw
... 1633 M Guerrero Pla Angel M30-34 ... E
... 258 M Bellm Mathias M35-39 ... D
... 655 M Moratto Alessio M40-44 ... I
... 1317 M Solari Jean-Jacques M50-54 ... TAH
... 1311 F Duranel Isabelle F40-44 ... F
... 1012 M Endler Maximilian M40-44 ... D
... 284 M Schreiner Jorg M40-44 ... D
... 14 M Butturini Jacopo ... ITU
... 204 M Lindner Thomas M40-44 ... D
... 1168 M Gramke Peter M45-49 ... D

nnc = arnc['name'].unique() #nnc - names with no country

Уникальных имен среди записей без страны – 3 051. Посмотрим, можно ли сократить это число.

Дело в том, что в триатлоне люди редко ограничиваются лишь одной гонкой, обычно участвуют в соревнованиях периодически, несколько раз за сезон, из года в год, постоянно тренируясь. Поэтому для многих имен в данных наверняка есть больше одной записи. Чтобы восстановить информацию о стране, попробуем найти записи с таким же именем среди тех, в которых страна указана.

arwc = ar[ar['country'] != ''] #arwc – all records with country
nwc = arwc['name'].unique() #nwc – names with country
tofix = set(nnc).intersection(nwc)

Out: ['Kleber-Schad Ute Cathrin', 'Sellner Peter', 'Pfeiffer Christian', 'Scholl Thomas', 'Petersohn Sandra', 'Marchand Kurt', 'Janneck Britta', 'Angheben Riccardo', 'Thiele Yvonne', 'Kie?Wetter Martin', 'Schymik Gerhard', 'Clark Donald', 'Berod Brigitte', 'Theile Markus', 'Giuliattini Burbui Margherita', 'Wehrum Alexander', 'Kenny Oisin', 'Schwieger Peter', 'Grosse Bianca', 'Schafter Carsten', 'Breck Dirk', 'Mautes Christoph', 'Herrmann Andreas', 'Gilbert Kai', 'Steger Peter', 'Jirouskova Jana', 'Jehrke Michael', 'Valentine David', 'Reis Michael', 'Wanka Michael', 'Schomburg Jonas', 'Giehl Caprice', 'Zinser Carsten', 'Schumann Marcus', 'Magoni Livio', 'Lauden Yann', 'Mayer Dieter', 'Krisa Stefan', 'Haberecht Bernd', 'Schneider Achim', 'Gibanel Curto Antonio', 'Miranda Antonio', 'Juarez Pedro', 'Prelle Gerrit', 'Wuste Kay', 'Bullock Graeme', 'Hahner Martin', 'Kahl Maik', 'Schubnell Frank', 'Hastenteufel Marco', …]

Таких оказалось 2 236, то есть почти три четверти. Теперь для каждого имени из этого списка нужно определить страну по тем записям, где она есть. Но бывает так, что одно и то же имя встречается в нескольких записях и в них разные страны. Это или тезки, или, может быть, человек переехал. Поэтому сначала обработаем те, где все однозначно.

fix = {}

for n in tofix:
    nr = arwc[arwc['name'] == n] 
	
    if len(nr['country'].unique()) == 1:
        fix[n] = nr['country'].iloc[0]

Cделал в цикле. Но, честно говоря, отрабатывает долго – примерно три минуты. Если бы записей было на порядок больше, то пришлось бы, наверное, придумывать векторную реализацию. Нашлось 2 013 записей, или 90% от потенциально возможных.

Имена, для которых в разных записях могут встречаться разные страны, возьмем ту страну, которая встречается чаще всего.

if n not in fix:
    nr = arwc[arwc['name'] == n]
    vc = nr['country'].value_counts()    
	
    if vc[0] > vc[1]:
        fix[n] = vc.index[0]

Таким образом были найдены соответствия для 2 208 имен или 99% от всех потенциально возможных.
{'Kleber-Schad Ute Cathrin': 'DEU', 'Sellner Peter': 'AUT', 'Pfeiffer Christian': 'AUT', 'Scholl Thomas': 'DEU', 'Petersohn Sandra': 'DEU', 'Marchand Kurt': 'BEL', 'Janneck Britta': 'DEU', 'Angheben Riccardo': 'ITA', 'Thiele Yvonne': 'DEU', 'Kie?Wetter Martin': 'DEU', 'Clark Donald': 'GBR', 'Berod Brigitte': 'FRA', 'Theile Markus': 'DEU', 'Giuliattini Burbui Margherita': 'ITA', 'Wehrum Alexander': 'DEU', 'Kenny Oisin': 'IRL', 'Schwieger Peter': 'DEU', 'Schafter Carsten': 'DEU', 'Breck Dirk': 'DEU', 'Mautes Christoph': 'DEU', 'Herrmann Andreas': 'DEU', 'Gilbert Kai': 'DEU', 'Steger Peter': 'AUT', 'Jirouskova Jana': 'CZE', 'Jehrke Michael': 'DEU', 'Wanka Michael': 'DEU', 'Giehl Caprice': 'DEU', 'Zinser Carsten': 'DEU', 'Schumann Marcus': 'DEU', 'Magoni Livio': 'ITA', 'Lauden Yann': 'FRA', 'Mayer Dieter': 'DEU', 'Krisa Stefan': 'DEU', 'Haberecht Bernd': 'DEU', 'Schneider Achim': 'DEU', 'Gibanel Curto Antonio': 'ESP', 'Juarez Pedro': 'ESP', 'Prelle Gerrit': 'DEU', 'Wuste Kay': 'DEU', 'Bullock Graeme': 'GBR', 'Hahner Martin': 'DEU', 'Kahl Maik': 'DEU', 'Schubnell Frank': 'DEU', 'Hastenteufel Marco': 'DEU', 'Tedde Roberto': 'ITA', 'Minervini Domenico': 'ITA', 'Respondek Markus': 'DEU', 'Kramer Arne': 'DEU', 'Schreck Alex': 'DEU', 'Bichler Matthias': 'DEU', …}

Применим эти соответствия:

for n in fix:
    ind = arnc[arnc['name'] == n].index
    ar.loc[ind, 'country'] = fix[n]

event place sex name country group ... country raw
... 1633 M Guerrero Pla Angel ESP M30-34 ... E
... 258 M Bellm Mathias DEU M35-39 ... D
... 655 M Moratto Alessio ITA M40-44 ... I
... 1317 M Solari Jean-Jacques PYF M50-54 ... TAH
... 1311 F Duranel Isabelle FRA F40-44 ... F
... 1012 M Endler Maximilian DEU M40-44 ... D
... 284 M Schreiner Jorg DEU M40-44 ... D
... 14 M Butturini Jacopo HRV ... ITU
... 204 M Lindner Thomas DEU M40-44 ... D
... 1168 M Gramke Peter DEU M45-49 ... D

После наших исправлений количество записей без страны сократилось до 909, то есть больше чем в три раза. Хотя общее количество в 2 208 не так велико на фоне полутора миллионов, все равно приятно.

Далее как обычно, переводим объединенный датафрейм ar обратно в словарь rd и сохраняем.

pkl.dump(rd, open(r'D:tridetails7.pkl', 'wb'))

Пол

Так же, как и в случае со странами есть записи, в которых не указан пол участника.

ar[ar['sex'] == '']

Таких 2 538. Относительно немного, но снова попытаемся сделать еще меньше. Сохраним исходные значения в новой колонке.

ar['sex raw'] =ar['sex']

В отличие от стран, где мы восстанавливали информацию по имени из других протоколов, здесь все немного сложнее. Дело в том, что в данных полно ошибок и существует немало имен (всего 2 101), которые встречаются с пометками обоих полов.

arws = ar[(ar['sex'] != '')&(ar['name'] != '')] #arws – all records with sex
snds = arws[arws.duplicated(subset='name',keep=False)]#snds–single name different sex
snds = snds.drop_duplicates(subset=['name','sex'], keep = 'first')
snds = snds.sort_values(by='name')
snds = snds[snds.duplicated(subset = 'name', keep=False)]

snds

event place sex name country group ... country raw group raw sex raw
... 428 F Aagaard Ida NOR F40-44 ... NOR F40-44 F
... 718 M Aagaard Ida NOR M40-44 ... NOR M40-44 M
740 M Aarekol Tove Aase NOR M50-54 ... NOR M50-54 M
... 520 F Aarekol Tove Aase NOR F50-54 ... NOR F50-54 F
... 665 F Aaroy Torunn NOR F40-44 ... NOR F40-44 F
... 1591 M Aaroy Torunn NOR M40-44 ... NOR M40-44 M
... 70 M Aberg Cobo Dolores ARG FPRO ... ARG FPRO M
... 1258 F Aberg Cobo Dolores ARG F30-34 ... ARG F30-34 F
... 1909 F Aboulfaida Zineb MAR F35-39 ... MAR F35-39 F
... 340 M Aboulfaida Zineb MAR M35-39 ... MAR M35-39 M
... 63 F Abram Felicity AUS FPRO ... AUS FPRO F
... 38 M Abram Felicity AUS FJUNIOR ... AUS FJUNIOR M
... 134 M Abramowski Jannicke DEU FPRO ... GER FPRO M
... 323 F Abramowski Jannicke DEU F25-29 ... GER F25-29 F
... 21 M Abrosimova Anastasia RUS FPRO ... RUS FPRO M
... 177 F Abrosimova Anastasia RUS FPRO ... RUS FPRO F
... 188 M Abysova Irina RUS FPRO ... RUS FPRO M
... 60 F Abysova Irina RUS FPRO ... RUS FPRO F
... 312 M Acaron Fabiola PRI FJUNIOR ... PUR FJUNIOR M
... 294 F Acaron Fabiola PRI F45-49 ... PUR F45-49 F
... 1500 M Achampong Benjamin GBR M35-39 ... GBR M35-39 M
... 749 F Achampong Benjamin GBR M35-39 ... GBR M35-39 F

Да, в принципе существуют имена унисекс (или андрогинные), то есть такие которыми называют и мальчиков, и девочек. А для азиатских атлетов вообще трудно определить пол по имени – возможно мне просто не хватает знаний. Однако, с трудом верится, чтобы имя Ирина или Анастасия принадлежало мужчине, а Бенджамином звали женщину. В добавок к этому, в какой-то момент я обнаружил, что существует большое количество протоколов, в которых все участники помечены каким-то одним полом.

rss = [rd[e] for e in rd if len(rd[e][rd[e]['sex'] != '']['sex'].unique()) == 1] #rss – races with single sex

Всего их 633. Казалось бы, это вполне возможно, просто протокол отдельно по женщинам, отдельно по мужчинам. Но дело в том, что почти во всех этих протоколах встречаются возрастные группы обоих полов (мужские возрастные группы начинаются с буквы M, женские – с буквы F). Например:

'ITU World Cup Tiszaujvaros Olympic 2002'

place sex name country group ... country raw group raw name raw
76 M Dederko Ewa POL FPRO ... POL FPRO Dederko Ewa
84 M Chenevier Giunia ITA FPRO ... ITA FPRO Chenevier Giunia
36 M O'Grady Graham NZL MPRO ... NZL MPRO O'Grady Graham
23 M Danek Michal CZE MPRO ... CZE MPRO Danek Michal
74 M Peon Carole FRA FPRO ... FRA FPRO Peon Carole
48 M Hechenblaickner Daniel AUT MPRO ... AUT MPRO Hechenblaickner Daniel
70 M Blatchford Liz GBR FPRO ... GBR FPRO Blatchford Liz
1 M Walton Craig AUS MPRO ... AUS MPRO Walton Craig
20 M Hobor Peter HUN MPRO ... HUN MPRO Hobor Peter
56 M Kaldau Szabolcs HUN MPRO ... HUN MPRO Kaldau Szabolcs

Ожидается, что название возрастной группы начинается с буквы M для мужчин и с буквы F для женщин. В предыдущих двух примерах, несмотря на ошибки в колонке sex, название группы все еще, вроде бы, верно описывало пол участника. На основании нескольких выборочных примеров, делаем предположение, что группа указана верно, а пол может быть указан ошибочно. Найдем все записи, где первая буква в названии группы не соответствует полу. Будем брать начальное название группы group raw, так как при стандартизации многие записи остались без группы, но нам сейчас нужна только первая буква, так что стандарт не важен.

ar['grflc'] = ar['group raw'].str.upper().str[0] #grflc – group raw first letter capital
grncs = ar[(ar['grflc'].isin(['M','F']))&(ar['sex']!=ar['grflc'])] #grncs – group raw not consistent with sex

Таких записей 26 161. Немало. Что ж, исправим пол в соответствии с названием возрастной группы:

ar.loc[grncs.index, 'sex'] = grncs['grflc']

Посмотрим на результат:

event place sex name country group ... country raw group raw sex raw grflc
... 59 F Ueda Ai JPN FPRO ... JPN FPRO M F
... 50 F Zemanova Lenka CZE FPRO ... CZE FPRO M F
... 83 F Spearing Kyleigh USA FPRO ... USA FPRO M F
... 63 F Abysova Irina RUS FPRO ... RUS FPRO M F
... 57 F Knapp Anja DEU FPRO ... GER FPRO M F
... 68 M Matthews Andrew GBR M30-34 ... GBR M30-34 F M
... 46 F Rappaport Summer USA FPRO ... USA FPRO M F
... 60 F Reid Aileen IRL FPRO ... IRL FPRO M F
... 142 F Mcdowall Edwina GBR F45-49 ... GBR F45-49 F
... 141 M O'Bray Luke GBR M30-34 ... GBR M30-34 M

Хорошо. Сколько же теперь осталось записей без пола?

ar[(ar['sex'] == '')&(ar['name'] != '')]

Оказывается, ровно одна!

event place sex name country group ... country raw group raw sex raw grflc
London Triathlon Olympic 2019 672 Stather Emily GBR ... GBR unknown U


Что ж, группа действительно не указана, но, судя по всему, это женщина. Эмили – женское имя, к тому же эта участница (или ее тезка) финишировала годом ранее, и в том протоколе пол и группа указаны.

event place sex name country group ... country raw group raw sex raw grflc
Ironman Staffordshire 70.3 2018 1859 F Stather Emily GBR F40-44 ... GBR F40-44 F F


Восстановим здесь вручную* и пойдем дальше.

ar.loc[arns.index, 'sex'] = 'F'

Теперь все записи с полом.

*Вообще, так делать, конечно, неправильно – при повторных прогонах если изменится что-то в цепочке до этого, например в конвертации имени, тогда записей без пола может оказаться больше одной, и не все они будут женскими, возникнет ошибка. Поэтому нужно либо вставлять тяжелую логику для поиска участника с таким же именем и с указанием пола в других протоколах, как для восстановления страна, и как то ее тестировать, либо, чтобы излишне не усложнять, к этой логике добавить проверку, что запись найдена только одна и имя такое-то, иначе выдавать исключение, которое остановит выполнение всего ноутбука, можно будет заметить отклонение от плана и вмешаться.

if len(arns) == 1 and arns['name'].iloc[0] == 'Stather Emily':
    ar.loc[arns.index, 'sex'] = 'F'
else:
    raise Exception('Different scenario!')

Казалось бы, на этом можно и успокоиться. Но дело в том, что исправления основаны на предположении, что группа указана верно. И это действительно так. Почти всегда. Почти. Все-таки несколько нестыковок были случайно замечены, поэтому попробуем теперь определить их все, ну или как можно больше. Как уже упоминалось, в первом примере насторожило именно то, что пол не соответствовал имени исходя из собственных представлений о мужских и женских именах.

Найдем все имена у мужских и женских записей. Здесь под именем понимается именно имя, а не полное имя, то есть без фамилии, то, что по-английски называется first name.

ar['fn'] = ar['name'].str.split().str[-1] #fn – first name
mfn = list(ar[ar['sex'] == 'M']['fn'].unique()) #mfn – male first names

Всего в списке 32 508 мужских имен. Вот 50 самых популярных:
['Michael', 'David', 'Thomas', 'John', 'Daniel', 'Mark', 'Peter', 'Paul', 'Christian', 'Robert', 'Martin', 'James', 'Andrew', 'Chris', 'Richard', 'Andreas', 'Matthew', 'Brian', 'Kevin', 'Patrick', 'Scott', 'Stefan', 'Jason', 'Eric', 'Alexander', 'Christopher', 'Simon', 'Mike', 'Tim', 'Frank', 'Stephen', 'Steve', 'Jonathan', 'Marco', 'Markus', 'Adam', 'Ryan', 'Tom', 'Jan', 'Marc', 'Carlos', 'Matt', 'Steven', 'Jeff', 'Sergey', 'William', 'Aleksandr', 'Andrey', 'Benjamin', 'Jose']

ffn = list(ar[ar['sex'] == 'F']['fn'].unique()) #ffn – female first names

Женских меньше – 14 423. Cамые популярные:
['Jennifer', 'Sarah', 'Laura', 'Lisa', 'Anna', 'Michelle', 'Maria', 'Andrea', 'Nicole', 'Jessica', 'Julie', 'Elizabeth', 'Stephanie', 'Karen', 'Christine', 'Amy', 'Rebecca', 'Susan', 'Rachel', 'Anne', 'Heather', 'Kelly', 'Barbara', 'Claudia', 'Amanda', 'Sandra', 'Julia', 'Lauren', 'Melissa', 'Emma', 'Sara', 'Katie', 'Melanie', 'Kim', 'Caroline', 'Erin', 'Kate', 'Linda', 'Mary', 'Alexandra', 'Christina', 'Emily', 'Angela', 'Catherine', 'Claire', 'Elena', 'Patricia', 'Charlotte', 'Megan', 'Daniela']

Хорошо, вроде выглядит логично. Посмотрим есть ли пересечения.

mffn = set(mfn).intersection(ffn) #mffn – male-female first names

Есть. И их 2 811. Посмотрим на них более пристально. Для начала, узнаем сколько записей с этими именами:

armfn = ar[ar['fn'].isin(mffn)] #armfn – all records with male-female names

Их 725 562. То есть половина! Это удивительно! Всего уникальных имен почти 37 000, но у половины записей в общей сложности только 2 800. Посмотрим, что это за имена, какие из них самые популярные. Для этого создадим новый датафрейм, где эти имена будут индексами:

df = pd.DataFrame(armfn['fn'].value_counts())
df = df.rename(columns={'fn':'total'})

Вычислим, сколько мужских и женских записей с каждым из них.

df['M'] = armfn[armfn['sex'] == 'M']['fn'].value_counts()
df['F'] = armfn[armfn['sex'] == 'F']['fn'].value_counts()

total M F
Michael 20648 20638 10
David 18493 18485 8
Thomas 12746 12740 6
John 11634 11632 2
Daniel 11045 11041 4
Mark 10968 10965 3
Peter 10692 10691 1
Paul 9616 9614 2
Christian 8863 8859 4
Robert 8666 8664 2
... ... ... ...

Так… выглядит подозрительно. Насколько мне известно, все эти имена мужские. Но с каждым из них есть какое-то небольшое количество женских записей. Вероятно, это ошибки в данных.

Посмотрим на женские имена.

df.sort_values(by = 'F', ascending=False)

total M F
Jennifer 3652 3 3649
Sarah 3288 4 3284
Laura 2636 3 2633
Lisa 2618 2 2616
Anna 2563 10 2553
Michelle 2373 1 2372
Maria 2555 386 2169
Andrea 4323 2235 2088
Nicole 2025 6 2019
Julie 1938 2 1936
... ... ... ...

То же самое. Почти. Выделяется Andrea, которое действительно является андрогинным именем, и чуть меньше Maria, уже по непонятной причине.

На самом деле не стоит забывать, что мы исследуем данные людей из очень разных стран, можно сказать, по всему миру. В разных культурах одно и то же имя может использоваться совершенно по-разному. Вот пример. Karen — одно из популярных женских имен из нашего списка, но с другой стороны есть имя Карéн, которое в транслите будет писаться так же, но носят его исключительно мужчины. К счастью, существует пакет, которых хранит всю эту мировую мудрость. Называется gender-guesser.

Работает он так:

import gender_guesser.detector as gg

d = gg.Detector()
d.get_gender(u'Oleg')

Out: 'male'

d.get_gender(u'Evgeniya')

Out: 'female'

Все классно. Но если проверить имя Andrea, то он тоже выдает female, что не совсем верно. Правда здесь есть выход. Если взглянуть, на свойство names у детектора, то там становится видно всю неоднозначность.

d.names['Andrea']

Out: {'female': ' 4 4 3 4788 64 579 34 1 7 ',
'mostly_female': '5 6 7 ',
'male': ' 7 '}

Ага, то есть get_gender выдает просто самый вероятный вариант, но на самом деле все может быть гораздо сложнее. Проверим другие имена:

d.names['Maria']

Out: {'female': '686 6 A 85986 A BA 3B98A75457 6 ',
'mostly_female': ' BBC A 678A9 '}

d.names['Oleg']

Out: {'male': ' 6 2 99894737 3 '}

То есть в списке names каждому имени соответствует одна или несколько пар ключ-значение, где ключ – это пол: male, female, mostly_male, mostly_female, andy, а значение – список значений соответствующих стране: 1,2,3 … 9ABC... Страны такие:

d.COUNTRIES

Out: ['great_britain', 'ireland', 'usa', 'italy', 'malta', 'portugal', 'spain', 'france', 'belgium', 'luxembourg', 'the_netherlands', 'east_frisia', 'germany', 'austria', 'swiss', 'iceland', 'denmark', 'norway', 'sweden', 'finland', 'estonia', 'latvia', 'lithuania', 'poland', 'czech_republic', 'slovakia', 'hungary', 'romania', 'bulgaria', 'bosniaand', 'croatia', 'kosovo', 'macedonia', 'montenegro', 'serbia', 'slovenia', 'albania', 'greece', 'russia', 'belarus', 'moldova', 'ukraine', 'armenia', 'azerbaijan', 'georgia', 'the_stans', 'turkey', 'arabia', 'israel', 'china', 'india', 'japan', 'korea', 'vietnam', 'other_countries']

Я до конца не понял, что конкретно обозначают цифро-буквенные значения или их отсутствие в списке. Но это было и не важно, так как я решил ограничиться использованием только тех имен, что имеют однозначное толкование. То есть для которых есть только одна пара ключ-значение и ключ – это либо male, либо female. Для каждого имени из нашего датафрейма, запишем его интерпретацию gender-guesser:

df['sex from gg'] = ''

for n in df.index:
    if n in list(d.names.keys()):
        options = list(d.names[n].keys())
        if len(options) == 1 and options[0] == 'male':
            df.loc[n, 'sex from gg'] = 'M'
        if len(options) == 1 and options[0] == 'female':
            df.loc[n, 'sex from gg'] = 'F'

Получилось 1 150 имен. Вот самые популярные из них, которые уже рассматривались выше:

total M F sex from gg
Michael 20648 20638 10 M
David 18493 18485 8 M
Thomas 12746 12740 6 M
John 11634 11632 2 M
Daniel 11045 11041 4 M
Mark 10968 10965 3 M
Peter 10692 10691 1 M
Paul 9616 9614 2 M
Christian 8863 8859 4
Robert 8666 8664 2 M


total M F sex from gg
Jennifer 3652 3 3649 F
Sarah 3288 4 3284 F
Laura 2636 3 2633 F
Lisa 2618 2 2616 F
Anna 2563 10 2553 F
Michelle 2373 1 2372 F
Maria 2555 386 2169
Andrea 4323 2235 2088
Nicole 2025 6 2019 F
Julie 1938 2 1936

Что ж, неплохо. Теперь применим эту логику ко всем записям.

all_names = ar['fn'].unique()

male_names = []
female_names = []

for n in all_names:
    if n in list(d.names.keys()):
        options = list(d.names[n].keys())
        if len(options) == 1:
            if options[0] == 'male':
                male_names.append(n)
            if options[0] == 'female':
                female_names.append(n)

Найдено 7 091 мужских имен и 5 054 женских. Применяем преобразование:

tofixm = ar[ar['fn'].isin(male_names)]
ar.loc[tofixm.index, 'sex'] = 'M'
tofixf = ar[ar['fn'].isin(female_names)]
ar.loc[tofixf.index, 'sex'] = 'F'

Смотрим результат:

ar[ar['sex']!=ar['sex raw']]

Исправлено 30 352 записи (вместе с исправлением по названию группы). Как обычно, 10 случайных:

event place sex name country group ... country raw group raw sex raw grflc
... 37 F Pilz Christiane DEU FPRO ... GER FPRO M F
... 92 F Brault Sarah-Anne CAN FPRO ... CAN FPRO M F
... 96 F Murphy Susanna IRL FPRO ... IRL FPRO M F
... 105 F Spoelder Romy NLD ... NED FJUNIOR M F
... 424 M Watson Tom GBR M40-44 ... GBR M40-44 F M
... 81 F Morel Charlotte FRA ... FRA FJUNIOR M F
... 65 F Selekhova Olga RUS ... RUS FU23 M F
... 166 F Keat Rebekah AUS ... AUS FJUNIOR M F
... 119 F Eim Nina DEU ... GER FQUAL… M F
... 73 F Sukhoruchenkova Evgenia RUS FPRO ... RUS FPRO M F

Теперь, раз уж мы уверены, что правильно определили пол, приведем в соответствие и стандартные группы. Посмотрим, где они не совпадают:

ar['gfl'] = ar['group'].str[0]
gncws = ar[(ar['sex'] != ar['gfl']) & (ar['group']!='')]

4 248 записи. Заменяем первую букву:

ar.loc[gncws.index, 'group'] = ar.loc[gncws.index, 'sex'] + ar.loc[gncws.index, 'group'].str[1:].index, 'sex']

event place sex name country group ... country raw group raw sex raw
... 803 F Kenney Joelle USA F35-39 ... USA M35-39 M
... 1432 M Holmberg Henriette Gorm DNK M45-49 ... DEN F45-49 F
... 503 M Tai Oy Leen MYS M40-44 ... MAS F40-44 F
... 236 F Dissanayake Aruna LKA F25-29 ... SRI M25-29 M
... 1349 F Delos Reyes Joshua Rafaelle PHL F18-24 ... PHI M18-24 M
... 543 F Vandekendelaere Janique BEL F50-54 ... BEL M50-54 M
... 1029 M Provost Shaun USA M25-29 ... USA F25-29 F
... 303 F Torrens Vadell Macia ESP F30-34 ... ESP M30-34 M
... 1338 F Suarez Renan BOL F35-39 ... BOL M35-39 M
... 502 F Everlo Linda NLD F30-34 ... NED M30-34 M

Вероятно, где-то исправления оказались некорректными, но все рано думаю, что они принесли больше пользы чем вреда. Для статистики это важно.

На этом с восстановлением пола все. Удаляем рабочие колонки, переводим в словарь и сохраняемся.

pkl.dump(rd, open(r'D:tridetails8.pkl', 'wb'))

На этом вообще все, с восстановлением неполных данных.

Обновление сводки

Осталось обновить сводную таблицу уточненными данными по количеству мужчин и женщин, и др.

rs['total raw'] = rs['total']
rs['males raw'] = rs['males']
rs['females raw'] = rs['females']
rs['rus raw'] = rs['rus']

for i in rs.index:
    e = rs.loc[i,'event']
    rs.loc[i,'total'] = len(rd[e])
    rs.loc[i,'males'] = len(rd[e][rd[e]['sex'] == 'M'])
    rs.loc[i,'females'] = len(rd[e][rd[e]['sex'] == 'F'])
    rs.loc[i,'rus'] = len(rd[e][rd[e]['country'] == 'RUS'])

len(rs[rs['total'] != rs['total raw']])

Out: 288

len(rs[rs['males'] != rs['males raw']])

Out:962

len(rs[rs['females'] != rs['females raw']])

Out: 836

len(rs[rs['rus'] != rs['rus raw']])

Out: 8

pkl.dump(rs, open(r'D:trisummary6.pkl', 'wb'))

Часть 4. Выборка

Сейчас триатлон очень популярен. За сезон проходит множество открытых соревнований, в которых принимает участие огромное количество атлетов, в основном любителей. Но так было не всегда. В наших данных есть записи начиная с 1990 года. Пролистывая tristats.ru я заметил, что гонок значительно больше в последние годы, и очень мало в первые. Но теперь, когда наши данные подготовлены, можно посмотреть на это более внимательно.

Десятилетний период

Подсчитаем количество гонок и финишеров в каждом году:

rs['year'] = pd.DatetimeIndex(rs['date']).year
years = range(rs['year'].min(),rs['year'].max())
rsy = pd.DataFrame(columns = ['races', 'finishers', 'rus', 'RUS'], index = years) #rsy – races summary by year

for y in rsy.index:
    rsy.loc[y,'races'] = len(rs[rs['year'] == y])
    rsy.loc[y,'finishers'] = sum(rs[rs['year'] == y]['total'])
    rsy.loc[y,'rus'] =  sum(rs[rs['year'] == y]['rus'])
    rsy.loc[y,'RUS'] = len(rs[(rs['year'] == y)&(rs['country'] == 'RUS')])

year races finishers rus RUS
1990 1 286 5 0
1991 0 0 0 0
1992 1 317 3 0
1993 2 887 3 0
1994 2 128 3 0
1995 3 731 7 0
1996 3 776 6 0
1997 3 403 11 0
1998 4 583 21 0
1999 10 1106 26 0
2000 10 1231 29 0
2001 11 1992 32 0
2002 21 2249 100 0
2003 30 3152 158 0
2004 19 5488 128 1
2005 16 3024 244 1
2006 29 6210 369 1
2007 44 12153 444 1
2008 43 13830 369 1
2009 49 27047 478 1
2010 47 26528 366 1
2011 77 45412 848 5
2012 96 75590 1055 4
2013 98 86617 2165 9
2014 135 138018 3188 11
2015 164 172375 4846 15
2016 192 178630 7541 27
2017 238 185473 8825 42
2018 278 203031 10954 54
2019 293 220901 13354 59

RUS – гонки в России. rus – финишеры из России.

Вот как это выглядит на графике:
Большой туториал по обработке спортивных данных на python - 10

Видно, что количества гонок и участников в начале периода и в конце просто несоизмеримы. Значительное увеличение общего числа гонок начинается с 2011 года, тогда же возрастает и количество стартов в России. При этом рост количества участников можно наблюдать еще в 2009 году. Это может говорить о возросшем интересе среди участников, то есть возросшем спросе, за которым спустя два года возросло предложение, то есть количество стартов. Однако не стоит забывать, что данные могут быть не полными и какие-то, а возможно и многие гонки в них отсутствуют. В том числе из-за того, что проект по сбору этих данных начался только в 2010 году, что тоже может объяснять значительный скачок на графике именно в этот момент. В том числе поэтому, для дальнейшего анализа я решил взять последние 10 лет. Это достаточно длинный период, для того чтобы отследить какие-то тренды за несколько лет, при этом достаточно короткий чтобы туда не попали, в основном, профессиональные соревнования из 90-х и начала 2000-х.

rs = rs[(rs['year']>=2010)&(rs['year']<= 2019)]

Большой туториал по обработке спортивных данных на python - 11

В выбранный период, кстати, попало 84% гонок и 94% финишеров.

Любительские старты

Итак, подавляющее большинство участников выбранных стартов — это атлеты-любители, поэтому хорошую статистику можно получить именно по ним. Честно говоря, это и представляло основной интерес для меня, так как сам участвую в таких стартах, но по уровню очень далек от олимпийских чемпионов. Однако, профессиональные соревнования очевидно тоже проходили и в выбранный период. Чтобы не смешивать показатели по любительским и профессиональным гонкам, было решено убрать из рассмотрения последние. Как их определить? По скоростям. Вычислим их. На одном из начальных этапов подготовки данных мы уже определили какой тип дистанции был на каждой гонке – спринт, олимпийская, половинка, железная. Для каждой из них четко определен километраж этапов – плавательного, вело и бегового. Это 0.75+20+5 для спринта, 1.5+40+10 для олимпийской, 1.9+90+21.1 для половинки и 3.8+180+42.2 для железной. Конечно, по факту, для любого типа реальные цифры могут разниться от гонки к гонке условно до одного процента, но информации об этом нету, так что будем считать, что все было точно.

rs['km'] = ''

rs.loc[rs['dist'] == 'sprint', 'km'] = 0.75+20+5
rs.loc[rs['dist'] == 'olympic', 'km'] = 1.5+40+10
rs.loc[rs['dist'] == 'half', 'km'] = 1.9+90+21.1
rs.loc[rs['dist'] == 'full', 'km'] = 3.8+180+42.2

Вычислим среднюю и максимальную скорости на каждой гонке. Под максимальной здесь понимается средняя скорость атлета, занявшего первое место.

for index, row in rs.iterrows():
    e = row['event']
    rd[e]['th'] = pd.TimedeltaIndex(rd[e]['result']).seconds/3600
    rd[e]['v'] = rs.loc[i, 'km'] / rd[e]['th']

for index, row in rs.iterrows():
    e = row['event']
    rs.loc[index,'vmax'] = rd[e]['v'].max()
    rs.loc[index,'vavg'] = rd[e]['v'].mean()

Большой туториал по обработке спортивных данных на python - 12

Что ж, можно видеть, что основная масса скоростей собрались кучно примерно между 15 км/ч и 30 км/ч, но есть определенное количество совершенно «космических» значений. Отсортируем по средней скорости и посмотрим, сколько их:

rs = rs.sort_values(by='vavg')

Большой туториал по обработке спортивных данных на python - 13

Здесь мы изменили шкалу и можно оценить диапазон более точно. Для средних скоростей это примерно от 17 км/ч до 27 км/ч, для максимальных – от 18 км/ч до 32 км/ч. Плюс есть «хвосты» с очень низкими и очень высокими средними скоростями. Низкие скорости скорее всего соответствуют экстремальным соревнованиям типа Norseman, а высокие могут быть в случае с отмененным плаванием, где вместо спринта был суперспринт, или просто ошибочные данные. Еще один важный момент – плавная ступенька в районе 1200 по оси Х, и более высокие значения средней скорости после нее. Там же можно видеть значительно меньшую разницу между средними и максимальным скоростями, чем в первых двух третях графика. Судя по всему, это профессиональные соревнования. Чтобы выделить их более явно, вычислим отношение максимальной скорости к средней. На профессиональных соревнованиях, где нет случайных людей и у всех участников очень высокий уровень физической подготовки, это соотношение должно быть минимальным.

rs['vmdbva'] = rs['vmax']/rs['vavg'] #vmdbva - v max divided by v avg
rs = rs.sort_values(by='vmdbva')

Большой туториал по обработке спортивных данных на python - 14

На этом графике первая четверть выделяется очень четко: отношение максимальной скорости к средней небольшое, высокая средняя скорость, малое количество участников. Это профессиональные соревнования. Ступенька на зеленой кривой находится где-то в районе 1.2. Оставим в нашей выборке только записи со значением отношения больше 1.2.

rs = rs[rs['vmdbva'] > 1.2]

Так же уберем записи со нетипичными низкими и высокими скоростями. В статье What are the triathlon “world records” for each distance? опубликованы рекордные времена прохождения разных дистанций на 2019 год. Если пересчитать их на средние скорости, то можно увидеть, что она не может быть выше 33 км/ч даже у самых быстрых. Так что будем считать протоколы, где средние скорости получаются выше, невалидными и уберем их из рассмотрения.

rs = rs[(rs['vavg'] > 17)&(rs['vmax'] < 33)]

Вот, что осталось:
Большой туториал по обработке спортивных данных на python - 15
Большой туториал по обработке спортивных данных на python - 16

Теперь все выглядит достаточно однородно и не вызывает вопросов. В результате всего этого отбора мы потеряли 777 из 1922 протоколов, или 40%. При этом общее количество финишеров сократилось не так сильно – всего на 13%.

Итак, осталось 1 145 гонок с 1 231 772 финишерами. Эта выборка и стала материалом для моего анализа и визуализации.

Часть 5. Анализ и визуализация

В этой работе собственно анализ и визуализация были самыми простыми частями. Верхушкой айсберга, подводной частью которого была как раз подготовка данных. Анализ, по сути, представлял собой простые арифметические операции над pandas Series, вычисление средних, фильтрацию – все это делается элементарными средствами pandas и в приведенном выше коде полно примеров. Визуализация в свою очередь, в основном делалась с помощью наистандартнейшего matplotlib. Использовались plot, bar, pie. Кое-где, правда, пришлось повозиться с подписями осей, в случае дат и пиктограмм, но это не то, чтобы тянет на развернутое описание здесь. Единственное, про что, наверное, стоит рассказать, это представление геоданных. Как минимум, это не matplotlib.

Геоданные

По каждой гонке у нас есть информация о месте ее проведения. В самом начале с помощью geopy мы вычислили координаты для каждой локации. Многие гонки проводятся ежегодно в одном и том же месте.

event date country latitude longitude loc
0 Ironman Indian Wells La Quinta 70.3 2019 2019-12-08 USA 33.7238 -116.305 Indian Wells/La Quinta, California, USA
1 Ironman Taupo 70.3 2019 2019-12-07 NZL -41.5001 172.834 New Zealand
2 Ironman Western Australia 2019 2019-12-01 AUS -33.6445 115.349 Busselton, Western Australia
3 Ironman Mar del Plata 2019 2019-12-01 ARG -37.9977 -57.5483 Mar del Plata, Argentina
4 Ironman Cozumel 2019 2019-11-24 MEX 20.4318 -86.9203 Cozumel, Mexico
5 Ironman Arizona 2019 2019-11-24 USA 33.4255 -111.94 Tempe, Arizona, USA
6 Ironman Xiamen 70.3 2019 2019-11-10 CHN 24.4758 118.075 Xiamen, China
7 Ironman Turkey 70.3 2019 2019-11-03 TUR 36.8633 31.0578 Belek, Antalya, Turkey
8 Ironman Florida 2019 2019-11-02 USA 30.1766 -85.8055 Panama City Beach, Florida, USA
9 Ironman Marrakech 70.3 2019 2019-10-27 MAR 31.6258 -7.98916 Marrakech, Morocco
10 Ironman Waco 70.3 2019 2019-10-27 USA 31.5493 -97.1467 Waco, Texas, USA


Очень удобный инструмент для визуализации геоданных в python – это folium. Вот как он работает:

import folium

m = folium.Map() 
folium.Marker(['55.7522200', '37.6155600'], popup='Москва').add_to(m)

И получаем интерактивную карту прямо в юпитер ноутбуке.

Большой туториал по обработке спортивных данных на python - 17

Теперь, к нашим данным. Для начала заведем новую колонку из комбинации наших координат:

rs['coords'] = rs['latitude'].astype(str) + ', ' + rs['longitude'].astype(str)

Уникальных координат coords получается 291. А уникальных локаций loc – 324, значит какие-то названия немного различаются, при этом соответствуют одной и той же точке. Это не страшно, мы будем считать уникальность по coords. Подсчитаем сколько событий прошло за все время в каждой локации (с уникальными координатами):

vc = rs['coords'].value_counts()

vc

Out:
43.7009358, 7.2683912 22
43.5854823, 39.723109 20
29.03970805, -13.636291 16
47.3723941, 8.5423328 16
59.3110918, 24.420907 15
51.0834196, 10.4234469 15
54.7585694, 38.8818137 14
20.4317585, -86.9202745 13
52.3727598, 4.8936041 12
41.6132925, 2.6576102 12
... ...

Теперь создадим карту, и добавим на нее маркеры в виде кругов, радиус которых будет зависеть от количества проведенных на локации событий. К маркерам добавим всплывающие таблички с названием локации.

m = folium.Map(location=[25,10], zoom_start=2)

for c in rs['coords'].unique():
    row = [r[1] for r in rs.iterrows() if r[1]['coords'] == c][0]    
    folium.Circle([row['latitude'], row['longitude']], 
					popup=(row['location']+'n('+str(vc[c])+' races)'), 
					radius = 10000*int(vc[c]), 
					color='darkorange', 
					fill=True, 
					stroke=True, 
					weight=1).add_to(m)

Готово. Можно посмотреть результат:

Большой туториал по обработке спортивных данных на python - 18

Прогресс участников

На самом деле, помимо геданных работа над еще одним графиком тоже была нетривиальной. Это график прогресса участников, самый последний. Вот он:

Большой туториал по обработке спортивных данных на python - 19

Разберем его, заодно приведу код для отрисовки, как пример использования matplotlib:

fig = plt.figure()
fig.set_size_inches(10, 6)

ax = fig.add_axes([0,0,1,1])

b = ax.bar(exp,numrecs, color = 'navajowhite')

ax1 = ax.twinx()

for i in range(len(exp_samp)):
    ax1.plot(exp_samp[i], vproc_samp[i], '.')
	
p, = ax1.plot(exp, vpm, 'o-',markersize=8, linewidth=2, color='C0')

for i in range(len(exp)):
    if i < len(exp)-1 and (vpm[i] < vpm[i+1]):
        ax1.text(x = exp[i]+0.1, y = vpm[i]-0.2, s = '{0:3.1f}%'.format(vpm[i]),size=12)
    else:
        ax1.text(x = exp[i]+0.1, y = vpm[i]+0.1, s = '{0:3.1f}%'.format(vpm[i]),size=12)

ax.legend((b,p), ('Количество данных', 'Скорость'),loc='center right')
ax.set_xlabel('Соревновательный опыт в годах')
ax.set_ylabel('Записи')
ax1.set_ylabel('% от средней скорости на гонке')
ax.set_xticks(np.arange(1, 11, step=1))
ax.set_yticks(np.arange(0, 230000, step=25000))
ax1.set_ylim(97.5,103.5)
ax.yaxis.set_label_position("right")
ax.yaxis.tick_right()
ax1.yaxis.set_label_position("left")
ax1.yaxis.tick_left()      

plt.show()

Теперь о том, как вычислялись данные для него. Для начала нужно было выбрать имена участников, которые финишировали хотя бы в двух гонках, причем в разных календарных годах, и при этом не являются профессионалами.

Сперва для каждого протокола заполним новую колонку date, где будет указана дата гонки. Так же нам понадобится год из этой даты, сделаем колонку year. Так как мы собираемся анализировать скорость каждого атлета относительно средней скорости на гонке, сразу вычислим эту скорость в новой колонке vproc – скорость в процентах от средней.

for index, row in rs.iterrows():
    e = row['event']
    rd[e]['date'] = row['date']
    rd[e]['year'] = row['year']
    rd[e]['vproc'] = 100 * rd[e]['v'] / rd[e]['v'].mean()

Вот, как теперь выглядят протоколы:
'Чемпионат самарской области Sprint 2019'

place sex name country ... th v date year vproc
0 1 M Shalev Aleksey RUS ... 1.161944 22.161128 2019-09-14 2019 130.666668
1 2 M Nikolaev Artem RUS ... 1.228611 20.958625 2019-09-14 2019 123.576458
2 3 M Kuchierskiy Aleksandr RUS ... 1.255556 20.508850 2019-09-14 2019 120.924485
3 4 F Korchagina Mariya RUS ... 1.297222 19.850107 2019-09-14 2019 117.040401
4 5 M Solodov Ivan RUS ... 1.298056 19.837364 2019-09-14 2019 116.965263
5 6 M Bukin Sergey RUS ... 1.300278 19.803461 2019-09-14 2019 116.765365
6 7 M Lavrentev Dmitriy RUS ... 1.300278 19.803461 2019-09-14 2019 116.765365
7 8 M Dolgov Petr RUS ... 1.321667 19.482976 2019-09-14 2019 114.875719
8 9 M Bezruchenko Mikhailn RUS ... 1.345000 19.144981 2019-09-14 2019 112.882832
9 10 M Ryazantsev Dmitriy RUS ... 1.359444 18.941561 2019-09-14 2019 111.683423
10 11 M Ibragimov Ramil RUS ... 1.376389 18.708375 2019-09-14 2019 110.308511


Далее объединим все протоколы в один датафрейм.

ar = pd.concat(rd)

Для каждого участника оставим только одну запись в каждом календарном году:

ar1 = ar.drop_duplicates(subset = ['name','year'], keep='first')

Далее из всех уникальных имен этих записей найдем те, которые встречаются минимум дважды:

nvc = ar1['name'].value_counts()
names = list(nvc[nvc > 1].index)

таких 219 890. Удалим из этого списка имена про-атлетов:

pro_names = ar[ar['group'].isin(['MPRO','FPRO'])]['name'].unique()
names = list(set(names) - set(pro_names))

А также имена атлетов, начавших выступать до 2010 года. Для этого загрузим данные, которые были сохранены до того, как мы произвели выборку за последние 10 лет. Поместим их в объекты rsa (races summary all) и rda (race details all).

rdo = {} 

for e in rda:    
    if rsa[rsa['event'] == e]['year'].iloc[0] < 2010:
        rdo[e] = rda[e]

aro = pd.concat(rdo)
old_names = aro['name'].unique()
names = list(set(names) - set(old_names))

И напоследок найдем имена, которые встречаются более одного раза в один и тот же день. Таким образом минимизируем присутствие полных тезок в нашей выборке.

namesakes = ar[ar.duplicated(subset = ['name','date'], keep = False)]['name'].unique()
names = list(set(names) - set(namesakes))

Итак, осталось 198 075 имен. Из всего датасета выделяем только записи с найденными именами:

ars = ar[ar['name'].isin(names)] #ars – all recоrds selected

Теперь для каждой записи нужно определить какому году в карьере атлета она соответствует – первому, второму, третьему, или десятому. Делаем цикл по всем именам и вычисляем.

ars['exp'] = '' #exp – experience, counted in years of racing, starts from 1.

for n in names:    
    ind = ars[ars['name'] == n].index 
    yos = ars.loc[ind, 'year'].min() #yos – year of start
    ars.loc[ind, 'exp'] = ars.loc[ind, 'year'] - yos + 1 

Вот пример того, что получилось:

event place sex name country group th v date year vproc exp
633 M Golovin Sergey RUS M40-44 5.356111 21.097397 2014-08-31 2014 106.036879 1
302 M Golovin Sergey RUS M40-44 11.236389 20.113223 2015-08-30 2015 108.231254 2
522 M Golovin Sergey RUS M40-44 10.402778 21.724967 2016-07-17 2016 111.265107 3
25 M Golovin Sergey RUS M40-44 10.910833 20.713358 2017-09-23 2017 112.953644 4
23 M Golovin Sergey RUS M40-44 4.700000 24.042553 2017-06-03 2017 120.565211 4
42 M Golovin Sergey RUS M40-44 4.599167 24.569668 2018-06-17 2018 124.579862 5
90 M Golovin Sergey NOR 14.069167 16.063496 2018-08-04 2018 100.001834 5
86 M Golovin Sergey RUS M45-49 9.820556 23.012955 2019-08-03 2019 118.375766 6


Судя по всему, тезки все-таки остались. Это ожидаемо, но не страшно, так как мы будем все усреднять, а их должно быть не так много. Далее строим массивы для графика:

exp = [] 
vpm = [] #vpm – v proc mean
numrecs = [] #number of records

for x in range(ars['exp'].min(), ars['exp'].max() + 1): 
    exp.append(x)
    vpm.append(ars[ars['exp'] == x]['vproc'].mean())
    numrecs.append(len(ars[ars['exp'] == x]))

Все, основа есть:

Большой туториал по обработке спортивных данных на python - 20

Теперь чтобы украсить его точками, соответсвующими конкретным результатам, выберем 1000 случайных имен и построим массивы с результатами для них.

names_samp = random.sample(names,1000)
ars_samp = ars[ars['name'].isin(names_samp)]

ars_samp = ars_samp.reset_index(drop = True)
exp_samp = []
vproc_samp = []

for n in names_samp:
    nr = ars_samp[ars_samp['name'] == n]
    nr = nr.sort_values('exp')
    exp_samp.append(list(nr['exp']))
    vproc_samp.append(list(nr['vproc']))

Добавляем цикл для построения графиков из этой случайной выборки.

for i in range(len(exp_samp)):
    ax1.plot(exp_samp[i], vproc_samp[i], '.')

Теперь все готово:

Большой туториал по обработке спортивных данных на python - 21

В целом, несложно. Но есть одна проблема. Для вычисления опыта exp в цикле всем именам, которых почти 200 тысяч, требуется восемь часов. Приходилось отлаживать алгоритм на небольших выборках, а потом запускать расчет на ночь. В принципе один раз так можно сделать, но, если обнаруживается какая-то ошибка или, что-то хочется поменять, и нужно пересчитать заново, это начинает напрягать. И вот, когда я уже вечером собирался опубликовать отчет, выяснилось, что снова надо пересчитать все заново. Ждать до утра не входило в мои планы, и я стал искать способ сделать расчет быстрее. Решил распараллелить.

Нашел где-то способ сделать это с помощью multiprocessing. Для того чтобы работало на Windows, нужны было основную логику каждой параллельной задачи поместить в отдельный workers.py файл:

import pickle as pkl

def worker(args):
    names = args[0]
    ars=args[1]
    num=args[2]
    ars = ars.sort_values(by='name')
    ars = ars.reset_index(drop=True)  

    for n in names:   
        ind = ars[ars['name'] == n].index 
        yos = ars.loc[ind, 'year'].min()
        ars.loc[ind, 'exp'] = ars.loc[ind, 'year'] - yos + 1                      

    with open(r'D:triparprog' + str(num) + '.pkl', 'wb') as f:
        pkl.dump(ars,f)

В процедуру передается порция имен names, часть датафрейма ar только с этими именами, и порядковый номер параллельной задачи — num. Вычисления записываются в датафрейм и, в конце, датафрейм записывается в файл. В ноутбуке, который вызывает это worker, соответственно, подготавливаем аргументы:

num_proc = 8 #number of processors
args = []

for i in range(num_proc):
    step = int(len(names_samp)/num_proc) + 1
    names_i = names_samp[i*step:min((i+1)*step, len(names_samp))]
    ars_i = ars[ars['name'].isin(names_i)]
    args.append([names_i, ars_i, i])

Запускаем параллельные вычисления:

from multiprocessing import Pool
import workers

if __name__ ==  '__main__':     
    p=Pool(processes = num_proc)
    p.map(workers.worker,args)

И по окончании считываем результаты из файлов и собираем кусочки обратно в целый датафрейм:

ars=pd.DataFrame(columns = ars.columns)

for i in range(num_proc):
    with open(r'D:triparprog'+str(i)+'.pkl', 'rb') as f:    
        arsi = pkl.load(f)
        print(len(arsi))
        ars = pd.concat([ars, arsi])

Таким образом удалось получить ускорение в 40 раз, и вместо 8 часов завершить расчет за 11 минут и опубликовать отчет тем же вечером. Заодно узнал, как распараллеливать на python, думаю еще пригодится. Здесь ускорение оказалось еще больше, чем просто в 8 раз по количеству ядер, за счет того, что в каждой задаче использовался маленький датафрейм, по которому поиск быстрее. В принципе таким образом можно было ускорить и последовательные вычисления, но вопрос — как до этого догадаться?

Однако, я не мог успокоиться и даже после публикации постоянно думал о том, как сделать расчет, используя векторизацию, то есть операции над целым колонками датафрейма pandas Series. Такие вычисления на порядок быстрее любых распараллеленных циклов, хоть на суперкластере. И придумал. Оказывается, чтобы для каждого имени найти год начала карьеры, нужно наоборот – для каждого года найти участников, которые в нем начинали. Для этого нужно сначала определить все имена для первого года из нашей выборки, это 2010. Соответственно все записи с этими именами обрабатываем, применяя этот год. Далее берем следующий год – 2011.

Опять находим все имена с записями в этом году, но берем из них только необработанные, то есть те, которые не встречались в 2010 и обрабатываем, их применяя 2011 год. И так далее все остальные года. Тоже цикл, но уже не двести тысяч итераций, а всего девять.

for y in range(ars['year'].min(),ars['year'].max()):
    arsynp = ars[(ars['exp'] == '') & (ars['year'] == y)] #arsynp - all records selected for year not processed
    namesy = arsynp['name'].unique()
    ind = ars[ars['name'].isin(namesy)].index
    ars.loc[ind, 'exp'] = ars.loc[ind,'year'] - y + 1

Этот цикл отрабатывает буквально за пару секунд. Да и код получился гораздо лаконичнее.

Заключение

Ну вот, наконец-то, большая работа завершена. Для меня это был, по сути, первый проект такого рода. Когда я брался за него, основной целью было поупражняться в использовании питона и его библиотек. Эта задача выполнена с лихвой. Да и сами результаты получились вполне презентабельные. Какие выводы я сделал для себя по завершении?

Первое: Данные неидеальны. Это, наверное, справедливо практически для любой задачи по анализу. Даже если они вполне структурированы, а ведь часто бывает и по-другому, нужно быть готовым повозиться с ними, перед тем как приступить вычислению характеристик и к поиску трендов – найти ошибки, выбросы, отклонения от стандартов и т. д.

Второе: Любая задача имеет решение. Это больше похоже на лозунг, но часто так и есть. Просто это решение может быть не таким очевидным и кроется не в самих данных, а так сказать, outside of the box. Как пример – обработка имен участников, описанная выше, или скраппинг сайта.

Третье: Знание предметной области исключительно важно. Это позволит более качественно подготовить данные, убрав заведомо невалидные или нестандартные, избежать ошибок при интерпретации, задействовать информацию, отсутствующую в данных, например, дистанции в этом проекте, представить результаты в виде, принятом в сообществе, при этом избежать глупых, неверных выводов.

Четвертое: Для работы в python существует богатейший набор инструментов. Иногда кажется, что стоит о чем-то подумать, начинаешь искать – оно уже есть. Это просто здорово! Огромная благодарность создателям за этот вклад, в особенности за инструменты, которые пригодились мне здесь: это selenium для скраппинга, pycountry для определения кода страны по стандарту ISO, country codes (datahub) – для олимпийских кодов, geopy – для определения координат по адресу, folium – для визуализации геоданных, gender-guesser – для анализа имен, multiprocessing – для параллельных вычислений, matplotlib, numpy, и конечно pandas – без него вообще никуда.

Пятое: Векторизация – наше все. Крайне важно уметь пользоваться встроенными средствами pandas, это очень эффективно. Полагаю, в большинстве случаев, когда количество записей измеряется начиная с десятков тысяч, этот навык становится просто необходимым.

Шестое: обрабатывать данные вручную – плохая идея. Нужно стараться минимизировать любое ручное вмешательство – во-первых, это не масштабируется, то есть при увеличении количества данных в несколько раз время на обработку вручную увеличится до неприемлемых значений, во-вторых, будет плохая повторяемость – что-то забудешь, где-то ошибешься. Все только программно, если, что-то при этом выпадает из общего стандарта для программного решения, ну ничего страшного, можно пожертвовать какой-то частью данных, плюсов все равно будет больше.

Седьмое: Код нужно содержать в рабочем состоянии. Казалось бы, что может быть очевиднее! На самом деле, если речь идет о коде для собственного использования, целью которого является публикация результатов работы этого кода, тут все не так строго. Я работал в Юпитер Ноутбуках, а это среда, на мой взгляд, как раз не располагает к созданию цельных программных продуктов. Она настроена на построчный, покусочный запуск, в этом есть свои плюсы – это быстро: разработка, отладка и исполнение одновременно. Но часто слишком велик соблазн просто отредактировать какую-нибудь строку и быстро получить новый результат, вместо того чтобы сделать дубликат или обернуть в def. Конечно, такого соблазна нужно избегать. К хорошему коду, даже “для себя”, надо стремиться, как минимум потому, что даже для одной работы по анализу запуск делается множество раз, и вложения времени вначале, обязательно окупятся в дальнейшем. А еще можно добавлять тесты, даже в ноутбуках, в виде проверок критических параметров и выбрасывания исключений – очень полезно.

Восьмое: Сохраняться чаще. На каждом шаге я сохранял новую версию файла. Всего их получилось порядка 10. Это удобно, так как при обнаружении ошибки помогает быстрее определить на каком этапе она возникла. Плюс я сохранял исходные данные в колонках с пометкой raw — это позволяет очень быстро проверять результат и видеть расхождение.

Девятое: Нужно соизмерять вложения времени и результат. Местами я очень долго возился над восстановлением данных, которые образуют доли процента от общего количества. По сути, в этом не было никакого смысла, нужно было просто выкинуть их, и все. И я бы так и сделал, будь это коммерческий проект, а не аутотренинг. Это позволило бы получить результат гораздо быстрее. Здесь работает принцип Парето – 80% результата достигается за 20% времени.

И последнее: работа над подобными проектами очень здорово расширяет кругозор. Волей-неволей узнаешь, что-то новое – например, названия диковинных стран — типа островов Питкэрн, то, что по стандарту ISO код Швейцарии – CHE, от латинского “Confoederatio Helvetica”, что такое испанское имя, ну и собственно о самом триатлоне – рекорды, их обладатели, места гонок, история событий и так далее.

Пожалуй, достаточно. На этом все. Спасибо всем, кто дочитал до конца!

Автор: Олег Ковалевский

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js