This notebook is adapted by Zhuo Chen from the notebooks created by Nathan Kelber, William Mattingly and Melanie Walsh under Creative Commons CC BY License.
For questions/comments/improvements, email zhuo.chen@ithaka.org or nathan.kelber@ithaka.org.
___
Description: This notebook describes how to:
This is the first notebook in a series on learning to use Pandas.
Use Case: For Learners (Detailed explanation, not ideal for researchers)
Difficulty: Beginner
Knowledge Required:
Knowledge Recommended:
Completion Time: 90 minutes
Data Format: .csv, .xsxl
Libraries Used: Pandas
Research Pipeline: None ___
Pandas is a Python library that allows you to easily work with tabular data. Most people are familiar with commercial spreadsheet software, such as Microsoft Excel or Google Sheets. While spreadsheet software and Pandas can accomplish similar tasks, each has significant advantages depending on the use-case.
Advantages of Spreadsheet Software
Advantages of Pandas
In short, spreadsheet software is better for browsing small datasets and making moderate adjustments. Pandas is better for automating data cleaning processes that require large or complex data manipulation.
# import pandas, `as pd` allows us to shorten typing `pandas` to `pd` when we call pandas
import pandas as pd
In Pandas, data are stored in two fundamental objects:
We can think of a Series as a single column or row of data. Here we have a column called Champions
with the country names of the winners of the most recent ten FIFA world cup games.
Champions |
---|
Argentina |
France |
Germany |
Spain |
Italy |
Brazil |
France |
Brazil |
Germany |
Argentina |
Let's create a Series based on this column. To create our Series, we pass a list into the .Series()
method:
# Create a data series object in Pandas
champions = pd.Series(["Argentina",
"France",
"Germany",
"Spain",
"Italy",
"Brazil",
"France",
"Brazil",
"Germany",
"Argentina"]
)
# Take a look at the Series
champions
As you can see, except the data column, we also have an index column. By default, the indexes are numbers starting from 0. We could define the indexes ourselves. To do that, we will pass a dictionary to the .Series()
method. The keys of the dictionary will be used as indexes.
# Use self-defined indexes
pd.Series({2022: "Argentina",
2018: "France",
2014: "Germany",
2010: "Spain",
2006: "Italy",
2002: "Brazil",
1998: "France",
1994: "Brazil",
1990: "Germany",
1986: "Argentina"}
)
You can give a name to your Pandas Series using the name
parameter.
# give a name to the series
pd.Series({2022: "Argentina",
2018: "France",
2014: "Germany",
2010: "Spain",
2006: "Italy",
2002: "Brazil",
1998: "France",
1994: "Brazil",
1990: "Germany",
1986: "Argentina"},
name = 'World Cup Champions'
)
While a Pandas Series is 1-dimensional with a single column/row of data, a Pandas DataFrame is 2-dimensional and can have multiple columns and rows.
Year | Champion | Host |
---|---|---|
2022 | Argentina | Qatar |
2018 | France | Russia |
2014 | Germany | Brazil |
2010 | Spain | South Africa |
2006 | Italy | Germany |
2002 | Brazil | Korea/Japan |
1998 | France | France |
1994 | Brazil | USA |
1990 | Germany | Italy |
1986 | Argentina | Mexico |
Let's create a Pandas DataFrame based on this table. To create our dataframe, we pass a dictionary into the DataFrame()
method. Each key:value
pair will form a column in the dataframe, with the key as the column name and the value as the data in that column.
# Create a Pandas dataframe
wcup = pd.DataFrame({"Year": [2022,
2018,
2014,
2010,
2006,
2002,
1998,
1994,
1990,
1986],
"Champion": ["Argentina",
"France",
"Germany",
"Spain",
"Italy",
"Brazil",
"France",
"Brazil",
"Germany",
"Argentina"],
"Host": ["Qatar",
"Russia",
"Brazil",
"South Africa",
"Germany",
"Korea/Japan",
"France",
"USA",
"Italy",
"Mexico"]
})
wcup
In a Pandas dataframe, each row/column is technically a Pandas Series. We can see this by selecting the first row with the iloc
method and check its type.
# Get the type of a row in a dataframe
type(wcup.iloc[0])
Let's select a column and check its type.
# Get the type of a column in a dataframe
type(wcup['Champion'])
We will describe row/column selection in greater detail below.
You are a middle school teacher. You teach the Butterfly Class and the Hippo Class. Last week, the Butterfly class had an English test and a math test. You would like to make a dataframe to record the English grades and math grades of the students in the Butterfly Class.
Make a dataframe with three columns: name, English and Math.
# make a dataframe
After we build a dataframe, it is helpful to get a general idea of the data. The first step is to explore the dataframe's attributes. Attributes are properties of the dataframe (not functions), so they do not have parentheses ()
after them.
Attribute | Reveals |
---|---|
.shape | The number of rows and columns |
.columns | The name of each column |
To get how many rows and columns a dataframe has, we use the .shape
attribute. df.shape
returns a tuple with (number of rows, number of columns).
# df.shape returns a tuple (# of rows, # of columns)
wcup.shape
# Use `.columns` attribute to find the column names
wcup.columns
There are some methods we can use to explore the data as well.
Method | Reveals |
---|---|
.info( ) | Column count and data type |
.head( ) | First five rows |
.tail( ) | Last five rows |
# Use `.info()` to get column count and data type
wcup.info()
We can get a preview of the dataframe. The .head()
and .tail()
methods help us do that.
# Display the first five rows of the data
wcup.head()
# Display the last five rows of the data
wcup.tail()
# Specify the number of rows at the beginning of the table to display
wcup.head(8)
Pandas provides different methods to read and write tabular data. The methods used to read in data from files are .read_*()
. The methods used to write data into files are .to_*()
.
For example, we can create a dataframe from a csv file using the .read_csv()
method.
### Download the csv file
from pathlib import Path
import urllib.request
# Check if a data folder exists. If not, create it.
data_folder = Path('./data/')
data_folder.mkdir(exist_ok=True)
# Download the file
url = 'https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/Pandas1_failed_banks_since_2000.csv'
urllib.request.urlretrieve(url, './data/failed_banks_since_2000.csv')
# Download success message
print('Sample file ready.')
# Use the read_csv() method to create a dataframe
failed_banks = pd.read_csv('./data/failed_banks_since_2000.csv')
failed_banks
You can also write the tabular data from a dataframe into a file.
# write the dataframe we created for the world cup champions into a file
wcup.to_csv('./data/wcup_champions.csv')
Pandas can read data from files of many different formats and write data into files of many different formats.
Suppose you would like to read the data of COVID-19 cases in Massachusetts into a dataframe. Can you use a Pandas method to do that?
# Download the excel file
url = 'https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/Pandas1_covid_MA_06292023.xlsx'
file = './data/covid_MA.xlsx'
urllib.request.urlretrieve(url, file)
print('Sample file ready.')
# Read in the data
covid = pd.read_excel(file)
covid
Now, can you write the dataframe you created into a json file?
# write the dataframe into a json file
In this section, we will take a look at the different ways of accessing the data in a dataframe.
For example, once you get the column names, you could access a column of your interest. You can use the bracket notation df[ColumnName]
to get a specific column.
# Use bracket notation to access the column 'Champion'
wcup['Champion']
We can also access multiple columns from a dataframe by putting the column names in a list. Note that in this case, you have two layers of hard brackets.
# Access multiple columns
wcup[['Year','Champion']]
.iloc
and .loc
¶In Pandas, there are two indexers .iloc
and .loc
that are often used to access data in a dataframe.
.iloc
allows us to access a row or a column using its integer location.
Recall that in a dataframe, by default, to the left of each row are index numbers. The index numbers are similar to the index numbers for a Python list; they help us reference a particular row for data retrieval. Also, like a Python list, the index begins with 0.
We can retrieve data using the .iloc
attribute. The syntax of .iloc
indexer is df.iloc[row selection, column selection]
.
# Access a single row
wcup.iloc[5] # Access the row with the index number 5
When we select multiple consecutive rows from a dataframe, we give a starting index and an ending index. Notice that the selected rows will not include the final index row.
# Access multiple consecutive rows
wcup.iloc[2:5] # Access the rows with the index number 2, 3, and 4
# Access multiple non-consecutive rows
wcup.iloc[[0,2,5]] # Access the rows with the index number 0, 2, and 5
# access every other row in wcup
We have seen how we can access rows from a dataframe using the .iloc
indexer. In the following, we will use the .iloc
indexer to access columns. Recall that the syntax of .iloc
indexer is df.iloc[row selection, column selection]
. Again, the index numbers for the columns are similar to the index numbers for a Python list; they help us reference a particular column for data retrieval. Also, like a Python list, the index begins with 0.
# Access a single column
wcup.iloc[:,1] # Access the column with the index number 1
Note that we cannot use the column name, i.e., the header, to access a column because .iloc
accesses data using their integer location. If we try to access a column using its column name, we get an error!
# .iloc cannot access a column by its name
wcup.iloc[:,'Champion']
We can use integer slice to access multiple columns from a dataframe.
# Access multiple consecutive columns
wcup.iloc[:,1:3] # Access the second and third column of the dataframe wcup
# Access multiple non-consecutive columns
wcup.iloc[:,[0,2]] # Access the first and third column of the dataframe wcup
Now that you know how to select rows and columns from a dataframe using .iloc
. You should be able to figure out how to get a slice of a dataframe using .iloc
. For example, if you would like to know the champion of the world cup games between 1994 and 2010. How do you slice the dataframe wcup
to get the part you are interested in?
# Slice the dataframe using .iloc[ ]
While .iloc
is integer-based, .loc
is label-based. It means that you have to access rows and columns based on their row and column labels.
The syntax of .loc
is df.loc[row selection, column selection]
.
At the moment, the labels for the rows are just their index numbers. When we use .loc
to access a row, it will look very similar to what we did with .iloc
.
# Access a row using .loc
wcup.loc[0]
But we could make our index column customized. For example, we could use the column Year
as the index column.
# Set the column 'Year' as the index column
wcup = wcup.set_index('Year')
wcup
After we make the change, we will use the new labels to access the rows.
# Access a row using .loc
wcup.loc[2006]
# Access multiple consecutive rows
wcup.loc[2018:2010]
Note that with the label search, the ending index row is included.
# Access multiple non-consecutive rows
wcup.loc[[1994, 2002, 2010]]
# Access a column
wcup.loc[:, 'Host']
Now that you know how to select rows and columns from a dataframe using .loc[ ]
. You should be able to figure out how to get a slice of a dataframe using .loc[ ]
. For example, if you would like to know the champion of the world cup games between 1994 and 2010. How do you slice the dataframe wcup
to get the part you are interested in?
# Slice the dataframe using .loc[ ]
As a quick reminder, remember that .iloc[]
slicing is not inclusive of the final value. On the other hand, .loc[]
slicing does include the final value.
.iat
and .at
¶We have learned how to use the two indexers .iloc
and .loc
to access rows and columns from a dataframe. In real life, sometimes we only want to access the value in a single cell. In this case, the fastest way is to use the .iat
and at
indexers. We can now tell from the name that iat
provides integer-based lookups while at
provides label-based lookups.
Suppose we would like to get the champion country of the 2002 world cup. How do you do that?
# Get the champion country of the 2002 world cup using .at[]
wcup.at[2002, 'Champion']
# Get the champion country of the 2002 world cup using .iat[]
wcup.iat[5, 0]
You are a middle school teacher. You have a .csv file that stores the English grades and Math grades of the students in your Butterfly class. Can you use a .read_*()
method to read in the data from the file and create a dataframe? After that, can you use .iloc[]
or .loc[]
to get the Math grades of the first three students from the dataframe?
After you get the slice of the dataframe with the Math grades of the first three students, can you write the data into a file of .xlsx
format?
# Download the csv file
url = 'https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/Pandas1_grades.csv'
grades_file = './data/Grades.csv'
urllib.request.urlretrieve(url, grades_file)
print('Sample file ready.')
# Read in the data and create a dataframe
# Select the Math grades for the first three students
# Write the slice into a file of .xlsx format
Now that we know how to access data from a dataframe, we could easily use what we have learned to set the values in a dataframe. To do that, we will use assignment statements you have learned in Python Basics.
# Get the data
grades = pd.read_csv('./data/Grades.csv')
grades
Recall that we have learned how to use the indexers to access a slice of a dataframe. We can use the same indexers to change the values in a dataframe.
For example, we can get the English grades in the first two rows and change the values to 80.
# Get the English grades in the first two rows and change to 80
grades.iloc[:2, 1] = 80
Can you use loc
to change the math grades in the last two rows to 90?
# Change the math grades in the last two rows to 90
And of course, we can get a single value from a dataframe and change it using iat
or at
.
# Get Jane White's math grade and change it to 85
grades.at[3, 'Math'] = 85
It's your turn. Can you get Eve Lynn's English grade and change it to 82? This time, however, use iat
.
# Get Eve Lynn's math grade and change it to 85
Let's get the world cup dataframe again. We add a new column Score to store the scores of the games. Can you set the values in this column to the goals that were scored by the champion in the games?
# Add a new column of score
score = ["7-5", ### put the data in a list
"4-2",
"1-0",
"1-0",
"6-4",
"2-0",
"3-0",
"3-2",
"1-0",
"3-2"]
wcup['Score'] = score # make a new column of score
wcup
In soccer games, it is common to calculate the goals scored and goals conceded by the champion in the final.
# create a new column 'Goals Scored'
wcup['Goals Scored'] = wcup['Score'].str[0]
wcup
# create a new column 'Goals Conceded'
wcup['Goals Conceded'] = wcup['Score'].str[-1]
wcup
With the info on goals scored and conceded by the champion, we can create a column containing the difference between the two.
# Create a new column 'Difference'
wcup['Difference'] = wcup['Goals Scored'] - wcup['Goals Conceded']
We get an error! Why? The error message gives us a hint. The minus operator is not defined for the data type str!
Luckily, Pandas has a convenient method that allows us to convert data types.
# Create a new column 'Difference'
wcup['Difference'] = wcup['Goals Scored'].astype(int) - wcup['Goals Conceded'].astype(int)
wcup
Can you create two new columns in the grades dataframe, one with the students' first names, one with their last names?
# take a look at the grades df
grades
Congratulations! You have completed Pandas Basics 1.
Here are a few solutions for exercises in this lesson.
# Make a dataframe to record English and Math grades of the Butterfly class
butterfly = pd.DataFrame({"Name": ['John Smith',
'Alex Hazel',
'Beatrice Dean',
'Jane White',
'Eve Lynn'],
"English": [78,
80,
72,
75,
73],
"Math": [80,
75,
95,
70,
82]
})
butterfly
### Get the math grades of the first three students and write the data into an excel file
# Download the csv file
url = 'https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/Pandas1_grades.csv'
grades_file = './data/Grades.csv'
urllib.request.urlretrieve(url, grades_file)
# Read in the data and create a dataframe
butterfly = pd.read_csv(grades_file)
# Select the Math grades for the first three students
butterfly_slice = butterfly.loc[:3, ['Math']]
# Write the slice into a file of .xlsx format
butterfly_slice.to_excel('../data/butterfly_slice.xlsx')
# Can you create two new columns in the grades dataframe
# one with the students' first names, one with their last names
grades['First Name'] = grades['Name'].str.split().str[0]
grades['Last Name'] = grades['Name'].str.split().str[1]
grades