Big Data Analytics – ITM 740

More Python

Lab 2
School of Information Technology Management
Ted Rogers School of Management
Toronto Metropolitan University
Winter 2024
 Data Frames groupby() method

Using "group by" method we can:

• Split the data into groups based on some criteria
• Calculate statistics (or apply a function) to each group
• Similar to dplyr() function in R
In [ ]: #Group data using rank
df_rank = df.groupby(['rank'])

In [ ]: #Calculate mean value for each numeric column per each group
df_rank.mean()

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business

 Data Frames groupby method

Once groupby object is create we can calculate various statistics for each group:

In [ ]: #Calculate mean salary for each professor rank:

df.groupby('rank')[['salary']].mean()

Note: If single brackets are used to specify the column (e.g. salary), then the output is Pandas
Series object. When double brackets are used the output is a Data Frame
Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 3

 Data Frames groupby method

groupby performance notes:

- no grouping/splitting occurs until it's needed. Creating the groupby
object only verifies that you have passed a valid mapping
- by default the group keys are sorted during the groupby operation. You
may want to pass sort=False for potential speedup:

In [ ]: #Calculate mean salary for each professor rank:

df.groupby(['rank'], sort=False)[['salary']].mean()

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 4

 Data Frame: filtering
To subset the data we can apply Boolean indexing. This indexing is
commonly known as a filter. For example if we want to subset the rows in
which the salary value is greater than $120K:
In [ ]: #Calculate mean salary for each professor rank:
df_sub = df[ df['salary'] > 120000 ]

Any Boolean operator can be used to subset the data:

> greater; >= greater or equal;
< less; <= less or equal;
== equal; != not equal;
In [ ]: #Select only those rows that contain female professors:
df_f = df[ df['sex'] == 'Female' ]

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 5

 Data Frames: Slicing

There are a number of ways to subset the Data Frame:

• one or more columns
• one or more rows
• a subset of rows and columns

Rows and columns can be selected by their position or label

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 6

 Data Frames: Slicing

When selecting one column, it is possible to use single set of brackets, but
the resulting object will be a Series (not a DataFrame):
In [ ]: #Select column salary:
df['salary']

When we need to select more than one column and/or make the output to
be a DataFrame, we should use double brackets:
In [ ]: #Select column salary:
df[['rank','salary']]

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 7

 Data Frames: Selecting rows

If we need to select a range of rows, we can specify the range using ":"

In [ ]: #Select rows by their position:

df[10:20]

Notice that the first row has a position 0, and the last value in the range is
omitted:
So for 0:10 range the first 10 rows are returned with the positions starting
with 0 and ending with 9

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 8

 Data Frames: method loc

If we need to select a range of rows, using their labels we can use method
loc:
In [ ]: #Select rows by their labels:
df_sub.loc[10:20,['rank','sex','salary']]

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 9

 Data Frames: method iloc
If we need to select a range of rows and/or columns, using their positions
we can use method iloc:
In [ ]: #Select rows by their labels:
df_sub.iloc[10:20,[0, 3, 4, 5]]

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 10

 Data Frames: method iloc (summary)

df.iloc[0] # First row of a data frame

df.iloc[i] #(i+1)th row
df.iloc[-1] # Last row

df.iloc[:, 0] # First column

df.iloc[:, -1] # Last column

df.iloc[0:7] #First 7 rows

df.iloc[:, 0:2] #First 2 columns
df.iloc[1:3, 0:2] #Second through third rows and first 2 columns
df.iloc[[0,5], [1,3]] #1st and 6th rows and 2nd and 4th columns

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 11

 Data Frames: Sorting

We can sort the data by a value in the column. By default the sorting will
occur in ascending order and a new data frame is return.

In [ ]: # Create a new data frame from the original sorted by the column Salary
df_sorted = df.sort_values( by ='service')
df_sorted.head()

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 12

 Data Frames: Sorting
We can sort the data using 2 or more columns:
In [ ]: df_sorted = df.sort_values( by =['service', 'salary'], ascending = [True, False])
df_sorted.head(10)

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 13

 Missing Values

Missing values are marked as NaN

In [ ]: # Read a dataset with missing values
flights = pd.read_csv("flights.csv")

In [ ]: # Select the rows that have at least one missing value

flights[flights.isnull().any(axis=1)].head()

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 14

 Missing Values
There are a number of methods to deal with missing values in the data
frame:
df.method() description
dropna() Drop missing observations

dropna(how='all') Drop observations where all cells is NA

dropna(axis=1, Drop column if all the values are missing

how='all')
dropna(thresh = 5) Drop rows that contain less than 5 non-missing values

fillna(0) Replace missing values with zeros

isnull() returns True if the value is missing

notnull() Returns True for non-missing values

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 15

 Pandas DataFrame.dropna()
 DataFrameName.dropna(axis=0, how='any', thresh=None, subset=None,
inplace=False)
 Parameters:
axis: axis takes int or string value for rows/columns. Input can be 0 or 1 for Integer
and ‘index’ or ‘columns’ for String.
how: how takes string value of two kinds only (‘any’ or ‘all’). ‘any’ drops the
row/column if ANY value is Null and ‘all’ drops only if ALL values are null.
thresh: thresh takes integer value which tells minimum amount of na values to
drop.
subset: It’s an array which limits the dropping process to passed rows/columns
through list.
inplace: It is a boolean which makes the changes in data frame itself if True.

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 16

 Examples
In [ ]: #importing libraries
import pandas as pd
import numpy as np
# creating a sample DataFrame
sales_data =
pd.DataFrame({"name":["William","Emma","Sofia","Markus","Edward","Thom
as","Ethan",np.nan,"Arun","Anika","Paulo"]
,"region":[np.nan,"North","East","South","West","West","South",np.nan,
"West","East","South"]
,"sales":[50000,52000,90000,np.nan,42000,72000,49000,np.nan,67000,6500
0,67000]
,"expenses":[42000,43000,np.nan,44000,38000,39000,42000,np.nan,39000,4
4000,45000]})
sales_data

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 17

 Examples
Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 18

 Examples
In [ ]: sales_data.dropna()

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 19

 Examples
In [ ]: sales_data.dropna(how = 'all')

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 20

 Examples
In [ ]: sales_data.dropna(subset = ['sales', 'expenses'])

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 21

 Missing Values

• When summing the data, missing values will be treated as zero

• If all values are missing, the sum will be equal to NaN
• cumsum() and cumprod() methods ignore missing values but preserve
them in the resulting arrays
• Missing values in GroupBy method are excluded (just like in R)
• Many descriptive statistics methods have skipna option to control if
missing data should be excluded . This value is set to True by default
(unlike R)

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 22

 Aggregation Functions in Pandas

Aggregation - computing a summary statistic about each group, i.e.

• compute group sums or means
• compute group sizes/counts

Common aggregation functions:

min, max
count, sum, prod
mean, median, mode, mad
std, var

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 23

 Aggregation Functions in Pandas

agg() method are useful when multiple statistics are computed per column:
In [ ]: flights[['dep_delay','arr_delay']].agg(['min','mean','max'])

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 24

 Basic Descriptive Statistics

df.method() description
describe Basic statistics (count, mean, std, min, quantiles, max)

min, max Minimum and maximum values

mean, median, mode Arithmetic average, median and mode

var, std Variance and standard deviation

sem Standard error of mean

skew Sample skewness

kurt kurtosis

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 25

 Graphics to explore the data

Seaborn package is built on matplotlib but provides high level

interface for drawing attractive statistical graphics, similar to
ggplot2 library in R. It specifically targets statistical data
visualization

To show graphs within Python notebook include inline directive:

In [ ]: %matplotlib inline

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 26

 Graphics

description
distplot histogram
barplot estimate of central tendency for a numeric variable
violinplot similar to boxplot, also shows the probability density of the
data
jointplot Scatterplot
regplot Regression plot
pairplot Pairplot
boxplot boxplot
swarmplot categorical scatterplot
factorplot General categorical plot

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 27

 Example
In [ ]: import matplotlib.pyplot as plt
import seaborn as sns, numpy as np

sns.set(rc={"figure.figsize": (8, 4)}); np.random.seed(0)

x = np.random.randn(100)
ax = sns.distplot(x)
plt.show()

Out[ ]:

Modified based on: Python for Data Analysis, Research Computing Services, Boston University

ITM 740 Artificial Intelligence in Business 28