download notebook
view notebook w/ solutions

Exam 2 (100 pts total)

files needed = ('covid.csv', 'wdi_e2.csv', 'clubs.csv', 'roster.csv', 'q2a.csv') You can find them in 'exam2_data_prac.zip' on the course webpage.

You have 75 minutes to complete this exam.

Answer all the questions below in this notebook. You should insert as many cells into the notebook as you need. When you are finished, upload your finished notebook to Canvas.

  • You may use your notes and the Internet, but you cannot work with others.
  • Import any packages you need to complete this exam.
  • Do not modify the data files directly. All data manipulation should happen in your code.

Remember, jupyter notebooks and python have lots of built in help facilities.

Question 0 (5 pts): Last, First

Replace 'Last, First' above with your actual name. Enter it as: last name, first name.

Question 1 (5 points): Saving figures

  1. If you are saving a file to use on a website or to embed in a MS Word document which format should you use? Why?

Insert a cell below and type your answer there.

Question 2A (10 pts): Apply-split-combine

The file 'covid.csv' contains data on covid tests in Wisconsin by county. Each observation in the data is a county-day. The data cover March 15, 2020 to November 16, 2020.

  1. The POS_NEW variable contains the day's new cases. Use this variable to compute the cumulative number of cases in each county.
  2. Sort your results by county name (a to z) and print out the first three counties.

Question 2B (15 pts): Bar graphs and label formatting

If you completed question 2A, you may use your results in this question. If not, use the file 'q2a.csv' to complete this question.

  1. Create two subplots arranged as one column and two rows. Make the figure size 15 inches by 15 inches.
  2. In the top subplot, create a bar plot of the cumulative cases for the counties whose names start with A-L. The x axis should be the names of the counties.
  3. In the bottom subplot, create a bar plot of the cumulative cases for the counties whose names start with M-Z. The x axis should be the names of the counties.
  4. Rotate the county names on the x axis 90 degrees so that they are vertical.
  5. Label the y axes 'cumulative number of cases.'
  6. The bars should be silver and the major y-axis grid lines should be white.
  7. Give your graph a title.
  8. Remove the top and right spines from each subplot.

Question 4 (15 pts): Long and wide data

The file 'wdi_e2.csv' contains data on real GDP and the working age population (15-64 years) for China and Japan from 1970 to 2019.

  1. Compute the ratio of GDP to the working age population for both countries for all years. Name the variable 'gdpc'.
  2. Make sure that your DataFrame is organized so that the unit of observation (country-date) are rows and the variables are columns.
  3. Sorth the index of your DataFrame.
  4. Print out the first two rows of your DataFrame.

Question 5 (10 pts): FRED and working with dates

  1. Download the weekly initial (unemployment) claims data for Wisconsin from FRED. Get the data from the beginning of 2019 to the most recent. [Use the FRED website to find the variable code for 'Initial Claims in Wisconsin'.]
  2. Compute the average weekly claims for 2019 and the average weekly claims for 2020. Print them out as below, replacing the xs with the appropriate numbers.

'The 2019 average weekly claims are xxxx. The 2020 average weekly claims are xxxxx.'

Note that the numbers are printed out as integers.

Done!

This was originally an exam that I gave in the past. I think it was a little too long. Let's call Q1-Q5 the about the right length for the exam. I am leaving Q6 below, though, so you can use it to practice.

Question 6 (20 pts): Merging and cleaning

  1. Load 'clubs.csv' into a DataFrame named clubs. The file contains data about four BIG10 schools.
  2. Load 'roster.csv' into a DataFrame named roster. The file contains data about football players at four BIG10 schools.
  3. Merge the two DataFrames. The new DataFrame should contain all the players that are in the players dataset, matched to their team information. Name the new DataFrame data.
  4. How many matches do you have? Print out the number as "There are xx matches in data."
  5. There should be 50 matches but you probably found fewer. Let's fix this. Clean up the clubs and roster DataFrames using pandas commands (do not modify the data files) and perform another merge. Name this new matched DataFrame data_clean.
  6. Print out the number as "There are xx matches in data_clean." (The xx should be equal to 50 at this point!)