CS109A Introduction to Data Science

Lecture 3 (Pandas + Beautiful Soup)¶

Harvard University
Fall 2019
Instructors: Pavlos Protopapas, Kevin Rader, and Chris Tanner
Authors: Rahul Dave, David Sondak, Will Claybaugh, Pavlos Protopapas, Chris Tanner

Table of Contents¶

0. Learning Goals
1. Loading and Cleaning with Pandas
2. Parsing and Completing the Dataframe
3. Grouping

Learning Goals¶

This Jupyter notebook accompanies Lecture 3. By the end of this lecture, you should be able to:

• Understand why and how Pandas can be useful.
• Use Pandas to:
  • Load data into a DataFrame
  • Access subsets of data based on column and row values
  • Address missing values (e.g., NaN)
  • Use groupby() to select sections of data.
  • Plot DataFrames (e.g., barplot())
• Use Beautiful Soup to download a webpage and all of its links

Part 1: Processing Data without Pandas¶

../data/top50.csv is a dataset found online (Kaggle.com) that contains information about the 50 most popular songs on Spotify in 2019.

Each row represents a distinct song. The columns (in order) are:

ID: a unique ID (i.e., 1-50)
TrackName: Name of the Track
ArtistName: Name of the Artist
Genre: the genre of the track
BeatsPerMinute: The tempo of the song.
Energy: The energy of a song - the higher the value, the more energetic. song
Danceability: The higher the value, the easier it is to dance to this song.
Loudness: The higher the value, the louder the song.
Liveness: The higher the value, the more likely the song is a live recording.
Valence: The higher the value, the more positive mood for the song.
Length: The duration of the song (in seconds).
Acousticness: The higher the value, the more acoustic the song is.
Speechiness: The higher the value, the more spoken words the song contains.
Popularity: The higher the value, the more popular the song is.

Read and store ../data/top50.csv¶

Q1: Read in the ../data/top50.csv file and store all of its contents into any data structure(s) that make the most sense to you, keeping in mind that you'd want to easily access any row or column. What data structure(s) make the most sense to use?

Q2: Write code to print all songs (Artist and Track name) that are longer than 4 minutes (240 seconds):

Q3: Write code to print the most popular song (or song(s) if there is a tie):

Q4: Write code to print the songs (and their attributes), if we sorted by their popularity (highest scoring ones first).

Q5: How could you check for null/empty entries? This is only 50 entries. Imagine if we had 500,000.

Often times, one dataset doesn't contain all of the information you are interested in -- in which case, you need to combine data from multiple files.

Q6: Imagine we had another table (i.e., .csv file) below. How could we combine its data with our already-existing dataset?

Part 2: Processing Data with Pandas¶

Pandas is an open-source Python library designed for data analysis and processing. Being open-sourced means that anyone can contribute to it (don't worry, a team of people vett all official updates to the library). Pandas allows for high-performance, easy-to-use data structures. Namely, instead of using N-dimensional arrays like NumPy (which are extremely fast, though), Pandas provides a 2D-table object calleda DataFrame.

As a very gross simplification: NumPy is great for performing math operations with matrices, whereas Pandas is excellent for wrangling, processing, and understanding 2D data like spreadsheets (2D data like spreadsheets is very common and great).

Let's get started with simple examples of how to use Pandas. We will continue with our top50.csv Spotify music data.

First, we need to import pandas so that we have access to it. For typing convenience, we choose to rename it as pd, which is common practice.

Reading in the data¶

Pandas allows us to read in various structured files (e.g., .csv, .json, .html, etc) with just one line:

High-level view of the data¶

We can view the data frame by simply printing it:

Instead of printing the entire dataframe (e.g., print(top50)), we can also inspect the file by looking at just the first N rows or last N rows.

That's cool, but we can't see all of the columns too well. To get a list of all columns:

Remember, Pandas is in Python, so you can write regular Python code with it. For example, if you want to know how many columns there are, you can use Python's len() function.

Fortunately, many of the features in our dataset are numeric. Conveniently, Pandas' describe() function calculates basic statistics for our columns. It's pretty amazing, as it allows us a very coarse-grain approach to understanding our data and checking for errors. That is, if we notice any summary statistics that are drastically different than what we deem reasonable, we should dive deeper and figure out why the values are what they are.

Notice, it calculated statistics only for the columns that are of numeric data types. What about the textual ones (e.g., Track name and Artist)? Pandas is smart enough to infer the data types. Don't forget to inspect the columns that are text-based though, as we need to ensure they are sound, too.

To view the data type of each column:

Exploring the data¶

I agree with Pandas' handling of the data. If any column contained floating point numbers, we would expect to see such here, too.

Now that we've viewed our dataset at a high-level, let's actually use and explore it.

We can access a column of data the same way we access dictionary by its keys:

We could have also used this syntax (identical results):

If we want just the highest or lowest value of a given column, we can use the functions max() and min(), respectively.

If we want the row index that corresponds to a column's max or min value, we can use idxmax() and idxmin(), respectively.

We can also add conditional statements (e.g., >, <, ==) for columns, which yields a boolean vector:

This is useful, as it allows us to process only the rows with the True values.

The loc() function allows us to access data via labels:

• A single scalar label
• A list of labels
• A slice object
• A Boolean array

A single scalar:

Note, this returns a DataFrame. Everything we've learned so far concerns how to use DataFrames, so we can tack on additional syntax to this command if we wish to do further processing.

For example, if we want to index just select columns (e.g., ArtistName, TrackName, and Length) of this returned DataFrame:

Note, the above solves our original Q2: (Write code to print all songs (Artist and Track name) that are longer than 4 minutes (240 seconds))

Q3: Write code to print the most popular song (or song(s) if there is a tie):

We can also sort our data by a single column! This pertains to our original Q4!

Q4: Write code to print the songs (and their attributes), if we sorted by their popularity (highest scoring ones first).

While .loc() allows us to index based on passed-in labels, .iloc() allows us to access data based on 0-based indices.

The syntax is .iloc[, ], where and can be scalars, lists, or slices of indices.

Inspecting/cleaning the data¶

As mentioned, it is imperative to ensure the data is sound to use:

1. Did it come from a trustworthy, authoritative source?
2. Is the data a complete sample?
3. Does the data seem correct?
4. (optional) Is the data stored efficiently or does it have redundancies?

Let's walk through each of these points now:

1. Did it come from a trustworthy, authoritative source?

The data came from Kaggle.com, which anyone can publish to. However, the author claims that he/she used Spotify.com's official API to query songs in 2019. There are no public comments for it so far. It's potentially credible.

1. Is the data a complete sample?

Pandas has functions named isnull() and notnull(), which return DataFrames corresponding to any null or non-null entries, respectively.

For example:

If we run this for all of our features/columns, we will see there are no nulls. Since this dataset is manageable in size, you can also just scroll through it and notice no nulls.

This answers our original Q5:

Q5: How could you check for null/empty entries?

Continuing with our data sanity check list:

1. Does the data seem correct?

A quick scroll through the data, and we see a song by Maluma titled 0.95833333. This is possibly a song about probability, but I think the chances are slim. The song is 176 seconds long (2m56s). Looking on Spotify, we see Maluma's most popular song is currently 11PM which is 2m56s in length! Somehow, during the creation of the dataset, 11PM became 0.95833333. Bonus points if you can figure out where this pointing number could have come from.

Since only one song seems obviously wrong, we can manually fix it. And it's worth noting such to ourselves and to whomever else would see our results or receive a copy of our data. If there were many more wrong values, we'd potentialy not fix them, as we'd explore other options.

1. (optional) Is the data stored efficiently or does it have redundancies?

Everything seems fine. No repeated data.

Combining multiple DataFrames¶

As mentioned, often times one dataset doesn't contain all of the information you are interested in -- in which case, you need to combine data from multiple files. This also means you need to verify the accuracy (per above) of each dataset.

Pandas' groupby() function splits the DataFrame into different groups, depending on the passed-in variable. For example, we can group our data by the genres:

../data/spotify_aux.csv contains the same 50 songs as top50.csv; however, it only contains 3 columns:

• Track Name
• Artist Name
• Explicit Language (boolean valued)

Note, that 3rd column is just random values, but pretend as if it's correct. The point of this section is to demonstrate how to merge columns together.

Let's load ../data/spotify_aux.csv into a DataFrame:

Let's merge it with our top50 DataFrame.

.merge() is a Pandas function that stitches together DataFrames by their columns.

.concat() is a Pandas function that stitches together DataFrames by their rows (if you pass axis=1 as a flag, it will be column-based)

We see that all columns from both DataFrames have been added. That's nice, but having duplicate ArtistName and TrackName is unecessary. Since merge() uses DataFrames as the passed-in objects, we can simply pass merge() a stripped-down copy of ExplicitLanguage, which helps merge() not add any redundant fields.

This answers our original Q6:

Q6: Imagine we had another table (i.e., .csv file) below. How could we combine its data with our already-existing dataset?

While we do not exhaustively illustrate Pandas' joining/splitting functionality, you may find the following functions useful:

• merge()
• concat()
• aggregate()
• append()

Plotting DataFrames¶

As a very simple example of how one can plot elements of a DataFrame, we turn to Pandas' built-in plotting:

This shows the lack of a correlation between the Danceability of a song and its popularity, based on just the top 50 songs, of course.

Please feel free to experiment with plotting other items of interest, and we recommend using Seaborn.

Practice Problems with a Partner (or individually if you prefer)¶

P1. Print the shortest song (all features):

P2. Print the 5 shortest songs (all features):

P3. What is the average length of the 5 shortest songs?

P4. Write a function that accepts a DataFrame as an input and returns True if there exists any null values in the DataSet. Otherwise, returns False. Pass top50 to the function in order to test it.

P5. How many distinct genres are present in the top 50 songs?

P6. Print the songs that have a Danceability score above 80 and a popularity above 86. HINT: you can combine conditional statements with the & operator, and each item must be surrounded with ( ) brackets.

P7. Print the songs that are faster than the average Top 50 and more popular than the average Top 50?

P8. Plot a histogram of the Genre counts (x-axis is the Genres, y-axis is the # of songs with that Genre)

P9. (open ended) Think of a subset of the data that you're interested in. Think of an interesting plot that could be shown to illustrate that data. With a partner, discuss whose would be easier to create. Together, create that plot. Then, try to create the harder plot.