Week 2: Data Management

Preparating data for predictive analytics

Contents
Learn managing data through two examples

Example 1: 1000 IMDB Movies

Example 2: Olympic Medals

import pandas

In [1]: # import pandas library, for simplicity, let's call it pd

# pandas is the most useful library of Python for managing & analysizing data
import pandas as pd
Example 1: 1000 IMDB Movies
Visit the following website:

[Link]
resources/master/Python%20Pandas%20Tutorial%20A%20Complete%20Introduction%20for%20Beginners/IMDB-
[Link] ([Link]
resources/master/Python%20Pandas%20Tutorial%20A%20Complete%20Introduction%20for%20Beginners/IMDB-
[Link])

1000 movies in the dataset (2006-2016)

Part 1: Import & Display Data

[Link]
load [Link] with [Link]

Method 1: (1 Step) load data straight from the website with pd.read_csv("[Link]
Method 2: (2 steps) (1) download data from website (or L@G), save it in the computer (e.g., D:/xxx/[Link]), (2) load
it with pd.read_csv("C:/xxx/[Link]")
Method 3: (3 steps) (1) get data from website (or L@G), (2) upload it to Jupyter Note, (3) load it with
pd.read_csv("[Link]")
[Link]
resources/master/Python%20Pandas%20Tutorial%20A%20Complete%20Introduction%20for%20Beginners/IMDB-
[Link] ([Link]
resources/master/Python%20Pandas%20Tutorial%20A%20Complete%20Introduction%20for%20Beginners/IMDB-
[Link])

In [2]: # bypass (disable) SSL verification in Python (if you are using a uni computer)
# execute this cell only if necessary!

import ssl
try:
_create_unverified_https_context = ssl._create_unverified_context
except AttributeError:
# Legacy Python that doesn't verify HTTPS certificates by default
pass
else:
# Handle target environment that doesn't support HTTPS verification
ssl._create_default_https_context = _create_unverified_https_context

In [6]: # Method 1: Load data from web

url='[Link]
0Pandas%20Tutorial%20A%20Complete%20Introduction%20for%20Beginners/[Link]
v'
movie = pd.read_csv(url)
 In [7]: # save datafram "movie" as an Excel file (under current Jupyter directory)
# check Jupyter
movie.to_excel("[Link]", index=False)

In [8]: # Method 2: load [Link] from current Jupyter dir

movie=pd.read_excel("[Link]")

In [ ]:

head, tail, sample

In [9]: # print data (the first 4 rows)

[Link](3)

Out[9]:
Runtime
Rank Title Genre Description Director Actors Year Rating
(Minutes)

Chris
A group of
Pratt, Vin
Guardians intergalactic
Action,Adventure,Sci- James Diesel,
0 1 of the criminals 2014 121 8.1 75
Fi Gunn Bradley
Galaxy are forced
Cooper,
...
Zoe S...

Noomi
Following Rapace,
clues to the Logan
Adventure,Mystery,Sci- Ridley
1 2 Prometheus origin of Marshall- 2012 124 7.0 48
Fi Scott
mankind, a Green,
te... Michael
Fa...

James
Three girls
McAvoy,
are
Anya
kidnapped M. Night
2 3 Split Horror,Thriller Taylor- 2016 117 7.3 15
by a man Shyamalan
Joy,
with a
Haley Lu
diag...
Richar...

In [10]: # print data, the tail (bottom) 2 rows

[Link](2)

Out[10]:
Runtime
Rank Title Genre Description Director Actors Year Rati
(Minutes)

A pair of
friends Adam Pally, T.J.
Search Scot
998 999 Adventure,Comedy embark on a Miller, Thomas 2014 93 5
Party Armstrong
mission to Middleditch,Sh...
reuni...

A stuffy Kevin Spacey,

Nine businessman Barry Jennifer Garner,
999 1000 Comedy,Family,Fantasy 2016 87 5
Lives finds himself Sonnenfeld Robbie
trapped ins... Amell,Ch...
In [11]: # select a random row -- most likely to be different every time
[Link](1)

Out[11]:
Runtime
Rank Title Genre Description Director Actors Year Rating Votes
(Minutes)

Vin
Hobbs has Diesel,
Dominic and Paul
Furious Justin
134 135 Action,Crime,Thriller Brian Walker, 2013 130 7.1 318051
6 Lin
reassemble Dwayne
their c... Johnson,
Miche...

In [12]: # print data, select 3 random rows -- set seed (random_state) to get the same random
sample
[Link](3, random_state=111)

Out[12]:
Runtime
Rank Title Genre Description Director Actors Year Rat
(Minutes)

Michael
A lighthouse
Fassbender,
The Light keeper and
Derek Alicia
190 191 Between Drama,Romance his wife 2016 133
Cianfrance Vikander,
Oceans living off
Rachel
th...
We...

Henry
In the early Cavill,
The Man 1960s, CIA Armie
Guy
104 105 from Action,Adventure,Comedy agent Hammer, 2015 116
Ritchie
U.N.C.L.E. Napoleon Alicia
Solo an... Vikander,
E...

Benjamin
Travis and
Walker,
Gabby first
The Teresa
295 296 Drama,Romance meet as Ross Katz 2016 111
Choice Palmer,
neighbors in
Alexandra
a ...
Dadd...
[Link]
([Link]

information

In [13]: # column names

[Link]

Out[13]: Index(['Rank', 'Title', 'Genre', 'Description', 'Director', 'Actors', 'Year',

'Runtime (Minutes)', 'Rating', 'Votes', 'Revenue (Millions)',
'Metascore'],
dtype='object')

In [14]: # use info to get the general info of the dataset

[Link]()

<class '[Link]'>
RangeIndex: 1000 entries, 0 to 999
Data columns (total 12 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Rank 1000 non-null int64
1 Title 1000 non-null object
2 Genre 1000 non-null object
3 Description 1000 non-null object
4 Director 1000 non-null object
5 Actors 1000 non-null object
6 Year 1000 non-null int64
7 Runtime (Minutes) 1000 non-null int64
8 Rating 1000 non-null float64
9 Votes 1000 non-null int64
10 Revenue (Millions) 872 non-null float64
11 Metascore 936 non-null float64
dtypes: float64(3), int64(4), object(5)
memory usage: 93.9+ KB
Note: it contains missing values in two columns -- Revenue (Millions) & Metascore

In [15]: # data shape (rows, collumns)

[Link]

Out[15]: (1000, 12)

use sort_values

In [16]: # sort_values (default: not inplace (inplace=False), ascending (ascending=True)

# by Year from the latest
movie.sort_values(by='Year', inplace=True, ascending=False)
[Link](3)

Out[16]:
Runtime
Rank Title Genre Description Director Actors Year Rating Vo
(Minutes)

Kevin
A stuffy Spacey,
Nine businessman Barry Jennifer
999 1000 Comedy,Family,Fantasy 2016 87 5.3 12
Lives finds himself Sonnenfeld Garner,
trapped ins... Robbie
Amell,Ch...

Sharlto
Set in Copley,
Boston in Brie
Free Ben
161 162 Action,Comedy,Crime 1978, a Larson, 2016 90 7.0 6
Fire Wheatley
meeting in a Armie
deserted... Hammer,
Cil...

Dan
A challenged
Crisafulli,
man is
Tall Jonathan Kay
647 648 Fantasy,Horror,Thriller stalked by 2016 133 3.2
Men Holbrook Whitney,
tall
Richard
phantoms i...
Garcia, P...
 In [17]: # by Rank (from 1 to 1000), change back to the original ordering
movie.sort_values(by='Rank',inplace=True, ascending=True)
[Link](3)

Out[17]:
Runtime
Rank Title Genre Description Director Actors Year Rating
(Minutes)

Chris
A group of
Pratt, Vin
Guardians intergalactic
Action,Adventure,Sci- James Diesel,
0 1 of the criminals 2014 121 8.1 75
Fi Gunn Bradley
Galaxy are forced
Cooper,
...
Zoe S...

Noomi
Following Rapace,
clues to the Logan
Adventure,Mystery,Sci- Ridley
1 2 Prometheus origin of Marshall- 2012 124 7.0 48
Fi Scott
mankind, a Green,
te... Michael
Fa...

James
Three girls
McAvoy,
are
Anya
kidnapped M. Night
2 3 Split Horror,Thriller Taylor- 2016 117 7.3 15
by a man Shyamalan
Joy,
with a
Haley Lu
diag...
Richar...

Part 2: Selection

Use iloc & loc for selection

iloc[row_index, col_index]
loc[row_name, col_name]

Use iloc (by index)

In [18]: # get full description (collumn=3) of the first movie (row=0)

[Link][0,3]

Out[18]: 'A group of intergalactic criminals are forced to work together to stop a fanatical
warrior from taking control of the universe.'

In [19]: # get the title & year of first 3 movies

[Link][0:3,[1,6]]

Out[19]:
Title Year

0 Guardians of the Galaxy 2014

1 Prometheus 2012

2 Split 2016
 In [20]: # get the year & runtime (columns 6 & 7) of first 6 movies, make it DataFrame
[Link]([Link][0:6,6:8])

Out[20]:
Year Runtime (Minutes)

0 2014 121

1 2012 124

2 2016 117

3 2016 108

4 2016 123

5 2016 103

Use loc (by name)

In [21]: # tilte of the first movie

[Link][0,'Title']

Out[21]: 'Guardians of the Galaxy'

In [22]: # get Title, Diector & Year from the first & third movies
[Link][[0,2],['Title','Director','Year']]

Out[22]:
Title Director Year

0 Guardians of the Galaxy James Gunn 2014

2 Split M. Night Shyamalan 2016

In [23]: # get Title to Director cols from the first 3 movies

[Link][0:2,'Title':'Director']

Out[23]:
Title Genre Description Director

Guardians of the Action,Adventure,Sci- A group of intergalactic criminals are forced

0 James Gunn
Galaxy Fi ...

Adventure,Mystery,Sci- Following clues to the origin of mankind, a

1 Prometheus Ridley Scott
Fi te...

Three girls are kidnapped by a man with a M. Night

2 Split Horror,Thriller
diag... Shyamalan

Use condition(s) for selection

In [24]: # get movies directed by Michael Bay
movie[movie['Director'] == "Michael Bay"]

Out[24]:
Runtime
Rank Title Genre Description Director Actors Year Rating
(Minutes)

Mark
Autobots
Wahlberg,
Transformers: must escape
Action,Adventure,Sci- Michael Nicola
126 127 Age of sight from a 2014 165 5.7
Fi Bay Peltz, Jack
Extinction bounty
Reynor,
hunte...
Stan...

John
During an
Krasinski,
attack on a
Michael Pablo
168 169 13 Hours Action,Drama,History U.S. 2016 144 7.3
Bay Schreiber,
compound in
James
Libya, ...
Badge D...

Shia
An ancient
LaBeouf,
struggle
Action,Adventure,Sci- Michael Megan
212 213 Transformers between two 2007 144 7.1
Fi Bay Fox, Josh
Cybertronian
Duhamel,
r...
Tyrese ...

Shia
The
LaBeouf,
Transformers: Autobots
Action,Adventure,Sci- Michael Rosie
566 567 Dark of the learn of a 2011 154 6.3
Fi Bay Huntington-
Moon Cybertronian
Whiteley,
spacecraf...
Tyres...

Mark
A trio of
Wahlberg,
bodybuilders
Michael Dwayne
668 669 Pain & Gain Comedy,Crime,Drama in Florida 2013 129 6.5
Bay Johnson,
get caught
Anthony
u...
Mackie,...

Sam Shia
Witwicky LaBeouf,
Transformers:
Action,Adventure,Sci- leaves the Michael Megan
710 711 Revenge of 2009 150 6.0
Fi Autobots Bay Fox, Josh
the Fallen
behind for a Duhamel,
... Tyrese ...

In [25]: # get number of movies in 2016 with "len" (length)

len(movie[movie['Year'] == 2016])

Out[25]: 297

In [26]: # get number of movies in 2016 with shape[0] (row number)

movie[movie['Year'] == 2016].shape[0]

Out[26]: 297
 In [27]: # extract movies with rating higher than 8.8
movie[movie['Rating'] >= 8.8]

Out[27]:
Runtime
Rank Title Genre Description Director Actors Year Rating
(Minutes)

When the
Christian
menace
The Bale, Heath
known as Christopher
54 55 Dark Action,Crime,Drama Ledger, 2008 152 9.0
the Joker Nolan
Knight Aaron
wreaks
Eckhart,Mi...
havo...

Leonardo
A thief, who
DiCaprio,
steals
Christopher Joseph
80 81 Inception Action,Adventure,Sci-Fi corporate 2010 148 8.8
Nolan Gordon-
secrets
Levitt,
through ...
Ellen...

Former Aamir Khan,

wrestler Sakshi
Mahavir Nitesh Tanwar,
117 118 Dangal Action,Biography,Drama 2016 161 8.8
Singh Tiwari Fatima
Phogat and Sana
his t... Shaikh,...

In [28]: # two conditions

movie[(movie['Revenue (Millions)'] >= 600) & ([Link]>2012)]

Out[28]:
Runtime
Rank Title Genre Description Director Actors Year Rating
(Minutes)

Daisy
Star
Three Ridley,
Wars:
decades John
Episode J.J.
50 51 Action,Adventure,Fantasy after the Boyega, 2015 136 8.1
VII - The Abrams
defeat of the Oscar
Force
Galactic... Isaac,
Awakens
Domhna...

A new Chris Pratt,

theme park, Bryce
Jurassic Colin
85 86 Action,Adventure,Sci-Fi built on the Dallas 2015 124 7.0
World Trevorrow
original site Howard, Ty
o... Simpkins,...

Use [Link] & [Link] for selection

In [29]: # get all movies with Hugh Jackman ([Link])
# contains -- any (of the string)
movie[movie['Actors'].[Link]('Hugh Jackman')]
Out[29]:
Runtim
Rank Title Genre Description Director Actors Year
(Minute

Two stage
Christian Bale,
magicians
The Christopher Hugh Jackman,
64 65 Drama,Mystery,Sci-Fi engage in 2006 13
Prestige Nolan Scarlett
competitive
Johanss...
one-...

When Keller
Dover's Hugh Jackman,
Denis
90 91 Prisoners Crime,Drama,Mystery daughter Jake Gyllenhaal, 2013 15
Villeneuve
and her Viola Davis,Mel...
friend go...

The X-Men
X-Men: Patrick Stewart,
send
Days of Bryan Ian McKellen,
162 163 Action,Adventure,Sci-Fi Wolverine to 2014 13
Future Singer Hugh Jackman,
the past in a
Past J...
desp...

In 19th-
century Hugh Jackman,
Les France, Tom Russell Crowe,
245 246 Drama,Musical,Romance 2012 15
Misérables Jean Hooper Anne
Valjean, Hathaway,Ama...
who for ...

A look at Hugh Jackman,

X-Men
Wolverine's Gavin Liev Schreiber,
268 269 Origins: Action,Adventure,Sci-Fi 2009 10
early life, in Hood Ryan Reynolds,
Wolverine
particula... D...

When
Wolverine is Hugh Jackman,
The James
388 389 Action,Adventure,Sci-Fi summoned Will Yun Lee, Tao 2013 12
Wolverine Mangold
to Japan by Okamoto, Rila ...
an old ...

The story of
Eddie Taron Egerton,
Eddie the Edwards, Dexter Hugh Jackman,
475 476 Biography,Comedy,Drama 2016 10
Eagle the Fletcher Tom Costello, Jo
notoriously ...
te...

In the near
Sharlto Copley,
future, crime Neill
517 518 Chappie Action,Crime,Drama Dev Patel, Hugh 2015 12
is patrolled Blomkamp
Jackman,Sigour...
by a me...

12-year-old
orphan Levi Miller, Hugh
565 566 Pan Adventure,Family,Fantasy Peter is Joe Wright Jackman, Garrett 2015 11
spirited Hedlund, Ro...
away to t...

When a
cure is
X-Men: Patrick Stewart,
found to Brett
626 627 The Last Action,Adventure,Fantasy Hugh Jackman, 2006 10
treat Ratner
Stand Halle Berry, Fa...
mutations,
lines...

Set in
northern Nicole Kidman,
Australia Baz Hugh Jackman,
703 704 Australia Adventure,Drama,Romance 2008 16
before Luhrmann Shea Adams,
World War Eddie...
II,...

Into the
Elijah Wood,
world of the
Happy George Brittany Murphy,
715 716 Animation,Comedy,Family Emperor 2006 10
Feet Miller Hugh Jackman,
Penguins,
Ro...
who fi...
 Runtim
Rank Title Genre Description Director Actors Year
(Minute

As a
Hugh Jackman,
modern-day
The Darren Rachel Weisz,
774 775 Drama,Sci-Fi scientist, 2006 9
Fountain Aronofsky Sean Patrick
Tommy is
Thoma...
struggling...

In the near Hugh Jackman,

future, robot Shawn Evangeline Lilly,
892 893 Real Steel Action,Drama,Family 2011 12
boxing is a Levy Dakota
top spor... Goyo,An...

In [30]: # get all X-Men movies

movie[movie['Title'].[Link]('X-Men')]

Out[30]:
Runtime
Rank Title Genre Description Director Actors Year Ratin
(Minutes)

James
After the re-
McAvoy,
emergence
X-Men: Bryan Michael
32 33 Action,Adventure,Sci-Fi of the 2016 144 7.
Apocalypse Singer Fassbender,
world's first
Jennifer
mu...
Law...

Patrick
The X-Men
Stewart, Ian
X-Men: send
Bryan McKellen,
162 163 Days of Action,Adventure,Sci-Fi Wolverine to 2014 132 8.
Singer Hugh
Future Past the past in a
Jackman,
desp...
J...

Hugh
Jackman,
A look at
X-Men Liev
Wolverine's Gavin
268 269 Origins: Action,Adventure,Sci-Fi Schreiber, 2009 107 6.
early life, in Hood
Wolverine Ryan
particula...
Reynolds,
D...

When a Patrick
cure is Stewart,
X-Men: The found to Brett Hugh
626 627 Action,Adventure,Fantasy 2006 104 6.
Last Stand treat Ratner Jackman,
mutations, Halle Berry,
lines... Fa...

In [31]: # How many X-Men movies?

movie[movie['Title'].[Link]('X-Men')].shape[0]

Out[31]: 4

In [32]: # How many X-Men movies?

len(movie[movie['Title'].[Link]('X-Men')])

Out[32]: 4
 In [33]: # get all movies that Hugh Jackman is the lead actor ([Link])
# match -- "beginning" (of the string)
movie[movie['Actors'].[Link]('Hugh Jackman')]

Out[33]:
Runtime
Rank Title Genre Description Director Actors Year R
(Minutes)

When Keller
Hugh Jackman,
Dover's
Denis Jake Gyllenhaal,
90 91 Prisoners Crime,Drama,Mystery daughter 2013 153
Villeneuve Viola
and her
Davis,Mel...
friend go...

In 19th-
century Hugh Jackman,
Les France, Tom Russell Crowe,
245 246 Drama,Musical,Romance 2012 158
Misérables Jean Hooper Anne
Valjean, Hathaway,Ama...
who for ...

A look at Hugh Jackman,

X-Men
Wolverine's Gavin Liev Schreiber,
268 269 Origins: Action,Adventure,Sci-Fi 2009 107
early life, in Hood Ryan Reynolds,
Wolverine
particula... D...

When
Hugh Jackman,
Wolverine is
The James Will Yun Lee,
388 389 Action,Adventure,Sci-Fi summoned 2013 126
Wolverine Mangold Tao Okamoto,
to Japan by
Rila ...
an old ...

As a
Hugh Jackman,
modern-day
The Darren Rachel Weisz,
774 775 Drama,Sci-Fi scientist, 2006 96
Fountain Aronofsky Sean Patrick
Tommy is
Thoma...
struggling...

In the near Hugh Jackman,

future, robot Shawn Evangeline Lilly,
892 893 Real Steel Action,Drama,Family 2011 127
boxing is a Levy Dakota
top spor... Goyo,An...

Part 3: Rename index

Use "rename" to change column names

In [34]: # list columns

[Link]

Out[34]: Index(['Rank', 'Title', 'Genre', 'Description', 'Director', 'Actors', 'Year',

'Runtime (Minutes)', 'Rating', 'Votes', 'Revenue (Millions)',
'Metascore'],
dtype='object')

In [35]: [Link](columns = {'Runtime (Minutes)':'Duration_min', 'Revenue (Millions)':'Rev

enue_mil'})
[Link]

Out[35]: Index(['Rank', 'Title', 'Genre', 'Description', 'Director', 'Actors', 'Year',

'Runtime (Minutes)', 'Rating', 'Votes', 'Revenue (Millions)',
'Metascore'],
dtype='object')
 In [36]: # Use inplace=True to make change (defaule: inplace=False)
[Link](columns = {'Runtime (Minutes)':'Duration_min', 'Revenue (Millions)':'Rev
enue_mil'}, inplace=True)
[Link]

Out[36]: Index(['Rank', 'Title', 'Genre', 'Description', 'Director', 'Actors', 'Year',

'Duration_min', 'Rating', 'Votes', 'Revenue_mil', 'Metascore'],
dtype='object')

Use set_index & reset_index to set & reset index

In [37]: movie1=movie.set_index('Title')
[Link](2)

Out[37]:
Rank Genre Description Director Actors Year Duration_min Rating V

Title

Chris
A group of
Pratt, Vin
Guardians intergalactic
Action,Adventure,Sci- James Diesel,
of the 1 criminals 2014 121 8.1 75
Fi Gunn Bradley
Galaxy are forced
Cooper,
...
Zoe S...

Noomi
Following Rapace,
clues to the Logan
Adventure,Mystery,Sci- Ridley
Prometheus 2 origin of Marshall- 2012 124 7.0 48
Fi Scott
mankind, a Green,
te... Michael
Fa...

In [38]: # Use reset

movie1=movie1.reset_index()
[Link](2)

Out[38]:
Title Rank Genre Description Director Actors Year Duration_min Rating

Chris
A group of
Pratt, Vin
Guardians intergalactic
Action,Adventure,Sci- James Diesel,
0 of the 1 criminals 2014 121 8.1
Fi Gunn Bradley
Galaxy are forced
Cooper,
...
Zoe S...

Noomi
Following Rapace,
clues to the Logan
Adventure,Mystery,Sci- Ridley
1 Prometheus 2 origin of Marshall- 2012 124 7.0
Fi Scott
mankind, a Green,
te... Michael
Fa...

Part 4: Further info extraction

Use value_counts
Return counts of unique values

In [39]: # get director (with value_count)

movie['Director'].value_counts().head(5)

Out[39]: Ridley Scott 8

David Yates 6
Michael Bay 6
Paul W.S. Anderson 6
M. Night Shyamalan 6
Name: Director, dtype: int64

There are 8 movies directed by Ridley Scott -- list those after 2013???

In [40]: movie[([Link]=='Ridley Scott') & ([Link]>=2013)]

Out[40]:
Rank Title Genre Description Director Actors Year Duration_min Rat

An Matt
astronaut Damon,
The becomes Ridley Jessica
102 103 Adventure,Drama,Sci-Fi 2015 144
Martian stranded on Scott Chastain,
Mars after Kristen
hi... Wiig, Ka...

Christian
The defiant
Bale, Joel
Exodus: leader
Ridley Edgerton,
516 517 Gods and Action,Adventure,Drama Moses rises 2014 150
Scott Ben
Kings up against
Kingsley,
the ...
S...

Michael
A lawyer
Fassbender,
finds himself
The Ridley Penélope
521 522 Crime,Drama,Thriller in over his 2013 117
Counselor Scott Cruz,
head when
Cameron
h...
Dia...

In [41]: # get number of movies by year (with value_count)

movie['Year'].value_counts().head(10)

Out[41]: 2016 297

2015 127
2014 98
2013 91
2012 64
2011 63
2010 60
2007 53
2008 52
2009 51
Name: Year, dtype: int64

Part 5: Generate new variables & sort data

new column variable

In [42]: # revenue per min

movie['rev_pmin']=movie.Revenue_mil/movie.Duration_min
[Link](2)

Out[42]:
Rank Title Genre Description Director Actors Year Duration_min Rating

Chris
A group of
Pratt, Vin
Guardians intergalactic
Action,Adventure,Sci- James Diesel,
0 1 of the criminals 2014 121 8.1
Fi Gunn Bradley
Galaxy are forced
Cooper,
...
Zoe S...

Noomi
Following Rapace,
clues to the Logan
Adventure,Mystery,Sci- Ridley
1 2 Prometheus origin of Marshall- 2012 124 7.0
Fi Scott
mankind, a Green,
te... Michael
Fa...

In [43]: # split column by delimiter into multiple columns

movie['Genre'].[Link](',', expand=True).head(5)

Out[43]:
0 1 2

0 Action Adventure Sci-Fi

1 Adventure Mystery Sci-Fi

2 Horror Thriller None

3 Animation Comedy Family

4 Action Adventure Fantasy

In [44]: # add three separate genres to the dataframe

movie[['Genre_1', 'Genre_2', 'Genre_3']] = movie['Genre'].[Link](',', expand=True)
[Link](2)

Out[44]:
Rank Title Genre Description Director Actors Year Duration_min Rating

Chris
A group of
Pratt, Vin
Guardians intergalactic
Action,Adventure,Sci- James Diesel,
0 1 of the criminals 2014 121 8.1
Fi Gunn Bradley
Galaxy are forced
Cooper,
...
Zoe S...

Noomi
Following Rapace,
clues to the Logan
Adventure,Mystery,Sci- Ridley
1 2 Prometheus origin of Marshall- 2012 124 7.0
Fi Scott
mankind, a Green,
te... Michael
Fa...
 In [45]: # get number of movies by Genre_1
movie['Genre_1'].value_counts()

Out[45]: Action 293

Drama 195
Comedy 175
Adventure 75
Crime 71
Biography 64
Animation 49
Horror 46
Mystery 13
Thriller 10
Fantasy 4
Sci-Fi 3
Romance 2
Name: Genre_1, dtype: int64

In [46]: # get number of movies by Genre_1 for 2015 & 2016

movie[[Link]>=2015][['Year','Genre_1']].value_counts(sort=False)

Out[46]: Year Genre_1

2015 Action 34
Adventure 10
Animation 4
Biography 10
Comedy 23
Crime 9
Drama 33
Horror 2
Mystery 2
2016 Action 73
Adventure 17
Animation 18
Biography 25
Comedy 49
Crime 22
Drama 58
Fantasy 1
Horror 21
Mystery 2
Romance 1
Sci-Fi 2
Thriller 8
dtype: int64

Use sort_values
In [47]: # sort by a single column year decendingly
movie.sort_values(by='Year', ascending=False).head(3)

Out[47]:
Rank Title Genre Description Director Actors Year Duration_min Rating

Kevin
A stuffy Spacey,
Nine businessman Barry Jennifer
999 1000 Comedy,Family,Fantasy 2016 87 5.3
Lives finds himself Sonnenfeld Garner,
trapped ins... Robbie
Amell,Ch...

Sharlto
Set in Copley,
Boston in Brie
Free Ben
161 162 Action,Comedy,Crime 1978, a Larson, 2016 90 7.0
Fire Wheatley
meeting in a Armie
deserted... Hammer,
Cil...

Dan
A challenged
Crisafulli,
man is
Tall Jonathan Kay
647 648 Fantasy,Horror,Thriller stalked by 2016 133 3.2
Men Holbrook Whitney,
tall
Richard
phantoms i...
Garcia, P...

In [48]: # sort by two columns acendingly

movie.sort_values(by=['Rating','Year'], ascending=True).head(3)

Out[48]:
Rank Title Genre Description Director Actors Year Duration_min

Carmen
Over the
Electra,
Disaster course of one Jason
829 830 Comedy Vanessa 2008 87
Movie evening, an Friedberg
Lachey,Nicole
unsuspectin...
Parker, ...

Justin
The young Chatwin,
Dragonball warrior Son James James
871 872 Action,Adventure,Fantasy 2009 85
Evolution Goku sets out Wong Marsters,
on a quest... Yun-Fat
Chow, ...

A group of Brittany
Don't Fuck friends are Blanton, Ayse
Shawn
42 43 in the Horror going on a Howard, 2016 73
Burkett
Woods camping Roman
trip... Jossart,N...

In [ ]:
Example 2: Olympic Medals

In [ ]: # bypass (disable) SSL verification in Python (if you are using a uni computer)
# execute this cell only if necessary

import ssl
try:
_create_unverified_https_context = ssl._create_unverified_context
except AttributeError:
# Legacy Python that doesn't verify HTTPS certificates by default
pass
else:
# Handle target environment that doesn't support HTTPS verification
ssl._create_default_https_context = _create_unverified_https_context

Part 1: Import & export data

read (import) data from ESPN website (table)
[Link] ([Link]
[Link] ([Link]

In [49]: # Summer-2016 & Summer-2020

# load data from the web table, [0]: the first list
os_2016 = pd.read_html('[Link] match='Tota
l Medals By Country')[0]
os_2020 = pd.read_html('[Link] match='Tota
l Medals By Country')[0]

In [50]: # display the first 5 observations (2016)

os_2016.head()

Out[50]:
Group G S B Total

0 USA 46 37 38 121

1 CHN 26 18 26 70

2 GBR 27 23 17 67

3 RUS 19 18 19 56

4 GER 17 10 15 42

In [51]: # display the first 5 observations (2020)

os_2020.head()

Out[51]:
Group G S B Total

0 USA 39 41 33 113

1 CHN 38 32 18 88

2 ROC 20 28 23 71

3 GBR 22 21 22 65

4 JPN 27 14 17 58

Note: Russia is called RUS in 2016 & ROC in 2020!!!

write (export) data to local drive

In [53]: # write (save) the dataframe (os) to where you want

os_2016.to_excel('os_2016.xlsx')
os_2020.to_excel('os_2020.xlsx')

In [54]: # Use ExcelWriter to write muliple sheets in one excel file (without index)
writer = [Link]('[Link]')
os_2016.to_excel(writer,'2016', index=False)
os_2020.to_excel(writer,'2020', index=False)
[Link]()

In [55]: os_2016 = pd.read_excel("[Link]", sheet_name='2016')

os_2020 = pd.read_excel("[Link]", sheet_name='2020')
 In [56]: # get dataframe info
os_2016.info()
<class '[Link]'>
RangeIndex: 87 entries, 0 to 86
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Group 87 non-null object
1 G 87 non-null int64
2 S 87 non-null int64
3 B 87 non-null int64
4 Total 87 non-null int64
dtypes: int64(4), object(1)
memory usage: 3.5+ KB

In [57]: # get dataframe info

os_2020.info()

<class '[Link]'>
RangeIndex: 93 entries, 0 to 92
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Group 93 non-null object
1 G 93 non-null int64
2 S 93 non-null int64
3 B 93 non-null int64
4 Total 93 non-null int64
dtypes: int64(4), object(1)
memory usage: 3.8+ KB

Use mask & where for replacing values

mask: replace values where the condition is True

In [58]: # change ROC to Russia in 2020 with mask

# inplace=True --> to make real changes
os_2020['Group'].mask(os_2020['Group'] == 'ROC', 'RUS', inplace=True)
os_2020.head(4)

Out[58]:
Group G S B Total

0 USA 39 41 33 113

1 CHN 38 32 18 88

2 RUS 20 28 23 71

3 GBR 22 21 22 65

Part 2: Combine DataFrames

Use [Link] & [Link] for combining dataframes

[Link]
 In [59]: # stack (concat) top 3 os_2016 & os_2020, vertically (axis=0, rows)
[Link]([os_2016,os_2020], axis=0)

Out[59]:
Group G S B Total

0 USA 46 37 38 121

1 CHN 26 18 26 70

2 GBR 27 23 17 67

3 RUS 19 18 19 56

4 GER 17 10 15 42

... ... ... ... ... ...

88 CIV 0 0 1 1

89 BUR 0 0 1 1

90 BOT 0 0 1 1

91 MDA 0 0 1 1

92 KUW 0 0 1 1

180 rows × 5 columns

In [60]: #stack (concat) top 3 os_2016 & os_2020, horizontally (axis=1, columns)
[Link]([os_2016,os_2020], axis=1)

Out[60]:
Group G S B Total Group G S B Total

0 USA 46.0 37.0 38.0 121.0 USA 39 41 33 113

1 CHN 26.0 18.0 26.0 70.0 CHN 38 32 18 88

2 GBR 27.0 23.0 17.0 67.0 RUS 20 28 23 71

3 RUS 19.0 18.0 19.0 56.0 GBR 22 21 22 65

4 GER 17.0 10.0 15.0 42.0 JPN 27 14 17 58

... ... ... ... ... ... ... ... ... ... ...

88 NaN NaN NaN NaN NaN CIV 0 0 1 1

89 NaN NaN NaN NaN NaN BUR 0 0 1 1

90 NaN NaN NaN NaN NaN BOT 0 0 1 1

91 NaN NaN NaN NaN NaN MDA 0 0 1 1

92 NaN NaN NaN NaN NaN KUW 0 0 1 1

93 rows × 10 columns

lots of NaN... (in fact, they are 0 -- no medal)

[Link]
In [61]: # inner: use intersection of keys from both frames
# on='Group' (based on the Group column, country names)
[Link](os_2016,os_2020,on='Group', how='inner')

Out[61]:
Group G_x S_x B_x Total_x G_y S_y B_y Total_y

0 USA 46 37 38 121 39 41 33 113

1 CHN 26 18 26 70 38 32 18 88

2 GBR 27 23 17 67 22 21 22 65

3 RUS 19 18 19 56 20 28 23 71

4 GER 17 10 15 42 10 11 16 37

... ... ... ... ... ... ... ... ... ...

72 DOM 0 0 1 1 0 3 2 5

73 MDA 0 0 1 1 0 0 1 1

74 FIN 0 0 1 1 0 0 2 2

75 EST 0 0 1 1 1 0 1 2

76 AUT 0 0 1 1 1 1 5 7

77 rows × 9 columns

In [62]: # outer: use all keys from both frames

[Link](os_2016,os_2020, on='Group', how='outer')

Out[62]:
Group G_x S_x B_x Total_x G_y S_y B_y Total_y

0 USA 46.0 37.0 38.0 121.0 39.0 41.0 33.0 113.0

1 CHN 26.0 18.0 26.0 70.0 38.0 32.0 18.0 88.0

2 GBR 27.0 23.0 17.0 67.0 22.0 21.0 22.0 65.0

3 RUS 19.0 18.0 19.0 56.0 20.0 28.0 23.0 71.0

4 GER 17.0 10.0 15.0 42.0 10.0 11.0 16.0 37.0

... ... ... ... ... ... ... ... ... ...

98 SYR NaN NaN NaN NaN 0.0 0.0 1.0 1.0

99 GHA NaN NaN NaN NaN 0.0 0.0 1.0 1.0

100 BUR NaN NaN NaN NaN 0.0 0.0 1.0 1.0

101 BOT NaN NaN NaN NaN 0.0 0.0 1.0 1.0

102 KUW NaN NaN NaN NaN 0.0 0.0 1.0 1.0

103 rows × 9 columns

inner: 77 countries; outer: 103: countries

In [63]: # assign the outer merge to a new dataframe: os

os=[Link](os_2016,os_2020,on='Group', how='outer')
Tasks for tidying up the os DataFrame
rename (columns): x--> 2016, y=2020
set NaN (missing) to zero
create a new column: total
sort by total

In [64]: # Use "replace" to replace columns _x by _2016 and _y by _2020 with a for loop
[Link] = [[Link]('_x', '_2016') for col in [Link]]
[Link] = [[Link]('_y', '_2020') for col in [Link]]
os

Out[64]:
Group G_2016 S_2016 B_2016 Total_2016 G_2020 S_2020 B_2020 Total_2020

0 USA 46.0 37.0 38.0 121.0 39.0 41.0 33.0 113.0

1 CHN 26.0 18.0 26.0 70.0 38.0 32.0 18.0 88.0

2 GBR 27.0 23.0 17.0 67.0 22.0 21.0 22.0 65.0

3 RUS 19.0 18.0 19.0 56.0 20.0 28.0 23.0 71.0

4 GER 17.0 10.0 15.0 42.0 10.0 11.0 16.0 37.0

... ... ... ... ... ... ... ... ... ...

98 SYR NaN NaN NaN NaN 0.0 0.0 1.0 1.0

99 GHA NaN NaN NaN NaN 0.0 0.0 1.0 1.0

100 BUR NaN NaN NaN NaN 0.0 0.0 1.0 1.0

101 BOT NaN NaN NaN NaN 0.0 0.0 1.0 1.0

102 KUW NaN NaN NaN NaN 0.0 0.0 1.0 1.0

103 rows × 9 columns

In [65]: # fill NaN with 0

os=[Link](0)
os

Out[65]:
Group G_2016 S_2016 B_2016 Total_2016 G_2020 S_2020 B_2020 Total_2020

0 USA 46.0 37.0 38.0 121.0 39.0 41.0 33.0 113.0

1 CHN 26.0 18.0 26.0 70.0 38.0 32.0 18.0 88.0

2 GBR 27.0 23.0 17.0 67.0 22.0 21.0 22.0 65.0

3 RUS 19.0 18.0 19.0 56.0 20.0 28.0 23.0 71.0

4 GER 17.0 10.0 15.0 42.0 10.0 11.0 16.0 37.0

... ... ... ... ... ... ... ... ... ...

98 SYR 0.0 0.0 0.0 0.0 0.0 0.0 1.0 1.0

99 GHA 0.0 0.0 0.0 0.0 0.0 0.0 1.0 1.0

100 BUR 0.0 0.0 0.0 0.0 0.0 0.0 1.0 1.0

101 BOT 0.0 0.0 0.0 0.0 0.0 0.0 1.0 1.0

102 KUW 0.0 0.0 0.0 0.0 0.0 0.0 1.0 1.0

103 rows × 9 columns

In [66]: # unique (Group/country) -- check
[Link]()

Out[66]: array(['USA', 'CHN', 'GBR', 'RUS', 'GER', 'FRA', 'JPN', 'AUS', 'ITA',
'CAN', 'KOR', 'NED', 'BRA', 'NZL', 'AZE', 'ESP', 'KAZ', 'HUN',
'DEN', 'KEN', 'UZB', 'JAM', 'CUB', 'SWE', 'UKR', 'POL', 'CRO',
'RSA', 'CZE', 'BLR', 'COL', 'IRI', 'SRB', 'TUR', 'ETH', 'SUI',
'PRK', 'GEO', 'GRE', 'THA', 'BEL', 'ROU', 'MAS', 'MEX', 'ARG',
'SVK', 'ARM', 'SLO', 'LTU', 'NOR', 'INA', 'TPE', 'VEN', 'BUL',
'TUN', 'EGY', 'BRN', 'VIE', 'IOC', 'CIV', 'BAH', 'ALG', 'IRL',
'MGL', 'IND', 'ISR', 'KOS', 'SIN', 'JOR', 'TJK', 'PUR', 'FIJ',
'QAT', 'PHI', 'NIG', 'GRN', 'BDI', 'UAE', 'POR', 'NGR', 'MAR',
'DOM', 'TRI', 'MDA', 'FIN', 'EST', 'AUT', 'HKG', 'UGA', 'ECU',
'KGZ', 'SMR', 'LAT', 'BER', 'TKM', 'NAM', 'KSA', 'MKD', 'SYR',
'GHA', 'BUR', 'BOT', 'KUW'], dtype=object)

In [67]: # number (len)

len([Link]())

Out[67]: 103

In [68]: # generate a new row: Total (sum of total of 2016 & 2020)
# sort by Total decendingly
os['Total']=os.Total_2016+os.Total_2020
os.sort_values(by=['Total'],inplace=True, ascending=False)
[Link](5)

Out[68]:
Group G_2016 S_2016 B_2016 Total_2016 G_2020 S_2020 B_2020 Total_2020 Total

0 USA 46.0 37.0 38.0 121.0 39.0 41.0 33.0 113.0 234.0

1 CHN 26.0 18.0 26.0 70.0 38.0 32.0 18.0 88.0 158.0

2 GBR 27.0 23.0 17.0 67.0 22.0 21.0 22.0 65.0 132.0

3 RUS 19.0 18.0 19.0 56.0 20.0 28.0 23.0 71.0 127.0

6 JPN 12.0 8.0 21.0 41.0 27.0 14.0 17.0 58.0 99.0

In [69]: # get Aussie

os[[Link]=='AUS']

Out[69]:
Group G_2016 S_2016 B_2016 Total_2016 G_2020 S_2020 B_2020 Total_2020 Total

7 AUS 8.0 11.0 10.0 29.0 17.0 7.0 22.0 46.0 75.0

In [70]: # Top 3 countries with best "improvement" of winning gold medals (from 2016 to 2020)
os['G_diff']=os.G_2020-os.G_2016
os.sort_values(by=['G_diff'], ascending=False).head(3)

Out[70]:
Group G_2016 S_2016 B_2016 Total_2016 G_2020 S_2020 B_2020 Total_2020 Total G_diff

6 JPN 12.0 8.0 21.0 41.0 27.0 14.0 17.0 58.0 99.0 15.0

1 CHN 26.0 18.0 26.0 70.0 38.0 32.0 18.0 88.0 158.0 12.0

7 AUS 8.0 11.0 10.0 29.0 17.0 7.0 22.0 46.0 75.0 9.0
 In [71]: # Top 3 countries with best improvement of winning total medals (from 2016 to 2020)
os['Total_diff']=os.Total_2020-os.Total_2016
os.sort_values(by=['Total_diff'], ascending=False).head(3)

Out[71]:
Group G_2016 S_2016 B_2016 Total_2016 G_2020 S_2020 B_2020 Total_2020 Total G_diff Tot

1 CHN 26.0 18.0 26.0 70.0 38.0 32.0 18.0 88.0 158.0 12.0

6 JPN 12.0 8.0 21.0 41.0 27.0 14.0 17.0 58.0 99.0 15.0

7 AUS 8.0 11.0 10.0 29.0 17.0 7.0 22.0 46.0 75.0 9.0

In [ ]:

End of Week 2

Appendix

Very useful shortcuts: Ctrl + Z , Esc + Z , Shift + Tab

Ctrl + Z: recover deleted stuff in a cell
Esc + Z: recover deleted cell
Shift + Tab: show documentation of a command

In [ ]: