download notebook
view notebook w/o solutions

Exam 2 Fall 2023 (100 pts total)

files needed = ('fish.csv', 'room_char.xlsx', 'cpi.csv', 'cb_2022_55_tract_500k.zip', 'wdi.csv'), which can be found in exam2_data.zip

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.
• You cannot work with others on the exam. You cannot post questions online and solicit answers, e.g., through Chegg or chat GPT.
• Import any packages you need to complete this exam.
• Do not modify the data files directly. All data manipulation should happen in your code.

Question 0 (5 pts): Last, First

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

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import datetime as dt
import geopandas as gpd

C:\Users\kimru\anaconda3\envs\geo3\lib\site-packages\pyproj\__init__.py:91: UserWarning: Valid PROJ data directory not found. Either set the path using the environmental variable PROJ_LIB or with `pyproj.datadir.set_data_dir`.
  warnings.warn(str(err))

Question 1 (15 points):

Below are two DataFrames. Insert a markdown cell below this one, and use the DataFrames to answer the following questions. You do not need to write any code for this question.

1. How many rows and columns does Z have after running the following code?

z = pd.merge(left=X, right=Y, on='id', how='inner')

1. How many rows and columns does Z have after running the following code? How many missing values are in Z['price']?

z = pd.merge(left=X, right=Y, on='id', how='right')

DataFrame X

DataFrame Y

1. columns = 3, rows = 3 (pear, orange, apple)
2. columns = 3, rows = 5; missing values in Z['price'] = 2 (unmatched Bannana and kiwi)

Question 2 (10 points): MultiIndex

1. Load the file fish.csv
2. The column index should be a multiIndex with 'Family' at level 0 and 'Type' at level 1

3. The row index should be a multiIndex with date at level 0 and state at level 1

4. Print out a DataFrame that contains only the observations from 2023

fish = pd.read_csv('fish.csv', header=[0,1], index_col=[0,1], parse_dates=[0])
fish.sort_index(inplace=True)

fish.loc['2023']

Question 3 (20 points): Grouping

1. Load the file room_char.xlsx
2. Create a DataFrame named that holds the number of rooms with windows and without windows for each building
   • The row index should have BLDG at level 0 and WINDOWS at level 1
   • There is only one column, which holds the room counts. Name the column ROOM COUNT
3. Print out your DataFrame

For example, the first two rows of this DataFrame would be

room = pd.read_excel('room_char.xlsx')
room.head(2)

room_counts = room[['BLDG', 'WINDOWS', 'ROOM']].groupby(['BLDG', 'WINDOWS']).count()
room_counts.rename(columns={'ROOM':'ROOM COUNT'}, inplace=True)
room_counts

Question 4 (15 points): Time series

1. Load the file cpi.csv. It contains data on the consumer price index.
2. Create a CPI variable at the quarterly frequency
3. Plot the quarterly CPI. Make your figure graphically excellent.
4. Add a vertical line to your plot at August 1, 2020

cpi = pd.read_csv('cpi.csv', parse_dates=['DATE'], index_col='DATE')
cpiq = cpi.resample('Q').mean()

fig, ax = plt.subplots()
ax.plot(cpiq.index, cpiq['CPIAUCSL'], color='black')
ax.axvline(x=dt.datetime(2020,8,1), color='black', ls='--')
ax.set_title('Consumer Price Index (1982-84 = 100)')
ax.set_ylabel('index')

sns.despine()

Question 5 (15 points):

The file cb_2022_55_tract_500k.zip contains the shapefile for the census tracts in Wisconsin, downloaded from here.

1. Create a map of only the census tracts in Dane County. Use a white background and black edges.
2. Lake Mendota is tract 9917.02 and Lake Monona is tract 9917.03. Color these two tracts skyblue.

dane = gpd.read_file('cb_2022_55_tract_500k.zip')

fig, ax = plt.subplots(figsize=(15,8))

dane[dane['NAMELSADCO']=='Dane County'].plot(color='white', edgecolor='black', ax=ax)
dane[dane['NAME'].isin(['9917.03','9917.02'])].plot(color='skyblue', edgecolor='black', ax=ax)

ax.set_title('Dane County census tracts')
ax.axis('off')
plt.show()

Question 6 (10 points):

1. Use the code below to create a DataFrame

ads = pd.DataFrame({'address':['1180 Observatory Dr.;Madison;WI;53706', '1220 Linden Dr.;Madison;WI;53706']})

1. Extract the street, city, state, and zip out of the address variable and save them in columns named named Street, City, State, and Zip.

The first row should look like:

ads = pd.DataFrame({'address':['1180 Observatory Dr.;Madison;WI;53706', '1220 Linden Dr.;Madison;WI;53706']})

ads[['Street', 'City', 'State', 'Zip']] = ads['address'].str.split(';', expand=True)
ads

Question 7 (10 points):

The file wdi.csv contains a panel of data. It covers the United States and Germany from 1998 to 2022. For each country and year, it measures service exports and GDP.

1. Insert a markdown cell below this one and write which part of the data should be columns and which should be rows in order to make the data "tidy."

Insert a code cell below your answer from part 1 and

1. Load the wdi.csv file into a DataFrame.

2. Reshape the DataFrame to match your answer from part 1.

3. Sort your row index and print out your DataFrame.

Tidy data has observations as rows and variables as columns. In this case, I want the country and time to be the rows and service exports and gdp to be the columns.

wdi = pd.read_csv('wdi.csv', index_col=['Country Name', 'Country Code', 'Series Name', 'Series Code']).stack()
wdi.unstack(['Series Name', 'Series Code']).sort_index()