read data from html tables on web pages

Telcontar120

There are many pages on the web that contain useful data in the form of simple html tables.  Here's an example:

https://en.wikipedia.org/wiki/List_of_metropolitan_areas_of_the_United_States

 

I know how to use RapidMiner to retrieve this data automatically in html form using "get page" and store it as a document, and I even know how to do this iteratively if a set of related pages are required.  I also have some familiarity with how to manipulate documents, but what I really want is to extract the information in the html table into a usable example set in RapidMiner.  Is there any relatively simple way of doing the following:  

1. collect the table column headers and use them as attribute names
2. collect each data row from the table and store it as an example
3. identify and set the appropriate data type for each resulting attribute

 

It seems like it would be an incredibly useful operator that did all this automatically -  "HTML table to data" or something similar.  I'm fairly certain that such an operator doesn't exist (yet), but I'm not even sure of the collection of existing operators that would be required to do all of the above.  Any ideas @mschmitz or @Thomas_Ott ?

 

Thanks.

 

Telcontar120

This is now solved by the Read HTML table operator.

Thomas_Ott

Hi @Telcontar120,

 

Unfortunately there is no HTML table to Example set operator. I wish there was because there is much good info trapped in tables around the Interwebz.  The only work around I can see is using Xpath to extract the tables from their respective <div> and <td>, <tr> tags. 

 

@mschmitz any other ideas?

MartinLiebig

What about HTML to XML and then Read XML?

 

~Martin

Telcontar120

I believe that is basically the xpath route that @Thomas_Ott is suggesting--which unfortunately involves a lot of manual fiddling with xpath expressions.  Plus I think you would actually need two separate streams, one to extract the column titles and apply those as attribute names, and another to extract the actual data for the examples.  So it's all possible, just not easy (and probably not for the faint of heart either).  Well, I know what I will be suggesting next in the product ideas forum :-)

robin

But how would you do that step of reding the html into xml?

Thomas_Ott

The Text Mining extension has an HTML to XML operator. Give that a try.

Telcontar120

@Thomas_Ott If anyone comes up with an elegant solution to this, I am still interested.  My experience is that the HTML to XML is still quite messy.  In the meantime, my preferred workaround is using Google Sheets to read the html table from the URL (they have a very handy function for exactly that, much like what I was suggesting for RapidMiner).  Then I can export that sheet as a csv and read it into RapidMiner.  It would be even nicer if RapidMiner had the ability to dynamically read Google Sheets, but I haven't figured that out yet either...

Thomas_Ott

Hmm, I recently did a proof of concept where I imported a RM webservice into Google Sheets and managed to score data in the Google Sheet by passing to to the Server. 

 

I wonder if there's a way to cut out that export step you're using.