|
calculated stats based on FINAL results
|
| TankNL |
Posted on 23-07-2014 17:54
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
Hi all,
Next week my holiday will start and I’ve started a project to keep myself busy 
No, I will not create a database myself, but I will generate a couple of excel files that will help future database builders to get the statics of the riders more accurate.
That is the goal, but we’ll have to see if it is feasible or not.
This project will be open source, so all the source data I have will be shared. Only the excel macro have been deleted for the reason that I don’t want to create a DDoS attack on the website.
What I have thought of is grab all the results of all WorldTour, all 2.HC, 2.1 and all 1.HC and 1.1 races and score the results. In order to do that, all the races have to be labelled, if it is a Sprint an uphill Sprint, Mountain or Mountain finish even. By doing that I should be able to distribute the points that are given based on the category of the race to the according riders stats.
So in the end, I’m hoping to create riders stat’s, based on actual results.
To show you that it is not just an idea, I will include all the results of 2013 and a first step-up of the pivot to create the stats.
https://dl.dropboxusercontent.com/u/4...pivot.xlsx
If some of you “database and statistic freaks” would join me in this quest and give me feedback or tips on the things you need or would do with it… I would be much more motivated. Feel free to discuss and share thoughts here.
update 17-08-2014
version 4 of my stats created on actual results; https://dl.dropbo...%20v4.xlsx
and a .cdb based on the ASOdb2014; https://dl.dropbo...lStats.cdb
update 23-09-2014
I've created a database based on PCMDaily 1.2 with version 7 of my stats algorithm based on actual results. Here is the .cdb; https://dl.dropbo...sRawV7.cdb
update 28-03-2015
I've updated the big file with all the races up until now. In total 2135 results are taken into account to calculate the stats of all the riders.
https://dl.dropbo...%20v1.xlsx
There is a sheet called; calStats with the raw calculated stats. I've modified a couple before I loaded them into the database (sheet dbStats) and the alterations are shown in yellow.
I've edited the 1.75 database of Jesleyh, just to see how it would run. I think the results are very accurate. Will do some more testing.
For those who are interested;
https://dl.dropbo...cStats.cdb
update 10-05-2015
Stats created based on 2.422 results resulting in calculated stats for 5.024 cyclist of which some have gone into retirement, but still a lot of useful stats..
Here is the new excel file; https://dl.dropboxusercontent.com/u/4...%20v2.xlsx
For those who are interested; my .cdb file based of JesGD2015 version 1.75 db (all credits to him) and filled with my calculated stats; https://dl.dropboxusercontent.com/u/4...erion2.cdb
update 13-05-2015
new Excel file uploaded;
- 3146 results captured and categorized
- all World Tour results of 2011
- all WT, .HC and .1 results of 2012 and 2013
- all WT, .HC, .1, .2, .2U and .NE results of 2014 until now
- created stats for 5575 cyclist based on those results
link; https://dl.dropboxusercontent.com/u/4...%20v2.xlsx
- personal note; only 9 cyclist changed according to personal preference when uploaded into a database, rest is calculated by the algorithm's
Tank
Edited by TankNL on 16-05-2015 10:34
|
| |
|
|
| Lachi |
Posted on 23-07-2014 18:09
|
Grand Tour Champion
Posts: 8516
Joined: 29-06-2007
PCM$: 200.00
|
Good luck with your project.
You can ask me if you have questions regarding excel statistics, formulas or programming. |
| |
|
|
| AireeZZ |
Posted on 23-07-2014 18:12
|
Amateur
Posts: 13
Joined: 21-07-2014
PCM$: 200.00
|
Sounds good, Will be following your progress  |
| |
|
|
| TankNL |
Posted on 23-07-2014 19:42
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
Next up is 2012 and after that 2014... because the TDF will be over by then. That should give me more than enough results to get the statistics going. |
| |
|
|
| Adamb |
Posted on 23-07-2014 20:00
|
Domestique
Posts: 642
Joined: 22-08-2013
PCM$: 200.00
|
Loving the project. |
| |
|
|
| madzdaman |
Posted on 23-07-2014 20:02
|
Stagiare
Posts: 194
Joined: 26-07-2011
PCM$: 200.00
|
Great idea,but what about domestiquesetc, who dont really have any results but just lead the peleton(or not) for ages? |
| |
|
|
| Jesleyh |
Posted on 23-07-2014 20:22
|
Tour de France Champion
Posts: 15274
Joined: 21-07-2012
PCM$: 200.00
|
That is a very interesting idea.
Mainly for CT teams, this would be amazing. So it'd be great if somehow you find a way to include .2 races.
Feyenoord(football) and Kelderman fanboy
PCMdaily Awards: 12x nomination, 9x runner-up, 0x win.
|
| |
|
|
| TankNL |
Posted on 23-07-2014 20:41
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
Thanks for the support and discussion. That will help me motivated.
@madzdaman; yes that could potentially become a problem, but I would recon that a domestic also has some races where he can go for his own results... but will have to see what the statistics will have to say about that 
@Jesleyh; have thought about that, but the hardest part is the labeling of the race. For some 2.1 or 2.2 races it is even hard to find the route or stage-profile. If I could count on the support of the community to help me do the labeling, than I will definitely consider it an option.
the webgrabbing is not the problem. As I'm typing, my computer is processing 3000 race results of 2012, after that I will have to label all the results, thats the most time consuming part. |
| |
|
|
| AaB-ern |
Posted on 23-07-2014 22:52
|
Directeur Sportif
Posts: 1489
Joined: 29-11-2006
PCM$: 200.00
|
This sounds like one of the best projects ever seen in the PCM community. I love the idea.
|
| |
|
|
| TankNL |
Posted on 24-07-2014 08:06
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
for 2013 I have labelled these races as cobble races. Are there more out there (category .1 or above)?
| date | race name | country | cat | race_type | | 23-2-2013 | Omloop Het Nieuwsblad (199 km) | Bel | 1.HC | CobbleHills | | 24-2-2013 | Kuurne - Brussel - Kuurne (193 km) | Bel | 1.1 | cancelled | | 2-3-2013 | Strade Bianche (188 km) | Ita | 1.1 | CobbleHills | | 9-3-2013 | Ronde van Drenthe (195,5 km) | Ned | 1.1 | CobbleSprint | | 13-3-2013 | Nokere Koerse (196,4 km) | Bel | 1.1 | cancelled | | 15-3-2013 | Handzame Classic (196 km) | Bel | 1.1 | CobbleSprint | | 20-3-2013 | Dwars door Vlaanderen (199 km) | Bel | 1.HC | CobbleHills | | 26-3-2013 | Driedaagse De Panne-Koksijde, Stage 1 : Middelkerke - Zottegem (199,8 km) | Bel | 2.HCs | CobbleHills | | 22-3-2013 | E3 Prijs Vlaanderen - Harelbeke (211 km) | Bel | 1.WT2 | CobbleHills | | 24-3-2013 | Gent - Wevelgem (185 km) | Bel | 1.WT2 | CobbleSprint | | 31-3-2013 | Ronde van Vlaanderen (256,2 km) | Bel | 1.WT1 | CobbleHills | | 7-4-2013 | Paris - Roubaix (254 km) | Fra | 1.WT1 | Cobbles | | 18-8-2013 | Eneco Tour of Benelux, Stage 7 : Tienen - Geraardsbergen (206,8 km) | Bel | 2.WT2s | CobbleHills |
|
| |
|
|
| Ad Bot |
Posted on 21-11-2024 20:07
|
Bot Agent
Posts: Countless
Joined: 23.11.09
|
|
| IP: None |
|
|
| MARSUPILAMI |
Posted on 24-07-2014 08:26
|
Team Leader
Posts: 5597
Joined: 10-08-2013
PCM$: 300.00
|
I don´t think so
|
| |
|
|
| canojuancho |
Posted on 24-07-2014 12:23
|
Breakaway Specialist
Posts: 917
Joined: 21-07-2008
PCM$: 600.00
|
strade bianche as cobbled race? the race has a kind of soil or sand but not cobblestones.
Le samyn i think has shorts cobblestones sections.
And maybe De Brabantse Pijl has too. |
| |
|
|
| TankNL |
Posted on 24-07-2014 21:58
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
canojuancho wrote:
strade bianche as cobbled race? the race has a kind of soil or sand but not cobblestones.
Le samyn i think has shorts cobblestones sections.
And maybe De Brabantse Pijl has too.
Strade bianche, because of the dirt roads indeed. Previous winners or top finishers include Ballan, Cancellara and Nibali so thought to give the points towards cobbles and hills. |
| |
|
|
| TankNL |
Posted on 01-08-2014 09:43
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
ok, small update;
I'm still working on 2012, halfway done. Will be done in the weekend or beginning next week. Will then also do 2014, but that will go faster, because the most time is consumed by looking up the profile for the old races.
I will have then all the race results of 2012, 2013 and all 2014 up until Augusts.
Here are my thoughts on calculating the stats for PCM;
Flat = ? could be a percentage of the sprint stages?
Mountains = all the assigned points in that cat (normalized to PCM scale)
Hills = all the assigned points in that cat (normalized to PCM scale)
TimeTrail = all the assigned points in that cat (normalized to PCM scale). TeamTimeTrails are excluded
Prologue = all the assigned points in that cat (normalized to PCM scale)
Cobble = all the assigned points in that cat (normalized to PCM scale)
Sprint = all the assigned points in that cat (normalized to PCM scale)
Acceleration = ?
Downhill = ?
Fighter = ?
Stamina = ? could be; points that are collected in races longer than 200km and multiplied by the percentile difference of the race compared to 200km.
Resistance = ? could be; same as stamina?
Recuperation = ? could be; points scored in classifications (GC's, MC's PC's of stage races)?
Any thoughts on this would be helpful.
hoping that with you input i can post some stats next week.
Edited by TankNL on 01-08-2014 09:52
|
| |
|
|
| admirschleck |
Posted on 01-08-2014 09:49
|
Team Leader
Posts: 6690
Joined: 11-10-2010
PCM$: 200.00
|
Wow, such a great project this is. It's probably a bit too late, but nevertheless - good luck!
Edited by admirschleck on 01-08-2014 09:49
|
| |
|
|
| The Hobbit |
Posted on 01-08-2014 10:04
|
Small Tour Specialist
Posts: 2730
Joined: 18-08-2013
PCM$: 200.00
|
This looks great, I thought of this myself, but I never had the drive to make it a thing, I think you will do very well, this could be pretty helpful in the future. |
| |
|
|
| TankNL |
Posted on 01-08-2014 11:04
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
Thanks guys.
@admirschleck; it is never too late to discuss and help to think of the possibilities. would be great to have a couple of you share your thoughts on how you would calculate the stats. |
| |
|
|
| TankNL |
Posted on 01-08-2014 21:17
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
Hi,
I could really use some help. I'm almost done with 2012, but the Chinese races are a drama. Can't get good info on the stage profiles. Maybe, some of you have better luck.
| 7-9-2012 | Tour of China I, Stage 1 : Xi'an - Xi'an T.T.T. (19,8 km) | Chn | 2.1s | TeamTimeTrail | | 8-9-2012 | Tour of China I, Stage 2 : Xi'an - Xi'an (100 km) | Chn | 2.1s | Sprint | | 9-9-2012 | Tour of China I, Stage 3 : Xi'an - Shangluo (125,6 km) | Chn | 2.1s | | | 11-9-2012 | Tour of China I, Stage 4 : Xiangyang - Xiangyang (102,4 km) | Chn | 2.1s | | | 12-9-2012 | Tour of China I, Stage 5 : Zaoyang - Zaoyang (115 km) | Chn | 2.1s | | | 13-9-2012 | Tour of China I, Stage 6 : Wuhan - Wuhan (90,4 km) | Chn | 2.1s | | | 16-9-2012 | Tour of China II, Prologue : Wuhan I.T.T. (6,2 km) | Chn | 2.1s | Prologue | | 18-9-2012 | Tour of China II, Stage 1 : Huainan - Huainan (121,6 km) | Chn | 2.1s | | | 20-9-2012 | Tour of China II, Stage 2 : Jining - Jining (146 km) | Chn | 2.1s | | | 21-9-2012 | Tour of China II, Stage 3 : Dezhou - Dezhou (112 km) | Chn | 2.1s | | | 22-9-2012 | Tour of China II, Stage 4 : Tianjin I.T.T. (18,2 km) | Chn | 2.1s | | | 23-9-2012 | Tour of China II, Stage 5 : Tianjin - Tianjin (90 km) | Chn | 2.1s | |
help is much appreciated.
it can be 6 kind of races in my mind;
Hills
finishOnHill
Mountains
finishOnMountain
Sprint
UphillSprint
Edited by TankNL on 01-08-2014 21:18
|
| |
|
|
| TankNL |
Posted on 01-08-2014 21:20
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
links to the website of the 2012 races with the profiles will of course do, that way i can finish the categorization of the 2012 races |
| |
|
|
| TankNL |
Posted on 01-08-2014 21:36
|
Domestique
Posts: 440
Joined: 19-03-2007
PCM$: 200.00
|
actually thinking of skipping all the races in China
Tour of China I = 2.1
Tour of China II = 2.1
Tour of Hainan = 2.HC !!!!
Tour of Taihu Lake = 2.1
and still i can't find a decent site with the stage profiles on them. The own race sites only list very old races or the 2014 race. If you guy's are not able to find them, I will ignore the results of those races. |
| |
|
