OverviewTeaching: 40 min
Exercises: 20 minQuestions
What dataset are we using today?
How can I process tabular data files in Python?Objectives
Explain what a library is and what libraries are used for.
Import the Pandas library
Use Pandas to read a simple CSV data set
Get some basic information about a Pandas Dataframe
We are using a dataset generated by Gapminder which describes income per person (GDP per capita) and life expectancy over a series of years between 1952 and 2007. We have a csv file for each continent and another that combines all data.
The data sets are stored in comma-separated values (CSV) format with each row holding information on a single country.
The first three rows of the first file look like this:
"continent","country","gdpPercap_1952","gdpPercap_1957","gdpPercap_1962","gdpPercap_1967","gdpPercap_1972","gdpPercap_1977","gdpPercap_1982","gdpPercap_1987","gdpPercap_1992","gdpPercap_1997","gdpPercap_2002","gdpPercap_2007","lifeExp_1952","lifeExp_1957","lifeExp_1962","lifeExp_1967","lifeExp_1972","lifeExp_1977","lifeExp_1982","lifeExp_1987","lifeExp_1992","lifeExp_1997","lifeExp_2002","lifeExp_2007","pop_1952","pop_1957","pop_1962","pop_1967","pop_1972","pop_1977","pop_1982","pop_1987","pop_1992","pop_1997","pop_2002","pop_2007" "Africa","Algeria",2449.008185,3013.976023,2550.81688,3246.991771,4182.663766,4910.416756,5745.160213,5681.358539,5023.216647,4797.295051,5288.040382,6223.367465,43.077,45.685,48.303,51.407,54.518,58.014,61.368,65.799,67.744,69.152,70.994,72.301,9279525,10270856,11000948,12760499,14760787,17152804,20033753,23254956,26298373,29072015,31287142,33333216 "Africa","Angola",3520.610273,3827.940465,4269.276742,5522.776375,5473.288005,3008.647355,2756.953672,2430.208311,2627.845685,2277.140884,2773.287312,4797.231267,30.015,31.999,34,35.985,37.928,39.483,39.942,39.906,40.647,40.963,41.003,42.731,4232095,4561361,4826015,5247469,5894858,6162675,7016384,7874230,8735988,9875024,10866106,12420476
We’ll learn more about how programming can help us explore this data.
Words are useful, but what’s more useful are the sentences and stories we build with them. Similarly, while a lot of powerful, general tools are built into Python, specialized tools built up from these basic units live in libraries that can be called upon when needed.
Use the Pandas library to do explore tabular data.
Importing a library is like getting a piece of lab equipment out of a storage locker and setting it up on the bench. Libraries provide additional functionality to the basic Python package, much like a new piece of equipment adds functionality to a lab space. Just like in the lab, importing too many libraries can sometimes complicate and slow down your programs - so we only import what we need for each program.
- Pandas is a widely-used Python library for statistics, particularly on tabular data.
- Borrows many features from R’s dataframes.
- A 2-dimensional table whose columns have names and potentially have different data types.
- Load it with
import pandas as pd. The alias pd is commonly used for Pandas.
- Read a Comma Separated Values (CSV) data file with
- Argument is the name of the file to be read.
- Assign result to a variable to store the data that was read.
Once we’ve imported the library, we can ask the library to read our data file for us:
import pandas as pd data = pd.read_csv('data/gapminder_gdp_oceania.csv') print(data)
country gdpPercap_1952 gdpPercap_1957 gdpPercap_1962 \ 0 Australia 10039.59564 10949.64959 12217.22686 1 New Zealand 10556.57566 12247.39532 13175.67800 gdpPercap_1967 gdpPercap_1972 gdpPercap_1977 gdpPercap_1982 \ 0 14526.12465 16788.62948 18334.19751 19477.00928 1 14463.91893 16046.03728 16233.71770 17632.41040 gdpPercap_1987 gdpPercap_1992 gdpPercap_1997 gdpPercap_2002 \ 0 21888.88903 23424.76683 26997.93657 30687.75473 1 19007.19129 18363.32494 21050.41377 23189.80135 gdpPercap_2007 0 34435.36744 1 25185.00911
pd.read_csv is a
that asks Python to run the function
belongs to the
This dotted notation
is used everywhere in Python: the thing that appears before the dot contains the thing that
As an example, John Smith is the John that belongs to the Smith family.
We could use the dot notation to write his name
read_csv is a function that belongs to the
Our call read our file and saved the data in memory to a variable called
data. We then checked our data had been loaded successfully by printing the variable’s value to the screen.
Pandas uses backslash
\ to show wrapped lines when output is too wide to fit the screen.
File Not Found
Our lessons store their data files in a
datasub-directory, which is why the path to the file is
data/gapminder_gdp_oceania.csv. If you forget to include
data/, or if you include it but your copy of the file is somewhere else, you will get a runtime error that ends with a line like this:
FileNotFoundError: [Errno 2] No such file or directory: 'data/gapminder_gdp_oceania.csv'
Now that the data are in memory, we can manipulate them.
index_col to specify that a column’s values should be used as row headings.
- Row headings are numbers (0 and 1 in this case).
- Really want to index by country.
- Pass the name of the column to
index_colparameter to do this.
data = pd.read_csv('data/gapminder_gdp_oceania.csv', index_col='country') print(data)
gdpPercap_1952 gdpPercap_1957 gdpPercap_1962 gdpPercap_1967 \ country Australia 10039.59564 10949.64959 12217.22686 14526.12465 New Zealand 10556.57566 12247.39532 13175.67800 14463.91893 gdpPercap_1972 gdpPercap_1977 gdpPercap_1982 gdpPercap_1987 \ country Australia 16788.62948 18334.19751 19477.00928 21888.88903 New Zealand 16046.03728 16233.71770 17632.41040 19007.19129 gdpPercap_1992 gdpPercap_1997 gdpPercap_2002 gdpPercap_2007 country Australia 23424.76683 26997.93657 30687.75473 34435.36744 New Zealand 18363.32494 21050.41377 23189.80135 25185.00911
DataFrame.info() method to find out more about a dataframe.
<class 'pandas.core.frame.DataFrame'> Index: 2 entries, Australia to New Zealand Data columns (total 12 columns): gdpPercap_1952 2 non-null float64 gdpPercap_1957 2 non-null float64 gdpPercap_1962 2 non-null float64 gdpPercap_1967 2 non-null float64 gdpPercap_1972 2 non-null float64 gdpPercap_1977 2 non-null float64 gdpPercap_1982 2 non-null float64 gdpPercap_1987 2 non-null float64 gdpPercap_1992 2 non-null float64 gdpPercap_1997 2 non-null float64 gdpPercap_2002 2 non-null float64 gdpPercap_2007 2 non-null float64 dtypes: float64(12) memory usage: 208.0+ bytes
- This is a
- Two rows named
- Twelve columns, each of which has two actual 64-bit floating point values.
- We will talk later about null values, which are used to represent missing observations.
- Uses 208 bytes of memory.
DataFrame.columns variable stores information about the dataframe’s columns.
- Note that this is data, not a method. (It doesn’t have parentheses.)
- So do not use
()to try to call it.
- Called a member variable, or just member.
Index(['gdpPercap_1952', 'gdpPercap_1957', 'gdpPercap_1962', 'gdpPercap_1967', 'gdpPercap_1972', 'gdpPercap_1977', 'gdpPercap_1982', 'gdpPercap_1987', 'gdpPercap_1992', 'gdpPercap_1997', 'gdpPercap_2002', 'gdpPercap_2007'], dtype='object')
DataFrame.T to transpose a dataframe.
- Sometimes want to treat columns as rows and vice versa.
- Transpose (written
.T) doesn’t copy the data, just changes the program’s view of it.
columns, it is a member variable.
- We will use this again when we try and plot the data.
country Australia New Zealand gdpPercap_1952 10039.59564 10556.57566 gdpPercap_1957 10949.64959 12247.39532 gdpPercap_1962 12217.22686 13175.67800 gdpPercap_1967 14526.12465 14463.91893 gdpPercap_1972 16788.62948 16046.03728 gdpPercap_1977 18334.19751 16233.71770 gdpPercap_1982 19477.00928 17632.41040 gdpPercap_1987 21888.88903 19007.19129 gdpPercap_1992 23424.76683 18363.32494 gdpPercap_1997 26997.93657 21050.41377 gdpPercap_2002 30687.75473 23189.80135 gdpPercap_2007 34435.36744 25185.00911
Note about Pandas DataFrames/Series
A DataFrame is a collection of Series; The DataFrame is the way Pandas represents a table, and Series is the data-structure Pandas use to represent a column.
Pandas is built on top of the Numpy library, which in practice means that most of the methods defined for Numpy Arrays apply to Pandas Series/DataFrames.
What makes Pandas so attractive is the powerful interface to access individual records of the table, proper handling of missing values, and relational-databases operations between DataFrames.
To access a value at the position
[i,j] of a DataFrame, we have two options, depending on
what is the meaning of
i in use.
Remember that a DataFrame provides an index as a way to identify the rows of the table;
a row, then, has a position inside the table as well as a label, which
uniquely identifies its entry in the DataFrame.
DataFrame.iloc[..., ...] to select values by their (entry) position
- Can specify location by numerical index analogously to 2D version of character selection in strings.
import pandas as pd data = pd.read_csv('data/gapminder_gdp_europe.csv', index_col='country') print(data.iloc[0, 0])
data[30, 20] accesses the element at row 30, column 20. While this expression may
not surprise you,
data[0, 0] might.
Programming languages like Fortran, MATLAB and R start counting at 1
because that’s what human beings have done for thousands of years.
Languages in the C family (including C++, Java, Perl, and Python) count from 0
because it represents an offset from the first value in the array (the second
value is offset by one index from the first value). This is closer to the way
that computers represent arrays (if you are interested in the historical
reasons behind counting indices from zero, you can read
Mike Hoye’s blog post).
As a result,
if we have an M×N array in Python,
its indices go from 0 to M-1 on the first axis
and 0 to N-1 on the second.
It takes a bit of getting used to,
but one way to remember the rule is that
the index is how many steps we have to take from the start to get the item we want.
In the Corner
What may also surprise you is that when Python displays an array, it shows the element with index
[0, 0]in the upper left corner rather than the lower left. This is consistent with the way mathematicians draw matrices but different from the Cartesian coordinates. The indices are (row, column) instead of (column, row) for the same reason, which can be confusing when plotting data.
DataFrame.loc[..., ...] to select values by their (entry) label.
- Can specify location by row name analogously to 2D version of dictionary keys.
: on its own to mean all columns or all rows.
- Just like Python’s usual slicing notation.
gdpPercap_1952 1601.056136 gdpPercap_1957 1942.284244 gdpPercap_1962 2312.888958 gdpPercap_1967 2760.196931 gdpPercap_1972 3313.422188 gdpPercap_1977 3533.003910 gdpPercap_1982 3630.880722 gdpPercap_1987 3738.932735 gdpPercap_1992 2497.437901 gdpPercap_1997 3193.054604 gdpPercap_2002 4604.211737 gdpPercap_2007 5937.029526 Name: Albania, dtype: float64
- Would get the same result printing
data.loc["Albania"](without a second index).
country Albania 1601.056136 Austria 6137.076492 Belgium 8343.105127 ⋮ ⋮ ⋮ Switzerland 14734.232750 Turkey 1969.100980 United Kingdom 9979.508487 Name: gdpPercap_1952, dtype: float64
- Would get the same result printing
- Also get the same result printing
data.gdpPercap_1952(not recommended, because easily confused with
.notation for methods)
Select multiple columns or rows using
DataFrame.loc and a named slice.
gdpPercap_1962 gdpPercap_1967 gdpPercap_1972 country Italy 8243.582340 10022.401310 12269.273780 Montenegro 4649.593785 5907.850937 7778.414017 Netherlands 12790.849560 15363.251360 18794.745670 Norway 13450.401510 16361.876470 18965.055510 Poland 5338.752143 6557.152776 8006.506993
In the above code, we discover that slicing using
loc is inclusive at both
ends, which differs from slicing using
iloc, where slicing indicates
everything up to but not including the final index.
Result of slicing can be used in further operations.
- Usually don’t just print a slice.
- All the statistical operators that work on entire dataframes work the same way on slices.
- E.g., calculate max of a slice.
gdpPercap_1962 13450.40151 gdpPercap_1967 16361.87647 gdpPercap_1972 18965.05551 dtype: float64
gdpPercap_1962 4649.593785 gdpPercap_1967 5907.850937 gdpPercap_1972 7778.414017 dtype: float64
Not All Functions Have Input
Generally, a function uses inputs to produce outputs. However, some functions produce outputs without needing any input. For example, checking the current time doesn’t require any input.
import time print(time.ctime())
Sat Mar 26 13:07:33 2016
For functions that don’t take in any arguments, we still need parentheses (
()) to tell Python to go and do something for us.
A section of an array is called a slice. We can take slices of character strings as well:
element = 'oxygen' print('first three characters:', element[0:3]) print('last three characters:', element[3:6])
first three characters: oxy last three characters: gen
What is the value of
element[:4]? What about
oxyg en oxygen
element[-1]? What is
Given those answers, explain what
Creates a substring from index 1 up to (not including) the final index, effectively removing the first and last letters from ‘oxygen’
How can we rewrite the slice for getting the last three characters of
element, so that it works even if we assign a different string to
element? Test your solution with the following strings:
element = 'oxygen' print('last three characters:', element[-3:]) element = 'carpentry' print('last three characters:', element[-3:]) element = 'clone' print('last three characters:', element[-3:]) element = 'hi' print('last three characters:', element[-3:])
last three characters: gen last three characters: try last three characters: one last three characters: hi
Selection of Individual Values
Assume Pandas has been imported into your notebook and the Gapminder GDP data for Europe has been loaded:
import pandas as pd df = pd.read_csv('data/gapminder_gdp_europe.csv', index_col='country')
Write an expression to find the Per Capita GDP of Serbia in 2007.
The selection can be done by using the labels for both the row (“Serbia”) and the column (“gdpPercap_2007”):
The output is
Extent of Slicing
- Do the two statements below produce the same output?
- Based on this, what rule governs what is included (or not) in numerical slices and named slices in Pandas?
print(df.iloc[0:2, 0:2]) print(df.loc['Albania':'Belgium', 'gdpPercap_1952':'gdpPercap_1962'])
No, they do not produce the same output! The output of the first statement is:
gdpPercap_1952 gdpPercap_1957 country Albania 1601.056136 1942.284244 Austria 6137.076492 8842.598030
The second statement gives:
gdpPercap_1952 gdpPercap_1957 gdpPercap_1962 country Albania 1601.056136 1942.284244 2312.888958 Austria 6137.076492 8842.598030 10750.721110 Belgium 8343.105127 9714.960623 10991.206760
Clearly, the second statement produces an additional column and an additional row compared to the first statement.
What conclusion can we draw? We see that a numerical slice, 0:2, omits the final index (i.e. index 2) in the range provided, while a named slice, ‘gdpPercap_1952’:’gdpPercap_1962’, includes the final element.
Explain what each line in the following short program does: what is in
first = pd.read_csv('data/gapminder_all.csv', index_col='country') second = first[first['continent'] == 'Americas'] third = second.drop('Puerto Rico') fourth = third.drop('continent', axis = 1) fourth.to_csv('result.csv')
Let’s go through this piece of code line by line.
first = pd.read_csv('data/gapminder_all.csv', index_col='country')
This line loads the dataset containing the GDP data from all countries into a dataframe called
index_col='country'parameter selects which column to use as the row labels in the dataframe.
second = first[first['continent'] == 'Americas']
This line makes a selection: only those rows of
firstfor which the ‘continent’ column matches ‘Americas’ are extracted. Notice how the Boolean expression inside the brackets,
first['continent'] == 'Americas', is used to select only those rows where the expression is true. Try printing this expression! Can you print also its individual True/False elements? (hint: first assign the expression to a variable)
third = second.drop('Puerto Rico')
As the syntax suggests, this line drops the row from
secondwhere the label is ‘Puerto Rico’. The resulting dataframe
thirdhas one row less than the original dataframe
fourth = third.drop('continent', axis = 1)
Again we apply the drop function, but in this case we are dropping not a row but a whole column. To accomplish this, we need to specify also the
axisparameter (we want to drop the second column which has index 1).
The final step is to write the data that we have been working on to a csv file. Pandas makes this easy with the
to_csv()function. The only required argument to the function is the filename. Note that the file will be written in the directory from which you started the Jupyter or Python session.
Explain in simple terms what
idxmaxdo in the short program below. When would you use these methods?
data = pd.read_csv('data/gapminder_gdp_europe.csv', index_col='country') print(data.idxmin()) print(data.idxmax())
For each column in
idxminwill return the index value corresponding to each column’s minimum;
idxmaxwill do accordingly the same for each column’s maximum value.
You can use these functions whenever you want to get the row index of the minimum/maximum value and not the actual minimum/maximum value.
Practice with Selection
Assume Pandas has been imported and the Gapminder GDP data for Europe has been loaded. Write an expression to select each of the following:
- GDP per capita for all countries in 1982.
- GDP per capita for Denmark for all years.
- GDP per capita for all countries for years after 1985.
- GDP per capita for each country in 2007 as a multiple of GDP per capita for that country in 1952.
Pandas is smart enough to recognize the number at the end of the column label and does not give you an error, although no column named
gdpPercap_1985actually exists. This is useful if new columns are added to the CSV file later.
Many Ways of Access
There are at least two ways of accessing a value or slice of a DataFrame: by name or index. However, there are many others. For example, a single column or row can be accessed either as a
Suggest different ways of doing the following operations on a DataFrame:
- Access a single column
- Access a single row
- Access an individual DataFrame element
- Access several columns
- Access several rows
- Access a subset of specific rows and columns
- Access a subset of row and column ranges
1. Access a single column:
# by name data["col_name"] # as a Series data[["col_name"]] # as a DataFrame # by name using .loc data.T.loc["col_name"] # as a Series data.T.loc[["col_name"]].T # as a DataFrame # Dot notation (Series) data.col_name # by index (iloc) data.iloc[:, col_index] # as a Series data.iloc[:, [col_index]] # as a DataFrame # using a mask data.T[data.T.index == "col_name"].T
2. Access a single row:
# by name using .loc data.loc["row_name"] # as a Series data.loc[["row_name"]] # as a DataFrame # by name data.T["row_name"] # as a Series data.T[["row_name"]].T as a DataFrame # by index data.iloc[row_index] # as a Series data.iloc[[row_index]] # as a DataFrame # using mask data[data.index == "row_name"]
3. Access an individual DataFrame element:
# by column/row names data["column_name"]["row_name"] # as a Series data[["col_name"]].loc["row_name"] # as a Series data[["col_name"]].loc[["row_name"]] # as a DataFrame data.loc["row_name"]["col_name"] # as a value data.loc[["row_name"]]["col_name"] # as a Series data.loc[["row_name"]][["col_name"]] # as a DataFrame data.loc["row_name", "col_name"] # as a value data.loc[["row_name"], "col_name"] # as a Series. Preserves index. Column name is moved to `.name`. data.loc["row_name", ["col_name"]] # as a Series. Index is moved to `.name.` Sets index to column name. data.loc[["row_name"], ["col_name"]] # as a DataFrame (preserves original index and column name) # by column/row names: Dot notation data.col_name.row_name # by column/row indices data.iloc[row_index, col_index] # as a value data.iloc[[row_index], col_index] # as a Series. Preserves index. Column name is moved to `.name` data.iloc[row_index, [col_index]] # as a Series. Index is moved to `.name.` Sets index to column name. data.iloc[[row_index], [col_index]] # as a DataFrame (preserves original index and column name) # column name + row index data["col_name"][row_index] data.col_name[row_index] data["col_name"].iloc[row_index] # column index + row name data.iloc[:, [col_index]].loc["row_name"] # as a Series data.iloc[:, [col_index]].loc[["row_name"]] # as a DataFrame # using masks data[data.index == "row_name"].T[data.T.index == "col_name"].T
4. Access several columns:
# by name data[["col1", "col2", "col3"]] data.loc[:, ["col1", "col2", "col3"]] # by index data.iloc[:, [col1_index, col2_index, col3_index]]
5. Access several rows
# by name data.loc[["row1", "row2", "row3"]] # by index data.iloc[[row1_index, row2_index, row3_index]]
6. Access a subset of specific rows and columns
# by names data.loc[["row1", "row2", "row3"], ["col1", "col2", "col3"]] # by indices data.iloc[[row1_index, row2_index, row3_index], [col1_index, col2_index, col3_index]] # column names + row indices data[["col1", "col2", "col3"]].iloc[[row1_index, row2_index, row3_index]] # column indices + row names data.iloc[:, [col1_index, col2_index, col3_index]].loc[["row1", "row2", "row3"]]
7. Access a subset of row and column ranges
# by name data.loc["row1":"row2", "col1":"col2"] # by index data.iloc[row1_index:row2_index, col1_index:col2_index] # column names + row indices data.loc[:, "col1_name":"col2_name"].iloc[row1_index:row2_index] # column indices + row names data.iloc[:, col1_index:col2_index].loc["row1":"row2"]
Import a library into a program using
pandaslibrary to work with arrays in Python.
Array indices start at 0, not 1.
DataFrame.iloc[..., ...]to select values by integer location.
:on its own to mean all columns or all rows.
Select multiple columns or rows using
DataFrame.locand a named slice.
Result of slicing can be used in further operations.