Lesson 1: Analyzing Data in Python

Data analysis
According to International Data Corporation, an IT industry analyst, the total amount of data
created, captured, copied, and consumed in the world in 2018 was 33 zettabytes (ZB), the
equivalent of 33 trillion gigabytes. The amount of data grew to 59ZB in 2020 and is predicted to
reach a mind-boggling 175ZB by 2025. This growth has created both an opportunity and a
challenge. The opportunity is to convert this data into information that can be used to inform
decision-making. The challenge is finding the data relevant to solving a particular problem and
transforming it into a format that supports the decision-making process. Roughly speaking, the
process for meeting this challenge is:

1. Set your goals. Determine what type of questions you are trying to answer or what
problems you are trying to solve. If you are doing this work for a customer, often this
includes understanding the customer's business needs and how the information will be
used to implement change in the business.
2. Define your target audience. If the user doesn't understand the information you're
presenting, then they won't be able to act on that information in an effective way. For
example, is the end user of the information an IT person, someone in Marketing,
someone who works on a production line, or some other role in a company? Each of
these roles will require different types of information to be presented in different ways to
be as effective as possible.
3. Get the data. A vast amount of raw data is available on the internet and in private
spreadsheets, databases, and other systems that are owned by companies. Your job as
a data analyst is to first locate and identify the data that will support solving the problem,
and then get the data into a form that can be loaded into analysis tools. Often, this
requires finding multiple sources containing parts of the data you need and finding ways
to stitch this data together.
4. Clean the data. Often, the available data is in a raw format and is not exactly what you
need to solve the problem. Therefore, the data must be cleaned before processing can
continue. Some examples of cleaning the data include:
○ Removing unnecessary rows and columns.
○ Sanitizing the data to remove sensitive information that isn't needed for analysis
(like customer or employee identifying information, salaries, etc.). The purpose is
to prevent negative consequences if the data is "leaked."
○ Handling invalid or missing values.
○ Changing data types to more appropriate types.
○ Canonicalizing values by dealing with inconsistencies in the way data is
represented. For example, the same Social Security Number might be
represented as 123-45-6789 in one data source and 123456789 in another data
source. Or, a person might be named John Smith, J. Smith, John Robert Smith,
J. R. Smith, Doctor John Smith, or John Smith, Jr. in different data sources.
Detecting that all of these different strings should (or shouldn't) map to the same
person's name is a difficult problem but will improve the quality of the resulting
information.
5. Prepare the data. Once the data has been cleaned, we can begin massaging it into a
format that supports analysis. Some steps in data preparation include:
○ Adding columns that are derived from other columns.
○ Shaping the data into the forms needed for analysis (such as the format required
for plotting the data).
○ Reviewing the content of the data and the distribution of values within the data.
This often includes creating preliminary visualizations of the data to better
understand it.
○ Checking the data for bias (for example, making sure that all demographic
groups are equally represented in the sample).
6. Analyze the data. In this step, the actual data exploration occurs. This might involve
grouping or aggregating the data in different ways, looking for relationships between
different attributes, and creating simple visualizations to look for relevant factors. In
some cases, the analysis will also include "data science" tasks, such as building models
for classification or prediction using Machine Learning or other statistical modeling
techniques.
7. Visualize the data. Finally, the relevant factors determined during analysis are prepared
for presentation to the end-user. Often, this will include enhancing the simple
visualizations prepared in the previous step and tailoring the visualizations to meet the
target audience’s needs.
 Lesson 2: Tools for Data Analysis
Tools for data analysis
Three popular Python packages that are often used for data analysis and visualization are:

● Numpy - a Python library that provides a multidimensional array object and an

assortment of routines for fast operations on arrays. Numpy has a relatively low-level
interface, so you’ll usually work with data using a package called Pandas, which is built
on top of Numpy.
● Pandas is an open source library that provides high-performance, easy-to-use data
structures and data analysis tools for the Python programming language.
● Matplotlib - a comprehensive library for creating static, animated, and interactive
visualizations in Python.

These packages can be installed globally using pip, or into a PyCharm project's Virtual
Environment using the PyCharm package installer. To install globally, use the following pip
command:

pip install numpy pandas matplotlib

This video from Module 7 shows how to install pyodbc. The process is the same for Numpy,
Pandas, and Matplotlib.

Video Script: Transcript

0:00
after you've installed the visual see
0:03
build tools and ms odbc
0:06
and rebooted your machine it's time to
0:08
install
0:10
pi odbc and the way you install
0:13
piodbc depends on how you've set up your
0:16
python project
0:18
so let me go ahead and start up pycharm
0:36
and i'm going to go ahead and create a
0:37
new project
0:40
and on the first page you enter the name
0:43
of your project
0:44
and so i'll do lab 6 because that's the
0:47
database lab
0:49
and then under python interpreter you
0:51
basically have two options so
0:54
by default you probably have new
0:57
environment using
0:58
virtual and set and what this does
1:01
is inside of your project folder
1:05
it creates a subfolder called vnd
1:08
and that's where all of your packages
1:11
get
1:12
installed so basically it creates a
1:15
self-contained
1:17
virtual python environment that has all
1:20
of the
1:20
packages and all the executables that
1:23
you need to run your program
1:26
and this is separate from the global
1:28
build environment that you got when you
1:30
installed python
1:32
so you can specify a base interpreter
1:34
and here's the
1:35
default path so it's in my users app
1:38
data
1:39
local programs python and then i have
1:42
python 3.9 some odd installed so it's
1:45
python39 and then [Link]
1:50
and then i have some other python
1:52
versions installed
1:53
in a couple of different places and i
1:56
could select
1:56
a different python and then that would
1:59
be set up in my virtual environment
2:02
so if you have a virtual environment set
2:05
up then you're going to be
2:06
installing pi odbc into
2:09
this vn folder using a tool that's built
2:13
into
2:14
pycharm your other option
2:17
is previously configured interpreter and
2:20
then this will be
2:21
a copy of python that you have installed
2:24
globally on your system
2:26
and it's basically the python that you
2:28
get when you run python from the command
2:30
line
2:31
so if you select this option
2:35
there will be no vm folder in your
2:38
python folder it will just
2:39
use whichever interpreter you have
2:42
configured here
2:43
and then you have to install piodbc
2:47
into the library that's associated with
2:51
this particular python interpreter
2:54
so i'm going to do both of those things
2:56
to show you both processes so
2:59
first i'm going to use the global
3:01
version of python and i'm going to say
3:03
create
3:07
and then in my main
3:11
i'm actually going to copy and paste the
3:13
sample code that i have in the course
3:14
shells
3:15
so let me go to
3:21
my core shell and then under the content
3:25
tab i'm going to scroll down to
3:28
module 7 database development
3:31
and uh you should see a page for
3:36
database use in python
3:40
and the page is probably different than
3:42
the page you're seeing is a little
3:44
different from
3:45
this version but somewhere down
3:52
under sql commands you'll see some
3:54
sample code
3:56
so i'm going to copy that sample code
4:01
and i'm going to select all and paste
4:04
to replace the code in here
4:08
and then the pi odbc that this is using
4:11
since there's no
4:12
virtual end is the one that i have
4:15
associated with my python
4:18
and so if i open a command shell
4:23
like so and i say python
4:27
dash dash version it shows me that i'm
4:30
running python
4:32
version 3.9.5
4:35
and if i do pip dash dash version
4:41
it tells me that i'm running pip 21.1.3
4:46
from and then it gives me the full path
4:48
so this is
4:49
inside of the python39 folder
4:52
that i'm using for this project
4:56
and there's a lib folder under there
4:57
site packages and then pip
5:00
which is the python package installer
5:03
so what i want to do is use pip to
5:07
install
5:08
pi odbc into this
5:11
installation of python and i just say
5:14
pip install pi odbc
5:21
and i've already installed previously
5:24
and so it just says requirement already
5:26
satisfied
5:27
but as long as you have the visual c
5:30
build tools
5:32
this should work just fine and it will
5:34
tell you that it's installed
5:36
now i'm getting a warning that the pip
5:38
version i'm using is a little out of
5:40
date it's up to
5:42
21.2.2 and if you get a similar message
5:45
then it's safe to say python-m
5:49
pip install dash dash
5:52
upgrade pip
6:03
and now my pip has been updated and if i
6:05
repeat the pi
6:06
install pi odbc it's still satisfied so
6:11
that works the same but i don't get any
6:13
errors so now that
6:16
pi odbc is installed as long as msodbc
6:20
has also been installed and the machine
6:21
has been rebooted
6:23
i should be able to just run this so i'm
6:25
going to run
6:28
and in fact this is the expected
6:30
behavior
6:31
so it's fetched 50 rows of data about
6:36
television shows and movies and what
6:38
have you from
6:39
the imdb database that's running
6:43
on [Link]
6:48
so this is the database server sql
6:50
server database that we
6:52
use for cis275 and these are the login
6:56
credentials for
6:58
cis275 and so we're just connecting to
7:01
the database server
7:03
and we're executing this sql command
7:07
on the imdb database
7:10
so that works fine um and then
7:14
if i create the project the other way
7:19
so i'm going to make a new project and
7:21
i'll call this
7:23
lab6b
7:29
and then this time i'm going to go ahead
7:31
and create a new environment using
7:32
virtual end
7:33
the location will be inside my lab6b
7:36
folder
7:37
the same base interpreter as i was using
7:40
before so python39
7:43
and then i'm going to go ahead and
7:44
create that using the defaults
7:55
so this is creating the virtual
7:57
environment that will be in the vm
8:10
folder
8:17
and here it is um so if you look inside
8:20
of here there's a bunch of stuff
8:22
under lib there's a
8:26
pip the python package installer folder
8:30
and various library folders under
8:32
scripts
8:33
there's some executables including the
8:35
pip executable
8:37
python itself and so on
8:40
usually you don't need to go in there so
8:43
now we want to
8:44
install piodbc so i'm going to go ahead
8:47
and
8:48
replace the code with the same piece of
8:51
code and if i run this now
8:54
i get an error that
8:58
no module is named piodbc and that's
9:01
because it's not
9:02
installed in the virtual environment
9:04
it's installed globally from my system
9:06
but not in the virtual environment for
9:08
this project
9:09
so to install it i want to go to under
9:12
file there's a settings menu
9:18
and should be under build execution and
9:22
deployment
9:23
actually there's a there's a tab for
9:25
interpreter
9:27
and i'm just going to go ahead and
9:28
search for it
9:31
so interpreter is under actually the
9:35
project folder
9:36
python interpreter and
9:39
it's showing me where the interpreter is
9:42
it's inside of the virtual
9:43
environment folder for lab 6b and here's
9:46
a list of the packages that are
9:48
installed so right now just pip and
9:50
setup tools
9:52
but i want to add pi odbc to this list
9:55
so the way i do that is i click on this
9:57
plus sign which says install
10:00
and then i can search for the package
10:03
oh d b c the package i want to install
10:08
make sure it's highlighted it'll tell me
10:11
about the package
10:12
and then i go ahead and click this
10:14
install package button
10:17
and it will say package biodbc installed
10:20
successfully
10:21
assuming you have installed the visual c
10:24
build tools
10:25
and everything else and then if i close
10:28
this
10:29
you'll see that piodbc is installed
10:32
and i say ok and now if i run the
10:36
program
10:37
it works just the same as it does when
10:40
i'm using the global interpreter

[Link]

After installing the packages, you can import them into your Python programs using:

import numpy as np
import pandas as pd
import [Link] as plt
Here's an example program you can run to make sure everything is installed and working
correctly:

import pymssql
import pandas as pd
import [Link] as plt
connection = [Link](
server='[Link]',
user='275student',
password='275student',
database='IMDB'
)

sql = """
SELECT startYear, COUNT(*) AS count
FROM title_basics
WHERE titleType = 'tvSeries'
AND startYear BETWEEN 1920 AND 2016
GROUP BY startYear
ORDER BY startYear;
"""

df = pd.read_sql(sql, connection)
[Link](df['startYear'], df['count'])
[Link]("Year the Series Started")
[Link]("Number of Series")
[Link]("Number of TV Series that Started in Each Year")
[Link]()

If everything is working, it should produce the following graph in Figure 2.1:

Figure 2.1: Number of TV series that started per year

 Lesson 3: A Case Study
Let's begin with a simple case study that illustrates all of the steps we described in the overview.
The steps were:

1. Set your goals

2. Define your target audience
3. Get the data
4. Clean the data
5. Prepare and analyze the data
6. Visualize the data

Click on the accordion title below to expand each step for more information.

Set your goals: Since

we've been working with IMDB (Internet Movie
Database) data earlier in the course, let's continue with that dataset.
Here, our client will be a movie producer interested in using analytics to
choose a new project. In particular, the client wants to know what movie
genres are hot right now and are therefore more likely to turn a profit (if
the client likes our work in helping to select a genre, we may get follow-
on work where we help the client select a director and some actors).

Define your target audience: Our customer is a movie producer who is a

financially savvy individual. They're used to working with spreadsheets and looking at
graphs, but they're "big picture" thinkers and don't have a lot of patience for details.
Therefore, we will want to provide them with a clear line of reasoning and a few simple
but compelling data visualizations.

Get the data: What the client wants to know is how much money they will
make from their next film. So, ideally, we'd like to find a dataset that
includes both genre and total box office for a large number of movies
and derive average earnings for movies in each genre. Unfortunately,
most of the published data show the total box office for each genre, not
for each movie. For example, the box office data in the graph here:
Statista: North American Box Office Data shows the total box office for
Adventure movies from 1995-2021 was $64 billion. However, what
doesn’t show was the average box office for each Adventure movie or
the total number of Adventure movies produced. So it's hard to estimate
how much a new Adventure movie is likely to earn on average or to
compare the average earnings of an Adventure movie with an Action or
Drama movie.
We also want trend information for each genre on a year-by-year basis
to find genres that are currently trending upward. Thus, we increase the
chances the genre will be hot when the client completes their new film.
Unfortunately, we don't have box office data in precisely the format we
need; however, we have access to IMDB data. It doesn't include box
office data for each movie, but it does have over 670 million movie
ratings covering 478 thousand movies. We know from running the
following code:
import pymssql
import pandas as pd

# Use pymssql to connect to the IMDB database on [Link]

connection = [Link](
server='[Link]',
database='IMDB',
user='275student',
password='275student'
)

# This SQL query sums up all the values in the numVotes column in
title_ratings
# for all the movies. That displays the total number of times an IMDB user
rated a movie.
sql = """
SELECT SUM(numVotes) AS totalVotes
FROM title_ratings
JOIN title_basics ON title_ratings.tconst = title_basics.tconst
WHERE titleType = 'movie';
"""

# Read the results of the query and store them in a pandas dataframe.
df = pd.read_sql(sql, connection)
print(df)

# This query counts the total number of movies in the database.

sql = """
SELECT COUNT(*) AS count
 FROM title_basics
WHERE titleType = 'movie';
"""

# Execute the query and store the results in a pandas dataframe.

df = pd.read_sql(sql, connection)
print()
print(df)

which produces the following output:

totalVotes
0 670948989

count
0 478024
Using the IMDB data represents a compromise we can discuss with the
client. Instead of choosing a genre to maximize earnings, we'll choose a
genre to maximize viewer ratings, which we expect will correlate with
earnings to some degree (movies that are highly rated by moviegoers
tend to do well at the box office). We can also determine the degree of
correlation if we can find a smaller set of movies with their box office
numbers. We can then calculate the statistical correlation between the
ratings data from the IMDB title_ratings table and the box office data for
the smaller sample. This can also be used to create a simple formula for
estimating box office based on the average IMDB rating.
As shown in the sample code above, we can use pyodbc to open a
connection to the database and the Pandas read_sql method to send a
SQL command to the database and wrap the results up in a dataframe.
A dataframe is a Pandas data structure that holds a table worth of data,
including an index, a set of columns, and a set of rows. Here, the queries
we are executing return very simple dataframes - they only include one
column of information (the aggregate number of total votes across all
movies in the title_ratings table for the first query, and the total number
of movies in the title_basics table for the second query), and one row for
each dataframe which includes the aggregate statistics across the entire
database.
Note when the data is already in a SQL database, we can rely on
complex SQL queries to fetch exactly what we need, which reduces the
amount of work we need to do in Pandas to clean and prepare the data.
However, that’s not true when we're reading data from a flat text file
(such as a Comma-Separated Values or CSV text file). When reading a
flat text file, we have to read the entire file into a dataframe and then rely
on Pandas commands to filter exactly what we need.
Clear the data: The first step in cleaning the data is to examine the
values in the dataset. For this problem, we're going to be focusing on the
year a movie was released (the startYear column in the title_basics table
of the IMDB), the genres of each movie (the genre column of the
title_genres table), and the average rating of each movie (the
averageRating column of the title_ratings table). The value that links
these tables together is the unique ID assigned to each movie, which is
found in the tconst column of each table.
We start by looking at the values in the startYear column, as follows:
# Imports and connection to the database are as given above, and
# will not be repeated to keep the code examples as short as possible.

# Count the total number of movies for each year.

sql = """
SELECT startYear, COUNT(*) AS Count
FROM title_basics
WHERE titleType = 'movie'
GROUP BY startYear
ORDER BY startYear;
"""

# Execute the query and store it in a pandas dataframe

df = pd.read_sql(sql, connection)
# Show an excerpt of the data.
print(df)

# Graph the data as a line graph using matplotlib.

# startYear will be on the X coordinate and the number of movies
# will be on the Y coordinate.

# Set the size of the graph to 12" by 8" at 72dpi.

[Link](figsize=(12, 8), dpi=72)
# Plot the data.
[Link]("startYear", "Count", data=df, marker='o')
# Show the graph.
[Link]()
Produces this output:
 startYear Count
0 NaN 36925
1 1894.0 1
2 1897.0 2
3 1898.0 26
4 1899.0 12
.. ... ...
129 2024.0 6
130 2025.0 3
131 2026.0 1
132 2028.0 1
133 2115.0 1

[134 rows x 2 columns]

In the dataframe, we see there are a large number of "not a number"
values and the year is being read as a decimal number. We also see
some movies with startYears in the future (we assume this is for planned
releases, but it's not clear how someone would plan a release for the
year 2115).

Figure 3.1: Number of movies per year

Looking at the plot in Figure 3.1, we see a small number of movies were
released as far back as the late 1800s, but there was a surge in the
number of movies from about 1910-1920. We'll want to skip years before
1920 because there's not enough data to make reliable conclusions.
Towards the middle of the graph, we see a big jump in the number of
movies starting slightly after 2000. Perhaps this is due to the wide
availability of digital recording devices and the rise of the worldwide web,
which allowed streaming services to get their start. These new means of
production and distribution contributed to a reduction in the cost of
making an independent movie and made movies accessible to a wider
audience, which increased the commercial opportunities for creating new
movies. The data sharply declines starting in 2018, which is expected
because the database was built using data from 2017. So, we'll want to
skip all data from 2018 on.
Here is the same SQL query, but we've added a filter to the dataset:
sql = """
SELECT startYear, COUNT(*) AS Count
FROM title_basics
WHERE titleType = 'movie'
GROUP BY startYear;
"""

df = pd.read_sql(sql, connection)

# this line takes the dataframe, filters out all rows where the startYear
is null, or the year is
# less than 1920, or the year is greater than 2017. Then, it sorts the
rows in the dataframe
# in ascending order (the default) by the startYear.
df = df[(df['startYear'].notnull()) & (df['startYear'] >= 1920) &
(df['startYear'] <= 2017)].sort_values("startYear")

# print an excerpt of the cleaned data.

print(df)

# Plot the data. This prot is the same as the one above, but it's running
on a filtered dataframe,
# and we've also added a command to show tick labels every 10 years from
1920 through 2020.
[Link](figsize=(12, 8), dpi=72)
[Link]("startYear", "Count", data=df, marker='o')
[Link](range(1920, 2020, 10))
[Link]()
The query above produces the following output:
startYear Count
12 1920.0 2630
13 1921.0 2590
79 1922.0 2172
80 1923.0 1893
81 1924.0 1928
.. ... ...
60 2013.0 14620
124 2014.0 15389
125 2015.0 16036
126 2016.0 17216
127 2017.0 18969

[98 rows x 2 columns]

We see the NaN row has been removed, and the startYear ranges from
1920-2017, with the years in ascending order.

Figure 3.2: Number of movies per year with filter

The plot above in Figure 3.2 looks good and no other cleaning on the
startYear data seems necessary.
Next, we look at the averageRating column in title_ratings. Here's a
histogram showing the distribution of ratings:
# Get all the ratings for all the movies between 1920 and 2017. This takes
a while.
sql = """
SELECT startYear, averageRating
FROM title_basics
JOIN title_ratings ON title_basics.tconst = title_ratings.tconst
WHERE startYear BETWEEN 1920 AND 2017
AND titleType = 'movie';
"""

df = pd.read_sql(sql, connection)
[Link](figsize=(12, 8), dpi=72)

# The matplotlib hist method produces a histogram that shows the frequency
of values
# in different "bins." The IMDB ratings range from 1.0 up to 10.0 by
tenths, so it makes
# sense to use bins from 1-2, from 2-3, ..., from 9-10. That's 9 bins
total.
[Link](df['averageRating'], bins=9)
[Link](range(0, 11, 1))
[Link]()
Figure 3.3 shows the histogram:
 Figure 3.3: Histogram of distribution of ratings

This is about what one would expect. The most frequent movie rating is
between 6 and 7, and the ratings look like a standard bell curve. Next,
let's look at a breakdown of the averageRating by year:
# GROUP BY means combine all the data for each individual year into one
row of results.
# That will result in one row each for 1920, 1921, 1922, ..., 2017. Each
row will have a rating
# column, and the value of rating will be the average of all the
individual averageRatings for
# each of the movies in that year.
sql = """
SELECT startYear, AVG(averageRating) AS rating
FROM title_basics
JOIN title_ratings ON title_basics.tconst = title_ratings.tconst
WHERE startYear BETWEEN 1920 AND 2017
AND titleType = 'movie'
GROUP BY startYear
ORDER BY startYear;
"""

df = pd.read_sql(sql, connection)
# Create a simple plot of the results.
[Link](figsize=(12, 8), dpi=72)
[Link]("startYear", "rating", data=df)
[Link]()
Figure 3.4 below shows the resulting graph:

Figure 3.4: Number of movies per year

The results look pretty noisy but pay close attention to the Y-axis labels,
which range from 6.0 up to 6.8. That means the average rating only
varied from 6.4 ± 0.4 (6.4 plus or minus 0.4). That's a pretty narrow
range for almost 100 years of movie data and indicates the data is
probably reasonably clean. There may be some additional factors in play
between 1970-2010 that explain why the ratings were slightly depressed
over that time period, but we have no direct evidence for the cause.
Finally, let's look at the genre values in title_genres (we need to JOIN
with title_basics to select only the shows that are movies, with startYear
between 1920 and 2017):
# For each genre, merge all the movies for that genre into a single row.
# The Count column will include the number of movies from that genre.
# Return the results with the most common genres first.
sql = """
SELECT genre, COUNT(*) AS Count
FROM title_genre
JOIN title_basics ON title_genre.tconst = title_basics.tconst
WHERE startYear BETWEEN 1920 AND 2017
AND titleType = 'movie'
GROUP BY genre
ORDER BY Count DESC
"""

df = pd.read_sql(sql, connection)
[Link](figsize=(12, 8), dpi=72)
# Create a bar chart this time, because we are dealing with categories of
# information (different genres) rather than a range of quantities (like
years)
# There will be one bar for each genre.
[Link](df['genre'], df['Count'])
# Rotate the x labels by 90 degrees (vertical orientation) so that they
# don't overlap.
[Link](rotation=90)
# When you rotate the labels, they take up move space than they normally
would.
# The next line tells matplotlib to recalculate the layout so that the
labels
# aren't cut off.
plt.tight_layout()
Below is the resulting bar chart in Figure 3.5:
 Figure 3.5: Number of movies per year

We see a relatively large number of genres, but most of the genres

include relatively few movies. We will want to only include the most
common genres because the conclusions you draw from small amounts
of data are unreliable. Also, a large number of genres will make it difficult
to see and understand how the genres compare with each other.

Prepare and analyze the data: We already began preparing the data in
the previous step, when we began to create simple visualizations. In this
step, we will continue to answer the "big question," which genre should
we recommend to the client. Let's begin by creating a list of the genres
we want to include in the analysis:
# This is the same query as we saw in the previous example.
# It calculates the number of movies for each genre.
sql = """
SELECT genre, COUNT(*) AS Count
FROM title_genre
JOIN title_basics ON title_genre.tconst = title_basics.tconst
 WHERE startYear BETWEEN 1920 AND 2017
AND titleType = 'movie'
GROUP BY genre
ORDER BY Count DESC
"""

df = pd.read_sql(sql, connection)
# Let's filter out all of the genres with less than 20,000 movies.
MIN_MOVIES = 20000
# Select only the rows where the number of movies is greater than
MIN_MOVIES,
# and then return the genres column for only those movies.
genres = df[df['Count'] > MIN_MOVIES]['genre'].values
# This creates a string of comma separated values with single
# quotes around them that we can use as a filter in a SQL query.
# Basically, the code creates a list of each genre in genres, and adds
single
# quotes around each genre in the list. Then, it joins all the strings in
the
# list together using a comma as a separator between each quoted genre.
GENRES = ",".join([ "'" + x + "'" for x in genres])
print(GENRES)
Here's the GENRES string:
'Drama','Documentary','Comedy','Romance','Action','Crime','Thriller'

There ended up being seven genres with more than 20,000 movies. We
could adjust MIN_MOVIES in the code above if we wanted to include
more or fewer genres in the list.
Now let's look at the relationship between genre and overall rating for all
of the genres:
# For each genre, create one row of results by merging all
# the movies in that genre into a single row. Calculate the average
# of the average ratings for all of those movies. Order the results
# in descending order by the average of the average ratings (more highly
# rated genres come before less highly rated genres.
sql = """
SELECT genre, AVG(averageRating) AS rating
FROM title_genre
JOIN title_basics ON title_genre.tconst = title_basics.tconst
JOIN title_ratings ON title_genre.tconst = title_ratings.tconst
WHERE startYear BETWEEN 1920 AND 2017
AND titleType = 'movie'
GROUP BY genre
 ORDER BY AVG(averageRating) DESC;
"""

df = pd.read_sql(sql, connection)

# Display the results as a bar chart.

[Link](df['genre'], df['rating'])
[Link](rotation=90)
plt.tight_layout()
Figure 3.6 shows the resulting bar chart:

Figure 3.6: Histogram of genres from highly rated to lower rated

We see that some of the highly rated genres have very few movies (for
example, News movies and Talk-Show movies). Let's add our filter on
GENRES:
# The SQL condition:
# fruit in ('Apple','Cherry','Banana')
# returns true for rows where the value in the fruit column is in the list
of strings.
# We're using this syntax to find genres in our list of GENRES.
sql = F"""
 SELECT genre, AVG(averageRating) AS rating
FROM title_genre
JOIN title_basics ON title_genre.tconst = title_basics.tconst
JOIN title_ratings ON title_genre.tconst = title_ratings.tconst
WHERE startYear BETWEEN 1920 AND 2017
AND titleType = 'movie'
AND genre IN ({GENRES})
GROUP BY genre
ORDER BY AVG(averageRating) DESC;
"""

df = pd.read_sql(sql, connection)
[Link](figsize=(12, 8), dpi=72)
[Link](df['genre'], df['rating'])
[Link](rotation=90)
plt.tight_layout()
Figure 3.7 below shows the graph:

Figure 3.7: Histogram of highly rated genres with movies

This tells part of the story because it indicates Documentary movies

have been popular over the entire range of 1920 through 2017.
However, tastes change over time. For example, Westerns were the
most popular Hollywood genre until the 1960s, but lost popularity after
that. And following on the successes of Star Wars and Close Encounters
of the Third Kind in 1977, there was a big boom in Science Fiction
movies until about the 2000s. What we ultimately want is not just popular
genres but genres that are trending. We need to look at the average
rating breakdown for each genre by year and look for genres where the
popularity is on the rise.
# Same as before, but now we're produced one row for each combination
# of startYear and genre. That will let us plot a separate trend line
# for each genre, where the x coordinate is the year, and the y
# coordinate is the average of the average ratings.
sql = F"""
SELECT genre, startYear, AVG(averageRating) AS rating
FROM title_genre
JOIN title_basics ON title_genre.tconst = title_basics.tconst
JOIN title_ratings ON title_genre.tconst = title_ratings.tconst
WHERE genre IN ({GENRES})
AND startYear BETWEEN 1920 AND 2017
AND titleType = 'movie'
GROUP BY genre, startYear
ORDER BY startYear;
"""

df = pd.read_sql(sql, connection)
print(df)

[Link](figsize=(12, 8), dpi=72)

# Loop through each genre in the list of common genres and add a line plot
for each one.
# Note that the data parameter in the plot statement will only include the
rows in the dataframe
# where the value of genre matches the genre for this trend line.
for genre in genres:
[Link]("startYear", "rating", data=df[df['genre'] == genre])
# Add a legend so we can see which line matches which genre.
[Link](genres)
[Link]()
Here's the dataframe. You can see there are columns for genre and
startYear, and the rating gives the average of the ratings for all the
movies in that genre in that year.
genre startYear rating
0 Action 1920 5.566666
1 Comedy 1920 6.548571
2 Documentary 1920 6.540000
3 Drama 1920 6.410377
4 Romance 1920 6.705263
.. ... ... ...
679 Romance 2017 6.638619
680 Crime 2017 6.479086
681 Documentary 2017 7.595419
682 Drama 2017 6.858024
683 Thriller 2017 6.089536

[684 rows x 3 columns]

Figure 3.8 shows the graph:

Figure 3.8: Average movie ratings for each genre per year

The graph is a bit of a mess! There are a lot of jagged lines, and they
overlap, and it's hard to make out exactly what's going on. Fortunately,
we can smooth the data a bit to show the overall trends without all the
visual clutter.
[Link](figsize=(12, 8), dpi=72)
for genre in genres:
 # This is using an "exponential weighted average" to smooth the data
in the forward direction.
smooth = df[df['genre'] == genre]["rating"].ewm(span=20).mean()
# Next we reverse the data.
smooth = [Link](index=[Link][::-1])
# Now we smooth it again in the opposite direction. That just makes
sure that the peaks
# and valleys land in the right places.
smooth = [Link](span=20).mean()
# Finally, we reverse the data again to restore the original order.
smooth = [Link](index=[Link][::-1])
[Link](df[df['genre'] == genre]["startYear"], smooth)
[Link](genres)
[Link]()
Figure 3.9 shows the smoothed graph:

Figure 3.9: Average movie rating per genre per year

Now, we can see what's going on a bit more easily. We see

Documentary films have been on a slow upward trend since the 1950s,
while the rest of the genres are in a slump until the last decade or so. In
particular, thrillers were highly rated in the 1920s but have been steadily
declining since then. Overall, based on this evidence, we would have to
suggest Documentaries are a good genre if one is interested in high
ratings. The average documentary scores 7.25 on IMDB, while the
average drama, the next most highly rated genre, only scores 6.5, which
is over a full point lower.
Finally, let's look at some supporting evidence. Here are the most highly
rated documentaries from 2012 through 2017 (the last five years in our
database):
GENRE = "Documentary"

# Concatenating the movie's title with it's startYear.

# The query itself is just ordering the Documentary movies with more than
10000 votes by average rating,
# with the most highly rated movies at the top.
sql = """
SELECT TOP 20 primaryTitle + ' (' + CONVERT(VARCHAR, startYear) + ')'
AS primaryTitle, averageRating AS rating, numVotes
FROM title_genre
JOIN title_basics ON title_genre.tconst = title_basics.tconst
JOIN title_ratings ON title_genre.tconst = title_ratings.tconst
WHERE genre = ?
AND titleType = 'movie'
AND startYear BETWEEN 2012 AND 2017
AND numVotes > 10000
ORDER BY averageRating DESC
"""

df = pd.read_sql(sql, connection, params=(GENRE,))

print(df)
[Link](figsize=(12, 8), dpi=72)
[Link](df["primaryTitle"], df["rating"], data=df)
[Link](rotation=90)
[Link]()
An excerpt from the dataframe:
primaryTitle rating numVotes
0 CM101MMXI Fundamentals (2013) 9.3 39090
1 O.J.: Made in America (2016) 9.0 12915
2 Cowspiracy: The Sustainability Secret (2014) 8.4 15048
3 The Salt of the Earth (2014) 8.4 15472
4 Before the Flood (2016) 8.3 19395
.. ... ... ...
15 Marley (2012) 8.0 11924
16 What the Health (2017) 7.9 19676
17 I Am Not Your Negro (2016) 7.8 10455
18 Jim & Andy: The Great Beyond (2017) 7.8 12473
19 Amy (2015) 7.8 39489

[20 rows x 3 columns]

Figure 3.10 contains a bar plot showing some popular documentary
movies over the last five years in the database:

Figure 3.10: Popular documentary movies over the last five years by rating
Though for this one, given the length of the labels, a better use of the
space would probably be a horizontal bar graph (Figure 3.11) using
[Link]():

Figure 3.11: Popular documentary movies over the last five years by rating

Interestingly, the #1 film on the list is "CM101MMXI Fundamentals",

which Wikipedia says is a Turkish stand-up comedy that attracted an
audience of over 600,000 in its first week. Something like that sounds
like it would be comparatively cheap to produce but has the potential for
a large return on investment.
Visualize the data:
All that's left is to select which graphics and tables we need to tell our
story to the client and add appropriate labels to the axes and titles for the
charts. The key visual is this graph:
genres = ['Documentary', 'Comedy', 'Drama', 'Action', 'Romance']

sql = F"""
SELECT genre, startYear, AVG(averageRating) AS rating
FROM title_genre
JOIN title_basics ON title_genre.tconst = title_basics.tconst
JOIN title_ratings ON title_genre.tconst = title_ratings.tconst
WHERE genre IN ({GENRES})
AND startYear BETWEEN 1920 AND 2017
AND titleType = 'movie'
GROUP BY genre, startYear
 ORDER BY startYear;
"""

df = pd.read_sql(sql, connection)

[Link](figsize=(12, 8), dpi=72)

for genre in genres:
# This is using an "exponential weighted average" to smooth the data
in the forward direction.
smooth = df[df['genre'] == genre]["rating"].ewm(span=20).mean()
# Next we reverse the data.
smooth = [Link](index=[Link][::-1])
# Now we smooth it again in the opposite direction. That just makes
sure that the peaks
# and valleys land in the right places.
smooth = [Link](span=20).mean()
# Finally, we reverse the data again to restore the original order.
smooth = [Link](index=[Link][::-1])
[Link](df[df['genre'] == genre]["startYear"], smooth)
[Link](genres)
[Link]("Release Year")
[Link]("Average Rating on IMDB")
[Link]("Ratings by Genre from 1920-2017 (smoothed)")
[Link]()
Figure 3.12 shows the final version of the graph:
 Figure 3.12: Popular documentary movies over the last five years by rating

I've taken the smoothed graph from before but pruned it down to five
popular genres to make it a bit less complicated. I've also added a
descriptive title and labels for the x and y axes.
For the second visualization, I'd probably just produce a table showing
some popular documentaries:
GENRE = "Documentary"

sql = """
SELECT primaryTitle AS Title, startYear AS "Release Year",
averageRating AS "Rating on IMDB", numVotes AS "Number of Votes"
FROM title_genre
JOIN title_basics ON title_genre.tconst = title_basics.tconst
JOIN title_ratings ON title_genre.tconst = title_ratings.tconst
WHERE genre = ?
AND titleType = 'movie'
AND startYear BETWEEN 2012 AND 2017
AND numVotes > 10000
AND averageRating >= 7.5
 ORDER BY averageRating DESC
"""

df = pd.read_sql(sql, connection, params=(GENRE,))

# This option shows all the rows in the dataframe, instead of an excerpt.
pd.set_option('display.max_rows', None)
print(df.to_html(index=False))
Sometimes, a table is easier to read than a graph.

Title Release Rating on Number of

Year IMDB Votes

CM101MMXI Fundamentals 2013 9.3 39090

O.J.: Made in America 2016 9.0 12915

Cowspiracy: The Sustainability Secret 2014 8.4 15048

The Salt of the Earth 2014 8.4 15472

Before the Flood 2016 8.3 19395

13th 2016 8.2 15872

Searching for Sugar Man 2012 8.2 54783

The Act of Killing 2012 8.2 28475

Blackfish 2013 8.1 52567

Citizenfour 2014 8.1 44956

The Internet's Own Boy: The Story of 2014 8.1 13777

Aaron Swartz

Jodorowsky's Dune 2013 8.1 17221

Marley 2012 8.0 11924

Going Clear: Scientology & the Prison of 2015 8.0 31011

Belief

Icarus 2017 8.0 12172

Free to Play 2014 8.0 12466

What the Health 2017 7.9 19676

I Am Not Your Negro 2016 7.8 10455

Jim & Andy: The Great Beyond 2017 7.8 12473

Amy 2015 7.8 39489

Life Itself 2014 7.8 13195

Sound City 2013 7.8 10889

Finding Vivian Maier 2013 7.7 10878

Indie Game: The Movie 2012 7.7 18825

Stories We Tell 2012 7.6 10569

Side by Side 2012 7.6 12054

TPB AFK: The Pirate Bay Away from 2013 7.6 12732
Keyboard

Where to Invade Next 2015 7.6 18708

Tickled 2016 7.6 10165

Soaked in Bleach 2015 7.5 11329

Cobain: Montage of Heck 2015 7.5 24189

The Imposter 2012 7.5 39515

Lesson 4: Essential Operations in Pandas
Reading data into frames:
Click on the accordion title below to expand for more information and
examples on a few of the essential operations you will be using in Pandas:

Handy functions for creating a dataframe include:

import pandas as pd

# Reads a flat file of comma separated values (CSV) and stores the data in
a dataframe.
my_dataframe = pd.read_csv("my_csv_file.csv")

# Instead of a filename, you can also supply a URL that fetches CSV data.
url = "[Link]
accessType=DOWNLOAD"
mortality_data = pd.read_csv(url)

# As we saw in the case study, Pandas can also read data directly from a
SQL database.
connection = ... open a database connection ...
SQL = ... A string holding a SQL command ...
dataframe_from_database = pd.read_sql(SQL, connection)

Examining a dataframe:
When you print a dataframe in pandas, it looks like this:
startYear count
0 1921 2
1 1922 1
2 1923 2
3 1924 2
4 1927 1
.. ... ...
88 2012 7474
89 2013 7629
90 2014 7699
91 2015 8050
92 2016 7651

[93 rows x 2 columns]

 A dataframe includes the following parts:

Component Description

Column labels The names at the tops of the columns (startYear and count in
the dataframe above).

Column data The data in the columns. All of the data in a column typically has
the same data type with one entry in each row.

Column data types Each column has a defined data type. If all of the elements in a
column don’t have the same data type, the elements are stored
with the object data type.

Index Also known as a row label. If an index isn’t defined, it is

generated as a sequence of integers starting with zero (the
numbers 0 to 92 in the dataframe above).

Metadata Attributes of the DataFrame that are generated by Pandas when

the DataFrame is constructed or changed.

You can access some important attributes of a dataframe directly:

print("Index: ", mortality_data.index)
print("Columns: ", mortality_data.columns)
print("Size: ", mortality_data.size)
print("Shape: ", mortality_data.shape)

Running the code produces this output:

Index: RangeIndex(start=0, stop=476, step=1)
Columns: Index(['Year', 'Age Group', 'Death Rate'], dtype='object')
Size: 1428
Shape: (476, 3)

There are also some handy functions that will give you more insight into
the data:
url = "[Link]
accessType=DOWNLOAD"
mortality_data = pd.read_csv(url)

# Shows a list of the columns along with the number of values and the
types
print("info()")
print("=====================")
mortality_data.info()

# Shows the number of unique values for each column

print()
print("nunique()")
print("=====================")
print(mortality_data.nunique())

# Shows statistics for the values in each column

print()
print("describe()")
print("=====================")
print(mortality_data.describe())

Produces this output:

info()
=====================
<class '[Link]'>
RangeIndex: 476 entries, 0 to 475
Data columns (total 3 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Year 476 non-null int64
1 Age Group 476 non-null object
2 Death Rate 476 non-null float64
dtypes: float64(1), int64(1), object(1)
memory usage: 11.3+ KB

nunique()
=====================
Year 119
Age Group 4
Death Rate 430
dtype: int64

describe()
=====================
Year Death Rate
count 476.000000 476.000000
mean 1959.000000 192.924160
std 34.387268 293.224216
min 1900.000000 11.400000
25% 1929.000000 40.575000
50% 1959.000000 89.500000
75% 1989.000000 222.575000
 max 2018.000000 1983.800000

Accessing data:
You can select columns in a dataframe using square brackets along with
the column name:
# Produces a column of data along with an index.
# The actual type is <class '[Link]'>
print(mortality_data['Death Rate'])
print("=========================\n")

# Produces a dataframe with one column

# The actual type is <class '[Link]'>
print(mortality_data[['Death Rate']])
print("=========================\n")

# Produces a dataframe with two columns (can be used to produce as many

columns as are available)
print(mortality_data[['Death Rate', 'Year']])

Produces this output:

0 1983.8
1 1695.0
2 1655.7
3 1542.1
4 1591.5
...
471 45.5
472 48.3
473 51.2
474 51.5
475 49.2
Name: Death Rate, Length: 476, dtype: float64
=========================

Death Rate
0 1983.8
1 1695.0
2 1655.7
3 1542.1
4 1591.5
.. ...
471 45.5
472 48.3
473 51.2
474 51.5
475 49.2

[476 rows x 1 columns]

=========================

Death Rate Year

0 1983.8 1900
1 1695.0 1901
2 1655.7 1902
3 1542.1 1903
4 1591.5 1904
.. ... ...
471 45.5 2014
472 48.3 2015
473 51.2 2016
474 51.5 2017
475 49.2 2018

[476 rows x 2 columns]

You can also select rows from a dataframe:
# Select all the rows where Age Group is 1-4 Years
one_to_four_years = mortality_data[mortality_data['Age Group'] == '1-4
Years']

# The above line can be more easily understood as two separate lines:
# selected_rows will be a sequence of true/false values
selected_rows = mortality_data['Age Group'] == '1-4 Years'
# When you put a sequence of true/false values inside the square brackets,
# it selects the rows where the value is true.
one_to_four_years = mortality_data[selected_rows]

# Multiple selection criteria can also be combined using & (and) and |
(or).
# It's important to use the parenthesies when you do this because of
operator precedence.
# This will select the rows where the Age Group is 1-4 Years and also the
Year is between 2000 and 2015.
selected = mortality_data[(mortality_data['Age Group'] == '1-4 Years')
& (mortality_data['Year'] >= 2000)
& (mortality_data['Year'] <= 2015)]

# You can combine row selection and column selection. Here's only the
Death Rate series for the above selection:
death_rate_one_to_four_years_from_2000_through_2015 = selected['Death
Rate']
Sorting data:
If you're working with data from a database, you can use the SQL
ORDER BY clause of a SELECT statement to return the rows in a
particular data. However, sometimes, you'll want to specify the order
after the data is already in a dataframe. The sort_values method of
dataframes can be used to do that:
# sort_values doesn't change the order in mortality_data, it returns a
dataframe
# in the right order. So, we have to save that return value in a variable
if
# we want to use it later.
sorted = mortality_data.sort_values('Death Rate', ascending=False)

# You can also sort on multiple columns and specify ascending/descending

for each column
sorted = mortality_data.sort_values(['Year', 'Death Rate'],
ascending=[True, False])

# sort_values can be chained together with other row and column

selections.
sorted = mortality_data[(mortality_data['Age Group'] == '1-4 Years')
& (mortality_data['Year'] >= 2000)
& (mortality_data['Year'] <=
2015)].sort_values('Death Rate', ascending=False)

Arithmetic operations on series:

Just like you can add, subtract, divide, etc., individual numbers, you can
also perform basic arithmetic operations on pandas series. Normally,
when you operate on a list of numbers, you have to write a for loop or a
while loop and calculate each value one at a time. However, pandas
does the loop for you automatically. For example:
# This causes an error because you can't divide a list by a number:
scaled = [1000, 1200, 1300] / 100000

# However, this works correctly:

percent_mortality = mortality_data['Death Rate'] / 100000
# The above line returns a Series whose length is equal to the number of
rows in mortality_data.
# You can take advantage of that by storing the series back in the
dataframe as a new column.
# This code takes the Death Rate series, which is the number of deaths per
100000 people,
# uses it to calculate a new series, which is the percentage of people who
dies, and then
# stores the series as the Death Percentage column in mortality_data.
mortality_data['Death Percentage'] = 100 * mortality_data['Death Rate'] /
100000
print(mortality_data)

Running the code (except the first line, which causes an error) produces
this output:
Year Age Group Death Rate Death Percent
0 1900 1-4 Years 1983.8 1.9838
1 1901 1-4 Years 1695.0 1.6950
2 1902 1-4 Years 1655.7 1.6557
3 1903 1-4 Years 1542.1 1.5421
4 1904 1-4 Years 1591.5 1.5915
.. ... ... ... ...
471 2014 15-19 Years 45.5 0.0455
472 2015 15-19 Years 48.3 0.0483
473 2016 15-19 Years 51.2 0.0512
474 2017 15-19 Years 51.5 0.0515
475 2018 15-19 Years 49.2 0.0492

[476 rows x 4 columns]

This works because the arithmetic operators +, -, *, /, //, %, and ** have
all been redefined when applied to dataframes. However, other
operators and functions that you write yourself, which operate on
individual numbers, will probably not work correctly. For example:
strings = "{:.2f}%".format(mortality_data['Death Percent'])
will not work because format doesn't know how to handle a series as an
argument. When situations like that arise, you can write a function that
operates on individual values and then apply it to each element in the
series like this:
# This works on one number.
def format_percent(num):
return "{:.2f}%".format(num)
# This applies format_percent to each element in the series.
mortality_data['Percent String'] = mortality_data['Death
Percent'].apply(format_percent)
print(mortality_data)
and produces this output:
Year Age Group Death Rate Death Percent Percent String
0 1900 1-4 Years 1983.8 1.9838 1.98%
1 1901 1-4 Years 1695.0 1.6950 1.70%
2 1902 1-4 Years 1655.7 1.6557 1.66%
3 1903 1-4 Years 1542.1 1.5421 1.54%
4 1904 1-4 Years 1591.5 1.5915 1.59%
.. ... ... ... ... ...
471 2014 15-19 Years 45.5 0.0455 0.05%
472 2015 15-19 Years 48.3 0.0483 0.05%
473 2016 15-19 Years 51.2 0.0512 0.05%
474 2017 15-19 Years 51.5 0.0515 0.05%
475 2018 15-19 Years 49.2 0.0492 0.05%

[476 rows x 5 columns]

The function you're applying can take any type of parameter. For
example, we could use the following code get rid of the word "Years" in
the Age Group column:
def strip_years(group_string):
if group_string[-6:] == ' Years':
return group_string[:-6]
else:
return group_string

mortality_data['Stripped Age'] = mortality_data['Age

Group'].apply(strip_years)
print(mortality_data)
produces this output:
Year Age Group Death Rate Death Percent Percent String Stripped
Age
0 1900 1-4 Years 1983.8 1.9838 1.98%
1-4
1 1901 1-4 Years 1695.0 1.6950 1.70%
1-4
2 1902 1-4 Years 1655.7 1.6557 1.66%
1-4
3 1903 1-4 Years 1542.1 1.5421 1.54%
1-4
4 1904 1-4 Years 1591.5 1.5915 1.59%
1-4
.. ... ... ... ... ...
...
471 2014 15-19 Years 45.5 0.0455 0.05%
15-19
472 2015 15-19 Years 48.3 0.0483 0.05%
15-19
473 2016 15-19 Years 51.2 0.0512 0.05%
15-19
474 2017 15-19 Years 51.5 0.0515 0.05%
15-19
475 2018 15-19 Years 49.2 0.0492 0.05%
15-19

[476 rows x 6 columns]

Reshaping the data - Pivot:

Data often comes in two formats, sometimes known as "long form" or
"wide form." The mortality_data dataframe is in "long form" because the
Age Group column includes separate categories for the different age
groups all in one row, and the Death Rate column contains the death
rate for all of the different categories all in one (long) column:

Year Age Group Death Rate

0 1900 1-4 Years 1983.8

1 1901 1-4 Years 1695.0

2 1902 1-4 Years 1655.7

3 1903 1-4 Years 1542.1

4 1904 1-4 Years 1591.5

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

471 2014 15-19 Years 45.5

472 2015 15-19 Years 48.3

473 2016 15-19 Years 51.2

 474 2017 15-19 Years 51.5

475 2018 15-19 Years 49.2

476 rows × 3 columns
However, consider an alternative presentation of the same data with
separate columns for the death rates for each of the different age
categories:

Year 1-4 Years 10-14 Years 15-19 Years 5-9 Years

0 1900 1983.8 298.3 484.8 466.1

1 1901 1695.0 273.6 454.4 427.6

2 1902 1655.7 252.5 421.5 403.3

3 1903 1542.1 268.2 434.1 414.7

4 1904 1591.5 305.2 471.4 425.0

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

114 2014 24.0 14.0 45.5 11.5

115 2015 24.9 14.6 48.3 11.7

116 2016 25.3 14.6 51.2 12.2

117 2017 24.3 15.5 51.5 11.6

118 2018 24.0 14.9 49.2 11.5

119 rows × 5 columns
This presentation includes the same data, just organized into "wide
form," with multiple columns for each age group, with the Death Rate
values inside of the age group columns. Another example would be
some name data for the name Lynn:

Year Name Gender NameCount

0 1915.0 Lynn F 44.0

 1 1915.0 Lynn M 305.0

2 1916.0 Lynn F 56.0

3 1916.0 Lynn M 320.0

4 1917.0 Lynn F 58.0

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

195 2012.0 Lynn M 27.0

196 2013.0 Lynn F 91.0

197 2013.0 Lynn M 20.0

198 2014.0 Lynn F 87.0

199 2014.0 Lynn M 16.0

200 rows × 4 columns
This data is in "long" format because the Gender column holds data that
marks both male and female genders. Here's the wide form of the same
data:

Year Name F M

0 1915.0 Lynn 44.0 305.0

1 1916.0 Lynn 56.0 320.0

2 1917.0 Lynn 58.0 345.0

3 1918.0 Lynn 69.0 362.0

4 1919.0 Lynn 64.0 346.0

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

95 2010.0 Lynn 111.0 19.0

96 2011.0 Lynn 109.0 30.0

97 2012.0 Lynn 91.0 27.0

 98 2013.0 Lynn 91.0 20.0

99 2014.0 Lynn 87.0 16.0

100 rows × 4 columns
In the wide form, there are separate columns for each of the different
values of Gender, and the values in those columns are the NameCount
values from the long form version. Why would we want to convert long
form data into wide form data? Sometimes, it's easier to graph when the
data is in separate columns, and sometimes it's easier to compare
different values when they're in the same row. For example, in wide
form, it's easy for me to calculate the percentage of babies that are
named Lynn who are female:
lynn_wide['Percent Female'] = 100 * lynn_wide['F'] / (lynn_wide['F'] +
lynn_wide['M'])
Which looks like this:

Year Name F M Percent Female

0 1915.0 Lynn 44.0 305.0 12.607450

1 1916.0 Lynn 56.0 320.0 14.893617

2 1917.0 Lynn 58.0 345.0 14.392060

3 1918.0 Lynn 69.0 362.0 16.009281

4 1919.0 Lynn 64.0 346.0 15.609756

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

95 2010.0 Lynn 111.0 19.0 85.384615

96 2011.0 Lynn 109.0 30.0 78.417266

97 2012.0 Lynn 91.0 27.0 77.118644

98 2013.0 Lynn 91.0 20.0 81.981982

99 2014.0 Lynn 87.0 16.0 84.466019

100 rows × 5 columns
It would be more complicated to calculate this in long form because we'd
have to match separate rows before we could do the calculation. It's
interesting to note that Lynn was predominantly a male name in the early
years, but in more recent times, Lynn is most often used for female
babies. We'll visit this data again when we cover plotting.
The conversion from long form to wide form can be done using a
DataFrame method called "pivot", which takes three optional
parameters.
● index - The wide form version should include one row of results for
each distinct value in the index column (or combination of values if
there are multiple index columns). For example, in both the
mortality data and the Lynn data, we want to end up with one row
for each year, so year would be an index column in the pivot. In
addition, in the Lynn data, we want to keep a column for the name,
even though there's only one value for all of the rows, so we
include Name along with Year as an index column.
● columns - This is the column or columns that holds the different
categories that we want to create wide form columns for. In the
mortality data, "columns" would be the Age Group column,
because that holds each of the different age categories. For the
Lynn data, "columns" would be the Gender column, because we
want separate wide form columns for M gender and F gender.
● values - After creating the new wide form columns, we need to fill
data into each row for those columns. "values" specifies what
column this data comes from. For the mortality data, we want to
store the Death Rate data for a combination of Year and Age
Group into the corresponding wide form row. For the Lynn data, we
want to store the NameCount value for the combination of Year,
Name, and Gender into the matching M and F columns.
Once you've identified the right combination of index, columns, and
values, writing the pivot itself is easy:
mortality_wide = mortality_data.pivot(index='Year', columns='Age Group',
values='Death Rate')
lynn_data_wide = lynn_data_long.pivot(index=["Year", "Name"],
columns="Gender", values="NameCount")
However, what this gives us is almost, but not quite, what we want:
 Gender F M

Year Name

1915.0 Lynn 44.0 305.0

1916.0 Lynn 56.0 320.0

1917.0 Lynn 58.0 345.0

1918.0 Lynn 69.0 362.0

1919.0 Lynn 64.0 346.0

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

2010.0 Lynn 111.0 19.0

2011.0 Lynn 109.0 30.0

2012.0 Lynn 91.0 27.0

2013.0 Lynn 91.0 20.0

2014.0 Lynn 87.0 16.0

100 rows × 2 columns
You would see similar results for the mortality data. You'll notice that we
have what appears to be the right columns, but the Year column and the
Name column are being shown in bold, and headers for Year and Name
are in a separate row from the headers for M and F. Also, there's a
header for Gender next to the header for M and F. What's going on is
that Year and Name are forming a multi-level index and are not columns
at all (the indexes behave differently from columns in several important
ways in pandas). Since this is often not what you want, you can "reset
the index" and make Year and Name be regular columns again, and you
can also clear the Gender label (which is the name of the columns):
lynn_data_wide = lynn_data_long.pivot(index=["Year", "Name"],
columns="Gender", values="NameCount")
lynn_wide = lynn_data_wide.reset_index()
lynn_wide.[Link] = None
The second line moves the indexes back to normal columns and creates
a new index that's numbered from 0 up to the number of rows minus one
(which is just like what we had in the long form dataframe). The third line
clears the lingering Gender label, which pandas thinks is the name of the
index. Similarly, for the mortality data:
mortality_wide = mortality_data.pivot(index='Year', columns='Age Group',
values='Death Rate')
mortality_wide = mortality_data_wide.reset_index()
mortality_wide.[Link] = None

Reshaping the data - Melt:

When we pivoted the long form data into wide form, we gave it a column
that held a number of different categories, and a column that held
values, and then the pivot created a bunch of columns, one for each
category, and placed the values into those columns. The opposite of that
operation can be done with the dataframe "melt" method. Melt creates a
long form dataframe from a wide form dataframe. You might want to do
that because some packages, such as a graphing package named
Seaborn which is built on top of Matplotlib, work better with long form
data. The parameters to melt are:
● var_name - this is the name of the column that will hold our
different categories. For the mortality data, it will be "Age Group",
and for the Lynn data, it will be "Gender".
● id_vars - when we pivoted the data, we gave it one or more index
columns. Those were the columns for which we wanted one row for
each distinct value. Here, we specify the same columns, but we
want them to go from having one row for each distinct value to
having one row for each distinct value in combination with the
var_name column. For the mortality data, id_vars would be Year by
itself. For the Lynn data, the id_vars would be both the Year and
also the Name column.
● value_vars - this is a list of the column names for each of the
categories. For the mortality data, value_vars would be ['1-4
Years', '10-14 Years', '15-19 Years', '5-9 Years']. For the Lynn
data, value_vars would be ['M', 'F'].
● value_name - this is the name of the column where you want the
values that are currently in the value_vars columns to go. It would
 be 'Death Rate' for the mortality data and 'NameCount' for the Lynn
data.
The actual commands to melt the wide data back into their original long
format are:
ld = lynn_wide.melt(var_name='Gender', id_vars=['Year', 'Name'],
value_vars=['M', 'F'], value_name="NameCount")
md = mortality_wide.melt(var_name='Age Group', id_vars='Year',
value_vars=['1-4 Years', '10-14 Years', '15-19
Years', '5-9 Years'], value_name='Death Rate')
This produces results like:

Year Name Gender NameCount

0 1915.0 Lynn M 305.0

1 1916.0 Lynn M 320.0

2 1917.0 Lynn M 345.0

3 1918.0 Lynn M 362.0

4 1919.0 Lynn M 346.0

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

195 2010.0 Lynn F 111.0

196 2011.0 Lynn F 109.0

197 2012.0 Lynn F 91.0

198 2013.0 Lynn F 91.0

199 2014.0 Lynn F 87.0

200 rows × 4 columns
Note that this contains the same data as the original long form version,
but the order might differ. If you want to maintain the same order, you'll
need to add an appropriate call to the sort_values method (please see
the above section on Sorting Data).
 Lesson 5: Essential Operations in
Matplotlib
Graphing with matplotlib
A picture is worth a thousand words! matplotlib is a popular Python
package used to visualize data and build plots (i.e., graphs). We can
create many types of graphs, including line, bar, pie, and scatter graphs,
using the matplotlib commands. The main subset of matplotlib package
is pyplot which houses all graph related functions. So we usually import
only that part as :
import [Link] as plt
Plotting data consists of the following steps:
1. Import pandas, [Link], and any other packages your
program needs.
2. Fetch the data and prepare it for plotting (usually done by creating
and manipulating a pandas dataframe).
3. Plot the data using [Link].
4. Add plot details, such as titles, legends, axis labels, and grid lines.
5. Show the plot using the show() method in [Link].
Here is a simple program you can try in PyCharm.
# Step 1: import our packages

import pandas as pd
import pymssql
import [Link] as plt

# Step 2: fetch the data and prepare it for plotting

name_connection = [Link](
server='[Link]',
database='NAMES',
user='275student',
password='275student'
)
sql = """
SELECT Year, Name, Gender, NameCount
FROM all_data
WHERE Name = 'Lynn';
"""

lynn_data_long = pd.read_sql(sql, name_connection)

lynn_data_wide = lynn_data_long.pivot(index=["Year", "Name"],

columns="Gender", values="NameCount")
lynn_wide = lynn_data_wide.reset_index()
lynn_wide.[Link] = None

# Step 3: plot the data

[Link](figsize=(12, 8), dpi=72)

[Link]('Year', 'F', data=lynn_wide)
[Link]('Year', 'M', data=lynn_wide)

# Step 4: add plot details

[Link]()
[Link]("Popularity of the Name Lynn")
[Link]("Year")
[Link]("Number of Babies Named Lynn")
[Link](['Female Babies', 'Male Babies'])

# Step 5: show the plot

[Link]()

# For a second graph, we've already imported our packages and fetched the
data.
# We just need to do a little more preparation this time.

# Step 2: prepare the data

lynn_wide['Percent Female'] = 100 * lynn_wide['F'] / (lynn_wide['F'] +

lynn_wide['M'])

# Step 3: plot the data

[Link](figsize=(12, 8), dpi=72)

[Link]('Year', 'Percent Female', data=lynn_wide)

# Step 4: add plot details

[Link]()
[Link]("Is Lynn a Female Name or a Male Name?")
[Link]("Year")
[Link]("Percent of Babies Named Lynn Who Were Female")

# Step 5: show the plot

[Link]()
When you run this in PyCharm, the plots will appear on the SciView tab
(underneath the Database tab on the far right of PyCharm). Click on the
tab to open the SciView pane, and then you should see two plots. Click
on each of the thumbnails to view the corresponding plot. You can also
resize the pane by grabbing an edge and dragging, and zoom in or out
using the buttons above the plot. Figure 5.1 shows a picture of the
SciView pane:

Figure 5.1: Popularity of the name Lynn by year

Two interfaces to graph creation

[Link] contains two separate interfaces for creating plots. The
first interface, which we will be focusing on in this class, is a "procedural"
interface where you create plots by issuing a series of commands that
affect the currently active plot. The second interface, which we will not
be using in this class, is more object-oriented, and provides you with
Figure and Axes objects you can call methods on. The main advantage
of the object-oriented interface is that you can have multiple figures and
axes active simultaneously and add to all of them in no particular order.
However, this added flexibility comes at a cost in terms of the complexity
of the code, which is why we're focusing on the procedural interface
instead.

Types of plots
Matplotlib supports many different types of plots, but most are fairly
specific to particular types of problems or problem domains. Rather than
present an exhaustive list, we'll instead focus on the most common types
of plots you're likely to need. These include:
● Line plots - probably, the single most common type of plot, and
where we'll focus the bulk of our attention. Line plots are good for
showing the relationship between two numerical quantities, such
as the number of people with a given name on a year-by-year
basis, or the number of people who come down with COVID-19 on
a given day.
● Bar charts - also, very common. These are good when one of the
values is a category rather than a quantity. For example, if you
want to show the number of babies with a particular name who
were male vs. the number of babies with the same name who were
female, you'd want to use bars to show that instead of a line plot.
Or, if you want to show the average rating for each movie genre,
you'd want to use one bar for each genre, and then the length of
the bar could be used to show the rating.
● Pie plots - similar to bar charts, they show how a quantity is related
to a category. But pie plots are more suitable when the number of
categories is small, and when the relationship shows the
percentage each category represents of a total. For example, a pie
chart would ideally represent the percentage of Americans who
belong to each political party or who are independent.
 ● Histograms - when you have many numerical data points, and
want to understand the distribution of those values, a histogram is
the right choice. For example, you might want to see the
distribution of ratings on IMDB.
● Scatter plots - good for understanding the relationship between two
numerical quantities, especially when the numerical quantities are
not strongly enough related for a line plot to make sense. For
example, you might want to look at a scatter plot that shows the
popularity of Lynn as a male baby name vs. the popularity of Lynn
as a male baby name on a year-by-year basis. Would you guess
that when Lynn is a popular male name, it is also a popular female
name at the same time, or would you guess when Lynn is a
popular male name, it's an unpopular female name, and vice
versa? A scatter plot can tell you.

Line plots: Line plots are suitable when you have a numeric quantity that
varies in relationship to some other numeric quantity. For example, if you
want to look at the number of movies each year, a line plot would suit
that. Or, if you wanted to look at the popularity of a particular name each
year, the popularity would be a numeric quantity, and the year would be
a numeric quantity, so a line plot would be appropriate for that as well.
To create a line plot, you need a series of data points containing an X
value and a Y value (X values are plotted against a horizontal axis and Y
values are plotted against a vertical axis). Line plots can be created with
the plot method of pyplot. The first two parameters to plot are a set of X
values (which could be a list or a pandas Series) and a set of Y values
(which could also be a list or a pandas Series). The simplest form of a
line plot would be something like this:
import [Link] as plt

import pandas as pd

import pymssql

connection = [Link](

server='[Link]',
 database='IMDB',

user='275student',

password='275student'

sql = """

SELECT startYear, COUNT(*) AS count

FROM title_basics

WHERE titleType = 'movie'

AND startYear BETWEEN 1920 AND 2017

GROUP BY startYear

ORDER BY startYear;

"""

df = pd.read_sql(sql, connection)

[Link](df['startYear'], df['count'])

[Link]()

Here, we pull out the series of data containing startYear, pass that as the
x values, and pull out the series of data that includes the count of the
number of movies released in the year and pass that as the y values.
When we show the plot, it looks like this:
 Figure 5.2: Number of movies released per year
A slightly way to create a plot with the same data is:
df = pd.read_sql(sql, connection)

[Link]('startYear', 'count', data=df)

[Link]()

Here, we pass in a data parameter that contains a pandas dataframe,

the name of a column in the dataframe for the X value, and the name of
a different column in the dataframe for the Y value.
Adjusting the line style
You can also adjust the line style:
[Link]('startYear', 'count', data=df, color='green', marker='o',
linestyle='dotted')

In Figure 5.3, we're using green for the line color, showing circular
markers for each data point, and using a dotted line. It looks like this:
 Figure 5.3: Number of movies released per year
You'll notice the markers are crowded together pretty closely. We
probably want either a larger chart, or smaller markers, or both:
[Link](figsize=(12, 8), dpi=72)

[Link]('startYear', 'count', data=df, color='green', marker='o',

linestyle='dotted', markersize=3)

In Figure 5.4, we've set the size of the current figure (where the current
plot lives) to be bigger and set the marker size to be as small as we
could get it. So now the markers are less crowded, and we can also see
that the line is dotted a little better:
 Figure 5.4: Number of movies released per year
You can also set the linewidth to be thicker or thinner.
There are a bunch of different ways to specify the line color, as
documented on the [Link] page, but this list of colors works
and should be helpful:
Here's a list of different marker names you can use:

character description

'.' point marker

',' pixel marker

'o' circle marker

'v' triangle_down marker

'^' triangle_up marker

'<' triangle_left marker

'>' triangle_right marker

'1' tri_down marker

'2' tri_up marker

'3' tri_left marker

'4' tri_right marker

'8' octagon marker

's' square marker

'p' pentagon marker

'P' plus (filled) marker

'*' star marker

'h' hexagon1 marker

'H' hexagon2 marker

'+' plus marker

'x' x marker
'X' x (filled) marker

'D' diamond marker

'd' thin_diamond marker

'|' vline marker

'_' hline marker

And here's a list of line styles:

Linestyle Description

'-' or 'solid' solid line

'--' or 'dashed' dashed line

'-.' or 'dashdot' dash-dotted line

':' or 'dotted' dotted line

'None' or ' ' or '' draw nothing

There are other style properties of your line and marker you can change,
but these are the most common ones. For future reference, here's a list
of all the line style properties you can set:

Property Value Type

alpha float

animated [True | False]

antialiased or aa [True | False]

clip_box a [Link] instance

clip_on [True | False]

clip_path a Path instance and a Transform instance, a Patch

color or c any matplotlib color

contains the hit testing function

dash_capstyle ['butt' | 'round' | 'projecting']

dash_joinstyle ['miter' | 'round' | 'bevel']

dashes sequence of on/off ink in points

data ([Link] xdata, [Link] ydata)

figure a [Link] instance

label any string

linestyle or ls [ '-' | '--' | '-.' | ':' | 'steps' | ...]

linewidth or lw float value in points

marker [ '+' | ',' | '.' | '1' | '2' | '3' | '4' ]

markeredgecolor or mec any matplotlib color

markeredgewidth or mew float value in points

markerfacecolor or mfc any matplotlib color

markersize or ms float

markevery [ None | integer | (startind, stride) ]

picker used in interactive line selection

pickradius the line pick selection radius

solid_capstyle ['butt' | 'round' | 'projecting']

solid_joinstyle ['miter' | 'round' | 'bevel']

transform a [Link] instance

visible [True | False]

xdata [Link]

ydata [Link]

zorder any number

Multiple lines on the same plot

Often, you will want to compare two trends on a single plot. For example,
you might want to compare the number of movies each year with the
number of tvSeries. There are a few ways to do this. The first way is to
call plot twice on the same current plot, and specify different Y values
each time:
# We have to RTRIM the titleType to strip off some extra spaces that get
added to the end.

# The spaces are added because titleType is defined as a fixed-width

character string in the database

# So shorter values get padded out with spaces (which we don't want).
sql = """

SELECT startYear, RTRIM(titleType) AS titleType, COUNT(*) AS count

FROM title_basics

WHERE (titleType = 'movie'

OR titleType = 'tvSeries')

AND startYear BETWEEN 1920 AND 2017

GROUP BY startYear, titleType

ORDER BY startYear;

"""

df = pd.read_sql(sql, connection)

# This is in long form, so let's pivot it to wide form

df_wide = [Link](index='startYear', columns='titleType', values='count')

df_wide = df_wide.reset_index()

[Link](figsize=(12, 8), dpi=72)

[Link](df_wide['startYear'], df_wide['movie'])

[Link](df_wide['startYear'], df_wide['tvSeries'])

[Link]()

Here, we're adding separate lines for the number of movies (dark blue)
and the number of tvSeries (orange). Here's the plot:
 Figure 5.5: Number of movies and TV series released per year
When you don't specify colors, matplotlib runs through a pre-defined
cycle of colors (you can change the default "cycler," or just include the
color parameter to plot). But either way, there's nothing in the above
diagram that tells the user which line is for movie and which line is for
tvSeries. We need to add a legend to the graph to clear that up. We'll
see how to do that in the next section. However, since we added movies
first and tvSeries second, the default color cycle means the blue line is
movies and the orange line is TV series. You'll notice the TV series line
is a little broken. That's because there were no TV series for those
years, and we ended up with NaN (Not a Number) values in those rows.
If you want to fix that as part of data cleansing, you can replace the NaN
values with 0s.
Another way to plot multiple lines is to supply additional pairs of X and Y
series to the call to plot:
[Link](df_wide['startYear'], df_wide['movie'], df_wide['startYear'],
df_wide['tvSeries'])
This produces the same plot, but I think it's messier and harder to read
than just calling plot twice.
As a side note, you might be wondering why there were any TV series at
all in the 1920s and 1930s when TV hadn't been invented yet. Well, here
are all of the TV series listed before 1930:

tcons titleT primaryTitle originalTitle isA start end runtimeM

t ype dult Year Year inutes

0 tt5987 tvSer Palm Beach Palm Beach Fals 1897 NaN NaN
568 ies Daily News Daily News e

1 tt4993 tvSer Urban Legends Urban Legends Fals 1905 NaN NaN
824 ies e

2 tt4046 tvSer British Pathé British Pathé Fals 1910 1970 NaN
852 ies News News e .0

3 tt6964 tvSer A Paul Terry A Paul Terry Fals 1914 NaN 7.0
140 ies Cartoon Cartoon e

4 tt5553 tvSer FoxyTv FoxyTv Fals 1916 NaN 15.0

344 ies e

5 tt7431 tvSer Aesop's fables Aesop's fables Fals 1921 1933 7.0
484 ies e .0

6 tt6352 tvSer Aesop's Fables Aesop's Fables Fals 1921 1933 7.0
818 ies e .0

7 tt5983 tvSer WSB News WSB News Fals 1922 NaN NaN
102 ies e

8 tt6352 tvSer Alice Comedies Alice Comedies Fals 1923 1927 6.0
816 ies e .0

9 tt7431 tvSer Alice comedies Alice comedies Fals 1923 1927 6.0
 464 ies e .0

1 tt6289 tvSer Castle Film Castle Film Fals 1924 1924 9.0
0 264 ies e .0

1 tt0230 tvSer From Sparks: Iz iskry plamya Fals 1924 NaN NaN
1 344 ies Flames e

1 tt6964 tvSer Oswald the Oswald the Fals 1927 NaN 6.0
2 138 ies Lucky Rabbit Lucky Rabbit e

1 tt7051 tvSer US Presidential US Presidential Fals 1928 NaN NaN

3 618 ies Elections Elections e

1 tt4048 tvSer British British Fals 1929 1979 NaN

4 740 ies Movietone Movietone e .0
News News

Most of these seem to be short serial films that were shown alongside
films in movie theaters. I'm guessing that rather than create a category
for movieSerials, the data engineers at IMDB just decided to go with
tvSeries as the nearest available category. The more you work with data,
the more you appreciate these weird little exceptional cases.

Bar charts:
Line charts are good for plotting series of datapoints with two numerical
values that can be translated into an X coordinate and a Y coordinate.
But sometimes, you want to plot a numerical value against a non-
numerical value. For example, if you want to know the number of records
in the title_basics table for each type of show. Type of show is not a
numeric quantity. Rather, it's a category. Bar charts are ideally suited for
working with different categories of data. For example, here's the code to
produce a bar chart with the number of shows for each title type:
sql = """
 SELECT RTRIM(titleType) AS titleType, COUNT(*) AS count

FROM title_basics

GROUP BY titleType

ORDER BY COUNT(*) DESC;

"""

df = pd.read_sql(sql, connection)

[Link](df['titleType'], df['count'])

[Link]()

The bar method takes an X series and a Y series, just like the plot
method, though some of the optional parameters are different because
we're dealing with bars instead of lines. Here's the resulting graph:

Figure 5.6: Number of records for each type of show

The code is simple, but there are a couple of problems with the default
settings. First, the number of shows is large (over 3,000,000 for
tvEpisodes), so matplotlib has automatically switched to scientific
notation for the Y axis (it shows 1e6 at the top of the Y axis, so a Y value
of 3.0 would actually be 3.0x106, or 3,000,000). Second, there are too
many bars on too small of a graph, so the X labels overlap and are
unreadable. Of course, we could adjust the size of the graph, but we can
also change the plot code a bit to avoid both problems:
sql = """df = pd.read_sql(sql, connection)

[Link](df['titleType'], df['count'])

# This sets the y-axis labels to "plain" format, which prevents it from
using scientific notation.

plt.ticklabel_format(style='plain', axis='y')

# This rotates the x tick labels by 45 degrees, so they don't overlap.

[Link](rotation=45)

[Link]()

Here's the fixed version:

Figure 5.7: Number of records for each type of show

I've rotated the x labels by 45 degrees, but you can also use 90,
depending on whichever you think looks best. Of course, another
solution for the tick labels is to use a horizontal bar chart, which uses the
barh method:
sql = """

SELECT RTRIM(titleType) AS titleType, COUNT(*) AS count

FROM title_basics
 GROUP BY titleType

ORDER BY COUNT(*) DESC;

"""

df = pd.read_sql(sql, connection)

[Link](df['titleType'], df['count'])

# Prevent scientific notation on the X axis this time

plt.ticklabel_format(style='plain', axis='x')

[Link]()

And here's the horizontal bar chart:

Figure 5.8: Number of records for each type of show - horizontal bars
Note that the first parameter to barh is still the titleType, and the second
parameter is still the count, even though titleType is now on the Y axis
and count is now on the X axis.
Generally, if I'm ordering the results by count, popularity, cost, or
something similar, I prefer to have the longest bar at the top rather than
the bottom. To clean that up, I can add a call to sort_values:
sql = """

SELECT RTRIM(titleType) AS titleType, COUNT(*) AS count

FROM title_basics
 GROUP BY titleType

ORDER BY COUNT(*) DESC;

"""

df = pd.read_sql(sql, connection)

# Reverse the order of the rows. We could also change the ORDER BY in the

# SQL statement, but that's only because we're not using TOP. If we were
using TOP,

# we'd end up with the titleTypes with the smallest counts instead of the
largest counts,

# which is probably not what we want. So, we could either write a

significantly more

# complicated SQL statement, or we can just toss in one line of pandas

code.

df = df.sort_values('count', ascending=True)

[Link](df['titleType'], df['count'])

plt.ticklabel_format(style='plain', axis='x')

[Link]()

Here's the corrected plot:

 Figure 5.9: Number of records for each type of show - horizontal bars, highest on top
What if we want to do something a little more complicated, like compare
the number of shows for each of the different title types before 1980 to
the numbers on or after 1980? That would be similar to plotting two lines
on a line chart. Let's try doing that by calling bar twice:
# Collect the data in long format

sql = """

SELECT RTRIM(titleType) AS titleType, COUNT(*) AS count, 'before' AS

"Before or After 1980"

FROM title_basics

WHERE startYear < 1980

GROUP BY titleType

UNION ALL

SELECT RTRIM(titleType) AS titleType, COUNT(*) AS count, 'after' AS

"Before or After 1980"

FROM title_basics

WHERE startYear >= 1980

GROUP BY titleType

"""

# Convert it to wide format and sort it

df = pd.read_sql(sql, connection)

df = [Link](index='titleType', columns='Before or After 1980',

values='count')

df = df.reset_index()

df = df.sort_values('after', ascending=False)

# Plot on or after 1980 and before 1980

[Link](df['titleType'], df['after'])

[Link](df['titleType'], df['before'])
# This sets the y-axis labels to "plain" format, which prevents it from
using scientific notation.

plt.ticklabel_format(style='plain', axis='y')

# Prevent the labels from overlapping

[Link](rotation=45)

[Link]()

Here's the plot:

Figure 5.10: Number of shows per title type before 1980 and after 1980
This almost worked, but it drew the bars on top of each other. I ordered
them to draw the "on or after 1980" bars first, since those are taller than
the "before 1980" bars, because otherwise, the "before 1980" bars would
be completely covered up. What we need to do to fix this is make the
bars about half as wide and then offset their positions a little bit so
they're drawn next to each other. This is easier than it sounds:
# Collect the data in long form

sql = """

SELECT RTRIM(titleType) AS titleType, COUNT(*) AS count, 'before' AS

"Before or After 1980"

FROM title_basics
 WHERE startYear < 1980

GROUP BY titleType

UNION ALL

SELECT RTRIM(titleType) AS titleType, COUNT(*) AS count, 'after' AS

"Before or After 1980"

FROM title_basics

WHERE startYear >= 1980

GROUP BY titleType

"""

# Convert it to wide form and sort

df = pd.read_sql(sql, connection)

df = [Link](index='titleType', columns='Before or After 1980',

values='count')

df = df.reset_index()

df = df.sort_values('after', ascending=False)

# Draw each bar in about half the width, and offset them so that they're

# next to each other instead of on top of each other. We have to supply

# positions for the X coordinates instead ot titleType labels.

[Link]([i - 0.2 for i in range(len(df))], df['after'], width=0.4)

[Link]([i + 0.2 for i in range(len(df))], df['before'], width=0.4)

# This sets the y-axis labels to "plain" format, which prevents it from
using scientific notation.

plt.ticklabel_format(style='plain', axis='y')

# Prevent the labels from overlapping

# Now we can put the tick labels and positions here because we had to use

# positions in the calls to [Link] above.

[Link](labels=df['titleType'], ticks=[i for i in range(len(df))],
rotation=45)

[Link]()

And here's the corrected plot:

Figure 5.11: Number of shows per title type before 1980 and after 1980

Pine Plots:
A pie plot might be a good alternative to a bar chart when you have a
small number of categories. For some people, the size of a wedge of pie
is easier to compare visually than two bars. Here's the distribution of
titleTypes for the five most common titleTypes (any more, and the labels
start to jam together):
sql = """

SELECT TOP 5 RTRIM(titleType) AS titleType, COUNT(*) AS count

FROM title_basics

GROUP BY titleType

ORDER BY COUNT(*) DESC;

"""
df = pd.read_sql(sql, connection)

# Reverse the order of the rows. We could also change the ORDER BY in the

# SQL statement, but that's only because we're not using TOP. If we were
using TOP,

# we'd end up with the titleTypes with the smallest counts instead of the
largest counts,

# which is probably not what we want. So, we could either write a

significantly more

# complicated SQL statement, or we can just toss in one line of pandas

code.

df = df.sort_values('count', ascending=True)

[Link](df['count'], labels=df['titleType'])

[Link]()

Note that the counts are supplied as a single dimension, and then the
categories are filled in as an optional label parameter. And here's the
plot itself:

Figure 5.12: Percentage of records per TV category type

You can also specify how much you want to "explode" each section out
to draw attention to it:
[Link](df['count'], labels=df['titleType'], explode=[0, 0, .25, 0, 0])
Figure 5.13: Percentage of records per TV category type with pop out

Histograms:
If you're interested in the distribution of values in a numeric column, it's very easy to
create a histogram:
sql = """
SELECT startYear
FROM title_basics
WHERE startYear BETWEEN 1920 AND 2017;
"""

df = pd.read_sql(sql, connection)

[Link](df['startYear'])
plt.ticklabel_format(style='plain', axis='y')
[Link]()
Here's the resulting histogram:
 Figure 5.14: Distribution of categories per year in ten bins
The values (startYear in this case) are split into ranges, called "bins." By default, you
get ten equally spaced bins, so if the data ranges from 1920 through 2017, you will end
up with about 9.3 years per bin. However, you can adjust this by specifying a bin’s
parameter. If bins=<an integer>, then that will be the number of bins. If bins=<a list of
numbers>, the numbers specify the cut points between bins. For example:
[Link](df['startYear'], bins=[1920, 1960, 2000, 2010, 2017])
creates 5 bins, from 1920 up to but not including 1960, from 1960 up to but not including
2000, from 2000 up to but not including 2010, and from 2010 up to and including 2017.
Here's the result:

Figure 5.15: Distribution of categories per year in five bins

Scatter plots:
Scatter plots are like line plots in that both types of graphs show the relationship
between two numerical values. However, line plots are best suited for data where the X
values are in numerical order, and there is only one value of Y for each distinct value of
X. On the other hand, scatter plots are better for showing weaker relationships between
two numeric quantities. Like line plots, scatter plots can be created by specifying a
series of X values and a series of Y values. But scatter plots only show a marker for
each point, and the markers are not connected in any particular order. For example,
here’s the code to generate a scatter plot with two separate relationships - the
relationship between the number of Sci-Fi shows and the number of Westerns for each
year from 1920-2017, and the relationship between the number of Sci-Fi shows and the
number of Fantasy shows over the same period:
# Read the data in long form
sql = """
SELECT startYear, genre, COUNT(*) AS count
FROM title_basics
JOIN title_genre ON title_basics.tconst = title_genre.tconst
WHERE startYear BETWEEN 1920 AND 2017
GROUP BY startYear, genre;
"""

df = pd.read_sql(sql, connection)

# Convert to wide form

df = [Link](index='startYear', columns='genre',
values='count').reset_index()

[Link](figsize=(12, 8), dpi=72)

[Link](df['Western'], df['Sci-Fi'], s=7)
[Link](df['Fantasy'], df['Sci-Fi'], s=7)
[Link]()
Here is the graph:
 Figure 5.16: Relationship of Sci-Fi and Westerns
The blue dots show the relationship between Sci-Fi and Westerns, with Sci-Fi on the Y
axis and Westerns on the X axis. When there are a lot of Westerns, there are very few
Sci-Fi shows, and when there are a lot of Sci-Fi shows, there are very few Westerns.
However, there are a number of years where there are not many of either type of show.
This makes sense because Westerns were popular until about the 1960s, and then Sci-
Fi shows started to gain popularity in the late 1970s to early 1980s. So, the blue data
forms an L shape.

On the other hand, the orange data shows the relationship between Sci-Fi and Fantasy.
When Sci-Fi is popular, Fantasy is as well, and when Sci-Fi is unpopular, then Fantasy
is as well. This shows a high degree of correlation between the popularity of the two
genres.

Adding text to plots

There are several types of text you can add to your plot to make it easier
to understand:
● You can add a title with [Link]("Title Goes Here").
● You can add a label to the X axis with [Link]("Label Goes
Here").
● Similarly, you can add a label to the Y axis with [Link]("Label
Goes Here").
● You can add a legend to a plot that has more than one set of data
on it with, for example, [Link](["Name of first series", "Name of
second series"]).
● You can add a piece of text as an annotation at a particular
location with [Link](x_location, y_location, "Annotation Text").
● You can also add an annotation with an arrow that points at
something on your plot with, for example, [Link]('local max',
xy=(2, 1), xytext=(3, 1.5), arrowprops={facecolor: 'black', shrink:
0.05})
Here's an example with all of those texts added:
sql = """
SELECT startYear, COUNT(*) AS Count
FROM title_basics
WHERE titleType = 'movie'
AND startYear BETWEEN 1920 AND 2017
GROUP BY startYear
ORDER BY startYear;
"""

df = pd.read_sql(sql, connection)
df = df[(df['startYear'].notnull()) & (df['startYear'] >= 1920) &
(df['startYear'] <= 2017)].sort_values("startYear")
[Link](figsize=(12, 8), dpi=72)
[Link]("startYear", "Count", data=df, marker='o')
[Link](range(1920, 2020, 10))
[Link]("Movies Released per Year", fontsize=16)
[Link]("Year", fontsize=14)
[Link]("Number of Movies", fontsize=14)
[Link]("2003, WWW starting\n to take off.", xytext=(1980,
7000), xy=(2003, 5500),
arrowprops={'facecolor': 'black', 'width': 1, 'headwidth': 7,
'shrink': 0.05})
[Link]("1995, Digital Video\n standard set.", xytext=(2000,
1500), xy=(1995, 3500),
 arrowprops={'facecolor': 'black', 'width': 1, 'headwidth': 7,
'shrink': 0.05})
[Link]()

Figure 5.17: Movies released per year with text on chart

Adding gridlines
Adding gridlines to your plot can often help the viewer understand the
data, particularly when reading specific quantities from the axes.
Gridlines can be enabled by calling [Link](True). By default, you'll get
gridlines in both X and Y directions, and you'll get one gridline for each of
the ticks on the X and Y axes. For example:
 Figure 5.18: Movies released per year with text on chart and gridlines
As with lines on line plots, you can adjust the line width, line color, line
style, and so on. You can also choose to have grid lines in only the X
direction, only the Y direction, or both. In particular, you probably don't
want to have X gridlines for bar plots, but Y gridlines are handy:
[Link](True, axis='y', linestyle='dotted', color='green')
Results in:
 Figure 5.19: Bar chart with horizontal green dotted lines for axis

xkcd
If you haven't read xkcd, check it out. You might like it! Now, matplotlib
has the ability to style your plots to look like xkcd. Why? That's the wrong
question. Do or do not, there is no why.
# Step 1: import our packages

import pandas as pd
import pymssql
import [Link] as plt

# Step 2: fetch the data and prepare it for plotting

name_connection = [Link](
server='[Link]',
database='NAMES',
user='275student',
password='275student'
)

sql = """
SELECT Year, Name, Gender, NameCount
FROM all_data
WHERE Name = 'Lynn';
"""

lynn_data_long = pd.read_sql(sql, name_connection)

lynn_data_wide = lynn_data_long.pivot(index=["Year", "Name"],
columns="Gender", values="NameCount")
lynn_wide = lynn_data_wide.reset_index()
lynn_wide.[Link] = None

# Step 3: plot the data

with [Link]():
[Link](figsize=(12, 8), dpi=72)
[Link]('Year', 'F', data=lynn_wide)
[Link]('Year', 'M', data=lynn_wide)

# Step 4: add plot details

[Link]()
[Link]("Popularity of the Name Lynn")
[Link]("Year")
[Link]("Number of Babies Named Lynn")
[Link](['Female Babies', 'Male Babies'])

# Step 5: show the plot

[Link]()

# For a second graph, we've already imported our packages and fetched
the data.
# We just need to do a little more preparation this time.

# Step 2: prepare the data

lynn_wide['Percent Female'] = 100 * lynn_wide['F'] / (lynn_wide['F'] +

lynn_wide['M'])

# Step 3: plot the data

[Link](figsize=(12, 8), dpi=72)

[Link]('Year', 'Percent Female', data=lynn_wide)

# Step 4: add plot details

[Link]()
[Link]("Is Lynn a Female Name or a Male Name?")
[Link]("Year")
[Link]("Percent of Babies Named Lynn Who Were Female")

# Step 5: show the plot

[Link]()

Figure 5.20: Number of babies named Lynn per year

Figure 5.21: Percent of female babies named Lynn per year