<jupyter_start><jupyter_text>Lab 4 - Accessing Data Sources*© 2021 <NAME>*Welcome to Week 4 of INFO 6270! Last week we covered basic data cleaning and analysis using lists and dictionaries. This week we are making our way to our final lesson on basic Python: libraries and external files. This week we will do a few things that will more relatable (and useful!) to most of you. We will start by learning how to navigate files in our Python environment before making our way to work with CSV files and JSON data. With this final skill, we will be ready to complete substantial data science tasks.This week's work covers a **lot** of ground from [Sweigart (2020)](https://automatetheboringstuff.com/). Instead of going through all of the possible reference chapters, we will focus on material from Chapter 16 throughout and apply it in a way that is more relevant to our context. If you are interested (_and happen to have the time_) it is also helpful to have read Chapters 6 and 7 on string manipulation and regular expressions. The later is quite complex however and we will not cover it in this course; if you want to be a data science expert though, you should definitely learn regular expressions! It might also be useful to read  Chapter 9 on reading and writing files, though these optional readings might be best done once you have time either later in the course or (more likely) in the summer if you want to extend your skills further!**This week, we will achieve the following objectives:**- Retrieve and locate CSV data- Analyze and write CSV data- Search for books using the Open Library API- Use an API to retrieve Open Library collections data- Write API data to a CSV fileWeekly reading: Sweigart (2014) Ch. 16. Note that some of the latter objectives have multiple challenge questions this week.  Case: The 2016 Canadian CensusThe Canadian Census Program is a data collection program conducted by Statistics Canada every five years and has occurred regularly since 1851, before confederation. Census records are maintained by two federal government agencies based on their date of record. Census records prior to 1926 are curated by [Library and Archives Canada](https://www.bac-lac.gc.ca/eng/census/Pages/census.aspx), while records after 1926 are maintained by [Statistics Canada](https://www12.statcan.gc.ca/census-recensement/index-eng.cfm). These records represent the most comprehensive data on the Canadian population and are (for the most part) publicly available.In addition to census profiles on various regions throughout the country, the Census Program provides [detailed data tables](https://www12.statcan.gc.ca/census-recensement/2016/dp-pd/dt-td/index-eng.cfm) on a variety of topics. Housing is one such topic and the data table titled ["Tenure including presence of mortgage payments and subsidized housing"](https://www12.statcan.gc.ca/census-recensement/2016/dp-pd/dt-td/Rp-eng.cfm?TABID=2&LANG=E&A=R&APATH=3&DETAIL=0&DIM=0&FL=A&FREE=0&GC=01&GL=-1&GID=1341679&GK=1&GRP=1&O=D&PID=110574&PRID=10&PTYPE=109445&S=0&SHOWALL=0&SUB=0&Temporal=2017&THEME=121&VID=0&VNAMEE=&VNAMEF=&D1=0&D2=0&D3=0&D4=0&D5=0&D6=0) is particularly relevant to understanding housing affordability among Canadians because it provides the number of Canadian households which reported unaffordable housing or whether their households were in need of repairs. In this last lab in a series related to housing security, we will retrieve and analyze the tables provided by the census to generate insights about housing needs in Canada and Halifax specifically, if so desired.  Objective 1: Retrieve and locate CSV data The first step with any data project is to source and attain the data that you plan to analyze. Though the data has been provided for you in the /data folder, you can also choose to retrieve this on your own. Statistics Canada's census portal provides a (somewhat complicated) interface for attaining the data that we are interested in. By default, the portal gives a user the ability to create a custom table containing only the data that a user is interested in. Visit the provided at [this link](https://www12.statcan.gc.ca/census-recensement/2016/dp-pd/dt-td/Rp-eng.cfm?TABID=2&LANG=E&A=R&APATH=3&DETAIL=0&DIM=0&FL=A&FREE=0&GC=01&GL=-1&GID=1341679&GK=1&GRP=1&O=D&PID=110574&PRID=10&PTYPE=109445&S=0&SHOWALL=0&SUB=0&Temporal=2017&THEME=121&VID=0&VNAMEE=&VNAMEF=&D1=0&D2=0&D3=0&D4=0&D5=0&D6=0) and click on the Download tab. Select `CSV (comma-separated values) file` from the Download interface under _"Download data as displayed in the Data table tab"_. This will download a CSV file into your downloads folder. Figure 1 - How to retrieve data from StatsCan![alt text](img/4-1.png "Download the Table")Rename this file to `w4_canada_housing.csv` and move it into the `/data` subdirectory that has been provided to you. You can do this by right clicking on the file that you downloaded and selecting rename and then by dragging it to the relevant folder. Once your data is in the relevant folder, you are ready to interpret it for our first data science-style project!  Python Libraries and CSVBefore we can get started with data science earnestly, we need to know more about one last core Python feature: *libraries*. As mentioned in class, one of the main advantages of Python versus many other programming languages is that it is *high level and highly portable*. This is to say that you can do a lot with Python in a few lines of code. One of the main things that makes this possible are Python's libraries.In programming, a __library__ is a collection of pre-defined routines that you can import into your code without writing them. These greatly accelerate the time that it takes to finish a programming task, and in some cases, save you years of work. Just like libraries designed for humans, Python programming libraries are generally curated by groups of people who ensure that the library is usable. As a free and open source programming language, experienced developers will often create and curate libraries for free, sometimes at great expense of their time!You will probably be unsurprised to learn that we will again leverage a library to read this file; in this case, the `csv` library. This library is the bread and butter of basic data science and we will come back to it almost every class from here on out. Python's [csv library](https://docs.python.org/3/library/csv.html) is a fantastic resource for reading and writing csv files. This one takes a little getting used to, so it is better to simply give an example of its basic use and then explain it. The following cell gives code for reading the file that you downloaded from Statistics Canada.<jupyter_code>import csv
with open('data/w4_canada_housing.csv', newline='') as csvfile: # tells Python which file to read
    housing_reader = csv.reader(csvfile, delimiter=',') # draws on the reader object to read the file
    for row in housing_reader:
        print(row)<jupyter_output><empty_output><jupyter_text>__As you can see, the file is _quite messy!___ However, the code really only has two unfamiliar parts to it. The first is the `with open('data/w4_canada_housing.csv', newline='') as csvfile:`. The `with open()` statement is the way that you command Python to open an external file. In this line of code, you are telling Python to open this file and call its contents `csvfile` in our environment.The second unfamiliar piece of code is `housing_reader = csv.reader(csvfile, delimiter=',')`. The `csv.reader` is an object contained in the `csv` library which is designed to read csv files. In the `(csvfile, delimiter=',')` bit, we commanded the csv reader to read the opened `csvfile` and that each data in that file was separated (delimited) by the character `,`. CSV (comma separated values) files are simply a series of data separated by commas. In this line of code, we thus created a reader called `housing_reader` which reads the data inside of the csv file.The reader object consists of a series of rows for each row in the csv file. We can loop through the rows using a for loop, just like with other data! Unfortunately, Statistics Canada's CSV files contain a lot of data which are not useful for this task. What we need is a way to clean the data efficiently. Fortunately, we learned this skill in Week 2; let's apply it to CSV files!  *Challenge Question 1 (1 point)*Currently the Statistics Canada table is structured poorly for Python analysis. Fortunately most of the unusable data are similarly  structured, each being placed on a single column row. Modify the code below to do the following:- check to see if the row contains too few columns- append the rows that have the useful data to a listDoing this will give us a "list of lists" (a.k.a. two dimensional list) which we can use for analysis later.__Hint:__ You can use `len()` to find the length of rows and use `if` to exclude rows that are too long!<jupyter_code>import csv

housing = []

with open('data/w4_canada_housing.csv', newline='') as csvfile:
    housing_reader = csv.reader(csvfile, delimiter=',')
    for row in housing_reader:
        # some logic to filter out rows that have too few items
        # some logic to append the row to the housing list
test = ['Housing indicators (5)', 'Total - Tenure including presence of mortgage payments and subsidized housing [4]', '  Owner', '    With mortgage', '    Without mortgage', '  Renter', '    Subsidized housing', '    Not subsidized housing', ' ']<jupyter_output><empty_output><jupyter_text>Sample Test Should return a list called `housing` that looks like this:`[['Housing indicators (5)', 'Total - Tenure including presence of mortgage payments and subsidized housing [4]', '  Owner', '    With mortgage', '    Without mortgage', '  Renter', '    Subsidized housing', '    Not subsidized housing', ' '], ['Total - Housing indicators [5]', '13798300', '9357290', '5680655', '3676630', '4441020', '575830', '3865190 '], ['   Adequacy: major repairs needed', '867565', '516640', '337990', '178645', '350925', '54300', '296625 '], ['   Suitability: not suitable', '670735', '253560', '199985', '53575', '417175', '48835', '368335 '], ['   Affordability: 30% or more of household income is spent on shelter costs', '3325950', '1550380', '1308780', '241600', '1775570', '238825', '1536740 '], ['   Adequacy, suitability or affordability: major repairs needed, or not suitable, or 30% or more of household income is spent on shelter costs [6]', '4373550', '2140660', '1694325', '446335', '2232895', '304675', '1928215 ']]`<jupyter_code>print(housing)<jupyter_output><empty_output><jupyter_text>Objective 2: Analyze and write CSV data In addition to reading CSV files, the CSV library helps us write files. One of the most tangible, practical uses for Python in an office setting is that you can clean such files and return them accordingly. Let' start by retrieving your current `housing` list.<jupyter_code>for h in housing: # print each line separately for readability
    print(h)<jupyter_output><empty_output><jupyter_text>It would be desirable to reduce the length of the long titles, such as `Adequacy: major repairs needed` and replace them with something more digestible. This would be a pain to do in a defined business analytics program.  Cleaning your CSV dataAn effective way to clean csv data is to create a function that iterates through a list file. For instance, we already decided to save the contents of our csv file in a list called `housing`. We could create the `cleanCSV` function which removes the colons as follows.<jupyter_code>def cleanCSV(csvfile):
    new_file = []
    i = 0 
    while i < len(csvfile): # the length of the number of rows
        new_list = [] # a placeholder for cleaned row strings
        j = 0
        while j < len(csvfile[i]): # the number of values in this row
            if ":" in csvfile[i][j]: 
                colon_index = csvfile[i][j].index(":") #retrieve the index of the colon
                new_list.append(csvfile[i][j][:colon_index]) # [:colon_index] retrieves the string characters to the left of the index.
            else:
                new_list.append(csvfile[i][j]) # if there is no colon in the value, append it to the placeholder
            j += 1
        new_file.append(new_list) # append the cleaned list to the first level list
        i += 1
    return(new_file) # returns the cleaned file<jupyter_output><empty_output><jupyter_text>We can then create a `new_housing` list which contains the cleaned version of the `housing` data.<jupyter_code>new_housing = cleanCSV(housing) # apply the function

for h in new_housing: # print each line separately for readability
    print(h)<jupyter_output><empty_output><jupyter_text>This is a bit better. Some of the unwieldly titles have changed. We can then use this cleaned data to write a CSV file.  Writing a CSV file Similarly to the `reader`, the Python csv library has a `writer`. The writer similarly uses the `with open(` structure, though contains the 'w' option. The writer similarly can create new rows and is designed to be iterated. Try executing the following code-- you will be left with cleaned data file called `w4_canada_housing_cleaned.csv` which you can open in Excel.<jupyter_code>with open('data/w4_canada_housing_cleaned.csv', 'w', newline='') as csvfile: # open a new file
    housing_writer = csv.writer(csvfile, delimiter=',') # create a writer
    for row in new_housing: # for every row in the new_housing list
        housing_writer.writerow(row) # write the row<jupyter_output><empty_output><jupyter_text>Figure 2 - Cleaned data in Excel![alt text](img/4-2.png "Cleaned Data")  *Challenge Question 2 (1 point)*Though the `cleanCSV()` function currently cleans out values to the left of the colon, there is still data which can be further cleaned. Modify the function to clean the data further. There are many ways to answer this question; you will be evaluated based on whether you:- modify the cleanCSV function- apply it to create an even cleaner csv file__Hint:__ Any tangible improvement to the `cleanCSV()` will net you full points here. Don't overcomplicate it! It could be as simple as removing some characters that you don't want in the final output.  Modify the cleanCSV function here<jupyter_code>def cleanCSV(csvfile):
    new_file = []
    i = 0 
    while i < len(csvfile): # the length of the number of rows
        new_list = [] # a placeholder for cleaned row strings
        j = 0
        while j < len(csvfile[i]): # the number of values in this row
            if ":" in csvfile[i][j]: 
                colon_index = csvfile[i][j].index(":") #retrieve the index of the colon
                new_list.append(csvfile[i][j][:colon_index]) # [:colon_index] retrieves the string characters to the left of the index.
            else:
                new_list.append(csvfile[i][j]) # if there is no colon in the value, append it to the placeholder
            j += 1
        new_file.append(new_list) # append the cleaned list to the first level list
        i += 1
    return(new_file) # returns the cleaned file
foo ="bar"
foo.replace("a","")<jupyter_output><empty_output><jupyter_text>Apply the function<jupyter_code>new_housing = cleanCSV(housing) # apply the function
#### Write the file
import csv

with open('data/w4_canada_housing_cleaned.csv', 'w', newline='') as csvfile:
    housing_writer = csv.writer(csvfile, delimiter=',')
    for row in new_housing:
        housing_writer.writerow(row)<jupyter_output><empty_output><jupyter_text>Case: An Open CollectionCSV files are good, thuogh there is a second common method for accessing data sources, one that is arguable even more important: Application Programming Interfaces (APIs). APIs are communication protocols that govern how software communicates to each other. In the case of web APIs, they often govern how computers exchange data over the internet. These days, internet data is often exchanged in JavaScript Object Notation (JSON) format, which we explored briefly last week.Though there are virtually infinite uses for web application programming interfaces (APIs), one of the most tangible and easy to use is that provided by [Internet Archive's](https://archive.org/) Open Library, which is a free online book lending service. The vision of the [Open Library](https://openlibrary.org/) is to make all of humanity's published works freely available to everyone in the world. It does this by providing a digital collection of books in a variety of formats, ranging from text to Kindle.The [Open Library's API](https://openlibrary.org/developers/api) gives detailed documentation about how to access and use data retained on their system. Though we will not use the API to retrieve book content, we will use it to navigate their collection and retain their library system data. Though book content may be copyrighted, their system data is [freely available for web developers to use](https://openlibrary.org/developers/licensing). For more information about open data licenses, please refer to the documentation on [opensource.org](https://opensource.org/licenses).There are many APIs which may be useful to you in your research. You may be interested in checking out some of the following free APIs:- [Open Corporates](https://api.opencorporates.com/) - A large repository of company information;- [NASA](https://api.nasa.gov/) - NASA images galore! (Requires a key);- [Chuck Norris jokes](http://www.icndb.com/api/) - A simple Chuck Norris joke generator;- [REST Countries](https://restcountries.eu/) - Country information;- [Reddit](https://www.reddit.com/dev/api/) - Access to social media data.In the remainder of this exercise, we will learn about Python **request libraries** by querying the **Open Library API**. Learning about computer libraries by querying innovative library technologies... it's pretty meta!  Objective 3: Search for books using the Open Library APIPython has a few great libraries for retrieving and managing JSON data. The first library we will explore is `requests`. Start by importing `requests`, as we did with the `csv` library.<jupyter_code>import requests # import the requests library<jupyter_output><empty_output><jupyter_text>It's possible that this code gave you an error because it is not installed. ***If it is not install***, try the following command, which leverages the `pip` installer to install the library: `pip install requests` You can learn more about installing libraries in Python [here](https://packaging.python.org/tutorials/installing-packages/).If the above code did work however, rejoice! Though it might seem like wizardry at times, all the `requests` library does is allow us to make web requests similarly to how a web browser does. Building on Sweigart's example, we could use requests to retrieve a web page from a particular URL. For example, the following code retrieves the results of a request (in this case, HTML code of my home page) and saves it in the specified variable.<jupyter_code>resp = requests.get('https://python.org') # retrieves the Python Foundation's webpage
resp # tell us what this is<jupyter_output><empty_output><jupyter_text>If you execute the code above successfully you should get something along the lines of ``, which denotes that this is a response object that was successful (HTTP's code for success). If we wanted to see the content of the response we could try typing the following.<jupyter_code>resp.text # give us the text result from the request<jupyter_output><empty_output><jupyter_text>The text generated above is actually the HTML code for *Python.org*. When rendered by a web browser, it creates a nice interface, though here it does not. You can take a look at the code next to the page in Chrome below.   Figure 3 - Demonstration of the web page code![alt text](img/4-3.png "Python.org") Similarly, we can make web requests to an API to retrieve data. For those of you who took INFO 5590, you may recall a lab where we retrieved JSON data using an API through either a web browser or Linux shell, possibly using the Open Library API. We will explore this in more detail throughout this lab. Open a web browser then copy and paste the following URL and see what happens: `http://openlibrary.org/search.json?q=brave+new+world`. You will likely be given a wall of JSON text. These are results of a request to the Open Library API for 'Brave New World', one of my all-time favorite fiction novels. We can retrieve the same results `requests` in Python.<jupyter_code># save the response as a variable and retrieve the JSON data

response = requests.get('http://openlibrary.org/search.json?q=brave+new+world') 
response.json() # show us the response in JSON format<jupyter_output><empty_output><jupyter_text>You can probably see where this is going. When we do this in Python we retrieve the data in JSON format easily and use it like a Python dictionary. This gives us a lot of power; in the wise words of <NAME> 'with great power comes great responsibility'.  *Challenge Question 3 (0.5 points)*The Open Library API provides [documentation on performing searches](https://openlibrary.org/dev/docs/api/search) using the API. In a previous example, we searched for <NAME>'s *Brave New World*. Modify the previously used code to conduct a search, retrieve the results as JSON data, and display the JSON data in Jupyter.__Hint:__ Don't overcomplicate this one. It is 0.5 points, which suggests that the answer is pretty simple :)<jupyter_code># conduct a different search and retrieve the JSON data<jupyter_output><empty_output><jupyter_text>*Challenge Question 4 (0.5 points)*Retrieve the number of items found from your search above. **Hint:** you should be able to do that by saving your response.json() as a variable and retrieving the dictionary value of the `num_found` key.<jupyter_code># save the JSON results as a variable and retrieve the num_found<jupyter_output><empty_output><jupyter_text>Objective 4: Use an API to retrieve Open Library collections dataLet's take a closer look at how the Open Library API works. [According to their documentation](https://openlibrary.org/dev/docs/api/books), we also have the ability to retrieve particular book information. The books are indexed by many keys, including ISBN numbers and a unique Open Library ID key (OLID). Using these keys we can retrieve data about the particular books in question.When you conducted a search for Brave New World earlier, you retrieved a series of OLID keys the first of which was `OL22123296M`. Using the first key in that set, we can retrieve the data for this particular collection item. Just like everything in this lab, we make a requests over the internet for using a specific URL. The structure of an Open Library query is as follows:- It begins with the call for book data (rather than, say, a search): `https://openlibrary.org/api/books?` - It then then adds the key information: `bibkeys=OLID:OL22123296M`- And completes by stating the desired format: `&format=json`This leads us to the following URL call: `https://openlibrary.org/api/books?bibkeys=OLID:OL22123296M&format=json`. Try calling this request below.<jupyter_code>import requests # retrieve the requests library
request = requests.get('https://openlibrary.org/api/books?bibkeys=OLID:OL22123296M&format=json')
bnw_info = request.json() # as before; show us the results in JSON
bnw_info<jupyter_output><empty_output><jupyter_text>The data saved in the `bnw` variable is now callable in a dictionary format. If we want to retrieve the preview URL we can execute the code below. Consider copying this into your web browser!<jupyter_code>bnw_info['OLID:OL22123296M']['preview_url'] # note that there are two levels in this dictionary<jupyter_output><empty_output><jupyter_text>*Challenge Question 5 (0.5 points)*In Challenge Question 3 you searched for a book and retrieved a series of OLID keys. Using one of these keys, in the cell below conduct a book query and provide the results using print or by calling the request.<jupyter_code># insert code here<jupyter_output><empty_output><jupyter_text>*Challenge Question 6 (0.5 points)*[Sweigart (2020)](https://automatetheboringstuff.com/2e/chapter12/) provides code for ordering Python to open your web browser to a specified URL. In theory, we could combine this code with the Open Library API to create a simple app for reading books. Retrieve the `preview_url` for your book as demonstrated above and use the `webbrowser.open()` function to order your web browser to open the book preview. You can use this skill in many different ways. I would quote Spiderman again, though it will lose its impact if done too much.<jupyter_code># insert code here<jupyter_output><empty_output><jupyter_text>Objective 5: Write API data to a CSVIts now time to bring this all together into a very handy skill: using Python to write API data into CSV files. This could very well make you a superstar in your office. It is often desirable to retrieve data from a JSON API, but many offices (including business analytics departments) are ill-prepared to work with that data.How do we get started? Let's start by taking a closer look at the API's GET response. The code below executes a request to the Open Library API to retrieve information for the work with id `OL64468W`. Unlike the search API request, this request will return information for Brave New World (by <NAME>) specifically, as that is the work associated with that tag.<jupyter_code>import requests # retrieve the requests library
response = requests.get('https://openlibrary.org/works/OL64468W.json') # retrieves work data from OL64468W
query = response.json() # as before
type(query) # tell us the type of the query that we made<jupyter_output><empty_output><jupyter_text>The `requests` (which in turn draws from the `json` library) makes it easy for us to work with the JSON data. The `query` is automatically saved as a Python dictionary. With this, we can do a lot, as we saw last week.As you may recall, Python dictionaries always have key-value pairs. Though values are normally retrieved by referencing its key, dictionaries also come with a few built-in methods to make our lives easier. For example, we can retrieve all of the keys from a dictionary using the `.keys()` method and all of the values using the `.values()` method. The code below demonstrates how we could do that with our Brave New World `query`.<jupyter_code>query.keys() # retrieve the keys from the query
query.values() # retrieve the values from the query<jupyter_output><empty_output><jupyter_text>By bringing these together, we can use the `key()` and `value()` methods with the CSV library to write the JSON data. The code below is a simple script for writing a single work that we could query. Consider executing this code and observing the result. Then consider changing the work_id to `OL450125W`... you will retrieve a different book!Very handy indeed!<jupyter_code># saves a CSV from our work_id query

import csv, requests

work_id = 'OL64468W' # the target work. Consider changing this to, say, OL450125W and see what happens
url = 'https://openlibrary.org/works/' + work_id + '.json' # add work_id to create url string

response = requests.get(url) # retrieve the response from the url, as before
query = response.json() # as before

with open('data/output.csv', 'w', newline='') as csvfile: # open a write file, as before
    csv_writer = csv.writer(csvfile, delimiter=',') # specify the writer
    csv_writer.writerow(query.keys()) # write a header row using the keys()
    csv_writer.writerow(query.values()) # write a subsequent row using the values()<jupyter_output><empty_output><jupyter_text>*Challenge Question 7 (1 point)*The code above, though handy, could be improved by allowing you to iterate through multiple works, rather than just one. Using the `scifi` list below, create a script that writes the JSON data for each work as a row of a CSV file. In doing so, you will have demonstrated knowledge of loops, lists, dictionaries, as well as APIs and the CSV library. If you achieve this milestone, you should celebrate! You have all of the programming knowledge you need to start doing data science. Though I am sure that you don't feel like a Python master, you have come remarkably far in a relatively short amount of time. From this point forward, we will not really expand our programing capabilities further. Believe it or not, next week's material will likely feel a ___lot less technical than this week's!___If you are interested in learning more about programming, consider finishing Sweigart's book. Though intermediate or advanced programing is not expected for this course, it could help you if you choose to pursue data science or other technical  professions.<jupyter_code># scifi lists a series of books that we would like to retrieve data from

scifi = [
    'OL64468W',
    'OL450125W',
    'OL52267W',
    'OL15396204W',
    'OL46404W'
]

# write your code below<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07091685609974513, 'fraction_numerical': 0.01952882827030378, 'mean_word_length': 4.707686259091803, 'pattern_counts': {'":': 4, '<': 101, '<?xml version=': 0, '>': 97, 'https://': 26, 'lorem ipsum': 0, 'www.': 7, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '471169', 'license': 'MIT', 'n_tokens_mistral': 8080, 'n_tokens_neox': 7524, 'n_words': 4013, 'path': 'Lab 4 - Accessing data sources.ipynb', 'repo_name': 'cdconrad/ds-2021', 'size': '29056'}