From a CSV file to a dataframe

TL;DR#

Today we sent data to the course API with a POST, saved every town in Massachusetts to a CSV file, read the file back, and answered one question with a loop, in polars and in SQL. The habit worth keeping: everything you read back from a CSV file is text until you convert it. int() comes before any comparison or sum.

What we covered#

A function takes its values from the call. compute_bmi(height, weight) says in its first line what to pass in. If the body asks for input() again, compute_bmi(1.75, 70) throws away 1.75 and 70 and uses whatever is typed. Keep input() in main(), and let the function use its parameters.

Each item in a list of dictionaries is a whole dictionary. In for person in people:, person is {"name": "Carla", "bmi": 27.1} the first time round, and person["name"] gives the name alone.

An API sends one page at a time. /ma/towns?county=Worcester replies with 50 towns, while reply["pagination"]["total"] says 60. Ask for per_page=100, as get_towns in notebook 5 does, or ask for the next page. Full reference: notebook 4, section 3, More Than One Page.

Decomposition and abstraction are two different moves. Decomposition breaks a problem into pieces small enough to write: find a restaurant, place the order, send a driver. Abstraction uses a piece by what it takes and what it gives back, without reading its inside, the way you call requests.get or compute_cost(holdings). Alfred North Whitehead put it this way in An Introduction to Mathematics (1911): "Civilization advances by extending the number of important operations which we can perform without thinking about them."

A search box is a GET form. In the page's HTML, the form's action says where the request goes and the input's name is the parameter. Google's sends /search?q=babson. Change action to Amazon's /s and name to k, and the same box searches Amazon. params={"county": "Worcester"} builds the same kind of address in Python.

A POST carries its data in the body. A login form uses POST, so your password never shows in the address bar. The board's json= puts your message in the body, and the x-token header says who is posting.

A saved file answers without the network. An API can go down, change, limit how often you ask, or start charging. A CSV file on disk gives the same answer next week.

A CSV file has no types. csv.DictReader gives every value back as a string, so town["average_single_family_value_dollars"] >= 1000000 stops with a TypeError. Wrap the value in int() first. Writing the number back to the file stores its digits as characters again. CSV suits a table; JSON suits data with lists inside dictionaries.

Start from the input and the output, then write the loop. For A step 1 the input is towns_from_file, a list of dictionaries. The output pairs each county with a count, so it is a dictionary. Then the loop is short: for each town worth $1,000,000 or more, add one to its county. counts[county] = counts.get(county, 0) + 1 does the "first time, start at zero" step in one line.

A dataframe is a table with named columns, the same idea as a data frame in R. pl.read_csv reads the file and works out which columns are numbers, so you do not convert them yourself, and .schema shows the type of each. If you know dplyr, polars reads much the same way: filter, group_by, then agg where dplyr has summarize, and sort where it has arrange.

A count can mislead. Middlesex has the most million-dollar towns, 14, and it also has 54 towns in all. By share, Nantucket and Dukes come first, with every town worth $1,000,000 or more, then Barnstable, Essex and Norfolk. An agent writes the code for either number; deciding which one answers the question is your call. The chart in section 4 draws the share.

A loop, polars and SQL give the same counts. mo.sql runs the query through DuckDB, which reads town_table directly, with no database server to set up.

What equalized_value_dollars means. It is the state's equalized valuation (EQV): the Massachusetts Department of Revenue's estimate of the full market value of all taxable property in a town. average_single_family_value_dollars is what an average single-family home is assessed at.

A CSV file opens in VS Code or in Excel. The VS Code extension Rainbow CSV colors each column.

If your notebook 5 looks different#

Notebook 5 changed shortly before class. If you downloaded it early, download it again from the session page and replace your copy in notebooks/.

Public links#

• Session page, with the slides and notebook 5
• Mini Project 2
• Course API documentation
• Python §8, Functions

Before next session#

1. Mini Project 2 is due Sunday 10/25. Submit the link to your notebook inside projects/mp2/. The instructions are on the Mini Project 2 page.
2. Notebook 4, section 4, 04-functions-and-apis.py.
3. Notebook 5, sections 1 to 4, 05-csv-and-dataframes.py: A to D.

Checkpoint 3 is due Sunday 10/25. The full checklist is on Canvas.