Overview

This is a sample notebook to show how you can create a dynamic document that reworks itself if the data changes. Note that this is not a Microsoft Office. At the end of this talk, we are going to have a document with updated data, new charts and tables, as well as a saved dataset or two.

We’ll do this by downloading Environment Canada data one month at a time, performing some functions on it and then combining all the months together.

Here’s the temperature every hour for 2012. It is not pretty, but it is mostly readable. The x-axis needs some work, but this is literally done using all defaults.

One month of weather data

When using the data in my previous talk, I loaded it into an RRD. For anyone who is familiar with RRD, it is a fixed size and lossy storage format. You you lose detail over time and data stored values can average across readings. It is good for trending, but not for anything requiring exact numbers.

In this case, we have complete data (well, as complete as it gets). First we are going to look at the hourly temperatures for Ottawa. Environment Canada makes it easy to download data, but it uses a web interface. If you play with it for a bit, you can see the pattern in the request URL and automate from there.

Ottawa is station id 49568.

The following is a template we can use for fetching specific months in a year. We could even allow for the choice of station to make it more flexible, but that is not really necessary here.

In order to download data for the month of March in 2012, we need to specify month=3, year=2012 using the format function.

If you have used R, you are familiar with reading from a URL as well as a file. Pandas has that capability also.

Just use the same read_csv function as before with the URL instead of the filename, and just give it a URL as a filename.

There are 16 rows of metadata at the top of this CSV, you need to have looked at it before trying this in order to isolate the actual data from the rest of the explanation. In this case we need to skip over 16 rows to get to the data. There is no extra junk at the bottom we need to ignore, so we have a simple import. Since pandas understands the CSV format and that there are irregularities in the files, there is a skiprows option. Our options will be to parse the dates again, and set ‘Date/Time’ to be the index column.

Here’s the resulting dataframe.

And since graphics are much more interesting than tables, lets plot it.

Notice how it goes up to above 25° C in the middle there? That was an interesting March. We can quickly look at March of 2013, 2014 and 2015 to compare it.

That didn’t work out too well, did it? I’m sure there is a way to show stacked plots, but our dates happen to be sequential and the x-axis allowed for that.

Separating it into three cells gives a slightly better view

You can see that 2013, 2014 and 2015 were colder then 2012 in March.

Now I want to get rid of the screwy special characters in the temperature columns. They look odd in the headers and require entering them as hex values. We can see the interpretation of them by listing the column values.

As you can see, we have some embedded characters there that could make life interesting. We can rename the column names using the rename function. We can look at the table again to see the difference. I’m going to strip out the  and ° characters, as they are bot east to type on my keyboard and do not really add anything.

We have a success, the extra characters are gone.

You may have noticed that in the output table summary above, there are columns which are are either entirely empty or only have a few values in them. We could use a shortcut and use the dropna function.

Something like this: ott_weather_mar2012 = ott_weather_mar2012.dropna(axis=1, how='any')

The argument axis=1 to dropna means “drop columns”, not rows", and how='any' means “drop the column if any value is null”.

This technique works well if you can guarantee the columns you are interested in have data. In this case, you can see from the plot above that the temperature data has at least one discontinuity. We could do it the hard way, by using the column names and the drop function.

Just to show what would happen, I’ll make a copy of the table and use the dropna function.

In most of my tests, this deleted the Temp (C) column. In order to avoid that, we would have had to drop by name or manipulated the data to remove missing rows from the data set. The generated list of column labels could have been useful if we wanted to programmatically remove columns we are not interested in.

The Year/Month/Day/Time columns are not necessary - it is duplicate data. The Data Quality column doesn’t look like it has anything of value either, so out they go.

This time, we use the drop function. We will still use the axis=1 argument so we drop columns. The default for operations like dropna and drop is always to operate on rows.

We can still remove a few columns we don’t need. The dropna will kill our data, so we can manually drop a number of the columns and we will certainly have a much more manageable table to work with. I am not bothering at this time. You would still have to specify them somehow and you can’t do it programatically unless you already know which columns you wish to key.

The object here is to start with a known data set and reduce it to what we want to work with. All steps are visible and the original data has not been touched. All modifications are documented and anybody can reproduce your steps with the same set of tools. Interpretations of the results later may differ, but the path is visible and can be followed.

This is the reproducible part of reproducible research that is mentioned in all of the data science courses/books/talks/etc. The data and the methodology is available for anyone to see and use.

How about plotting the temperature by hour of day?

Using the groupby and aggregate functions, you can determine if it gets colder at night. While you already know the answer from experience, we can show it visually. We group readings by the hour, get the average of each group and then plot the results.

In our case, Ottawa appears to have the highest median temperature at 13:00 in March. Another question that I will not bother with at present would be to see what the highest median temperature for each month and see if it varies during the year. You could also do this across multiple years and see if there is a trend or if it has a lot of variation.

Obtaining a whole year of data

If we want the data for the whole year, we would just ask for it. Unfortunately, it seems to be only available by month so you need to gather each month and combine them.

First step, use the work from above to create a function that downloads the weather for a given month.

The author of the original notebook noticed that there’s an irritating bug where when you ask for January, it gives the data for the previous year. The data set seems to have had that corrected, so we don’t require a special case for January. This function is a modified version of the original, which no longer needs to correct January nor does it munge the data. We will do that later.

We can test that this function does the right thing:

With this function, we can get all the months at once. This will take a little while to run.

Once we have run this, it’s easy to concatenate all the dataframes together into one big dataframe using pd.concat. The result is the whole year’s data.

Of course, we still have to sanitize the data. The original defined function tried to do that, but the Ottawa data had some issues. A post-collection clean-up makes a little more sense to me anyway. Get the data, then manipulate it.

I’m make a copy of the data we aggregated first so I can show what happens if you simply use the dropna immediately.

We lost the data we were interested in, but we already had a good idea this would happen. Apparently there was a lot of missing data in 2012, or at least enough to trigger the column to be dropped. Dropping the redundant rows at this point would only leave us with a time series for the year. Not terribly interesting.

Restoring to our saved data, we can use a slightly different methodology to get the data that has values. We can just drop the rows where the temperature has no value. We should see how many rows we have before and after to see if there was a significant amount of data lost. Significant is left as an exercise for the person doing the work.

Depending on how that turned out, do we trust the data set now?

Using the dataframe shape function, we can get the size of the data frame.

The first column is the number of rows. We have a lot of rows.

Sanitizing the data, uses a few manipulations. Since we really only care about the temperature, we can effectively drop all the other columns. That isn’t quite as easy as it sounds, we would either have to use indices or names and that was already looked at.

The following could be a function, but I’m not bothering at this point. I just want sanitized data.

Since we have sanitized, we should check the dataframe again

We only have two columns now, so many were dropped with the dropna function. We also have a reduced number of rows. At the time I wrote this, we reduced the row count by 65 rows or roughly 0.8%. Since we are only doing hourly data, this is does not appear to be terribly significant, however we should check for large gaps in the data. If these all occurred together, we would have close to a three day data gap that needed explanation.

Our new data frame looks like this now.

Saving the data to a CSV

It’s slow and unnecessary to download the data every time, so we should save our dataframe for later use. In reality, we should have saved the full set for the year, just in case we wanted to use other columns in the future.

Data saved. Just for fun, using the saved data, we can plot the data for the year. This looks suspiciously like the first graph.