On driverStandings table there are also some duplicate entries for driverId 128, on races 300, 301, 302, 303 and 304.
The constructorStandings and driverStandings duplicates can be removed by the following two queries:
delete from constructorstandings where constructorstandingsid = 24518;
delete from driverstandings where driverstandingsid in (7118, 7149, 7180, 7211, 7243);
Finally, on the results table, there are 83 occasions where the same driverId has 2 or more results for the same raceId. This would mean the same driver participated on the same race for different teams. Did those drivers actually switch teams mid-race?
These entries can be found with this query:
select driverid, raceid, count(1) c from results group by driverid, raceid having c > 1;
“Finally, on the results table, there are 83 occasions where the same driverId has 2 or more results for the same raceId. This would mean the same driver participated on the same race for different teams. Did those drivers actually switch teams mid-race?”
Not necessarily different “teams”, just different entries, your query does not group by teams (i.e. constructors).
And yes, shared drives and car switches have long history in F1.
Hi, Chris, I’ve just checked. Nothing’s the problem with these.
77 of these are just shared drives/car switches.
The remaining 6 are:
– two entries for Harald Ertl in the 1978 Italian GP (he DNPQ’d an Ensign and DNQ’d an ETS)
– two entries for Keith Greene in the 1962 British GP (he was initially entered in car 50, but that didn’t arrive, so he practised in car 48 and DNS’d)
– two entries for Ron Flockhart in the 1958 Monaco GP (he tried and failed to qualify a Cooper and a BRM)
It’s just that a unique constraint on (raceId, driverId) is an invalid assumption on Eneko’s behalf as there are genuine multiple entries for the same driver in some races.
Thank you looking into it, emkael. Agreed, a unique index constraint for the races table does not apply, my bad. I updated the gist with the indices last night to reflect that change. The other indices should work well.
For the 2017 Season I tried querying http://ergast.com/api/f1/2017/results?limit=1000 and noticed that the displayed number (#2) for Stoffel Vandoorne was coming out at (#47) in one of the columns.
After poking around i noticed that the “attribute:number” is correctly showing #2 for him but his “driver.permanentnumber” is set to #47
Also, the grid #s shown for Australia-2017 seem to be outdated.
Daniel Ricciardo shows up #15 after he received a 5-grid penalty but during the formation lap his called stalled and he was forced to start from PitLane moving everyone below him up one spot and having him start from PitLane (i.e. 20).
Thanks, now corrected. Note that drivers don’t move up the grid (the vacated space is left unoccupied) and that I use ‘0’ to indicate that a driver started from the pit lane.
Ricciardo’s case is an odd one – I can’t recall any precedent of a driver “retiring” into the pit lane on formation lap, but not retiring from the race entirely.
So far, it’s been either a grid drop into the pit lane (due to penalties) – the grid was moved up because that was the new correct grid order, and driver(s) starting from the pit lane were at the back of the grid.
Or, it’s been a DNS (with either grid position “0” or driver’s initial grid position) if a car didn’t make to the grid.
All in all, not sure how it should be handled.
Anyway, speaking of grid position bugs – 1989 Spanish GP, Eddie Cheever (resultId = 8388) has a grid position of 61 (number of laps he covered in that race) – should be 22 🙂
Hi there!
F1 driver standing shows wrong order here http://ergast.com/api/f1/2017/2/driverStandings
vettel and hamilton have same points, same wins. But vettel goes first because he win first.
Really like the API, helping me creating a hobby project. Noticed a small error in the circuit name for the Russin circuit. It no longer is called “Sochi International Street Circuit” but it is now called “Sochi Autodrom”.
Hi Will,
I agree – this wasn’t quite right at the beginning and then it became hard to change things without breaking things for users.
I can’t remember the reason but it was probably due to the fact that the JSON format was derived from the XML, where attributes are strings.
There’s also the issue that some fields are usually numbers but there are exceptions e.g. a position is usually a number but can also be “R”, “D”, etc. If you use string fields in the database you have the flexibility to cope with this.
Chris
I think the 1987 Austrian Grand Prix has an error in it. It has “1987-06-18” as its date, but was on August 16, 1987. Seems like the numbers are a bit mixed up
Qualifying laptimes are malformatted in a lot more cases than already submitted. There are four different kinds of errors:
1. colon between seconds and milliseconds (qualifyId 988 1786 5323 5324 5325 5326 5327 5328 5329 5330 5331 5332 5333 5334 5335 5336 5337 5338 5339 5340 5341 5342 5343 5344 5345 6411 6413 6418 6447, note that some of them have this error in all q1-q2-q3 entries, others only once or twice)
2. period between minutes and seconds (5665 5669 5670)
3. other kinds of strange characters (499 1632)
4. empty string instead of NULL (too many to put here, 161 instances, but you should be able to find them).
The next issue is mapping race results to qualifying results via the keys raceId and driverId.
There are six instances where a qualifying result can’t be mapped to a race result. In detail:
resultId 5435: driverId should be 113 (Philippe Adams) instead of 70 (Zanardi)
qualifyId 4019 and 4038: driverId should be 47 (Baumgartner) instead of 42 (Pizzonia)
2011 Australia: Liuzzi and Karthikeyan DNQ, but it’s not shown in results. They didn’t race but normally you still include them in the results with the statusId of DNQ or 107% rule.
2011 Monaco: Perez DNS due to injury. This too is usually kept in the results with the correct statusId.
Furthermore, the database has complete qualifying information since 2003, but it sometimes misses instances when a driver did not qualify but was 1) still allowed to start the race, 2) didn’t start either but is recorded in the results database with a flag like DNS or Injury.
The list of post-2003 resultId-s that have no matching qualifyId are: 2014 2130 2145 20858 20880 21044 21195 21362 21423 21540 21611 22521 22525 22575 22615 22674 22831 23064 23135 23187 23220 23326. In these cases IMO the consistent way is putting NULL, NULL, NULL laptimes in the qualifying table regardless of whether the driver participated or had some issue.
BTW, I have scraped all qualifying information from formula1.com, which goes back to 1983. I will try to find time to get them into a format that could be plugged into the Ergast database. It will be plenty of work to not just map the driver and constructor IDs, but cross-check the contents against information that is already in the Ergast DB.
On driverStandings table there are also some duplicate entries for driverId 128, on races 300, 301, 302, 303 and 304.
The constructorStandings and driverStandings duplicates can be removed by the following two queries:
delete from constructorstandings where constructorstandingsid = 24518;
delete from driverstandings where driverstandingsid in (7118, 7149, 7180, 7211, 7243);
Finally, on the results table, there are 83 occasions where the same driverId has 2 or more results for the same raceId. This would mean the same driver participated on the same race for different teams. Did those drivers actually switch teams mid-race?
These entries can be found with this query:
select driverid, raceid, count(1) c from results group by driverid, raceid having c > 1;
Thanks!
“Finally, on the results table, there are 83 occasions where the same driverId has 2 or more results for the same raceId. This would mean the same driver participated on the same race for different teams. Did those drivers actually switch teams mid-race?”
Not necessarily different “teams”, just different entries, your query does not group by teams (i.e. constructors).
And yes, shared drives and car switches have long history in F1.
Thanks Eneko,
The standings errors have been corrected. I’ll work through the 83 results to find out what the problem is in a few days.
Chris
Hi, Chris, I’ve just checked. Nothing’s the problem with these.
77 of these are just shared drives/car switches.
The remaining 6 are:
– two entries for Harald Ertl in the 1978 Italian GP (he DNPQ’d an Ensign and DNQ’d an ETS)
– two entries for Keith Greene in the 1962 British GP (he was initially entered in car 50, but that didn’t arrive, so he practised in car 48 and DNS’d)
– two entries for Ron Flockhart in the 1958 Monaco GP (he tried and failed to qualify a Cooper and a BRM)
It’s just that a unique constraint on (raceId, driverId) is an invalid assumption on Eneko’s behalf as there are genuine multiple entries for the same driver in some races.
Thank you looking into it, emkael. Agreed, a unique index constraint for the races table does not apply, my bad. I updated the gist with the indices last night to reflect that change. The other indices should work well.
Regards,
Eneko
Thanks Emkael – hard to imagine all that car hopping these days!
Hi Chris,
Will you update the database image with latest races and drivers? 2017 races are not in the races table yet….
Thanks
Alan
Hi Alan,
Done – watch out for updates though, as new drivers are announced etc.
qualifying row with id 409 has strange characters in the q1 column:
1:36.827â€
qualifying rows with raceid 64 are incorrectly formatted:
id 998 = 1:15:329
id 988 = 1:14:862
id 990 = 1:14:934
Further to my previous qualifying formatting issues there are some that have the following formatting:
id 1007 = 1.20.080
Thanks Rob – all now corrected.
Jean Alesi has grid position 41 on 1998 Australian Grand Prix (result id 3666). The correct grid position is 12. (https://en.wikipedia.org/wiki/1998_Australian_Grand_Prix#Race)
The ’98 Australian GP has incorrect grid position for Jarno Trulli, too. He qualified 15th, Wikipedia lists grid position of 11 (the same as Wurz’s).
For the 2017 Season I tried querying http://ergast.com/api/f1/2017/results?limit=1000 and noticed that the displayed number (#2) for Stoffel Vandoorne was coming out at (#47) in one of the columns.
After poking around i noticed that the “attribute:number” is correctly showing #2 for him but his “driver.permanentnumber” is set to #47
Can you please fix this?
Also, the grid #s shown for Australia-2017 seem to be outdated.
Daniel Ricciardo shows up #15 after he received a 5-grid penalty but during the formation lap his called stalled and he was forced to start from PitLane moving everyone below him up one spot and having him start from PitLane (i.e. 20).
https://en.wikipedia.org/wiki/2017_Australian_Grand_Prix#Race
Eneko, Emkael,
Thanks – now fixed.
Hi Jorge,
Thanks, now corrected. Note that drivers don’t move up the grid (the vacated space is left unoccupied) and that I use ‘0’ to indicate that a driver started from the pit lane.
Thanks for the fixes.
Ricciardo’s case is an odd one – I can’t recall any precedent of a driver “retiring” into the pit lane on formation lap, but not retiring from the race entirely.
So far, it’s been either a grid drop into the pit lane (due to penalties) – the grid was moved up because that was the new correct grid order, and driver(s) starting from the pit lane were at the back of the grid.
Or, it’s been a DNS (with either grid position “0” or driver’s initial grid position) if a car didn’t make to the grid.
All in all, not sure how it should be handled.
Anyway, speaking of grid position bugs – 1989 Spanish GP, Eddie Cheever (resultId = 8388) has a grid position of 61 (number of laps he covered in that race) – should be 22 🙂
Will there be an update for the whole VES/VER acronym debacle, resulting in reporting Verstappen as “VER” from 2017 onward?
He’s currently still turning up as “code=VES” in the Aussie GP results.
Hi, max verstappen is named VER in 2017 ( .. it was VES in 2016 2015 ..)
thanks Koen
sperry, koen,
Thanks for the heads-up. Verstappen is now VER but this can’t be time-dependent due to technical limitations.
Hi, ok, why not just change it to VER so that it is correct now in stead of being correct in the past ?
Hi koen,
That is what I’ve done.
Chris
oki thx
Thanks a ton, Chris. Not just for the VES->VER update, but for hosting and maintaining this whole API!
Hi there!
F1 driver standing shows wrong order here
http://ergast.com/api/f1/2017/2/driverStandings
vettel and hamilton have same points, same wins. But vettel goes first because he win first.
Thanks Basil – now corrected. Looks like there’s a bug.
Hi Chris,
Really like the API, helping me creating a hobby project. Noticed a small error in the circuit name for the Russin circuit. It no longer is called “Sochi International Street Circuit” but it is now called “Sochi Autodrom”.
Regards,
Mark
Hi Mark,
Thanks for the heads-up – now updated.
Chris
Hi,
Why is all the numerical data formatted as strings?
For example: https://ergast.com/api/f1/2017.json
“round”: “1”,
could be
“round”: 1,
“season”: “2017” => “season”: 2017
“limit”: “30” => “limit”: 30
etc.
There is a lot of numeric data, JSON is able to represent numbers, and I would expect it to be numeric.
I’m sure you have reasons for outputting all strings, what are they though?
Cheers,
– Will
Hi Will,
I agree – this wasn’t quite right at the beginning and then it became hard to change things without breaking things for users.
I can’t remember the reason but it was probably due to the fact that the JSON format was derived from the XML, where attributes are strings.
There’s also the issue that some fields are usually numbers but there are exceptions e.g. a position is usually a number but can also be “R”, “D”, etc. If you use string fields in the database you have the flexibility to cope with this.
Chris
Hi! I’ve got a bug for you: if you query this http://ergast.com/api/f1/2017/drivers/ you will see Max Verstappen in the table however querying http://ergast.com/api/f1/2017/drivers/verstappen returns 0 results and querying http://ergast.com/api/f1/drivers/verstappen will return only Jos Verstappen, max’ dad.
🙂
Above also happens with palmer
Hi Ben,
Whenever a new driver has a surname used by a predecessor I add a qualifier e.g. the driver ID for Max Verstappen is “max_verstappen”.
You should hopefully get the correct results from:
http://ergast.com/api/f1/2017/drivers/max_verstappen
Hi,
I think the 1987 Austrian Grand Prix has an error in it. It has “1987-06-18” as its date, but was on August 16, 1987. Seems like the numbers are a bit mixed up
Thanks and keep up the great work!
Hi Bassti,
Many thanks for the heads-up – now corrected.
Chris
Hey, thanks for the great database.
Qualifying laptimes are malformatted in a lot more cases than already submitted. There are four different kinds of errors:
1. colon between seconds and milliseconds (qualifyId 988 1786 5323 5324 5325 5326 5327 5328 5329 5330 5331 5332 5333 5334 5335 5336 5337 5338 5339 5340 5341 5342 5343 5344 5345 6411 6413 6418 6447, note that some of them have this error in all q1-q2-q3 entries, others only once or twice)
2. period between minutes and seconds (5665 5669 5670)
3. other kinds of strange characters (499 1632)
4. empty string instead of NULL (too many to put here, 161 instances, but you should be able to find them).
The next issue is mapping race results to qualifying results via the keys raceId and driverId.
There are six instances where a qualifying result can’t be mapped to a race result. In detail:
resultId 5435: driverId should be 113 (Philippe Adams) instead of 70 (Zanardi)
qualifyId 4019 and 4038: driverId should be 47 (Baumgartner) instead of 42 (Pizzonia)
2011 Australia: Liuzzi and Karthikeyan DNQ, but it’s not shown in results. They didn’t race but normally you still include them in the results with the statusId of DNQ or 107% rule.
2011 Monaco: Perez DNS due to injury. This too is usually kept in the results with the correct statusId.
Furthermore, the database has complete qualifying information since 2003, but it sometimes misses instances when a driver did not qualify but was 1) still allowed to start the race, 2) didn’t start either but is recorded in the results database with a flag like DNS or Injury.
The list of post-2003 resultId-s that have no matching qualifyId are: 2014 2130 2145 20858 20880 21044 21195 21362 21423 21540 21611 22521 22525 22575 22615 22674 22831 23064 23135 23187 23220 23326. In these cases IMO the consistent way is putting NULL, NULL, NULL laptimes in the qualifying table regardless of whether the driver participated or had some issue.
BTW, I have scraped all qualifying information from formula1.com, which goes back to 1983. I will try to find time to get them into a format that could be plugged into the Ergast database. It will be plenty of work to not just map the driver and constructor IDs, but cross-check the contents against information that is already in the Ergast DB.
Patrese was born in 1954-04-17 and not 1957-04-17.
There are way more instances of incorrect birth dates. I’ve got them all in a single SQL file, which tracks any corrections that I found and reported here, but weren’t fixed:
https://github.com/emkael/ergast-goodies/blob/master/import/10_fixes.sql
Thanks Paipa – Patrese corrected. I’m working on the longer list.
Thanks emkael – this must have been a lot of work.
I’ve run these updates and re-published the database image.