On this page

Callysto.ca Banner

Open in Callysto

3. Joining Datasets

Unlike standard SQL, SODA does not (currently) support the joining of separate datasets. As you recall, this is a fundamental part of working with data. Often, there are different sets of data for the same entity, and it doesn’t make sense to store them all in one dataset. Even if you wanted to, this would quickly increase the # of columns in a dataset.

For example, the School Locations dataset we have already looked at stores both the geographical information of schools in Calgary. The School Enrolment dataset stores the enrollment information for schools. It should be quite obvious that these two datasets should remain separate. Most often, you would not need enrollment #s when looking for locations, and vice versa. If the datasets were permanently combined, you likely have a lot of redundant columns. Moreover, the school enrollment dataset is keyed on the school year. You would have to repeat the geographical data in each row, which would be a poor use of space.

But, what if we wanted to demonstrate additional features of the scattermapbox by visualizing school enrollment? We will need to obtain data from the two seperate datasets, and dynamically join them together. As mentioned, we can’t do it in SODA, but we can do so programatically using the pandas ‘merge’ function.

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

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

uuid_school_locations = "fd9t-tdn2"
uuid_school_enrollment = "9qye-mibh"

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

The first dataset contains latitude and longitude

query = """
SELECT
    *
"""

calgary_school_location = run_query(domain, uuid_school_locations, query)
calgary_school_location
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

The second dataset contains enrollment data. Note that the enrollment dataset also contains a school_year column, which makes sense, as enrollment values do change. However, our intent is to visualize enrollment at a specific point in time, so we use the WHERE clause to filter out rows that do not match that time.

query = """
SELECT
    *
WHERE
    school_year = '2019-2020'
"""

calgary_school_enrollment = run_query(domain, uuid_school_enrollment, query)
print(calgary_school_enrollment)

    school_year school_authority_category  \
0     2019-2020                    Public   
1     2019-2020                    Public   
2     2019-2020                    Public   
3     2019-2020                  Separate   
4     2019-2020                  Separate   
..          ...                       ...   
385   2019-2020                  Separate   
386   2019-2020                    Public   
387   2019-2020                  Separate   
388   2019-2020                    Public   
389   2019-2020                   Private   

                                 school_authority_name  school_authority_code  \
0                          The Calgary School Division                   3030   
1                          The Calgary School Division                   3030   
2                          The Calgary School Division                   3030   
3    The Calgary Roman Catholic Separate School Div...                   4010   
4    The Calgary Roman Catholic Separate School Div...                   4010   
..                                                 ...                    ...   
385  The Calgary Roman Catholic Separate School Div...                   4010   
386                        The Calgary School Division                   3030   
387  The Calgary Roman Catholic Separate School Div...                   4010   
388                        The Calgary School Division                   3030   
389                     Calgary Waldorf School Society                   9224   

                 school_name  school_code   ecs  grade_1  grade_2  grade_3  \
0            Haysboro School         9316  21.0     24.0     25.0     26.0   
1         Guy Weadick School         9343  33.0     40.0     44.0     62.0   
2      Silver Springs School         9256  28.0     38.0     33.0     36.0   
3         Holy Angels School          389  31.0     25.0     37.0     28.0   
4          St. Helena School         8711   NaN      NaN      NaN      NaN   
..                       ...          ...   ...      ...      ...      ...   
385              St. Anthony         8515   NaN      NaN      2.0      2.0   
386            Elboya School         9627  26.0     45.0     43.0     50.0   
387       St. Patrick School         8507  54.0     31.0     40.0     34.0   
388  Dr. E. W. Coffin School         9214  21.0     27.0     26.0     36.0   
389   Calgary Waldorf School         9949  17.0     19.0     28.0     22.0   

     grade_4  grade_5  grade_6  grade_7  grade_8  grade_9  grade_10  grade_11  \
0       25.0     31.0     23.0      NaN      NaN      NaN       NaN       NaN   
1       48.0     52.0     36.0      NaN      NaN      NaN       NaN       NaN   
2       41.0     43.0     44.0      NaN      NaN      NaN       NaN       NaN   
3       33.0     35.0     24.0      NaN      NaN      NaN       NaN       NaN   
4        NaN      NaN      NaN    116.0     85.0    116.0       NaN       NaN   
..       ...      ...      ...      ...      ...      ...       ...       ...   
385      3.0      2.0      NaN      3.0      1.0      2.0       4.0       1.0   
386     36.0     96.0    115.0    110.0    101.0     87.0       NaN       NaN   
387     29.0     30.0     31.0      NaN      NaN      NaN       NaN       NaN   
388     26.0     29.0     18.0      NaN      NaN      NaN       NaN       NaN   
389     22.0     21.0     21.0     22.0     20.0     19.0       NaN       NaN   

     grade_12  total  
0         NaN    175  
1         NaN    315  
2         NaN    263  
3         NaN    213  
4         NaN    317  
..        ...    ...  
385       7.0     27  
386       NaN    709  
387       NaN    249  
388       NaN    183  
389       NaN    211  

[390 rows x 20 columns]

Data from CSV files

We could instead use pandas to import data from CSV (comma separated values) files.

import pandas as pd

calgary_school_location = pd.read_csv("https://data.calgary.ca/resource/64t2-ax4d.csv")
calgary_school_enrollment = pd.read_csv("https://data.calgary.ca/resource/9qye-mibh.csv?$where=School_Year%20=%20'2019-2020'")

calgary_school_location
NAME BOARD TYPE NAME_AB ADDRESS_AB CITY PROVINCE GRADES POSTSECOND DATA_SOURCE ... POSTAL_COD STRUCTURE_ID longitude latitude location Ward Boundaries City Quadrants Calgary Communities Ward Boundaries 2013-2017 CALGIS CENSUS COMMUNITY DIST 2015 Shape File
0 Mount Royal University - Lincoln Park Campus NaN Post Secondary NaN NaN Calgary AB NaN Y Post Secondary Data ... T3E6K6 1537443 -114.132720 51.012805 (51.01280496081857, -114.13272034345307) 8 1 42 2 11
1 SAIT - Art Smith Aero Centre NaN Post Secondary NaN NaN Calgary AB NaN Y Post Secondary Data ... T2E9B5 1374654 -114.005456 51.101159 (51.10115940461863, -114.00545627006355) 11 4 86 1 226
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 ... T2E5S7 1384092 -114.060601 51.095453 (51.095453050755296, -114.06060095936921) 2 4 241 4 101
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 ... T2J1K4 1382277 -114.054651 50.969404 (50.96940358851814, -114.05465136499893) 10 3 174 5 81
4 Louise Dean The Calgary School Division Public School Louise Dean School 120 - 23 Street N.W. Calgary AB Senior High N Provincial Data ... T2N2P1 1382214 -114.114682 51.053151 (51.05315071429952, -114.11468247256505) 7 2 280 7 35
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
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 ... T3E5P7 1382200 -114.114754 50.999077 (50.99907702433947, -114.1147539295334) 8 1 122 5 265
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 ... T2Y4H3 1559224 -114.064567 50.898092 (50.89809184871791, -114.06456676845627) 5 3 115 6 281
466 Sundance The Calgary School Division Public School Sundance School 200 Sunmills Drive S.E. Calgary AB Elementary N Provincial Data ... T2X2N9 1382957 -114.046813 50.900661 (50.90066056931126, -114.04681306876033) 6 3 52 13 128
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 ... T2L2J6 1383224 -114.139990 51.100113 (51.10011269545306, -114.1399900255147) 7 2 50 4 280
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 ... T2B2J1 1382602 -113.992197 51.021910 (51.0219097042796, -113.99219707864222) 10 3 196 11 1

469 rows × 26 columns

Joining data sets

Whichever method we use to import the data, we can now join the two datasets on the school name (school_name in calgary_school_location and name or NAME in calgary_school_enrollment) as the values in these fields match up. In proper SQL parlance, they share a key. Again, the dataset has already been filtered for a single school year. This means that there should be no duplicates of school name in the school enrollment dataset.

Thus, we can do a simple left join with the enrollment on the left and the locations on the right. That way, our resulting dataframe will only have data on schools that have enrollment data. We would run into problems later on if there are NaN (aka null) values in the total field.

calgary_school_location_enrollment = pd.merge(left=calgary_school_enrollment, 
                                              right=calgary_school_location, 
                                              how='left', 
                                              left_on='school_name', 
                                              right_on='NAME' )
                                              # right_on='name' ) #use this line if you imported data using Requests

calgary_school_location_enrollment
school_year school_authority_category school_authority_name school_authority_code school_name school_code ecs grade_1 grade_2 grade_3 ... POSTAL_COD STRUCTURE_ID longitude latitude location Ward Boundaries City Quadrants Calgary Communities Ward Boundaries 2013-2017 CALGIS CENSUS COMMUNITY DIST 2015 Shape File
0 2019-2020 Public The Calgary School Division 3030 Haysboro School 9316 21.0 24.0 25.0 26.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 2019-2020 Public The Calgary School Division 3030 Guy Weadick School 9343 33.0 40.0 44.0 62.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 2019-2020 Public The Calgary School Division 3030 Silver Springs School 9256 28.0 38.0 33.0 36.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
3 2019-2020 Separate The Calgary Roman Catholic Separate School Div... 4010 Holy Angels School 389 31.0 25.0 37.0 28.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
4 2019-2020 Separate The Calgary Roman Catholic Separate School Div... 4010 St. Helena School 8711 NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
386 2019-2020 Separate The Calgary Roman Catholic Separate School Div... 4010 St. Anthony 8515 NaN NaN 2.0 2.0 ... T2S2N5 1377870.0 -114.079295 51.010108 (51.01010774903789, -114.0792951385963) 8.0 1.0 143.0 5.0 275.0
387 2019-2020 Public The Calgary School Division 3030 Elboya School 9627 26.0 45.0 43.0 50.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
388 2019-2020 Separate The Calgary Roman Catholic Separate School Div... 4010 St. Patrick School 8507 54.0 31.0 40.0 34.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
389 2019-2020 Public The Calgary School Division 3030 Dr. E. W. Coffin School 9214 21.0 27.0 26.0 36.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
390 2019-2020 Private Calgary Waldorf School Society 9224 Calgary Waldorf School 9949 17.0 19.0 28.0 22.0 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

391 rows × 46 columns

Visualizing the data

We will now visualize the resulting dataset, and use the size and color to add two extra dimensions of data - the total enrollment and the school authority.

Note

  • the showlegend parameter is used to hide the legend, but this is only to clean up the presentation!

import plotly.express as px

figure1 = px.scatter_mapbox(calgary_school_location_enrollment, 
                            size="total",
                            color="school_authority_name", 
                            #showlegend= False,
                            color_continuous_scale=px.colors.cyclical.IceFire,
                            size_max=45, 
                            lat="latitude", 
                            lon="longitude", 
                            hover_name="school_name", 
                            hover_data=["TYPE", "GRADES", "ADDRESS_AB"],
                            #hover_data=["type", "grades", "address_ab"], #use this line if you imported data using Requests
                            zoom=10, 
                            height=600)

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

Conclusion

This notebook introduced the ability to join datasets. The last notebook in this series is filtering datasets.

Callysto.ca License

2. Introduction to Plotly 4. Filtering Datasets