Too Many Grades! Published 15th Apr 2016

How long is a piece of string?

Have you ever heard that question? A common answer is “twice as long as from one end to the middle”, but perhaps an overlooked response is “according to which unit of measurement?”. These conversions have tripped people up over many years (including, unfortunately a Mars space incident in that 1990’s which I leave you to research!)

As may have transpired from my first blog post, I’m a fan of walking, scrambling, and indeed climbing! For many sports, there are “helpful” gradings to tell us which challenges we should be aiming for – and perhaps more importantly, which challenges we should not be aiming for (at least not yet!)

These gradings can be especially helpful for incrementally increasing one’s ability and, though inevitably subjective, I’ve found them helpful to push myself to the next level. (I still remember the fear when skiing and attempting my first “red” – it looked almost cliff-like, and so much less friendly than those “blue” markers I had grown used to).

Unfortunately for climbing there are quite a number of different rating systems – favouring endurance, technique or strength. “Wouldn’t it be great to know how these standards compare!”, I hear you asking.

Fortunately, Wikipedia is ready to help, and has a number of comparison charts. In this article, we’re going to explore the import techniques of Schematiq, and use them to create an automatic converter for USA to French bouldering grades using the Wikipedia web page.

If you’re not familiar with HTML or XPath syntax - do not fear - we’ll explain the purpose of each formula as we go along – or feel free to read ahead to the “Begin the Analysis” section below.

First let’s load the HTML webpage using Schematiq’s “web.page” command:

A1 := web.Page("https://en.wikipedia.org/wiki/Grade_(bouldering)")

A2 := =tbl.ImportHtml(A1)

undefined

Looking in Schematiq’s viewer, we see a lot of HTML code. This is the same as you’d find if you loaded the web page and clicked “view page source”. We’d like to select the relevant table from the HTML, and copy in to a Schematiq table.

Fortunately Schematiq has built-in features, intended precisely for this purpose (there is a great tutorial explaining how this works). For now, let’s be content with checking how each part works (if you’re happy to accept this filtering of the HTML code, then do read ahead to the section “Begin the analysis!” below).

The next three cells read the table elements from the HTML, and output the relevant rows to a new Schematiq table, which is seen in the viewer:

A3 :=tbl.ImportHtml(A1,"//table[1]//tr", "td/text()", "Grades")

A4 :=tbl.Filter(A3, "$Row", "r => AND(r >= 5, r <= 31)")

A5 := =tbl.CalculateColumn(A4, "Grades", "tbl.Create")

(To compare: the coloured table shown is the actual one from Wikipedia)

undefined

The commands above translate to: a) get the rows and columns from the first table in the webpage in to a column called “Grades”; b) select the rows of this table that we need, c) let each row contain a mini table with the conversion information.

 undefined

Looking at this table, we’re almost done! Since each USA grade may be equivalent to multiple French grades, we must replace a blank row by the preceding USA grade. For example, row 2 in the viewer should be equivalent to “VB”.

Let’s apply some filtering to check if a row has a USA value assigned already:

A7 := =tbl.CalculateColumn(A5, "Grades", B7, "Has_USA")

A8 := =tbl.CalculateColumn(A7, {"Grades","Has_USA"}, B8, "USA")

A9 := =tbl.CalculateColumn(A8, {"Grades","Has_USA"}, B9, "France").

 undefined

 

Any row in the viewer set to “False” must be replaced by the most recent USA grade above it, e.g. the second and third rows will also be equivalent to the grade “VB”:

A12 := (USA, previous) => IF(ISBLANK(USA), previous, USA)

A12 :=tbl.CalculateColumn(A9, {"USA","[-1]"}, B12, "USA")

And finally we can use the nesting feature of Schematiq tables, so a single row with USA grade “VB” can contain a table with all of the equivalent French grades.

A13 := =tbl.Pack(A12, "USA", "France", "France").

And we’re done!

undefined

Begin the analysis!

Let’s enjoy the fruit of our work now and create a function which, given a USA grade, will return all equivalent French grades. Let’s search for the table row containing “V3”, and then find the maximum and minimum French grades in this row:

B16 : (x,y)=>x=y

A16 :=fn.FixInput(B16,”V3”,2)

Applying this formula to our table, we select the required row:

A17 :=tbl.Filter(A13,"USA",A16)

undefined

So can’t I just look at Wikipedia?

Good question, of course you can! The advantage of setting this automatic converter is two-fold. Firstly, in general we may be interested in a very large table, and we might not want to search through manually – Schematiq happily handles larger data sets in Excel.

Secondly, if I were to have copied and pasted the table in to Excel – what if the table changes or expands, and I would never know?

Indeed, Wikipedia is always ready to help!

As it turns out, another page on Wikipedia has a similar but slightly different table – let’s compare them using Schematiq with the following very simple edits:

A1 : =web.Page("https://en.wikipedia.org/wiki/Grade_(climbing)"),

Our table is the fourth one on this new page,

A3 : =tbl.ImportHtml(A1,"//table[4]//tr", "td/text()", "Grades")

A4 : =tbl.Filter(A3, "$Row", "r => AND(r >= 2, r <= 31)")

And we need only the two columns with USA and French grades:

B7 := t => IF(tbl.RowCount(t) = 3, true, false).

 undefined

Very easily we see that the previously undefined grade “V0-“ is comparable with a French grade 4-.

So, how long is a piece of string? Well, it’s still undetermined, but just maybe it depends which Wikipedia page you use.

As for climbing grades, thanks to Wikipedia, we’ve no need to worry about the conversions. Let the climbing begin!

blog comments powered by Disqus
Writing some notes

Blog & News

Find out what we're talking about on the blog