1. Introduction to Socrata¶

by Gustaaf J Wehnes, Calgary Board of Education

We will be using Socrata open data for our forays into the rapdily emerging field of data science. Socrata is used by many governments and organiations, including our own City of Calgary. Take some time to familiarize yourself with the technology through their home page

The SODA API¶

As programmers, we will want to know how to programatically interact with open data sources. will be using an API (Application Programming Interface) developed Thus, you will also want to bookmark the Socrata Open Data API (aka SODA). Pay particular attention to the Getting Started and the API Docs pages.

If you don’t know what an API is, here is a definition

The City of Calgary datasets¶

Also have a look at The City of Calgary’s Open Data Portal. There are all kinds of cool data sets here, along with tools for visualizing this data. Hopefully you can already start seeing how open data provides a massive opportunity for app developers and researchers.

In particular, have a look at three datasets we will be using in our journey: River Levels and Flows, School Locations, and School Enrolment.

https://dev.socrata.com/docs/functions/#,

https://data.calgary.ca/Environment/River-Levels-and-Flows/5fdg-ifgr

Queries in SODA¶

The Socrata interface allows the building of SQL-like queries: query Parameter. Theses queries are much more readable than the queries we were writing before using keywords in a URL

To allow the potential running of multiple queries, let’s take the time to create a function that does some of the dirty work for us. We use the requests library to obtain data from an internet request based on the domain, uuid, and query. The get method returns a byte array, which we then have to conver to a dataframe

function run_query

paramter domain: refers to the organization that supplies the data

parameter uuid: universal unique ID; refers to the specific dataset.

parameter query: SODA query

returns: panadas dataframe

Python tools¶

The last few pieces that needs introducing are the Python libraries that we will use. Again, take some time to have a look. The first is an open source data analysis and manipulation tool called pandas. Two other libraries, which you only need to be passingly familiar with, are requests and Python io.

Don’t get overwhelmed; there is a lot of functionality here, and just like me, you will just learn it as you go.

The beauty of using Jupyter is that these tools are pre-installed! So, no messing with downloading and installing. We simply import them :-)

import requests as rq
import pandas as pd
import io as io

Some terminology and a helper function¶

To simplify the execution of multiple queries, let’s take the time to create a function that does some of the dirty work for us. We use the requests library to obtain data from an internet request based on the domain, uuid of the dataset, and the query itself. A ‘session’ object is created with the requests library; the get method is used to return a byte array from an http source, and we then covert that array to a pandas dataframe object. Note that we also import the four libraries previously mentioned.

function run_query

paramter domain: refers to the organization that supplies the data

parameter uuid: universal unique ID; refers to the specific dataset.

parameter query: SODA query

returns: panadas dataframe

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

READY? Lets query some data!¶

You may already have come across the documentation for the SODA query. This will look quite familiar to the SQL statements that you have already learned about. Let’s run through a pile of examples, where you can see how you can use the tools to obtain data in CSV (or JSON) format.

simple query¶

Retrieve the entire dataset.

Note:¶

A maximum of 1000 records are returned. That is probably a good thing, as the dataset itself is huge (5.48 million records as of April 2020, and ever growing).

domain = "https://data.calgary.ca/resource/"

uuid_river_levels = "5fdg-ifgr"
uuid_school_enrollment = "9qye-mibh"

query = """
SELECT *
"""

river_levels = run_query(domain, uuid_river_levels, query)
river_levels

	station_number	station_name	timestamp	level	flow	id
0	05BJ008	Glenmore Reservoir at Calgary	2020-07-03T07:15:00.000Z	1074.356	NaN	05BJ008-2020-07-03 07:15:00
1	05BH005	Bow River near Cochrane	2020-07-03T07:15:00.000Z	17.026	244.822	05BH005-2020-07-03 07:15:00
2	05BH005	Bow River near Cochrane	2020-07-03T07:10:00.000Z	17.025	244.472	05BH005-2020-07-03 07:10:00
3	05BJ004	Elbow River at Bragg Creek	2020-07-03T07:05:00.000Z	1.821	26.320	05BJ004-2020-07-03 07:05:00
4	05BJ001	Elbow River below Glenmore Dam	2020-07-03T07:05:00.000Z	1.300	32.213	05BJ001-2020-07-03 07:05:00
...	...	...	...	...	...	...
995	05BH015	Jumpingpound Creek at Township Road 252	2020-07-02T19:25:00.000Z	2.936	8.152	05BH015-2020-07-02 19:25:00
996	05BH014	Nose Creek above Airdrie	2020-07-02T19:25:00.000Z	9.193	3.146	05BH014-2020-07-02 19:25:00
997	05BH004	Bow River at Calgary	2020-07-02T19:25:00.000Z	1.865	281.155	05BH004-2020-07-02 19:25:00
998	05BJ001	Elbow River below Glenmore Dam	2020-07-02T19:25:00.000Z	1.330	34.213	05BJ001-2020-07-02 19:25:00
999	05BJ004	Elbow River at Bragg Creek	2020-07-02T19:25:00.000Z	1.845	27.569	05BJ004-2020-07-02 19:25:00

1000 rows × 6 columns

limiting the amount of data¶

Retrieve only the first 100 record of the dataset.

Note:¶

You can set any limit that you like, but be aware of how much data you are going to get back. Obviously, the larger the dataset (rows * columns) the longer the download time (and the data used).

query = """
SELECT
    *
LIMIT
    100
"""

river_levels = run_query(domain, uuid_river_levels, query)
river_levels

	station_number	station_name	timestamp	level	flow	id
0	05BJ008	Glenmore Reservoir at Calgary	2020-07-03T07:15:00.000Z	1074.356	NaN	05BJ008-2020-07-03 07:15:00
1	05BH005	Bow River near Cochrane	2020-07-03T07:15:00.000Z	17.026	244.822	05BH005-2020-07-03 07:15:00
2	05BH005	Bow River near Cochrane	2020-07-03T07:10:00.000Z	17.025	244.472	05BH005-2020-07-03 07:10:00
3	05BH004	Bow River at Calgary	2020-07-03T07:05:00.000Z	1.763	248.663	05BH004-2020-07-03 07:05:00
4	05BH005	Bow River near Cochrane	2020-07-03T07:05:00.000Z	17.025	244.472	05BH005-2020-07-03 07:05:00
...	...	...	...	...	...	...
95	05BH005	Bow River near Cochrane	2020-07-03T05:50:00.000Z	17.023	243.777	05BH005-2020-07-03 05:50:00
96	05BJ001	Elbow River below Glenmore Dam	2020-07-03T05:50:00.000Z	1.361	36.300	05BJ001-2020-07-03 05:50:00
97	05BH004	Bow River at Calgary	2020-07-03T05:50:00.000Z	1.767	249.901	05BH004-2020-07-03 05:50:00
98	05BH005	Bow River near Cochrane	2020-07-03T05:45:00.000Z	17.023	243.777	05BH005-2020-07-03 05:45:00
99	05BH015	Jumpingpound Creek at Township Road 252	2020-07-03T05:45:00.000Z	2.907	7.294	05BH015-2020-07-03 05:45:00

100 rows × 6 columns

paging through data¶

Retrieve records 101 to 200. You will again receive 100 record.

Note:¶

This is known as paging. You could, if you want, retrieve the data in small chunks. We will not be doing this in this course, but it is good to know that it can be done.

query = """
SELECT
    *
LIMIT
    100
OFFSET
    100
"""

river_levels = run_query(domain, uuid_river_levels, query)
river_levels

	station_number	station_name	timestamp	level	flow	id
0	05BJ010	Elbow River at Sarcee Bridge	2020-07-03T05:45:00.000Z	4.438	33.929	05BJ010-2020-07-03 05:45:00
1	05BH005	Bow River near Cochrane	2020-07-03T05:45:00.000Z	17.023	243.777	05BH005-2020-07-03 05:45:00
2	05BJ004	Elbow River at Bragg Creek	2020-07-03T05:45:00.000Z	1.823	26.423	05BJ004-2020-07-03 05:45:00
3	05BH015	Jumpingpound Creek at Township Road 252	2020-07-03T05:45:00.000Z	2.907	7.294	05BH015-2020-07-03 05:45:00
4	05BJ008	Glenmore Reservoir at Calgary	2020-07-03T05:45:00.000Z	1074.358	NaN	05BJ008-2020-07-03 05:45:00
...	...	...	...	...	...	...
95	05BH004	Bow River at Calgary	2020-07-03T04:35:00.000Z	1.761	248.046	05BH004-2020-07-03 04:35:00
96	05BH014	Nose Creek above Airdrie	2020-07-03T04:35:00.000Z	9.180	2.987	05BH014-2020-07-03 04:35:00
97	05BJ004	Elbow River at Bragg Creek	2020-07-03T04:35:00.000Z	1.823	26.423	05BJ004-2020-07-03 04:35:00
98	05BJ001	Elbow River below Glenmore Dam	2020-07-03T04:35:00.000Z	1.361	36.300	05BJ001-2020-07-03 04:35:00
99	05BH015	Jumpingpound Creek at Township Road 252	2020-07-03T04:35:00.000Z	2.911	7.410	05BH015-2020-07-03 04:35:00

100 rows × 6 columns

select a limited number of fields¶

This can dramatically reduce the amount of data returned (as the dataframe is like a table, so rows x columns).

query = """
SELECT
    station_number,
    timestamp,
    level
LIMIT
    100    
"""

river_levels = run_query(domain, uuid_river_levels, query)
river_levels

	station_number	timestamp	level
0	05BH005	2019-11-10T10:40:00.000Z	16.306
1	05BH005	2019-11-10T10:45:00.000Z	16.305
2	05BH005	2019-11-10T10:50:00.000Z	16.309
3	05BH005	2019-11-10T10:55:00.000Z	16.307
4	05BH005	2019-11-10T11:00:00.000Z	16.308
...	...	...	...
95	05BH005	2010-04-21T10:30:00.000Z	16.006
96	05BH005	2010-04-21T10:45:00.000Z	16.031
97	05BH005	2010-04-21T11:00:00.000Z	16.030
98	05BH005	2010-04-21T11:15:00.000Z	16.056
99	05BH005	2010-04-21T11:30:00.000Z	16.094

100 rows × 3 columns

providing an alias for a field¶

You can rename a field with a custom name. This will be useful later when we calculate our own fields; for now, you can see the syntax is simple. Observe that the dataframe has the desired field names.

query = """
SELECT
    station_number as station,
    timestamp as time,
    level as river_level
LIMIT
    100    
"""

river_levels = run_query(domain, uuid_river_levels, query)
river_levels

	station	time	river_level
0	05BH005	2019-11-10T10:40:00.000Z	16.306
1	05BH005	2019-11-10T10:45:00.000Z	16.305
2	05BH005	2019-11-10T10:50:00.000Z	16.309
3	05BH005	2019-11-10T10:55:00.000Z	16.307
4	05BH005	2019-11-10T11:00:00.000Z	16.308
...	...	...	...
95	05BH005	2010-04-21T10:30:00.000Z	16.006
96	05BH005	2010-04-21T10:45:00.000Z	16.031
97	05BH005	2010-04-21T11:00:00.000Z	16.030
98	05BH005	2010-04-21T11:15:00.000Z	16.056
99	05BH005	2010-04-21T11:30:00.000Z	16.094

100 rows × 3 columns

data transform functions¶

If you look at the dataset ‘meta’ data on the City of Calgary’s website, you will notice that all of the fields have a type. This is no different than in most programming languages: you have text, integers, and even date types.

Unfortunately, fields are sometimes not of the desired type. This may be due to the dataset not being well designed, or for reasons of saving space. Fortunately, SODA provides a series of Data Transform functions which allow you to transform (or ‘cast’) the field to a different type.

Note:¶

Subclauses in the WHERE clause are separated by AND instead of commas, to reflect that these are boolean expressions
OR could be used as well
at this time, I am not certain why I cannot convert level to a number using the to_number function. Stay tuned…

query = """
SELECT
    station_name,
    timestamp,
    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,
    date_extract_hh(to_floating_timestamp(timestamp,'UTC')) as hour,
    date_extract_mm(to_floating_timestamp(timestamp,'UTC')) as minute,
    date_extract_ss(to_floating_timestamp(timestamp,'UTC')) as second,
    level
LIMIT
    10
"""

river_levels = run_query(domain, uuid_river_levels, query)
river_levels

	station_name	timestamp	year	month	day	hour	minute	level
0	Bow River near Cochrane	2019-11-10T10:40:00.000Z	2019	11	10	10	40	16.306
1	Bow River near Cochrane	2019-11-10T10:45:00.000Z	2019	11	10	10	45	16.305
2	Bow River near Cochrane	2019-11-10T10:50:00.000Z	2019	11	10	10	50	16.309
3	Bow River near Cochrane	2019-11-10T10:55:00.000Z	2019	11	10	10	55	16.307
4	Bow River near Cochrane	2019-11-10T11:00:00.000Z	2019	11	10	11	0	16.308
5	Bow River near Cochrane	2019-11-10T11:05:00.000Z	2019	11	10	11	5	16.306
6	Bow River near Cochrane	2019-11-10T11:10:00.000Z	2019	11	10	11	10	16.306
7	Bow River near Cochrane	2019-11-10T11:15:00.000Z	2019	11	10	11	15	16.307
8	Bow River near Cochrane	2019-11-10T11:20:00.000Z	2019	11	10	11	20	16.306
9	Bow River near Cochrane	2019-11-10T11:25:00.000Z	2019	11	10	11	25	16.307

WHERE clause¶

Just like in SQL, we can filter the records returned using the WHERE clause. There are a lot of powerful operators available, as per the documentation. Don’t hesitate to experiment!

Note:¶

Subclauses in the WHERE clause are separated by AND instead of commas, to reflect that these are boolean expressions
OR could be used as well

query = """
SELECT
    station_name,
    timestamp,
    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,
    date_extract_hh(to_floating_timestamp(timestamp,'UTC')) as hour,
    date_extract_mm(to_floating_timestamp(timestamp,'UTC')) as minute,
    date_extract_ss(to_floating_timestamp(timestamp,'UTC')) as second,
    level
WHERE
    station_name = 'Bow River at Calgary' AND
    year = 2004 AND
    (month between 4 AND 5 OR month between 9 AND 10) AND
    day = 1 AND
    hour = 0 AND
    minute = 0
LIMIT
    10
"""

river_levels = run_query(domain, uuid_river_levels, query)
river_levels

	station_name	timestamp	year	month	day	level
0	Bow River at Calgary	2004-04-01T00:00:00.000Z	2004	4	1	0.998
1	Bow River at Calgary	2004-05-01T00:00:00.000Z	2004	5	1	0.990
2	Bow River at Calgary	2004-09-01T00:00:00.000Z	2004	9	1	1.416
3	Bow River at Calgary	2004-10-01T00:00:00.000Z	2004	10	1	1.249

ORDER BY clause¶

Just like in SQL, we can order the records returned using the ORDER BY clause. You can specify multiple fields in order to create a secondary order.

query = """
SELECT
    station_name,
    timestamp,
    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,
    date_extract_hh(to_floating_timestamp(timestamp,'UTC')) as hour,
    date_extract_mm(to_floating_timestamp(timestamp,'UTC')) as minute,
    date_extract_ss(to_floating_timestamp(timestamp,'UTC')) as second,
    level
WHERE
    station_name = 'Bow River at Calgary' AND
    year = 2004 AND
    (month between 4 AND 5 OR month between 9 AND 10) AND
    (day = 1 OR day = 15) AND
    hour = 0 AND
    minute = 0
ORDER BY
    month DESC,
    day DESC
LIMIT
    10
"""

river_levels = run_query(domain, uuid_river_levels, query)
river_levels

	station_name	timestamp	year	month	day	level
0	Bow River at Calgary	2004-10-15T00:00:00.000Z	2004	10	15	1.185
1	Bow River at Calgary	2004-10-01T00:00:00.000Z	2004	10	1	1.249
2	Bow River at Calgary	2004-09-15T00:00:00.000Z	2004	9	15	1.396
3	Bow River at Calgary	2004-09-01T00:00:00.000Z	2004	9	1	1.416
4	Bow River at Calgary	2004-05-15T00:00:00.000Z	2004	5	15	1.095
5	Bow River at Calgary	2004-05-01T00:00:00.000Z	2004	5	1	0.990
6	Bow River at Calgary	2004-04-15T00:00:00.000Z	2004	4	15	0.997
7	Bow River at Calgary	2004-04-01T00:00:00.000Z	2004	4	1	0.998

GROUP BY clause¶

This is where things get interesting, as you would know if you’ve gone through the w3schools’scourse on SQL. As this group by section explains, grouping is most often used along with aggregate functions (COUNT, MAX, MIN, SUM, AVG).

For these next examples, we will use the school enrollment dataset. The first example below will check how many high schools existed for each school authority over three years.

You can think of grouping as collapsing the data that would be returned if you ran the query without the GROUP BY clause. All of the records that share the same values for all of the fields are aggregated (collapsed) into a single row. The fields that you are not grouping by thus need to have an aggregate function of some sort in the WHERE clause: hence COUNT(level), which will do exactly that.

Note:¶

Observe how the aggregated field is automatically named

query = """
SELECT
    school_authority_name,
    school_year,
    COUNT(school_name)
WHERE
    (school_year = "2018_2019" OR school_year = "2017_2018" OR school_year = "2016_2017" OR school_year = "2015_2016") AND
    grade_12 > 0
GROUP BY
    school_authority_name,
    school_year
ORDER BY
    school_authority_name,
    school_year
LIMIT
    60
"""
    
school_enrollment = run_query(domain, uuid_school_enrollment, query)
school_enrollment

	school_authority_name	school_year	COUNT_school_name
0	Access International College (Calgary) Incorpo...	2017_2018	1
1	Access International College (Calgary) Incorpo...	2018_2019	1
2	Aurora Learning Foundation (Calgary)	2015_2016	1
3	Aurora Learning Foundation (Calgary)	2016_2017	1
4	Aurora Learning Foundation (Calgary)	2018_2019	1
5	Calgary Academy Society	2017_2018	2
6	Calgary Academy Society	2018_2019	2
7	Calgary Chinese Alliance School Society	2015_2016	1
8	Calgary Chinese Alliance School Society	2018_2019	1
9	Calgary French & International School Society	2015_2016	1
10	Calgary French & International School Society	2016_2017	1
11	Calgary French & International School Society	2017_2018	1
12	Calgary French & International School Society	2018_2019	1
13	Calgary German Language School Society	2015_2016	1
14	Calgary Quest Children's Society	2015_2016	1
15	Calgary Quest Children's Society	2016_2017	1
16	Calgary Quest Children's Society	2017_2018	1
17	Calgary Quest Children's Society	2018_2019	1
18	Calgary Roman Catholic Separate School Distric...	2015_2016	14
19	Calgary Roman Catholic Separate School Distric...	2016_2017	13
20	Calgary Roman Catholic Separate School Distric...	2017_2018	14
21	Calgary Roman Catholic Separate School Distric...	2018_2019	15
22	Calgary School District No. 19	2015_2016	36
23	Calgary School District No. 19	2016_2017	37
24	Calgary School District No. 19	2017_2018	36
25	Calgary School District No. 19	2018_2019	36
26	Centro Linguistico E Culturale Italiano Calgary	2015_2016	1
27	The Calgary Chinese Public School Society	2015_2016	1
28	The Calgary Chinese Public School Society	2016_2017	1
29	The Calgary Society for Effective Education of...	2015_2016	2
30	The Calgary Society for Effective Education of...	2016_2017	2
31	The Canadian Reformed School Society of Calgary	2015_2016	1
32	The Canadian Reformed School Society of Calgary	2016_2017	1
33	The Canadian Reformed School Society of Calgary	2017_2018	1
34	The Canadian Reformed School Society of Calgary	2018_2019	1

You can also try the MAX, MIN, AVG, and SUM functions. In this case, let’s only run the query on schools that have Grade 12 students.

Note:¶

We will provide slightly more descriptive field aliasses.
You can re-use a field to get multiple aggregations.

query = """
SELECT
    school_authority_name,
    COUNT(school_name) as total_schools,
    MAX(grade_12) as max_grade_12,
    AVG(grade_12) as avg_grade_12,
    MIN(grade_12) as min_grade_12,
    SUM(grade_12) as total_grade12
WHERE
    school_year = "2018_2019" AND
    grade_12 > 0
GROUP BY
    school_authority_name
LIMIT
    50
"""
    
school_enrollment = run_query(domain, uuid_school_enrollment, query)
school_enrollment

	school_authority_name	total_schools	max_grade_12	avg_grade_12	min_grade_12	total_grade12
0	Aurora Learning Foundation (Calgary)	1	2	2.000000	2	2
1	Calgary Quest Children's Society	1	27	27.000000	27	27
2	Calgary French & International School Society	1	5	5.000000	5	5
3	Calgary School District No. 19	36	981	332.027778	1	11953
4	Calgary Chinese Alliance School Society	1	2	2.000000	2	2
5	Calgary Academy Society	2	51	31.000000	11	62
6	The Canadian Reformed School Society of Calgary	1	14	14.000000	14	14
7	Calgary Roman Catholic Separate School Distric...	15	1458	359.400000	2	5391
8	Access International College (Calgary) Incorpo...	1	9	9.000000	9	9

AN IMPORTANT DISTINCTION¶

The data above begs the question of what the largest Grade 12 class in the city is! However, you do not have to use the MAX function to find this. Rather, you could simply use the SORT BY and the LIMIT function. This gets around the difficulty of having to either group or aggregate on all fields. If you grouped the above query by school_name, you would receive a maximum for each school. If you were to use an aggregate function instead, you would not get the right school name!

This also allows me to quickly discuss the NaN value, which can be though of like a null. If you do not include the WHERE clause in the query below, you will get a record with NaN,

query = """
SELECT
    COUNT(school_name) as total_schools,
    MAX(grade_12) as max_grade_12
WHERE
    school_year = "2018_2019" AND
    grade_12 > 0
LIMIT
    1
"""
    
school_enrollment = run_query(domain, uuid_school_enrollment, query)
school_enrollment

	total_schools	max_grade_12
0	59	1458

Conclusion¶

This notebook introduced Socrata and the SODA API. Up next is an Introduction to Plotly.

Callysto Curriculum Notebooks

1. Introduction to Socrata¶

The SODA API¶

The City of Calgary datasets¶

Queries in SODA¶

Python tools¶

Some terminology and a helper function¶

READY? Lets query some data!¶

simple query¶

Note:¶

limiting the amount of data¶

Note:¶

paging through data¶

Note:¶

select a limited number of fields¶

providing an alias for a field¶

data transform functions¶

Note:¶

WHERE clause¶

Note:¶

ORDER BY clause¶

GROUP BY clause¶

Note:¶

Note:¶

AN IMPORTANT DISTINCTION¶

Conclusion¶