Pandas dataframes¶
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
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
Bosnia and Herzegovina 973.533195
Bulgaria 2444.286648
Croatia 3119.236520
Czech Republic 6876.140250
Denmark 9692.385245
Finland 6424.519071
France 7029.809327
Germany 7144.114393
Greece 3530.690067
Hungary 5263.673816
Iceland 7267.688428
Ireland 5210.280328
Italy 4931.404155
Montenegro 2647.585601
Netherlands 8941.571858
Norway 10095.421720
Poland 4029.329699
Portugal 3068.319867
Romania 3144.613186
Serbia 3581.459448
Slovak Republic 5074.659104
Slovenia 4215.041741
Spain 3834.034742
Sweden 8527.844662
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.¶
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.
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.
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
Use comparisons to select data based on value.¶
Comparison is applied element by element.
Returns a similarly-shaped dataframe of
TrueandFalse.
# Use a subset of data to keep output readable.
subset = data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972']
print('Subset of data:\n', subset)
# Which values were greater than 10000 ?
print('\nWhere are values large?\n', subset > 10000)
Subset of data:
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
Where are values large?
gdpPercap_1962 gdpPercap_1967 gdpPercap_1972
country
Italy False True True
Montenegro False False False
Netherlands True True True
Norway True True True
Poland False False False
Select values or NaN using a Boolean mask.¶
A frame full of Booleans is sometimes called a mask because of how it can be used.
mask = subset > 10000
print(subset[mask])
gdpPercap_1962 gdpPercap_1967 gdpPercap_1972
country
Italy NaN 10022.40131 12269.27378
Montenegro NaN NaN NaN
Netherlands 12790.84956 15363.25136 18794.74567
Norway 13450.40151 16361.87647 18965.05551
Poland NaN NaN NaN
Get the value where the mask is true, and NaN (Not a Number) where it is false.
Useful because NaNs are ignored by operations like max, min, average, etc.
print(subset[subset > 10000].describe())
gdpPercap_1962 gdpPercap_1967 gdpPercap_1972
count 2.000000 3.000000 3.000000
mean 13120.625535 13915.843047 16676.358320
std 466.373656 3408.589070 3817.597015
min 12790.849560 10022.401310 12269.273780
25% 12955.737547 12692.826335 15532.009725
50% 13120.625535 15363.251360 18794.745670
75% 13285.513523 15862.563915 18879.900590
max 13450.401510 16361.876470 18965.055510
Group By: split-apply-combine¶
Pandas vectorizing methods and grouping operations are features that provide users much flexibility to analyse their data.
For instance, let’s say we want to have a clearer view on how the European countries split themselves according to their GDP.
We may have a glance by splitting the countries in two groups during the years surveyed, those who presented a GDP higher than the European average and those with a lower GDP.
We then estimate a wealthy score based on the historical (from 1962 to 2007) values, where we account how many times a country has participated in the groups of lower or higher GDP
mask_higher = data > data.mean()
wealth_score = mask_higher.aggregate('sum', axis=1) / len(data.columns)
wealth_score
country
Albania 0.000000
Austria 1.000000
Belgium 1.000000
Bosnia and Herzegovina 0.000000
Bulgaria 0.000000
Croatia 0.000000
Czech Republic 0.500000
Denmark 1.000000
Finland 1.000000
France 1.000000
Germany 1.000000
Greece 0.333333
Hungary 0.000000
Iceland 1.000000
Ireland 0.333333
Italy 0.500000
Montenegro 0.000000
Netherlands 1.000000
Norway 1.000000
Poland 0.000000
Portugal 0.000000
Romania 0.000000
Serbia 0.000000
Slovak Republic 0.000000
Slovenia 0.333333
Spain 0.333333
Sweden 1.000000
Switzerland 1.000000
Turkey 0.000000
United Kingdom 1.000000
dtype: float64
Finally, for each group in the wealth_score table, we sum their (financial) contribution across the years surveyed using chained methods:
data.groupby(wealth_score).sum()
| gdpPercap_1952 | gdpPercap_1957 | gdpPercap_1962 | gdpPercap_1967 | gdpPercap_1972 | gdpPercap_1977 | gdpPercap_1982 | gdpPercap_1987 | gdpPercap_1992 | gdpPercap_1997 | gdpPercap_2002 | gdpPercap_2007 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0.000000 | 36916.854200 | 46110.918793 | 56850.065437 | 71324.848786 | 88569.346898 | 104459.358438 | 113553.768507 | 119649.599409 | 92380.047256 | 103772.937598 | 118590.929863 | 149577.357928 |
| 0.333333 | 16790.046878 | 20942.456800 | 25744.935321 | 33567.667670 | 45277.839976 | 53860.456750 | 59679.634020 | 64436.912960 | 67918.093220 | 80876.051580 | 102086.795210 | 122803.729520 |
| 0.500000 | 11807.544405 | 14505.000150 | 18380.449470 | 21421.846200 | 25377.727380 | 29056.145370 | 31914.712050 | 35517.678220 | 36310.666080 | 40723.538700 | 45564.308390 | 51403.028210 |
| 1.000000 | 104317.277560 | 127332.008735 | 149989.154201 | 178000.350040 | 215162.343140 | 241143.412730 | 263388.781960 | 296825.131210 | 315238.235970 | 346930.926170 | 385109.939210 | 427850.333420 |
Exercise: 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.
See 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'])
Exercise: 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'])
See 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.
Exercise: 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')
See Solution
Let’s go through this piece of code line by line.
first = pd.read_csv('data/gapminder_all.csv', index_col='country')
{: .language-python}
This line loads the dataset containing the GDP data from all countries into a dataframe called
first. The 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 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 dataframe third has one row less than the original dataframe second.
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.
Exercise: Selecting Indices
Explain in simple terms what idxmin and idxmax 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())
See 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']
Exploring available methods using the dir() function¶
Python includes a dir() function that can be used to display all of the available methods (functions) that are built into a data object. In Episode 4, we used some methods with a string. But we can see many more are available by using dir():
my_string = 'Hello world!' # creation of a string object
dir(myString)
{: .language-python} This command returns:
['__add__',
...
'__subclasshook__',
'capitalize',
'casefold',
'center',
...
'upper',
'zfill']
You can use help() or Shift+Tab to get more information about what these methods do.
Assume Pandas has been imported and the Gapminder GDP data for Europe has been loaded as data. Then, use dir() to find the function that prints out the median per-capita GDP across all European countries for each year that information is available.
Solution¶
Among many choices, dir() lists the median() function as a possibility. Thus,
data.median()
Interpretation¶
Poland’s borders have been stable since 1945, but changed several times in the years before then. How would you handle this if you were creating a table of GDP per capita for Poland for the entire twentieth century?
Test a directive
Note
This text is standard Markdown