← return to study.practicaldsc.org

The problems in this worksheet are taken from past exams in similar classes. Work on them on paper, since the exams you take in this course will also be on paper.

We encourage you to complete this worksheet in a live discussion section. Solutions will be made available after all discussion sections have concluded. You don’t need to submit your answers anywhere.

Note: We do not plan to cover all problems here in the live discussion section; the problems we don’t cover can be used for extra practice.

Problem 1

In this problem, we will work with the DataFrame tv, which contains information about various TV shows available to watch on streaming services. For each TV show, we have:

• "Title" (object): The title of the TV show.
• "Year" (int): The year in which the TV show was first released. (For instance, the show How I Met Your Mother ran from 2005 to 2014; there is only one row for How I Met Your Mother in tv, and its "Year" value is 2005.)
• "Age" (object): The age category for the TV show. If not missing, "Age" is one of "all", "7+", "13+", "16+", or "18+". (For instance, "all" means that the show is appropriate for all audiences, while `“18+”} means that the show contains mature content and viewers should be at least 18 years old.)
• "IMDb" (float): The TV show’s rating on IMDb (between 0 and 10).
• "Rotten Tomatoes" (int): The TV show’s rating on Rotten Tomatoes (between 0 and 100).
• "Netflix" (int): 1 if the show is available for streaming on Netflix and 0 otherwise. The "Hulu", "Prime Video", and "Disney+" columns work the same way.

The first few rows of tv are shown below (though tv has many more rows than are pictured here).

As you see in the first few rows of tv, some TV shows are available for streaming on multiple streaming services. Fill in the blanks so that the two expressions below, Expression 1 and Expression 2, both evaluate to the "Title" of the TV show that is available for streaming on the greatest number of streaming services. Assume there are no ties and that the "Title" column contains unique values.

Expression 1:

tv.set_index("Title").loc[__(a)__].T.sum(axis=0).idxmax()

Expression 2:

    (
        tv.assign(num_services=tv.iloc[__(b)__].sum(__(c)__))
            .sort_values("num_services")
            .iloc[__(d)__]
    )

Hint: .T transposes the rows and columns of a DataFrame — the indexes of df are the columns of df.T and vice versa.

What goes in the blanks?

Problem 2

The laptops DataFrame contains information on various factors that influence the pricing of laptops. Each row represents a laptop, and the columns are:

• "Mfr" (str): the company that manufactures the laptop, like “Apple” or “Dell”.
• "Model" (str): the model name of the laptop, such as “MacBook Pro”.
• "OS" (str): the operating system, such as “macOS” or “Windows 11”.
• "Screen Size" (float): the diagonal length of the screen, in inches.
• "Price" (float): the price of the laptop, in dollars.

Problem 2.1

Without using groupby, write an expression that evaluates to the average price of laptops with the "macOS" operating system (the same quantity as above).

Problem 2.2

Using groupby, write an expression that evaluates to the average price of laptops with the "macOS" operating system.

Problem 3

You are given a DataFrame called books that contains columns 'author' (string), 'title' (string), 'num_chapters' (int), and 'publication_year' (int).

Suppose that after doing books.groupby('author').max(), one row says

Problem 3.1

Based on this data, can you conclude that Charles Dickens is the alphabetically last of all author names in this dataset?

• Yes

• No

Problem 3.2

Based on this data, can you conclude that Charles Dickens wrote Oliver Twist?

• Yes

• No

Problem 3.3

Based on this data, can you conclude that Oliver Twist has 53 chapters?

• Yes

• No

Problem 3.4

Based on this data, can you conclude that Charles Dickens wrote a book with 53 chapters that was published in 1838?

• Yes

• No

Problem 4

The h table records addresses within San Diego. Only 50 addresses are recorded. The index of the dataframe contains the numbers 1-50 as unique integers.

• "number" (int): Street address number
• "street" (str): Street name

Fill in the Python code to create a DataFrame containing the proportion of 4-digit address numbers for each unique street in h.

def foo(x):
    lengths = __(a)__
    return (lengths == 4).mean()

h.groupby(__(b)__).__(c)__(foo)

Problem 5

The DataFrame items describes various items available to collect or purchase using bells, the currency used in the game Animal Crossing: New Horizons.

For each item, we have:

• "Item" (str): The name of the item.
• "Cost" (int): The cost of the item in bells. Items that cost 0 bells cannot be purchased and must be collected through other means (such as crafting).
• "Location" (str): The store or action through which the item can be obtained.

The first 6 rows of items are below, though items has more rows than are shown here.

The DataFrame keepers has 5 rows, each of which represent a different shopkeeper in the Animal Crossing: New Horizons universe.

keepers is shown below in its entirety.

How many rows are in the following DataFrame? Give your answer as an integer.

keepers.merge(items.iloc[:6], 
              left_on="Store", 
              right_on="Location")

Problem 6

Suppose we create a DataFrame called midwest containing Nishant’s flights departing from DTW, ORD, and MKE. midwest has 10 rows; the bar chart below shows how many of these 10 flights departed from each airport.

Consider the DataFrame that results from merging midwest with itself, as follows:

double_merge = midwest.merge(midwest, left_on='FROM', right_on='FROM')

How many rows does double_merge have?

Problem 7

Kyle and Yutong are trying to decide where they’ll study on campus and start flipping a Michigan-themed coin, with a picture of the Michigan Union on the heads side and a picture of the Shapiro Undergraduate Library (aka the UgLi) on the tails side.

Kyle flips the coin 21 times and sees 13 heads and 8 tails. He stores this information in a DataFrame named kyle that has 21 rows and 2 columns, such that:

• The "flips" column contains "Heads" 13 times and "Tails" 8 times.

• The "Markley" column contains "Kyle" 21 times.

Then, Yutong flips the coin 11 times and sees 4 heads and 7 tails. She stores this information in a DataFrame named yutong that has 11 rows and 2 columns, such that:

• The "flips" column contains "Heads" 4 times and "Tails" 7 times.

• The "MoJo" column contains "Yutong" 11 times.

Problem 7.1

How many rows are in the following DataFrame? Give your answer as an integer.

    kyle.merge(yutong, on="flips")

Hint: The answer is less than 200.

Problem 7.2

Let A be your answer to the previous part. Now, suppose that:

• kyle contains an additional row, whose "flips" value is "Total" and whose "Markley" value is 21.

• yutong contains an additional row, whose "flips" value is "Total" and whose "MoJo" value is 11.

Suppose we again merge kyle and yutong on the "flips" column. In terms of A, how many rows are in the new merged DataFrame?

• A

• A+1

• A+2

• A+4

• A+231

Problem 8

Define small_students to be the DataFrame with 8 rows and 2 columns shown directly below, and define districts to be the DataFrame with 3 rows and 2 columns shown below small_students.

Consider the DataFrame merged, defined below.

merged = small_students.merge(districts, 
                                left_on="High School", 
                                right_on="school", 
                                how="outer")

How many total NaN values does merged contain? Give your answer as an integer.

Click to view the solution.

Answer: 4

merged is shown below.

Problem 9

The DataFrame dogs, contains one row for every registered pet dog in Zurich, Switzerland in 2017.

The first few rows of dogs are shown below, but dogs has many more rows than are shown.

• "owner_id" (int): A unique ID for each owner. Note that, for example, there are two rows in the preview for 4215, meaning that owner has at least 2 dogs. Assume that if an "owner_id" appears in dogs multiple times, the corresponding "owner_age", "owner_sex", and "district" are always the same.
• "owner_age" (str): The age group of the owner; either "11-20", "21-30", …, or "91-100" (9 possibilities in total).
• "owner_sex" (str): The birth sex of the owner; either "m" (male) or "f" (female).
• "district" (int): The city district the owner lives in; a positive integer between 1 and 12 (inclusive).
• "primary_breed" (str): The primary breed of the dog.
• "secondary_breed" (str): The secondary breed of the dog. If this column is not null, the dog is a “mixed breed” dog; otherwise, the dog is a “purebred” dog.
• "dog_sex" (str): The birth sex of the dog; either "m" (male) or "f" (female).
• "birth_year" (int): The birth year of the dog.

In this question, assume that there are more than 12 districts in dogs.

Suppose we merge the dogs DataFrame with itself as follows.

# on="x" is the same as specifying both left_on="x" and right_on="x".
double = dogs.merge(dogs, on="district")

# sort_index sorts a Series in increasing order of its index.
square = double["district"].value_counts().value_counts().sort_index()

The first few rows of square are shown below.

1     5500
4      215
9       40

Problem 9.1

In dogs, there are 12 rows with a "district" of 8. How many rows of double have a "district" of 8? Give your answer as a positive integer.

Problem 9.2

What does the following expression evaluate to? Give your answer as a positive integer.

dogs.groupby("district").filter(lambda df: df.shape[0] == 3).shape[0]

Hint: Unlike in 5.1, your answer to 5.2 depends on the values in square.

Problem 10

The DataFrame tv_excl contains all of the information we have for TV shows that are available to stream exclusively on a single streaming service. The "Service" column contains the name of the one streaming service that the TV show is available for streaming on.

The first few rows of tv_excl are shown below (though, of course, tv_excl has many more rows than are pictured here). Note that Being Erica is not in tv_excl, since it is available to stream on multiple services.

The DataFrame counts, shown in full below, contains the number of TV shows for every combination of "Age" and "Service".

Given the above information, what does the following expression evaluate to?

tv_excl.groupby(["Age", "Service"]).sum().shape[0]

• 4

• 5

• 12

• 16

• 18

• 20

• 25

Problem 11

The DataFrame flights contains information about recent flights, with each row representing a specific flight and the following columns:

• "flight num" (str): The unique code of the flight, consisting of a two-character airline designator followed by 1 to 4 digits (e.g., "UA1989").
• "airline" (str): The airline name (e.g., "United").
• "departure" (str): The code for the airport from which the flight departs (e.g., "SAN").
• "arrival" (str): The code for the airport at which the flight arrives (e.g., "LAX").

Suppose we have another DataFrame more_flights which contains the same columns as flights, but different rows. Define merged as follows.

        merged = flights.merge(more_flights, on = "airline")

Suppose that in merged, there are 108 flights where the airline is "United", and in more_flights, there are 12 flights where the airline is "United". If flights has 15 rows in total, how many of these rows are not for "United" flights? Give your answer as an integer.

Problem 12

For your convenience, the first few rows of tv are shown again below.

For the purposes of this question only, suppose we have also access to another similar DataFrame, movies, which contains information about a variety of movies. The information we have for each movie in movies is the same as the information we have for each TV show in tv, except for IMDb ratings, which are missing from movies.

The first few rows of movies are shown below (though movies has many more rows than are pictured here).

Problem 12.1

The function total_null, defined below, takes in a DataFrame and returns the total number of null values in the DataFrame.

total_null = lambda df: df.isna().sum().sum()

Consider the function delta, defined below.

def delta(a, b):
    tv_a = tv.head(a)
    movies_b = movies.head(b)
    together = pd.concat([tv_a, movies_b])
    return total_null(together) - total_null(tv_a) - total_null(movies_b)

Which of the following functions is equivalent to delta?

• lambda a, b: a

• lambda a, b: b

• lambda a, b: 9 * a

• lambda a, b: 8 * b

• lambda a, b: min(9 * a, 8 * b)

Problem 12.2

Fill in the blank to complete the implementation of the function size_of_merge, which takes a string col, corresponding to the name of a single column that is shared between tv and movies, and returns the number of rows in the DataFrame tv.merge(movies, on=col).

• For instance, size_of_merge("Year") should return the number of rows in tv.merge(movies, on="Year").

• The purpose of this question is to have you think conceptually about how merges work. As such, solutions containing merge or concat will receive 0 points.

What goes in the blank below?

def size_of_merge(col):
    return (____).sum()

Hint: Consider the behavior below.

>>> s1 = pd.Series({'a': 2, 'b': 3})
>>> s2 = pd.Series({'c': 4, 'a': -1, 'b': 4})
>>> s1 * s2
a    -2.0
b    12.0
c     NaN
dtype: float64

👋 Feedback: Find an error? Still confused? Have a suggestion? Let us know here.