Nested Tables

[jonfitt]

Hello all,

I was really impressed by the Strata webcast, I could think of loads of occasions when I've cut and pasted data from the web into something like Excel when I wanted to really do data analysis (which is hard going in excel) and this could do so much more.

So I thought I'd play around a bit with it first and went to weather.com for some weather data:
http://www.weather.com/weather/wxclimat ... y/USTX1134

But the monthly weather data is stored in tables nested in tables, so whenever I say "Import Table" it only grabs one "row". Is there a way around this, or is it something for a software enhancement?

Thanks.


PS Any chance of producing an Extension which is similar to Firefox's Adblock? That's the first thing I install on a fresh install of Firefox

[Ken]

Unfortunately, for many websites, Strata is currently at the mercy of website developers (outside of actually scripting something to parse the tables, that is). However, because there is just too much variation on the web to be able to try and predict every possible configuration, we've got some stuff cooking in the labs to help solve these problems. You will also run into this sort of issue when tables are formatted using <div> tags instead of the traditional <table> tags.

The workaround is annoying for a situation like this (it might be easier to just type in the values from the 12 rows). However, it may be useful if you run into other bigger tables you'd want to merge together into a single table. So here it is for the sake of posterity:

Because these rows are, inexplicably, each tables unto themselves, you'd need to import each table (in this case, each row) individually. Then you'd save each of the tables to the project tree (click the untitled table and click the save icon). Then, you'd go to Tools > Merge Tables and drag in all 12 tables into the merge dialog. Name your output file, click OK and you'll get your combined table.

Note however that Strata also seem to be having problems auto-sensing these values (in some ways it does well to guess these are numbers, but it isn't picking up the month character or the decimal places. When it does its auto-sensing, it helps to have multiple rows in order to predict the correct type, so this example doesn't give it much to go on). I'll add this to the bug list.

So, in a nutshell, the best answer to solve this issue is still being worked on.

Regarding Adblock, I feel your pain. We've got a full mechanism in Strata for building scripts and extensions to share with others, although we haven't done a good job yet of exposing this to everyone. More documentation and examples will be coming down the pipe sooner rather than later though. So, even though many of these will likely be data related, there is no reason that other extensions like Adblock could also be created/ported since we are using the Mozilla's Gecko for our rendering engine. We'll be looking for community support to fill in some of these holes.

Thanks for the report!

[jonfitt]

Had a go at importing them all separately, but I'm sorry to say I gave up before finishing!
With the import not detecting the type of the "Month" column all of the imports start with 0, so it was really tricky to sort them, and the thought of repeating this for multiple locations (12 imports for each location) to actually do some data analysis really put me off.

I tried importing the table to word to get it to recreate the structure but it didn't work, I will now try excel.
I'll let you know how it goes!

There must be something out there which will render it as written, but then refactor the table to be logical?

[Ken]

Yeah, weather.com isn't doing us any favors here.

OK -- two more options, neither of which are ideal either:

1) If you did use Strata to import each table individually, the trick would be to add the "Jan", "Feb" manually. The way you do this is go to toggle to the design view and change the structure of field1 from numeric to character. Then, toggle back to the table view and double click on that field1 cell and enter "Jan". Again, this is pretty annoying since you'd have to do this step for all 12 tables. This auto-sensing issue has been added to the bug list, so hopefully it'll get fixed soon. [EDIT: This has been fixed for Beta 4]

2) You can't just copy and paste from Strata/Firefox to Excel -- otherwise you get one long string of text in a individual cell. What you need to do is go to Data > Get External Data > New Web Query. Enter the URL and then select "Only the tables" and Excel will try to bring down any table data it finds into the worksheet. Unfortunately, you get a bunch of unwanted data, so you need to scroll down to find the specific weather data you are looking for. I quickly pulled this out into a separate worksheet and saved it as a CSV file (attached below). However, if you want to use it in Strata, you'll need to a) clean up some of the characters (degree symbol). Also, if you want to use the numbers, you'll need to take off the "F" etc in order to get it in a sortable format.

All in all, not a simple solution in this case either way.