> ## Content Index > Fetch the complete content index at: https://www.bambooweekly.com/llms.txt > Use this file to discover other available public pages before exploring further. # Bamboo Weekly #51: Academy Awards (solutions) - URL: https://www.bambooweekly.com/bw-51-academy-awards-solution/ - Published: 2024-02-01T16:01:23.000Z - Updated: 2026-08-23T09:36:56.000Z - Description: Get better at: CSV, PyArrow, missing values, cleaning, grouping, memory optimization, and plotting - Author: Reuven M. Lerner - Tags: csv, pyarrow, missing-data, cleaning, grouping, memory-optimization, plotting This week, we looked at data from the Academy Awards, aka the Oscars. These awards are presented annually by the Academy of Motion Picture Arts and Sciences ([https://oscars.org](https://oscars.org/?ref=bambooweekly.com)). In researching this week’s topic, I learned that the Academy’s founding had a great deal to do with helping production companies avoid unions during labor negotiations. The history written at Wikipedia ([https://en.wikipedia.org/wiki/Academy\_of\_Motion\_Picture\_Arts\_and\_Sciences](https://en.wikipedia.org/wiki/Academy%5Fof%5FMotion%5FPicture%5FArts%5Fand%5FSciences?ref=bambooweekly.com)) was quite eye-opening in this regard, and is worth a quick look. The Oscars have been around for nearly a century, and while the number and names of the awards have changed, there’s definitely some cachet associated with being nominated for one, let alone for winning one. Given that nominations were announced last week ([https://www.nytimes.com/2024/01/23/movies/2024-oscar-nominees-list.html?unlocked\_article\_code=1.R00.b7Z6.51ltAGMr6vjb&bgrp=a&smid=url-share](https://www.nytimes.com/2024/01/23/movies/2024-oscar-nominees-list.html?unlocked%5Farticle%5Fcode=1.R00.b7Z6.51ltAGMr6vjb&bgrp=a&smid=url-share&ref=bambooweekly.com)), I thought that we could look through the history of Oscar nominations and awards, and learn something interesting about them. ### Data and eight questions David Lu ([https://davidlu.dev/](https://davidlu.dev/?ref=bambooweekly.com)), a software developer with an interest in open source, had put together a data set that includes much of the information from Kaggle ([https://www.kaggle.com/datasets/unanimad/the-oscar-award](https://www.kaggle.com/datasets/unanimad/the-oscar-award?ref=bambooweekly.com)), but also includes last week’s nominations. I asked you to download the CSV file that Lu provided on his site, and then gave you eight tasks and questions about them. As always, the Jupyter notebook that I used to solve these problems is at the end of this post. ### Download the \`oscars.csv\` file, and turn it into a data frame. We'll only use the Year, CanonicalCategory (which should then be renamed "Category"), Film, FilmId, Name, and Winner columns. Use PyArrow for the backend data storage. The "Winner" column should have True/False values, rather than True/NA values. The first thing that I did, as always, was to load Pandas: ``` import pandas as pd ``` Given that the file has a “csv” extension, we can expect that it’s a CSV (aka “comma-separated values”) file, which we can then read into Pandas with the “[read\_csv](https://www.bambooweekly.com/pandas-read-csv/)” method: ``` df = ( pd .read_csv(filename) ) ``` However, we would be wrong; this doesn’t do the trick. That’s because the file uses tabs (\\t) to separate fields, rather than commas. I’m actually a big fan of using tabs, since they are less likely to be inside of our data, and thus to force the use of quotes. We can tell read\_csv to treat tabs as the field separator, rather than commas, by passing the “sep” keyword argument: ``` df = ( pd .read_csv(filename, sep='\t') ) ``` This is a good start, but there are a few more tasks to do. First of all, I asked you to include only some of the columns. We can pass a list of strings to the “usecols” keyword argument to select them: ``` df = ( pd .read_csv(filename, sep='\t', usecols=['Year', 'CanonicalCategory', 'Film', 'FilmId', 'Name', 'Winner']) ) ``` Furthermore, I asked you to use the “PyArrow” (https://arrow.apache.org/docs/python/index.html) backend for Pandas. Arrow is an open-source project that provides an in-memory backend suitable for use in data-analysis languages and libraries, including Pandas. By default, Pandas uses NumPy as its backend, but Arrow is the clear future direction, and the core developers are pushing ahead to use it. Before you can use PyArrow as a backend, you’ll need to install it on your system with “pip install pyarrow”. Once you’ve done that, you can pass the “dtype\_backend” keyword argument to read\_csv: ``` df = ( pd .read_csv(filename, sep='\t', usecols=['Year', 'CanonicalCategory', 'Film', 'FilmId', 'Name', 'Winner'], dtype_backend='pyarrow')) ) ``` We now have a data frame with all of the rows and columns that we want. However, I asked you to do two additional things: First, I asked that you rename the “CanonicalCategory” column to be “Category”. The original data set has two columns, one with each of these names; the “Category” column is the official name that the Academy uses, whereas the “CanonicalCategory” ensures that we can compare awards across years by standardizing the names. We can use the “[rename](https://www.bambooweekly.com/pandas-rename/)” method on our data frame, passing a dictionary to the “columns” keyword argument, to rename the column: ``` df = ( pd .read_csv(filename, sep='\t', usecols=['Year', 'CanonicalCategory', 'Film', 'FilmId', 'Name', 'Winner'], dtype_backend='pyarrow') .rename(columns={'CanonicalCategory':'Category'}) ) ``` The data frame now contains the data we want, and the column names we want. Actually, that’s not completely true: The “Winner” column, which indicates whether a nominee won their award, is theoretically a boolean value (aka True or False). But in actuality, the values are either True or pd.NA, the PyArrow (and modern Pandas) version of NaN. (More info is at [https://pandas.pydata.org/pandas-docs/stable/user\_guide/missing\_data.html](https://pandas.pydata.org/pandas-docs/stable/user%5Fguide/missing%5Fdata.html?ref=bambooweekly.com).) We can turn all of those pd.NA values into False with a call to “[replace](https://www.bambooweekly.com/pandas-replace/)”. Notice that here, we’ll pass a dictionary as an argument. That dict’s keys are the columns in which we want to make replacements. The dict’s values are themselves dicts, with the keys representing what we want replaced, and the values indicating what the substitutions should be. ``` df = ( pd .read_csv(filename, sep='\t', usecols=['Year', 'CanonicalCategory', 'Film', 'FilmId', 'Name', 'Winner'], dtype_backend='pyarrow') .rename(columns={'CanonicalCategory':'Category'}) .replace({'Winner': {pd.NA: False}}) ) ``` With this in place, we now have a data frame containing 11,856 rows and 6 columns. We can see that it’s using PyArrow by looking at the dtypes: ``` Year int64 Category string[pyarrow] Film string[pyarrow] FilmId string[pyarrow] Name string[pyarrow] Winner bool[pyarrow] dtype: object ``` ### The "Year" column should contain integers, but it doesn't, because the first few awards were listed as having two years (e.g., "1927/28"). Change the values in this column to reflect the second date (e.g., "1927/28" should become "1928") and then change the column to contain integers. Dates and times can be frustrating to work with, for a variety of reasons. But when I saw that the “Year” column hadn’t been assigned an integer dtype value when read\_csv imported the file, I was rather surprised. A quick look showed what I described in the problem statement, namely that during the first few years, the Oscars were listed as being in “1927/28”, with two years named. At first, I thought that this meant the awards were given only once every two years, but I was wrong. If we were to just take any year value “19XX/YY”, we could replace it with “19YY” and be right. Moreover, this would then contain an integer value that we could turn into an appropriate dtype. There are several ways to solve this problem, but my favorite is (of course) to use a regular expression. Moreover, I want to use one of the more powerful aspects of regular expressions, namely capturing. In other words, given a value “19XX/YY”, I’ll grab the values in YY and then replace the entire string with “19YY”. The value will then be ready for us to turn into an integer. How does this work? Well, given that \\d in a regular expression means “any digit 0-9,” the pattern we want can be described as “19\\d\\d/\\d\\d” — 19, followed by two digits, followed by a slash, and then another two digits. I can capture the final two digits with parentheses, as “19\\d\\d/(\\d\\d)”. I can then tell Pandas that I want to replace that first string with “19\\1”, where \\1 means, “Whatever was found in that first (and only) group of parentheses.” That’s great, but how can I make this substitution? I can use the “[str.replace](https://www.bambooweekly.com/pandas-str-replace/)” method, giving the search regexp, the replacement (with \\1 substitution), and also indicating regexp=True as a keyword argument: ``` ( df ['Year'] .str.replace(r'19\d\d/(\d\d)', r'19\1', regex=True) ) ``` Notice that I used raw strings (i.e., with an r before the opening quote) to tell Python that it should double all of the backslashes, This reduces the potential confusion and fights between Python’s view of backslashed characters and the regular expression engine’s view. The above gives us back a column containing only four-character strings, in which all of the characters are digits (aka years). However, they are still strings. We can invoke “[astype](https://www.bambooweekly.com/pandas-astype/)” to get back an integer column, which we then assign back to the “Year” column in our data frame: ``` ( df ['Year'] .str.replace(r'19\d\d/(\d\d)', r'19\1', regex=True) .astype(int) ) ``` Our “Year” column now contains integers, giving us a chance to work with them numerically. ### Film names are allowed to repeat. (That's why we kept the "FilmID" column.) How many film names have been used multiple times? What film name has been used the greatest number of times? If we only had the “Film” column, then we wouldn’t be able to answer this question. That’s because counting the number of times that a film appears won’t quite work, given that films are often nominated for multiple Academy Awards in the same year. Having the “FilmId” column is a big help: We can use “[groupby](https://www.bambooweekly.com/pandas-groupby/)” to find out how many different IDs there are for a given film name: ``` ( df .groupby('Film')['FilmId'].count() ) ``` But wait: This hasn’t solved the problem at all! Using the “[count](https://www.bambooweekly.com/pandas-count/)” method will tell us how many times it appeared. If the same film was nominated multiple times, then we’ll be counting all of those times. Instead, we want to use the “nunique” aggregation method, which tells us how many distinct values of FilmId we have for each value of Film: ``` ( df .groupby('Film')['FilmId'].nunique() ) ``` Now we know how many unique IDs there are for each film name. However, this result includes all of the films, including those that only appear once. We want to keep only those that appear once or more. We can do that with “[loc](https://www.bambooweekly.com/pandas-loc/)” and a lambda: ``` ( df .groupby('Film')['FilmId'].nunique() .loc[lambda s_: s_>1] ) ``` Finally, let’s find out how many such films exist, by using “count”: ``` ( df .groupby('Film')['FilmId'].nunique() .loc[lambda s_: s_>1] .count() ) ``` I get a result of 93. I then asked you to find which film names had been repeated the most times. We can do that by taking the post-filtered results (i.e., after using “loc”), sorting the values in descending order with “[sort\_values](https://www.bambooweekly.com/pandas-sort-values/)”, and then grabbing the top 10 values with “[head](https://www.bambooweekly.com/pandas-head/)”: ``` ( df .groupby('Film')['FilmId'].nunique() .loc[lambda s_: s_>1] .sort_values(ascending=False) .head(10) ) ``` Here is what I get: ``` Film A Star Is Born 4 Little Women 4 Emma 3 Hamlet 3 A Farewell to Arms 2 The Buccaneer 2 The Hurricane 2 The Hunchback of Notre Dame 2 The Great Gatsby 2 The Fly 2 Name: FilmId, dtype: int64 ``` We can see that both “A Star Is Born” and “Little Women” has existed in four different versions that were nominated. Note that this doesn’t really represent the number of times the film title has been used. Rather, it shows how many times the title has been used on four different films that were nominated for Academy Awards. ### What film has been nominated for the greatest number of awards? Which film actually won the greatest number of awards? Once again, we face a grouping question: We want to count the number of nominations (i.e., rows in the data frame) for each unique film. I could have grouped on the “FilmId” column, since we know that it’s unique. But I decided that I would instead group on a combination of “Year” and “Film”, which is also unique. We can then count, for each Year/Film, how many times “FilmId” appeared in the data frame: ``` ( df .groupby(['Year', 'Film'])['FilmId'] .count() ) ``` With that in hand, I can then sort the values in descending order, and get the top 15: ``` ( df .groupby(['Year', 'Film'])['FilmId'] .count() .sort_values(ascending=False) .head(15) ) ``` This is what I get: ``` Year Film 1939 Gone with the Wind 15 1950 All about Eve 14 1997 Titanic 14 2016 La La Land 14 1953 From Here to Eternity 13 1964 Mary Poppins 13 1966 Who's Afraid of Virginia Woolf? 13 1994 Forrest Gump 13 1998 Shakespeare in Love 13 2001 The Lord of the Rings: The Fellowship of the Ring 13 2002 Chicago 13 2008 The Curious Case of Benjamin Button 13 2017 The Shape of Water 13 2023 Oppenheimer 13 1942 Mrs. Miniver 12 Name: FilmId, dtype: int64[pyarrow] ``` This describes the number of nominations. What if we count Oscar wins? We can count those by using almost the same query, first using “loc” to keep only those rows where “Winner” is True. Our grouping and counting will then look only at winning films: ``` ( df .loc[lambda df_: df_['Winner'] == True] .groupby(['Year', 'Film'])['FilmId'] .count() .sort_values(ascending=False) .head(15) ``` The result: ``` Year Film 1959 Ben-Hur 11 1997 Titanic 11 2003 The Lord of the Rings: The Return of the King 11 1939 Gone with the Wind 10 1961 West Side Story 10 1958 Gigi 9 1987 The Last Emperor 9 1996 The English Patient 9 1946 The Best Years of Our Lives 8 1953 From Here to Eternity 8 1954 On the Waterfront 8 1964 My Fair Lady 8 1972 Cabaret 8 1982 Gandhi 8 1984 Amadeus 8 Name: FilmId, dtype: int64[pyarrow] ``` ### Who has been nominated the most times for best actor/actress (of any sort, leading or supporting)? Who has won the greatest number of times? Now that we’ve looked at the most-nominated films, let’s look at the people in those films. Who has been nominated the most times? This is a similar problem to what we did before, except that we only want to look at a subset of the awards. This means filtering the award names. But we don’t want just one name; we want a whole bunch of them. We can solve this by using “loc” to filter, passing a lambda that checks if the “Category” any of several values: ``` ( df .loc[lambda df_: df_['Category'].isin(['ACTOR IN A SUPPORTING ROLE', 'ACTRESS IN A SUPPORTING ROLE', 'ACTOR IN A LEADING ROLE', 'ACTRESS IN A LEADING ROLE', 'ACTOR', 'ACTRESS'])] ['Name'] .value_counts() .head(15) ) ``` In the above query, I list all of the different possible awards for actors and actresses. (The award name has changed over time, and there’s the leading/supporting distinction.) So I could use “[isin](https://www.bambooweekly.com/pandas-isin/)” in order to choose from those strings. Then, after choosing just those rows via loc and lambda, I can retrieve the “Name” column, count how often they show up, and grab the top 15. The result: ``` Name Meryl Streep 21 Katharine Hepburn 12 Jack Nicholson 12 Bette Davis 11 Spencer Tracy 9 Laurence Olivier 9 Paul Newman 9 Al Pacino 9 Denzel Washington 9 Marlon Brando 8 Geraldine Page 8 Jack Lemmon 8 Peter O'Toole 8 Robert De Niro 8 Glenn Close 8 Name: count, dtype: int64[pyarrow] ``` Wow! I knew that Meryl Streep had been nominated many times, but didn’t realize quite how many. That’s … very impressive, all right. But what about wins, rather than nominations? We can combine this query with the filtering we did in the last problem, first keeping only those rows for winners (vs. nominees): ``` ( df .loc[lambda df_: df_['Winner'] == True] .loc[lambda df_: df_['Category'].isin(['ACTOR IN A SUPPORTING ROLE', 'ACTRESS IN A SUPPORTING ROLE', 'ACTOR IN A LEADING ROLE', 'ACTRESS IN A LEADING ROLE', 'ACTOR', 'ACTRESS'])] ['Name'] .value_counts() .head(15) ) ``` Notice an important point of using “loc” with lambda twice in a row: Because parameters are local to the function, and because we’re working with “df\_” (i.e., the value we were passed via method chaining) rather than “df” (i.e., the global variable), we’re able to use loc to slowly but surely chip away at the rows and columns. If we had use “df” in one or both of these lambdas, it would likely have gotten very confused, because the data frame it was handed was not the same as “df”, and didn’t even have all of df’s index values. The result of this query: ``` Name Katharine Hepburn 4 Janet Gaynor 3 Walter Brennan 3 Ingrid Bergman 3 Jack Nicholson 3 Meryl Streep 3 Frances McDormand 3 Emil Jannings 2 Fredric March 2 Helen Hayes 2 Bette Davis 2 Luise Rainer 2 Spencer Tracy 2 Vivien Leigh 2 Gary Cooper 2 Name: count, dtype: int64[pyarrow] ``` Ha! Meryl Streep might have been nominated 21 times, but she has only won 3 times, same as Ingrid Berman, Jack Nicholson, and Frances McDormand. What a loser. ### What was the greatest span of time between someone's first nomination and their last one? Some actors are in the film industry for a long time, and are nominated for awards at different ages. I was curious to know how much time passed between someone’s first and last nominations. (I should note that I only looked at the actor/actress categories of various sorts, something I probably should have mentioned in my question text.) Let’s first filter the rows, such that we only have awards for acting. Rather than use “isin” as before, I used a regular expression: - I wanted the regexp to match the start of the string, so I used ^ - I wanted to match either ACTOR or ACTRESS, so I put a | between them - To indicate that either of these words should come right after the start of the string, I put parentheses around them - To avoid having those parentheses interpreted as a capture group (i.e., what we did in question 2), I use the ?: construct inside of the opening parenthesis. This tells Python not to treat the parentheses for capturing, but rather only for grouping: ``` ( df .loc[lambda df_: df_['Category'].str.contains(r'^(?:ACTOR|ACTRESS)', regex=True)] ) ``` Now that we have only those awards, I want to get the minimum and maximum values of “Year” for each name. This sounds like a job for “groupby”. We can normally apply a single aggregation method to a groupby. Here, though, we want both “min” and “max”. How can we do that? With “[agg](https://www.bambooweekly.com/pandas-agg/)”, a grouping method that lets you pass a list of the methods (as strings) you really want to run: ``` ( df .loc[lambda df_: df_['Category'].str.contains(r'^(?:ACTOR|ACTRESS)', regex=True)] .groupby(['Name'])['Year'] .agg(['min', 'max']) ) ``` We now have a data frame in which the index contains names, and the columns contain the min and max years for someone’s nominations. We want to know much time passed between these, and then sort on that. Here, I thought it appropriate to add a new (temporary) column using “[assign](https://www.bambooweekly.com/pandas-assign/)”, giving it the difference between max and min. I called the new column “diff”: ``` ( df .loc[lambda df_: df_['Category'].str.contains(r'^(?:ACTOR|ACTRESS)', regex=True)] .groupby(['Name'])['Year'] .agg(['min', 'max']) .assign(diff=lambda df_: df_['max'] - df_['min']) ) ``` With the “diff” column in place, we can now sort by it, getting the 15 highest-ranked values: ``` ( df .loc[lambda df_: df_['Category'].str.contains(r'^(?:ACTOR|ACTRESS)', regex=True)] .groupby(['Name'])['Year'] .agg(['min', 'max']) .assign(diff=lambda df_: df_['max'] - df_['min']) .sort_values('diff', ascending=False) .head(15) ) ``` The results: ``` min max diff Name Robert De Niro 1974 2023 49 Katharine Hepburn 1933 1981 48 Jodie Foster 1976 2023 47 Al Pacino 1972 2019 47 Alan Arkin 1966 2012 46 Jeff Bridges 1971 2016 45 Peter O'Toole 1962 2006 44 Paul Newman 1958 2002 44 Robert Duvall 1972 2014 42 Judd Hirsch 1980 2022 42 Julie Christie 1965 2007 42 Henry Fonda 1940 1981 41 Mickey Rooney 1939 1979 40 Jack Palance 1952 1991 39 Laurence Olivier 1939 1978 39 ``` ### It sometimes seems like the number of awards has gone up over the years. Count the number of awards at each year's ceremony, and plot a line graph showing any changes. We want to find out the number of awards per year. That’s actually going to be the easiest use of groupby in this week’s newsletter: ``` ( df .groupby('Year')['Category'].nunique() ) ``` The above will find the number of times that each unique value for “Category” appeared in each year. And if I then want to turn the result into a plot? I call “plot.line”: ``` ( df .groupby('Year')['Category'].nunique() .plot.line() ) ``` Here’s what I got: ![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2fbc8e174b-f624-46f3-a36a-131b697fb92b_543x432.png) Wow! If you thought there were a lot of awards in the modern era, it looks like the late 1940s were just chock full of them. ### Finally, how much memory did the data frame use with PyArrow? How much would it have used with the standard NumPy backend? I asked you to use PyArrow, which is playing an increasingly central role in Pandas, and will be required as of Pandas 3.0\. We saw that we can use it instead of NumPy for backend data storage — but did we really save anything? Let’s check to see how much memory we are currently using: ``` usage = df.memory_usage(deep=True).sum() print(f'{usage:,}') ``` With PyArrow, I got a result of 1,120,354 bytes, or about 1.1 MB. I then loaded the data with a NumPy backend: ``` np_df = ( pd .read_csv(filename, sep='\t', usecols=['Year', 'CanonicalCategory', 'Film', 'FilmId', 'Name', 'Winner']) .rename(columns={'CanonicalCategory':'Category'}) .assign(Winner=lambda df_: df_['Winner'].astype(bool).fillna(False)) ) usage = np_df.memory_usage(deep=True).sum() print(f'{usage:,}') ``` Notice that the only difference is that I didn’t specify the backend, which defaults to NumPy. The memory usage report says it’s using 3,735,417 bytes, or about 3.7 MB. Which means that when it comes to this data set, Pandas is using 3x as much memory with NumPy as with PyArrow. That’s quite a difference. That’s it for my analysis this week. Here’s a link to my Jupyter notebook: [https://drive.google.com/file/d/1EZhkQwC8zlGEck4Xt9p9nZViDFDzmN9R/view?usp=sharing](https://drive.google.com/file/d/1EZhkQwC8zlGEck4Xt9p9nZViDFDzmN9R/view?usp=sharing&ref=bambooweekly.com) I’ll be back on Wednesday with more Pandas puzzles based on current events. Reuven