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

Month-calendar for December 2015 in Excel

#47 Month-calendar for December 2015 in Excel

In this post the new month-calendar for December 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Event of this month is Carrera de las Empresas 2015 Madrid (13/12), a 10K run, a competition between (teams) of companies, so an event where work and sports meet like in this blog. For more info about this event , see:

http://www.carreradelasempresas.com/

http://www.expansion.com/actualidadeconomica/2015/11/02/5633741b46163f905f8b45b5.html

                                          fig.1 Dec.2015 calendar


Downloads:

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

SQL Saturday Madrid 2015 statistics with PowerPivot and PowerQuery

#46: SQL Saturday Madrid 2015 statistics with PowerPivot and PowerQuery

On 21-11-2015, so tomorrow, Saturday, I go to this event: SQL Saturday Madrid 2015, #SQLSatMadrid, where several Microsoft SQL Server and Business Intelligence (BI) MVP´s give some talks, for more information see:

http://www.sqlsaturday.com/459/eventhome.aspx

and for the time-schedule:

http://www.sqlsaturday.com/459/sessions/schedule.aspx


As you can see, there is a total of 27 courses, in 7 time-blocks of 1 hour, in 4 rooms, and for each course the schedule shows this data:

• Course name
• MVP name
• Level (Beginner, Intermediate, Advanced)
• Theme (Big Data, BI, Cloud, DBA), indicated by cell-color
• Language (most are in Spanish but when the course-name is in English , I guess the course is in English)

To ´warm up´ for the sessions about PowerBI, I converted this schedule in a pivot-table and graph, using Power Pivot and Power Query, see fig.1 for the end-result.

 fig.1 Pivot table Courses SQLSat.



To make this Excel, first I copied the Schedule (PDF) to Excel (export to Excel in the tool Nitro PDF) and did some cleaning (delete rows with non-schedule data and 'undo' of cell-merges and delete empty columns), see file 'SQLSaturday459_Madrid2015_Schedule_20151120_v2.xlsx' in Downloads below.
And then I made a 2nd Excel with the statistics, and with PowerQuery I loaded the schedule data from Excel-1, see file 'SQLSaturday459_Madrid2015_Schedule_Statistics' in Downloads and fig.2-3 for some of the steps in the Power Query formula language 'M'. An example of an 'M-formula':

= Table.AddColumn(#"Replaced Value3", "Level_1", each Text.Contains([Value], "Beginner"))

which looks in Excel-1 if in a cell there is the word 'Beginner' (level of course) and if so, sets the value in the Level-1 column to True (and if not to False), for more details of this formula, see:

https://msdn.microsoft.com/en-us/library/mt253324.aspx

fig.2: Power Query import

 fig.3 Power Query formula to Unpivot all columns (Room 1-4') except 1st column ('Time')

And after the Power Query import I did some other things in Excel-2 like adding a Time-block column and Course-Level-column, hiding come columns etc.) to facilitate creating the final pivot-table.
BTW: The Course-Level column I made manually because this information in Excel-1 is not part of the cell (string) value, but color-coded. But probably this could also be automated (so the conversion from cell-color to a (string-)value, see e.g.:

http://excelribbon.tips.net/T010780_Colors_in_an_IF_Function.html

I keep it short now, tomorrow I have to get up early, the event starts at 08:30, and that for a Saturday..


Downloads:

https://goo.gl/xVUdoo

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

Month-calendar for November 2015 in Excel

#44 Month-calendar for November 2015 in Excel


In this post the new month-calendar for November 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is the Science Week, 'la Semana de la Ciencia', in Madrid (2/11-15/11), where universities etc. open their doors to the public, see:


http://www.madrimasd.org/semanaciencia/2015/


Last year one of the activites of the Science Week was a guided walk in a garden with Bonsai-trees, for some photos I made, see:

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

And if your are interested in some science about data-visualization and the important role of Exel in this, as I have tried to show in several posts in this blog (with label: 'Data visualization'), see:

https://blogs.scientificamerican.com/sa-visual/the-science-of-visualization/

--fig.1: Nov. 2015 Calendar

Download:

https://goo.gl/pJdDcd

Month-calendar for October 2015 in Excel

#43 Month-calendar for October 2015 in Excel


In this post the new month-calendar for October 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is the Worldcup Rugby (until 31/10), see:


http://www.rugbyworldcup.com

The quote of this month, comparing soccer and rugby, I found on this poster:

https://www.facebook.com/veitchyonsport/photos/a.566695656700484.1073741828.203262656377121/692113720825343/?type=1&theater


It will be interesting to see how the teams who play against New Zealand will respond to their intimidating Maori war-dance, the famous 'Haka' (which until now worked well for them: in the Worldcup the 'All Blacks' currently are leading in their pool).  So this Worldcup no 'Waka Waka', but the 'Haka', for some nice videos, see :

https://youtu.be/GyZEUeAoFKE

https://www.youtube.com/watch?v=yiKFYTFJ_kw

https://youtu.be/bUZJyZldy10

fig.1 month-calendar Oct.2015

Downloads:

https://goo.gl/J6Uuzx 

Month-calendar for September 2015 in Excel

#42 Month-calendar for September 2015 in Excel

Note 10/9/2015: I updated this post, with a new Excel which includes a 2015/16-schoolyear-calendar, see fig.2 and dowload-url V2 below.

In this post the new month-calendar for September 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is (again) "La Vuelta Ciclista a España - Cycling Tour of Spain 2015", which is held from 20 Aug. to 13 Sept.,  for more info, see:

http://www.lavuelta.com/15pr/en/index.html

The reason to repeat the theme (la Vuelta was also theme of month in Aug.) was to write about a Dutch surprise in this edition of La Vuelta, Tom Dumoulin, who won yesterday in a magnificant race the 9th stage (in Valencia), and is now also leading in the general ranking.  For a video of (part of) the race, see e.g.:

https://youtu.be/Ko-Hix712Ck
https://youtu.be/zj3r4apKNhY

I hope to see Dumoulin finish here in Madrid 13 Sept in the red jersey ( the Spanish yellow jersey).

Fig.1: month-calendar

fig.2: schoolyear-calendar


Downloads:

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

V2 (zip with Excel and PDF-version):
https://goo.gl/XoCvaG