%matplotlib inline
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
import pandas as pd
plt.style.use('fivethirtyeight')
sns.set_context("notebook")
Pandas has a number of very useful file reading tools. You can see them enumerated by typing "pd.re" and pressing tab. We'll be using read_csv today.
elections = pd.read_csv("elections.csv")
elections # if we end a cell with an expression or variable name, the result will print
We can use the head command to return only a few rows of a dataframe.
elections.head(10)
There is also a tail command.
elections.tail(7)
The read_csv command lets us specify a column to use an index. For example, we could have used Year as the index.
elections_year_index = pd.read_csv("elections.csv", index_col = "Year")
elections_year_index.head(5)
Alternately, we could have used the set_index commmand.
elections_party_index = elections.set_index("Party")
elections_party_index.head(5)
The set_index command (along with all other data frame methods) does not modify the dataframe. That is, the original "elections" is untouched. Note: There is a flag called "inplace" which does modify the calling dataframe.
elections.head() #the index remains unchanged
By contrast, column names are ideally unique. For example, if we try to read in a file for which column names are not unique, Pandas will automatically rename any duplicates.
dups = pd.read_csv("duplicate_columns.csv")
dups
The DataFrame class has an indexing operator [] that lets you do a variety of different things. If your provide a String to the [] operator, you get back a Series corresponding to the requested label.
elections_year_index.head(6)
elections_year_index["Candidate"].head(6)
The [] operator also accepts a list of strings. In this case, you get back a DataFrame corresponding to the requested strings.
elections_year_index[["Candidate", "Party"]].head()
A list of one label also returns a DataFrame. This can be handy if you want your results as a DataFrame, not a series.
elections_year_index[["Candidate"]].head()
Note that we can also use the to_frame method to turn a Series into a DataFrame.
elections_year_index["Candidate"].to_frame().head()
The [] operator also accepts numerical slices as arguments. In this case, we are indexing by row, not column!
elections_year_index[0:3]
If you provide a single argument to the [] operator, it tries to use it as a name. This is true even if the argument passed to [] is an integer.
#elections_year_index[0] #this does not work, try uncommenting this to see it fail in action, woo
The following cells allow you to test your understanding.
weird = pd.DataFrame({
1:["topdog","botdog"],
"1":["topcat","botcat"]
})
weird
weird[1] #try to predict the output
weird["1"] #try to predict the output
weird[1:] #try to predict the output
The [] operator also supports array of booleans as an input. In this case, the array must be exactly as long as the number of rows. The result is a filtered version of the data frame, where only rows corresponding to True appear.
elections_year_index
elections_year_index[[False, False, False, False, False,
False, False, True, False, False,
True, False, False, False, True,
False, False, False, False, False,
False, False, True]]
One very common task in Data Science is filtering. Boolean Array Selection is one way to achieve this in Pandas. We start by observing logical operators like the equality operator can be applied to Pandas Series data to generate a Boolean Array. For example, we can compare the 'Result' column to the String 'win':
elections_year_index.head(5)
iswin = elections_year_index['Result'] == 'win'
iswin#.head(5)
The output of the logical operator applied to the Series is another Series with the same name and index, but of datatype boolean. The entry at row #i represents the result of the application of that operator to the entry of the original Series at row #i.
Such a boolean Series can be used as an argument to the [] operator. For example, the following code creates a DataFrame of all election winners since 1980.
elections_year_index[iswin]
Above, we've assigned the result of the logical operator to a new variable called iswin. This is uncommon. Usually, the series is created and used on the same line. Such code is a little tricky to read at first, but you'll get used to it quickly.
elections_year_index[elections_year_index['Result'] == 'win']
We can select multiple criteria by creating multiple boolean Series and combining them using the & operator.
win50plus = (elections_year_index['Result'] == 'win') & (elections_year_index['%'] < 50)
win50plus.head(5)
elections_year_index[(elections_year_index['Result'] == 'win')
& (elections_year_index['%'] < 50)]
# Note for Python experts: The reason we use the & symbol and not the word "and" is because the Python __and__
# method overrides the "&" operator, not the "and" operator.
The | operator is the symbol for or.
elections_year_index[(elections_year_index['Party'] == 'Republican')
| (elections_year_index['Party'] == "Democratic")]
If we have multiple conditions (say Republican or Democratic), we can use the isin operator to simplify our code.
elections_year_index['Party'].isin(["Republican", "Democratic"])
elections_year_index[elections_year_index['Party'].isin(["Republican", "Democratic"])]
An alternate simpler way to get back a specific set of rows is to use the query command.
elections_year_index.query?
elections_year_index.query("Result == 'win' and Year < 2000")
loc¶elections.head(5)
elections.loc[[0, 1, 2, 3, 4], ['Candidate','Party', 'Year']]
Note: The loc command won't work with numeric arguments if we're using the elections DataFrame that was indexed by year.
elections_year_index.head(5)
#causes error
elections_year_index.loc[[0, 1, 2, 3, 4], ['Candidate','Party']]#
elections_year_index.loc[[1980, 1984], ['Candidate','Party']]
Loc also supports slicing (for all types, including numeric and string labels!). Note that the slicing for loc is inclusive, even for numeric slices.
elections.loc[0:4, 'Candidate':'Year']
elections_year_index.loc[1980:1984, 'Candidate':'Party']
If we provide only a single label for the column argument, we get back a Series.
elections.loc[0:4, 'Candidate']
If we want a data frame instead and don't want to use to_frame, we can provde a list containing the column name.
elections.loc[0:4, ['Candidate']]
If we give only one row but many column labels, we'll get back a Series corresponding to a row of the table. This new Series has a neat index, where each entry is the name of the column that the data came from.
elections.head(1)
elections.loc[0, 'Candidate':'Year']
elections.loc[[0], 'Candidate':'Year']
If we omit the column argument altogether, the default behavior is to retrieve all columns.
elections.loc[[2, 4, 5]]
Loc also supports boolean array inputs instead of labels. The Boolean arrays must be of the same length as the row/column shape of the dataframe, respectively (in versions prior to 0.25, Pandas used to allow size mismatches and would assume the missing values were all False, this was changed in 2019).
elections.loc[[True, False, False, True, False, False, True, True, True, False, False, True,
True, True, False, True, True, False, False, False, True, False, False], # row mask
[True, False, False, True, True] # column mask
]
elections.loc[[0, 3], ['Candidate', 'Year']]
We can use boolean array arguments for one axis of the data, and labels for the other.
elections.loc[[True, False, False, True, False, False, True, True, True, False, False, True,
True, True, False, True, True, False, False, False, True, False, False], # row mask
'Candidate':'%' # column label slice
]
A student asks what happens if you give scalar arguments for the requested rows AND columns. The answer is that you get back just a single value.
elections.loc[15, '%']
iloc¶loc's cousin iloc is very similar, but is used to access based on numerical position instead of label. For example, to access to the top 3 rows and top 3 columns of a table, we can use [0:3, 0:3]. iloc slicing is exclusive, just like standard Python slicing of numerical values.
elections.head(5)
elections.iloc[:3, 2:]
We will use both loc and iloc in the course. Loc is generally preferred for a number of reasons, for example:
However, iloc is sometimes more convenient. We'll provide examples of when iloc is the superior choice.
Which of the following expressions return DataFrame of the first 3 Candidate and Year for candidates that won with more than 50% of the vote.
elections.head(10)
elections.iloc[[0, 3, 5], [0, 3]]
elections.loc[[0, 3, 5], "Candidate":"Year"]
elections.loc[elections["%"] > 50, ["Candidate", "Year"]].head(3)
elections.loc[elections["%"] > 50, ["Candidate", "Year"]].iloc[0:2, :]
Pandas dataframes also make it easy to get a sample. We simply use the sample method and provide the number of samples that we'd like as the arugment. Sampling is done without replacement by default. Set replace=True if you want replacement.
elections.sample(10)
elections.query("Year < 1992").sample(50, replace=True)
Consider a series of only the vote percentages of election winners.
winners = elections.query("Result == 'win'")["%"]
winners
We can perform various Python operations (including numpy operations) to DataFrames and Series.
max(winners)
np.mean(winners)
We can also do more complicated operations like computing the mean squared error, i.e. the average L2 loss. (This will mean more in the next few weeks.)
c = 50.38
mse = np.mean((c - winners)**2)
mse
c2 = 50.35
mse2 = np.mean((c2 - winners)**2)
mse2
We can also apply mathematical operations to a DataFrame so long as it has only numerical data.
(elections[["%", "Year"]] + 3).head(5)
The head, shape, size, and describe methods can be used to quickly get a good sense of the data we're working with. For example:
mottos = pd.read_csv("mottos.csv", index_col="State")
mottos.head(20)
mottos.size
The fact that the size is 200 means our data file is relatively small, with only 200 total entries.
mottos.shape
Since we're looking at data for states, and we see the number 50, it looks like we've mostly likely got a complete dataset that omits Washington D.C. and U.S. territories like Guam and Puerto Rico.
mottos.describe()
Above, we see a quick summary of all the data. For example, the most common language for mottos is Latin, which covers 23 different states. Does anything else seem surprising?
We can get a direct reference to the index using .index.
mottos.index
We can also access individual properties of the index, for example, mottos.index.name.
mottos.index.name
This reflects the fact that in our data frame, the index IS the state name!
mottos.head(2)
It turns out the columns also have an Index. We can access this index by using .columns.
mottos.head(2)
There are also a ton of useful utility methods we can use with Data Frames and Series. For example, we can create a copy of a data frame sorted by a specific column using sort_values.
elections.sort_values('%', ascending=False)
As mentioned before, all Data Frame methods return a copy and do not modify the original data structure, unless you set inplace to True.
elections.head(5)
If we want to sort in reverse order, we can set ascending=False.
elections.sort_values('%', ascending=False)
We can also use sort_values on Series objects.
mottos['Language'].sort_values(ascending=False).head(10)
For Series, the value_counts method is often quite handy.
elections['Party'].value_counts()
mottos['Language'].value_counts()
Also commonly used is the unique method, which returns all unique values as a numpy array.
mottos['Language'].unique()
Now let's play around a bit with a large baby names dataset that is publicly available. We'll start by loading that dataset from the social security administration's website.
To keep the data small enough to avoid crashing datahub, we're going to look at only California rather than looking at the national dataset.
import urllib.request
import os.path
import zipfile
data_url = "https://www.ssa.gov/oact/babynames/state/namesbystate.zip"
local_filename = "babynamesbystate.zip"
if not os.path.exists(local_filename): # if the data exists don't download again
with urllib.request.urlopen(data_url) as resp, open(local_filename, 'wb') as f:
f.write(resp.read())
zf = zipfile.ZipFile(local_filename, 'r')
ca_name = 'CA.TXT'
field_names = ['State', 'Sex', 'Year', 'Name', 'Count']
with zf.open(ca_name) as fh:
babynames = pd.read_csv(fh, header=None, names=field_names)
babynames.sample(5)
Goal 1: Find the most popular baby name in California in 2018
babynames[babynames["Year"] == 2018].sort_values(by = "Count", ascending = False).head(5)
most_common_count = babynames[babynames["Year"] == 2018].sort_values(by = "Count", ascending = False).iloc[0]["Count"]
most_common_count
Goal 2: Baby names that start with j. Hard to do with today's tools.
Goal 3: Name whose popularity has changed the most. Also tough.
These goals are hard with our tools so far. Will discuss next ime.