CS303E Project 3

Instructor: Dr. Bill Young
Due Date: Monday, May 3 at 11:59pm

Building a Query Processing Utility

A common type of application allows the user to input queries and retrieve information from a database. In this assignment, you will be building such a system to allow requesting information about totals of Covid-19 cases and deaths in Texas counties and statewide. The database will be in the form of a dictionary with Texas county names as keys and pairs of integers as values.

The raw information is in a file which you can download here: Input File. You must name your input file county-covid-data.txt, so that the TAs can run your code on their version of this file. The first few lines in the file are: 

# County,Confirmed Cases,Probable Cases,Deaths
Anderson,4595,1137,85
Andrews,1630,0,37

Note that some lines begin with a '#'; you'll ignore those. Other lines have the following four fields separated by commas: a Texas county name, confirmed Covid cases in the county (as of a certain date), probable cases in the county, and total Covid deaths in the county. We will ignore the probable cases field.

Open and read the file. For each line that doesn't start with '#', create a record in a dictionary of the form: [ countyName: (confirmedCases, deaths) ]. That is, the key is the county name (as a string), and the value is a tuple containing two integers (remember to convert them to ints). While you're reading in this data, also sum up the confirmed cases and deaths for all counties so that you can insert one final item for the entire state of Texas. That is, suppose the only counties in the file were Anderson and Andrews as shown above. Then your dictionary would have the form: 

{ Anderson: (4595, 85), Andrews: (1630, 37), Texas: (6225, 122) }
While you're doing the above, also create a list of all the county names in the file. In this case, it would be the list: 
[ Anderson, Andrews ]
I promise, it's the complete list of Texas counties and each appears only once. Also, the counties are in alphabetical order. Recent versions of Python (after 3.7) guarantee that dictionary keys are maintained in insertion order. So, if you insert them in order, they'll stay in order.

Notice that creating the list of county names is not strictly necessary. It would be the same as the list of keys in the dictionary (except for 'Texas'). But it would be inefficient to grab the keys each time you want to Counties. So create the list as suggested.

Once you have the database (dictionary) and list of counties, you will build a query processing system that allows a user to ask questions about the number of cases and deaths in specific counties or statewide. The available commands are these: help, quit, counties, cases (by county or statewide), and deaths (by county or statewide). See examples of the use of these below.

Your program will initially print a Welcome message, which should look like this: 

Welcome to the Texas Covid Database Dashboard.
This provides Covid data in Texas as of 1/26/21.
Creating dictionary from file: county-covid-data.txt

Enter any of the following commands:
Help - list available commands;
Quit - exit this dashboard;
Counties - list all Texas counties;
Cases <countyName>/Texas - confirmed Covid cases in specified county or statewide;
Deaths <countyName>/Texas - Covid deaths in specified county or statewide.
Note that the commands print in bold (as do the prompts for user input below). To print a string in bold in Python, put "\033[1m" right before the string and "\033[0m" immediately after it. Making it bold is optional; at least one of the TAs couldn't get it to work. If you can't, don't worry about it.

After printing the welcome message, enter a loop to accept commands from the user. You will prompt the user to enter any of these commands and respond appropriately. User input should not be sensitive to case. However, be aware that all county names in the database are capitalized and some of them have two words. None have more than two words, but don't rely on that fact. If you need the original form of a county name, as you will whenever you're indexing into the dictionary, you can use the s.title() string method, which capitalizes each word in string s. Thus if the user enters 

cases san saba
you'll need to access the confirmed cases field in dictionary record for county "San Saba".

Update (5/1): It's been pointed out to me that the above doesn't quite work. If you use the original county name as a key in the database, it's not always the case that name.title() gets you back to the right string. It doesn't work for counties McCulloch, McLennan, and McMullen. The right thing to do might have been to use the lowercase version of the county name as the database key, and also store a "display" version. You don't need to do that. We won't test your code on those three county names, and the algorithm should work fine for the others.

Here is some code that can help you parse a command that may contain a two word county name. You are welcome to use this code, or write your own. Assume that you've read the command submitted by the user into a string variable named commandInput 

# Parse the command into a list of words (assuming there's no punctuation).
commWords = commandInput.split()

# Extract the first word in the command (which is always a one-word command):
comm = commWords[0]

# Extract the rest of the words and re-assemble them into a single string, 
# separated by spaces. 
args = commWords[1:]
arg = " ".join(args)
Following this code, comm will contain the initial command word and arg will contain the argument, if any. This preserves the case, but you can use arg.lower() to lowercase it, and arg.title() to capitalize each word. You'll probably need both, particularly when dealing with county names.

For the Help command, print the command list exactly as in the Welcome message. I bolded the commands in the help message, but you don't have to.

For Quit, print a Goodbye message and exit the program.

For Counties, print the counties 10 per line. I didn't bother to remove the comma after Zavala, but you can if you like.

For Cases the user can specify a county name or 'Texas'. Print the appropriate answer. Notice that the output format is slightly different for an individual county or a statewide result.

Deaths is handled similarly to Cases.

The Algorithm:

Don't attempt to do this entire assignment at once; break it into pieces and test each thoroughly before going on to the next one. Perhaps create a version of the input file with only 3-4 lines for testing.

Here is a suggestion of some steps to carry out this assignment.

  1. Write a separate function that reads lines from the input file and creates two data structures: a dictionary and a list of county names.
    1. Assume for this function that the input file exists.
    2. Read a line from the file; if it starts with '#' discard it. Otherwise, parse it into four fields: countyName, confirmedCases, probableCases, deaths.
    3. Convert confirmedCases and deaths into integers; you may have to strip a newline from deaths, since it's at the end of the line.
    4. Keep a running total of confirmedCases and of deaths; you'll need those later.
    5. Also keep a running list of county names.
    6. Associate the pair (confirmedCases, deaths) with countyName in the dictionary.
    7. Repeat the steps above for all lines in the file.
    8. After you've processed all lines in the file, add one more record to the dictionary associating the total confirmed cases and total deaths with key 'Texas'.
    9. Finally, return the pair (dictionary, list of county names).

  2. Now you'll build the query processing functionality. This will be your main function.
    1. Check that the input file exists; if not, print the error message "File county-covid-data.txt not found." and exit. You'll need to import os.path for this.
    2. From the input file, build your database (dictionary) and county list using the function described above.
    3. Print the welcome message.
    4. Accept commands from the user, parse them and process them. (Remember that case doesn't matter, but be sure to capitalize county names when using them as dictionary keys.)
    5. Individual commands should be handled as follow:
      1. Given a Help command, print the Help message.
      2. Given a Quit command, say goodbye and exit.
      3. Given a Counties command, print the list of counties, 10 per line, from the list you've accumulated.
      4. Given a Cases command, if the input is 'Texas', get that info from the database. If a county, see if it's a Texas county and acceess the info from database. If not, print an error message. For this error you can use the county name as entered or in the .title() form.
      5. The Deaths command is handled similarly to the Cases command.
      6. Any other commands should be rejected.
Notice that creating the list of county names is not strictly necessary. It should be the same as the list of keys in the dictionary (except for 'Texas'). But it would be inefficient to grab the keys each time you want to Counties. So create the list as suggested.

Sample Behavior:

> python3 Project3.py

Welcome to the Texas Covid Database Dashboard.
This provides Covid data in Texas as of 1/26/21.
Creating dictionary from file: county-covid-data.txt 

Enter any of the following commands:
Help  - list available commands;
Quit - exit this dashboard;
Counties - list all Texas counties;
Cases <countyName>/Texas - confirmed Covid cases in specified county or statewide;
Deaths <countyName>/Texas - Covid deaths in specified county or statewide.

Please enter a command: counties
Anderson, Andrews, Angelina, Aransas, Archer, Armstrong, Atascosa, Austin, Bailey, Bandera, 
Bastrop, Baylor, Bee, Bell, Bexar, Blanco, Borden, Bosque, Bowie, Brazoria, 
Brazos, Brewster, Briscoe, Brooks, Brown, Burleson, Burnet, Caldwell, Calhoun, Callahan, 
Cameron, Camp, Carson, Cass, Castro, Chambers, Cherokee, Childress, Clay, Cochran, 
Coke, Coleman, Collin, Collingsworth, Colorado, Comal, Comanche, Concho, Cooke, Coryell, 
Cottle, Crane, Crockett, Crosby, Culberson, Dallam, Dallas, Dawson, DeWitt, Deaf Smith, 
Delta, Denton, Dickens, Dimmit, Donley, Duval, Eastland, Ector, Edwards, El Paso, 
Ellis, Erath, Falls, Fannin, Fayette, Fisher, Floyd, Foard, Fort Bend, Franklin, 
Freestone, Frio, Gaines, Galveston, Garza, Gillespie, Glasscock, Goliad, Gonzales, Gray, 
Grayson, Gregg, Grimes, Guadalupe, Hale, Hall, Hamilton, Hansford, Hardeman, Hardin, 
Harris, Harrison, Hartley, Haskell, Hays, Hemphill, Henderson, Hidalgo, Hill, Hockley, 
Hood, Hopkins, Houston, Howard, Hudspeth, Hunt, Hutchinson, Irion, Jack, Jackson, 
Jasper, Jeff Davis, Jefferson, Jim Hogg, Jim Wells, Johnson, Jones, Karnes, Kaufman, Kendall, 
Kenedy, Kent, Kerr, Kimble, King, Kinney, Kleberg, Knox, La Salle, Lamar, 
Lamb, Lampasas, Lavaca, Lee, Leon, Liberty, Limestone, Lipscomb, Live Oak, Llano, 
Loving, Lubbock, Lynn, Madison, Marion, Martin, Mason, Matagorda, Maverick, McCulloch, 
McLennan, McMullen, Medina, Menard, Midland, Milam, Mills, Mitchell, Montague, Montgomery, 
Moore, Morris, Motley, Nacogdoches, Navarro, Newton, Nolan, Nueces, Ochiltree, Oldham, 
Orange, Palo Pinto, Panola, Parker, Parmer, Pecos, Polk, Potter, Presidio, Rains, 
Randall, Reagan, Real, Red River, Reeves, Refugio, Roberts, Robertson, Rockwall, Runnels, 
Rusk, Sabine, San Augustine, San Jacinto, San Patricio, San Saba, Schleicher, Scurry, Shackelford, Shelby, 
Sherman, Smith, Somervell, Starr, Stephens, Sterling, Stonewall, Sutton, Swisher, Tarrant, 
Taylor, Terrell, Terry, Throckmorton, Titus, Tom Green, Travis, Trinity, Tyler, Upshur, 
Upton, Uvalde, Val Verde, Van Zandt, Victoria, Walker, Waller, Ward, Washington, Webb, 
Wharton, Wheeler, Wichita, Wilbarger, Willacy, Williamson, Wilson, Winkler, Wise, Wood, 
Yoakum, Young, Zapata, Zavala, 

Please enter a command: help
Help - list available commands;
Quit - exit this dashboard;
Counties - list all Texas counties;
Cases <countyName>/Texas - confirmed Covid cases in specified county or statewide;
Deaths <countyName>/Texas - Covid deaths in specified county or statewide.

Please enter a command: cases san SaBA
San Saba county has 385 confirmed Covid cases.

Please enter a command: cases Travis
Travis county has 65507 confirmed Covid cases.

Please enter a command: courses Travis
Command is not recognized.  Try again!

Please enter a command: deaths traVIS
Travis county has 685 fatalities.

Please enter a command: cases ArgleBargle
County ArgleBargle is not recognized.            

Please enter a command: CASES texas
Texas total confirmed Covid cases: 1988063

Please enter a command: DEATHS texas
Texas total confirmed Covid deaths: 34685

Please enter a command: quit
Thank you for using the Texas Covid Database Dashboard.  Goodbye!

>

An Aside

Defining a user interface or command processor like this one is a common task in programming. It's what you might do, for example, when you program an application of any kind.

BTW: it's also very much like the implementation of Python itself. Prior to execution, Python code is translated by the system into an intermediate form called bytecode. This is common to all implementations of Python. (There may be exceptions, but I don't know of any.) Bytecode is then interpreted by a lower level command processor called the Python Virtual Machine (PVM). The PVM is a command interpreter for the bytecode language and runs on the actual underlying machine. Each different type of processor has its own native language (machine language) which is what actually runs on the hardware. So when you implement Python on a new type of machine, you need to write a version of the PVM for that machine. This can be written in any language available on that machine: Python, Ruby, C, Perl, etc.

Turning in the Assignment:

The program should be in a file named Project3.py. Submit the file via Canvas before the deadline shown at the top of this page. Submit it to the assignment project3 under the assignments sections by uploading your python file.

Your file must compile and run before submission. It must also contain a header with the following format:

# File: Project3.py
# Student: 
# UT EID:
# Course Name: CS303E
# 
# Date Created:
# Date Last Modified: 
# Description of Program: