Lecture 01: Python primer - Deep Learning for Solving and Estimating Dynamic Models in Economics and Finance

Course: Deep Learning for Solving and Estimating Dynamic Models in Economics and Finance
Script reference: Front matter and Appendix~E — pandas, groupby, basic data analysis
Notebook role: primer (pre-course self-study; skip if you write Python every day)
Author: Simon Scheidegger

Python Basics 10: The Python Data Analysis Library¶

The objectives of this notebook are:

to introduce students to the pandas library, including loading data files in python, cleaning data, removing null values, indexing of pandas dataframes, selecting data columns, and some basic plotting

Pandas Introduction¶

What is pandas? Pandas is a python package that provides fast, flexible and data structures that lets you work real life data quickly. More information about the pandas package can be found here

In order to use pandas in python we need to import the library into the notebook, as shown in the following cell.

# import the libraries needed

import numpy as np   # matrix and linear algebra
import pandas as pd  # python data analysis library

Loading files¶

If we want to look at data we have collected for a project or experiment, we need to upload that data into jupyter notebook. To do this we can import the file using a pandas command. Pandas is able to read various types of files, in this activity we will be using pandas to read a csv (comma separated values) file.

To import the csv file in Python we use the following command pandas.read_csv. In Python there are some common shorthand we can use, pandas is typically shorten to pd. So we will use pd.read_csv to import the data file that we will work with.

Dataset¶

Scenario: A researcher wants to gain some insight about energy usage in apartment homes in a community and provide a grade for each apartments energy usage. The dataset temp_price.csv that you are given contains HVAC energy usage data collected from 50 apartment homes in one day

The apartment ID number (household)
The outdoor temperature for the day (t_out)
The indoor temperature of the apartment for the day (t_unit)
The amount of kwh consumed by the HVAC system for the day (hvac)
The price per kwh used (price)
The price per week for using the hvac (Price per week)
The price per day for using the hvac (Price per day)

Let’s import the data to explore. This dataset is small, so that you will be able to see the changes you are making in order to become comfortable with cleaning data. In the future you will work with larger sized data sets.

The dataset is the following:

temp_price.csv

If you run Jupyter your PC, then all you have to do is to download this file and put it in the same directory as the notebook. If you work on Google Colab things are a bit more complicated. We will show you how to do it later in the homework problems. For now, let us just sprinkle some magick code that downloads the file and puts it in a spot that Google Colab can see:

Note on temp_price.csv (data quality). Seven rows in the file use score = -100 as a sentinel for “missing/invalid”, and the corresponding t_unit and Price cells are left empty — pandas will read those as NaN. If you compute aggregates (mean, groupby, etc.) without first filtering or imputing those rows, you can get NaN-poisoned results without realising it. The clean way to handle this is df = df[df["score"] != -100].copy() before aggregating, or df.replace(-100, np.nan).dropna(subset=["score"]). Sentinel-handling like this is realistic data-wrangling territory and is worth getting comfortable with before the data sets in later lectures get larger.

# Make sure you run this on Google Colab or another jupyter hub
import requests
import os
def download(url, local_filename=None):
    """
    Downloads the file in the ``url`` and saves it in the current working directory.
    """
    data = requests.get(url)
    if local_filename is None:
        local_filename = os.path.basename(url)
    with open(local_filename, 'wb') as fd:
        fd.write(data.content)
   
# The url of the file we want to download
url = 'temp_price.csv'
#download(url) #in case you download it from a webpage

Now you should have access to the temp_price.csv file from within this notebook and the following code should work:

# Reads a csv file into the pandas framework
temp_price = pd.read_csv('temp_price.csv')

# Displays how pandas looks at the file
temp_price

Observations from the dataset so far¶

The very first column is the index of each entry. Notice how python counts the rows, it starts with 0 and not 1. This is different than other programming softwares that start counting from 1. Then you have columns with various names. The first column is the “household” id, then the “date,” etc. Questions: What are some other things you notice about the data set?

Let’s continue to explore the data. The following function gives you a summary of the satistics of the data:

# This provides the statistics of each column 
temp_price.describe()

Printing specific ranges¶

In this next section we look at different ways to quickly get some insight about the data and also how to look at specific columns, rows, etc.

If we want to look at specific locations in the data we can do the following:

head() - the default displays the first 5 rows
tail() - the default displays the last 5 rows

The way you can think about the syntax when using pandas is Dataframe_name.action_to_be_done(params_if_needed)

temp_price.head()

# This will print the first 10 rows
# Let's print out the first 10 rows, what would you do?
temp_price.head()

# This prints the last 5 rows
temp_price.tail()

# If you wanted to print the first 7 rows (instead of the first 5) you would do this:
temp_price.head(7)

# Let's print the last 12 rows, what would you do?
temp_price.tail()

Cleaning the data¶

In the temp_price file you notice that some cells have -100 under the score column, -100.0 under the t_unit or NaN value for hvac. There are various reasons for this, but the details around it are not helpful for this point. We will focus on the NaN.

When you see NaN this means that the value here is Not a Number and this is the pythonic notation to display that. For this example, when we see NaN that means that this unit is missing over 70% of its data for the week. This most likely means that the sensor for the unit needs to be replaced.

Having these -100, -100.0 and NaN can mess with the interpretation of our anaylsis, so we want to clean the data to remove the corresponding observations.

To do this we will use dropna() to remove the rows that have NaN. To get a better understanding of how dropna() works we can look at the documentation for it: pandas dropna docs

Look at the documentation to see what the axis needs to be in order to remove rows vs. columns

# Remove NaN rows from the table
temp_no_null_rows = temp_price.dropna(axis=0)

# Print data with no NaN rows
temp_no_null_rows

What do you notice?

Do you see any NaN or -100 values in the dataset?
Do we still have the same number of indicies?

We can use the DataFrame.shape to compare the previous table with the removal of the NaN to double check.

# shape of data after cleaning
temp_no_null_rows.shape

(46, 9)

# shape of data before cleaning
temp_price.shape

(50, 9)

The shape() tells the number of rows and columns of the dataset.

Note¶

When dealing with columns, you typically want to keep everything lowercase and remove spaces. So to rename an existing column in the dataframe we can do the following: df.rename(columns={'current_name' : 'new_name'}, inplace=True)

Let’s rename Price per week to week_price and Price per day to daily_price:

# Changing the title of columns
clean_data = temp_no_null_rows.rename(columns={'Price per week': 'week_price',
                                               'Price per day': 'daily_price'})

# See the the column names have changed
clean_data.head()

Now, you may be wondering what exactly is:

columns={'Price per week': 'week_price',
         'Price per day': 'daily_price'}

This defines a dictionary. It is a special Python class called dict. See Sec. 5.5 here. But let’s also run it separately:

columns={'Price per week': 'week_price',
         'Price per day': 'daily_price'}

columns

{'Price per day': 'daily_price', 'Price per week': 'week_price'}

type(columns)

dict

columns['Price per week']

'week_price'

columns['Price per day']

'daily_price'

columns.keys()

dict_keys(['Price per week', 'Price per day'])

So, rename looks at the dictionary for keys that are column names and then replacs the corresponding column name with whatever the dictionary indicates.

Now that we have clean data, we can explore the data set with the following actions:

df['column_name'] - to select a single column from the dataframe
df[['column_name1', 'column_name7']] - to select a lists of columns from dataframe
df.loc[rowindex1] - to select a single row from the dataframe
df.loc[[rowindex1, rowindex2]] - to select a list of rows from the dataframe

# Select a single column
clean_data['household']

0      a1
1     a10
2     a11
3     a12
4     a13
5     a14
6     a15
7     a16
8      a2
9      a3
10     a4
11     a5
12     a6
13     a7
14     a8
15     a9
16    b17
17    b18
18    b19
19    b20
20    b21
21    b22
22    b23
23    b24
24    b25
25    b26
27    b28
28    b29
29    b30
30    b31
32    b33
34    c35
35    c36
36    c37
37    c38
38    c39
39    c40
40    c41
42    c43
43    c44
44    c45
45    c46
46    c47
47    c48
48    c49
49    c50
Name: household, dtype: object

# Select multiple columns
clean_data[['household', 'daily_price']]

# select a row
clean_data.loc[30]

household             b31
date           2019-01-06
score                  95
t_out             38.5992
t_unit            70.9052
hvac              55.9409
price             0.17303
week_price        9.67945
daily_price       1.38278
Name: 30, dtype: object

# select certain rows
clean_data.loc[[25,39, 45]]

# select a slice of rows
clean_data.loc[25:36]

But we can do even more complicated things. For example, let’s say that we wan to select all rows with a score greater than 80. How would we do this? Here it is:

# First you create an array of booleans indicating whether or not a
# row satisfies a constraint
idx = clean_data['score'] > 80
idx

# And now you can just use these indices to get a slice of the data frame:
clean_data[idx]

# And here is how you would pick all rows with score between 80 and 90:
idx = (clean_data['score'] > 80) & (clean_data['score'] < 90)
clean_data[idx]

# And here is how you would pick all rows with score less than 20 or greater than 90:
idx = (clean_data['score'] < 20) | (clean_data['score'] > 90)
clean_data[idx]

And there many more things that you can easily do with pandas. There are so many things to do with pandas that you can spend weeks on this library alone. Our suggestion is to learn pandas as needed when you experiment with data files. Typically, if you think about what you need to do, you can find how to do it in pandas with a good Google search.

Questions¶

Write pandas code that extracts the week_price column from clean_data.

# Your code here

Write pandas code that extracts all rows with week_price less than 50.

# Your code here

Write pandas code that gives you how many households have a score greater than 50 and a week_price less than 10.

# Your code here