Lesson 19: Introduction to Pandas¶
(c) 2018 Justin Bois. With the exception of pasted graphics, where the source is noted, this work is licensed under a Creative Commons Attribution License CC-BY 4.0. All code contained herein is licensed under an MIT license.
This document was prepared at Caltech with financial support from the Donna and Benjamin M. Rosen Bioengineering Center.
This lesson was generated from a Jupyter notebook. You can download the notebook here.
import numpy as np
# Pandas, conventionally imported as pd
import pandas as pd
Throughout your research career, you will undoubtedly need to handle data, possibly lots of data. The data comes in lots of formats, and you will spend much of your time wrangling the data to get it into a usable form.
Pandas is the primary tool in the SciPy stack for handling data. Its primary object, the DataFrame is extremely useful in wrangling data. We will explore some of that functionality here, and will put it to use in the next lesson.
The data set¶
We will explore using Pandas with a real data set. We will use a data set published in Beattie, et al., Perceptual impairment in face identification with poor sleep, Royal Society Open Science, 3, 160321, 2016. In this paper, researchers used the Glasgow Factial Matching Test (GMFT) to investigate how sleep deprivation affects a subject's ability to match faces, as well as the confidence the subject has in those matches. Briefly, the test works by having subjects look at a pair of faces. Two such pairs are shown below.
The top two pictures are the same person, the bottom two pictures are different people. For each pair of faces, the subject gets as much time as he or she needs and then says whether or not they are the same person. The subject then rates his or her confidence in the choice.
In this study, subjects also took surveys to determine properties about their sleep. The Sleep Condition Indicator (SCI) is a measure of insomnia disorder over the past month (scores of 16 and below indicate insomnia). The Pittsburgh Sleep Quality Index (PSQI) quantifies how well a subject sleeps in terms of interruptions, latency, etc. A higher score indicates poorer sleep. The Epworth Sleepiness Scale (ESS) assesses daytime drowsiness.
The data set is stored in the file ~/git/bootcamp/data/gfmt_sleep.csv. The contents of this file were adapted from the Excel file posted on the public Dryad repository. (Note this: if you want other people to use and explore your data, make it publicly available.)
This is a CSV file, where CSV stands for comma-separated value. This is a text file that is easily read into data structures in many programming languages. You should generally always store your data in such a format, not necessarily CSV, but a format that is open, has a well-defined specification, and is readable in many contexts. Excel files do not meet these criteria. Neither to .mat files.
Let's take a look at the CSV file.
!head data/gfmt_sleep.csv
The first line contains the headers for each column. They are participant number, gender, age, etc. The data follow. There are two important things to note here. First, notice that the gender column has string data (m or f), while the rest of the data are numeric. Note also that there are some missing data, denoted by the *s in the file.
Given the file I/O skills you recently learned, you could write some functions to parse this file and extra the data you want. You can imagine that this might be kind of painful. However, if the file format is nice and clean, like we more or less have here, we can use pre-built tools. Pandas has a very powerful function, pd.read_csv() that can read in a CSV file and store the contents in a convenient data structure called a DataFrame.
Reading in data¶
Let's first look at the doc string of pd.read_csv().
pd.read_csv?
Holy cow! There are so many options we can specify for reading in a CSV file. You will likely find reasons to use many of these throughout your research. For this particular data set, we really only need the na_values kwarg. This specifies what characters signify that a data point is missing. The resulting DataFrame is populated with a NaN, or not-a-number, wherever this character is present in the file. In this case, we want na_values='*'. So, let's load in the data set.
df = pd.read_csv('data/gfmt_sleep.csv', na_values='*')
# Check the type
type(df)
We now have the data stored in a DataFrame. We can look at it in the Jupyter notebook.
df
This is a nice representation of the data, but we really do not need to display that much. Instead, we can use the head() method of DataFrames to look at the first few rows.
df.head()
This is more manageable and gives us an overview of what the columns are. Note also the the missing data was populated with NaN.
Indexing data frames¶
The DataFrame is a convenient data structure for many reasons that will become clear as we start exploring. Let's start by looking at how DataFrames are indexed. Let's try to look at the first row.
df[0]
Yikes! Lots of errors. The problem is that we tried to index numerically by row. We index DataFrames, by columns. And there is no column that has the name 0, though there could be. Instead, a might want to look at the column with the percentage of correct face matching tasks.
df['percent correct']
This gave us the numbers we were after. Notice that when it was printed, the index of the rows came along with it. If we wanted to pull out a single percentage correct, say corresponding to index 4, we can do that.
df['percent correct'][4]
However, this is not the preferred way to do this. It is better to use .loc. This give the location in the DataFrame we want.
df.loc[4, 'percent correct']
It is also important to note that row indices need not be integers. And you should not count on them being integers. In practice I almost never use row indices, but rather use Boolean indexing.
Boolean indexing of data frames¶
Let's say I wanted the percent correct of participant number 42. I can use Boolean indexing to specify the row. Specifically, I want the row for which df['participant number'] == 42. You can essentially plop this syntax directly when using .loc.
df.loc[df['participant number'] == 42, 'percent correct']
If I want to pull the whole record for that participant, I can use : for the column index.
df.loc[df['participant number'] == 42, :]
Notice that the index, 54, comes along for the ride, but we do not need it.
Now, let's pull out all records of females under the age of 21. We can again use Boolean indexing, but we need to use an & operator. We did not cover this bitwise operator before, but the syntax is self-explanatory in the below. Note that it is important that each Boolean operation you are doing is in parentheses.
df.loc[(df['age'] < 21) & (df['gender'] == 'f'), :]
We can do something even more complicated, like pull out all females under 30 who got more than 85% of the face matching tasks correct. The code is clearer if we set up our Boolean indexing first, as follows.
inds = (df['age'] < 30) & (df['gender'] == 'f') & (df['percent correct'] > 85)
# Take a look
inds
Notice that inds is an array (actually a Pandas Series, a essentially a DataFrame with one column) of Trues and Falses. When we index with it using .loc, we get back rows where inds is True.
df.loc[inds, :]
Of interest in this exercise in Boolean indexing is that we never had to write a loop. To produce our indices, we could have done the following.
# Initialize array of Boolean indices
inds = [False] * len(df)
# Iterate over the rows of the DataFrame to check if the row should be included
for i, r in df.iterrows():
if r['age'] < 30 and r['gender'] == 'f' and r['percent correct'] > 85:
inds[i] = True
# Make our seleciton with Boolean indexing
df.loc[inds, :]
This feature, where the looping is done automatically on Pandas objects like DataFrames, is very powerful and saves us writing lots of lines of code. This example also showed how to use the iterrows() method of a DataFrame to iterate over the rows of a DataFrame. It is actually rare that you will need to do that, as we'll show next when computing with DataFrames.
Calculating with data frames¶
Recall that a subject is said to suffer from insomnia if he or she has an SCI of 16 or below. We might like to add a column to the DataFrame that specifies whether or not the subject suffers from insomnia. We can conveniently compute with columns. This is done elementwise.
df['sci'] <= 16
This tells use how is an insomniac. We can simply add this back to the DataFrame.
# Add the column to the DataFrame
df['insomnia'] = df['sci'] <= 16
# Take a look
df.head()
Remember when we briefly say the np.mean() function? We can compute with that as well. Let's compare the mean percent correct for insomniacs versus those who are not.
print('Insomniacs:', np.mean(df.loc[df['insomnia'], 'percent correct']))
print('Control: ', np.mean(df.loc[~df['insomnia'], 'percent correct']))
Notice that I used the ~ operator, which is a bit switcher. It changes all Trues to Falses and vice versa. In this case, it functions like NOT.
We will do a lot more computing with Pandas DataFrames in the next lessons. For our last demonstration in this lesson, we can quickly compute summary statistics about each column of a DataFrame using its describe() method.
df.describe()
This gives us a DataFrame with summary statistics. Note that in this DataFrame, the row indices are not integers, but are the names of the summary statistics.
Outputting a new CSV file¶
Now that we added the insomniac column, we might like to save our DataFrame as a new CSV that we can reload later. We use df.to_csv() for this with the index kwarg to ask Pandas not to explicitly write the indices to the file.
df.to_csv('gfmt_sleep_with_insomnia.csv', index=False)
Let's take a look at what this file looks like.
!head gfmt_sleep_with_insomnia.csv
Very nice. Notice that by default Pandas leave an empty field for NaNs, and we do not need the na_values kwarg when we load in this CSV file.