Project 1: Tables

# Project 1: Tables ## Due Date Information This project will be due before the end of the semester. ## Summary It’s time to try your data science skills on real datasets! For this assignment, you will choose one of three datasets to work on. You’ll then apply what we’ve learned so far to clean up the data, analyze the data, and write a report that presents the results of your analysis. There’s no difference in difficulty across the datasets – we’re merely letting you choose which dataset/question you are most interested in exploring. The project occurs in two stages. During the first week, you’ll work on the design of your tables and functions, reviewing your work Mr. Baker during a **Design Check**. In the second week, you’ll implement your design, presenting your results in a written report that includes charts and plots from the tables that you created. The report and the code you used to process the data get turned in for the Final Handin. This is a pair project. You and a partner should complete all project work together. You can find your own partner or we can match you with someone. Note that you have to work with different partners on each of the three projects in the course. ## Resources [https://hackmd.io/@cs111/table](https://hackmd.io/@cs111/table) ## Dataset Options Your dataset/question options are: * **Climate Change**: Annual CO2 emissions data since 1960 by country, with a second table showing temperature changes per country since 1960. Your analysis will look at how temperature changes relate to total emissions in different regions of the world. * **Bikeshare**: Bikeshare data from New York City for the month of October 2020, combined with a table of zipcodes in which bike stations are located. Your analysis will look at how far people are traveling and whether that varies by part of the city. * **Grocery Stores**: Data on the number of grocery/convenience stores per county in the USA, with another table of county-level population data. Your analysis will look at whether some states offer better access to grocery stores than others. Whichever dataset you choose, you will be given a collection of questions to answer in your analysis. You will also provide a function (`summary-generator`) that can be used to generate summary data about a specific aspect of your dataset. The `summary-generator` function will allow the user to customize which statistic (such as average, sum, median) gets used to generate the table data. Detailed instructions for accessing each dataset, and the corresponding analysis and summary requirements, are in the following expansion options. The rest of the handout (after the expansion options) explains general requirements that apply regardless of which option you have chosen. <div class="alert alert-info part" data-startline="46" data-endline="47" data-position="2938" data-size="998"> I suggest skimming the instructions for your chosen dataset, and then reading the design check instructions, and then reading the instructions for your chosen dataset in detail. The overarching goal of this project is to answer the analysis questions and to write and test the <code data-position="3225" data-size="17">summary-generator</code> function, and the rest of this handout walks you through those goals. We expect that the specific analysis questions and <code data-position="3366" data-size="17">summary-generator</code> function description will take a few read-throughs to thoroughly understand, and that you’ll switch between the general instructions and the specific instructions for your dataset while making your design check plan. Do not worry if you and your partner do not immediately arrive at the list of tasks you need to do in order to complete the project – one of the skills you are practicing is how to break a large analysis down into smaller steps. The design check with Mr. Baker will be one opportunity to let you know if you are on the right track. </div> ### CO2 Emissions <details class="part" data-startline="52" data-endline="161" data-position="3959" data-size="9173" open=""><summary>Instructions</summary> Overview: This dataset covers country-level carbon emissions and climate change measurements since 1960. The main table in this dataset (<code data-position="4127" data-size="20">co2-emissions-table </code>) gives the total and per-capita emissions for every country and year in the dataset. You are also given a table of all years from 1960 until the end of the dataset, a table of all regions, and a table that has a row for each country with the region it is in and its cumulative warming in degrees since 1960. Take a minute to load the tables (using the stencil code below) into Pyret and take a look at the contents of each table before continuing reading this section. Analysis: If you choose this dataset, your analysis should answer the following questions about the CO2 Emissions data: All analysis questions should be answered with both a visualization/chart and corresponding table <ul> <li class="" data-position="4851" data-size="0" data-startline-back="59" data-endline-back="60">Which region (third column in the <code data-position="4886" data-size="19">warming-deg-c-table</code>) had the highest cumulative CO2 emissions since 1960? <ul> <li class="" data-position="4972" data-size="0" data-startline-back="60" data-endline-back="60">Did this region always have the highest emissions in each year since 1960?</li> </ul> </li> <li class="" data-position="5049" data-size="0" data-startline-back="61" data-endline-back="61">Is there a relationship between cumulative emissions since 1960 and the increase in temperature since 1960 for countries?</li> <li class="" data-position="5173" data-size="0" data-startline-back="62" data-endline-back="63">Of the top 10 countries with the highest cumulative emissions since 1960, what proportion of these countries belong to each region?</li> </ul> Note: Use the following function that’s included in your stencil code instead of Pyret’s builtin <code data-position="5404" data-size="16">string-to-number</code> function! Take a look at the <code data-position="5452" data-size="5">where</code> block to see how it is used. <pre><code>fun string-to-num-project1(s :: String) -> Number: doc: "converts string to number value" n = string-to-number(s) cases (Option) n: | some(v) => v | none => raise("Non-number value passed in: " + s) end where: string-to-num-project1("3") is 3 string-to-num-project1("3.5") is 3.5 string-to-num-project1("-10") is -10 string-to-num-project1("hello") raises "Non-number value passed in: hello" end </code></pre> Summary Generator: You will also create a <code data-position="5983" data-size="17">summary-generator</code> function that can be used to generate summaries of country-level emissions and warming datasets. Imagine that there are multiple country-level emissions and warming datasets starting in 1960, and that you need to create summaries of them with different types of statistics. For example, in one case, you may get the dataset from 1960 through 2020 and need to find the total CO2 emissions over all years in the time period, over all of the countries in each region. Or in another case, you may get the dataset with a different set of years or countries, and need to find the average CO2 emissions over all years in the time period, over all of the countries in each region. This all requires building a function that is flexible in terms of which data it presents and what kind of statistics it computes. Your <code data-position="6826" data-size="17">summary-generator</code> will take in a table that contains the following columns: <ul> <li class="" data-position="6905" data-size="0" data-startline-back="91" data-endline-back="91">“year”: the year this row’s data is from</li> <li class="" data-position="6948" data-size="0" data-startline-back="92" data-endline-back="92">“country”: the name of the country that this row’s data is from</li> <li class="" data-position="7014" data-size="0" data-startline-back="93" data-endline-back="93">“total-co2-emission-metric-ton”: the country’s total emissions for the given year</li> <li class="" data-position="7098" data-size="0" data-startline-back="94" data-endline-back="94">“warming-deg-c-since-1960”: the total warming of the country in celsius over the entire timespan of the dataset, since 1960 (this means that any two rows with the same “country” value but different “year” values will have the same value in this column!)</li> <li class="" data-position="7360" data-size="0" data-startline-back="95" data-endline-back="96">“region”: the region of the world (Africa, Asia, Europe, Oceania, N. America, S. America, Other) the country is in</li> </ul> For example, the input table might look like <pre><code>| year | country | total-co2-emission-metric-ton | warming-deg-c-since-1960 | region | | ----- | -------- | ----------------------------- | ------------------------ | ---------- | | 1960 | Bolivia | emi1 | warm1 | S. America | | 1961 | Bolivia | emi2 | warm1 | ... | | 1960 | Peru | emi3 | warm2 | ... | | 1961 | Peru | ... | ... | ... | | ... | ... | ... | ... | ... | ... </code></pre> The <code data-position="8191" data-size="17">summary-generator</code> will also take in a <a href="https://hackmd.io/@cs111/table#Summarizing-Columns" target="_blank" rel="noopener">summary function</a>. Take a look at the functions (<code data-position="8333" data-size="3">sum</code>, <code data-position="8340" data-size="4">mean</code>, etc) on the summary function documentation. Each takes in a <code data-position="8408" data-size="5">Table</code> and <code data-position="8420" data-size="6">String</code> representing a column name, and applies the relevant math over that column of the table to produce a single <code data-position="8537" data-size="6">Number</code> (for example, <code data-position="8560" data-size="4">mean</code> produces the mean of all of the values in the column). The goal of your <code data-position="8640" data-size="17">summary-generator</code> is to figure out how to use the summary function and the input table to produce an output table with only these columns: “region”, “avg-warming”, and “CO2-summary”: <pre><code>| region | avg-warming | CO2-summary | | ------------- | ----------- | ----------- | | Oceania | avg-warm | num1 | | Africa | ... | ... | | Asia | ... | ... | | S. America | ... | ... | ... </code></pre> Each row represents the statistics for a single Region. The <code data-position="9175" data-size="11">avg-warming</code> column contains the average warming across all countries on that continent since 1960. The “region” and “avg-warming” columns will be the same for a given input table, no matter what summary function you give to your <code data-position="9408" data-size="17">summary-generator</code>. The <code data-position="9435" data-size="11">CO2-summary</code> column summarizes some statistic about the emissions per year across countries in the region since 1960, based on the summary function input. The CO2 statistic might be the total, average, median, etc emissions across countries in each region. For instance, if the <code data-position="9721" data-size="4">mean</code> function were passed into your <code data-position="9759" data-size="17">summary-generator</code> function, the <code data-position="9793" data-size="11">CO2-summary</code> column should contain the average CO2 emissions over all years of the data set, over all of the countries in that region. If the <code data-position="9938" data-size="3">sum</code> function were passed into your <code data-position="9975" data-size="17">summary-generator</code> function, the <code data-position="10009" data-size="11">CO2-summary</code> column should contain the total CO2 emissions over all years of the data set, over all of the countries in that region. For the <code data-position="10152" data-size="17">summary-generator</code> function, use the following header: <pre><code># the summary-func takes a smaller table and a column name (the String input) fun summary-generator(t :: Table, summary-func :: (Table , String -> Number)) -> Table: doc: ```Produces a table that uses the given function to summarize CO2 emissions for every region (Oceania/Asia/Europe/Africa/ SouthAmerica/NorthAmerica/Other). The outputted table should also have the average warming in every region.``` ... end </code></pre> This might be called as <code data-position="10680" data-size="31">summary-generator(mytable, sum)</code> or <code data-position="10717" data-size="32">summary-generator(mytable, mean)</code> to summarize the total or average CO2 emissions since 1960 across countries in each region as represented in <code data-position="10863" data-size="7">mytable</code>. Note: <code data-position="10886" data-size="3">sum</code> and <code data-position="10896" data-size="4">mean</code> here are built-in functions (that you do not write), as described above. Passing a function as an argument is like what you have done when using or table operations like <code data-position="11073" data-size="12">build-column</code>, <code data-position="11089" data-size="11">filter-with</code>, and <code data-position="11108" data-size="16">transform-column</code>. Your <code data-position="11134" data-size="17">summary-generator</code> function should not reference any tables from outside the function except the provided <code data-position="11245" data-size="13">regions-table</code>. While producing your output table, you should use <code data-position="11312" data-size="13">regions-table</code> as a starting point (to build columns for the output table and to extract data from the input table <code data-position="11428" data-size="1">t</code>). Also, your output table should not contain any columns other than those shown in the example above: “region”, “avg-warming”, and “CO2-summary”. Note: You do not need to test <code data-position="11617" data-size="17">summary-generator</code>. However, please run <code data-position="11658" data-size="17">summary-generator</code> twice outside of the function with two different summary functions. Make sure the output makes sense! This will look something like this: <pre><code>fun summary-generator(...): # your code end summary-generator(your-input-table, func1) summary-generator(your-input-table, func2) </code></pre> Hints: <ol data-position="11972" data-size="0"> <li class="" data-position="11975" data-size="0" data-startline-back="159" data-endline-back="159">You will have to construct an example input table with the column names “year,” “country,” “total-co2-emission-metric-ton,” “warming-deg-c-since-1960,” and “region” yourself. Plan out how you will do this in the design check! It will also help to manually create smaller input tables that you can use while developing the <code data-position="12298" data-size="17">summary-generator</code>.</li> <li class="" data-position="12321" data-size="0" data-startline-back="160" data-endline-back="160">The format of the output suggests that you will have to call the summary function once for every region to generate the specific summary value for that region’s row. The summary function takes in a <code data-position="12520" data-size="5">Table</code> and a <code data-position="12534" data-size="6">String</code>. For each region, what does the input table to the summary function look like in order to get the desired output? It may help to draw out an example table for a specific region. Then, think about how you to create those <code data-position="12763" data-size="5">Table</code>s out of the input table to <code data-position="12798" data-size="17">summary-generator</code>.</li> <li class="" data-position="12821" data-size="0" data-startline-back="161" data-endline-back="161">Go back to the analysis questions. Can you use tables created by your <code data-position="12892" data-size="17">summary-generator</code> to answer some of those questions? What summary functions would you use? Understanding this question will go a long way in helping you understand the goal of the <code data-position="13074" data-size="17">summary-generator</code> function and the entire assignment.</li> </ol> </details> <details class="part" data-startline="164" data-endline="232" data-position="13134" data-size="2231" open=""><summary>Stencil</summary> Copy and paste the following code to load the datasets into Pyret. <pre><code>include tables include gdrive-sheets include image include shared-gdrive("dcic-2021", "1wyQZj_L0qqV9Ekgr9au6RX2iqt2Ga8Ep") import math as M import statistics as S import data-source as DS google-id = "1igZMhJUpAiKg3U6775pcWFIRyDBNmTTLwlrXoUem29M" warming-deg-c-unsanitized-table = load-spreadsheet(google-id) co2-emissions-unsanitized-table = load-spreadsheet(google-id) years-1960-2014-unsanitized-table = load-spreadsheet(google-id) regions-unsanitized-table = load-spreadsheet(google-id) ################## Importing tables ################## warming-deg-c-table = load-table: country :: String, warming-deg-c-since-1960 :: String, region :: String source: warming-deg-c-unsanitized-table.sheet-by-name("warming-degc", true) sanitize country using DS.string-sanitizer sanitize warming-deg-c-since-1960 using DS.string-sanitizer sanitize region using DS.string-sanitizer end co2-emissions-table = load-table: year :: Number, country :: String, total-co2-emission-metric-ton :: Number, per-capita :: Number source: warming-deg-c-unsanitized-table.sheet-by-name( "fossil-fuel-co2-emissions-by-nation_csv", true) sanitize year using DS.strict-num-sanitizer sanitize country using DS.string-sanitizer sanitize total-co2-emission-metric-ton using DS.strict-num-sanitizer sanitize per-capita using DS.strict-num-sanitizer end years-1960-2014-table = load-table: year :: Number source: years-1960-2014-unsanitized-table.sheet-by-name("years", true) sanitize year using DS.strict-num-sanitizer end regions-table = load-table: region :: String source: regions-unsanitized-table.sheet-by-name("regions", true) sanitize region using DS.string-sanitizer end fun string-to-num-project1(s :: String) -> Number: doc: "converts string to number value" n = string-to-number(s) cases (Option) n: | some(v) => v | none => raise("Non-number value passed in: " + s) end where: string-to-num-project1("3") is 3 string-to-num-project1("3.5") is 3.5 string-to-num-project1("-10") is -10 string-to-num-project1("hello") raises "Non-number value passed in: hello" end </code></pre> </details> ### Bikeshare Data <details class="part" data-startline="237" data-endline="325" data-position="15387" data-size="7247"><summary>Instructions</summary> Overview: This dataset covers customers’ starting and stopping zip codes for a bikshare service. The data was collected over October 2020. The main table in this dataset (<code data-position="15588" data-size="27">october-2020-citibike-table</code>) shows information about the start/stop bikeshare stations customers have used and trip duration, as well as the age/gender of customers. Additionally, we include a table with the zipcodes of each bikeshare station, and a table of all zipcodes. Take a minute to load the tables (using the stencil code below) into Pyret and take a look at the contents of each table before continuing reading this section. Analysis: If you choose this dataset, your analysis should answer the following questions about the bikeshare data: All analysis questions should be answered with both a visualization/chart and corresponding table <ul> <li class="" data-position="16250" data-size="0" data-startline-back="243" data-endline-back="243">Which 5 zip codes were used the most number of times as start stations? As end stations?</li> <li class="" data-position="16341" data-size="0" data-startline-back="244" data-endline-back="244">Find the top 5 starting zipcodes for customers, and the top 5 starting zipcodes for subscribers. Compare these sets of zip codes. Are there any similarities? If there are similar zipcodes between the sets, are there more customers or subscribers at those zipcodes?</li> <li class="" data-position="16608" data-size="0" data-startline-back="245" data-endline-back="245">Find the top 5 starting zip codes for the following age groups: 20 or under, 21-30, 31-40, and 41 or older. Are there any similarities between these sets of zip codes?</li> <li class="" data-position="16778" data-size="0" data-startline-back="246" data-endline-back="248">Is there a relationship between the length of rides and gender of rider?</li> </ul> Summary Generator: You must now create a <code data-position="16899" data-size="17">summary-generator</code> function that can be used to generate summaries of bikeshare datasets. Imagine that there are many bikshare datasets, and that you need to create summaries of them with different types of statistics. For example, in one case, you may get the dataset from 2019 and need to find the average ride duration for each zipcode. Or in another case, you may get the dataset from 2022 and need to find the median ride duration for each zipcode. This all requires building a function that is flexible in terms of which data it presents and what kind of statistics it computes. Your <code data-position="17501" data-size="17">summary-generator</code> will take in a table with the following columns: <ul> <li class="" data-position="17571" data-size="0" data-startline-back="254" data-endline-back="254">“start-zip”: the starting zip code for a ride</li> <li class="" data-position="17619" data-size="0" data-startline-back="255" data-endline-back="255">“end-zip”: the ending zip code for a ride</li> <li class="" data-position="17663" data-size="0" data-startline-back="256" data-endline-back="256">“age”: the age of the rider</li> <li class="" data-position="17693" data-size="0" data-startline-back="257" data-endline-back="258">“duration”: the duration of the ride</li> </ul> For example, the input table might look like <pre><code> | start-zip| end-zip | age | duration | | -------- | -------- | -------- | -------- | | zip1 | zip2 | age1 | dur1 | | zip3 | zip4 | age2 | dur2 | | zip5 | zip6 | age3 | dur3 | | ... | ... | ... | ... | </code></pre> The <code data-position="18069" data-size="17">summary-generator</code> will also take in a <a href="https://hackmd.io/@cs111/table#Summarizing-Columns" target="_blank" rel="noopener">summary function</a>. Take a look at the functions (<code data-position="18211" data-size="3">sum</code>, <code data-position="18218" data-size="4">mean</code>, etc) on the summary function documentation. Each takes in a <code data-position="18286" data-size="5">Table</code> and <code data-position="18298" data-size="6">String</code> representing a column name, and applies the relevant math over that column of the table to produce a single <code data-position="18415" data-size="6">Number</code> (for example, <code data-position="18438" data-size="4">mean</code> produces the mean of all of the values in the column). The goal of your <code data-position="18518" data-size="17">summary-generator</code> is to figure out how to use the summary function and the input table to produce an output table with only these columns: “zipcode”, “average-age”, and “duration-summary”: <pre><code>| zipcode | average-age | duration-summary | | ------- | ---------- | ---------------- | | 10020 | 24.7 | 853.7 | | 11101 | ... | ... | | 10451 | ... | ... | | 11237 | ... | ... | ... </code></pre> Each row has data for a particular zipcode. The <code data-position="19043" data-size="11">average-age</code> column contains the average age of the riders for each ride that started or ended in that zipcode. The <code data-position="19162" data-size="7">zipcode</code> and <code data-position="19176" data-size="11">average-age</code> columns will be the same for a given input table, no matter what summary function you give to your <code data-position="19289" data-size="17">summary-generator</code>. The <code data-position="19316" data-size="16">duration-summary</code> column summarizes some statistic about the durations of the unique rides around that zipcode, based on the summary function input. The <code data-position="19474" data-size="16">duration-summary</code> statistic might be the total duration, the average duration, the median, and so on. For instance, if the <code data-position="19600" data-size="4">mean</code> function were passed into your <code data-position="19638" data-size="17">summary-generator</code> function, the <code data-position="19672" data-size="16">duration-summary</code> column should contain the average duration over all rides of the data set. If the <code data-position="19773" data-size="3">sum</code> function were passed into your <code data-position="19810" data-size="17">summary-generator</code> function, the <code data-position="19844" data-size="16">duration-summary</code> column should contain the total duration over all rides of the data set. For the <code data-position="19945" data-size="17">summary-generator</code> function, use the following header: <pre><code>fun summary-generator(t :: Table, summary-func :: (Table , String -> Number))-> Table: doc: ```Produces a table that uses the given function to summarize duration of rides across zipcodes. The outputted table should also have average age of riders for each zipcdoe.``` ... end </code></pre> This might be called as <code data-position="20326" data-size="32">summary-generator(mytable, sum)</code> or <code data-position="20364" data-size="32">summary-generator(mytable, mean)</code> to summarize the total or average duration of rides in each zipcode represented in <code data-position="20482" data-size="7">mytable</code>. Note: <code data-position="20504" data-size="3">sum</code> and <code data-position="20514" data-size="4">mean</code> here are built-in functions (that you do not write), as described above. Passing a function as an argument is like what you have done when using or table operations like <code data-position="20691" data-size="12">build-column</code>, <code data-position="20707" data-size="11">filter-with</code>, and <code data-position="20726" data-size="16">transform-column</code>. Your <code data-position="20752" data-size="17">summary-generator</code> function should not reference any tables from outside the function except the provided <code data-position="20863" data-size="22">sorted-zip-codes-table</code>. While producing your output table, you should build columns to <code data-position="20952" data-size="22">sorted-zip-codes-table</code>. Also, your output table should not contain any columns other than those shown in the example above: “zipcode”, “average-age” and “duration-summary”. Note: You do not need to test <code data-position="21162" data-size="17">summary-generator</code>. However, please run <code data-position="21203" data-size="17">summary-generator</code> twice outside of the function with two different summary functions. Make sure the output makes sense! This will look something like this: <pre><code>fun summary-generator(...): # your code end summary-generator(your-input-table, func1) summary-generator(your-input-table, func2) </code></pre> Hints: <ol data-position="21517" data-size="0"> <li class="" data-position="21520" data-size="0" data-startline-back="323" data-endline-back="323">You will have to construct an example input table with the column names “start-zip,” “end-zip,” “age,” and “duration” yourself. Plan out how you will do this in the design check! It will also help to manually create smaller input tables that you can use while developing the <code data-position="21796" data-size="17">summary-generator</code>.</li> <li class="" data-position="21819" data-size="0" data-startline-back="324" data-endline-back="324">The format of the output suggests that you will have to call the summary function once for every zipcode to generate the specific summary value for that zipcode’s row. The summary function takes in a <code data-position="22020" data-size="5">Table</code> and a <code data-position="22034" data-size="6">String</code>. For each zipcode, what does the input table to the summary function look like in order to get the desired output? It may help to draw out an example table for a specific zipcode. Then, think about how you to create those <code data-position="22265" data-size="5">Table</code>s out of the input table to <code data-position="22300" data-size="17">summary-generator</code>.</li> <li class="" data-position="22323" data-size="0" data-startline-back="325" data-endline-back="325">Go back to the analysis questions. Can you use tables created by your <code data-position="22394" data-size="17">summary-generator</code> to answer some of those questions? What summary functions would you use? Understanding this question will go a long way in helping you understand the goal of the <code data-position="22576" data-size="17">summary-generator</code> function and the entire assignment.</li> </ol> </details> <details class="part" data-startline="328" data-endline="383" data-position="22636" data-size="2054"><summary>Stencil</summary> Copy and paste the following code to load the datasets into Pyret. <pre><code>include tables include gdrive-sheets include shared-gdrive("dcic-2021", "1wyQZj_L0qqV9Ekgr9au6RX2iqt2Ga8Ep") import math as M import statistics as S import data-source as DS google-id = "1iSAp4AXNNcfdxm7cBCSPcy_KPINpgf0nPYGvxxIZXV4" october-2020-citibike-unsanitized-table = load-spreadsheet(google-id) stations-unsanitized-table = load-spreadsheet(google-id) sorted-zip-codes-unsanitized-table = load-spreadsheet(google-id) #| Note: for the gender column 0 represents unknown 1 represents male 2 represents female |# october-2020-citibike-table = load-table: trip-duration :: Number, start-time :: String, stop-time :: String, start-station-id :: Number, start-station-name :: String, end-station-id :: Number, end-station-name :: String, bike-id :: Number, user-type :: String, birth-year :: Number, gender :: Number source: october-2020-citibike-unsanitized-table.sheet-by-name("october-2020-citibike-sample", true) sanitize trip-duration using DS.strict-num-sanitizer sanitize start-time using DS.string-sanitizer sanitize stop-time using DS.string-sanitizer sanitize start-station-id using DS.strict-num-sanitizer sanitize start-station-name using DS.string-sanitizer sanitize end-station-id using DS.strict-num-sanitizer sanitize end-station-name using DS.string-sanitizer sanitize bike-id using DS.strict-num-sanitizer sanitize user-type using DS.string-sanitizer sanitize birth-year using DS.strict-num-sanitizer sanitize gender using DS.strict-num-sanitizer end stations-table = load-table: station-name :: String, zipcode :: Number source: stations-unsanitized-table.sheet-by-name("station-dataset", true) sanitize station-name using DS.string-sanitizer sanitize zipcode using DS.strict-num-sanitizer end sorted-zip-codes-table = load-table: zipcode :: Number source: sorted-zip-codes-unsanitized-table.sheet-by-name("zipcodes", true) sanitize zipcode using DS.strict-num-sanitizer end </code></pre> </details> ### Grocery Stores <details class="part" data-startline="388" data-endline="481" data-position="24712" data-size="7894"><summary>Instructions</summary> Overview: This dataset covers the county-level quantities of grocery and convenience stores, as well as county populations. The main table (<code data-position="24882" data-size="24">county-store-count-table</code>) shows the numbers of grocery and convenience stores in counties. You are also given a table with the populations of counties, as well as a table of state abbreviations. Take a minute to load the tables (using the stencil code below) into Pyret and take a look at the contents of each table before continuing reading this section. Analysis: If you choose this dataset, your analysis should answer the following questions about the grocery and population data: All analysis questions should be answered with both a visualization/chart and corresponding table Note: “combined stores” here refers to the sum of grocery and convenience stores. <ul> <li class="" data-position="25564" data-size="0" data-startline-back="398" data-endline-back="398">Which state has the largest number of combined stores per capita?</li> <li class="" data-position="25633" data-size="0" data-startline-back="399" data-endline-back="399">Do states with the largest populations also have the most combined stores?</li> <li class="" data-position="25710" data-size="0" data-startline-back="400" data-endline-back="400">Is there a correlation between county populations and the number of combined stores per capita in the states to which the counties belong?</li> <li class="" data-position="25851" data-size="0" data-startline-back="401" data-endline-back="404">Which 5 states have the largest ratio of convenience stores to combined stores?</li> </ul> Summary Generator: You must now create a <code data-position="25980" data-size="17">summary-generator</code> function that can be be used to generate summaries of county-level store datasets. Imagine that there are many county-level store datasets in the US, and that you need to create summaries of them with different types of statistics. For example, in one case, you may get the dataset from 1970 and need to find the maximum stores-per-capita value for counties in each state. Or in another case, you may get the dataset from 2021 and need to find the average stores-per-capita for counties in each state. This all requires building a function that is flexible in terms of which data it presents and what kind of statistics it computes. Your <code data-position="26649" data-size="17">summary-generator</code> will take in a table that contains the following columns: <ul> <li class="" data-position="26729" data-size="0" data-startline-back="411" data-endline-back="411">“county”: the county’s name</li> <li class="" data-position="26759" data-size="0" data-startline-back="412" data-endline-back="412">“state”: the county’s state, either as the state’s full name or abbreviation</li> <li class="" data-position="26838" data-size="0" data-startline-back="413" data-endline-back="413">“num-stores-county”: the total number of grocery and convenience stores in the county</li> <li class="" data-position="26926" data-size="0" data-startline-back="414" data-endline-back="415">“stores-per-capita-county”: the total number of grocery and convenience stores in the county, divided by the county’s population</li> </ul> For example, the input table might look like <pre><code>| county | state | num-stores-county | stores-per-capita-county | | ------------- | ----- | ----------------- | ------------------------ | | county 1 | RI | num1. | num2. | | county 2 | CO | ... | ... | | county 3 | MD | ... | ... | | county 4 | SD | ... | ... | ... </code></pre> The <code data-position="27558" data-size="17">summary-generator</code> will also take in a <a href="https://hackmd.io/@cs111/table#Summarizing-Columns" target="_blank" rel="noopener">summary function</a>. Take a look at the functions (<code data-position="27700" data-size="3">sum</code>, <code data-position="27707" data-size="4">mean</code>, etc) on the summary function documentation. Each takes in a <code data-position="27775" data-size="5">Table</code> and <code data-position="27787" data-size="6">String</code> representing a column name, and applies the relevant math over that column of the table to produce a single <code data-position="27904" data-size="6">Number</code> (for example, <code data-position="27927" data-size="4">mean</code> produces the mean of all of the values in the column). The goal of your <code data-position="28007" data-size="17">summary-generator</code> is to figure out how to use the summary function and the input table to produce an output table with only these columns: “state”, “abbv”, “num-stores”, and “per-capita-summary”: <pre><code>| state | abbv | num-stores | per-capita-summary | | ------------- | ----- | ---------- | ------------------ | | Rhode Island | RI | 145,000 | 0.001 | | Colorado | CO | ... | ... | | Maryland | MD | ... | ... | | South Dakota | SD | ... | ... | ... </code></pre> Each row is a State. The <code data-position="28604" data-size="10">num-stores</code> column contains the total number of grocery and convenience stores in that state. The “state”, “abbv” and “num-stores” columns will be the same for a given input table, no matter what summary function you give to your <code data-position="28837" data-size="17">summary-generator</code>. The <code data-position="28864" data-size="18">per-capita-summary</code> column summarizes some statistic about the total number of stores (grocery and convenience) per capita across counties in that state, , based on the summary function input. The per-capita statistic might be the total, average, median, etc stores per capita across counties in that state. For instance, if the <code data-position="29202" data-size="4">mean</code> function were passed into your <code data-position="29240" data-size="17">summary-generator</code> function, the <code data-position="29274" data-size="18">per-capita-summary</code> column should contain the average value of total stores per capita across all counties in the state for that row. If the <code data-position="29418" data-size="3">sum</code> function were passed into your <code data-position="29455" data-size="17">summary-generator</code> function, the <code data-position="29489" data-size="18">per-capita-summary</code> column should contain the sum of the total stores per capita across all the counties in that state. The person who calls your <code data-position="29640" data-size="17">summary-generator</code> function will indicate which summary method to use by passing another function as input. For the <code data-position="29758" data-size="17">summary-generator</code> function, use the following header: <pre><code>fun summary-generator(t :: Table, summary-func :: (Table , String -> Number))-> Table: doc: ```Produces a table that uses the given function to summarize stores per capita across counties. The outputted table should also have total number of grocery and convenience stores for every state.``` ... end </code></pre> This might be called as <code data-position="30169" data-size="31">summary-generator(mytable, sum)</code> or <code data-position="30206" data-size="32">summary-generator(mytable, mean)</code> to summary the total or average CO2 emissions since 1960 across countries in each region as represented in <code data-position="30350" data-size="7">mytable</code>. Note: <code data-position="30373" data-size="3">sum</code> and <code data-position="30383" data-size="4">mean</code> here are built-in functions (that you do not write), as described above. Passing a function as an argument is like what you have done when using or table operations like <code data-position="30560" data-size="12">build-column</code>, <code data-position="30576" data-size="11">filter-with</code>, and <code data-position="30595" data-size="16">transform-column</code>. Your <code data-position="30621" data-size="17">summary-generator</code> function should not reference any tables from outside the function except the provided <code data-position="30732" data-size="16">state-abbv-table</code>. While producing your output table, you should use <code data-position="30802" data-size="16">state-abbv-table</code> as a starting point (to build columns for the output table and to extract data from the input table <code data-position="30921" data-size="1">t</code>). Also, your output table should not contain any columns other than those shown in the example above: “state”, “abbv”, “num-stores” and “per-capita-summary” Note: You do not need to test <code data-position="31117" data-size="17">summary-generator</code>. However, please run <code data-position="31158" data-size="17">summary-generator</code> twice outside of the function with two different summary functions. Make sure the output makes sense! This will look something like this: <pre><code>fun summary-generator(...): # your code end summary-generator(your-input-table, func1) summary-generator(your-input-table, func2) </code></pre> Hints: <ol data-position="31472" data-size="0"> <li class="" data-position="31475" data-size="0" data-startline-back="478" data-endline-back="478">You will have to construct an example input table with the column names “state”, “county,” “num-stores-county” and “stores-per-capita-county” yourself. Plan out how you will do this in the design check! It will also help to manually create smaller input tables that you can use while developing the <code data-position="31775" data-size="17">summary-generator</code>.</li> <li class="" data-position="31798" data-size="0" data-startline-back="479" data-endline-back="479">The format of the output suggests that you will have to call the summary function once for every state to generate the specific summary value for that state’s row. The summary function takes in a <code data-position="31995" data-size="5">Table</code> and a <code data-position="32009" data-size="6">String</code>. For each state, what does the input table to the summary function look like in order to get the desired output? It may help to draw out an example table for a specific state. Then, think about how you to create those <code data-position="32236" data-size="5">Table</code>s out of the input table to <code data-position="32271" data-size="17">summary-generator</code>.</li> <li class="" data-position="32294" data-size="0" data-startline-back="480" data-endline-back="481">Go back to the analysis questions. Can you use tables created by your <code data-position="32365" data-size="17">summary-generator</code> to answer some of those questions? What summary functions would you use? Understanding this question will go a long way in helping you understand the goal of the <code data-position="32547" data-size="17">summary-generator</code> function and the entire assignment.</li> </ol> </details> <details class="part" data-startline="484" data-endline="524" data-position="32608" data-size="1554"><summary>Stencil</summary> Copy and paste the following code to load the dataset into Pyret: <pre><code>include tables include gdrive-sheets include shared-gdrive("dcic-2021", "1wyQZj_L0qqV9Ekgr9au6RX2iqt2Ga8Ep") import math as M import statistics as S import data-source as DS google-id = "17OCB7nDBepuvxHrDKB4qMPcI0_UHbTzNwMP_2s0WkXw" county-population-unsanitized-table = load-spreadsheet(google-id) county-store-count-unsanitized-table = load-spreadsheet(google-id) state-abbv-unsanitized-table = load-spreadsheet(google-id) county-population-table = load-table: county :: String, state :: String, population-estimate-2016 :: Number source: county-population-unsanitized-table.sheet-by-name("county-population", true) sanitize county using DS.string-sanitizer sanitize state using DS.string-sanitizer sanitize population-estimate-2016 using DS.strict-num-sanitizer end county-store-count-table = load-table: state :: String, county :: String, num-grocery-stores :: Number, num-convenience-stores :: Number source: county-store-count-unsanitized-table.sheet-by-name("county-store-count", true) sanitize state using DS.string-sanitizer sanitize county using DS.string-sanitizer sanitize num-grocery-stores using DS.strict-num-sanitizer sanitize num-convenience-stores using DS.strict-num-sanitizer end state-abbv-table = load-table: state :: String, abbv :: String source: state-abbv-unsanitized-table.sheet-by-name("state-abbv", true) sanitize state using DS.string-sanitizer sanitize abbv using DS.string-sanitizer end </code></pre> </details> --- ## Deadline 1: The Design Stage The design check is a 30-minute one-on-one meeting between your team and a TA to review your project plans and to give you feedback well before the final deadline. Many students make changes to their designs following the check: doing so is common and will not cost you points.