Data Iteration, Indexing and Filters¶

In this notebook we will learn indexing DataFrame with loc and iloc functionality and slicing operation. Some Data Filtering techniques will also be conducted.

Read more about these functionality from Pydata documentation for indexing[1]. Some parts of this notebook are taken from EuroScipy 2016 Pandas Tutorial by Joris Van den Bossche and Nicholas Devenish[2]

In [5]:

import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline

In [6]:

titanic = pd.read_csv('data/titanic.csv')
titanic = titanic.set_index("Name")

In [7]:

titanic.head(2)

Out[7]:

	PassengerId	Survived	Pclass	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked
Name
Braund, Mr. Owen Harris	1	0	3	male	22.0	1	0	A/5 21171	7.2500	NaN	S
Cumings, Mrs. John Bradley (Florence Briggs Thayer)	2	1	1	female	38.0	1	0	PC 17599	71.2833	C85	C

Lets create a small sample of data

In [8]:

sample = titanic[0:5]

1. Iteration of rows implementing `iterrows`¶

In [9]:

for index,row in sample.iterrows():
    print(index,list(row))

Braund, Mr. Owen Harris [1, 0, 3, 'male', 22.0, 1, 0, 'A/5 21171', 7.25, nan, 'S']
Cumings, Mrs. John Bradley (Florence Briggs Thayer) [2, 1, 1, 'female', 38.0, 1, 0, 'PC 17599', 71.2833, 'C85', 'C']
Heikkinen, Miss. Laina [3, 1, 3, 'female', 26.0, 0, 0, 'STON/O2. 3101282', 7.925, nan, 'S']
Futrelle, Mrs. Jacques Heath (Lily May Peel) [4, 1, 1, 'female', 35.0, 1, 0, '113803', 53.1, 'C123', 'S']
Allen, Mr. William Henry [5, 0, 3, 'male', 35.0, 0, 0, '373450', 8.05, nan, 'S']

We can select specific columns by passing column names in row() input

In [10]:

for index,row in sample.iterrows():
    print(index,row['Sex'],row['Age'])

Braund, Mr. Owen Harris male 22.0
Cumings, Mrs. John Bradley (Florence Briggs Thayer) female 38.0
Heikkinen, Miss. Laina female 26.0
Futrelle, Mrs. Jacques Heath (Lily May Peel) female 35.0
Allen, Mr. William Henry male 35.0

2. Iteration of rows with `iteritems`¶

In [11]:

for index,row in sample.T.iteritems():
    print(index,list(row))

Braund, Mr. Owen Harris [1, 0, 3, 'male', 22.0, 1, 0, 'A/5 21171', 7.25, nan, 'S']
Cumings, Mrs. John Bradley (Florence Briggs Thayer) [2, 1, 1, 'female', 38.0, 1, 0, 'PC 17599', 71.2833, 'C85', 'C']
Heikkinen, Miss. Laina [3, 1, 3, 'female', 26.0, 0, 0, 'STON/O2. 3101282', 7.925, nan, 'S']
Futrelle, Mrs. Jacques Heath (Lily May Peel) [4, 1, 1, 'female', 35.0, 1, 0, '113803', 53.1, 'C123', 'S']
Allen, Mr. William Henry [5, 0, 3, 'male', 35.0, 0, 0, '373450', 8.05, nan, 'S']

In [12]:

for index,row in sample.iteritems():
    print(index,row[0],row[1],row[2])

PassengerId 1 2 3
Survived 0 1 1
Pclass 3 1 3
Sex male female female
Age 22.0 38.0 26.0
SibSp 1 1 0
Parch 0 0 0
Ticket A/5 21171 PC 17599 STON/O2. 3101282
Fare 7.25 71.2833 7.925
Cabin nan C85 nan
Embarked S C S

3. Indexing Data¶

Source: Using iloc, loc, & ix to select rows and columns in Pandas DataFrames

loc and iloc :

The iloc indexer for Pandas Dataframe is used for integer-location based indexing / selection by position.

The Pandas loc indexer can be used with DataFrames for two different use cases:

a.) Selecting rows by label/index
b.) Selecting rows with a boolean / conditional lookup

In [13]:

sample.iloc[0:2,:]

Out[13]:

	PassengerId	Survived	Pclass	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked
Name
Braund, Mr. Owen Harris	1	0	3	male	22.0	1	0	A/5 21171	7.2500	NaN	S
Cumings, Mrs. John Bradley (Florence Briggs Thayer)	2	1	1	female	38.0	1	0	PC 17599	71.2833	C85	C

In [14]:

sample.iloc[1,0:3]

Out[14]:

PassengerId    2
Survived       1
Pclass         1
Name: Cumings, Mrs. John Bradley (Florence Briggs Thayer), dtype: object

In [15]:

sample.loc[:,'Survived': 'Ticket']

Out[15]:

	Survived	Pclass	Sex	Age	SibSp	Parch	Ticket
Name
Braund, Mr. Owen Harris	0	3	male	22.0	1	0	A/5 21171
Cumings, Mrs. John Bradley (Florence Briggs Thayer)	1	1	female	38.0	1	0	PC 17599
Heikkinen, Miss. Laina	1	3	female	26.0	0	0	STON/O2. 3101282
Futrelle, Mrs. Jacques Heath (Lily May Peel)	1	1	female	35.0	1	0	113803
Allen, Mr. William Henry	0	3	male	35.0	0	0	373450

In [16]:

sample.loc['Braund, Mr. Owen Harris',:]

Out[16]:

PassengerId            1
Survived               0
Pclass                 3
Sex                 male
Age                   22
SibSp                  1
Parch                  0
Ticket         A/5 21171
Fare                7.25
Cabin                NaN
Embarked               S
Name: Braund, Mr. Owen Harris, dtype: object

4 - Data Filters:¶

Data Filters can be performed by either selecting specific set of column names or by seting boolean filters.

Dictionary to DataFrame

In [17]:

data = {'country': ['Belgium', 'France', 'Germany', 'Netherlands', 'United Kingdom'],
        'population': [11.3, 64.3, 81.3, 16.9, 64.9],
        'area': [30510, 671308, 357050, 41526, 244820],
        'capital': ['Brussels', 'Paris', 'Berlin', 'Amsterdam', 'London']}



countries = pd.DataFrame(data)
countries

Out[17]:

	country	population	area	capital
0	Belgium	11.3	30510	Brussels
1	France	64.3	671308	Paris
2	Germany	81.3	357050	Berlin
3	Netherlands	16.9	41526	Amsterdam
4	United Kingdom	64.9	244820	London

To set the country as index:

In [18]:

countries = countries.set_index('country')
countries

Out[18]:

	population	area	capital
country
Belgium	11.3	30510	Brussels
France	64.3	671308	Paris
Germany	81.3	357050	Berlin
Netherlands	16.9	41526	Amsterdam
United Kingdom	64.9	244820	London

To find the area of each country:

In [19]:

countries['area']

Out[19]:

country
Belgium            30510
France            671308
Germany           357050
Netherlands        41526
United Kingdom    244820
Name: area, dtype: int64

To create a list instead of pandas Series object.

In [20]:

Area = list(countries['area'])

In [21]:

Area

Out[21]:

[30510, 671308, 357050, 41526, 244820]

To create a sub-DataFrame by choosing area and population

In [22]:

countries[['area', 'population']]

Out[22]:

	area	population
country
Belgium	30510	11.3
France	671308	64.3
Germany	357050	81.3
Netherlands	41526	16.9
United Kingdom	244820	64.9

To observe slice of data by index names.

In [12]:

countries['France':'Netherlands']

Out[12]:

	population	area	capital
country
France	64.3	671308	Paris
Germany	81.3	357050	Berlin
Netherlands	16.9	41526	Amsterdam

To observe the data at specific row identified by index name(s) and spefic column identified by columnname(s):

In [23]:

countries.loc['Germany', 'area']

Out[23]:

In [24]:

countries.loc['France':'Germany', ['area', 'population']]

Out[24]:

	area	population
country
France	671308	64.3
Germany	357050	81.3

To set boolean selection for subDataFrame:

In [18]:

countries['area'] > 100000

Out[18]:

country
Belgium           False
France             True
Germany            True
Netherlands       False
United Kingdom     True
Name: area, dtype: bool

In [19]:

countries[countries['area'] > 100000]

Out[19]:

	population	area	capital
country
France	64.3	671308	Paris
Germany	81.3	357050	Berlin
United Kingdom	64.9	244820	London

5. Exercises:¶

EXERCISE: Add a column `density` with the population density (note: population column is expressed in millions)

In [20]:

countries['density'] = countries['population']*1000000 / countries['area']
countries

Out[20]:

	population	area	capital	density
country
Belgium	11.3	30510	Brussels	370.370370
France	64.3	671308	Paris	95.783158
Germany	81.3	357050	Berlin	227.699202
Netherlands	16.9	41526	Amsterdam	406.973944
United Kingdom	64.9	244820	London	265.092721

EXERCISE: Select the capital and the population column of those countries where the density is larger than 300