In this lab, we'll learn the various ways to query a dataset and get information using pandas.
- Query DataFrames by slicing with conditional logic
- Use the query method to access data
- Query dataframes with SQL using the
pandasql
library
In this lab, we'll continue working with the Titanic Survivors Dataset
Begin by importing pandas
as pd
, numpy
as np
, and matplotlib.pyplot
as plt
, and set the appropriate alias for each. Also set %matplotlib inline
.
Next, read in the data from titanic.csv
and store it as a DataFrame in df
. Display the .head()
to ensure that everything loaded correctly.
df = None
One of the most common ways to query data with pandas is to simply slice the DataFrame so that the object returned contains only the data you're interested in.
In the cell below, slice the DataFrame so that it only contains passengers with 2nd or 3rd class tickets (denoted by the Pclass
column).
Hint: Remember, your conditional logic must be passed in to the slicing operator to return a slice of the DataFrame--otherwise, it will just return a table of boolean values based on the conditional statement!
no_first_class_df = None
We can also chain conditional statements together by wrapping them in parenthesis and making us of the &
and |
operators ('and' and 'or' operators, respectively).
In the cell below, slice the DataFrame so that it only contains passengers with a Fare
value between 50 and 100, inclusive.
fares_50_to_100_df = None
Remember that there are two syntactically correct ways to access a column in a DataFrame. For instance, df['Name']
and df.Name
return the same thing.
In the cell below, use the dot notation syntax and slice a DataFrame that contains male passengers that survived that also belong to Pclass 2 or 3.
poor_male_survivors_df = None
Great! Now that we've explored the methods for slicing a DataFrame for querying our data, let's explore a sample use case.
We have a sneaking suspicion that women and children survived more than men, and that rich passengers were more likely to survive than poor passengers. The easiest way to confirm this is to slice the data into DataFrames that contain each subgroup, and then quickly visualize the survival rate of each subgroup with histograms--so that's exactly what we're going to do in the cells below!
In the cell below, create a DataFrame that contains passengers that are female, as well as children (males included) under the age of 15 (exclusive).
Also create a DataFrame that contains only male passengers of all ages.
women_and_children_df = None
Great! Now, we'll use the matplotlib
functionality built into the DataFrame objects to quickly create visualizations of the Survived
column for each DataFrame.
In the cell below, create histogram visualizations of the Survived
column for both DataFrames. Bonus points if you use plt.title()
to label them correctly and make it easy to tell them apart!
Well that seems like a pretty stark difference--it seems our intuition was correct! Now, let's repeat the same process, but separating rich and poor passengers.
In the cell below, create one DataFrame containing Fist Class passengers (Pclass == 1
), and another DataFrame containing everyone else.
first_class_df = None
second_third_class_df = None
Now, create histograms of the surivival for each subgroup, just as we did above.
To the surprise of absolutely no one, it seems like First Class passengers were more likely to survive than not, while 2nd and 3rd class passengers were more likely to die than not. However, don't read too far into these graphs, as these aren't at the same scale, so they aren't fair comparisons.
Slicing is a useful method for quickly getting DataFrames that contain only the examples we're looking for. It's a quick, easy method that feels intuitive in Python, since we can rely on the same conditional logic that we would if we were just writing if/else
statements.
Instead of slicing, we can also make use the DataFrame's built-in .query()
method. This method reads a bit cleaner, and allows us to pass in our arguments as a string. For more information or example code on how to use this method, see the pandas documentation.
In the cell below, use the .query()
method to slice a DataFrame that contains only passengers who have a PassengerId
greater than or equal to 500.
query_string = None
high_passenger_number_df = None
# high_passenger_number_df.head()
Just as with slicing, we can pass in queries with mutliple conditions. One unique difference between using the .query()
method and conditional slicing is that we can use and
or &
as well as or
or |
(for fun, try reading this last sentence out loud), while we are limited to the &
and |
symbols to denote and/or operations with conditional slicing.
In the cell below, use the query()
method to return a DataFrame that contains only female passengers under the age of 15 (exclusive).
Hint: Although the entire query is a string, you'll still need to denote that female
is also a string, within the string. (String-Ception?)
female_children_df = None
# female_children_df.head()
A cousin of the query()
method, eval()
allows us to use the same string-filled syntax as querying for creating new columns. For instance:
some_df.eval('C = A + B')
would return a copy of the some_df
dataframe, but will now include a column C
where all values are equal to the sum of the A
and B
values for any given row. This method also allows the user to specify if the operation should be done in place or not, providing a quick, easy syntax for simple feature engineering.
In the cell below, use the DataFrame's eval()
method in place to add a column called Age_x_Fare
, and set it equal to Age
multiplied by Fare
.
# df.head()
Great! Now, we'll move on the coolest part of this lab--querying DataFrames with SQL!
For this final section of the lab, we'll make use of the pandasql
library. Pandasql is a library designed to make it easy to query DataFrames directly wit SQL syntax, which was open-sourced by the company Yhat in late 2016. It's very straightforward to use, but you are still encouraged to take a look at the documentation as needed.
We'll start by making sure the library is installed. Run the cell below.
!pip install pandasql
That should have installed everything correctly. This library has a few dependencies, which you should already have installed. If you don't, just pip install
them in your terminal and you'll be good to go!
In the cell below, import sqldf
from pandasql
.
Great! Now, let's get some practice with this handy library.
pandasql
allows us to pass in SQL queries in the form of a string to directly query our database. Each time we make a query, we need pass in additional parameter that gives it access to the other variables in our session/environment. We can use a lambda function to pass locals()
or globals()
so that we don't have to type this every time.
In the cell below, create a variable called pysqldf
and set it equal to a lambda function q
that returns sqldf(q, globals())
. If you're unsure of how to do this, see the example in the documentation.
pysqldf = None
Great! That will save us from having to pass globals()
as an argument every time we query, which can get a bit tedious.
Let's write a basic query to get a list of passenger names from df
, limit 10. If you would prefer to format your query on multiple lines and style it as canonical SQL, that's fine--remember that multi-line strings in python are denoted by """
--for example:
"""
This is a
Multi-Line String
"""
In the cell below, write a SQL query that returns the names of the first 10 passengers.
q = None
passenger_names = None
passenger_names
Great! Now, let's try a harder one:
In the cell below, query the DataFrame for names and fares of any male passengers that survived, limit 30.
q2 = None
sql_surviving_males = None
sql_surviving_males
This library is really powerful! This makes it easy for us to leverage all of SQL knowledge to quickly query any DataFrame, especially when we only want to select certain columns. This saves us from having to slice/query the DataFrame and then slice the columns we want (or drop the ones we don't want).
Although it's outside the scope of this lab, it's also worth noting that both pandas
and pandasql
provide built-in functionality for join operations, too!
In the cell below, create 2 separate DataFrames using pandasql
. One should contain the Pclass of all female passengers that survived, and the other should contain the Pclass of all female passengers that died.
Then, create histogram visualizations of the Pclass
column for each DataFrame to compare the two. Bonus points for taking the time to make the graphs extra readable by adding titles, labeling each axis, and cleaning up the number of ticks on the X-axis!
# Write your queries in these variables to keep your code well-formatted and readable
q3 = None
q4 = None
survived_females_by_pclass_df = None
died_females_by_pclass_df = None
# Create and label the histograms for each below!
In this lab, we learned how to:
- Query DataFrames by slicing using conditional logic
- Make use of the
.query()
method to query a dataset - Query DataFrames using SQL with the
pandasql
library