Let’s work backwards…
… and answer the last SRS Challenge first… you remember, the one about finding the largest US city with a very low population density!
I’ll answer the previous Challenge (“rusty thing I found in the woods”) before next week and get us back on track.
If you recall, our Challenge was: “what large US city has the lowest population density as of 2020?”
There are tables one can find that will tell you one answer, but I’d like you to solve this Challenge in a more direct way–a way that will teach you how to download data directly into a table and then manipulate it yourself.
Can you do this hands-on data manipulation Challenge?
Here’s what I want you to do:
1. Search for a table of the largest US cities by population. You’ll want to find a table with at least 330 entires in it.
2. Download that table into a spreadsheet.
3. Compute the population density (if you need to… it might be a column in the data set).
4. Sort the table by density, and then tell us what the city name is!
Your table should look like the one above (hint: I got it from Wikipedia, but you can find your own source if you’d like–the diversity of data sources might be interesting).
Here’s what I did…
First search for the Wikipedia table. My query was:
[ wikipedia table largest US cities population ]
which led me to the Wikipedia table List of US cities by population. It’s a classic Wikipedia entry with all the standard data disclaimers and information (e.g., This table lists the 331 incorporated places in the United States (excluding the U.S. territories) with a population of at least 100,000 on July 1, 2021, as estimated by the United States Census Bureau. The table displays: (a) The city rank by population as of July 1, 2021, as estimated by the United States Census Bureau, (b) The city name, etc etc etc.)
If you look carefully, you’ll see that the columns can be sorted by clicking on the sorting widget at the top of each column in the table. (You need to recognize that that’s what these widgets do–it’s part of your SRS visual literacy: recognize and understand what UI widgets are and what they do.)
If you click on that widget, you can sort by population density:
And voila, you’ve got the answer. That’s the fast and easy way to get to the answer.
BUT... the point of the Challenge is to get you to figure out how to download this table and then manipulate it to get to the same answer. (That is, the pedagogical point of this Challenge is to learn how to download data tables from the web and then how to work with them.)
Do you know how to pull data tables off the internet and into your favorite spreadsheet?
Well, the obvious SRS way to find out is with a search:
[ how to import data tables into Google sheets ]
which will lead you to a number of sources, including this well-written and extensive post by Parul Pandey about Importing HTML tables into Google Sheets or this YouTube video from Teacher’s Tech about How to Import Data from Webpages into Google Sheets. These are excellent resources, and for full details about how to do this, I recommend those page.
For our purposes, I’ll cut to the chase and point you to my Google Sheet with the population data in it. This sheet looks like this:
There are a couple of things to note here. First, cell A1 has the magic function in it:
=ImportHTML (URL, “table”, 5)
And that LOOKS right. But as we know, appearances can be deceiving. I thought it was right, but when I sorted the column, I noticed that the sequence was bizarre. I saw patterns in the data that looked like this:
So… there’s a quick and easy way (just use the built-in sort on the Wikipedia page), and also a more sophisticated way to download the data to your own sheet. Of course, this then allows you to do more things with it–say, create a chart like this: