<jupyter_start><jupyter_text>Audax India RandonneursAudax India Randonneurs (AIR) is the all-India organization of randonneurs, which is recognized by Audax Club Parisien (ACP) for conducting and overseeing all Brevets de Randonneurs Mondiaux (BRMs) and Audax events in India.BRM stands for Brevets de Randonneurs Mondiaux (BRMs). Randonneuring is long-distance Endurance Cycling with rides of 200, 300, 400, 600 and 1000 kms.  Business understandingWe will answer follwing questions1. Which club is most active in terms of number of BRM events conducted and number of participats ?2. How many different category of events conducted accross India by AIR ?2. How many riders complete ride on time based on different category ?<jupyter_code>import pandas as pd
import numpy as np
import seaborn as sns
import mysql.connector

sql_connection = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="<PASSWORD>",
    database="air"
)<jupyter_output><empty_output><jupyter_text>Data understandingWe have scraped the data from AIR website (https://audaxindia.org) and dumped in SQL DatabaseWe have 4 tables Club, Event, Cyclist and Record 1. Club --> It contains information about clubs2. Event --> It contains information about events3. Cyclist --> It contains profile of cyclist4. Record --> It contains infromation about ride of all rides event wise Data preparationData is download and saved in database using `fetch_data` program.This data set is clean and do not contains null values.Record table contains a column time_mins which shows how much time it took to complete the ride. It can have values1. DNF --> Do not finish2. LF --> Late Finish3. DNS --> Did not startWe will clean and remove these values when required<jupyter_code>clubs = pd.read_sql_query("SELECT * FROM Club", sql_connection)
clubs = clubs.set_index('clubID')

events = pd.read_sql_query("SELECT * FROM Event", sql_connection)
events = events.set_index('eventID')

cyclists = pd.read_sql_query("SELECT * FROM Cyclist", sql_connection)
cyclists = cyclists.set_index('air')

records = pd.read_sql_query("SELECT * FROM Record", sql_connection)
records = records.set_index('recordID')
clubs.head()
events.head()
cyclists.head()
records.head()<jupyter_output><empty_output><jupyter_text>Data modeling<jupyter_code>events_by_club = events.join(clubs, on="clubID")
events_by_club.head()<jupyter_output><empty_output><jupyter_text>How many events were conducted based on distance<jupyter_code>records_event = records.join(events, on="eventID")
records_event.head()
sns.catplot(y="distance",
            data= records_event,
            order=["200", 
                   "300", 
                   "400", 
                   "600", 
                   "1000", 
                   "1200", 
                   "Fleche"],
            kind="count")<jupyter_output><empty_output><jupyter_text>How many events were conducted groupped by clubs<jupyter_code>sns.catplot(y="name",
            data=events_by_club.sort_values(by='name'),
            kind="count", 
            height = 10)<jupyter_output><empty_output><jupyter_text>Top 5 Clubs<jupyter_code>events_by_club.groupby('name').count().sort_values('clubID', ascending=False)[:5]<jupyter_output><empty_output><jupyter_text>Participartion by Club<jupyter_code>records_event_club = records_event.join(clubs, on="clubID")
records_event_club.head()
records_event_club.groupby('name').count().sort_values('air', ascending=False)[:5]
sns.catplot(y = 'name',
            data = records_event_club,
            kind = 'count',
            height= 10)<jupyter_output><empty_output><jupyter_text>How many percents of cyclist finish on time in each category<jupyter_code>distances = ['200', '300', '400', '600', '1000', '1200']
percent_finisher = []
for distance in distances:
    record_event_distance = records_event[records_event['distance'] == distance]
    dnf = (record_event_distance['time_mins'] == 'DNF').sum()
    lf = (record_event_distance['time_mins'] == 'LF').sum()
    total = len(record_event_distance)
    percent_finisher.append((total - dnf + lf) / total * 100)
percent_finisher_df = pd.DataFrame({'distance': distances, 'percent finshed': percent_finisher})
percent_finisher_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1049658743233702, 'fraction_numerical': 0.018121911037891267, 'mean_word_length': 4.666666666666667, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 41, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '447138', 'license': 'Apache-2.0', 'n_tokens_mistral': 1353, 'n_tokens_neox': 1268, 'n_words': 366, 'path': 'Audax India Randonneurs.ipynb', 'repo_name': 'debugger24/AIR', 'size': '4247'}