2. Introduction to Plotly¶

by Gustaaf J Wehnes, Calgary Board of Education

There are two major parts to data science: querying the data, and then interpreting the data. For the latter, few people like to look at tables. A picture is worth a thousand words, so let’s visualize the data! We will use a brilliant library of graphing tools called plotly. Many of the visualizations that you see on the internet will have been built using this library!

Again, we can just import the library here. You would need to install it locally on your device to run it there, but the install is not difficult. Note that plotly actually runs in JavaScript, and can thus be accessed through many other languages, including Java.

import plotly.express as px

We will again need our function for obtaining data and our UUIDs. That is just the nature of a Jupyter notebook; they need to be self-contained.

import requests
import pandas as pd
import io

def run_query(domain, uuid, query):
    session = requests.Session()
    results = session.get(domain + uuid +".csv?$query=" + query)
    dataframe =  pd.read_csv(io.StringIO(results.content.decode('utf-8')))
    return dataframe

The Data¶

Say that we are interested in how and when the Bow River tends to peak for the spring run-off. This would be extremely useful to know for the City of Calgary. You will likely remember the floods of 2013! Can we perhaps find patterns and thereby predict if another flood is coming?

Below is a somewhat complex query. However, you should be able to see that the query is finding the maximum daily river level of the Bow River between April and October for all years since 2004.

The tricky part of the query is in the SELECT statement. That is because we want to plot the river levels for each day as a continuum. It would be simplest if we translate the date of each measurement to the day of year. That way, the data sorts itself nicely. The first two lines in the SELECT clause do exactly that through a bit of fancy calculation. Strangely enough, there is no ‘+’ operation in SODA, so I am subtracting a negative instead. Programmers have to get creative to work around obstacles!

domain = "https://data.calgary.ca/resource/"
uuid = "5fdg-ifgr"

query = """
SELECT
    station_name,
    ((date_extract_m(to_floating_timestamp(timestamp,'UTC')) - 1) * 30) as temp,
    (temp - -date_extract_d(to_floating_timestamp(timestamp,'UTC'))) as day_of_year,
    date_extract_y(to_floating_timestamp(timestamp,'UTC')) as year,
    date_extract_m(to_floating_timestamp(timestamp,'UTC')) as month,
    date_extract_d(to_floating_timestamp(timestamp,'UTC')) as day,
    MAX(level) as level
WHERE
    station_name = 'Bow River at Calgary' AND
    year > 2004 AND
    month between 4 AND 10
GROUP BY
    station_name,
    temp,
    day_of_year,
    year,
    month,
    day
ORDER BY
    year ASC,
    day_of_year ASC
LIMIT
    10000
OFFSET
    0

"""

river_levels = run_query(domain, uuid, query)
river_levels

	station_name	temp	day_of_year	year	month	day	level
0	Bow River at Calgary	90	91	2005	4	1	1.06286
1	Bow River at Calgary	90	92	2005	4	2	1.05588
2	Bow River at Calgary	90	93	2005	4	3	1.05492
3	Bow River at Calgary	90	94	2005	4	4	1.06494
4	Bow River at Calgary	90	95	2005	4	5	1.05995
...	...	...	...	...	...	...	...
3206	Bow River at Calgary	150	179	2020	6	29	NaN
3207	Bow River at Calgary	150	180	2020	6	30	2.03900
3208	Bow River at Calgary	180	181	2020	7	1	2.00900
3209	Bow River at Calgary	180	182	2020	7	2	1.97900
3210	Bow River at Calgary	180	183	2020	7	3	1.81800

3211 rows × 7 columns

The Visualization¶

The plotly library has the ability to quickly graph multiple data series in line graphs: Line Charts in Python.

Note that a new object called fig is created, and that it takes in the dataset as the first argument. The x and the y axis are similarily mapped. The year is a third dimension in our data, so let’s have the graph use different color lines for each year.

Ready for some magic?

import plotly.express as px

fig = px.line(river_levels, x="day_of_year", y="level", color='year')
fig.show()

That is a colorful graph! Perhaps there is a little too much data in there, but it does show you around what time of year the Bow River crests. It also clearly demonstrates how extra-ordinary the floods of 2013 was.

Note: the gaps in the 2013 data are likely from the station not reporting on those days due to the power outages that occured.

Plot schools in Calgary on an open street map¶

For some further shock and awe, let’s use a different type of visualization - maps.

In this case, the query for the data will be very simple. The City of Calgary provides a dataset of all schools within its boundary, and includes the geographical location - i.e. the latitude and the longitude.

uuid_school_locations = "fd9t-tdn2"

query = """
SELECT
    *
"""

school_locations = run_query(domain, uuid_school_locations, query)
school_locations

	name	board	type	name_ab	address_ab	city	province	grades	postsecond	data_source	...	ecs	elem	junior_h	senior_h	process_dt	postal_cod	structure_id	longitude	latitude	location
0	Mount Royal University - Lincoln Park Campus	NaN	Post Secondary	NaN	NaN	Calgary	AB	NaN	Y	Post Secondary Data	...	N	N	N	N	2020-05-31T00:00:00.000Z	T3E6K6	1537443	-114.132720	51.012805	\n, \n(51.01280496081857, -114.13272034345307)
1	SAIT - Art Smith Aero Centre	NaN	Post Secondary	NaN	NaN	Calgary	AB	NaN	Y	Post Secondary Data	...	N	N	N	N	2020-05-31T00:00:00.000Z	T2E9B5	1374654	-114.005456	51.101159	\n, \n(51.10115940461863, -114.00545627006355)
2	North Middle School Campus	Foundations for the Future Charter Academy Cha...	Charter	North Middle School Campus	211 McKnight Blvd. NE	Calgary	AB	Elementary/Junior High	N	Provincial Data	...	N	Y	Y	N	2020-05-31T00:00:00.000Z	T2E5S7	1384092	-114.060601	51.095453	\n, \n(51.095453050755296, -114.06060095936921)
3	David Thompson	The Calgary School Division	Public School	David Thompson School	9320 Arbour Crescent S.E.	Calgary	AB	Elementary/Junior High	N	Provincial Data	...	N	Y	Y	N	2020-05-31T00:00:00.000Z	T2J1K4	1382277	-114.054651	50.969404	\n, \n(50.96940358851814, -114.05465136499893)
4	Louise Dean	The Calgary School Division	Public School	Louise Dean School	120 - 23 Street N.W.	Calgary	AB	Senior High	N	Provincial Data	...	N	N	N	Y	2020-05-31T00:00:00.000Z	T2N2P1	1382214	-114.114682	51.053151	\n, \n(51.05315071429952, -114.11468247256505)
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
464	Calgary Girls Charter	Calgary Girls' School Society	Charter	Calgary Girls Charter School	6304 Larkspur Way S.W.	Calgary	AB	Elementary/Junior High	N	Provincial Data	...	N	Y	Y	N	2020-05-31T00:00:00.000Z	T3E5P7	1382200	-114.114754	50.999077	\n, \n(50.99907702433947, -114.1147539295334)
465	Bishop O'Byrne High	The Calgary Roman Catholic Separate School Div...	Separate School	Bishop O'Byrne High School	Suite 500 333 Shawville Boulevard S.E.	Calgary	AB	Senior High	N	Provincial Data	...	N	N	N	Y	2020-05-31T00:00:00.000Z	T2Y4H3	1559224	-114.064567	50.898092	\n, \n(50.89809184871791, -114.06456676845627)
466	Sundance	The Calgary School Division	Public School	Sundance School	200 Sunmills Drive S.E.	Calgary	AB	Elementary	N	Provincial Data	...	N	Y	N	N	2020-05-31T00:00:00.000Z	T2X2N9	1382957	-114.046813	50.900661	\n, \n(50.90066056931126, -114.04681306876033)
467	Sir Winston Churchill High	The Calgary School Division	Public School	Sir Winston Churchill High School	5220 Northland Drive N.W.	Calgary	AB	Senior High	N	Provincial Data	...	N	N	N	Y	2020-05-31T00:00:00.000Z	T2L2J6	1383224	-114.139990	51.100113	\n, \n(51.10011269545306, -114.1399900255147)
468	St. Damien	The Calgary Roman Catholic Separate School Div...	Separate School	St. Damien School	3619 - 28 Street S.E.	Calgary	AB	Elementary	N	Provincial Data	...	N	Y	N	N	2020-05-31T00:00:00.000Z	T2B2J1	1382602	-113.992197	51.021910	\n, \n(51.0219097042796, -113.99219707864222)

469 rows × 21 columns

We will now use the Mapbox class in plotly to visualize the data. Note that a new fig object is created, and that it takes in the dataset as the first argument. The lat argument receives the name of the column in the dataset that contains latitude values (in our case “latitude”). The lon argument similarly is set to ‘longitude’. The other arguments should be quite obvious when we run the remaining code

figure1 = px.scatter_mapbox(school_locations, lat="latitude", lon="longitude",
                            color_discrete_sequence=["green"],
                            zoom=10, height=600,
                            hover_name="name",
                            hover_data=["type", "grades", "address_ab"],)

figure1.update_layout(mapbox_style="open-street-map")
figure1.update_layout(margin={"r":0,"t":0,"l":0,"b":0})

Conclusion¶

This notebook introduced visualizations of Socrata using Plotly. Up next is joining datasets.

Callysto Curriculum Notebooks

2. Introduction to Plotly¶

The Data¶

The Visualization¶

Plot schools in Calgary on an open street map¶

Conclusion¶