Midterm Exam II

Spring 2025, DANL 210-01

Descriptive Statistics

The distribution of scores for Midterm Exam II is shown below:

The distribution of raw scores for Data Collection in Midterm Exam II is shown below:

The distribution of raw scores for Pandas in Midterm Exam II is shown below:

The following provides the descriptive statistics for each part of the Midterm Exam II:

Section 1. Data Collection I

# %%
# =============================================================================
# Setting up
# =============================================================================
import pandas as pd
import os
import time
import random
from selenium import webdriver
from selenium.webdriver.common.by import By

driver = webdriver.Chrome() 

# Set the working directory path
wd_path = '/Users/bchoe/My Drive/suny-geneseo/spring2025/lecture-code'
os.chdir(wd_path)  

url = 'http://quotes.toscrape.com/tableful/'

First/Front page of the website for the url:

Last page of the website for the url:

A single quote with its author

Corresponding tags

Question 1 (Points: 4)

• Write pandas code that reads the HTML table from the front page of the website URL stored in url into a DataFrame called df.

df = pd.read_html(url)[0]

Question 2 (Points: 4)

• Write code to load the first/front page of the website for the url on the Chrome browser that is being controlled by automated test software, called selenium.

driver.get(url)

Question 3 (Points: 20)

• Write Python code to scrape every quote from a paginated website (unknown number of pages, 10 quotes per page) by repeatedly clicking the Next button until it disappears.
  • On each page, extract each quote and its author into a column quote_author and its associated tags into tags, appending them to a DataFrame named df_clean.
  • After loading each page, pause execution for a random 1–2 second interval.
  • Examine below XPath examples for quote_author (rows 2, 4, 6, …) and tags (rows 3, 5, 7, …), identify that pattern of even vs. odd row numbers, and use it to build your f-strings for locating each element.

# Table body - quote with author
xpath_quote_author_01 = '/html/body/div/table/tbody/tr[2]/td'
xpath_quote_author_02 = '/html/body/div/table/tbody/tr[4]/td'
xpath_quote_author_03 = '/html/body/div/table/tbody/tr[6]/td'
xpath_quote_author_04 = '/html/body/div/table/tbody/tr[8]/td'
xpath_quote_author_05 = '/html/body/div/table/tbody/tr[10]/td'
xpath_quote_author_06 = '/html/body/div/table/tbody/tr[12]/td'
xpath_quote_author_07 = '/html/body/div/table/tbody/tr[14]/td'
xpath_quote_author_08 = '/html/body/div/table/tbody/tr[16]/td'
xpath_quote_author_09 = '/html/body/div/table/tbody/tr[18]/td'
xpath_quote_author_10 = '/html/body/div/table/tbody/tr[20]/td'

# Table body - tags
xpath_tags_01 = '/html/body/div/table/tbody/tr[3]/td'
xpath_tags_02 = '/html/body/div/table/tbody/tr[5]/td'
xpath_tags_03 = '/html/body/div/table/tbody/tr[7]/td'
xpath_tags_04 = '/html/body/div/table/tbody/tr[9]/td'
xpath_tags_05 = '/html/body/div/table/tbody/tr[11]/td'
xpath_tags_06 = '/html/body/div/table/tbody/tr[13]/td'
xpath_tags_07 = '/html/body/div/table/tbody/tr[15]/td'
xpath_tags_08 = '/html/body/div/table/tbody/tr[17]/td'
xpath_tags_09 = '/html/body/div/table/tbody/tr[19]/td'
xpath_tags_10 = '/html/body/div/table/tbody/tr[21]/td'

df_clean = pd.DataFrame()
while True:
    
    try:
        btn = driver.find_element(By.PARTIAL_LINK_TEXT, "Next")
    except:
        btn = []

    for i in range(1,11):
        j = i*2
        k = i*2+1
        
        xpath_quote_author = f'/html/body/div/table/tbody/tr[{j}]/td'
        xpath_tags = f'/html/body/div/table/tbody/tr[{k}]/td'
        
        quote_author = driver.find_element(By.XPATH, xpath_quote_author).text
        tags = driver.find_element(By.XPATH, xpath_tags).text
        
        lst = [quote_author, tags]
        obs = pd.DataFrame([lst])
        df_clean = pd.concat([df_clean, obs])
    
    if btn != []:
        btn.click()
        time.sleep(random.uniform(1,2))
    else:
        break
      
df_clean.columns = ['quote_author', 'tags']

Question 4 (Points: 4)

• Write one-line code to export the df_clean DataFrame as a CSV file named table_quotes.csv inside the data subfolder of the current working directory given by wd_path.
  • Ensure that the CSV does not include row index of the df_clean DataFrame.

df_clean.to_csv('data/quotes_table.csv', index=False, encoding = 'utf-8-sig')  

Question 5 (Points: 4)

• Write a one-line code to quit the Chrome browser that is being controlled by automated test software, called selenium.

driver.quit()

Section 2. Data Collection II

Question 6 (Points: 4)

• In the client–server model of the web, which statement is true?

1. A client hosts webpages, and a server displays them to users.
2. A client requests data, and a server responds with data.
3. Clients and servers are the same machine communicating via HTTPS.
4. A server initiates requests, and a client responds with data.

b. A client requests data, and a server responds with data.

Question 7 (Points: 10)

• Fill in the following 5 blanks to make a request to the FRED API for collecting the U.S. unemployment rate (series_id = “UNRATE”)

import requests
import pandas as pd
param_dicts = {
  'api_key': 'YOUR_FRED_API_KEY', ## Change to your own key
  'file_type': 'json',
  'series_id': ___BLANK_1___    ## ID for series data from the FRED
}
api_endpoint = "https://api.stlouisfed.org/fred/series/observations"
response = ___BLANK_2___

# Check if the server successfully sends the requested content
if ___BLANK_3___:
    # Convert JSON response to Python dictionary.
    content = ___BLANK_4___

    # Create a DataFrame of key 'observations's value from the content dictionary
    df = pd.DataFrame( ___BLANK_5___ )
else:
    print("The server cannot find the requested content")

key-value pairs in the content dictionary

Answer for ___BLANK_1___:

"UNRATE"

Answer for ___BLANK_2___:

requests.get(api_endpoint, params=param_dicts)

Answer for ___BLANK_3___:

response.status_code == 200

Answer for ___BLANK_4___:

response.json()

Answer for ___BLANK_5___:

content['observations']

Section 3. Pandas Basics I

import pandas as pd
import numpy as np

Below is spotify DataFrame that reads the file spotify_all.csv containing data of Spotify users’ playlist information (Source: Spotify Million Playlist Dataset Challenge).

spotify = pd.read_csv('https://bcdanl.github.io/data/spotify_all.csv')

Variable Description

• pid: playlist ID; unique ID for playlist
• playlist_name: a name of playlist
• pos: a position of the track within a playlist (starting from 0)
• artist_name: name of the track’s primary artist
• track_name: name of the track
• duration_ms: duration of the track in milliseconds

Definition of a Song

• In Section 1, a song is defined as a combination of a artist_name value and a track_name value.

• E.g., the following provides the 12 distinct songs with the same track_name—I Love You:

Index	artist_name	track_name
0	Alex & Sierra	I Love You
1	Alex Clare	I Love You
2	Axwell /\ Ingrosso	I Love You
3	Beat Happening	I Love You
4	Climax Blues Band	I Love You
5	Céline Dion	I Love You
6	Donna Summer	I Love You
7	Eli Young Band	I Love You
8	Faith Evans	I Love You
9	Mary J. Blige	I Love You
10	OMFG	I Love You
11	Said The Whale	I Love You

Question 8 (Points: 4)

• Complete the following line so that num_columns is assigned the total number of columns in the spotify DataFrame as an integer:

num_columns = spotify._______________________________________________

num_columns = spotify.shape[1]

Question 9 (Points: 4)

• Write code to count how many tracks each playlist contains.

spotify[['pid', 'playlist_name']].value_counts().reset_index()

Question 10 (Points: 5)

• Write code to identify playlists that include at least 100 different artists.

plist_100 = (
 spotify[['pid', 'playlist_name', 'artist_name']]
 .drop_duplicates()
 .value_counts()
 .reset_index()[['pid', 'playlist_name']]
 .value_counts()
 .reset_index()
 .query('count >= 100')
 )

Question 11 (Points: 5)

• Write code to calculate the proportion of playlists in spotify that contain at least one duplicated song, i.e. (the number of playlists with at least 1 duplicate song) divided by (the total number of distinct playlists)

dup_T = spotify.duplicated(subset=["pid", "artist_name", "track_name"])
duplicate_plist = (
    spotify[ dup_T ]
    .drop_duplicates(subset = ['pid', 'playlist_name'])
    )


all_plists = (
    spotify
    .drop_duplicates(subset = ['pid', 'playlist_name'])
    )
    
duplicate_plist.shape[0] / all_plist.shape[0]

Question 12 (Points: 6)

• Write code to add a new column, duration_cat, to the spotify DataFrame that categorizes duration_ms into:
  • “short” for tracks under 3 minutes (<180000 ms)
  • “medium” for tracks between 3 and 5 minutes (180000–300000 ms)
  • “long” for tracks over 5 minutes (>300000 ms)

spotify['duration_cat'] = np.where(spotify['duration_ms'] < 180*1000,
                                   "short", 
                                   "")

spotify['duration_cat'] = np.where(spotify['duration_ms'] > 300*1000,
                                   "long", 
                                   spotify['duration_cat'])

spotify['duration_cat'] = np.where(spotify['duration_cat'] == '',
                                   "medium", 
                                   spotify['duration_cat'])

Question 13 (Points: 7)

• Write code to find the second most frequently appearing song(s) across all playlists that include “Viva La Vida” (track_name) by “Coldplay” (artist_name).

coldplay_list = (
    spotify.query('artist_name == "Coldplay" & track_name == "Viva La Vida"')
    )

after_coldplay = (
    spotify[ spotify['pid'].isin(coldplay_list['pid']) ]
    [ ['artist_name', 'track_name'] ]
    .value_counts()
    .reset_index()
    .nlargest(2, 'count', keep="all")
    )

Question 14 (Points: 7)

• Consider a DataFrame called songs, derived from spotify, that adds a new variable song by concatenating each row’s artist_name and track_name:

Index	pid	playlist_name	song
0	0	Throwbacks	Missy Elliott - Lose Control (feat. Ciara & Fat Man Scoop)
1	0	Throwbacks	Britney Spears - Toxic
2	0	Throwbacks	Beyoncé - Crazy In Love
		.....

• Note that the songs DataFrame has only the three variables—pid, playlist_name, and song, as shown above.

• Write code to determine which two different songs appear together in playlists more often than any other pair, as shown below.

  • (Hint: self‑merge the songs DataFrame on pid to get song_x-song_y pairs, which is a many-to-many join.)

Index	song_x	song_y	count
76	Kendrick Lamar – HUMBLE.	Lil Uzi Vert – XO TOUR Llif3	67

Below is how songs DataFrame is created:

songs = (
    spotify
    .drop_duplicates(subset = ['pid', 'playlist_name', 'artist_name', 'track_name'])
    [['pid', 'playlist_name', 'artist_name', 'track_name']]
    )

songs['song'] = songs['artist_name'] + ' – ' + songs['track_name']
songs = songs[['pid', 'playlist_name', 'song']]

pairs = songs.merge(songs, on='pid')

pair_counts = (
    pairs[['song_x','song_y']]
    .value_counts()
    .reset_index()
    .query('song_x != song_y')
    .nlargest(1, 'count', keep = "all")  
)

# Note: Lexicographic filtering is necessary to treat (song_x, song_y) and (song_y, song_x) as the same pair.

Section 4. Pandas Basics II

Below is shoes DataFrame that reads the file onlinestore_shoes_simple.csv containing data of “shoes” search information from an online store.

shoes = pd.read_csv('https://bcdanl.github.io/data/onlinestore_shoes_simple.csv')

Variable Description

Variable	Description
id	Unique identifier for the product
brandId	Unique identifier for the brand
brandName	Name of the brand
name	Product name or title
reviewCount	Number of customer reviews
reviewStarRating	Average star rating from customer reviews
current_p	Current price
clearance_p	Clearance price (NA if not available for clearance)

Question 15 (Points: 5)

• Write code to identify the top deal product(s), where a deal is defined by all of the following:
  1. On clearance: the product’s current_p equals its clearance_p.
  2. Highly reviewed: reviewCount is at or above the 95th percentile of all reviewCount values.
  3. Best rated: among these clearance, highly‑reviewed products, it has the maximum reviewStarRating (including any ties).

Index	id	brandId	brandName	name	reviewCount	reviewStarRating	currernt_p	clearance_p
92	6545910	9423	Tory Burch	Ballet Loafer	1008	4.8	178	178
393	7248724	9423	Tory Burch	Howell Court Sneaker	1044	4.8	88	88
6318	5654262	15361	See Kai Run	Stevie II Sneaker	113	4.8	35	35

review_top5 = shoes['reviewCount'].quantile(.95)
shoes_promo = (
    shoes[(shoes['clearance_p'] == shoes['currernt_p']) & (shoes['reviewCount'] >= review_top5) ]
    .nlargest(1, 'reviewStarRating', keep = 'all')
    )

Question 16 (Points: 7)

• Write pandas code to:
  1. Count products per brand: determine how many items each brand offers.
  2. Find the 90th‑percentile threshold of those brand‑level counts.
  3. Select “popular” brands whose product counts exceed that threshold.
  4. Compute clearance rates for each of these popular brands: the fraction of their items with a non‑missing clearance_p.
  5. List the top five brands by clearance rate, reporting each brand and its rate, as shown in below DataFrame.

Index	brandName	count_x	count_y	rate
4	MIA	51	76	0.6710526
1	Fly London	76	125	0.6080000
8	Vince Camuto	41	77	0.5324675
13	Keds®	37	70	0.5285714
12	Cole Haan	37	85	0.4352941

# 1. Count total products per brand
total_per_brand = shoes['brandName'].value_counts().reset_index()

total_per_brand_n = total_per_brand['count'].quantile(.9)
total_per_brand = total_per_brand[total_per_brand['count'] >= total_per_brand_n]

# 2. Count total products per brand
shoes['clearance_available'] = shoes['clearance_p'].notna()

total_per_brand_c = (
    shoes
    .query('clearance_available == True')['brandName']
    .value_counts()
    .reset_index()
    )

# 3. Join
df = total_per_brand_c.merge(total_per_brand, on='brandName', how = 'left')
df = df[~df['count_y'].isna()]
df['rate'] = df['count_x'] / df['count_y'] 
df_top5 = df.nlargest(5, 'rate', keep = 'all')