{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Package for WRC Results Scrape\n", "\n", "This notebook contains most of the ingredients for a package that can be used to scrape results data from current and archived WRC Live Timing pages.\n", "\n", "At the moment, the script will only happily downloaded the complete set of results for a particular rally. The code needs updating to allow:\n", "\n", "- loading data for a completed stage into a database that already contains results for other completed stages;\n", "- incremental loads of data to upsert data into the database from an ongoing stage;" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#https://www.wrc.com/service/sasCacheApi.php?route=rallies%2F40%2Fitinerary" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "YEAR = 2019" ] }, { "cell_type": "code", "execution_count": 80, "metadata": {}, "outputs": [], "source": [ "url_root='http://www.wrc.com/service/sasCacheApi.php?route={stub}'\n", "\n", "#What's the events/{ID}/{stub} ID number?\n", "#url_base='http://www.wrc.com/service/sasCacheApi.php?route=events/79/{stub}'\n", "url_base='http://www.wrc.com/service/sasCacheApi.php?route=events/{SASEVENTID}/{{stub}}'\n", "#we need to grab SASEVENTID from getEventMetadata()" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "#Call a resource by ID\n", "wrcapi='https://webappsdata.wrc.com/srv/wrc/json/api/wrcsrv/byId?id=%22{}%22' #requires resource ID" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "stubs = { 'itinerary': 'rallies/{rallyId}/itinerary',\n", " 'startlists': 'rallies/{rallyId}/entries',\n", " 'penalties': 'rallies/{rallyId}/penalties',\n", " 'retirements': 'rallies/{rallyId}/retirements',\n", " 'stagewinners':'rallies/{rallyId}/stagewinners',\n", " 'overall':'stages/{stageId}/results?rallyId={rallyId}',\n", " 'split_times':'stages/{stageId}/splittimes?rallyId={rallyId}',\n", " 'stage_times_stage':'stages/{stageId}/stagetimes?rallyId={rallyId}',\n", " 'stage_times_overall':'stages/{stageId}/results?rallyId={rallyId}',\n", " 'seasons':'seasons',\n", " 'seasonDetails':'seasons/{seasonId}',\n", " # TO DO - for 2019, the following may need to be prefixed with seasons/ ?\n", " 'championship':'seasons/4/championships/{championshipId}',\n", " 'championship_results':'seasons/4/championships/{championshipId}/results',\n", " }" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "#pip3 install sqlite-utils\n", "\n", "import requests\n", "import re\n", "import json\n", "from bs4 import BeautifulSoup \n", "\n", "import sqlite3\n", "from sqlite_utils import Database\n", "\n", "import pandas as pd\n", "from pandas.io.json import json_normalize\n", "\n", "#!pip3 install isodate\n", "import isodate" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Database Schema\n", "\n", "The following tables are literal mappings from flattened JSON datafiles published by the WRC.\n", "\n", "The data model (primary and foreign key relationships) is derived by observation." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "#SQL in wrcResults.sql\n", "setup_q='''\n", "CREATE TABLE \"itinerary_event\" (\n", " \"eventId\" INTEGER,\n", " \"itineraryId\" INTEGER PRIMARY KEY,\n", " \"name\" TEXT,\n", " \"priority\" INTEGER\n", ");\n", "CREATE TABLE \"itinerary_legs\" (\n", " \"itineraryId\" INTEGER,\n", " \"itineraryLegId\" INTEGER PRIMARY KEY,\n", " \"legDate\" TEXT,\n", " \"name\" TEXT,\n", " \"order\" INTEGER,\n", " \"startListId\" INTEGER,\n", " \"status\" TEXT,\n", " FOREIGN KEY (\"itineraryId\") REFERENCES \"itinerary_event\" (\"itineraryId\")\n", ");\n", "CREATE TABLE \"itinerary_sections\" (\n", " \"itineraryLegId\" INTEGER,\n", " \"itinerarySectionId\" INTEGER PRIMARY KEY,\n", " \"name\" TEXT,\n", " \"order\" INTEGER,\n", " FOREIGN KEY (\"itineraryLegId\") REFERENCES \"itinerary_legs\" (\"itineraryLegId\")\n", ");\n", "CREATE TABLE \"itinerary_stages\" (\n", " \"code\" TEXT,\n", " \"distance\" REAL,\n", " \"eventId\" INTEGER,\n", " \"name\" TEXT,\n", " \"number\" INTEGER,\n", " \"stageId\" INTEGER PRIMARY KEY,\n", " \"stageType\" TEXT,\n", " \"status\" TEXT,\n", " \"timingPrecision\" TEXT,\n", " \"itineraryLegId\" INTEGER,\n", " \"itinerarySections.itinerarySectionId\" INTEGER,\n", " FOREIGN KEY (\"itineraryLegId\") REFERENCES \"itinerary_legs\" (\"itineraryLegId\")\n", ");\n", "CREATE TABLE \"itinerary_controls\" (\n", " \"code\" TEXT,\n", " \"controlId\" INTEGER PRIMARY KEY,\n", " \"controlPenalties\" TEXT,\n", " \"distance\" REAL,\n", " \"eventId\" INTEGER,\n", " \"firstCarDueDateTime\" TEXT,\n", " \"firstCarDueDateTimeLocal\" TEXT,\n", " \"location\" TEXT,\n", " \"stageId\" INTEGER,\n", " \"status\" TEXT,\n", " \"targetDuration\" TEXT,\n", " \"targetDurationMs\" INTEGER,\n", " \"timingPrecision\" TEXT,\n", " \"type\" TEXT,\n", " \"itineraryLegId\" INTEGER,\n", " \"itinerarySections.itinerarySectionId\" INTEGER,\n", " \"roundingPolicy\" TEXT,\n", " FOREIGN KEY (\"itineraryLegId\") REFERENCES \"itinerary_legs\" (\"itineraryLegId\")\n", ");\n", "CREATE TABLE \"startlists\" (\n", " \"codriver.abbvName\" TEXT,\n", " \"codriver.code\" TEXT,\n", " \"codriver.country.countryId\" INTEGER,\n", " \"codriver.country.iso2\" TEXT,\n", " \"codriver.country.iso3\" TEXT,\n", " \"codriver.country.name\" TEXT,\n", " \"codriver.countryId\" INTEGER,\n", " \"codriver.firstName\" TEXT,\n", " \"codriver.fullName\" TEXT,\n", " \"codriver.lastName\" TEXT,\n", " \"codriver.personId\" INTEGER,\n", " \"codriverId\" INTEGER,\n", " \"driver.abbvName\" TEXT,\n", " \"driver.code\" TEXT,\n", " \"driver.country.countryId\" INTEGER,\n", " \"driver.country.iso2\" TEXT,\n", " \"driver.country.iso3\" TEXT,\n", " \"driver.country.name\" TEXT,\n", " \"driver.countryId\" INTEGER,\n", " \"driver.firstName\" TEXT,\n", " \"driver.fullName\" TEXT,\n", " \"driver.lastName\" TEXT,\n", " \"driver.personId\" INTEGER,\n", " \"driverId\" INTEGER,\n", " \"eligibility\" TEXT,\n", " \"entrant.entrantId\" INTEGER,\n", " \"entrant.logoFilename\" TEXT,\n", " \"entrant.name\" TEXT,\n", " \"entrantId\" INTEGER,\n", " \"entryId\" INTEGER PRIMARY KEY,\n", " \"eventId\" INTEGER,\n", " \"group.name\" TEXT,\n", " \"groupId\" INTEGER,\n", " \"group.groupId\" INTEGER,\n", " \"identifier\" TEXT,\n", " \"manufacturer.logoFilename\" TEXT,\n", " \"manufacturer.manufacturerId\" INTEGER,\n", " \"manufacturer.name\" TEXT,\n", " \"manufacturerId\" INTEGER,\n", " \"priority\" TEXT,\n", " \"status\" TEXT,\n", " \"tag\" TEXT,\n", " \"tag.name\" TEXT,\n", " \"tag.tagId\" INTEGER,\n", " \"tagId\" INTEGER,\n", " \"tyreManufacturer\" TEXT,\n", " \"vehicleModel\" TEXT,\n", " \"entryListOrder\" INTEGER,\n", " FOREIGN KEY (\"eventId\") REFERENCES \"itinerary_event\" (\"eventId\")\n", ");\n", "CREATE TABLE \"roster\" (\n", " \"fiasn\" INTEGER,\n", " \"code\" TEXT,\n", " \"sas-entryid\" INTEGER PRIMARY KEY,\n", " \"roster_num\" INTEGER,\n", " FOREIGN KEY (\"sas-entryid\") REFERENCES \"startlists\" (\"entryId\")\n", ");\n", "CREATE TABLE \"startlist_classes\" (\n", " \"eventClassId\" INTEGER,\n", " \"eventId\" INTEGER,\n", " \"name\" TEXT,\n", " \"entryId\" INTEGER,\n", " PRIMARY KEY (\"eventClassId\",\"entryId\"),\n", " FOREIGN KEY (\"eventId\") REFERENCES \"itinerary_event\" (\"eventId\"),\n", " FOREIGN KEY (\"entryId\") REFERENCES \"startlists\" (\"entryId\")\n", ");\n", "CREATE TABLE \"penalties\" (\n", " \"controlId\" INTEGER,\n", " \"entryId\" INTEGER,\n", " \"penaltyDuration\" TEXT,\n", " \"penaltyDurationMs\" INTEGER,\n", " \"penaltyId\" INTEGER PRIMARY KEY,\n", " \"reason\" TEXT,\n", " FOREIGN KEY (\"entryId\") REFERENCES \"startlists\" (\"entryId\")\n", ");\n", "CREATE TABLE \"retirements\" (\n", " \"controlId\" INTEGER,\n", " \"entryId\" INTEGER,\n", " \"reason\" TEXT,\n", " \"retirementDateTime\" TEXT,\n", " \"retirementDateTimeLocal\" TEXT,\n", " \"retirementId\" INTEGER PRIMARY KEY,\n", " \"status\" TEXT,\n", " FOREIGN KEY (\"entryId\") REFERENCES \"startlists\" (\"entryId\")\n", ");\n", "CREATE TABLE \"stagewinners\" (\n", " \"elapsedDuration\" TEXT,\n", " \"elapsedDurationMs\" INTEGER,\n", " \"entryId\" INTEGER,\n", " \"stageId\" INTEGER,\n", " \"stageName\" TEXT,\n", " PRIMARY KEY (\"stageId\"),\n", " FOREIGN KEY (\"entryId\") REFERENCES \"startlists\" (\"entryId\"),\n", " FOREIGN KEY (\"stageId\") REFERENCES \"itinerary_stages\" (\"stageId\")\n", ");\n", "CREATE TABLE \"stage_overall\" (\n", " \"diffFirst\" TEXT,\n", " \"diffFirstMs\" INTEGER,\n", " \"diffPrev\" TEXT,\n", " \"diffPrevMs\" INTEGER,\n", " \"entryId\" INTEGER,\n", " \"penaltyTime\" TEXT,\n", " \"penaltyTimeMs\" INTEGER,\n", " \"position\" INTEGER,\n", " \"stageTime\" TEXT,\n", " \"stageTimeMs\" INTEGER,\n", " \"totalTime\" TEXT,\n", " \"totalTimeMs\" INTEGER,\n", " \"stageId\" INTEGER,\n", " PRIMARY KEY (\"stageId\",\"entryId\"),\n", " FOREIGN KEY (\"stageId\") REFERENCES \"itinerary_stages\" (\"stageId\"),\n", " FOREIGN KEY (\"entryId\") REFERENCES \"startlists\" (\"entryId\")\n", ");\n", "CREATE TABLE \"split_times\" (\n", " \"elapsedDuration\" TEXT,\n", " \"elapsedDurationMs\" INTEGER,\n", " \"entryId\" INTEGER,\n", " \"splitDateTime\" TEXT,\n", " \"splitDateTimeLocal\" TEXT,\n", " \"splitPointId\" INTEGER,\n", " \"splitPointTimeId\" INTEGER PRIMARY KEY,\n", " \"stageTimeDuration\" TEXT,\n", " \"stageTimeDurationMs\" REAL,\n", " \"startDateTime\" TEXT,\n", " \"startDateTimeLocal\" TEXT,\n", " \"stageId\" INTEGER,\n", " FOREIGN KEY (\"stageId\") REFERENCES \"itinerary_stages\" (\"stageId\"),\n", " FOREIGN KEY (\"entryId\") REFERENCES \"startlists\" (\"entryId\")\n", ");\n", "CREATE TABLE \"stage_times_stage\" (\n", " \"diffFirst\" TEXT,\n", " \"diffFirstMs\" INTEGER,\n", " \"diffPrev\" TEXT,\n", " \"diffPrevMs\" INTEGER,\n", " \"elapsedDuration\" TEXT,\n", " \"elapsedDurationMs\" INTEGER,\n", " \"entryId\" INTEGER,\n", " \"position\" INTEGER,\n", " \"source\" TEXT,\n", " \"stageId\" INTEGER,\n", " \"stageTimeId\" INTEGER PRIMARY KEY,\n", " \"status\" TEXT,\n", " FOREIGN KEY (\"stageId\") REFERENCES \"itinerary_stages\" (\"stageId\"),\n", " FOREIGN KEY (\"entryId\") REFERENCES \"startlists\" (\"entryId\")\n", ");\n", "CREATE TABLE \"stage_times_overall\" (\n", " \"diffFirst\" TEXT,\n", " \"diffFirstMs\" INTEGER,\n", " \"diffPrev\" TEXT,\n", " \"diffPrevMs\" INTEGER,\n", " \"entryId\" INTEGER,\n", " \"penaltyTime\" TEXT,\n", " \"penaltyTimeMs\" INTEGER,\n", " \"position\" INTEGER,\n", " \"stageTime\" TEXT,\n", " \"stageTimeMs\" INTEGER,\n", " \"totalTime\" TEXT,\n", " \"totalTimeMs\" INTEGER,\n", " \"stageId\" INTEGER,\n", " PRIMARY KEY (\"stageId\",\"entryId\"),\n", " FOREIGN KEY (\"stageId\") REFERENCES \"itinerary_stages\" (\"stageId\"),\n", " FOREIGN KEY (\"entryId\") REFERENCES \"startlists\" (\"entryId\")\n", ");\n", "CREATE TABLE \"championship_lookup\" (\n", " \"championshipId\" INTEGER PRIMARY KEY,\n", " \"fieldFiveDescription\" TEXT,\n", " \"fieldFourDescription\" TEXT,\n", " \"fieldOneDescription\" TEXT,\n", " \"fieldThreeDescription\" TEXT,\n", " \"fieldTwoDescription\" TEXT,\n", " \"name\" TEXT,\n", " \"seasonId\" INTEGER,\n", " \"type\" TEXT,\n", " \"_codeClass\" TEXT,\n", " \"_codeTyp\" TEXT\n", ");\n", "CREATE TABLE \"championship_results\" (\n", " \"championshipEntryId\" INTEGER,\n", " \"championshipId\" INTEGER,\n", " \"dropped\" INTEGER,\n", " \"eventId\" INTEGER,\n", " \"pointsBreakdown\" TEXT,\n", " \"position\" INTEGER,\n", " \"publishedStatus\" TEXT,\n", " \"status\" TEXT,\n", " \"totalPoints\" INTEGER,\n", " PRIMARY KEY (\"championshipEntryId\",\"eventId\"),\n", " FOREIGN KEY (\"championshipId\") REFERENCES \"championship_lookup\" (\"championshipId\"),\n", " FOREIGN KEY (\"eventId\") REFERENCES \"itinerary_event\" (\"eventId\")\n", ");\n", "CREATE TABLE \"championship_entries_codrivers\" (\n", " \"championshipEntryId\" INTEGER PRIMARY KEY,\n", " \"championshipId\" INTEGER,\n", " \"entrantId\" TEXT,\n", " \"ManufacturerTyre\" TEXT,\n", " \"Manufacturer\" TEXT,\n", " \"FirstName\" TEXT,\n", " \"CountryISO3\" TEXT,\n", " \"CountryISO2\" TEXT,\n", " \"LastName\" TEXT,\n", " \"manufacturerId\" INTEGER,\n", " \"personId\" INTEGER,\n", " \"tyreManufacturer\" TEXT,\n", " FOREIGN KEY (\"championshipId\") REFERENCES \"championship_lookup\" (\"championshipId\")\n", ");\n", "CREATE TABLE \"championship_entries_manufacturers\" (\n", " \"championshipEntryId\" INTEGER PRIMARY KEY ,\n", " \"championshipId\" INTEGER,\n", " \"entrantId\" INTEGER,\n", " \"Name\" TEXT,\n", " \"LogoFileName\" TEXT,\n", " \"Manufacturer\" TEXT,\n", " \"manufacturerId\" INTEGER,\n", " \"personId\" TEXT,\n", " \"tyreManufacturer\" TEXT,\n", " FOREIGN KEY (\"championshipId\") REFERENCES \"championship_lookup\" (\"championshipId\")\n", ");\n", "CREATE TABLE \"championship_rounds\" (\n", " \"championshipId\" INTEGER,\n", " \"eventId\" INTEGER,\n", " \"order\" INTEGER,\n", " PRIMARY KEY (\"championshipId\",\"eventId\"),\n", " FOREIGN KEY (\"championshipId\") REFERENCES \"championship_lookup\" (\"championshipId\"),\n", " FOREIGN KEY (\"eventId\") REFERENCES \"itinerary_event\" (\"eventId\")\n", ");\n", "CREATE TABLE \"championship_events\" (\n", " \"categories\" TEXT,\n", " \"clerkOfTheCourse\" TEXT,\n", " \"country.countryId\" INTEGER,\n", " \"country.iso2\" TEXT,\n", " \"country.iso3\" TEXT,\n", " \"country.name\" TEXT,\n", " \"countryId\" INTEGER,\n", " \"eventId\" INTEGER PRIMARY KEY,\n", " \"finishDate\" TEXT,\n", " \"location\" TEXT,\n", " \"mode\" TEXT,\n", " \"name\" TEXT,\n", " \"organiserUrl\" TEXT,\n", " \"slug\" TEXT,\n", " \"startDate\" TEXT,\n", " \"stewards\" TEXT,\n", " \"surfaces\" TEXT,\n", " \"templateFilename\" TEXT,\n", " \"timeZoneId\" TEXT,\n", " \"timeZoneName\" TEXT,\n", " \"timeZoneOffset\" INTEGER,\n", " \"trackingEventId\" INTEGER ,\n", " FOREIGN KEY (\"eventId\") REFERENCES \"itinerary_event\" (\"eventId\")\n", ");\n", "CREATE TABLE \"championship_entries_drivers\" (\n", " \"championshipEntryId\" INTEGER PRIMARY KEY ,\n", " \"championshipId\" INTEGER,\n", " \"entrantId\" TEXT,\n", " \"ManufacturerTyre\" TEXT,\n", " \"Manufacturer\" TEXT,\n", " \"FirstName\" TEXT,\n", " \"CountryISO3\" TEXT,\n", " \"CountryISO2\" TEXT,\n", " \"LastName\" TEXT,\n", " \"manufacturerId\" INTEGER,\n", " \"personId\" INTEGER,\n", " \"tyreManufacturer\" TEXT,\n", " FOREIGN KEY (\"championshipId\") REFERENCES \"championship_lookup\" (\"championshipId\")\n", ");\n", "CREATE TABLE \"event_metadata\" (\n", " \"_id\" TEXT,\n", " \"availability\" TEXT,\n", " \"date-finish\" TEXT,\n", " \"date-start\" TEXT,\n", " \"gallery\" TEXT,\n", " \"hasdata\" TEXT,\n", " \"hasfootage\" TEXT,\n", " \"hasvideos\" TEXT,\n", " \"id\" TEXT,\n", " \"info-based\" TEXT,\n", " \"info-categories\" TEXT,\n", " \"info-date\" TEXT,\n", " \"info-flag\" TEXT,\n", " \"info-surface\" TEXT,\n", " \"info-website\" TEXT,\n", " \"kmlfile\" TEXT,\n", " \"logo\" TEXT,\n", " \"name\" TEXT,\n", " \"org-website\" TEXT,\n", " \"poi-Klo im Wald\" TEXT,\n", " \"poilistid\" TEXT,\n", " \"position\" TEXT,\n", " \"rosterid\" TEXT,\n", " \"sas-eventid\" TEXT,\n", " \"sas-itineraryid\" TEXT,\n", " \"sas-rallyid\" TEXT,\n", " \"sas-trackingid\" TEXT,\n", " \"sitid\" TEXT,\n", " \"testid\" TEXT,\n", " \"thumbnail\" TEXT,\n", " \"time-zone\" TEXT,\n", " \"tzoffset\" TEXT,\n", " \"year\" INTEGER\n", ");\n", "\n", "\n", "'''\n", "\n", "\n", "#conn = sqlite3.connect('wrc18_test1keys.db')\n", "#c = conn.cursor()\n", "#c.executescript(setup_q)" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "#Maybe bring in additional tables \n", "#eg via https://blog.ouseful.info/2016/11/14/what-nationality-did-you-say-you-were-again/\n", "#bring in a table to give nationalites from country codes\n", "#Or maybe make that a pip package?" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "setup_views_q = '''\n", "'''" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "#meta={'rallyId':None, 'stages':[], 'championshipId':None }" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "#This is a literal approach and is DEPRECATED\n", "\n", "def getRally_URLs(results_main_url=None):\n", " if results_main_url is None:\n", " results_main_url='http://www.wrc.com/en/wrc/results/wales/stage-times/page/416-238---.html#'\n", "\n", " html=requests.get(results_main_url)\n", " soup=BeautifulSoup(html.content, \"html5lib\")\n", " #BeautifulSoup has a routine - find_all() - that will find all the HTML tags of a particular sort\n", " #Links are represented in HTML pages in the form link text\n", " #Grab all the (anchor) tags...\n", " souplist=soup.findAll(\"li\",{'class':'flag'})\n", "\n", " items={}\n", " for s in souplist:\n", " href=s.find('a')['href']\n", " if href:\n", " title=s.find('img')['title']\n", " title = 'Monaco' if title == 'Monte Carlo' else title\n", " items[title]=href\n", " return items\n", "\n", "def listRallies(display=True, **kwargs):\n", " rallyURLs = getRally_URLs(**kwargs)\n", " if display:\n", " print( ', '.join(rallyURLs.keys()) )\n", " else:\n", " return getRallyIDs" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
n_idavailabilitydate-finishdate-startgalleryhasdatahasvideosidinfo-basedinfo-categories...sas-eventidsas-itineraryidsas-rallyidsas-trackingidsitidtestidthumbnailtime-zonetzoffsetyear
0029e41e3-afff-44ad-950f-9af3dccf06benow2015-08-022015-07-30NaNNaNtrueneste-oil-rally-finlandJyväskyläWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN143NaNNaNHelsinkiNaN2015
103bea1af-87f3-43d2-b499-0d9095440ee5NaN2019-08-252019-08-22NaNNaNNaNNaNBostaseeNaN...87138103279187NaNNaNNaN02019
203d002d1-82d8-499c-8252-bdc36685ffd3now2015-10-252015-10-22NaNNaNtruerallyracc-rally-de-espanaSalouWRC, WRC-2, WRC-3...NaNNaNNaNNaN151NaNNaNMadridNaN2015
30919ef82-a65f-4115-8372-1364be04a219now2015-10-042015-10-02NaNNaNtruerallye-de-france-alsaceCorteWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN149NaNNaNParisNaN2015
4098ea36b-902d-4292-8e3a-c85b6ae741b6NaN2019-03-102019-03-07NaNNaNNaNNaNLeonNaN...8013196278480NaNNaNNaN-252000002019
\n", "

5 rows × 32 columns

\n", "
" ], "text/plain": [ "n _id availability date-finish date-start \\\n", "0 029e41e3-afff-44ad-950f-9af3dccf06be now 2015-08-02 2015-07-30 \n", "1 03bea1af-87f3-43d2-b499-0d9095440ee5 NaN 2019-08-25 2019-08-22 \n", "2 03d002d1-82d8-499c-8252-bdc36685ffd3 now 2015-10-25 2015-10-22 \n", "3 0919ef82-a65f-4115-8372-1364be04a219 now 2015-10-04 2015-10-02 \n", "4 098ea36b-902d-4292-8e3a-c85b6ae741b6 NaN 2019-03-10 2019-03-07 \n", "\n", "n gallery hasdata hasvideos id info-based \\\n", "0 NaN NaN true neste-oil-rally-finland Jyväskylä \n", "1 NaN NaN NaN NaN Bostasee \n", "2 NaN NaN true rallyracc-rally-de-espana Salou \n", "3 NaN NaN true rallye-de-france-alsace Corte \n", "4 NaN NaN NaN NaN Leon \n", "\n", "n info-categories ... sas-eventid sas-itineraryid sas-rallyid \\\n", "0 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "1 NaN ... 87 138 103 \n", "2 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "3 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "4 NaN ... 80 131 96 \n", "\n", "n sas-trackingid sitid testid thumbnail time-zone tzoffset year \n", "0 NaN 143 NaN NaN Helsinki NaN 2015 \n", "1 2791 87 NaN NaN NaN 0 2019 \n", "2 NaN 151 NaN NaN Madrid NaN 2015 \n", "3 NaN 149 NaN NaN Paris NaN 2015 \n", "4 2784 80 NaN NaN NaN -25200000 2019 \n", "\n", "[5 rows x 32 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def _getEventMetadata():\n", " ''' Get event metadata as JSON data feed from WRC API. '''\n", " url='https://webappsdata.wrc.com/srv/wrc/json/api/wrcsrv/byType?t=%22Event%22&maxdepth=1'\n", " eventmeta = requests.get(url).json()\n", " return eventmeta\n", "\n", "def getEventMetadata():\n", " ''' Get a list of events from WRC as a flat pandas dataframe.\n", " Itinerary / event data is only available for rallies starting in stated year. '''\n", " eventMetadata = json_normalize(_getEventMetadata(),\n", " record_path='_meta',\n", " meta='_id' ).drop_duplicates().pivot('_id', 'n','v').reset_index()\n", "\n", " eventMetadata['date-finish']=pd.to_datetime(eventMetadata['date-finish'])\n", " eventMetadata['date-start']=pd.to_datetime(eventMetadata['date-start'])\n", " eventMetadata['year'] = eventMetadata['date-start'].dt.year\n", " \n", " return eventMetadata\n", "\n", "\n", "getEventMetadata().head()" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "def _getRallyIDs2(year=YEAR):\n", " em=getEventMetadata()\n", " em = em[em['year']==year][['name','sas-rallyid', 'sas-eventid', 'kmlfile', 'date-start']].reset_index(drop=True).dropna()\n", " em['stub']=em['kmlfile'].apply(lambda x: x.split('_')[0])\n", " return em\n", "\n", "def getRallyIDs2(year=YEAR):\n", " em = _getRallyIDs2(year=year)\n", " return em[['stub','sas-rallyid']].set_index('stub').to_dict()['sas-rallyid']\n", "\n", "def getEventID(year=YEAR):\n", " em = _getRallyIDs2(year=year)\n", " return em[['stub','sas-eventid']].set_index('stub').to_dict()['sas-eventid']\n", "\n", "\n", "def listRallies2(year=YEAR):\n", " return getRallyIDs2(year)" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'sweden': '79', 'montecarlo': '78'}" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "getEventID(2019)" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "ename": "NameError", "evalue": "name 'name' is not defined", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mNameError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0murl_base\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'http://www.wrc.com/service/sasCacheApi.php?route=events/{SASEVENTID}/{{stub}}'\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mSASEVENTID\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mgetEventID\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m2019\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 2\u001b[0m \u001b[0murl_base\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mNameError\u001b[0m: name 'name' is not defined" ] } ], "source": [ "url_base='http://www.wrc.com/service/sasCacheApi.php?route=events/{SASEVENTID}/{{stub}}'.format(SASEVENTID=getEventID(2019)[name])\n", "url_base" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "listRallies2()" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "#This appproach makes literal calls to an original HTML page and is DEPRECATED\n", "def _getRallyID(rallyURL):\n", " html=requests.get(rallyURL)\n", " m = re.search(\"var rallyId = '(.+?)'\", html.text)\n", " if m:\n", " return m.group(1)\n", " return None\n", " \n", "def getRallyIDs(rally=None,results_main_url=None):\n", " rallyids={}\n", "\n", " items = getRally_URLs(results_main_url)\n", " \n", " #if we know the rally, just get that one.. \n", " if rally in items:\n", " items = {rally:items[rally]}\n", "\n", " for item in items:\n", " rallyids[item] = _getRallyID(items[item])\n", "\n", " return rallyids " ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "'''\n", "def set_rallyId(rally, year, rallyIDs=None):\n", " meta={'rallyId':None, 'stages':[], 'championshipId':None }\n", " if rallyIDs is None:\n", " rallyIDs = getRallyIDs()\n", " if rally in rallyIDs:\n", " meta['rallyId']=rallyIDs[rally]\n", " meta['rally_name'] = rally\n", " return meta\n", "'''\n", "\n", "def set_rallyId2(rally, year, rallyIDs=None):\n", " meta={'rallyId':None, 'stages':[], 'championshipId':None }\n", " if rallyIDs is None:\n", " rallyIDs = getRallyIDs2()\n", " if rally in rallyIDs:\n", " meta['rallyId']=rallyIDs[rally]\n", " meta['rally_name'] = rally\n", " return meta" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'rallyId': '95', 'stages': [], 'championshipId': None, 'rally_name': 'sweden'}" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "year = 2019\n", "name = 'sweden'\n", "dbname='sweden19.db'\n", "\n", "meta = set_rallyId2(name, year)\n", "meta" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'sweden': '95', 'montecarlo': '94'}" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "listRallies2()" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "#rallyIDs = getRallyIDs2()\n", "#rallyIDs" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "def nvToDict(nvdict, key='n',val='v', retdict=None):\n", " if retdict is None:\n", " retdict={nvdict[key]:nvdict[val]}\n", " else:\n", " retdict[nvdict[key]]=nvdict[val]\n", " return retdict\n", "#assert nvToDict({'n': \"id\",'v': \"adac-rallye-deutschland\"}) == {'id': 'adac-rallye-deutschland'}" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [], "source": [ " #getEventMetadata()['rosterid'].iloc[0]" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
n_idavailabilitydate-finishdate-startgalleryhasdatahasvideosidinfo-basedinfo-categories...sas-eventidsas-itineraryidsas-rallyidsas-trackingidsitidtestidthumbnailtime-zonetzoffsetyear
0029e41e3-afff-44ad-950f-9af3dccf06benow2015-08-022015-07-30NaNNaNtrueneste-oil-rally-finlandJyväskyläWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN143NaNNaNHelsinkiNaN2015
103bea1af-87f3-43d2-b499-0d9095440ee5NaN2019-08-252019-08-22NaNNaNNaNNaNBostaseeNaN...87138103279187NaNNaNNaN02019
\n", "

2 rows × 32 columns

\n", "
" ], "text/plain": [ "n _id availability date-finish date-start \\\n", "0 029e41e3-afff-44ad-950f-9af3dccf06be now 2015-08-02 2015-07-30 \n", "1 03bea1af-87f3-43d2-b499-0d9095440ee5 NaN 2019-08-25 2019-08-22 \n", "\n", "n gallery hasdata hasvideos id info-based \\\n", "0 NaN NaN true neste-oil-rally-finland Jyväskylä \n", "1 NaN NaN NaN NaN Bostasee \n", "\n", "n info-categories ... sas-eventid sas-itineraryid sas-rallyid \\\n", "0 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "1 NaN ... 87 138 103 \n", "\n", "n sas-trackingid sitid testid thumbnail time-zone tzoffset year \n", "0 NaN 143 NaN NaN Helsinki NaN 2015 \n", "1 2791 87 NaN NaN NaN 0 2019 \n", "\n", "[2 rows x 32 columns]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "getEventMetadata().head(2)" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [], "source": [ "#roster_id='bab64d15-4691-4561-a6bf-7284f3bd85f9'\n", "import requests\n", "#roster_json = requests.get( '{}&maxdepth=2'.format(wrcapi.format(roster_id),) ).json()\n", "#roster_json \n", "\n", "#TO CHECK - is the sas-entryid the entryid we use elsewhere?\n", "\n", "#This comes from event metadata\n", "def _getRoster(roster_id):\n", " roster_json = requests.get(wrcapi.format(roster_id) ).json()\n", " roster=json_normalize(roster_json)\n", " \n", " aa=json_normalize(roster_json, record_path='_dchildren')\n", " zz=json_normalize(roster_json['_dchildren'],record_path=['_meta'], meta='_id').pivot('_id', 'n','v').reset_index()\n", " zz=pd.merge(zz,aa[['_id','name','type']], on='_id')[['fiasn','filename','sas-entryid','name']]\n", " zz.columns = ['fiasn','code','sas-entryid','roster_num']\n", " #defensive?\n", " zz = zz.dropna(subset=['sas-entryid'])\n", " return zz\n", "\n", "def getRoster(meta):\n", " em = getEventMetadata()\n", " roster_id= em[em['sas-rallyid']==meta['rallyId']]['rosterid'].iloc[0]\n", " return _getRoster(roster_id)" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [], "source": [ "def getItinerary(meta):\n", " ''' Get event itinerary. Also updates the stages metadata. '''\n", " itinerary_json=requests.get( url_base.format(stub=stubs['itinerary'].format(**meta) ) ).json()\n", " itinerary_event = json_normalize(itinerary_json).drop('itineraryLegs', axis=1)\n", " \n", " #meta='eventId' for eventId\n", " itinerary_legs = json_normalize(itinerary_json, \n", " record_path='itineraryLegs').drop('itinerarySections', axis=1)\n", " #meta='eventId' for eventId\n", " itinerary_sections = json_normalize(itinerary_json,\n", " ['itineraryLegs', 'itinerarySections']).drop(['stages','controls'],axis=1)\n", "\n", " itinerary_stages=json_normalize(itinerary_json['itineraryLegs'],\n", " ['itinerarySections','stages'],\n", " meta=['itineraryLegId',['itinerarySections','itinerarySectionId']])\n", " meta['stages']=itinerary_stages['stageId'].tolist()\n", " #Should do this a pandas idiomatic way\n", " #meta['_stages']=zip(itinerary_stages['stageId'].tolist(),\n", " # itinerary_stages['code'].tolist(),\n", " # itinerary_stages['status'].tolist())\n", " meta['_stages'] = itinerary_stages[['stageId','code','status']].set_index('code').to_dict(orient='index')\n", " itinerary_controls=json_normalize(itinerary_json['itineraryLegs'], \n", " ['itinerarySections','controls'] ,\n", " meta=['itineraryLegId',['itinerarySections','itinerarySectionId']])\n", " itinerary_controls['stageId'] = itinerary_controls['stageId'].fillna(-1).astype(int)\n", " \n", " return itinerary_event, itinerary_legs, itinerary_sections, itinerary_stages, itinerary_controls" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "ename": "KeyError", "evalue": "'SASEVENTID'", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mKeyError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mitinerary_json\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mrequests\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m \u001b[0murl_base\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstub\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mstubs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'itinerary'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjson\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 2\u001b[0m \u001b[0mitinerary_json\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mKeyError\u001b[0m: 'SASEVENTID'" ] } ], "source": [ "itinerary_json=requests.get( url_base.format(stub=stubs['itinerary'].format(**meta) ) ).json()\n", "itinerary_json" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'rallyId': '95', 'stages': [], 'championshipId': None, 'rally_name': 'sweden'}" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "'''\n", "{'rallyId': '30',\n", " 'stages': [],\n", " 'championshipId': None,\n", " 'rally_name': 'montecarlo'}\n", " '''\n", "\n", "#https://www.wrc.com/service/sasCacheApi.php?route=events%2F78%2Frallies%2F94%2Fitinerary\n", "meta" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "ename": "KeyError", "evalue": "'SASEVENTID'", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mKeyError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mgetItinerary\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;32m\u001b[0m in \u001b[0;36mgetItinerary\u001b[0;34m(meta)\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mgetItinerary\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2\u001b[0m \u001b[0;34m''' Get event itinerary. Also updates the stages metadata. '''\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mitinerary_json\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mrequests\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m \u001b[0murl_base\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstub\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mstubs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'itinerary'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjson\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 4\u001b[0m \u001b[0mitinerary_event\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mjson_normalize\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mitinerary_json\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'itineraryLegs'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 5\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mKeyError\u001b[0m: 'SASEVENTID'" ] } ], "source": [ "getItinerary(meta)" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [], "source": [ "#a,b,c,d,e = getItinerary(meta)" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": [ "def _get_single_json_table(meta, stub):\n", " _json = requests.get( url_base.format(stub=stubs[stub].format(**meta) ) ).json()\n", " return json_normalize(_json)\n", "\n", "def _get_single_json_table_root(meta, stub):\n", " _json = requests.get( url_root.format(stub=stubs[stub].format(**meta) ) ).json()\n", " return json_normalize(_json)" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [], "source": [ "#meta = set_rallyId(name, year)\n", "\n", "#startlists_json=requests.get( url_base.format(stub=stubs['startlists'].format(**meta) ) ).json()\n", "#ff=[]\n", "#for f in startlists_json:\n", "# if f['manufacturer']['logoFilename'] is None:\n", "# f['manufacturer']['logoFilename']=''\n", "# if f['entrant']['logoFilename'] is None:\n", "# f['entrant']['logoFilename']='' \n", "# ff.append(f)\n", "#ff" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [], "source": [ "#startlists = json_normalize(ff).drop('eventClasses', axis=1)" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [], "source": [ "def get_startlists(meta):\n", " startlists_json=requests.get( url_base.format(stub=stubs['startlists'].format(**meta) ) ).json()\n", " ff=[]\n", " for f in startlists_json:\n", " if f['manufacturer']['logoFilename'] is None:\n", " f['manufacturer']['logoFilename']=''\n", " if f['entrant']['logoFilename'] is None:\n", " f['entrant']['logoFilename']='' \n", " ff.append(f)\n", " startlists = json_normalize(ff).drop('eventClasses', axis=1)\n", " startlist_classes = json_normalize(ff,['eventClasses'], 'entryId' )\n", " #startlists = json_normalize(startlists_json).drop('eventClasses', axis=1)\n", " #startlist_classes = json_normalize(startlists_json,['eventClasses'], 'entryId' )\n", " \n", " return startlists, startlist_classes " ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [], "source": [ "def get_penalties(meta):\n", " ''' Get the list of penalties for a specified event. '''\n", " penalties = _get_single_json_table(meta, 'penalties')\n", " return penalties" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [], "source": [ "def get_retirements(meta):\n", " ''' Get the list of retirements for a specified event. '''\n", " retirements = _get_single_json_table(meta, 'retirements')\n", " return retirements" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [], "source": [ "def get_stagewinners(meta):\n", " ''' Get the stage winners table for a specified event. '''\n", " stagewinners = _get_single_json_table(meta, 'stagewinners')\n", " return stagewinners" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [], "source": [ "def _single_stage(meta2, stub, stageId):\n", " ''' For a single stageId, get the requested resource. '''\n", " meta2['stageId']=stageId\n", " _json=requests.get( url_base.format(stub=stubs[stub].format(**meta2) ) ).json()\n", " _df = json_normalize(_json)\n", " _df['stageId'] = stageId\n", " return _df\n", "\n", "def _stage_iterator(meta, stub, stage=None):\n", " ''' Iterate through a list of stageId values and get requested resource. '''\n", " meta2={'rallyId':meta['rallyId']}\n", " df = pd.DataFrame()\n", " #If stage is None get data for all stages\n", " if stage is not None:\n", " stages=[]\n", " #If we have a single stage (specified in form SS4) get it\n", " if isinstance(stage,str) and stage in meta['_stages']:\n", " stages.append(meta['_stages'][stage]['stageId'])\n", " #If we have a list of stages (in form ['SS4','SS5']) get them all\n", " elif isinstance(stage, list):\n", " for _stage in stage:\n", " if isinstance(_stage,str) and _stage in meta['_stages']:\n", " stages.append(meta['_stages'][_stage]['stageId'])\n", " elif _stage in meta['stages']:\n", " stages.append(_stage)\n", " else:\n", " stages = meta['stages']\n", " \n", " #Get data for required stages\n", " for stageId in stages:\n", " #meta2['stageId']=stageId\n", " #_json=requests.get( url_base.format(stub=stubs[stub].format(**meta2) ) ).json()\n", " #_df = json_normalize(_json)\n", " #_df['stageId'] = stageId\n", " _df = _single_stage(meta2, stub, stageId)\n", " df = pd.concat([df, _df], sort=False)\n", " return df.reset_index(drop=True)" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "def _stage_iterator(meta, stub):\n", " ''' Iterate through a list of stageId values and get requested resource. '''\n", " meta2={'rallyId':meta['rallyId']}\n", " df = pd.DataFrame()\n", " for stageId in meta['stages']:\n", " meta2['stageId']=stageId\n", " _json=requests.get( url_base.format(stub=stubs[stub].format(**meta2) ) ).json()\n", " _df = json_normalize(_json)\n", " _df['stageId'] = stageId\n", " df = pd.concat([df, _df], sort=False)\n", " return df" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [], "source": [ "def get_overall(meta, stage=None):\n", " ''' Get the overall results table for all stages on an event or a specified stage. '''\n", " stage_overall = _stage_iterator(meta, 'overall', stage)\n", " return stage_overall" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [], "source": [ "#get_overall(meta)" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [], "source": [ "def get_splitTimes(meta, stage=None):\n", " ''' Get split times table for all stages on an event or a specified stage. '''\n", " split_times = _stage_iterator(meta, 'split_times', stage)\n", " return split_times" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [], "source": [ "#get_splitTimes(meta)" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [], "source": [ "def get_stage_times_stage(meta, stage=None):\n", " ''' Get stage times table for all stages on an event or a specified stage. '''\n", " stage_times_stage = _stage_iterator(meta, 'stage_times_stage', stage)\n", " return stage_times_stage" ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [], "source": [ "def get_stage_times_overall(meta,stage=None):\n", " ''' Get overall stage times table for all stages on an event or a specified stage. '''\n", " stage_times_overall = _stage_iterator(meta, 'stage_times_overall', stage)\n", " return stage_times_overall" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [], "source": [ "#There must be a JSON/API way of getting this rather than having to fish for it\n", "def _get_championship_codesOLD(url=None):\n", " if url is None:\n", " url = 'http://www.wrc.com/en/wrc/results/championship-standings/page/4176----.html'\n", " html2=requests.get(url).text\n", " m = re.search(\"var championshipClasses = (.*?);\", html2, re.DOTALL)\n", " mm=m.group(1).replace('\\n','').replace(\"'\",'\"')\n", " #Hack for null table\n", " for v in ['jwrcDriver','jwrcCoDriver','wrcDriver','wrcCoDriver','wrcManufacturers','wrc2ProDriver',\n", " 'wrc2ProCoDriver','wrc2ProManufacturers','wrc2Driver','wrc2CoDriver']:\n", " mm = mm.replace(v,'[]')\n", " d=json.loads(mm)\n", " #https://stackoverflow.com/a/35758583/454773\n", " championshipClasses={k.replace(' ', ''): v for k, v in d.items()}\n", " return championshipClasses" ] }, { "cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [ { "ename": "NameError", "evalue": "name 'season' is not defined", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mNameError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0;31m# 'championship':'seasons/4/championships/{championshipId}',\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2\u001b[0m \u001b[0;31m# 'championship_results':'seasons/4/championships/{championshipId}/results',\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0m_get_championship_codesOLD\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mseason\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;31mNameError\u001b[0m: name 'season' is not defined" ] } ], "source": [ "# 'championship':'seasons/4/championships/{championshipId}',\n", "# 'championship_results':'seasons/4/championships/{championshipId}/results',\n", "_get_championship_codesOLD(season)" ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [], "source": [ "# TO DO\n", "def get_championship_rounds():\n", " pass\n", " #https://www.wrc.com/service/sasCacheApi.php?route=seasons%2F4%2Fchampionships%2F24" ] }, { "cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[{'seasonId': 1, 'name': 'World Rally Championship', 'year': 2018},\n", " {'seasonId': 4, 'name': 'World Rally Championship', 'year': 2019}]" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def get_seasons():\n", " ''' Get season info. '''\n", " return requests.get(url_root.format(stub=stubs['seasons'] )).json()\n", "#https://www.wrc.com/service/sasCacheApi.php?route=seasons/\n", "get_seasons()" ] }, { "cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'championships': [{'championshipId': 24,\n", " 'seasonId': 4,\n", " 'name': 'FIA World Rally Championship for Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'},\n", " {'championshipId': 25,\n", " 'seasonId': 4,\n", " 'name': 'FIA World Rally Championship for Co-Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'},\n", " {'championshipId': 26,\n", " 'seasonId': 4,\n", " 'name': 'FIA World Rally Championship for Manufacturers',\n", " 'type': 'Manufacturer',\n", " 'fieldOneDescription': 'Name',\n", " 'fieldTwoDescription': 'Manufacturer',\n", " 'fieldThreeDescription': 'LogoFileName',\n", " 'fieldFourDescription': '',\n", " 'fieldFiveDescription': ''},\n", " {'championshipId': 27,\n", " 'seasonId': 4,\n", " 'name': 'FIA WRC 2 Pro Championship for Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'},\n", " {'championshipId': 28,\n", " 'seasonId': 4,\n", " 'name': 'FIA WRC 2 Pro Championship for Co-Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'},\n", " {'championshipId': 29,\n", " 'seasonId': 4,\n", " 'name': 'FIA WRC 2 Pro Championship for Manufacturers',\n", " 'type': 'Manufacturer',\n", " 'fieldOneDescription': 'Name',\n", " 'fieldTwoDescription': 'Manufacturer',\n", " 'fieldThreeDescription': 'LogoFileName',\n", " 'fieldFourDescription': '',\n", " 'fieldFiveDescription': ''},\n", " {'championshipId': 30,\n", " 'seasonId': 4,\n", " 'name': ' FIA WRC 2 Championship for Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'},\n", " {'championshipId': 31,\n", " 'seasonId': 4,\n", " 'name': ' FIA WRC 2 Championship for Co-Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'},\n", " {'championshipId': 32,\n", " 'seasonId': 4,\n", " 'name': 'FIA Junior WRC Championship for Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'},\n", " {'championshipId': 33,\n", " 'seasonId': 4,\n", " 'name': 'FIA Junior WRC Championship for Co-Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'},\n", " {'championshipId': 34,\n", " 'seasonId': 4,\n", " 'name': 'FIA Junior WRC Trophy for Nations',\n", " 'type': 'Nation',\n", " 'fieldOneDescription': 'Name',\n", " 'fieldTwoDescription': 'ISO2',\n", " 'fieldThreeDescription': 'ISO3',\n", " 'fieldFourDescription': '',\n", " 'fieldFiveDescription': ''},\n", " {'championshipId': 35,\n", " 'seasonId': 4,\n", " 'name': 'FIA RGT Cup for Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'},\n", " {'championshipId': 36,\n", " 'seasonId': 4,\n", " 'name': 'FIA RGT Cup for Co-Drivers',\n", " 'type': 'Person',\n", " 'fieldOneDescription': 'FirstName',\n", " 'fieldTwoDescription': 'LastName',\n", " 'fieldThreeDescription': 'CountryISO3',\n", " 'fieldFourDescription': 'Manufacturer',\n", " 'fieldFiveDescription': 'TyreManufacturer'}],\n", " 'seasonRounds': [{'seasonId': 4,\n", " 'eventId': 78,\n", " 'event': {'eventId': 78,\n", " 'countryId': 147,\n", " 'country': {'countryId': 147,\n", " 'name': 'Monaco',\n", " 'iso2': 'MC',\n", " 'iso3': 'MCO'},\n", " 'name': '87e Rallye Automobile Monte-Carlo',\n", " 'slug': '87e-rallye-automobile-monte-carlo-2019',\n", " 'location': 'Monte-Carlo',\n", " 'startDate': '2019-01-24',\n", " 'finishDate': '2019-01-27',\n", " 'timeZoneId': 'Romance Standard Time',\n", " 'timeZoneName': 'Central European Time (+01:00)',\n", " 'timeZoneOffset': 60,\n", " 'surfaces': 'Tarmac & Ice',\n", " 'organiserUrl': 'http://acm.mc/edition/rallye-monte-carlo-edition-2019/',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2782,\n", " 'clerkOfTheCourse': 'Alain Pallanca',\n", " 'stewards': 'Waltraud Wünsch Eric Barrabino Zrinko Gregurek',\n", " 'templateFilename': 'results-report-templates/e2a3be60-c4d4-4c97-9671-8cb701226d6e.dotx'},\n", " 'order': 1},\n", " {'seasonId': 4,\n", " 'eventId': 79,\n", " 'event': {'eventId': 79,\n", " 'countryId': 215,\n", " 'country': {'countryId': 215,\n", " 'name': 'Sweden',\n", " 'iso2': 'SE',\n", " 'iso3': 'SWE'},\n", " 'name': 'Rally Sweden',\n", " 'slug': 'rally-sweden-2019',\n", " 'location': 'Torsby',\n", " 'startDate': '2019-02-14',\n", " 'finishDate': '2019-02-17',\n", " 'timeZoneId': 'W. Europe Standard Time',\n", " 'timeZoneName': 'Central European Time (+01:00)',\n", " 'timeZoneOffset': 60,\n", " 'surfaces': 'Snow',\n", " 'organiserUrl': 'https://rallysweden.com/en/',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2783,\n", " 'clerkOfTheCourse': 'Stig Rune Kjernsli',\n", " 'stewards': 'Waltraud Wünsch Andrew Kellitt Bo Swaner',\n", " 'templateFilename': 'results-report-templates/de6b7684-b04f-4484-be60-6621dd305c0c.dotx'},\n", " 'order': 2},\n", " {'seasonId': 4,\n", " 'eventId': 80,\n", " 'event': {'eventId': 80,\n", " 'countryId': 144,\n", " 'country': {'countryId': 144,\n", " 'name': 'Mexico',\n", " 'iso2': 'MX',\n", " 'iso3': 'MEX'},\n", " 'name': 'Rally Guanajuato Mexico',\n", " 'slug': 'rally-mexico-2019',\n", " 'location': 'Leon',\n", " 'startDate': '2019-03-07',\n", " 'finishDate': '2019-03-10',\n", " 'timeZoneId': 'Central America Standard Time',\n", " 'timeZoneName': 'Central Time (-06:00)',\n", " 'timeZoneOffset': -360,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2784,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 3},\n", " {'seasonId': 4,\n", " 'eventId': 81,\n", " 'event': {'eventId': 81,\n", " 'countryId': 76,\n", " 'country': {'countryId': 76,\n", " 'name': 'France',\n", " 'iso2': 'FR',\n", " 'iso3': 'FRA'},\n", " 'name': 'Tour de Corse',\n", " 'slug': 'tour-de-corse-2019',\n", " 'location': 'Bastia',\n", " 'startDate': '2019-03-28',\n", " 'finishDate': '2019-03-31',\n", " 'timeZoneId': 'Romance Standard Time',\n", " 'timeZoneName': 'Central European Time (+01:00)',\n", " 'timeZoneOffset': 60,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2785,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 4},\n", " {'seasonId': 4,\n", " 'eventId': 82,\n", " 'event': {'eventId': 82,\n", " 'countryId': 11,\n", " 'country': {'countryId': 11,\n", " 'name': 'Argentina',\n", " 'iso2': 'AR',\n", " 'iso3': 'ARG'},\n", " 'name': 'Rally Argentina',\n", " 'slug': 'rally-argentina-2019',\n", " 'location': 'Villa Carlos Paz',\n", " 'startDate': '2019-04-25',\n", " 'finishDate': '2019-04-28',\n", " 'timeZoneId': 'Argentina Standard Time',\n", " 'timeZoneName': 'Argentina Time (-03:00)',\n", " 'timeZoneOffset': -180,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2786,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 5},\n", " {'seasonId': 4,\n", " 'eventId': 83,\n", " 'event': {'eventId': 83,\n", " 'countryId': 45,\n", " 'country': {'countryId': 45, 'name': 'Chile', 'iso2': 'CL', 'iso3': 'CHL'},\n", " 'name': 'Rally Chile',\n", " 'slug': 'rally-chile-2019',\n", " 'location': 'Concepcion',\n", " 'startDate': '2019-05-09',\n", " 'finishDate': '2019-05-12',\n", " 'timeZoneId': 'Pacific SA Standard Time',\n", " 'timeZoneName': 'Chile Time (-03:00)',\n", " 'timeZoneOffset': -180,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2787,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 6},\n", " {'seasonId': 4,\n", " 'eventId': 84,\n", " 'event': {'eventId': 84,\n", " 'countryId': 178,\n", " 'country': {'countryId': 178,\n", " 'name': 'Portugal',\n", " 'iso2': 'PT',\n", " 'iso3': 'PRT'},\n", " 'name': 'Rally de Portugal',\n", " 'slug': 'rally-de-portugal-2019',\n", " 'location': 'Matosinhos',\n", " 'startDate': '2019-05-30',\n", " 'finishDate': '2019-06-02',\n", " 'timeZoneId': 'GMT Standard Time',\n", " 'timeZoneName': 'United Kingdom Time (+00:00)',\n", " 'timeZoneOffset': 0,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2788,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 7},\n", " {'seasonId': 4,\n", " 'eventId': 85,\n", " 'event': {'eventId': 85,\n", " 'countryId': 110,\n", " 'country': {'countryId': 110,\n", " 'name': 'Italy',\n", " 'iso2': 'IT',\n", " 'iso3': 'ITA'},\n", " 'name': 'Rally Italia Sardegna',\n", " 'slug': 'rally-italia-sardegna-2019',\n", " 'location': 'Alghero',\n", " 'startDate': '2019-06-13',\n", " 'finishDate': '2019-06-16',\n", " 'timeZoneId': 'W. Europe Standard Time',\n", " 'timeZoneName': 'Central European Time (+01:00)',\n", " 'timeZoneOffset': 60,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2789,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 8},\n", " {'seasonId': 4,\n", " 'eventId': 86,\n", " 'event': {'eventId': 86,\n", " 'countryId': 75,\n", " 'country': {'countryId': 75,\n", " 'name': 'Finland',\n", " 'iso2': 'FI',\n", " 'iso3': 'FIN'},\n", " 'name': 'Rally Finland',\n", " 'slug': 'rally-finland-2019',\n", " 'location': 'Jyvaskyla',\n", " 'startDate': '2019-08-01',\n", " 'finishDate': '2019-08-04',\n", " 'timeZoneId': 'FLE Standard Time',\n", " 'timeZoneName': 'Eastern European Time (+02:00)',\n", " 'timeZoneOffset': 120,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2790,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 9},\n", " {'seasonId': 4,\n", " 'eventId': 87,\n", " 'event': {'eventId': 87,\n", " 'countryId': 83,\n", " 'country': {'countryId': 83,\n", " 'name': 'Germany',\n", " 'iso2': 'DE',\n", " 'iso3': 'DEU'},\n", " 'name': 'ADAC Rallye Deutschland',\n", " 'slug': 'adac-rallye-deutschland-2019',\n", " 'location': 'Bostasee',\n", " 'startDate': '2019-08-22',\n", " 'finishDate': '2019-08-25',\n", " 'timeZoneId': 'W. Europe Standard Time',\n", " 'timeZoneName': 'Central European Time (+01:00)',\n", " 'timeZoneOffset': 60,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2791,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 10},\n", " {'seasonId': 4,\n", " 'eventId': 88,\n", " 'event': {'eventId': 88,\n", " 'countryId': 228,\n", " 'country': {'countryId': 228,\n", " 'name': 'Turkey',\n", " 'iso2': 'TR',\n", " 'iso3': 'TUR'},\n", " 'name': 'Marmaris Rally of Turkey',\n", " 'slug': 'marmaris-rally-turkey-2019',\n", " 'location': 'Marmaris',\n", " 'startDate': '2019-09-12',\n", " 'finishDate': '2019-09-15',\n", " 'timeZoneId': 'Turkey Standard Time',\n", " 'timeZoneName': 'Turkey Time (+03:00)',\n", " 'timeZoneOffset': 180,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2792,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 11},\n", " {'seasonId': 4,\n", " 'eventId': 89,\n", " 'event': {'eventId': 89,\n", " 'countryId': 235,\n", " 'country': {'countryId': 235,\n", " 'name': 'United Kingdom of Great Britain and Northern Ireland',\n", " 'iso2': 'GB',\n", " 'iso3': 'GBR'},\n", " 'name': 'Wales Rally GB',\n", " 'slug': 'wales-rally-gb-2019',\n", " 'location': 'Deeside',\n", " 'startDate': '2019-10-03',\n", " 'finishDate': '2019-10-06',\n", " 'timeZoneId': 'GMT Standard Time',\n", " 'timeZoneName': 'United Kingdom Time (+00:00)',\n", " 'timeZoneOffset': 0,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2793,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 12},\n", " {'seasonId': 4,\n", " 'eventId': 90,\n", " 'event': {'eventId': 90,\n", " 'countryId': 209,\n", " 'country': {'countryId': 209,\n", " 'name': 'Spain',\n", " 'iso2': 'ES',\n", " 'iso3': 'ESP'},\n", " 'name': 'RACC Rally Catalunya de Espana',\n", " 'slug': 'rally-de-espana-2019',\n", " 'location': 'Salou',\n", " 'startDate': '2019-10-24',\n", " 'finishDate': '2019-10-27',\n", " 'timeZoneId': 'Romance Standard Time',\n", " 'timeZoneName': 'Central European Time (+01:00)',\n", " 'timeZoneOffset': 60,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2794,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 13},\n", " {'seasonId': 4,\n", " 'eventId': 91,\n", " 'event': {'eventId': 91,\n", " 'countryId': 14,\n", " 'country': {'countryId': 14,\n", " 'name': 'Australia',\n", " 'iso2': 'AU',\n", " 'iso3': 'AUS'},\n", " 'name': 'Rally Australia',\n", " 'slug': 'rally-australia-2019',\n", " 'location': 'Coffs Harbous',\n", " 'startDate': '2019-11-14',\n", " 'finishDate': '2019-11-17',\n", " 'timeZoneId': 'AUS Eastern Standard Time',\n", " 'timeZoneName': 'Eastern Australia Time (+11:00)',\n", " 'timeZoneOffset': 660,\n", " 'surfaces': '',\n", " 'organiserUrl': '',\n", " 'categories': None,\n", " 'mode': 'Rally',\n", " 'trackingEventId': 2795,\n", " 'clerkOfTheCourse': None,\n", " 'stewards': None,\n", " 'templateFilename': None},\n", " 'order': 14}],\n", " 'seasonId': 4,\n", " 'name': 'World Rally Championship',\n", " 'year': 2019}" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def getSeasonDetails(seasonId):\n", " return requests.get(url_root.format(stub=stubs['seasonDetails'].format(seasonId=seasonId) )).json()\n", "getSeasonDetails(4)" ] }, { "cell_type": "code", "execution_count": 46, "metadata": {}, "outputs": [], "source": [ "# TO DO set seasonId properly\n", "def championship_tables(champ_class=None, champ_typ=None, seasonId=4):\n", " ''' Get all championship tables in a particular championship and / or class. '''\n", " #if championship is None then get all\n", " championship_lookup = pd.DataFrame()\n", " championship_entries_all = {}\n", " championship_rounds = pd.DataFrame()\n", " championship_events = pd.DataFrame()\n", " championship_results = pd.DataFrame()\n", " \n", " championships = getSeasonDetails(seasonId)['championships']\n", " \n", " for championship in championships:\n", " champ_num = championship['championshipId']\n", " #TO DO - are we setting the champType correctly?\n", " # championship['type'] returns as Person or Manufacturer\n", " champType = championship['name'].split()[-1]#championship['type']\n", " if champType not in championship_entries_all:\n", " championship_entries_all[champType] = pd.DataFrame()\n", " \n", " meta2={'championshipId': champ_num}\n", " championship_url = url_root.format(stub=stubs['championship'].format(**meta2) )\n", " championship_json=requests.get( championship_url ).json()\n", " if championship_json:\n", " _championship_lookup = json_normalize(championship_json).drop(['championshipEntries','championshipRounds'], axis=1)\n", " _championship_lookup['_codeClass'] = championship['name']\n", " _championship_lookup['_codeTyp'] = championship['type']\n", " championship_lookup = pd.concat([championship_lookup,_championship_lookup],sort=True)\n", " \n", " championships={}\n", " championship_dict = _championship_lookup.to_dict()\n", " championships[champ_num] = {c:championship_dict[c][0] for c in championship_dict}\n", " renamer={c.replace('Description',''):championships[champ_num][c] for c in championships[champ_num] if c.startswith('field')} \n", " _championship_entries = json_normalize(championship_json,['championshipEntries'] )\n", " _championship_entries = _championship_entries.rename(columns=renamer)\n", " _championship_entries = _championship_entries[[c for c in _championship_entries.columns if c!='']]\n", " #pd.concat sort=False to retain current behaviour\n", " \n", " championship_entries_all[champType] = pd.concat([championship_entries_all[champType],_championship_entries],sort=False)\n", "\n", " _championship_rounds = json_normalize(championship_json,['championshipRounds'] ).drop('event', axis=1)\n", " championship_rounds = pd.concat([championship_rounds,_championship_rounds],sort=False).drop_duplicates()\n", "\n", " _events_json = json_normalize(championship_json,['championshipRounds' ])['event']\n", " _championship_events = json_normalize(_events_json)\n", " #Below also available as eg https://www.wrc.com/service/sasCacheApi.php?route=seasons/4/championships/24\n", " championship_events = pd.concat([championship_events,_championship_events],sort=False).drop_duplicates()\n", "\n", " _championship_results = _get_single_json_table_root(meta2, 'championship_results')\n", " championship_results = pd.concat([championship_results, _championship_results],sort=False)\n", " \n", " for k in championship_entries_all:\n", " championship_entries_all[k].reset_index(drop=True)\n", " if k in ['Driver', 'Co-Driver']:\n", " championship_entries_all[k] = championship_entries_all[k].rename(columns={'TyreManufacturer':'ManufacturerTyre'})\n", " \n", " return championship_lookup.reset_index(drop=True), \\\n", " championship_results.reset_index(drop=True), \\\n", " championship_entries_all, \\\n", " championship_rounds.reset_index(drop=True), \\\n", " championship_events.reset_index(drop=True)\n", "\n", "#championship_tables()" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "def championship_tablesOLD(champ_class=None, champ_typ=None):\n", " ''' Get all championship tables in a particular championship and / or class. '''\n", " #if championship is None then get all\n", " championship_lookup = pd.DataFrame()\n", " championship_entries_all = {}\n", " championship_rounds = pd.DataFrame()\n", " championship_events = pd.DataFrame()\n", " championship_results = pd.DataFrame()\n", " \n", " championship_codes = _get_championship_codes()\n", " _class_codes = championship_codes.keys() if champ_class is None else [champ_class]\n", " for champClass in _class_codes:\n", " _champ_typ = championship_codes[champClass].keys() if champ_typ is None else [champ_typ]\n", " for champType in _champ_typ:\n", " if champType not in championship_entries_all:\n", " championship_entries_all[champType] = pd.DataFrame()\n", " \n", " champ_num = championship_codes[champClass][champType]\n", " meta2={'championshipId': champ_num}\n", " \n", " championship_url = url_base.format(stub=stubs['championship'].format(**meta2) )\n", " print(championship_url)\n", " championship_json=requests.get( championship_url ).json()\n", " if championship_json:\n", " _championship_lookup = json_normalize(championship_json).drop(['championshipEntries','championshipRounds'], axis=1)\n", " _championship_lookup['_codeClass'] = champClass\n", " _championship_lookup['_codeTyp'] = champType\n", " championship_lookup = pd.concat([championship_lookup,_championship_lookup],sort=True)\n", "\n", " championships={}\n", " championship_dict = _championship_lookup.to_dict()\n", " championships[champ_num] = {c:championship_dict[c][0] for c in championship_dict}\n", " renamer={c.replace('Description',''):championships[champ_num][c] for c in championships[champ_num] if c.startswith('field')} \n", " _championship_entries = json_normalize(championship_json,['championshipEntries'] )\n", " _championship_entries = _championship_entries.rename(columns=renamer)\n", " _championship_entries = _championship_entries[[c for c in _championship_entries.columns if c!='']]\n", " #pd.concat sort=False to retain current behaviour\n", " championship_entries_all[champType] = pd.concat([championship_entries_all[champType],_championship_entries],sort=False)\n", "\n", " _championship_rounds = json_normalize(championship_json,['championshipRounds'] ).drop('event', axis=1)\n", " championship_rounds = pd.concat([championship_rounds,_championship_rounds],sort=False).drop_duplicates()\n", "\n", " _events_json = json_normalize(championship_json,['championshipRounds' ])['event']\n", " _championship_events = json_normalize(_events_json)\n", " #TO DO: Season id -> https://www.wrc.com/service/sasCacheApi.php?route=seasons/\n", " # TO DO: list of championships: eg https://www.wrc.com/service/sasCacheApi.php?route=seasons/4\n", " #Below also available as eg https://www.wrc.com/service/sasCacheApi.php?route=seasons/4/championships/24\n", " championship_events = pd.concat([championship_events,_championship_events],sort=False).drop_duplicates()\n", "\n", " _championship_results = _get_single_json_table(meta2, 'championship_results')\n", " championship_results = pd.concat([championship_results, _championship_results],sort=False)\n", " \n", " for k in championship_entries_all:\n", " championship_entries_all[k].reset_index(drop=True)\n", " if k in ['Driver', 'Co-Driver']:\n", " championship_entries_all[k] = championship_entries_all[k].rename(columns={'TyreManufacturer':'ManufacturerTyre'})\n", " \n", " return championship_lookup.reset_index(drop=True), \\\n", " championship_results.reset_index(drop=True), \\\n", " championship_entries_all, \\\n", " championship_rounds.reset_index(drop=True), \\\n", " championship_events.reset_index(drop=True)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Usage" ] }, { "cell_type": "code", "execution_count": 47, "metadata": {}, "outputs": [], "source": [ "#listRallies2()" ] }, { "cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [], "source": [ "def cleardbtable(conn, table):\n", " ''' Clear the table whilst retaining the table definition '''\n", " c = conn.cursor()\n", " c.execute('DELETE FROM \"{}\"'.format(table))\n", " \n", "def dbfy(conn, df, table, if_exists='upsert', index=False, clear=False, **kwargs):\n", " ''' Save a dataframe as a SQLite table.\n", " Clearing or replacing a table will first empty the table of entries but retain the structure. '''\n", " if if_exists=='replace':\n", " clear=True\n", " if_exists='append'\n", " if clear: cleardbtable(conn, table)\n", " if if_exists=='upsert':\n", " DB[table].upsert_all(df.to_dict(orient='records'))\n", " else:\n", " df.to_sql(table,conn,if_exists=if_exists,index=index)" ] }, { "cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [], "source": [ "def save_rally(meta, conn, stage=None):\n", " ''' Save all tables associated with a particular rally. '''\n", " \n", " if stage is None:\n", " print('Getting base info...')\n", " roster = getRoster(meta)\n", " dbfy(conn, roster, 'roster', if_exists='replace')\n", "\n", " itinerary_event, itinerary_legs, itinerary_sections, \\\n", " itinerary_stages, itinerary_controls = getItinerary(meta)\n", "\n", " dbfy(conn, itinerary_event, 'itinerary_event', if_exists='replace')\n", " dbfy(conn, itinerary_legs, 'itinerary_legs', if_exists='replace')\n", " dbfy(conn, itinerary_sections, 'itinerary_sections', if_exists='replace')\n", " dbfy(conn, itinerary_stages, 'itinerary_stages', if_exists='replace')\n", " dbfy(conn, itinerary_controls, 'itinerary_controls', if_exists='replace')\n", "\n", " startlists, startlist_classes = get_startlists(meta)\n", " dbfy(conn, startlists, 'startlists', if_exists='replace')\n", " dbfy(conn, startlist_classes, 'startlist_classes', if_exists='replace')\n", "\n", " #These need to be upserted\n", " print('Getting penalties...')\n", " penalties = get_penalties(meta)\n", " dbfy(conn, penalties, 'penalties')\n", "\n", " print('Getting retirements...')\n", " retirements = get_retirements(meta)\n", " dbfy(conn, retirements, 'retirements')\n", "\n", " print('Getting stagewinners...')\n", " stagewinners = get_stagewinners(meta)\n", " dbfy(conn, stagewinners, 'stagewinners')\n", "\n", " print('Getting stage_overall...')\n", " stage_overall = get_overall(meta, stage)\n", " dbfy(conn, stage_overall, 'stage_overall')\n", "\n", " print('Getting split_times...')\n", " split_times = get_splitTimes(meta, stage)\n", " dbfy(conn, split_times, 'split_times')\n", " \n", " print('Getting stage_times_stage...')\n", " stage_times_stage = get_stage_times_stage(meta, stage)\n", " dbfy(conn, stage_times_stage, 'stage_times_stage')\n", " \n", " print('Getting stage_times_overall...')\n", " stage_times_overall = get_stage_times_overall(meta, stage)\n", " dbfy(conn, stage_times_overall, 'stage_times_overall')\n", "\n", " \n", "def save_championship(meta, conn):\n", " ''' Save all championship tables for a particular year. '''\n", " championship_lookup, championship_results, _championship_entries_all, \\\n", " championship_rounds, championship_events = championship_tables()\n", " \n", " championship_entries_drivers = _championship_entries_all['Drivers']\n", " championship_entries_codrivers = _championship_entries_all['Co-Drivers']\n", " championship_entries_manufacturers = _championship_entries_all['Manufacturers']\n", " #championship_entries_nations = _championship_entries_all['Nations']\n", " \n", " dbfy(conn, championship_lookup, 'championship_lookup', if_exists='replace')\n", " dbfy(conn, championship_results, 'championship_results', if_exists='replace')\n", " dbfy(conn, championship_entries_drivers, 'championship_entries_drivers',if_exists='replace')\n", " dbfy(conn, championship_entries_codrivers, 'championship_entries_codrivers', if_exists='replace')\n", " dbfy(conn, championship_entries_manufacturers, 'championship_entries_manufacturers', if_exists='replace')\n", " dbfy(conn, championship_rounds, 'championship_rounds', if_exists='replace')\n", " dbfy(conn, championship_events, 'championship_events', if_exists='replace')\n", "\n", "def get_one(rally, stage, dbname='wrc19_test1.db', year=YEAR):\n", " conn = sqlite3.connect(dbname)\n", " meta = set_rallyId2(rally, year)\n", " getItinerary(meta) #to update meta\n", " print(meta)\n", " save_rally(meta, conn, stage)\n", " \n", "def get_all(rally, dbname='wrc19_test1.db', year=YEAR):\n", " \n", " conn = sqlite3.connect(dbname)\n", " \n", " meta = set_rallyId2(rally, year)\n", " \n", " save_rally(meta, conn)\n", " save_championship(meta, conn)\n", " \n", "def get_championship(rally, dbname='wrc19_test1.db', year=YEAR):\n", " \n", " conn = sqlite3.connect(dbname)\n", " \n", " meta = set_rallyId2(rally, year)\n", "\n", " save_championship(meta, conn)\n" ] }, { "cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [ { "ename": "KeyError", "evalue": "'SASEVENTID'", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mKeyError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0mchampionship_lookup\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mchampionship_results\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_championship_entries_all\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m\\\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mchampionship_rounds\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mchampionship_events\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mchampionship_tables\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;32m\u001b[0m in \u001b[0;36mchampionship_tables\u001b[0;34m(champ_class, champ_typ, seasonId)\u001b[0m\n\u001b[1;32m 47\u001b[0m \u001b[0mchampionship_events\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconcat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mchampionship_events\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0m_championship_events\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0msort\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop_duplicates\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 48\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 49\u001b[0;31m \u001b[0m_championship_results\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_get_single_json_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmeta2\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'championship_results'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 50\u001b[0m \u001b[0mchampionship_results\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconcat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mchampionship_results\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_championship_results\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0msort\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 51\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m\u001b[0m in \u001b[0;36m_get_single_json_table\u001b[0;34m(meta, stub)\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_get_single_json_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstub\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0m_json\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrequests\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m \u001b[0murl_base\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstub\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mstubs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mstub\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjson\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 3\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mjson_normalize\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_json\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mKeyError\u001b[0m: 'SASEVENTID'" ] } ], "source": [ "championship_lookup, championship_results, _championship_entries_all, \\\n", " championship_rounds, championship_events = championship_tables()" ] }, { "cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [ { "ename": "NameError", "evalue": "name '_championship_entries_all' is not defined", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mNameError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0m_championship_entries_all\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Manufacturers'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;31mNameError\u001b[0m: name '_championship_entries_all' is not defined" ] } ], "source": [ "_championship_entries_all['Manufacturers']" ] }, { "cell_type": "code", "execution_count": 52, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'sweden': '95', 'montecarlo': '94'}" ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#listRallies()\n", "listRallies2()" ] }, { "cell_type": "code", "execution_count": 53, "metadata": {}, "outputs": [], "source": [ "#dbname='wrc18.db'\n", "#year = 2019\n", "#name = 'sweden'\n", "#dbname='sweden.db'" ] }, { "cell_type": "code", "execution_count": 54, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'rallyId': '95', 'stages': [], 'championshipId': None, 'rally_name': 'sweden'}" ] }, "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ "meta = set_rallyId2(name, year)\n", "meta" ] }, { "cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [], "source": [ "import os\n", "\n", "#For some reason, these don't seem to get set / picked up correctly from a notebook?\n", "os.environ[\"WRC_RESULTS_NAME\"] = name\n", "os.environ[\"WRC_RESULTS_DBNAME\"] = dbname\n", "os.environ[\"WRC_RESULTS_YEAR\"] = str(year)" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "_meta=set_rallyId2(name, year)\n", "_,_,_,_s,_ = getItinerary(_meta)\n", "_meta" ] }, { "cell_type": "code", "execution_count": 56, "metadata": {}, "outputs": [], "source": [ "#championship_tables()" ] }, { "cell_type": "code", "execution_count": 57, "metadata": {}, "outputs": [], "source": [ "#set_rallyId2('uk', 2018)" ] }, { "cell_type": "code", "execution_count": 58, "metadata": {}, "outputs": [], "source": [ "#rr=get_retirements(meta)\n", "#rr.head()" ] }, { "cell_type": "code", "execution_count": 59, "metadata": {}, "outputs": [], "source": [ "# TO DO - ability to top up just the stage we need" ] }, { "cell_type": "code", "execution_count": 60, "metadata": {}, "outputs": [], "source": [ "#set_rallyId(\"Finland\",2018)\n", "#set_rallyId2(\"australia\",2018)" ] }, { "cell_type": "code", "execution_count": 61, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['_id', 'availability', 'date-finish', 'date-start', 'gallery',\n", " 'hasdata', 'hasvideos', 'id', 'info-based', 'info-categories',\n", " 'info-date', 'info-flag', 'info-surface', 'info-website', 'kmlfile',\n", " 'logo', 'name', 'org-website', 'poi-Klo im Wald', 'poilistid',\n", " 'position', 'rosterid', 'sas-eventid', 'sas-itineraryid', 'sas-rallyid',\n", " 'sas-trackingid', 'sitid', 'testid', 'thumbnail', 'time-zone',\n", " 'tzoffset', 'year'],\n", " dtype='object', name='n')" ] }, "execution_count": 61, "metadata": {}, "output_type": "execute_result" } ], "source": [ "getEventMetadata().columns" ] }, { "cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [], "source": [ "#full run\n", "#dbname = 'wrc18.db'" ] }, { "cell_type": "code", "execution_count": 63, "metadata": {}, "outputs": [], "source": [ "#from sqlite_utils import Database\n", "#DB = Database(sqlite3.connect(dbname))" ] }, { "cell_type": "code", "execution_count": 81, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "rm: sweden19.db: No such file or directory\r\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
0itinerary_event
1itinerary_legs
2itinerary_sections
3itinerary_stages
4itinerary_controls
5startlists
6roster
7startlist_classes
8penalties
9retirements
10stagewinners
11stage_overall
12split_times
13stage_times_stage
14stage_times_overall
15championship_lookup
16championship_results
17championship_entries_codrivers
18championship_entries_manufacturers
19championship_rounds
20championship_events
21championship_entries_drivers
22event_metadata
\n", "
" ], "text/plain": [ " name\n", "0 itinerary_event\n", "1 itinerary_legs\n", "2 itinerary_sections\n", "3 itinerary_stages\n", "4 itinerary_controls\n", "5 startlists\n", "6 roster\n", "7 startlist_classes\n", "8 penalties\n", "9 retirements\n", "10 stagewinners\n", "11 stage_overall\n", "12 split_times\n", "13 stage_times_stage\n", "14 stage_times_overall\n", "15 championship_lookup\n", "16 championship_results\n", "17 championship_entries_codrivers\n", "18 championship_entries_manufacturers\n", "19 championship_rounds\n", "20 championship_events\n", "21 championship_entries_drivers\n", "22 event_metadata" ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#new db\n", "!mv $dbname old-$dbname\n", "!rm $dbname\n", "conn = sqlite3.connect(dbname, timeout=10)\n", "c = conn.cursor()\n", "\n", "DB = Database(sqlite3.connect(dbname))\n", "\n", "c.executescript(setup_q)\n", "c.executescript(setup_views_q)\n", "q=\"SELECT name FROM sqlite_master WHERE type = 'table';\"\n", "\n", "#The upsert breaks with the - and space chars in column names\n", "dbfy(conn, getEventMetadata(), 'event_metadata', if_exists='replace')\n", "\n", "url_base='http://www.wrc.com/service/sasCacheApi.php?route=events/{SASEVENTID}/{{stub}}'\n", "url_base = url_base.format(SASEVENTID=getEventID(year)[name])\n", "\n", "\n", "pd.read_sql(q,conn)" ] }, { "cell_type": "code", "execution_count": 65, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'sweden19.db'" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dbname" ] }, { "cell_type": "code", "execution_count": 66, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
n_idavailabilitydate-finishdate-startgalleryhasdatahasvideosidinfo-basedinfo-categories...sas-eventidsas-itineraryidsas-rallyidsas-trackingidsitidtestidthumbnailtime-zonetzoffsetyear
0029e41e3-afff-44ad-950f-9af3dccf06benow2015-08-022015-07-30NaNNaNtrueneste-oil-rally-finlandJyväskyläWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN143NaNNaNHelsinkiNaN2015
103bea1af-87f3-43d2-b499-0d9095440ee5NaN2019-08-252019-08-22NaNNaNNaNNaNBostaseeNaN...87138103279187NaNNaNNaN02019
203d002d1-82d8-499c-8252-bdc36685ffd3now2015-10-252015-10-22NaNNaNtruerallyracc-rally-de-espanaSalouWRC, WRC-2, WRC-3...NaNNaNNaNNaN151NaNNaNMadridNaN2015
30919ef82-a65f-4115-8372-1364be04a219now2015-10-042015-10-02NaNNaNtruerallye-de-france-alsaceCorteWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN149NaNNaNParisNaN2015
4098ea36b-902d-4292-8e3a-c85b6ae741b6NaN2019-03-102019-03-07NaNNaNNaNNaNLeonNaN...8013196278480NaNNaNNaN-252000002019
50b24651f-0eeb-4e44-81ed-2a5aa3a3eba9now2017-04-302017-04-27NaNNaNtruerally-argentinaVilla Carlos PazWRC, WRC-2, WRC-3...NaNNaNNaNNaN224NaNNaNBuenos Aires-144000002017
60d6cf890-381e-4010-9fd8-36d942ac80d2now2017-02-122017-02-09NaNNaNtruerally-swedenKarlstadWRC, WRC-2, WRC-3...NaNNaNNaNNaN218NaNNaNStockholmNaN2017
70f3fa7ea-f3f1-493a-87d5-5cb3f0fad39enow2018-09-162018-09-13NaNNaNtrueNaNMarmarisNaN...3511439259635NaNNaNNaN108000002018
81127f742-70ee-4dc7-b093-05d7e7a8b8faNaN2019-03-312019-03-28NaNNaNNaNNaNBastiaNaN...8113297278581NaNNaNNaN02019
9117b1be7-1d26-447f-a405-878da1112436now2014-08-242014-08-21NaNNaNtrueadac-rallye-deutschlandTrierWRC, WRC-2, WRC-3...NaNNaNNaNNaN91NaNhttp://www.wrc.com/fileadmin/images/Calendar/2...Europe/BerlinNaN2014
10182ce929-8bda-4acf-9fd1-c178d9d68482now2018-06-102018-06-07NaNNaNtrueNaNSardegnaNaN...328336259332NaNNaNNaN36000002018
111a3cdf87-4d91-435c-a1f9-7aab556fc3b1now2017-08-202017-08-17NaNNaNtrueadac-rallye-deutschlandTrierWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN234NaNNaNBerlin36000002017
121d7c1500-42fd-4045-b447-1cc8006245b6NaN2014-06-082014-06-05NaNNaNNaNrally-italia-sardegnaAlgheroWRC, WRC-2, WRC-3...NaNNaNNaNNaN82NaNhttp://www.wrc.com/fileadmin/images/Calendar/2...RomeNaN2014
13209a7294-32ec-489e-bd86-14488dcc3f14now2018-04-082018-04-06NaNNaNtrueNaNBastiaNaN...293433259029NaNNaNNaN36000002018
14218862ea-0ead-4302-af36-8c15c79b558cnow2016-10-302016-10-28NaNNaNtruewales-rally-gbDeesideWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN199NaNNaNLondonNaN2016
1525313603-333c-40d4-82b9-c3868e6d0f61now2016-10-022016-09-30NaNNaNtruetour-de-corse-2016AjaccioWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN195NaNNaNParisNaN2016
1625e409c3-4122-43b8-b145-3892a7994790now2016-02-142016-02-12NaNNaNtruerally-swedenKarlstadWRC, WRC-2, WRC-3...NaNNaNNaNNaN177NaNNaNStockholmNaN2016
172e36e2f3-949e-4495-a37f-e5a4e81b4d15now2016-10-162016-10-14NaNNaNtruerallyracc-catalunya-costa-dauradaSalouWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN197NaNNaNMadridNaN2016
182fbc34c2-77eb-49cc-851f-ea2be5220bccnow2016-04-242016-04-21NaNNaNtruerally-argentinaVilla Carlos PazWRC, WRC-2, WRC-3...NaNNaNNaNNaN181NaNNaNBuenos Aires-144000002016
1931678d36-3066-4158-b04f-ddfc8a540c00now2015-09-132015-09-11NaNNaNtruecoates-hire-rally-australiaCoffs HarbourWRC, WRC-2, WRC-3...NaNNaNNaNNaN147NaNNaNBrisbaneNaN2015
2033635eee-719b-4d80-a87b-dc9c08a8dab8now2017-03-122017-03-09NaNNaNtruerally-guanajuato-mexicoLeonWRC, WRC-2, WRC-3...NaNNaNNaNNaN220NaNNaNMexico City-252000002017
213bb56cbc-0d15-44b7-8b4b-763d1a5c5581NaN2019-04-282019-04-25NaNNaNNaNNaNVilla Carlos PazNaN...8213398278682NaNNaNNaN-144000002019
223e9e8b54-0825-47a5-b154-8a6d4bee526enow2018-10-282018-10-25NaNNaNtrueNaNSalouNaN...3712441259837NaNNaNNaN360000002018
23419122ea-e3cb-4fca-bc06-f7d144dc3d2anow2016-07-032016-06-30NaNNaNtruelotos-rally-polandMikolajkiWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN187NaNNaNWarsawNaN2016
2445399001-bb08-4e0a-8273-663d1601ac2enow2018-07-292018-07-26NaNNaNtrueNaNJyväskyläNaN...3310637259433NaNNaNNaN72000002018
254c633f0d-5727-4491-b6cd-584f345c043eNaN2019-06-022019-05-30NaNNaNNaNNaNMatosinhosNaN...84135100278884NaNNaNNaN-36000002019
264d124a8b-f36b-43a3-93fb-d6e50bfe9a85now2018-05-202018-05-17NaNNaNtrueNaNMatosinhosNaN...317835259231NaNNaNNaN02018
274f81cf04-c9f8-4e34-b961-c2f4a4307a34now2016-06-122016-06-09NaNNaNtruerally-italia-sardegnaAlgheroWRC, WRC-2, WRC-3...NaNNaNNaNNaN185NaNNaNRomeNaN2016
2850f63c8e-7612-4918-8376-c745777ec8a4NaN2014-01-182014-01-16NaNNaNNaNrallye-monte-carloGap and MonacoWRC, WRC-2, WRC-3...NaNNaNNaNNaN67NaNNaNEurope/ParisNaN2014
2951282a82-107e-45db-b3ab-e3e38bbcb770now2014-09-142014-09-12http://www.wrc.com/service/gallery.php?appwv=0...NaNtruecoates-hire-rally-australiaCoffs HarbourWRC, WRC-2, WRC-3...NaNNaNNaNNaN94NaNhttp://www.wrc.com/fileadmin/images/Calendar/2...Australia/BrisbaneNaN2014
..................................................................
509c9b9998-f99b-48e7-be1c-e0eb3ec6f9c8NaN2019-11-172019-11-14NaNNaNNaNNaNCoffs HarbousNaN...91142107279591NaNNaNNaN360000002019
519ccc5470-5691-44c0-b8ed-d244d97a8ba0now2016-03-062016-03-04NaNNaNtruerally-guanajuato-mexicoLeonWRC, WRC-2, WRC-3...NaNNaNNaNNaN179NaNNaNMexico City-216000002016
52a0738cb4-ed12-42a5-b411-4fa189f18584now2014-08-032014-08-01NaNNaNtrueneste-oil-rally-finlandJyväskylaWRC, WRC-2, WRC-3...NaNNaNNaNNaN88NaNhttp://www.wrc.com/fileadmin/images/Calendar/2...Europe/HelsinkiNaN2014
53a0c3103c-1203-4642-92ee-3f3cb47fc3b5now2018-11-182018-11-15NaNNaNtrueNaNCoffs HarbourNaN...3812542259938NaNNaNNaN360000002018
54a506b05d-371f-4110-bd94-c0d1ff725926now2018-01-282018-01-25NaNNaNtrueNaNMonte CarloNaN...263130258626NaNNaNNaN02018
55acb0a6ac-f44b-45e3-bb5f-b6149112308cnow2017-04-092017-04-07NaNNaNtruetour-de-corse-2017AjaccioWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN222NaNNaNParis36000002017
56ad276338-3c76-418f-9f54-b9531f5a784fnow2018-10-072018-10-04NaNNaNtrueNaNDeesideNaN...3611740259736NaNNaNNaN36000002018
57b08b8196-beb8-40d3-92c8-3d057817134aNaN2014-04-062014-04-03NaNNaNNaNvodafone-rally-de-portugalFaroWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN76NaNhttp://www.wrc.com/fileadmin/images/Calendar/2...LisbonNaN2014
58b69d27e8-8d66-48c4-9840-aae1c9835c58NaN2014-03-092014-03-06NaNNaNNaNrally-guanajuato-mexicoLeonWRC, WRC-2, WRC-3...NaNNaNNaNNaN73NaNhttp://www.wrc.com/fileadmin/images/Calendar/2...GuadalajaraNaN2014
59b75fda9c-3113-427d-87b0-aa6a283ea3afnow2015-04-262015-04-23http://www.wrc.com/service/gallery.php?appwv=0...NaNtruerally-argentinaVilla Carlos PazWRC, WRC-2, WRC-3...NaNNaNNaNNaN135NaNNaNBuenos AiresNaN2015
60b8706701-bf54-48db-8e1c-691e824d6213now2017-07-022017-06-29NaNNaNtruepzm-rally-polandMikolajkiWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN230NaNNaNWarsaw36000002017
61b9a212ca-6fc6-4a16-9d71-49d4f452ec47now2015-07-052015-07-02http://www.wrc.com/service/gallery.php?appwv=0...NaNtruelotos-rally-polandMikolajkiWRC, WRC-2, WRC-3...NaNNaNNaNNaN141NaNNaNWarsawNaN2015
62bf065c52-ef68-49a4-8a18-29758838e4f4now2019-01-272019-01-24NaNNaNtrueNaNMonte-CarloNaN...7814494278278NaNNaNNaN02019
63c2ef6635-d041-4ca7-a193-d974a1810c62now2018-02-182018-02-15NaNNaNtrueNaNSwedenNaN...273231258727NaNNaNNaN02018
64d2ec66f4-75e6-4cbc-a2e8-b1ccdeb9353fnow2017-11-192017-11-17NaNNaNtruekennards-hire-rally-australiaCoffs HarbourWRC, WRC-2, WRC-3...NaNNaNNaNNaN240NaNNaNNew Caledonia360000002017
65d3bacdde-a073-4d55-9c88-c3e790acdd99now2015-05-242015-05-21http://www.wrc.com/service/gallery.php?appwv=0...NaNtruevodafone-rally-de-portugalPortoWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN137NaNNaNLisbonNaN2015
66d3f91d59-c1ad-49de-a74b-c0f248a52e5fnow2014-10-262014-10-23NaNNaNtruerallyracc-rally-de-espanaSalouWRC, WRC-2, WRC-3...NaNNaNNaNNaN100NaNhttp://www.wrc.com/fileadmin/images/Calendar/2...Europe/MadridNaN2014
67d5b87e31-fe70-436f-acfc-4b68962e23b4now2016-05-222016-05-19NaNNaNtruevodafone-rally-de-portugalMatosinhosWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN183NaNNaNLisbon02016
68d6a23bd3-a840-42b6-8afc-c0c52dc89138NaN2019-06-162019-06-13NaNNaNNaNNaNAlgheroNaN...85136101278985NaNNaNNaN02019
69d77b5bd3-9031-4de7-a3df-bd8d392c179cNaN2019-09-152019-09-12NaNNaNNaNNaNMarmarisNaN...88139104279288NaNNaNNaN72000002019
70d7e7150b-a6e6-4bab-bb22-6009a3c30f27NaN2019-05-122019-05-09NaNNaNNaNNaNConcepcionNaN...8313499278783NaNNaNNaN-144000002019
71dacb6932-e302-4806-b843-28a260227013now2017-06-112017-06-08NaNNaNtruerally-italia-sardegnaAlgheroWRC, WRC-2, WRC-3...NaNNaNNaNNaN228NaNNaNRome36000002017
72e2110172-5d94-4b8e-a814-a6ff58ba3cf4now2017-07-302017-07-27NaNNaNtrueneste-rally-finlandJyväskyläWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN232NaNNaNHelsinki72000002017
73e618baba-f112-48ac-b642-c8225f57004bNaN2014-02-082014-02-05NaNNaNNaNrally-swedenKarlstadWRC, WRC-2, WRC-3...NaNNaNNaNNaN70NaNhttp://www.wrc.com/fileadmin/images/Calendar/2...Europe/ParisNaN2014
74e77f81e6-0e13-44d6-a3cd-d64b577aa656now2015-02-152015-02-12NaNNaNtruerally-swedenKarlstadWRC, WRC-2, WRC-3...NaNNaNNaNNaN131NaNNaNParisNaN2015
75e81a90af-acf1-41e3-b9be-4afea9e0378enow2016-07-312016-07-28NaNNaNtrueneste-oil-rally-finlandJyväskyläWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN189NaNNaNHelsinki36000002016
76e921099e-be24-4ef7-a874-4c89a318c87enow2015-01-252015-01-22NaNNaNtruerallye-monte-carloGap and MonacoWRC, WRC-2, WRC-3...NaNNaNNaNNaN130NaNNaNParisNaN2015
77ed1debca-0913-4568-8479-00623b3975cbnow2014-11-162014-11-14NaNNaNtruewales-rally-gbDeesideWRC, WRC-2, WRC-3...NaNNaNNaNNaN1031http://www.wrc.com/fileadmin/images/Calendar/2...Europe/LondonNaN2014
78ee21bc9b-885a-41dc-bc35-88c1e8ebc323NaN2014-05-112014-05-08NaNNaNtruerally-argentinaVilla Carlos PazWRC, WRC-2, WRC-3...NaNNaNNaNNaN79NaNhttp://images.wrc.com/News/2637_wrc-argentina-...Buenos AiresNaN2014
79f34f9b28-199d-4e5a-95b9-dc012363f064now2016-08-212016-08-19NaNNaNtrueadac-rallye-deutschlandTrierWRC, WRC-2, WRC-3, JWRC...NaNNaNNaNNaN191NaNNaNBerlinNaN2016
\n", "

80 rows × 32 columns

\n", "
" ], "text/plain": [ "n _id availability date-finish date-start \\\n", "0 029e41e3-afff-44ad-950f-9af3dccf06be now 2015-08-02 2015-07-30 \n", "1 03bea1af-87f3-43d2-b499-0d9095440ee5 NaN 2019-08-25 2019-08-22 \n", "2 03d002d1-82d8-499c-8252-bdc36685ffd3 now 2015-10-25 2015-10-22 \n", "3 0919ef82-a65f-4115-8372-1364be04a219 now 2015-10-04 2015-10-02 \n", "4 098ea36b-902d-4292-8e3a-c85b6ae741b6 NaN 2019-03-10 2019-03-07 \n", "5 0b24651f-0eeb-4e44-81ed-2a5aa3a3eba9 now 2017-04-30 2017-04-27 \n", "6 0d6cf890-381e-4010-9fd8-36d942ac80d2 now 2017-02-12 2017-02-09 \n", "7 0f3fa7ea-f3f1-493a-87d5-5cb3f0fad39e now 2018-09-16 2018-09-13 \n", "8 1127f742-70ee-4dc7-b093-05d7e7a8b8fa NaN 2019-03-31 2019-03-28 \n", "9 117b1be7-1d26-447f-a405-878da1112436 now 2014-08-24 2014-08-21 \n", "10 182ce929-8bda-4acf-9fd1-c178d9d68482 now 2018-06-10 2018-06-07 \n", "11 1a3cdf87-4d91-435c-a1f9-7aab556fc3b1 now 2017-08-20 2017-08-17 \n", "12 1d7c1500-42fd-4045-b447-1cc8006245b6 NaN 2014-06-08 2014-06-05 \n", "13 209a7294-32ec-489e-bd86-14488dcc3f14 now 2018-04-08 2018-04-06 \n", "14 218862ea-0ead-4302-af36-8c15c79b558c now 2016-10-30 2016-10-28 \n", "15 25313603-333c-40d4-82b9-c3868e6d0f61 now 2016-10-02 2016-09-30 \n", "16 25e409c3-4122-43b8-b145-3892a7994790 now 2016-02-14 2016-02-12 \n", "17 2e36e2f3-949e-4495-a37f-e5a4e81b4d15 now 2016-10-16 2016-10-14 \n", "18 2fbc34c2-77eb-49cc-851f-ea2be5220bcc now 2016-04-24 2016-04-21 \n", "19 31678d36-3066-4158-b04f-ddfc8a540c00 now 2015-09-13 2015-09-11 \n", "20 33635eee-719b-4d80-a87b-dc9c08a8dab8 now 2017-03-12 2017-03-09 \n", "21 3bb56cbc-0d15-44b7-8b4b-763d1a5c5581 NaN 2019-04-28 2019-04-25 \n", "22 3e9e8b54-0825-47a5-b154-8a6d4bee526e now 2018-10-28 2018-10-25 \n", "23 419122ea-e3cb-4fca-bc06-f7d144dc3d2a now 2016-07-03 2016-06-30 \n", "24 45399001-bb08-4e0a-8273-663d1601ac2e now 2018-07-29 2018-07-26 \n", "25 4c633f0d-5727-4491-b6cd-584f345c043e NaN 2019-06-02 2019-05-30 \n", "26 4d124a8b-f36b-43a3-93fb-d6e50bfe9a85 now 2018-05-20 2018-05-17 \n", "27 4f81cf04-c9f8-4e34-b961-c2f4a4307a34 now 2016-06-12 2016-06-09 \n", "28 50f63c8e-7612-4918-8376-c745777ec8a4 NaN 2014-01-18 2014-01-16 \n", "29 51282a82-107e-45db-b3ab-e3e38bbcb770 now 2014-09-14 2014-09-12 \n", ".. ... ... ... ... \n", "50 9c9b9998-f99b-48e7-be1c-e0eb3ec6f9c8 NaN 2019-11-17 2019-11-14 \n", "51 9ccc5470-5691-44c0-b8ed-d244d97a8ba0 now 2016-03-06 2016-03-04 \n", "52 a0738cb4-ed12-42a5-b411-4fa189f18584 now 2014-08-03 2014-08-01 \n", "53 a0c3103c-1203-4642-92ee-3f3cb47fc3b5 now 2018-11-18 2018-11-15 \n", "54 a506b05d-371f-4110-bd94-c0d1ff725926 now 2018-01-28 2018-01-25 \n", "55 acb0a6ac-f44b-45e3-bb5f-b6149112308c now 2017-04-09 2017-04-07 \n", "56 ad276338-3c76-418f-9f54-b9531f5a784f now 2018-10-07 2018-10-04 \n", "57 b08b8196-beb8-40d3-92c8-3d057817134a NaN 2014-04-06 2014-04-03 \n", "58 b69d27e8-8d66-48c4-9840-aae1c9835c58 NaN 2014-03-09 2014-03-06 \n", "59 b75fda9c-3113-427d-87b0-aa6a283ea3af now 2015-04-26 2015-04-23 \n", "60 b8706701-bf54-48db-8e1c-691e824d6213 now 2017-07-02 2017-06-29 \n", "61 b9a212ca-6fc6-4a16-9d71-49d4f452ec47 now 2015-07-05 2015-07-02 \n", "62 bf065c52-ef68-49a4-8a18-29758838e4f4 now 2019-01-27 2019-01-24 \n", "63 c2ef6635-d041-4ca7-a193-d974a1810c62 now 2018-02-18 2018-02-15 \n", "64 d2ec66f4-75e6-4cbc-a2e8-b1ccdeb9353f now 2017-11-19 2017-11-17 \n", "65 d3bacdde-a073-4d55-9c88-c3e790acdd99 now 2015-05-24 2015-05-21 \n", "66 d3f91d59-c1ad-49de-a74b-c0f248a52e5f now 2014-10-26 2014-10-23 \n", "67 d5b87e31-fe70-436f-acfc-4b68962e23b4 now 2016-05-22 2016-05-19 \n", "68 d6a23bd3-a840-42b6-8afc-c0c52dc89138 NaN 2019-06-16 2019-06-13 \n", "69 d77b5bd3-9031-4de7-a3df-bd8d392c179c NaN 2019-09-15 2019-09-12 \n", "70 d7e7150b-a6e6-4bab-bb22-6009a3c30f27 NaN 2019-05-12 2019-05-09 \n", "71 dacb6932-e302-4806-b843-28a260227013 now 2017-06-11 2017-06-08 \n", "72 e2110172-5d94-4b8e-a814-a6ff58ba3cf4 now 2017-07-30 2017-07-27 \n", "73 e618baba-f112-48ac-b642-c8225f57004b NaN 2014-02-08 2014-02-05 \n", "74 e77f81e6-0e13-44d6-a3cd-d64b577aa656 now 2015-02-15 2015-02-12 \n", "75 e81a90af-acf1-41e3-b9be-4afea9e0378e now 2016-07-31 2016-07-28 \n", "76 e921099e-be24-4ef7-a874-4c89a318c87e now 2015-01-25 2015-01-22 \n", "77 ed1debca-0913-4568-8479-00623b3975cb now 2014-11-16 2014-11-14 \n", "78 ee21bc9b-885a-41dc-bc35-88c1e8ebc323 NaN 2014-05-11 2014-05-08 \n", "79 f34f9b28-199d-4e5a-95b9-dc012363f064 now 2016-08-21 2016-08-19 \n", "\n", "n gallery hasdata hasvideos \\\n", "0 NaN NaN true \n", "1 NaN NaN NaN \n", "2 NaN NaN true \n", "3 NaN NaN true \n", "4 NaN NaN NaN \n", "5 NaN NaN true \n", "6 NaN NaN true \n", "7 NaN NaN true \n", "8 NaN NaN NaN \n", "9 NaN NaN true \n", "10 NaN NaN true \n", "11 NaN NaN true \n", "12 NaN NaN NaN \n", "13 NaN NaN true \n", "14 NaN NaN true \n", "15 NaN NaN true \n", "16 NaN NaN true \n", "17 NaN NaN true \n", "18 NaN NaN true \n", "19 NaN NaN true \n", "20 NaN NaN true \n", "21 NaN NaN NaN \n", "22 NaN NaN true \n", "23 NaN NaN true \n", "24 NaN NaN true \n", "25 NaN NaN NaN \n", "26 NaN NaN true \n", "27 NaN NaN true \n", "28 NaN NaN NaN \n", "29 http://www.wrc.com/service/gallery.php?appwv=0... NaN true \n", ".. ... ... ... \n", "50 NaN NaN NaN \n", "51 NaN NaN true \n", "52 NaN NaN true \n", "53 NaN NaN true \n", "54 NaN NaN true \n", "55 NaN NaN true \n", "56 NaN NaN true \n", "57 NaN NaN NaN \n", "58 NaN NaN NaN \n", "59 http://www.wrc.com/service/gallery.php?appwv=0... NaN true \n", "60 NaN NaN true \n", "61 http://www.wrc.com/service/gallery.php?appwv=0... NaN true \n", "62 NaN NaN true \n", "63 NaN NaN true \n", "64 NaN NaN true \n", "65 http://www.wrc.com/service/gallery.php?appwv=0... NaN true \n", "66 NaN NaN true \n", "67 NaN NaN true \n", "68 NaN NaN NaN \n", "69 NaN NaN NaN \n", "70 NaN NaN NaN \n", "71 NaN NaN true \n", "72 NaN NaN true \n", "73 NaN NaN NaN \n", "74 NaN NaN true \n", "75 NaN NaN true \n", "76 NaN NaN true \n", "77 NaN NaN true \n", "78 NaN NaN true \n", "79 NaN NaN true \n", "\n", "n id info-based \\\n", "0 neste-oil-rally-finland Jyväskylä \n", "1 NaN Bostasee \n", "2 rallyracc-rally-de-espana Salou \n", "3 rallye-de-france-alsace Corte \n", "4 NaN Leon \n", "5 rally-argentina Villa Carlos Paz \n", "6 rally-sweden Karlstad \n", "7 NaN Marmaris \n", "8 NaN Bastia \n", "9 adac-rallye-deutschland Trier \n", "10 NaN Sardegna \n", "11 adac-rallye-deutschland Trier \n", "12 rally-italia-sardegna Alghero \n", "13 NaN Bastia \n", "14 wales-rally-gb Deeside \n", "15 tour-de-corse-2016 Ajaccio \n", "16 rally-sweden Karlstad \n", "17 rallyracc-catalunya-costa-daurada Salou \n", "18 rally-argentina Villa Carlos Paz \n", "19 coates-hire-rally-australia Coffs Harbour \n", "20 rally-guanajuato-mexico Leon \n", "21 NaN Villa Carlos Paz \n", "22 NaN Salou \n", "23 lotos-rally-poland Mikolajki \n", "24 NaN Jyväskylä \n", "25 NaN Matosinhos \n", "26 NaN Matosinhos \n", "27 rally-italia-sardegna Alghero \n", "28 rallye-monte-carlo Gap and Monaco \n", "29 coates-hire-rally-australia Coffs Harbour \n", ".. ... ... \n", "50 NaN Coffs Harbous \n", "51 rally-guanajuato-mexico Leon \n", "52 neste-oil-rally-finland Jyväskyla \n", "53 NaN Coffs Harbour \n", "54 NaN Monte Carlo \n", "55 tour-de-corse-2017 Ajaccio \n", "56 NaN Deeside \n", "57 vodafone-rally-de-portugal Faro \n", "58 rally-guanajuato-mexico Leon \n", "59 rally-argentina Villa Carlos Paz \n", "60 pzm-rally-poland Mikolajki \n", "61 lotos-rally-poland Mikolajki \n", "62 NaN Monte-Carlo \n", "63 NaN Sweden \n", "64 kennards-hire-rally-australia Coffs Harbour \n", "65 vodafone-rally-de-portugal Porto \n", "66 rallyracc-rally-de-espana Salou \n", "67 vodafone-rally-de-portugal Matosinhos \n", "68 NaN Alghero \n", "69 NaN Marmaris \n", "70 NaN Concepcion \n", "71 rally-italia-sardegna Alghero \n", "72 neste-rally-finland Jyväskylä \n", "73 rally-sweden Karlstad \n", "74 rally-sweden Karlstad \n", "75 neste-oil-rally-finland Jyväskylä \n", "76 rallye-monte-carlo Gap and Monaco \n", "77 wales-rally-gb Deeside \n", "78 rally-argentina Villa Carlos Paz \n", "79 adac-rallye-deutschland Trier \n", "\n", "n info-categories ... sas-eventid sas-itineraryid sas-rallyid \\\n", "0 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "1 NaN ... 87 138 103 \n", "2 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "3 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "4 NaN ... 80 131 96 \n", "5 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "6 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "7 NaN ... 35 114 39 \n", "8 NaN ... 81 132 97 \n", "9 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "10 NaN ... 32 83 36 \n", "11 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "12 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "13 NaN ... 29 34 33 \n", "14 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "15 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "16 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "17 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "18 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "19 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "20 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "21 NaN ... 82 133 98 \n", "22 NaN ... 37 124 41 \n", "23 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "24 NaN ... 33 106 37 \n", "25 NaN ... 84 135 100 \n", "26 NaN ... 31 78 35 \n", "27 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "28 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "29 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", ".. ... ... ... ... ... \n", "50 NaN ... 91 142 107 \n", "51 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "52 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "53 NaN ... 38 125 42 \n", "54 NaN ... 26 31 30 \n", "55 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "56 NaN ... 36 117 40 \n", "57 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "58 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "59 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "60 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "61 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "62 NaN ... 78 144 94 \n", "63 NaN ... 27 32 31 \n", "64 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "65 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "66 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "67 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "68 NaN ... 85 136 101 \n", "69 NaN ... 88 139 104 \n", "70 NaN ... 83 134 99 \n", "71 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "72 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "73 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "74 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "75 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "76 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "77 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "78 WRC, WRC-2, WRC-3 ... NaN NaN NaN \n", "79 WRC, WRC-2, WRC-3, JWRC ... NaN NaN NaN \n", "\n", "n sas-trackingid sitid testid \\\n", "0 NaN 143 NaN \n", "1 2791 87 NaN \n", "2 NaN 151 NaN \n", "3 NaN 149 NaN \n", "4 2784 80 NaN \n", "5 NaN 224 NaN \n", "6 NaN 218 NaN \n", "7 2596 35 NaN \n", "8 2785 81 NaN \n", "9 NaN 91 NaN \n", "10 2593 32 NaN \n", "11 NaN 234 NaN \n", "12 NaN 82 NaN \n", "13 2590 29 NaN \n", "14 NaN 199 NaN \n", "15 NaN 195 NaN \n", "16 NaN 177 NaN \n", "17 NaN 197 NaN \n", "18 NaN 181 NaN \n", "19 NaN 147 NaN \n", "20 NaN 220 NaN \n", "21 2786 82 NaN \n", "22 2598 37 NaN \n", "23 NaN 187 NaN \n", "24 2594 33 NaN \n", "25 2788 84 NaN \n", "26 2592 31 NaN \n", "27 NaN 185 NaN \n", "28 NaN 67 NaN \n", "29 NaN 94 NaN \n", ".. ... ... ... \n", "50 2795 91 NaN \n", "51 NaN 179 NaN \n", "52 NaN 88 NaN \n", "53 2599 38 NaN \n", "54 2586 26 NaN \n", "55 NaN 222 NaN \n", "56 2597 36 NaN \n", "57 NaN 76 NaN \n", "58 NaN 73 NaN \n", "59 NaN 135 NaN \n", "60 NaN 230 NaN \n", "61 NaN 141 NaN \n", "62 2782 78 NaN \n", "63 2587 27 NaN \n", "64 NaN 240 NaN \n", "65 NaN 137 NaN \n", "66 NaN 100 NaN \n", "67 NaN 183 NaN \n", "68 2789 85 NaN \n", "69 2792 88 NaN \n", "70 2787 83 NaN \n", "71 NaN 228 NaN \n", "72 NaN 232 NaN \n", "73 NaN 70 NaN \n", "74 NaN 131 NaN \n", "75 NaN 189 NaN \n", "76 NaN 130 NaN \n", "77 NaN 103 1 \n", "78 NaN 79 NaN \n", "79 NaN 191 NaN \n", "\n", "n thumbnail time-zone \\\n", "0 NaN Helsinki \n", "1 NaN NaN \n", "2 NaN Madrid \n", "3 NaN Paris \n", "4 NaN NaN \n", "5 NaN Buenos Aires \n", "6 NaN Stockholm \n", "7 NaN NaN \n", "8 NaN NaN \n", "9 http://www.wrc.com/fileadmin/images/Calendar/2... Europe/Berlin \n", "10 NaN NaN \n", "11 NaN Berlin \n", "12 http://www.wrc.com/fileadmin/images/Calendar/2... Rome \n", "13 NaN NaN \n", "14 NaN London \n", "15 NaN Paris \n", "16 NaN Stockholm \n", "17 NaN Madrid \n", "18 NaN Buenos Aires \n", "19 NaN Brisbane \n", "20 NaN Mexico City \n", "21 NaN NaN \n", "22 NaN NaN \n", "23 NaN Warsaw \n", "24 NaN NaN \n", "25 NaN NaN \n", "26 NaN NaN \n", "27 NaN Rome \n", "28 NaN Europe/Paris \n", "29 http://www.wrc.com/fileadmin/images/Calendar/2... Australia/Brisbane \n", ".. ... ... \n", "50 NaN NaN \n", "51 NaN Mexico City \n", "52 http://www.wrc.com/fileadmin/images/Calendar/2... Europe/Helsinki \n", "53 NaN NaN \n", "54 NaN NaN \n", "55 NaN Paris \n", "56 NaN NaN \n", "57 http://www.wrc.com/fileadmin/images/Calendar/2... Lisbon \n", "58 http://www.wrc.com/fileadmin/images/Calendar/2... Guadalajara \n", "59 NaN Buenos Aires \n", "60 NaN Warsaw \n", "61 NaN Warsaw \n", "62 NaN NaN \n", "63 NaN NaN \n", "64 NaN New Caledonia \n", "65 NaN Lisbon \n", "66 http://www.wrc.com/fileadmin/images/Calendar/2... Europe/Madrid \n", "67 NaN Lisbon \n", "68 NaN NaN \n", "69 NaN NaN \n", "70 NaN NaN \n", "71 NaN Rome \n", "72 NaN Helsinki \n", "73 http://www.wrc.com/fileadmin/images/Calendar/2... Europe/Paris \n", "74 NaN Paris \n", "75 NaN Helsinki \n", "76 NaN Paris \n", "77 http://www.wrc.com/fileadmin/images/Calendar/2... Europe/London \n", "78 http://images.wrc.com/News/2637_wrc-argentina-... Buenos Aires \n", "79 NaN Berlin \n", "\n", "n tzoffset year \n", "0 NaN 2015 \n", "1 0 2019 \n", "2 NaN 2015 \n", "3 NaN 2015 \n", "4 -25200000 2019 \n", "5 -14400000 2017 \n", "6 NaN 2017 \n", "7 10800000 2018 \n", "8 0 2019 \n", "9 NaN 2014 \n", "10 3600000 2018 \n", "11 3600000 2017 \n", "12 NaN 2014 \n", "13 3600000 2018 \n", "14 NaN 2016 \n", "15 NaN 2016 \n", "16 NaN 2016 \n", "17 NaN 2016 \n", "18 -14400000 2016 \n", "19 NaN 2015 \n", "20 -25200000 2017 \n", "21 -14400000 2019 \n", "22 36000000 2018 \n", "23 NaN 2016 \n", "24 7200000 2018 \n", "25 -3600000 2019 \n", "26 0 2018 \n", "27 NaN 2016 \n", "28 NaN 2014 \n", "29 NaN 2014 \n", ".. ... ... \n", "50 36000000 2019 \n", "51 -21600000 2016 \n", "52 NaN 2014 \n", "53 36000000 2018 \n", "54 0 2018 \n", "55 3600000 2017 \n", "56 3600000 2018 \n", "57 NaN 2014 \n", "58 NaN 2014 \n", "59 NaN 2015 \n", "60 3600000 2017 \n", "61 NaN 2015 \n", "62 0 2019 \n", "63 0 2018 \n", "64 36000000 2017 \n", "65 NaN 2015 \n", "66 NaN 2014 \n", "67 0 2016 \n", "68 0 2019 \n", "69 7200000 2019 \n", "70 -14400000 2019 \n", "71 3600000 2017 \n", "72 7200000 2017 \n", "73 NaN 2014 \n", "74 NaN 2015 \n", "75 3600000 2016 \n", "76 NaN 2015 \n", "77 NaN 2014 \n", "78 NaN 2014 \n", "79 NaN 2016 \n", "\n", "[80 rows x 32 columns]" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "getEventMetadata()" ] }, { "cell_type": "code", "execution_count": 67, "metadata": {}, "outputs": [], "source": [ "#DEBUG\n", "#for example: OperationalError: table event_metadata has no column named hasfootage\n", "#" ] }, { "cell_type": "code", "execution_count": 186, "metadata": {}, "outputs": [], "source": [ "#itinerary_event" ] }, { "cell_type": "code", "execution_count": 187, "metadata": {}, "outputs": [], "source": [ "#itinerary_event" ] }, { "cell_type": "code", "execution_count": 74, "metadata": {}, "outputs": [], "source": [ "get_championship(name, dbname=dbname, year=year )" ] }, { "cell_type": "code", "execution_count": 82, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Getting base info...\n", "Getting penalties...\n", "Getting retirements...\n", "Getting stagewinners...\n", "Getting stage_overall...\n", "Getting split_times...\n", "Getting stage_times_stage...\n", "Getting stage_times_overall...\n" ] } ], "source": [ "#set_rallyId( name,year )\n", "get_all(name, dbname=dbname, year=year )" ] }, { "cell_type": "code", "execution_count": 99, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "trying turkey\n", "trying sardegna\n", "trying france\n", "trying spain\n", "trying finland\n", "trying portugal\n", "trying germany\n", "trying mexico\n", "trying argentina\n", "trying australia\n", "trying montecarlo\n", "trying uk\n", "trying sweden\n" ] } ], "source": [ "#full run:\n", "for name in listRallies2():\n", " print('trying {}'.format(name))\n", " get_all(name, dbname=dbname, year=year )" ] }, { "cell_type": "code", "execution_count": 81, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{'rallyId': '95', 'stages': [901, 902, 904, 905, 907, 909, 917, 910, 911, 914, 918, 908, 900, 916, 913, 912, 906, 903, 915], 'championshipId': None, 'rally_name': 'sweden', '_stages': {'SS1': {'stageId': 901, 'status': 'Completed'}, 'SS2': {'stageId': 902, 'status': 'Completed'}, 'SS3': {'stageId': 904, 'status': 'Running'}, 'SS4': {'stageId': 905, 'status': 'Running'}, 'SS5': {'stageId': 907, 'status': 'ToRun'}, 'SS6': {'stageId': 909, 'status': 'ToRun'}, 'SS7': {'stageId': 917, 'status': 'ToRun'}, 'SS8': {'stageId': 910, 'status': 'ToRun'}, 'SS9': {'stageId': 911, 'status': 'ToRun'}, 'SS10': {'stageId': 914, 'status': 'ToRun'}, 'SS11': {'stageId': 918, 'status': 'ToRun'}, 'SS12': {'stageId': 908, 'status': 'ToRun'}, 'SS13': {'stageId': 900, 'status': 'ToRun'}, 'SS14': {'stageId': 916, 'status': 'ToRun'}, 'SS15': {'stageId': 913, 'status': 'ToRun'}, 'SS16': {'stageId': 912, 'status': 'ToRun'}, 'SS17': {'stageId': 906, 'status': 'ToRun'}, 'SS18': {'stageId': 903, 'status': 'ToRun'}, 'SS19': {'stageId': 915, 'status': 'ToRun'}}}\n", "Getting penalties...\n", "Getting retirements...\n", "Getting stagewinners...\n", "Getting stage_overall...\n", "Getting split_times...\n", "Getting stage_times_stage...\n", "Getting stage_times_overall...\n" ] } ], "source": [ "#PK issues?\n", "#upsert issues? Pandas doesn't support upsert\n", "\n", "#Use Simon Willison's sqlite utils, which has upsert.\n", "get_one(name, 'SS4', dbname=dbname, year=year)" ] }, { "cell_type": "code", "execution_count": 203, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'rallyId': '94',\n", " 'stages': [],\n", " 'championshipId': None,\n", " 'rally_name': 'montecarlo'}" ] }, "execution_count": 203, "metadata": {}, "output_type": "execute_result" } ], "source": [ "meta = set_rallyId2(name, year)\n", "meta" ] }, { "cell_type": "code", "execution_count": 76, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
0itinerary_event
1itinerary_legs
2itinerary_sections
3itinerary_stages
4itinerary_controls
5startlists
6roster
7startlist_classes
8penalties
9retirements
10stagewinners
11stage_overall
12split_times
13stage_times_stage
14stage_times_overall
15championship_lookup
16championship_results
17championship_entries_codrivers
18championship_entries_manufacturers
19championship_rounds
20championship_events
21championship_entries_drivers
22event_metadata
\n", "
" ], "text/plain": [ " name\n", "0 itinerary_event\n", "1 itinerary_legs\n", "2 itinerary_sections\n", "3 itinerary_stages\n", "4 itinerary_controls\n", "5 startlists\n", "6 roster\n", "7 startlist_classes\n", "8 penalties\n", "9 retirements\n", "10 stagewinners\n", "11 stage_overall\n", "12 split_times\n", "13 stage_times_stage\n", "14 stage_times_overall\n", "15 championship_lookup\n", "16 championship_results\n", "17 championship_entries_codrivers\n", "18 championship_entries_manufacturers\n", "19 championship_rounds\n", "20 championship_events\n", "21 championship_entries_drivers\n", "22 event_metadata" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "conn = sqlite3.connect(dbname)\n", "\n", "q=\"SELECT name FROM sqlite_master WHERE type = 'table';\"\n", "pd.read_sql(q,conn)" ] }, { "cell_type": "code", "execution_count": 221, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['_id', 'availability', 'date-finish', 'date-start', 'gallery',\n", " 'hasdata' TEXT,\\n 'hasvideos', 'id', 'info-based', 'info-categories',\n", " 'info-date', 'info-flag', 'info-surface', 'info-website', 'kmlfile',\n", " 'logo', 'name', 'org-website', 'poi-Klo im Wald', 'poilistid',\n", " 'position', 'rosterid', 'sas-eventid', 'sas-itineraryid', 'sas-rallyid',\n", " 'sas-trackingid', 'sitid', 'testid', 'thumbnail', 'time-zone',\n", " 'tzoffset', 'year'],\n", " dtype='object')" ] }, "execution_count": 221, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q=\"SELECT * FROM event_metadata LIMIT 1;\"\n", "pd.read_sql(q,conn).columns" ] }, { "cell_type": "code", "execution_count": 357, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
categoriesclerkOfTheCoursecountry.countryIdcountry.iso2country.iso3country.namecountryIdeventIdfinishDatelocation...organiserUrlslugstartDatestewardssurfacestemplateFilenametimeZoneIdtimeZoneNametimeZoneOffsettrackingEventId
0NoneAlain Pallanca147MCMCOMonaco147782019-01-27Monte-Carlo...http://acm.mc/edition/rallye-monte-carlo-editi...87e-rallye-automobile-monte-carlo-20192019-01-24Waltraud Wünsch ...Tarmac & Iceresults-report-templates/e2a3be60-c4d4-4c97-96...Romance Standard TimeCentral European Time (+01:00)602782
\n", "

1 rows × 22 columns

\n", "
" ], "text/plain": [ " categories clerkOfTheCourse country.countryId country.iso2 country.iso3 \\\n", "0 None Alain Pallanca 147 MC MCO \n", "\n", " country.name countryId eventId finishDate location ... \\\n", "0 Monaco 147 78 2019-01-27 Monte-Carlo ... \n", "\n", " organiserUrl \\\n", "0 http://acm.mc/edition/rallye-monte-carlo-editi... \n", "\n", " slug startDate \\\n", "0 87e-rallye-automobile-monte-carlo-2019 2019-01-24 \n", "\n", " stewards surfaces \\\n", "0 Waltraud Wünsch ... Tarmac & Ice \n", "\n", " templateFilename timeZoneId \\\n", "0 results-report-templates/e2a3be60-c4d4-4c97-96... Romance Standard Time \n", "\n", " timeZoneName timeZoneOffset trackingEventId \n", "0 Central European Time (+01:00) 60 2782 \n", "\n", "[1 rows x 22 columns]" ] }, "execution_count": 357, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q=\"SELECT * FROM championship_events LIMIT 1;\"\n", "pd.read_sql(q,conn)" ] }, { "cell_type": "code", "execution_count": 358, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
championshipIdeventIdorder
024781
\n", "
" ], "text/plain": [ " championshipId eventId order\n", "0 24 78 1" ] }, "execution_count": 358, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q=\"SELECT * FROM championship_rounds LIMIT 1;\"\n", "pd.read_sql(q,conn)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#!rm wrc18_test1.db" ] }, { "cell_type": "code", "execution_count": 245, "metadata": {}, "outputs": [], "source": [ "#!pip3 install isodate\n", "import isodate\n", "pd.to_timedelta(isodate.parse_duration('PT1H5M26S')), isodate.parse_duration('PT0.1S')\n", "#ISO 8601 https://stackoverflow.com/questions/51168022/what-does-pt-prefix-stand-for-in-duration\n", "\n", "# TO DO - some function that timifies particular columns\n", "\n", "def _totime(s):\n", " if s and isinstance(s,str):\n", " if s.startswith('PT'):\n", " return pd.to_timedelta(isodate.parse_duration(s))\n", " else:\n", " #Should regex to check this?\n", " return pd.to_timedelta(s)\n", " \n", "def timeify(df):\n", " time_cols = ['diffFirst', 'diffPrev']\n", " for c in [c for c in df.columns if c in time_cols]:\n", " df[c] = df[c].apply(_totime)\n", " return df" ] }, { "cell_type": "code", "execution_count": 246, "metadata": {}, "outputs": [], "source": [ "#Data2Text - Stage Result notebook has time wrangling" ] }, { "cell_type": "code", "execution_count": 247, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "diffFirst timedelta64[ns]\n", "diffFirstMs int64\n", "diffPrev timedelta64[ns]\n", "diffPrevMs int64\n", "elapsedDuration object\n", "elapsedDurationMs int64\n", "entryId int64\n", "position int64\n", "source object\n", "stageId int64\n", "stageTimeId int64\n", "status object\n", "dtype: object" ] }, "execution_count": 247, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Why does this get cast to time but stage_times_overall doesn't?\n", "q=\"SELECT * FROM stage_times_stage LIMIT 10 ;\"\n", "timeify(pd.read_sql(q,conn)).dtypes\n", "\n", "#The diffFirst etc are not time objects - they;re strings; cast to timedelta? DOes SQLIte do timedelta?\n", "#The cast is easy in Python: pd.to_timedelta('00:04:45.5000000')" ] }, { "cell_type": "code", "execution_count": 223, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
diffFirstdiffFirstMsdiffPrevdiffPrevMsentryIdpenaltyTimepenaltyTimeMspositionstageTimestageTimeMstotalTimetotalTimeMsstageId
0PT0S0PT0S03444PT0S01PT13M2S782000PT13M2S782000881
1PT5S5000PT5S50003451PT0S02PT13M7S787000PT13M7S787000881
2PT10.6S10600PT5.6S56003442PT0S03PT13M12.6S792600PT13M12.6S792600881
3PT23.2S23200PT12.6S126003450PT0S04PT13M25.2S805200PT13M25.2S805200881
4PT26.8S26800PT3.6S36003443PT0S05PT13M28.8S808800PT13M28.8S808800881
5PT32.3S32300PT5.5S55003448PT0S06PT13M34.3S814300PT13M34.3S814300881
6PT32.7S32700PT0.4S4003446PT0S07PT13M34.7S814700PT13M34.7S814700881
7PT35.4S35400PT2.7S27003445PT0S08PT13M37.4S817400PT13M37.4S817400881
8PT37S37000PT1.6S16003447PT0S09PT13M39S819000PT13M39S819000881
9PT52.2S52200PT15.2S152003452PT0S010PT13M54.2S834200PT13M54.2S834200881
\n", "
" ], "text/plain": [ " diffFirst diffFirstMs diffPrev diffPrevMs entryId penaltyTime \\\n", "0 PT0S 0 PT0S 0 3444 PT0S \n", "1 PT5S 5000 PT5S 5000 3451 PT0S \n", "2 PT10.6S 10600 PT5.6S 5600 3442 PT0S \n", "3 PT23.2S 23200 PT12.6S 12600 3450 PT0S \n", "4 PT26.8S 26800 PT3.6S 3600 3443 PT0S \n", "5 PT32.3S 32300 PT5.5S 5500 3448 PT0S \n", "6 PT32.7S 32700 PT0.4S 400 3446 PT0S \n", "7 PT35.4S 35400 PT2.7S 2700 3445 PT0S \n", "8 PT37S 37000 PT1.6S 1600 3447 PT0S \n", "9 PT52.2S 52200 PT15.2S 15200 3452 PT0S \n", "\n", " penaltyTimeMs position stageTime stageTimeMs totalTime totalTimeMs \\\n", "0 0 1 PT13M2S 782000 PT13M2S 782000 \n", "1 0 2 PT13M7S 787000 PT13M7S 787000 \n", "2 0 3 PT13M12.6S 792600 PT13M12.6S 792600 \n", "3 0 4 PT13M25.2S 805200 PT13M25.2S 805200 \n", "4 0 5 PT13M28.8S 808800 PT13M28.8S 808800 \n", "5 0 6 PT13M34.3S 814300 PT13M34.3S 814300 \n", "6 0 7 PT13M34.7S 814700 PT13M34.7S 814700 \n", "7 0 8 PT13M37.4S 817400 PT13M37.4S 817400 \n", "8 0 9 PT13M39S 819000 PT13M39S 819000 \n", "9 0 10 PT13M54.2S 834200 PT13M54.2S 834200 \n", "\n", " stageId \n", "0 881 \n", "1 881 \n", "2 881 \n", "3 881 \n", "4 881 \n", "5 881 \n", "6 881 \n", "7 881 \n", "8 881 \n", "9 881 " ] }, "execution_count": 223, "metadata": {}, "output_type": "execute_result" } ], "source": [ "## Are stage_times_overall and stage_overall the same?\n", "\n", "\n", "#Need to parse these time things correctly...\n", "#Maybe parse 'PT10M40.4S' to '00:10:40.4000000', 'PT1H9M30.8S' to '00:01:09:30.800000'\n", "#0.1S ->00:00:00.100000, 1M0.1s->00:10:00.100000, 1H1M0.1S ->01:10:00.100000\n", "q=\"SELECT * FROM stage_times_overall ORDER BY totalTimeMs LIMIT 10 ;\"\n", "pd.read_sql(q,conn)" ] }, { "cell_type": "code", "execution_count": 214, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
diffFirstdiffFirstMsdiffPrevdiffPrevMsentryIdpenaltyTimepenaltyTimeMspositionstageTimestageTimeMstotalTimetotalTimeMsstageId
0PT0S0PT0S03444PT0S01PT13M2S782000PT13M2S782000881
1PT5S5000PT5S50003451PT0S02PT13M7S787000PT13M7S787000881
2PT10.6S10600PT5.6S56003442PT0S03PT13M12.6S792600PT13M12.6S792600881
3PT23.2S23200PT12.6S126003450PT0S04PT13M25.2S805200PT13M25.2S805200881
4PT26.8S26800PT3.6S36003443PT0S05PT13M28.8S808800PT13M28.8S808800881
5PT32.3S32300PT5.5S55003448PT0S06PT13M34.3S814300PT13M34.3S814300881
6PT32.7S32700PT0.4S4003446PT0S07PT13M34.7S814700PT13M34.7S814700881
7PT35.4S35400PT2.7S27003445PT0S08PT13M37.4S817400PT13M37.4S817400881
8PT37S37000PT1.6S16003447PT0S09PT13M39S819000PT13M39S819000881
9PT52.2S52200PT15.2S152003452PT0S010PT13M54.2S834200PT13M54.2S834200881
\n", "
" ], "text/plain": [ " diffFirst diffFirstMs diffPrev diffPrevMs entryId penaltyTime \\\n", "0 PT0S 0 PT0S 0 3444 PT0S \n", "1 PT5S 5000 PT5S 5000 3451 PT0S \n", "2 PT10.6S 10600 PT5.6S 5600 3442 PT0S \n", "3 PT23.2S 23200 PT12.6S 12600 3450 PT0S \n", "4 PT26.8S 26800 PT3.6S 3600 3443 PT0S \n", "5 PT32.3S 32300 PT5.5S 5500 3448 PT0S \n", "6 PT32.7S 32700 PT0.4S 400 3446 PT0S \n", "7 PT35.4S 35400 PT2.7S 2700 3445 PT0S \n", "8 PT37S 37000 PT1.6S 1600 3447 PT0S \n", "9 PT52.2S 52200 PT15.2S 15200 3452 PT0S \n", "\n", " penaltyTimeMs position stageTime stageTimeMs totalTime totalTimeMs \\\n", "0 0 1 PT13M2S 782000 PT13M2S 782000 \n", "1 0 2 PT13M7S 787000 PT13M7S 787000 \n", "2 0 3 PT13M12.6S 792600 PT13M12.6S 792600 \n", "3 0 4 PT13M25.2S 805200 PT13M25.2S 805200 \n", "4 0 5 PT13M28.8S 808800 PT13M28.8S 808800 \n", "5 0 6 PT13M34.3S 814300 PT13M34.3S 814300 \n", "6 0 7 PT13M34.7S 814700 PT13M34.7S 814700 \n", "7 0 8 PT13M37.4S 817400 PT13M37.4S 817400 \n", "8 0 9 PT13M39S 819000 PT13M39S 819000 \n", "9 0 10 PT13M54.2S 834200 PT13M54.2S 834200 \n", "\n", " stageId \n", "0 881 \n", "1 881 \n", "2 881 \n", "3 881 \n", "4 881 \n", "5 881 \n", "6 881 \n", "7 881 \n", "8 881 \n", "9 881 " ] }, "execution_count": 214, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q=\"SELECT * FROM stage_overall ORDER BY totalTimeMs LIMIT 10;\"\n", "pd.read_sql(q,conn)" ] }, { "cell_type": "code", "execution_count": 229, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
elapsedDurationelapsedDurationMsentryIdstageIdstageName
0NoneNaNNaN881LA BREOLE - SELONNET
1NoneNaNNaN885AVANÇON - NOTRE DAME DU LAUS
2NoneNaNNaN888CURBANS - PIEGUT 1
3NoneNaNNaN886ROUSSIEUX - LABOREL 1
4NoneNaNNaN894VALDROME - SIGOTTIER 1
5NoneNaNNaN892ROUSSIEUX - LABOREL 2
6NoneNaNNaN889VALDROME - SIGOTTIER 2
7NoneNaNNaN887CURBANS - PIEGUT 2
8NoneNaNNaN893ST LEGER LES MELEZES - LA BATIE NEUVE 1
9NoneNaNNaN895AGNIERES EN DEVOLUY - CORPS 1
10NoneNaNNaN890ST LEGER LES MELEZES - LA BATIE NEUVE 2
11NoneNaNNaN891AGNIERES EN DEVOLUY - CORPS 2
12NoneNaNNaN882LA BOLLENE VESUBIE - PEIRA CAVA 2
13NoneNaNNaN883LA CABANETTE - COL DE BRAUS 1
14NoneNaNNaN884LA BOLLENE VESUBIE - PEIRA CAVA 1
15NoneNaNNaN880LA CABANETTE - COL DE BRAUS 2 (Power Stage)
16NoneNaNNaN888CURBANS - PIEGUT 1
17NoneNaNNaN886ROUSSIEUX - LABOREL 1
18NoneNaNNaN894VALDROME - SIGOTTIER 1
19NoneNaNNaN892ROUSSIEUX - LABOREL 2
20NoneNaNNaN889VALDROME - SIGOTTIER 2
21NoneNaNNaN887CURBANS - PIEGUT 2
22NoneNaNNaN893ST LEGER LES MELEZES - LA BATIE NEUVE 1
23NoneNaNNaN895AGNIERES EN DEVOLUY - CORPS 1
24NoneNaNNaN890ST LEGER LES MELEZES - LA BATIE NEUVE 2
25NoneNaNNaN891AGNIERES EN DEVOLUY - CORPS 2
26NoneNaNNaN882LA BOLLENE VESUBIE - PEIRA CAVA 2
27NoneNaNNaN883LA CABANETTE - COL DE BRAUS 1
28NoneNaNNaN884LA BOLLENE VESUBIE - PEIRA CAVA 1
29NoneNaNNaN880LA CABANETTE - COL DE BRAUS 2 (Power Stage)
3000:13:02782000.03444.0881LA BREOLE - SELONNET
3100:13:18.5000000798500.03443.0885AVANÇON - NOTRE DAME DU LAUS
32NoneNaNNaN888CURBANS - PIEGUT 1
33NoneNaNNaN886ROUSSIEUX - LABOREL 1
34NoneNaNNaN894VALDROME - SIGOTTIER 1
35NoneNaNNaN892ROUSSIEUX - LABOREL 2
36NoneNaNNaN889VALDROME - SIGOTTIER 2
37NoneNaNNaN887CURBANS - PIEGUT 2
38NoneNaNNaN893ST LEGER LES MELEZES - LA BATIE NEUVE 1
39NoneNaNNaN895AGNIERES EN DEVOLUY - CORPS 1
40NoneNaNNaN890ST LEGER LES MELEZES - LA BATIE NEUVE 2
41NoneNaNNaN891AGNIERES EN DEVOLUY - CORPS 2
42NoneNaNNaN882LA BOLLENE VESUBIE - PEIRA CAVA 2
43NoneNaNNaN883LA CABANETTE - COL DE BRAUS 1
44NoneNaNNaN884LA BOLLENE VESUBIE - PEIRA CAVA 1
45NoneNaNNaN880LA CABANETTE - COL DE BRAUS 2 (Power Stage)
\n", "
" ], "text/plain": [ " elapsedDuration elapsedDurationMs entryId stageId \\\n", "0 None NaN NaN 881 \n", "1 None NaN NaN 885 \n", "2 None NaN NaN 888 \n", "3 None NaN NaN 886 \n", "4 None NaN NaN 894 \n", "5 None NaN NaN 892 \n", "6 None NaN NaN 889 \n", "7 None NaN NaN 887 \n", "8 None NaN NaN 893 \n", "9 None NaN NaN 895 \n", "10 None NaN NaN 890 \n", "11 None NaN NaN 891 \n", "12 None NaN NaN 882 \n", "13 None NaN NaN 883 \n", "14 None NaN NaN 884 \n", "15 None NaN NaN 880 \n", "16 None NaN NaN 888 \n", "17 None NaN NaN 886 \n", "18 None NaN NaN 894 \n", "19 None NaN NaN 892 \n", "20 None NaN NaN 889 \n", "21 None NaN NaN 887 \n", "22 None NaN NaN 893 \n", "23 None NaN NaN 895 \n", "24 None NaN NaN 890 \n", "25 None NaN NaN 891 \n", "26 None NaN NaN 882 \n", "27 None NaN NaN 883 \n", "28 None NaN NaN 884 \n", "29 None NaN NaN 880 \n", "30 00:13:02 782000.0 3444.0 881 \n", "31 00:13:18.5000000 798500.0 3443.0 885 \n", "32 None NaN NaN 888 \n", "33 None NaN NaN 886 \n", "34 None NaN NaN 894 \n", "35 None NaN NaN 892 \n", "36 None NaN NaN 889 \n", "37 None NaN NaN 887 \n", "38 None NaN NaN 893 \n", "39 None NaN NaN 895 \n", "40 None NaN NaN 890 \n", "41 None NaN NaN 891 \n", "42 None NaN NaN 882 \n", "43 None NaN NaN 883 \n", "44 None NaN NaN 884 \n", "45 None NaN NaN 880 \n", "\n", " stageName \n", "0 LA BREOLE - SELONNET \n", "1 AVANÇON - NOTRE DAME DU LAUS \n", "2 CURBANS - PIEGUT 1 \n", "3 ROUSSIEUX - LABOREL 1 \n", "4 VALDROME - SIGOTTIER 1 \n", "5 ROUSSIEUX - LABOREL 2 \n", "6 VALDROME - SIGOTTIER 2 \n", "7 CURBANS - PIEGUT 2 \n", "8 ST LEGER LES MELEZES - LA BATIE NEUVE 1 \n", "9 AGNIERES EN DEVOLUY - CORPS 1 \n", "10 ST LEGER LES MELEZES - LA BATIE NEUVE 2 \n", "11 AGNIERES EN DEVOLUY - CORPS 2 \n", "12 LA BOLLENE VESUBIE - PEIRA CAVA 2 \n", "13 LA CABANETTE - COL DE BRAUS 1 \n", "14 LA BOLLENE VESUBIE - PEIRA CAVA 1 \n", "15 LA CABANETTE - COL DE BRAUS 2 (Power Stage) \n", "16 CURBANS - PIEGUT 1 \n", "17 ROUSSIEUX - LABOREL 1 \n", "18 VALDROME - SIGOTTIER 1 \n", "19 ROUSSIEUX - LABOREL 2 \n", "20 VALDROME - SIGOTTIER 2 \n", "21 CURBANS - PIEGUT 2 \n", "22 ST LEGER LES MELEZES - LA BATIE NEUVE 1 \n", "23 AGNIERES EN DEVOLUY - CORPS 1 \n", "24 ST LEGER LES MELEZES - LA BATIE NEUVE 2 \n", "25 AGNIERES EN DEVOLUY - CORPS 2 \n", "26 LA BOLLENE VESUBIE - PEIRA CAVA 2 \n", "27 LA CABANETTE - COL DE BRAUS 1 \n", "28 LA BOLLENE VESUBIE - PEIRA CAVA 1 \n", "29 LA CABANETTE - COL DE BRAUS 2 (Power Stage) \n", "30 LA BREOLE - SELONNET \n", "31 AVANÇON - NOTRE DAME DU LAUS \n", "32 CURBANS - PIEGUT 1 \n", "33 ROUSSIEUX - LABOREL 1 \n", "34 VALDROME - SIGOTTIER 1 \n", "35 ROUSSIEUX - LABOREL 2 \n", "36 VALDROME - SIGOTTIER 2 \n", "37 CURBANS - PIEGUT 2 \n", "38 ST LEGER LES MELEZES - LA BATIE NEUVE 1 \n", "39 AGNIERES EN DEVOLUY - CORPS 1 \n", "40 ST LEGER LES MELEZES - LA BATIE NEUVE 2 \n", "41 AGNIERES EN DEVOLUY - CORPS 2 \n", "42 LA BOLLENE VESUBIE - PEIRA CAVA 2 \n", "43 LA CABANETTE - COL DE BRAUS 1 \n", "44 LA BOLLENE VESUBIE - PEIRA CAVA 1 \n", "45 LA CABANETTE - COL DE BRAUS 2 (Power Stage) " ] }, "execution_count": 229, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q=\"SELECT * FROM stagewinners;\"\n", "pd.read_sql(q,conn)\n" ] }, { "cell_type": "code", "execution_count": 227, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "-rw-r--r-- 1 ajh59 1182653967 36864 12 Feb 15:32 amagicdemo.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 638976 19 May 16:01 argentina18.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 0 10 Feb 18:32 database.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 1421312 31 Jul 14:08 finland18.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 974848 19 May 12:46 france18.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 0 8 Jun 08:30 italy.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 1024000 11 Jun 09:46 italy18.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 622592 19 May 12:45 mexico18.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 966656 19 May 12:44 monaco18.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 1290240 20 May 14:30 portugal18.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 0 3 Feb 20:55 rally.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 1236992 19 May 12:45 sweden18.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 2117632 5 Mar 21:02 wrc18.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 954368 5 Mar 15:33 wrc18_monaco.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 954368 5 Mar 15:31 wrc18_sweden.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 0 5 Mar 14:59 wrc18_swedenX.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 917504 10 Feb 15:39 wrc18_test1.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 925696 10 Feb 19:22 wrc18_test1keys.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 831488 11 Feb 01:01 wrc18_test1keysconstraints.db\r\n", "-rw-r--r-- 1 ajh59 1182653967 126976 11 Feb 01:13 wrc18_test1keysconstraintsX.db\r\n" ] } ], "source": [ "!ls -al *.db" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 133, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
0itinerary_event
1itinerary_legs
2itinerary_sections
3itinerary_stages
4itinerary_controls
5startlists
6startlist_classes
7penalties
8retirements
9stagewinners
10stage_overall
11split_times
12stage_times_stage
13stage_times_overall
14championship_lookup
15championship_results
16championship_entries_codrivers
17championship_entries_manufacturers
18championship_rounds
19championship_events
20championship_entries_drivers
\n", "
" ], "text/plain": [ " name\n", "0 itinerary_event\n", "1 itinerary_legs\n", "2 itinerary_sections\n", "3 itinerary_stages\n", "4 itinerary_controls\n", "5 startlists\n", "6 startlist_classes\n", "7 penalties\n", "8 retirements\n", "9 stagewinners\n", "10 stage_overall\n", "11 split_times\n", "12 stage_times_stage\n", "13 stage_times_overall\n", "14 championship_lookup\n", "15 championship_results\n", "16 championship_entries_codrivers\n", "17 championship_entries_manufacturers\n", "18 championship_rounds\n", "19 championship_events\n", "20 championship_entries_drivers" ] }, "execution_count": 133, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "conn = sqlite3.connect(dbname)\n", "c = conn.cursor()\n", "#c.executescript(setup_q)\n", "#c.executescript(setup_views_q)\n", "q=\"SELECT name FROM sqlite_master WHERE type = 'table';\"\n", "pd.read_sql(q,conn)" ] }, { "cell_type": "code", "execution_count": 134, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'codriver.abbvName': {0: 'J. INGRASSIA'},\n", " 'codriver.code': {0: 'ING'},\n", " 'codriver.country.countryId': {0: 76},\n", " 'codriver.country.iso2': {0: 'FR'},\n", " 'codriver.country.iso3': {0: 'FRA'},\n", " 'codriver.country.name': {0: 'France'},\n", " 'codriver.countryId': {0: 76},\n", " 'codriver.firstName': {0: 'Julien'},\n", " 'codriver.fullName': {0: 'Julien INGRASSIA'},\n", " 'codriver.lastName': {0: 'INGRASSIA'},\n", " 'codriver.personId': {0: 521},\n", " 'codriverId': {0: 521},\n", " 'driver.abbvName': {0: 'S. OGIER'},\n", " 'driver.code': {0: 'OGI'},\n", " 'driver.country.countryId': {0: 76},\n", " 'driver.country.iso2': {0: 'FR'},\n", " 'driver.country.iso3': {0: 'FRA'},\n", " 'driver.country.name': {0: 'France'},\n", " 'driver.countryId': {0: 76},\n", " 'driver.firstName': {0: 'Sébastien\\xa0'},\n", " 'driver.fullName': {0: 'Sébastien\\xa0 OGIER'},\n", " 'driver.lastName': {0: 'OGIER'},\n", " 'driver.personId': {0: 2453},\n", " 'driverId': {0: 2453},\n", " 'eligibility': {0: 'M'},\n", " 'entrant.entrantId': {0: 94},\n", " 'entrant.logoFilename': {0: ''},\n", " 'entrant.name': {0: 'M-SPORT FORD WORLD RALLY TEAM'},\n", " 'entrantId': {0: 94},\n", " 'entryId': {0: 2275},\n", " 'eventId': {0: 32},\n", " 'group.name': {0: 'WRC'},\n", " 'groupId': {0: 10},\n", " 'group.groupId': {0: 10},\n", " 'identifier': {0: '1'},\n", " 'manufacturer.logoFilename': {0: 'ford'},\n", " 'manufacturer.manufacturerId': {0: 26},\n", " 'manufacturer.name': {0: 'Ford'},\n", " 'manufacturerId': {0: 26},\n", " 'priority': {0: 'P1'},\n", " 'status': {0: 'Entry'},\n", " 'tag': {0: None},\n", " 'tag.name': {0: None},\n", " 'tag.tagId': {0: None},\n", " 'tagId': {0: None},\n", " 'tyreManufacturer': {0: 'Michelin'},\n", " 'vehicleModel': {0: 'FIESTA WRC'}}" ] }, "execution_count": 134, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q=\"SELECT * FROM startlists LIMIT 1;\"\n", "pd.read_sql(q,conn).to_dict()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.0" } }, "nbformat": 4, "nbformat_minor": 2 }