Analyzing Data
Overview
Teaching: 40 min
Exercises: 20 minQuestions
What dataset are we using today?
How can I process tabular data files in Python?
Objectives
Introduce dataset
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
Our dataset
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.
Libraries
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
pd.read_csv
.- 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
The expression pd.read_csv
is a
function call
that asks Python to run the function read_csv
which
belongs to the pandas
library.
This dotted notation
is used everywhere in Python: the thing that appears before the dot contains the thing that
appears after.
As an example, John Smith is the John that belongs to the Smith family.
We could use the dot notation to write his name smith.john
,
just as read_csv
is a function that belongs to the pandas
library.
pandas.read_csv
has one parameter: the name of the file
we want to read. This needs to be character string
(or string for short), so we put it in quotes.
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
data
sub-directory, which is why the path to the file isdata/gapminder_gdp_oceania.csv
. If you forget to includedata/
, 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.
Use 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
read_csv
as itsindex_col
parameter 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
Use the DataFrame.info()
method to find out more about a dataframe.
data.info()
<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
DataFrame
- Two rows named
'Australia'
and'New Zealand'
- 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.
The DataFrame.columns
variable stores information about the dataframe’s columns.
- Note that this is data, not a method. (It doesn’t have parentheses.)
- Like
math.pi
. - So do not use
()
to try to call it.
- Like
- Called a member variable, or just member.
print(data.columns)
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')
Use 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. - Like
columns
, it is a member variable. - We will use this again when we try and plot the data.
print(data.T)
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.
Selecting values
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.
Use 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])
1601.056136
The expression 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.
Use DataFrame.loc[..., ...]
to select values by their (entry) label.
- Can specify location by row name analogously to 2D version of dictionary keys.
print(data.loc["Albania", "gdpPercap_1952"])
1601.056136
Use :
on its own to mean all columns or all rows.
- Just like Python’s usual slicing notation.
print(data.loc["Albania", :])
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).
print(data.loc[:, "gdpPercap_1952"])
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
data["gdpPercap_1952"]
- 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.
print(data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972'])
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.
print(data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972'].max())
gdpPercap_1962 13450.40151
gdpPercap_1967 16361.87647
gdpPercap_1972 18965.05551
dtype: float64
print(data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972'].min())
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.
Slicing Strings
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 aboutelement[4:]
? Orelement[:]
?Solution
oxyg en oxygen
What is
element[-1]
? What iselement[-2]
?Solution
n e
Given those answers, explain what
element[1:-1]
does.Solution
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 toelement
? Test your solution with the following strings:carpentry
,clone
,hi
.Solution
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.
Solution
The selection can be done by using the labels for both the row (“Serbia”) and the column (“gdpPercap_2007”):
print(df.loc['Serbia', 'gdpPercap_2007'])
The output is
9786.534714
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'])
Solution
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.
Reconstructing Data
Explain what each line in the following short program does: what is in
first
,second
, etc.?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')
Solution
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
first
. Theindex_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
first
for 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
second
where the label is ‘Puerto Rico’. The resulting dataframethird
has one row less than the original dataframesecond
.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
axis
parameter (we want to drop the second column which has index 1).fourth.to_csv('result.csv')
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.
Selecting Indices
Explain in simple terms what
idxmin
andidxmax
do 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())
Solution
For each column in
data
,idxmin
will return the index value corresponding to each column’s minimum;idxmax
will 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.
Solution
1:
data['gdpPercap_1982']
2:
data.loc['Denmark',:]
3:
data.loc[:,'gdpPercap_1985':]
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_1985
actually exists. This is useful if new columns are added to the CSV file later.4:
data['gdpPercap_2007']/data['gdpPercap_1952']
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
DataFrame
or aSeries
object.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
Solution
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"]
Key Points
Import a library into a program using
import libraryname
.Use the
pandas
library to work with arrays in Python.Array indices start at 0, not 1.
Use
DataFrame.iloc[..., ...]
to select values by integer location.Use
:
on its own to mean all columns or all rows.Select multiple columns or rows using
DataFrame.loc
and a named slice.Result of slicing can be used in further operations.