1a
Print books.shape.
Check your output
(20, 9)Show solutionHide solution
python
print(books.shape)Block 1 · Exercise 9
Pandas
- Starter notebook
09-pandas-starter- Fabric path
/lakehouse/default/Files/data/solutions/09.pandas/
Open the notebook 09-pandas-starter in your workspace and run its setup cell. It copies the course data to the notebook's own disk, and the Step 1 cell reads the books table of books.db from there : a small catalogue of twenty books, with a price, a rating, an availability and a category. Each step has a cell with comments where your code goes, and later steps reuse the books DataFrame.
Do one part at a time, 1a, 1b and so on, and compare with the expected output before you go on. Where a table is printed, your layout may differ ; the values have to match. The solution is in 09-pandas-solution, under every part on the exercise site, and at the back of the exercises PDF.
Step 1Load the books
The Step 1 cell has opened the database, printed the table names and read the books table into books.
1b
Print the first five rows of the columns title, price_gbp, rating and category.
Hint 1
The slide head and tail shows how to take the first rows. The slide Selecting Columns shows how to pick columns with a list of names.
Check your output
title price_gbp rating category
Sharp Objects 47.82 4 Mystery
In a Dark, Dark Wood 19.63 1 Mystery
The Past Never Ends 56.50 4 Mystery
A Murder in Time 16.64 1 Mystery
The Murder of Roger Ackroyd (Hercule Poirot #4) 44.10 4 MysteryShow solutionHide solution
python
print(books[["title", "price_gbp", "rating", "category"]].head().to_string(index=False))Step 2Inspect
2a
Print books.info() and books.describe(). Read off how many rows there are and what type each column has.
Hint 1
The slide info and describe shows what each of the two reports.
Check your output
<class 'pandas.DataFrame'>
RangeIndex: 20 entries, 0 to 19
Data columns (total 9 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 id 20 non-null int64
1 title 20 non-null str
2 price_gbp 20 non-null float64
3 rating 20 non-null int64
4 availability 20 non-null str
5 category 20 non-null str
6 url 20 non-null str
7 first_seen 20 non-null str
8 last_seen 20 non-null str
dtypes: float64(1), int64(2), str(6)
memory usage: 5.1 KB
id price_gbp rating
count 20.00000 20.000000 20.000000
mean 10.50000 32.794000 2.900000
std 5.91608 17.447465 1.447321
min 1.00000 10.690000 1.000000
25% 5.75000 16.707500 1.750000
50% 10.50000 27.030000 3.000000
75% 15.25000 49.337500 4.000000
max 20.00000 59.480000 5.000000The class name, the text dtype (str or object) and the memory line depend on the pandas version.
Show solutionHide solution
python
books.info()
print(books.describe())2b
Print the number of missing values in each column.
Hint 1
The slide Missing Values shows isna(), which gives a mask. A mask sums to a count.
Check your output
id 0
title 0
price_gbp 0
rating 0
availability 0
category 0
url 0
first_seen 0
last_seen 0
dtype: int64Show solutionHide solution
python
print(books.isna().sum())2c
Print the distinct values of the category column and how many books each one has.
Hint 1
One method returns the values and their counts together.
Check your output
category
Mystery 20
Name: count, dtype: int64Show solutionHide solution
python
print(books["category"].value_counts())2d
Print the dtype of price_gbp and the dtype of rating.
Check your output
float64
int64Show solutionHide solution
python
print(books["price_gbp"].dtype)
print(books["rating"].dtype)Step 3Filters
3a
Make three named masks : well_rated for a rating of 4 or more, in_stock for an availability of In stock, and affordable for a price_gbp under 40. Print how many rows each one matches.
Hint 1
The slide Filtering Rows shows a comparison on a column. True counts as 1 when you sum.
Check your output
8 20 12Show solutionHide solution
python
well_rated = books["rating"] >= 4
in_stock = books["availability"] == "In stock"
affordable = books["price_gbp"] < 40.0
print(well_rated.sum(), in_stock.sum(), affordable.sum())3b
Put the rows that satisfy all three masks in shortlist. Print its title, price_gbp, rating and availability.
Hint 1
The slide Combining Filters says which operator combines masks.
Hint 2
and wants one answer and a mask holds twenty. Use &.
Check your output
title price_gbp rating availability
What Happened on Beale Street (Secrets of the South Mysteries #2) 25.37 5 In stock
Delivering the Truth (Quaker Midwife Mystery #1) 20.89 4 In stockShow solutionHide solution
python
shortlist = books[well_rated & in_stock & affordable]
print(shortlist[["title", "price_gbp", "rating", "availability"]].to_string(index=False))3c
Print the first five books with a rating of 4 or 5, without a chain of or.
Hint 1
A column has a method that asks whether each value is in a list.
Check your output
title rating
Sharp Objects 4
The Past Never Ends 4
The Murder of Roger Ackroyd (Hercule Poirot #4) 4
A Time of Torment (Charlie Parker #14) 5
Murder at the 42nd Street Library (Raymond Ambler #1) 4Show solutionHide solution
python
print(books[books["rating"].isin([4, 5])][["title", "rating"]].head().to_string(index=False))3d
Print the books with a price_gbp from 20 to 40, both ends included, without two comparisons.
Hint 1
A column has a method that takes a lower and an upper bound.
Check your output
title price_gbp
Poisonous (Max Revere Novels #3) 26.80
Most Wanted 35.28
The Widow 27.26
What Happened on Beale Street (Secrets of the South Mysteries #2) 25.37
Delivering the Truth (Quaker Midwife Mystery #1) 20.89Show solutionHide solution
python
print(books[books["price_gbp"].between(20.0, 40.0)][["title", "price_gbp"]].to_string(index=False))Step 4Derived columns
The starter cell defines RATE, the euros to the pound.
4a
Add a column price_eur : price_gbp times RATE, rounded to two decimals. Print the first five rows of title, price_gbp and price_eur.
Hint 1
The slide Adding a Column shows assigning to a new column name.
Check your output
title price_gbp price_eur
Sharp Objects 47.82 55.95
In a Dark, Dark Wood 19.63 22.97
The Past Never Ends 56.50 66.10
A Murder in Time 16.64 19.47
The Murder of Roger Ackroyd (Hercule Poirot #4) 44.10 51.60Show solutionHide solution
python
books["price_eur"] = (books["price_gbp"] * RATE).round(2)
print(books[["title", "price_gbp", "price_eur"]].head().to_string(index=False))4b
Add a column band : budget under 20 euros, mid under 45, and premium above that. Print how many books are in each band.
Hint 1
The slide Deriving a Column shows np.where. A second np.where in its else part gives the third band.
Hint 2
pd.cut is the other way : it takes the edges and the labels.
Check your output
band
premium 8
mid 6
budget 6
Name: count, dtype: int64Show solutionHide solution
python
books["band"] = np.where(
books["price_eur"] < 20.0,
"budget",
np.where(books["price_eur"] < 45.0, "mid", "premium"),
)
print(books["band"].value_counts())4c
Print the five most expensive titles with both prices.
Hint 1
The slide Sorting shows sort_values. Its ascending argument reverses the order.
Hint 2
nlargest(5, "price_eur") says the same thing in one call.
Check your output
title price_gbp price_eur
Boar Island (Anna Pigeon #19) 59.48 69.59
The Past Never Ends 56.50 66.10
Murder at the 42nd Street Library (Raymond Ambler #1) 54.36 63.60
The Last Mile (Amos Decker #2) 54.21 63.43
The Bachelor Girl's Guide to Murder (Herringford and Watts Mysteries #1) 52.30 61.19Show solutionHide solution
python
top = books.sort_values("price_eur", ascending=False).head(5)
print(top[["title", "price_gbp", "price_eur"]].to_string(index=False))Step 5Summary by rating
5a
Make per_rating, one row per star rating, with four columns : titles, the number of books with that rating, then mean_price, cheapest and dearest, all on price_eur. Round to two decimals and turn the rating back into a column. Print it.
Hint 1
The slide Named Aggregation shows agg with name=(column, function) pairs, so the columns arrive with the names you want.
Hint 2
groupby("rating") first, then agg, then round(2), then reset_index().
Check your output
rating titles mean_price cheapest dearest
1 5 17.02 12.51 22.97
2 3 38.30 19.57 63.43
3 4 39.57 16.04 69.59
4 5 52.34 24.44 66.10
5 3 49.15 29.68 61.19Show solutionHide solution
python
per_rating = (
books.groupby("rating")
.agg(
titles=("title", "count"),
mean_price=("price_eur", "mean"),
cheapest=("price_eur", "min"),
dearest=("price_eur", "max"),
)
.round(2)
.reset_index()
)
print(per_rating.to_string(index=False))5b
Sort per_rating by mean_price, highest first, and print it. Does a higher rating cost more?
Check your output
rating titles mean_price cheapest dearest
4 5 52.34 24.44 66.10
5 3 49.15 29.68 61.19
3 4 39.57 16.04 69.59
2 3 38.30 19.57 63.43
1 5 17.02 12.51 22.97Show solutionHide solution
python
print(per_rating.sort_values("mean_price", ascending=False).to_string(index=False))Step 6Excel report
The starter cell defines WORKBOOK, the path of the report on the notebook's own disk.
6a
Make shortlist again : the books with a rating of 4 or more that are in stock, sorted by price_eur, most expensive first, with the columns title, category, rating, price_gbp, price_eur and band. Print its title, price_eur and band.
Hint 1
Reuse the masks from 3a. This time there is no price filter.
Check your output
title price_eur band
The Past Never Ends 66.10 premium
Murder at the 42nd Street Library (Raymond Ambler #1) 63.60 premium
The Bachelor Girl's Guide to Murder (Herringford and Watts Mysteries #1) 61.19 premium
A Time of Torment (Charlie Parker #14) 56.57 premium
Sharp Objects 55.95 premium
The Murder of Roger Ackroyd (Hercule Poirot #4) 51.60 premium
What Happened on Beale Street (Secrets of the South Mysteries #2) 29.68 mid
Delivering the Truth (Quaker Midwife Mystery #1) 24.44 midShow solutionHide solution
python
shortlist = books[well_rated & in_stock].sort_values("price_eur", ascending=False)[
["title", "category", "rating", "price_gbp", "price_eur", "band"]
]
print(shortlist[["title", "price_eur", "band"]].to_string(index=False))6b
Write shortlist and per_rating into WORKBOOK, one sheet each, named shortlist and by rating, without the row numbers. Then print the sheet names of the file.
Hint 1
The slide Writing Files shows pd.ExcelWriter and to_excel.
Hint 2
Pass index=False to both to_excel calls. pd.ExcelFile(path).sheet_names lists the sheets.
Check your output
['shortlist', 'by rating']Show solutionHide solution
python
with pd.ExcelWriter(WORKBOOK, engine="openpyxl") as workbook:
shortlist.to_excel(workbook, sheet_name="shortlist", index=False)
per_rating.to_excel(workbook, sheet_name="by rating", index=False)
print(pd.ExcelFile(WORKBOOK).sheet_names)6c
Write shortlist to book-report.csv in DATA as well, for whoever has no Excel, without the row numbers. Read the file back and print its number of rows.
Check your output
8Show solutionHide solution
python
csv_path = DATA / "book-report.csv"
shortlist.to_csv(csv_path, index=False)
print(len(pd.read_csv(csv_path)))6d
Read the by rating sheet back from WORKBOOK and print it, to check that pandas can open the file again.
Hint 1
pd.read_excel takes a sheet_name.
Check your output
rating titles mean_price cheapest dearest
1 5 17.02 12.51 22.97
2 3 38.30 19.57 63.43
3 4 39.57 16.04 69.59
4 5 52.34 24.44 66.10
5 3 49.15 29.68 61.19Show solutionHide solution
python
print(pd.read_excel(WORKBOOK, sheet_name="by rating").to_string(index=False))If time permits
- Keep the workbook. It sits on the notebook's own disk and goes when the session stops. Copy it into the
Filesfolder of your own lakehouse withshutil.copy, then download it from the lakehouse explorer. - A picture. Plot the mean price per rating as a bar chart of
per_rating. In a notebook the chart appears under the cell. Take the figure withax.get_figure()and save it intoDATAwithsavefig. - A join. Build a small lookup frame that gives each star rating a word,
poortoexcellent. Merge it ontoper_ratingonratingand print the result. Then delete one row from the lookup, merge withhow="left"andindicator=True, and see which rows failed to match. That check is worth doing on every join.