# Park Run Data - Scotland



## GorD (Jun 15, 2019)

I have created a query to pull all the park run data from all the scottish park runs which works ok

it uses 43ish urls in a table (a few tables as website didn't like all together) like below

https://www.parkrun.org.uk/standrews/results/latestresults/ 


I get all the data coming through fine , but the table of data doesn't contain a date - which is visible on the webpage above the data table.

My concern is that if a particular park run didn't happen one week i would not know and pull the data through as this weeks

So how can i load the data from the above url and include the date

Thanks for any help


----------



## sandy666 (Jun 15, 2019)

maybe something like this

St Andrews parkrun #359

there is a DateTime of last refresh


----------



## peter789 (Jun 15, 2019)

Unfortunately I get 403 forbidden on the website.  However I had a look earlier and would think about the following approach.  First create a table of the events you are interested in.  Create a query from this and build a link to the event history for each park in a custom column.  Then create a function to create a list of every event in each park with the run number and date. Add this in another column. Finally another function to extract the results.  If the website comes back up I will try it later.

Peter


----------



## GorD (Jun 15, 2019)

Sandy, is that not just the time that you ran your query, rather than the date of the park run that is classed as latest?


----------



## GorD (Jun 15, 2019)

peter789 said:


> Unfortunately I get 403 forbidden on the website.  However I had a look earlier and would think about the following approach.  First create a table of the events you are interested in.  Create a query from this and build a link to the event history for each park in a custom column.  Then create a function to create a list of every event in each park with the run number and date. Add this in another column. Finally another function to extract the results.  If the website comes back up I will try it later.
> 
> Peter



Peter that sounds like exactly what i have done, except i'm using "latest" rather than a specific event no. I don't want to have to go through all urls to see what number they are sitting at - especially when the data im after is already on the page 

Just wondering if it is accessable 

I quite often get the error you get and change browser and i get access again - think it must be something to stop DOS attacks


----------



## sandy666 (Jun 15, 2019)

this is last refresh of the data not a date from the site

text: *St Andrews parkrun # 359 - 15/06/2019* is outside of the table


----------



## GorD (Jun 15, 2019)

Peter, I see what you mean, and have had a go - issue I now see is getting the name of the location.

Also wondering if the web page would block me with running so many queries, as I was getting some mash-up errors when I tried to do all 40 odd urls at the same time

I know think i would do this function once to get the latest date of each event, and my existing function to get all the data - but I can't get the location name, in the same way, I couldn't get the date initially


----------



## GorD (Jun 15, 2019)

sandy666 said:


> this is last refresh of the data not a date from the site
> 
> text: *St Andrews parkrun # 359 - 15/06/2019* is outside of the table



Correct - that is the essence of the problem


----------



## peter789 (Jun 15, 2019)

I'll try again later and see if I can work out a way of limiting my calls to their server.  
I didn't realise you just wanted the latest. That makes it a bit easier.  I will still try calling up the event listings but just return the highest sequential number of run for each one and look up the actual event date from there.  Then I will build a unique URL for each one in a new column and create a function to get the data.
Peter


----------



## peter789 (Jun 15, 2019)

I'm blocked out again. Looks like I have to wait a few hours, I've tried re-booting without success.  I have tried finding the web site's Terms of service, it obviously doesn't like what we are attempting to do and I am slightly concerned about the ethics of continuing.  
However when I get back in I will try Function.Invokeafter with a time delay.
Back to your question on how to get the location name. I used a quick and dirty approach. Searched for event locations on the website filtered by Scotland; copy and paste into Excel, into a query, lower case, removed spaces.


----------



## GorD (Jun 15, 2019)

I have created a query to pull all the park run data from all the scottish park runs which works ok

it uses 43ish urls in a table (a few tables as website didn't like all together) like below

https://www.parkrun.org.uk/standrews/results/latestresults/ 


I get all the data coming through fine , but the table of data doesn't contain a date - which is visible on the webpage above the data table.

My concern is that if a particular park run didn't happen one week i would not know and pull the data through as this weeks

So how can i load the data from the above url and include the date

Thanks for any help


----------



## citizenbh (Jun 15, 2019)

My solution with all the races:  

```
let
     Source = Web.Page(Web.Contents("https://www.parkrun.org.uk/standrews/results/eventhistory/")),
     Data0 = Source{0}[Data],
     #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Run", Int64.Type}, {"Date", type text}, {"Number Runners", Int64.Type}, {"Number Volunteers", Int64.Type}, {"", type text}, {"Male First Finishers parkrunner", type text}, {"Male First Finishers Time", type time}, {"2", type text}, {"Female First Finishers parkrunner", type text}, {"Female First Finishers Time", type time}}),
     #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Number Runners", "Number Volunteers", "", "Male First Finishers parkrunner", "Male First Finishers Time", "2", "Female First Finishers parkrunner", "Female First Finishers Time"}),
     #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns", {{"Date", type date}}, "en-GB"),
     #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Run", type text}}),
     #"Added Custom" = Table.AddColumn(#"Changed Type1", "All_Results", each Web.Page(Web.Contents("https://www.parkrun.org.uk/standrews/results/weeklyresults/?runSeqNumber=" & [Run]))),
     #"Expanded All_Results" = Table.ExpandTableColumn(#"Added Custom", "All_Results", {"Data"}, {"Data"}),
     #"Expanded Data" = Table.ExpandTableColumn(#"Expanded All_Results", "Data", {"Pos", "parkrunner", "Time", "Age Cat", "Age Grade", "Gender Pos", "Club", "Note", "Total Runs", "Total Runs2"}, {"Pos", "parkrunner", "Time", "Age Cat", "Age Grade", "Gender Pos", "Club", "Note", "Total Runs", "Total Runs2"})
 
in     

   #"Expanded Data"
```


----------



## sandy666 (Jun 15, 2019)

GorD said:


> Correct - that is the essence of the problem



maybe like this


*Title**Pos**parkrunner**Time**Age Cat**Age Grade**Gender**Gender Pos**Club**Note**Total Runs*St Andrews parkrun #359  - 15/06/20191​Steven WATTERS17:23VM35-390.768​M1​Stonehaven Running ClubFirst Timer!12​St Andrews parkrun #359  - 15/06/20192​Alistair MCDONALD17:34SM25-290.7343​M2​New PB!6​St Andrews parkrun #359  - 15/06/20193​Ross MAXWELL17:53SM20-240.7213​M3​New PB!2​St Andrews parkrun #359  - 15/06/20194​Fergus INGLEDEW18:25SM20-240.7005​M4​Fife ACPB stays at 00:18:0114​St Andrews parkrun #359  - 15/06/20195​Charles HOWORTH18:31VM35-390.721​M5​Stonehaven Running ClubPB stays at 00:17:5943​St Andrews parkrun #359  - 15/06/20196​Jason SHARP18:54VM40-440.7222​M6​Falkland Trail RunnersNew PB!26​

title is extracted from the web page source
the table imported with standard way


----------



## citizenbh (Jun 15, 2019)

My solution has all the races from 1 to 359.
There is a table with a history of the race:

https://www.parkrun.org.uk/standrews/results/eventhistory/


----------



## GorD (Jun 15, 2019)

Sandy, that now looks perfect 

Peter - I'm slightly worried too that we are causing them issues and they think they are under attack lol - I think illl remove the url from thr original post in cae people all over the world are running function queries


----------



## sandy666 (Jun 15, 2019)

I posted part of the table only with title column added

https://www.parkrun.org.uk/standrews/results/latestresults/

edit:
I tried enter url manually (with Tor browser) and saw 403 forbidden


----------



## GorD (Jun 15, 2019)

Thanks Sandy  

Citizenbh - sorry i missed your post somehow - ill check that out too

P.S Looks like I am unable to edit my original post.


----------



## sandy666 (Jun 15, 2019)

GorD said:


> Sandy, that now looks perfect



that's fine but I am not sure they will keep structure always the same


----------



## sandy666 (Jun 15, 2019)

GorD said:


> P.S Looks like I am unable to edit my original post.



You've only 10 minutes to edit after post


----------



## sandy666 (Jun 15, 2019)

```
[SIZE=1]// Table02
let
    Source = Web.Page(Web.Contents("https://www.parkrun.org.uk/standrews/results/latestresults/")),
    Data = Source{0}[Data],
    Type = Table.TransformColumnTypes(Data,{{"Pos", Int64.Type}, {"parkrunner", type text}, {"Time", type text}, {"Age Cat", type text}, {"Age Grade", Percentage.Type}, {"", type text}, {"Gender Pos", Int64.Type}, {"Club", type text}, {"Note", type text}, {"Total Runs", Int64.Type}, {"Total Runs2", type text}})
in
    Type

// Table0
let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://www.parkrun.org.uk/standrews/results/latestresults/"))}),
    RemoveTopRows = Table.Skip(Source,217),
    KeeptFirstRow = Table.FirstN(RemoveTopRows,3),
    Trim = Table.TransformColumns(KeeptFirstRow,{{"Column1", Text.Trim, type text}}),
    Clean = Table.TransformColumns(Trim,{{"Column1", Text.Clean, type text}}),
    Transpose = Table.Transpose(Clean),
    MergeOne = Table.CombineColumns(Transpose,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    Extract = Table.TransformColumns(MergeOne, {{"Column3", each Text.Start(_, 10), type text}}),
    MergeTwo = Table.CombineColumns(Extract,{"Merged", "Column3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Title")
in
    MergeTwo

// Result
let
    Source = Table.NestedJoin(Table02,{"parkrunner"},Table0,{"Title"},"Table0",JoinKind.FullOuter),
    Expand = Table.ExpandTableColumn(Source, "Table0", {"Title"}, {"Title"}),
    FillUp = Table.FillUp(Expand,{"Title"}),
    Reorder = Table.ReorderColumns(FillUp,{"Title", "Pos", "parkrunner", "Time", "Age Cat", "Age Grade", "", "Gender Pos", "Club", "Note", "Total Runs", "Total Runs2"}),
    RemBottomRow = Table.RemoveLastN(Reorder,1),
    Ren = Table.RenameColumns(RemBottomRow,{{"", "Gender"}}),
    ROC = Table.SelectColumns(Ren,{"Title", "Pos", "parkrunner", "Time", "Age Cat", "Age Grade", "Gender", "Gender Pos", "Club", "Note", "Total Runs"})
in
    ROC[/SIZE]
```

and additional surprise:


----------



## GorD (Jun 15, 2019)

Sandy, I still not sure how you extracted the date?

See code now thanks a lot


----------



## GorD (Jun 15, 2019)

I have created a query to pull all the park run data from all the scottish park runs which works ok

it uses 43ish urls in a table (a few tables as website didn't like all together) like below

https://www.parkrun.org.uk/standrews/results/latestresults/ 


I get all the data coming through fine , but the table of data doesn't contain a date - which is visible on the webpage above the data table.

My concern is that if a particular park run didn't happen one week i would not know and pull the data through as this weeks

So how can i load the data from the above url and include the date

Thanks for any help


----------



## sandy666 (Jun 15, 2019)

You are welcome

but as I said before, I am not sure they will keep the same structure of the page all the time


----------



## citizenbh (Jun 15, 2019)

This is Excel file with data Connection only!

https://www.dropbox.com/s/mkpcpihjci6ga0a/Park Run Data Scotland_All_Races.xlsx?dl=0


----------

