Welcome to Engineering Python.
This is a Python programming course for engineers.
In this video, I'll talk about two Pandas examples, the US housing market and the US
unemployment map.
We will use the housing market data from Quandl.
Quandl is a search engine for many financial, economic, and social datasets.
To download data from quandl, you need to create a free account.
Manually download the dataset Freddie Mac House Price Index New York as an Excel file
from this link.
This is the downloaded file's name.
Put this file in the same folder as the current Jupyter Notebook file.
The Freddie Mac House Price Index provides a measure of typical price inflation for houses
within the United States.
Values are calculated monthly by state, starting from January 1975.
Let's read the Worksheet 1 of the downloaded excel file, using the column Date as the index.
We assign the data to a DataFrame named df.
This is the data.
In September 2017, the index is 178.80, in January 1975, the index is 23.66.
Note that df is in descending order right now, from 2017 to 1975.
We want to sort it in ascending order by Date.
This is the new DataFrame df.
After that, we plot this DataFrame and show the figure on the screen.
From this figure, we can see a very clearing increasing trend.
House prices in the New York State have increased about 7 or 8 times over the period of 42 years.
Now, let's try to retrieve the New York house pricing index data from the quandl website
automatically.
To do this, we need to install the quandl package by executing the following command
in a command window or a terminal: conda install quandl.
Note that this is not done within the python environment.
After the installation is done.
We import quandl, and then use quandl dot get function to get the Freddie Mac house
price index in New York State.
Automatically retrieved data is already sorted in ascending order.
Now, let's retrieve the data of four states, California, Illinois, New York, and Texas,
from the quandl website automatically.
You can change these to include other states you are interested in.
Note that, if you don't have a quandl api_key, you may see an error.
It says, Error Downloading!
Too Many Requests.
To apply for a free api_key, you need to log into your quandl account.
In your Account Settings, you will find your api key.
You can also request a new api key.
The first line of this program is to specify the api_key.
This is my old api key.
You must replace this with your own api key.
Otherwise, this program won't work.
Here, we define a list of states we are interested in.
We must use the two-letter abbreviations of the states.
Next, we create an empty DataFrame.
For each state abbreviation in this list, we concatenate it with this string to form
the data label.
We print out this data label to keep track of which state we are processing.
Next, we use the quandl dot get to retrieve the Freddie Mac House Price Index for that
state.
The authentication token is just the api key we defined at the beginning of this program.
The next four lines of code will just append this state's house price index to the DataFrame
df as a separate column.
Finally, we set the column names to be the state abbreviations.
Print the DataFrame df, and save it to Sheet 1 of the excel file.
This is the output.
We processed four states' house price indexes.
These indexes are shown in this DataFrame df.
Next, let's plot this DataFrame, put the legend at the best location with minimum overlapping.
In this Figure, the California index is denoted by the blue curve, Illinois by orange, New
York by green, and Texas by red.
The all seem to follow the same trend, although the California curve shows much more variation
than the others.
We find in this figure that all these curves intersect at this point around Year 2000.
It turns out the Freddie Mac House Price Index is calculated so that the index value of December
2000 equals 100% for every state.
Therefore, this is the reference point.
December 2000 is considered the base month.
We want to move the reference point to the beginning, that is, January 1975.
So, we use the entire DataFrame df minus the zeroth row of each state and divided by the
zeroth row.
We plot the new DataFrame.
This figure shows that California house prices have increased about 13 times since January
1975.
Illinois house prices have increased about 4 time during the same period.
We can also calculate the correlation values of the indexes.
A correlation value is always between -1 and +1.
Correlation characterizes whether two curves have a similar trend.
+1 means if one index increases, the other index also increases by the same amount.
-1 means if one index increases, the other index decreases by the same amount.
In this correlation table, we see that there is a strong correlation between all these
indexes.
For example, the correlation between California and NY is about 0.96.
It doesn't mean the increase of California house prices caused the increase of New York
house prices.
It just shows that they have a very similar trend.
There are many more free datasets on quandl.com for you to explore, like currencies, interest
rates, mutual funds, real estate, energy, and metals.
These are the data of many countries in the world, US, China, Europe, and so on.
In this second example, we want to make a choropleth map of the US unemployment rate
by county in 2009.
This is the year immediately following the 2007-2008 financial crisis.
A choropleth map is a way to display statistical variables on a map.
It visualizes how a measurement varies across a geographic area.
The unemployment rate data are stored in an excel file, unemployment2009.xslx.
This is the content of the excel file.
The Federal Information Processing Standard code, or FIPS code, uniquely identifies counties
and county equivalents.
For example, The FIPS code for Baldwin County, Alabama, is 1003.
The unemployment rate is 9.1%.
The base map is stored in this file, us_counties.svg.
This program uses Beautiful Soup, a Python package, for parsing XML labels in the base
map svg file.
This package is able to extract data from HTML and XML files, which is very useful for
web scraping.
This program is unemployment.py.
I will not explain the code in detail.
I'll leave it to you for practice.
We can run this program in Jupyter using the run magic.
This is the output.
In this map, high unemployment rates are marked by dark blue.
The US was going through a tough time in 2009 following the global financial crisis.
Two out of the Big Three went bankrupt.
Chrysler filed for bankruptcy in April 2009, and General Motors filed for bankruptcy in
June 2009.
California, Oregon, Michigan, and other Midwestern and Southeastern states suffered a lot.
The Northeastern states, the Mountain states, and the South-Central states were not so bad.
I just talked about two Pandas examples in this video.
The course materials are available on YouTube and GitHub.
You can watch the course videos in sequence.
If you like this video, please subscribe and share.
I'm Yong Wang.
Thanks for watching.