Results 6K run Dehesa de la Villa 2019, Men Veteran B

#64 Results 6K run/cross Dehesa de la Villa 2019, category Men Veteran B

Last week (15/12/2019) I participated in the running race "XXXVII (37th edition) Cross de Invierno Ciudad de los Poetas - Memorial Javier Martínez Morales", a 6 km race in the beautiful park Dehesa de la Villa in Madrid, organised by the local running club Agrupación Deportiva Ciudad de los Poetas (A.D. Ciudad de los Poetas).

For map-details, see: https://runedia.mundodeportivo.com/carrera/cross-de-invierno-ciudad-de-los-poetas-2019/20193350/

For the analysis of the results with Excel, I used the same method as I did last year, see for detailed description:
https://worktimesheet2014.blogspot.com/2018/12/data-analysis-for-finishing-times-of.html
NB: for download-details of the Excel, see end of blog. I also uploaded it to Excel Online (on Microsoft Onedrive) so you can also look into it even if you don't have Excel installed.

Besides Excel, I used this time also Flourish for data-vizualization, see e.g. fig.1 for an example of a chart made with this tool. The Vega-Lite charts that Flourish uses have some nice features, e.g:
- online editor Flourish Studio, to which you can upload an Excel with your data
- charts are described in JSON (very compact), see fig.2a-b
-  animation-effect when a chart is loaded
- you can create a 'story' to group charts which can be embedded in a web-page and 'played' (aka Powerpoint) , as the one I made and you can see below fig.1.

fig.1:  Flourish / Vega Lite Boxplot-chart finish-times byclub

..

My Flourish story (click on below arrow to go through 4 charts of this story):

The Flourish/Vega Lite charts are also responsive, so they adapt to the resolution of the device (laptop, smartphone, tablet), see fig.2c


fig.2a: Flourish Studio

https://app.flourish.studio/visualisation/1132053

fig.2b: Flourish Studio

fig.2c: Flourish responsive charts

I also used Excel to analyze the data and my finish-time (30:42 min., so 1842 sec, rank #83) using the Data Analysis menu with options "Descriptive Statistics' and "Rank and Percentile", see fig.3-4 for the result. And in Excel 2016 it is also quite easy to make Histogram- and Boxplot charts, see fig.5-6.


fig.3: Excel 'Data Analysis'-menu,  option "Descriptive Statistics'

fig.4: Exel 'Data Analysis'- menu,  option "Rank and Percentile"

fig.5: Excel Histogram-chart Finish-times (in sec)

fig.6: Excel Boxplot-chart Finish-times (in sec)

And to conclude I wanted to to thank AD Ciudad de los Poetas and all its volunteers for organizing this race, and also for giving all runners a nice memory of the race with all the photos they made and uploaded to Flickr, like this one of me, in white shirt, which I got with another race , of hospital Nino Jesus, and which I also made an analysis for using Power BI as dataviz.tool, see:
http://worktimesheet2014.blogspot.com/2017/11/statistics-result-10-km-run-corre-por.html
What I liked about Flourish is that it is 100%cloud and there is a free edition. When I looked for the opinion of others on Flourish, I found this interesting article in which various dataviz-tools are demonstrated, among which Flouris (and Power BI, Excel and more):

http://www.storytellingwithdata.com/blog/2019/1/24/new-year-new-tools




source: 
https://www.flickr.com/photos/adcpoetas/49241485423/in/album-72157712270066027/


Downloads:

#Mirror 1: Google Drive

https://cutt.ly/freIKwU


#Mirror 2: Microsoft OneDrive

https://cutt.ly/oreI1cX

--

Data analysis for finishing times of race Cross de invierno Ciudad de los Poetas 2018

#63 Data analysis for finishing times of race "Cross de invierno Ciudad de los Poetas 2018"

Last week (16/12/2018) I participated in the running race "XXXVI Cross de Invierno Ciudad de los Poetas - Memorial Javier Martínez Morales", a 6 km race (3 rounds o 2 km with some hills, for map, see: https://es.wikiloc.com/rutas-carrera/cross-invierno-dehesa-de-la-villa-8295067) in my neighourhood in Madrid' (Ciudad de los Poetas (Saconia)), in the beautiful park Dehesa de la Villa in Madrid, organised by the local running club Agrupación Deportiva Ciudad de los Poetas (A.D. Ciudad de los Poetas). The nice thing of this race, apart from its environment, is that there are races for all ages, from young to old (competing in 10 categories), so that the whole family can participate, which we also did. And besides, the race is also free, and perfectly organised, so I can really recommend it.

A.D. Ciudad de los Poetas also posted many nice photos of the race on their Flickr-space:

https://www.flickr.com/photos/adcpoetas/albums/with/72157676892042028

My favourites:

https://www.flickr.com/photos/adcpoetas/45684714234/in/album-72157676892042028/

https://www.flickr.com/photos/adcpoetas/46357585812/in/album-72157698961333180/

https://www.flickr.com/photos/adcpoetas/32536418498/in/album-72157698961333180/

https://www.flickr.com/photos/adcpoetas/32536419768/in/album-72157698961333180/

https://www.flickr.com/photos/adcpoetas/46408301381/in/album-72157698961333180/

https://www.flickr.com/photos/adcpoetas/46357585492/in/album-72157698961333180/

And for a nice video (of an earlier edition of the race):

https://www.youtube.com/watch?v=ejJhOhsDHTU&feature=youtu.be

In my race participated 108 runners, some were member of a running club, or of a school,besides 'independent' runners as me (total: #42, see worksheet "Pivot1" of the attached Excel). 

To analyse the finishing times, I downloaded the PDF-file from: 

https://drive.google.com/drive/folders/11VM3Hl3s9GgklhF7ZLNk5-bZq_MNclYj

and converted this to Excel with this (free) tool: 

https://www.pdftoexcel.com/

I added a column "TimeSecTotal" so that all finish times are converted to seconds (using simple string-functions, e.g. MID and RIGHT).

And also column "RankInRunnersClub", that indicates a ranking in a sub-competition (e.g. my (overall-)ranking was 75 (of 109) (69th percentile), but in my sub-competition ('Independent runners"), my ranking was 24 (of 42) (57th percentile)). For this sub-ranking ranking in a group), I used the function SUMPRODUCT, see e.g.:

https://www.extendoffice.com/documents/excel/4319-excel-rank-by-group.html

In my previous blog-posts about races in which I participated, you can see how you can make a boxplot and histogram for the finishing times in Excel, see e.g.: 

http://worktimesheet2014.blogspot.com/2015/11/statistics-result-10-km-run-corre-por.html

but this time I wanted  to see if there were some free tools with which this can be done, and there are.

To create the histogram (see fig.1), I used:

https://www.socscistatistics.com/descriptive/histograms/Default.aspx

NB: For other races about which I blogged I could check my histogram with that at the site of Runedia, see e.g.: 

https://runedia.mundodeportivo.com/en/race/carrera-de-las-empresas-10k-actualidad-economica-2014/201419683/

but for this race, they didn't publish it, but maybe later, here:

https://runedia.mundodeportivo.com/en/race/cross-de-invierno-ciudad-de-los-poetas-2018/20183350/.

And to create the boxplot (see fig.2), I used: 

http://www.alcula.com/calculators/statistics/box-plot/

Note that the boxplot also plotted an outlier (so this means that the 'upper-whisker' is not the slowest finishing time (2542 sec.), but the penultimate slowest time (2429 sec.)).

NB: I also found this stats tool:

https://plot.ly 

with which you can create all kinds of charts, but the box-plot didn't show the outliers.

To conclude, an interesting read (article with histogram and analysis):

https://www.runnersworld.com/runners-stories/a20851895/round-number-times-for-marathons/

fig.1: Histogram finishing times category Men Vet.B

fig.2: Boxplot v1 (with outlier) of finishing times category Men Vet.B

fig.3: Boxplot v2 of finishing times category Men Vet.B

fig.3: Boxplot v2 (detail) of finishing times category Men Vet.B

Downloads:

#Mirror 1: Google Drive

https://goo.gl/mkxxAT

Statistics result 10-km run Carrera de las Empresas Madrid 2014

#48 Statistics result 10-km run Carrera de las Empresas Madrid 2014

Note 21-12-2016:
For stats (made with R and Power BI) of last edition of this race, see this post:
http://worktimesheet2014.blogspot.com.es/2016/12/carrera-de-la-empresas-2016-madrid.html


Yesterday, so Sun. 13 Dec. 2015, the 10K run "Carrera de las Empresas" was held here in Madrid.
In this 'business run', companies (teams of collegues) compete in 18 categories (dimensions: number of runners in team (2,3,4), gender team-members (men, women, mix) and distance (6, 10 km)). I also wanted to participate with my company, Ilunion (coparate group ONCE), like I did last year, but unfortunately this time I could´t because I was injured. For the 2014 edition of this run I made an Excel with the results but I never published it because of time problems, but I thought to finish it last sunday, so although I did´t participate, my mind was with the race..

In the 2014-edition there were in total about 9000 runners (about 300 teams) from about 800 companies, For more info about the run, see:

http://www.carreradelasempresas.com/

http://www.expansion.com/2014/12/14/entorno/1418574269.html

and for some photos:

http://www.expansion.com/albumes/2014/12/15/xvi_carrera_de_las_empresas/index.html

and for some statistics:

http://www.runedia.com/cursa/201419683/carrera-de-las-empresas-10k-actualidad-economica/2014/

In fig.1 you can see the result, and, as always, in the end of this post you can find the
 download-URL´s of the Excel(s).

fig.1 statistics result (finish times) 10K run

So my Excel only has statistics of the 10K run, which (netto/chip-) finish-times I got from this PDF:

http://estaticos.expansion.com/opinion/documentosWeb/2014/12/23/ABSOLUTA%2010K.pdf

As you can see in this PDF, for each runner his team is normally specified as:
Name company +´'-' + number or letter, e.g. my team was named Ílunion-26.
I wanted to create also statistics about the companies (number of runners per company), so first I transformed the data of this PDF to get the company of a runner, so for my row in this Excel the transformation was: team  Ílunion-26 -> company  Ílunion. Then I calculated in the Power Pivot datamodel, with a DAX-formula 'distinct count' the number of companies (687) derived from the field team (total: 1848), see fig.2.

fig.2: calculating data 'Company' (EquipoDef) from field 'Team' (Equipo).


And then I created a statistic 'total runners per company', see fig.3 for top 25 companies
 (with most runners). NB: I don´t know if 'TR' is a company-name or maybe a dummy-value.

fig.3: total runners per company


I wondered if there was a correlation between the number of runners of a company and the best 'total-times' of a team (of 2,3 or 4 members).  I used the category '10K - 2 (male) runners per team' to test this, see fig.4 for the result, which shows the correlation is weak (-0.15, so far for the max (negative) correlation of -1). And in the graph (scatter-plot) you can see that companies with about 30 runners or more always have a total-time (sum of time of runner 1 (best runner of company) and runner-2 (2nd best runner of company) lower then 5000 sec, which is not the case for smaller companies (in the 'bin' of companies with 2 to 10 runners, the slowest total-time is about 8000 sec.). Although in this category, the winner was a team of a company with only 2 runners, from New Balance. I guess they run on NB-shoes, so this run can be a nice way to get some free promotion...

fig.4: correlation between the number of runners of a company and the best 'total-times' of a team


Downloads:

#Mirror 1: Google Drive (zip files with 2 Excel and PDF files):

https://goo.gl/VUAvVR

#Mirror 2: Microsoft Onedrive (1 Excel file):
NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC

http://1drv.ms/1O4FnIu


#Mirror 3: Scribd.com (1 PDF file):

https://es.scribd.com/doc/293387909/Statistics-10km-Run-CarreraEmpresas2014-v2-R1

Statistics result 10-km run Corre por el niño 2015

#45 Statistics result 10-km run Corre por el niño 2015

UPDATE 8-12-2015: I added another Excel with a boxplot with the finishing-times per category (men/women). In par. Downloads below, I marked this Excel with 'V2'.

UPDATE 16-11-2015: I added another Excel with the ranking per category (men/women).

On Sun. 8 Nov. I participated in the 5th edition of the 10K run "Corre por el Niño 2015" (#CorrePorElNiño), this year with over 8000 participants (10km, 4km and 1 km for kids).  This run is organized by hospital Niño Jesús (in Madrid), to raise funds for investigation of severe diseases with childeren, like e.g. cancer. For more info about this road-race, see:

http://www.correporelnino.com/

http://correporelnino.wordpress.com

https://twitter.com/CorrePorElNino

https://www.facebook.com/Carrera-Popular-Hospital-Ni%C3%B1o-Jes%C3%BAs-212488895455880/

For  some of my photos of the race (and that of 2013), see:

https://picasaweb.google.com/103278159654062440102/CarreraDelNino2013Madrid

In this run the bruto (gun) time and netto (chip) time are measured, and the bruto-time is the oficial time.

I also participated in this run, and, like last year, I made an Excel with the statistics of the result, see fig. 1 for the result. For more info about this Excel with the 2015-results was made,which is almost the same as the Excel with the 2014 result, see my 2014 blog-post about this 10K run:

http://worktimesheet2014.blogspot.com/2014/11/statistics-result-10-km-run-corre-por.html

And here are 2 others sites with statistics and all finish-times of #CarreraPopular #CorrePorElNiño Madrid 2015:

http://observon.blogspot.com.es/2015/11/carrera-popular-corre-por-el-nino-2015.html

http://www.runedia.com/cursa/201526160/carrera-popular-corre-por-el-nino-10k/2015/

(this last site used the netto-time in their statistics, although the oficial finish-time is the bruto-time).

The data from this Excel comes (like last year) from this site:

http://www.cronococa.com/ResultadosRunning.aspx

I copied the data from their site to my Excel and added some check-columns (which are hidden), which showed some errors (red cells):
- in the man/women category field ("Sexo") their was one row with value "Masculina" (in stead of "Masculino")
- for some rows, the netto-time was higher then the netto-time
For the result of the 'import' of the time-date of the website into Excel, see fig.2.

This year there were no chips for the people who registered on the day of the run, so my name is not on the list (sheet 'Data'). My time was something between 47 and 48 min. (I made a short stop during the race to say hello to my sister-in-law and niece who were supporting the runners and I didn´t stop my stop-watch during this break), so as you can see in sheet 'Data', this time corresponds to about ranking 240 (of the 1642 10K runners) and percentile 17. That is in the 'All-category' (Men and Women). And to know my percentile in the Men-category, I copied the Excel and deleted in the Data-sheet all rows with finish-time of category Women (and X - Unknow). The array-formula to calculate the Frequency-colums used 'named ranges' so these were automatically (re-)calculated. The array-formula looks like this:

{=FRECUENCIA(R_Time2;R_Bin1)}

with:
- R_Time2 = range on Time-Bruto column on Data-sheet
- R_Bin1 = range on Time-column on Stats-sheet

And I also made an Excel for the Women-category, and a final one with all Freq.values (so for categories All, Man, Women) with a graph which shows the Percentile-ranks for these 3 datasets, see fig.3, in which you can see e.g that after 55 min. about 50% of the men had finished, and about 20% of the women. And in the Excel for the Men-category I saw my percentile was about 18%.

And in fig.4 you can see a boxplot with the finishing-times per category (Men/Women). This is a stacked barchart based on the differences of the '5 number summary' (min, Q1, Q2 (median), Q3, max.). To create the boxplot, in menu Desgin (of chart) I selected 'Switch rows and columns' and replaced the part of the stached chart for the Min. and Max for the 'whiskers' (implemented by error-bars in chart) of the boxplot. For a more detailed explanation, see:

https://youtu.be/ZFbPnwKwVWk

In the boxplot you can e.g. easily see that 75% of the men (Q3) finished before 50% of the women (Q2, median).

It´s a pity in this run they don´t have more categories using also 'dimension' age-group, e.g. Men-Junior (until 30), Men-Senior A (between 30 and 50), Men-Senior B(older then 50) etc., so you could compare your finishing-time better (with more or less equals (same gender and age-group).

And to conclude, I want to thank hospital Niño Jesús and all the volunteers for making this run a really nice experience and the isotonic drinks and bananas they gave us after the race almost make you forget  the suffering in the last part of the run with some 'nice' slopes...

fig.1: statistics result 10K run "Corre por el Niño 2015" 

fig.2: Imported Time-data

fig.3: Percentile-Rank per Category (Man/Women)

--

fig.4: Boxplot with finishing-times per Category (Man/Women)

Downloads:

#Mirror 1: Scribd.com (PDF file):

https://es.scribd.com/doc/290102349/Statistics-10K-run-CorrePorElNino-Madrid-2015-with-MS-Excel


#Mirror 2: Microsoft Onedrive (Excel files):
NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC


http://1drv.ms/1j6TMLY

http://1drv.ms/1S3qqKw


#Mirror 3: Google Drive (zip files with Excel and PDF files):


https://goo.gl/frAsZF

https://goo.gl/yTzcIu

V2:
https://goo.gl/XBXbFg

Statistics result 10-km run Corre por el niño 2014

#27 Statistics result 10-km run Corre por el niño 2014

On Sun. 9 Nov. I participated in the 4th ed. of the 10K run "Corre por el Niño 2014" (#CorrePorElNiño), together with 1827 other people. (In total there were 7000 runners, the rest participated in the other 4 km run. This run was organized by hospital Niño Jesus (in Madrid), to raise funds for investigation of severe diseases with childeren, like e.g. cancer. For more info  about this road-race, see:

http://correporelnino.wordpress.com

https://twitter.com/CorrePorElNino

https://www.facebook.com/pages/Carrera-Popular-Hospital-Ni%C3%B1o-Jes%C3%BAs/212488895455880

Every participant of the 10k run gets a chip, and in this race the bruto (gun) time and netto (chip) time are measured. The organization decided to use the bruto time as the oficial time (as prescribed by IAAF), although I think the netto time would be better because of the large number of participants, not everybody can start at the start/finish line at the same (gun-)time. For me for example it took 20 sec. after the gun to reach the start/finish line. And the final ranking raised also some doubts: the netto time of the fastest woman (33:47 min, and bruto time 37:03) was better than that of the fastest man (36:05 min. (netto and bruto time)), which probably is incorrect, as was noticed here:

http://www.forofosdelrunning.com/index.php?topic=6709.30  

And I think it´s a pity that in the ranking-table the columns 'gender' and 'category' (age-classes) are missing.
But of course the most important thing was the hospital raised about 70.000 euro (every participant pays 10 euro). For  some of my photos of the race (and that of 2013), see:

https://picasaweb.google.com/103278159654062440102/CarreraDelNino2013Madrid

(one of them has Pedro Delgado on it, the former Tour de France winner, who participated in the run to promoto it).

After the race I wondered how good (or bad) was my ranking (my bruto-time: 53:33 min., netto: 53:13, about half as slow as the world-record for the 10k road-race (26:44 min)...). The ranking was published here:

http://www.cronococa.com/ResultadosRunning.aspx

and I saw I ended at 828th place (my name is in blank because I registered last minute, on the day of the race). And on this site:

http://www.runedia.com/cursa/201418490/carrera-popular-corre-por-el-nino-10k/2014/

I saw that my ranking corresponds to 45%-percentile. And this site also had a nice histogram of the times of the participants, which gave me the idea to do the same in Excel, see fig.1 for the result.

    fig.1: Distribution of finish-times runners

How did I make this? First, I copied the data from the CronoCoca-website, see fig.2, blue columns. Then, I added the orange columns. First, the bruto time in seconds, and the (integer-)values of this column were input for the FREQUENCY-funcion (1st parameter, 'data-array') which calculates the freq. of the values in this data-array. Then I created another table with the 'bins', time-intervals of x minutes (I made 2 versions, with x=2 and x=5), for which I also calculated the equivalent in seconds, which is the 2nd  parameter, 'bins array') of the FREQUENCY-funcion.The results is the column 'Freq.' (see table in fig.1), for which I created a bar-chart (histogram) (see left graph in fig.1). The next column is the cumulative frequency and the last columns the relative cumulative frequenc, or 'percentile rank'. For a more detailed description of Excel and distribution-functions, see:

http://exceluser.com/formulas/frequency-distribution-five-ways.htm

fig.2: Table with all finish-times

For how you can calculate percentiles (quartiles) in Excel (see fig.2, 2nd orange column), using e.g. the  statistical functions PERCENTRANK.INC, PERCENTILE, QUARTILE, see:

http://best-excel-tutorial.com/55-advanced/219-calculate-percentile

http://www.excel-easy.com/examples/percentiles-quartiles.html

As I said, my percentil and ranking was 45% and 828 (see table of fig.1, the orange row). In the histograms with bin-widths of 5 minutes (fig.1, graph Cumul. Rel. Freq.) you can see that my time (53:33 min.) falls between the percentiles of bins 0:51-0:53 and 0:53-0:55 min., so between 41% and 51%. And if you look at the same graph in sheet-2, with the (bigger) bin-width of 5 min., it shows my time is between the 29% (bin 0:45-0:50) and 51% (bin 0:50-0:55). So the smaller the bin-width, the more exact is the percentile-estimation. For how to determine the optimal bin-width, see:

http://stats.stackexchange.com/questions/798/calculating-optimal-number-of-bins-in-a-histogram-for-n-where-n-ranges-from-30

The RunEdia-site also had some other statistics, like mean and standard deviation of the finish-times, things which can also be calculated in Excel with the statistical-functions (AVERAGE, STDEV), see sheet-2 and fig.3 and this site:

http://office.microsoft.com/en-us/excel-help/statistical-functions-HP005203066.aspx

 fig.3: Statistics finish-times

The distribution of the times of the runners looks like a normal distribution  (a bell-shaped curve) and to test this, I used the statistical function NORM.INV to calculate the 3 quartiles Q1, Q2, Q3 and compared them with the 'real' Q1,Q2, Q3, and as you can see, Q1 and Q2 of the normal distribution are quite close to that of the real distribution. Another test for 'normality' of the distribution which I did was the SKEW-function, with a result of 0,3, and according to this site:

http://help.gooddata.com/doc/public/wh/WHAll/Default.htm?#MAQLRefGuide/NormalityTesting-SkewnessAndKurtosis.htm

that indicates the distribution is approximately symmetric (like a normal distribution). The fact the skew is positive means that the distribution-function has a tail, which means in this case (10K run) that there are more runners that are slower than the mean time (54:50 min.) then that there are runners that are faster then the mean time.

For this race I used the Runtastic Android-app to measure my km-times, which you can see here:

http://www.runtastic.com/sport-sessions/347242237

I also included these results (km-times and speed) in my Excel, see fig.4

  fig.4: my km-times and speed

This graphic has besides my speed als the height(differences) during the race (to get the absolute height, you must add ca. 667m, the hight of Madrid (above sea-level). For a description how to combine 2 graphs in 1 graphic (in this case: area-chart for height) and line-chart for speed), see:

http://blogs.office.com/2012/06/21/combining-chart-types-adding-a-second-axis/

For this post I want to thank (Crono)Coca, the company responsable for the time-registration in this 10K run, for answering my questions about the time-ranking table, and José, my running/training-partner, for reviewing the statistics of my Excel.

And to conclude, some interesting websites I found while making this Excel:

- statistics for a 10K run (very profesional!):
http://cnr.lwlss.net/RealData/

-Q/A distribution times 5K run:
http://www.letsrun.com/forum/flat_read.php?thread=4612823

- graphics for distributions:
http://flowingdata.com/2012/05/15/how-to-visualize-and-compare-distributions/

-histograms and (normal) distribution functions in Excel:
http://peltiertech.com/Excel/Charts/Histograms.html
http://exceluser.com/formulas/statsnormal.htm

- race-timing and discussion bruto vs netto finish times and funny anecdote:
http://www.aimsworldrunning.org/race_timing.htm
http://www.iaaf.org/news/news/sometimes-rules-can-be-complicated-to-explain

Downloads:

#Mirror 1 (PDF file):

https://es.scribd.com/doc/247957028/Statistics-10km-Run-CorrePorElNino2014-in-Excel

 #Mirror 2 (Excel file):
NB: this site has MS Onedrive, which has 'Excel-Online', so you can view my Excel-files here if you don´t have MS Excel on your PC

https://onedrive.live.com/redir?resid=3F963E9F1A42D952%21251

#Mirror 3 (Excel and PDF file in 1 zip):

https://drive.google.com/file/d/0BywxxSJoaUYxRkx4RnQwLWNNbVU/view?usp=sharing