import numpy as np
import pandas as pd4 Pandas and Polars
4.1 Topics Covered
- Creating and manipulating Series and DataFrames using Pandas
- Querying and subsetting DataFrames
- Handling missing values
- Summarizing and grouping DataFrames
- Concatenating and merging tables
- How Polars differs from Pandas
- Performing common data table operations using Polars
4.2 Introduction
Most scientists are first introduced to data tables in the form of spreadsheets, such as an Excel spreadsheet. In such a structure, each record or feature is represented by a row of data while each column represents a specific piece of information for each record. For example, each row in a table could contain all the data for a specific student, and each column could hold a specific attribute for all students, such as academic year, age, or height.
Further, spreadsheets are able to hold different data types in each column. For example, academic year is categorical or ordinal while height is ratio or continuous.
A comparable data structure would be handy for use in Python. This is made available by the Pandas library. Pandas allows for data to be stored in DataFrames. If you work in the R environment, this is very similar to the concept of data frames in R. In fact, Pandas DataFrames were inspired by R data frames.
Pandas makes use of the NumPy library (Chapter 3), so it is generally a good idea to import NumPy if you plan to use Pandas. Also, you will need to install Pandas and NumPy into your environment prior to using them. Both are included in the pybook environment.
The complete documentation for Pandas can be found here.
At the end of the chapter, we introduce Polars, a newer DataFrame library that offers an alternative to Pandas.
4.3 Intro to Series
Before we talk about DataFrames, we introduce the concept of a Series. These are very similar to a NumPy array except that they allow for axis labels to be assigned. Examples of generating a Series from lists, NumPy arrays, and dictionaries are provided below.
A Series is comparable to a single column from a spreadsheet.
lst1 = ["GIS", "Remote Sensing", "Spatial Analysis", "Digital Cartography"]
arr1 = np.array([350, 455, 457, 642])
dict1 = {'Class1':"GIS", "Class2":"Remote Sensing", "Class3":"Spatial Analysis", "Class4":"Digital Cartography"}
s_lst = pd.Series(data=lst1, index = ["Class1", "Class2", "Class3", "Class4"])
s_arr = pd.Series(data=arr1, index = ["Class1", "Class2", "Class3", "Class4"])
s_dict = pd.Series(dict1)
print(s_lst)Class1 GIS
Class2 Remote Sensing
Class3 Spatial Analysis
Class4 Digital Cartography
dtype: strprint(s_arr)Class1 350
Class2 455
Class3 457
Class4 642
dtype: int64print(s_dict)Class1 GIS
Class2 Remote Sensing
Class3 Spatial Analysis
Class4 Digital Cartography
dtype: strLabels or names can then be used to select data using either bracket notation or dot notation.
You can use whichever method you prefer. However, if you use dot notation, the labels cannot include spaces.
print(s_dict["Class3"])Spatial Analysisprint(s_dict.Class3)Spatial Analysis4.4 Intro to DataFrames
Let’s start by building a DataFrame from a set of lists. First, we create three lists to hold different components of course title information. Next, we combine these lists into a dictionary. Finally, we convert the dictionary into a DataFrame. The keys from the dictionary are used as the column names, and a default index is assigned to each row.
prefix = ["Geol", "Geol", "Geol", "Geol", "Geog", "Geog", "Geog"]
cnum = [103, 321, 331, 341, 350, 455, 462]
cname = ["Earth Through Time", "Geomorphology", "Paleontology", "Structural Geology", "GIScience", "Remote Sensing", "Digital Cartography"]
course_dict = {"prefix": prefix, "course_number": cnum, "course_name": cname}
course_df = pd.DataFrame(course_dict)
print(course_df) prefix course_number course_name
0 Geol 103 Earth Through Time
1 Geol 321 Geomorphology
2 Geol 331 Paleontology
3 Geol 341 Structural Geology
4 Geog 350 GIScience
5 Geog 455 Remote Sensing
6 Geog 462 Digital CartographySince column names are assigned, they can be used to select individual columns using bracket or dot notation. Single columns can be saved as a Series.
print(course_df["course_name"])0 Earth Through Time
1 Geomorphology
2 Paleontology
3 Structural Geology
4 GIScience
5 Remote Sensing
6 Digital Cartography
Name: course_name, dtype: str# Or course_df.course_nameA list of column names can be provided to subset multiple columns.
print(course_df[["course_number", "course_name"]]) course_number course_name
0 103 Earth Through Time
1 321 Geomorphology
2 331 Paleontology
3 341 Structural Geology
4 350 GIScience
5 455 Remote Sensing
6 462 Digital CartographyYou can also subset based on column names and row labels combined using .loc. .iloc, in contrast, is used for selection based on integer positions.
print(course_df.loc[[1, 2, 4],["course_number", "course_name"]]) course_number course_name
1 321 Geomorphology
2 331 Paleontology
4 350 GIScience# Or course_df.iloc[[1, 2, 4],[1, 2]]You can even use the data stored in existing columns to create a new column. Note that the new column does not need to be declared prior to writing to it. In the example, we write the entire course name to a new column. The map() method is used to make sure all data are treated as strings. It allows for the same function, in this case str(), to be applied to each element in a Series, in this case each row in the column. We include blank spaces so that the components do not run together.
course_df["full_name"] = course_df["prefix"].map(str) + " " + course_df["course_number"].map(str) + ": " + course_df["course_name"].map(str)
print(course_df) prefix course_number course_name full_name
0 Geol 103 Earth Through Time Geol 103: Earth Through Time
1 Geol 321 Geomorphology Geol 321: Geomorphology
2 Geol 331 Paleontology Geol 331: Paleontology
3 Geol 341 Structural Geology Geol 341: Structural Geology
4 Geog 350 GIScience Geog 350: GIScience
5 Geog 455 Remote Sensing Geog 455: Remote Sensing
6 Geog 462 Digital Cartography Geog 462: Digital CartographyInstead of creating data tables or DataFrames manually, you are more likely to read in a data table from a file or web link. Fortunately, Pandas provides functions for reading data in from a variety of formats. Here are some examples:
-
read_table(): delimited file (TXT, CSV, etc.) -
read_csv(): comma-separated values (CSV) -
read_excel(): Excel spreadsheet -
read_json(): JavaScript Object Notation (JSON) -
read_html(): HTML table -
read_sas(): SAS file
Full documentation on reading in data can be found here.
In the example below, we read in a CSV file from the local computer. The sep argument is used to define the delimiter. However, commas are the default, so it isn’t necessary to include this argument in this case. Setting the header argument to 0 indicates that the first row of data should be treated as column names or headers. It isn’t always necessary to specify the character encoding; however, it is necessary for this dataset due to the use of special characters.
To view the first 10 rows of the data, we use the head() method. The len() function returns the number of rows.
The matts_movies.csv dataset was created by Matt, the author’s brother, and provides his ratings for movies along with each movie’s name, director, release year, genre, and whether or not he owns it.
movies_df = pd.read_csv("datasets/matts_movies.csv", sep=",", header=0, encoding="ISO-8859-1")
print(movies_df.head(10)) Movie Name Director ... Genre Own
0 Almost Famous Cameron Crowe ... Drama Yes
1 The Shawshank Redemption Frank Darabont ... Drama Yes
2 Groundhog Day Harold Ramis ... Comedy Yes
3 Donnie Darko Richard Kelly ... Sci-Fi Yes
4 Children of Men Alfonso Cuaron ... Sci-Fi Yes
5 Annie Hall Woody Allen ... Comedy Yes
6 Rushmore Wes Anderson ... Independent Yes
7 Memento Christopher Nolan ... Thriller Yes
8 No Country for Old Men Joel and Ethan Coen ... Thriller Yes
9 Seven David Fincher ... Thriller Yes
[10 rows x 6 columns]print(len(movies_df))18524.5 DataFrame Query and Subset
Let’s now use this data table to explore data query and selection methods. In the first example, we select all movies that are dramas and save them to a new DataFrame. Note the use of bracket notation.
The second example includes a compound query. Note the use of parentheses within the query.
Lastly, it is also possible to subset only certain columns that meet the query. In the last example, we subset just the movie name and director.
#Example 1
just_dramas = movies_df[movies_df["Genre"]=="Drama"]
print(just_dramas.head(10)) Movie Name Director ... Genre Own
0 Almost Famous Cameron Crowe ... Drama Yes
1 The Shawshank Redemption Frank Darabont ... Drama Yes
21 Boyhood Richard Linklater ... Drama No
26 American Beauty Sam Mendes ... Drama Yes
39 Mystic River Clint Eastwood ... Drama Yes
51 Hannah and Her Sisters Woody Allen ... Drama Yes
52 In the Bedroom Todd Field ... Drama Yes
57 Big Fish Tim Burton ... Drama Yes
60 The Sweet Hereafter Atom Egoyan ... Drama Yes
62 Stand By Me Rob Reiner ... Drama Yes
[10 rows x 6 columns]print(len(just_dramas))321#Example 2
dramas_gt_9 = movies_df[(movies_df["Genre"]=="Drama") & (movies_df["My Rating"]>9.0)]
print(len(dramas_gt_9))23#Example 3
dramas_gt_9_sub = movies_df[(movies_df["Genre"]=="Drama") & (movies_df["My Rating"]>9.0)][["Movie Name", "Director"]]
print(dramas_gt_9_sub.head(10)) Movie Name Director
0 Almost Famous Cameron Crowe
1 The Shawshank Redemption Frank Darabont
21 Boyhood Richard Linklater
26 American Beauty Sam Mendes
39 Mystic River Clint Eastwood
51 Hannah and Her Sisters Woody Allen
52 In the Bedroom Todd Field
57 Big Fish Tim Burton
60 The Sweet Hereafter Atom Egoyan
62 Stand By Me Rob ReinerAnother option for performing queries is to use the query() method provided by Pandas. When using this method, the query must be provided as an expression in string form. Also, spaces in column names can be problematic, so spaces should be removed or replaced with underscores.
#Remove spaces in column names using list comprehension
movies_df.columns = [column.replace(" ", "_") for column in movies_df.columns]
#Example 1
just_dramas = movies_df.query('Genre=="Drama"')
print(just_dramas.head(10)) Movie_Name Director ... Genre Own
0 Almost Famous Cameron Crowe ... Drama Yes
1 The Shawshank Redemption Frank Darabont ... Drama Yes
21 Boyhood Richard Linklater ... Drama No
26 American Beauty Sam Mendes ... Drama Yes
39 Mystic River Clint Eastwood ... Drama Yes
51 Hannah and Her Sisters Woody Allen ... Drama Yes
52 In the Bedroom Todd Field ... Drama Yes
57 Big Fish Tim Burton ... Drama Yes
60 The Sweet Hereafter Atom Egoyan ... Drama Yes
62 Stand By Me Rob Reiner ... Drama Yes
[10 rows x 6 columns]print(len(just_dramas))321#Example 2
dramas_gt_9 = movies_df.query('Genre=="Drama" and My_Rating > 9.0')
print(len(dramas_gt_9))23#Example 3
dramas_gt_9_sub = movies_df.query('Genre=="Drama" and My_Rating > 9.0')[["Movie_Name", "Director"]]
print(dramas_gt_9_sub.head(10)) Movie_Name Director
0 Almost Famous Cameron Crowe
1 The Shawshank Redemption Frank Darabont
21 Boyhood Richard Linklater
26 American Beauty Sam Mendes
39 Mystic River Clint Eastwood
51 Hannah and Her Sisters Woody Allen
52 In the Bedroom Todd Field
57 Big Fish Tim Burton
60 The Sweet Hereafter Atom Egoyan
62 Stand By Me Rob ReinerOnce a query is complete, you may want to save the result back to a file on your local machine. The code below provides an example of saving the last subset of data to a CSV file in the output folder. The Pandas documentation provides examples for saving to other formats.
dramas_gt_9_sub.to_csv("output/matts_movies_sub.csv", sep=",", header=True)4.6 Missing Values
The NULL, NoData, or missing indicator in Pandas is NaN. To begin exploring missing values, let’s recode some of the data to NaN in the movies dataset. In the example below, we change the “Drama” and “Fantasy” genres to NaN. We also recode any ratings between 8.9 and 9.9 to NaN. The replace() method is used to change the categories while the mask() method is used to recode the rating values. np.nan is a NumPy constant used to represent null values.
movies_nan = movies_df.copy()
movies_nan["Genre"] = movies_nan[["Genre"]].replace(["Drama", "Fantasy"], np.nan)
movies_nan['My_Rating'] = movies_nan['My_Rating'].mask(movies_nan['My_Rating'].between(8.9, 9.9))
print(movies_nan.head(10)) Movie_Name Director ... Genre Own
0 Almost Famous Cameron Crowe ... NaN Yes
1 The Shawshank Redemption Frank Darabont ... NaN Yes
2 Groundhog Day Harold Ramis ... Comedy Yes
3 Donnie Darko Richard Kelly ... Sci-Fi Yes
4 Children of Men Alfonso Cuaron ... Sci-Fi Yes
5 Annie Hall Woody Allen ... Comedy Yes
6 Rushmore Wes Anderson ... Independent Yes
7 Memento Christopher Nolan ... Thriller Yes
8 No Country for Old Men Joel and Ethan Coen ... Thriller Yes
9 Seven David Fincher ... Thriller Yes
[10 rows x 6 columns]print(len(movies_nan))1852The dropna() method can be used to remove rows or columns that contain missing data. If the axis parameter is set to 0, rows with missing values in any column are removed. If it is set to 1, columns with missing data in any row are removed.
movies_drop = movies_nan.dropna(axis=0)
print(movies_drop.head(10)) Movie_Name Director ... Genre Own
2 Groundhog Day Harold Ramis ... Comedy Yes
3 Donnie Darko Richard Kelly ... Sci-Fi Yes
4 Children of Men Alfonso Cuaron ... Sci-Fi Yes
5 Annie Hall Woody Allen ... Comedy Yes
6 Rushmore Wes Anderson ... Independent Yes
7 Memento Christopher Nolan ... Thriller Yes
127 After Hours Martin Scorsese ... Comedy Yes
128 Shotgun Stories Jeff Nichols ... Independent Yes
129 The Untouchables Brian de Palma ... Action Yes
130 2 Days in Paris Julie Delpy ... Romance Yes
[10 rows x 6 columns]print(len(movies_drop))1420movies_dropc = movies_nan.dropna(axis=1)
print(movies_dropc.head(10)) Movie_Name Director Release_Year Own
0 Almost Famous Cameron Crowe 2000 Yes
1 The Shawshank Redemption Frank Darabont 1994 Yes
2 Groundhog Day Harold Ramis 1993 Yes
3 Donnie Darko Richard Kelly 2001 Yes
4 Children of Men Alfonso Cuaron 2006 Yes
5 Annie Hall Woody Allen 1977 Yes
6 Rushmore Wes Anderson 1998 Yes
7 Memento Christopher Nolan 2000 Yes
8 No Country for Old Men Joel and Ethan Coen 2007 Yes
9 Seven David Fincher 1995 Yesprint(len(movies_dropc))1852The fillna() method can be used to replace NaN values with another value or string. In the example, we change the missing genres to “Unknown Genre”.
movies_nan["Genre"] = movies_nan["Genre"].fillna(value="Unknown Genre")
print(movies_nan.head(10)) Movie_Name Director ... Genre Own
0 Almost Famous Cameron Crowe ... Unknown Genre Yes
1 The Shawshank Redemption Frank Darabont ... Unknown Genre Yes
2 Groundhog Day Harold Ramis ... Comedy Yes
3 Donnie Darko Richard Kelly ... Sci-Fi Yes
4 Children of Men Alfonso Cuaron ... Sci-Fi Yes
5 Annie Hall Woody Allen ... Comedy Yes
6 Rushmore Wes Anderson ... Independent Yes
7 Memento Christopher Nolan ... Thriller Yes
8 No Country for Old Men Joel and Ethan Coen ... Thriller Yes
9 Seven David Fincher ... Thriller Yes
[10 rows x 6 columns]It is also possible to replace null values with a statistic derived from the available values. In the example, we replace the missing ratings with the mean of all available ratings. This would likely not be valid in this case since the missing genres might have different average ratings than the available genres. This is just an example.
movies_nan["My_Rating"] = movies_nan["My_Rating"].fillna(value=movies_nan["My_Rating"].mean())
print(movies_nan.head(10)) Movie_Name Director ... Genre Own
0 Almost Famous Cameron Crowe ... Unknown Genre Yes
1 The Shawshank Redemption Frank Darabont ... Unknown Genre Yes
2 Groundhog Day Harold Ramis ... Comedy Yes
3 Donnie Darko Richard Kelly ... Sci-Fi Yes
4 Children of Men Alfonso Cuaron ... Sci-Fi Yes
5 Annie Hall Woody Allen ... Comedy Yes
6 Rushmore Wes Anderson ... Independent Yes
7 Memento Christopher Nolan ... Thriller Yes
8 No Country for Old Men Joel and Ethan Coen ... Thriller Yes
9 Seven David Fincher ... Thriller Yes
[10 rows x 6 columns]4.7 Grouping and Summarizing DataFrames
Pandas provides methods for summarizing data, as described in the examples below. First, we create individual statistics and save them to variables. We then create a Series from a dictionary of these statistics, convert it to a DataFrame using the to_frame() method, and then transpose the DataFrame using transpose().
movies_df = pd.read_csv("datasets/matts_movies.csv", sep=",", header=0, encoding="ISO-8859-1")
movies_df.columns = [column.replace(" ", "_") for column in movies_df.columns]
mov_cnt = movies_df["My_Rating"].count()
mov_mn = movies_df["My_Rating"].mean()
mov_max = movies_df["My_Rating"].max()
mov_min = movies_df["My_Rating"].min()
mov_rang = mov_max-mov_min
mov_stats= pd.Series({"Count": mov_cnt, "Mean": mov_mn, "Max": mov_max, "Min": mov_min, "Range": mov_rang}).to_frame().transpose()
print(mov_stats) Count Mean Max Min Range
0 1852.0 6.952527 9.99 0.67 9.32It is also possible to obtain summary statistics for each group separately by applying the very useful groupby() method. In the example below, we obtain statistics for each genre and save them into a DataFrame. The columns do not need to be defined beforehand.
movies_df = pd.read_csv("datasets/matts_movies.csv", sep=",", header=0, encoding="ISO-8859-1")
movies_df.columns =[column.replace(" ", "_") for column in movies_df.columns]
genre_stats = pd.DataFrame()
genre_stats["Count"] = movies_df.groupby("Genre")["My_Rating"].count()
genre_stats["Mean"] = movies_df.groupby("Genre")["My_Rating"].mean()
genre_stats["Max"] = movies_df.groupby("Genre")["My_Rating"].max()
genre_stats["Min"] = movies_df.groupby("Genre")["My_Rating"].min()
genre_stats["Range"] = genre_stats["Max"] - genre_stats["Min"]
print(genre_stats) Count Mean Max Min Range
Genre
Action 197 6.651878 9.87 0.67 9.20
Classic 47 6.741064 8.33 4.03 4.30
Comedy 275 6.738545 9.96 1.76 8.20
Documentary 78 7.283718 8.87 5.43 3.44
Drama 321 7.140561 9.99 0.88 9.11
Family 84 6.670714 9.86 1.01 8.85
Fantasy 21 7.473810 9.85 6.21 3.64
Foreign 157 7.453439 9.63 3.76 5.87
Horror 96 6.523021 9.60 1.65 7.95
Independent 190 6.940263 9.92 1.81 8.11
Romance 94 7.238723 9.84 3.45 6.39
Sci-Fi 62 6.872419 9.95 1.92 8.03
Sports 23 6.391304 8.78 2.94 5.84
Thriller 164 7.095976 9.91 3.11 6.80
War 27 6.542593 8.67 4.37 4.30
Western 14 6.960000 8.65 4.53 4.12
comedy 1 6.030000 6.03 6.03 0.00
thriller 1 5.750000 5.75 5.75 0.00You may have noticed above that there are some issues with the genre names. For example, “Comedy” and “Thriller” show up twice in the summary because these genres were not always capitalized. This can be fixed.
In the example below, we use the unique() method to obtain a list of all genre names. We then define a list of new names where capitalization is consistent. Next, we use the replace() method to change the original name set to our new name set and fix the capitalization issues.
If we re-execute the code from above, you can see that the duplicates have been removed in the summary.
orig_names = list(movies_df["Genre"].unique())
print(orig_names)['Drama', 'Comedy', 'Sci-Fi', 'Independent', 'Thriller', 'Action', 'Family', 'Fantasy', 'Romance', 'Foreign', 'Horror', 'Documentary', 'Sports', 'War', 'Western', 'Classic', 'comedy', 'thriller']new_names = ['Drama', 'Comedy', 'Sci-Fi', 'Independent', 'Thriller', 'Action', 'Family',
'Fantasy', 'Romance', 'Foreign', 'Horror', 'Documentary', 'Sports', 'War',
'Western', 'Classic', 'Comedy', 'Thriller']
movies_df['Genre'] = movies_df['Genre'].replace(orig_names, new_names)
list(movies_df["Genre"].unique())
genre_stats = pd.DataFrame()
genre_stats["Count"] = movies_df.groupby("Genre")["My_Rating"].count()
genre_stats["Mean"] = movies_df.groupby("Genre")["My_Rating"].mean()
genre_stats["Max"] = movies_df.groupby("Genre")["My_Rating"].max()
genre_stats["Min"] = movies_df.groupby("Genre")["My_Rating"].min()
genre_stats["Range"] = genre_stats["Max"] - genre_stats["Min"]
print(genre_stats) Count Mean Max Min Range
Genre
Action 197 6.651878 9.87 0.67 9.20
Classic 47 6.741064 8.33 4.03 4.30
Comedy 276 6.735978 9.96 1.76 8.20
Documentary 78 7.283718 8.87 5.43 3.44
Drama 321 7.140561 9.99 0.88 9.11
Family 84 6.670714 9.86 1.01 8.85
Fantasy 21 7.473810 9.85 6.21 3.64
Foreign 157 7.453439 9.63 3.76 5.87
Horror 96 6.523021 9.60 1.65 7.95
Independent 190 6.940263 9.92 1.81 8.11
Romance 94 7.238723 9.84 3.45 6.39
Sci-Fi 62 6.872419 9.95 1.92 8.03
Sports 23 6.391304 8.78 2.94 5.84
Thriller 165 7.087818 9.91 3.11 6.80
War 27 6.542593 8.67 4.37 4.30
Western 14 6.960000 8.65 4.53 4.12The describe() method can be used to obtain a set of default summary statistics for a column of data. Combining this with groupby() allows for the calculation of statistics by group.
print(movies_df.groupby("Genre")["My_Rating"].describe()) count mean std min 25% 50% 75% max
Genre
Action 197.0 6.651878 1.687098 0.67 5.8400 6.950 7.7300 9.87
Classic 47.0 6.741064 0.903364 4.03 6.3000 6.790 7.2550 8.33
Comedy 276.0 6.735978 1.494788 1.76 5.9025 6.800 7.7025 9.96
Documentary 78.0 7.283718 0.778165 5.43 6.8725 7.290 7.8800 8.87
Drama 321.0 7.140561 1.292603 0.88 6.5800 7.150 7.8900 9.99
Family 84.0 6.670714 1.654381 1.01 5.9550 6.930 7.4875 9.86
Fantasy 21.0 7.473810 1.113838 6.21 6.6500 7.030 7.9400 9.85
Foreign 157.0 7.453439 0.778073 3.76 6.9500 7.410 7.9200 9.63
Horror 96.0 6.523021 1.491829 1.65 5.9000 6.770 7.2425 9.60
Independent 190.0 6.940263 1.208283 1.81 6.3300 6.880 7.6525 9.92
Romance 94.0 7.238723 1.471279 3.45 6.4925 7.085 8.2150 9.84
Sci-Fi 62.0 6.872419 1.901382 1.92 5.9300 7.040 8.2000 9.95
Sports 23.0 6.391304 1.591958 2.94 5.5800 6.770 7.3000 8.78
Thriller 165.0 7.087818 1.427839 3.11 6.3200 7.120 8.0300 9.91
War 27.0 6.542593 1.096518 4.37 5.8400 6.560 7.2100 8.67
Western 14.0 6.960000 1.411033 4.53 6.0400 7.070 8.2275 8.654.8 Concatenate and Merge
The Pandas concat() function is used to concatenate DataFrames that have the same columns. This is comparable to copying and pasting rows from two spreadsheets into a new spreadsheet. To demonstrate this, we extract two sets of rows using indexes. Next, we concatenate them into a new DataFrame.
movies_df = pd.read_csv("datasets/matts_movies.csv", sep=",", header=0, encoding="ISO-8859-1")
movies_df.columns =[column.replace(" ", "_") for column in movies_df.columns]
movies_sub1 = movies_df[100:500]
movies_sub2 = movies_df[900:1300]
movies_subc = pd.concat([movies_sub1, movies_sub2])
print(len(movies_subc))800Merge is comparable to table joins when using SQL. This requires the use of keys and the declaration of a joining method, such as “left”, “right”, “inner”, or “outer”.
In the example, we first create a unique ID by copying the row index to a column. We then break the data into two components, each containing the ID and a subset of the remaining columns. Next, we use the merge() function to merge the DataFrames using the “inner” method and the common “id” field. “inner” only returns rows that occur in both datasets. Since both DataFrames were derived from the same original DataFrame, they have identical rows, so the result would be the same as using “left”, where all rows from the left table are maintained even if they don’t occur in the right table, or “right”, where all rows from the right table are maintained even if they don’t occur in the left table.
In the second example, we use a query to separate out only movies released in 2008. When we perform a join with all of the data using the “inner” method, we only get back the common or shared rows.
Note that there is also a join() method that joins based on indexes. However, that is not demonstrated here.
movies_df = pd.read_csv("datasets/matts_movies.csv", sep=",", header=0, encoding="ISO-8859-1")
movies_df.columns =[column.replace(" ", "_") for column in movies_df.columns]
movies_df["id"] = movies_df.index
print(movies_df.head()) Movie_Name Director Release_Year ... Genre Own id
0 Almost Famous Cameron Crowe 2000 ... Drama Yes 0
1 The Shawshank Redemption Frank Darabont 1994 ... Drama Yes 1
2 Groundhog Day Harold Ramis 1993 ... Comedy Yes 2
3 Donnie Darko Richard Kelly 2001 ... Sci-Fi Yes 3
4 Children of Men Alfonso Cuaron 2006 ... Sci-Fi Yes 4
[5 rows x 7 columns]movies_first = movies_df[["id", "Movie_Name"]]
movies_second = movies_df[["id", "Director", "Release_Year"]]
movies_merge = pd.merge(movies_first, movies_second, how="inner", on="id")
print(movies_merge.head()) id Movie_Name Director Release_Year
0 0 Almost Famous Cameron Crowe 2000
1 1 The Shawshank Redemption Frank Darabont 1994
2 2 Groundhog Day Harold Ramis 1993
3 3 Donnie Darko Richard Kelly 2001
4 4 Children of Men Alfonso Cuaron 2006print(len(movies_merge))1852movies_third = movies_df.query('Release_Year == 2008')[["id", "Director", "Release_Year"]]
movies_merge2 = pd.merge(movies_first, movies_third, how="inner", on="id")
print(movies_merge2.head()) id Movie_Name Director Release_Year
0 11 Wall-E Andrew Stanton 2008
1 38 The Dark Knight Christopher Nolan 2008
2 100 In Bruges Martin McDonagh 2008
3 102 Gran Torino Clint Eastwood 2008
4 104 Let the Right One In Tomas Alfredson 2008print(len(movies_merge2))934.9 Polars
4.9.1 How Polars Differs from Pandas
Polars is a more recent DataFrame library that serves the same general purpose as Pandas: reading, querying, manipulating, and summarizing data tables. However, it was designed from scratch with performance in mind. Polars is written in the Rust programming language and stores data using the Apache Arrow columnar memory format. Here are some of the key differences between Polars and Pandas:
-
No index: Pandas DataFrames have an index that labels each row, which is used by methods such as
.loc. Polars DataFrames do not have an index. Rows are identified by their position only, and rows are selected by filtering based on column values. -
Expressions: In Polars, most operations are written as expressions that describe a calculation on one or more columns, such as
pl.col("My Rating").mean(). Expressions are provided to a small set of methods, such asselect(),filter(),with_columns(), andgroup_by().agg(). Once you understand this pattern, it can be applied consistently. -
No in-place modification: In Pandas, it is common to create or change a column using assignment (for example,
df["new_col"] = ...). In Polars, methods return a new DataFrame, and new or altered columns are created usingwith_columns(). - Lazy evaluation: In addition to executing operations immediately (eager mode), Polars offers a lazy mode in which a sequence of operations is defined first and then executed all at once. This allows Polars to optimize the query before running it, for example, by only reading in the columns and rows that are actually needed.
- Speed and memory: Polars uses all available processor cores by default and is often much faster and more memory efficient than Pandas, especially for large datasets.
-
Missing data: Polars represents missing values as
nullfor all data types.NaNis a separate, valid floating point value as opposed to an indicator of missing data. - Ecosystem: Pandas has been around much longer, and many libraries, including Seaborn, Statsmodels, and GeoPandas, are built around Pandas DataFrames. Fortunately, it is easy to convert between Pandas and Polars.
Table 4.1 compares some common operations in Pandas and Polars.
| Task | Pandas | Polars |
|---|---|---|
| Read a CSV file | pd.read_csv() |
pl.read_csv() |
| Select columns | df[["a", "b"]] |
df.select("a", "b") |
| Filter rows |
df[df["a"] > 5] or df.query()
|
df.filter(pl.col("a") > 5) |
| Create a new column | df["c"] = df["a"] + df["b"] |
df.with_columns((pl.col("a") + pl.col("b")).alias("c")) |
| Group and summarize | df.groupby("g")["a"].mean() |
df.group_by("g").agg(pl.col("a").mean()) |
| Join tables | pd.merge() |
df.join() |
| Stack tables | pd.concat() |
pl.concat() |
| Remove missing values | df.dropna() |
df.drop_nulls() |
| Fill missing values | df.fillna() |
df.fill_null() |
It is common to assign Polars an alias of pl. Polars prints DataFrames as a formatted table with the data type of each column listed below the column name. By default, only 10 rows are printed. We use pl.Config.set_tbl_rows() to increase this to 20 rows.
import polars as pl
pl.Config.set_tbl_rows(20)<class 'polars.config.Config'>4.9.2 Reading Data
To compare the two libraries, we repeat several of the Pandas examples from above using the same movie dataset. Data are read from a CSV file using read_csv(). Similar to Pandas, we need to specify the character encoding for this file due to the use of special characters. The shape property returns the number of rows and columns, and the height property returns the number of rows. The schema property returns the column names and their associated data types.
movies_pl = pl.read_csv("datasets/matts_movies.csv", encoding="ISO-8859-1")
print(movies_pl.head(10))shape: (10, 6)
┌──────────────────────────┬─────────────────────┬──────────────┬───────────┬─────────────┬─────┐
│ Movie Name ┆ Director ┆ Release Year ┆ My Rating ┆ Genre ┆ Own │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ str ┆ i64 ┆ f64 ┆ str ┆ str │
╞══════════════════════════╪═════════════════════╪══════════════╪═══════════╪═════════════╪═════╡
│ Almost Famous ┆ Cameron Crowe ┆ 2000 ┆ 9.99 ┆ Drama ┆ Yes │
│ The Shawshank Redemption ┆ Frank Darabont ┆ 1994 ┆ 9.98 ┆ Drama ┆ Yes │
│ Groundhog Day ┆ Harold Ramis ┆ 1993 ┆ 9.96 ┆ Comedy ┆ Yes │
│ Donnie Darko ┆ Richard Kelly ┆ 2001 ┆ 9.95 ┆ Sci-Fi ┆ Yes │
│ Children of Men ┆ Alfonso Cuaron ┆ 2006 ┆ 9.94 ┆ Sci-Fi ┆ Yes │
│ Annie Hall ┆ Woody Allen ┆ 1977 ┆ 9.93 ┆ Comedy ┆ Yes │
│ Rushmore ┆ Wes Anderson ┆ 1998 ┆ 9.92 ┆ Independent ┆ Yes │
│ Memento ┆ Christopher Nolan ┆ 2000 ┆ 9.91 ┆ Thriller ┆ Yes │
│ No Country for Old Men ┆ Joel and Ethan Coen ┆ 2007 ┆ 9.9 ┆ Thriller ┆ Yes │
│ Seven ┆ David Fincher ┆ 1995 ┆ 9.88 ┆ Thriller ┆ Yes │
└──────────────────────────┴─────────────────────┴──────────────┴───────────┴─────────────┴─────┘print(movies_pl.shape)(1852, 6)print(movies_pl.height)1852print(movies_pl.schema)Schema([('Movie Name', String), ('Director', String), ('Release Year', Int64), ('My Rating', Float64), ('Genre', String), ('Own', String)])Since columns are referenced using pl.col() and the column name as a string, spaces in column names are not as problematic as they are when using dot notation or query() with Pandas. However, to stay consistent with the Pandas examples, we replace the spaces with underscores using the rename() method. rename() accepts either a dictionary of old and new names or a function to apply to every column name, in this case a lambda function.
movies_pl = movies_pl.rename(lambda col: col.replace(" ", "_"))
print(movies_pl.columns)['Movie_Name', 'Director', 'Release_Year', 'My_Rating', 'Genre', 'Own']4.9.3 Query and Subset
Rows are subset using the filter() method and an expression that evaluates to True or False for each row. Columns are subset using the select() method. The three examples below are equivalent to the first set of Pandas query examples. Similar to Pandas, compound queries are written using & (and) or | (or), and each condition must be placed in parentheses.
#Example 1
just_dramas = movies_pl.filter(pl.col("Genre")=="Drama")
print(just_dramas.head(10))shape: (10, 6)
┌──────────────────────────┬───────────────────┬──────────────┬───────────┬───────┬─────┐
│ Movie_Name ┆ Director ┆ Release_Year ┆ My_Rating ┆ Genre ┆ Own │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ str ┆ i64 ┆ f64 ┆ str ┆ str │
╞══════════════════════════╪═══════════════════╪══════════════╪═══════════╪═══════╪═════╡
│ Almost Famous ┆ Cameron Crowe ┆ 2000 ┆ 9.99 ┆ Drama ┆ Yes │
│ The Shawshank Redemption ┆ Frank Darabont ┆ 1994 ┆ 9.98 ┆ Drama ┆ Yes │
│ Boyhood ┆ Richard Linklater ┆ 2014 ┆ 9.75 ┆ Drama ┆ No │
│ American Beauty ┆ Sam Mendes ┆ 1999 ┆ 9.7 ┆ Drama ┆ Yes │
│ Mystic River ┆ Clint Eastwood ┆ 2003 ┆ 9.56 ┆ Drama ┆ Yes │
│ Hannah and Her Sisters ┆ Woody Allen ┆ 1986 ┆ 9.47 ┆ Drama ┆ Yes │
│ In the Bedroom ┆ Todd Field ┆ 2001 ┆ 9.47 ┆ Drama ┆ Yes │
│ Big Fish ┆ Tim Burton ┆ 2003 ┆ 9.44 ┆ Drama ┆ Yes │
│ The Sweet Hereafter ┆ Atom Egoyan ┆ 1997 ┆ 9.42 ┆ Drama ┆ Yes │
│ Stand By Me ┆ Rob Reiner ┆ 1986 ┆ 9.4 ┆ Drama ┆ Yes │
└──────────────────────────┴───────────────────┴──────────────┴───────────┴───────┴─────┘print(just_dramas.height)321#Example 2
dramas_gt_9 = movies_pl.filter((pl.col("Genre")=="Drama") & (pl.col("My_Rating")>9.0))
print(dramas_gt_9.height)23#Example 3
dramas_gt_9_sub = movies_pl.filter((pl.col("Genre")=="Drama") & (pl.col("My_Rating")>9.0)).select("Movie_Name", "Director")
print(dramas_gt_9_sub.head(10))shape: (10, 2)
┌──────────────────────────┬───────────────────┐
│ Movie_Name ┆ Director │
│ --- ┆ --- │
│ str ┆ str │
╞══════════════════════════╪═══════════════════╡
│ Almost Famous ┆ Cameron Crowe │
│ The Shawshank Redemption ┆ Frank Darabont │
│ Boyhood ┆ Richard Linklater │
│ American Beauty ┆ Sam Mendes │
│ Mystic River ┆ Clint Eastwood │
│ Hannah and Her Sisters ┆ Woody Allen │
│ In the Bedroom ┆ Todd Field │
│ Big Fish ┆ Tim Burton │
│ The Sweet Hereafter ┆ Atom Egoyan │
│ Stand By Me ┆ Rob Reiner │
└──────────────────────────┴───────────────────┘New columns are created using with_columns(). In the example, we use concat_str() to combine the movie name and release year into a new column. The lit() function is used to include literal strings (in this case, the parentheses), and the alias() method assigns a name to the new column. Note that the release year is an integer, but concat_str() converts it to a string automatically. Since Polars does not modify DataFrames in place, we must save the result to a variable.
movies_pl2 = movies_pl.with_columns(
pl.concat_str([pl.col("Movie_Name"), pl.lit(" ("), pl.col("Release_Year"), pl.lit(")")]).alias("Title_Year")
)
print(movies_pl2.head(5))shape: (5, 7)
┌───────────────────┬────────────────┬──────────────┬───────────┬────────┬─────┬───────────────────┐
│ Movie_Name ┆ Director ┆ Release_Year ┆ My_Rating ┆ Genre ┆ Own ┆ Title_Year │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ str ┆ i64 ┆ f64 ┆ str ┆ str ┆ str │
╞═══════════════════╪════════════════╪══════════════╪═══════════╪════════╪═════╪═══════════════════╡
│ Almost Famous ┆ Cameron Crowe ┆ 2000 ┆ 9.99 ┆ Drama ┆ Yes ┆ Almost Famous │
│ ┆ ┆ ┆ ┆ ┆ ┆ (2000) │
│ The Shawshank ┆ Frank Darabont ┆ 1994 ┆ 9.98 ┆ Drama ┆ Yes ┆ The Shawshank │
│ Redemption ┆ ┆ ┆ ┆ ┆ ┆ Redemption (1994… │
│ Groundhog Day ┆ Harold Ramis ┆ 1993 ┆ 9.96 ┆ Comedy ┆ Yes ┆ Groundhog Day │
│ ┆ ┆ ┆ ┆ ┆ ┆ (1993) │
│ Donnie Darko ┆ Richard Kelly ┆ 2001 ┆ 9.95 ┆ Sci-Fi ┆ Yes ┆ Donnie Darko │
│ ┆ ┆ ┆ ┆ ┆ ┆ (2001) │
│ Children of Men ┆ Alfonso Cuaron ┆ 2006 ┆ 9.94 ┆ Sci-Fi ┆ Yes ┆ Children of Men │
│ ┆ ┆ ┆ ┆ ┆ ┆ (2006) │
└───────────────────┴────────────────┴──────────────┴───────────┴────────┴─────┴───────────────────┘Data are written to a file using a write_ method, such as write_csv().
dramas_gt_9_sub.write_csv("output/matts_movies_sub_polars.csv")4.9.4 Missing Values
To compare missing data handling, we repeat the Pandas example in which the “Drama” and “Fantasy” genres and ratings between 8.9 and 9.9 were recoded to missing. In Polars, conditional values are created using when(), then(), and otherwise(), which is similar to an If…Else statement. Providing None to then() results in a null value. The null_count() method returns the number of missing values in each column.
movies_null = movies_pl.with_columns(
pl.when(pl.col("Genre").is_in(["Drama", "Fantasy"])).then(None).otherwise(pl.col("Genre")).alias("Genre"),
pl.when(pl.col("My_Rating").is_between(8.9, 9.9)).then(None).otherwise(pl.col("My_Rating")).alias("My_Rating")
)
print(movies_null.head(10))shape: (10, 6)
┌──────────────────────────┬─────────────────────┬──────────────┬───────────┬─────────────┬─────┐
│ Movie_Name ┆ Director ┆ Release_Year ┆ My_Rating ┆ Genre ┆ Own │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ str ┆ i64 ┆ f64 ┆ str ┆ str │
╞══════════════════════════╪═════════════════════╪══════════════╪═══════════╪═════════════╪═════╡
│ Almost Famous ┆ Cameron Crowe ┆ 2000 ┆ 9.99 ┆ null ┆ Yes │
│ The Shawshank Redemption ┆ Frank Darabont ┆ 1994 ┆ 9.98 ┆ null ┆ Yes │
│ Groundhog Day ┆ Harold Ramis ┆ 1993 ┆ 9.96 ┆ Comedy ┆ Yes │
│ Donnie Darko ┆ Richard Kelly ┆ 2001 ┆ 9.95 ┆ Sci-Fi ┆ Yes │
│ Children of Men ┆ Alfonso Cuaron ┆ 2006 ┆ 9.94 ┆ Sci-Fi ┆ Yes │
│ Annie Hall ┆ Woody Allen ┆ 1977 ┆ 9.93 ┆ Comedy ┆ Yes │
│ Rushmore ┆ Wes Anderson ┆ 1998 ┆ 9.92 ┆ Independent ┆ Yes │
│ Memento ┆ Christopher Nolan ┆ 2000 ┆ 9.91 ┆ Thriller ┆ Yes │
│ No Country for Old Men ┆ Joel and Ethan Coen ┆ 2007 ┆ null ┆ Thriller ┆ Yes │
│ Seven ┆ David Fincher ┆ 1995 ┆ null ┆ Thriller ┆ Yes │
└──────────────────────────┴─────────────────────┴──────────────┴───────────┴─────────────┴─────┘print(movies_null.null_count())shape: (1, 6)
┌────────────┬──────────┬──────────────┬───────────┬───────┬─────┐
│ Movie_Name ┆ Director ┆ Release_Year ┆ My_Rating ┆ Genre ┆ Own │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ u32 ┆ u32 ┆ u32 ┆ u32 ┆ u32 ┆ u32 │
╞════════════╪══════════╪══════════════╪═══════════╪═══════╪═════╡
│ 0 ┆ 0 ┆ 0 ┆ 119 ┆ 342 ┆ 0 │
└────────────┴──────────┴──────────────┴───────────┴───────┴─────┘The drop_nulls() method removes all rows that have a missing value in any column. The fill_null() method replaces missing values with a value or with the result of an expression. In the example, we fill the missing genres with “Unknown Genre” and the missing ratings with the mean of the available ratings, which is equivalent to the Pandas examples above.
movies_drop = movies_null.drop_nulls()
print(movies_drop.height)1420movies_fill = movies_null.with_columns(
pl.col("Genre").fill_null("Unknown Genre"),
pl.col("My_Rating").fill_null(pl.col("My_Rating").mean())
)
print(movies_fill.head(10))shape: (10, 6)
┌──────────────────────────┬─────────────────────┬──────────────┬───────────┬───────────────┬─────┐
│ Movie_Name ┆ Director ┆ Release_Year ┆ My_Rating ┆ Genre ┆ Own │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ str ┆ i64 ┆ f64 ┆ str ┆ str │
╞══════════════════════════╪═════════════════════╪══════════════╪═══════════╪═══════════════╪═════╡
│ Almost Famous ┆ Cameron Crowe ┆ 2000 ┆ 9.99 ┆ Unknown Genre ┆ Yes │
│ The Shawshank Redemption ┆ Frank Darabont ┆ 1994 ┆ 9.98 ┆ Unknown Genre ┆ Yes │
│ Groundhog Day ┆ Harold Ramis ┆ 1993 ┆ 9.96 ┆ Comedy ┆ Yes │
│ Donnie Darko ┆ Richard Kelly ┆ 2001 ┆ 9.95 ┆ Sci-Fi ┆ Yes │
│ Children of Men ┆ Alfonso Cuaron ┆ 2006 ┆ 9.94 ┆ Sci-Fi ┆ Yes │
│ Annie Hall ┆ Woody Allen ┆ 1977 ┆ 9.93 ┆ Comedy ┆ Yes │
│ Rushmore ┆ Wes Anderson ┆ 1998 ┆ 9.92 ┆ Independent ┆ Yes │
│ Memento ┆ Christopher Nolan ┆ 2000 ┆ 9.91 ┆ Thriller ┆ Yes │
│ No Country for Old Men ┆ Joel and Ethan Coen ┆ 2007 ┆ 6.786509 ┆ Thriller ┆ Yes │
│ Seven ┆ David Fincher ┆ 1995 ┆ 6.786509 ┆ Thriller ┆ Yes │
└──────────────────────────┴─────────────────────┴──────────────┴───────────┴───────────────┴─────┘print(movies_fill.null_count())shape: (1, 6)
┌────────────┬──────────┬──────────────┬───────────┬───────┬─────┐
│ Movie_Name ┆ Director ┆ Release_Year ┆ My_Rating ┆ Genre ┆ Own │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ u32 ┆ u32 ┆ u32 ┆ u32 ┆ u32 ┆ u32 │
╞════════════╪══════════╪══════════════╪═══════════╪═══════╪═════╡
│ 0 ┆ 0 ┆ 0 ┆ 0 ┆ 0 ┆ 0 │
└────────────┴──────────┴──────────────┴───────────┴───────┴─────┘4.9.5 Grouping and Summarizing
Summary statistics can be calculated by providing multiple expressions to select(). Each expression returns a single value, so the result is a DataFrame with one row, similar to the mov_stats DataFrame created with Pandas. Note that expressions can be combined; the range is calculated by subtracting the minimum from the maximum.
mov_stats_pl = movies_pl.select(
pl.col("My_Rating").count().alias("Count"),
pl.col("My_Rating").mean().alias("Mean"),
pl.col("My_Rating").max().alias("Max"),
pl.col("My_Rating").min().alias("Min"),
(pl.col("My_Rating").max() - pl.col("My_Rating").min()).alias("Range")
)
print(mov_stats_pl)shape: (1, 5)
┌───────┬──────────┬──────┬──────┬───────┐
│ Count ┆ Mean ┆ Max ┆ Min ┆ Range │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ u32 ┆ f64 ┆ f64 ┆ f64 ┆ f64 │
╞═══════╪══════════╪══════╪══════╪═══════╡
│ 1852 ┆ 6.952527 ┆ 9.99 ┆ 0.67 ┆ 9.32 │
└───────┴──────────┴──────┴──────┴───────┘Grouped summaries are calculated using group_by() followed by agg(). In comparison to the Pandas example, all of the statistics are calculated in a single step as opposed to calling groupby() separately for each statistic. By default, Polars does not maintain a specific order of the groups, so we sort the result by genre using sort().
As with Pandas, “Comedy” and “Thriller” appear twice in the result due to inconsistent capitalization.
genre_stats_pl = (
movies_pl.group_by("Genre")
.agg(
pl.col("My_Rating").count().alias("Count"),
pl.col("My_Rating").mean().alias("Mean"),
pl.col("My_Rating").max().alias("Max"),
pl.col("My_Rating").min().alias("Min")
)
.with_columns((pl.col("Max") - pl.col("Min")).alias("Range"))
.sort("Genre")
)
print(genre_stats_pl)shape: (18, 6)
┌─────────────┬───────┬──────────┬──────┬──────┬───────┐
│ Genre ┆ Count ┆ Mean ┆ Max ┆ Min ┆ Range │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ u32 ┆ f64 ┆ f64 ┆ f64 ┆ f64 │
╞═════════════╪═══════╪══════════╪══════╪══════╪═══════╡
│ Action ┆ 197 ┆ 6.651878 ┆ 9.87 ┆ 0.67 ┆ 9.2 │
│ Classic ┆ 47 ┆ 6.741064 ┆ 8.33 ┆ 4.03 ┆ 4.3 │
│ Comedy ┆ 275 ┆ 6.738545 ┆ 9.96 ┆ 1.76 ┆ 8.2 │
│ Documentary ┆ 78 ┆ 7.283718 ┆ 8.87 ┆ 5.43 ┆ 3.44 │
│ Drama ┆ 321 ┆ 7.140561 ┆ 9.99 ┆ 0.88 ┆ 9.11 │
│ Family ┆ 84 ┆ 6.670714 ┆ 9.86 ┆ 1.01 ┆ 8.85 │
│ Fantasy ┆ 21 ┆ 7.47381 ┆ 9.85 ┆ 6.21 ┆ 3.64 │
│ Foreign ┆ 157 ┆ 7.453439 ┆ 9.63 ┆ 3.76 ┆ 5.87 │
│ Horror ┆ 96 ┆ 6.523021 ┆ 9.6 ┆ 1.65 ┆ 7.95 │
│ Independent ┆ 190 ┆ 6.940263 ┆ 9.92 ┆ 1.81 ┆ 8.11 │
│ Romance ┆ 94 ┆ 7.238723 ┆ 9.84 ┆ 3.45 ┆ 6.39 │
│ Sci-Fi ┆ 62 ┆ 6.872419 ┆ 9.95 ┆ 1.92 ┆ 8.03 │
│ Sports ┆ 23 ┆ 6.391304 ┆ 8.78 ┆ 2.94 ┆ 5.84 │
│ Thriller ┆ 164 ┆ 7.095976 ┆ 9.91 ┆ 3.11 ┆ 6.8 │
│ War ┆ 27 ┆ 6.542593 ┆ 8.67 ┆ 4.37 ┆ 4.3 │
│ Western ┆ 14 ┆ 6.96 ┆ 8.65 ┆ 4.53 ┆ 4.12 │
│ comedy ┆ 1 ┆ 6.03 ┆ 6.03 ┆ 6.03 ┆ 0.0 │
│ thriller ┆ 1 ┆ 5.75 ┆ 5.75 ┆ 5.75 ┆ 0.0 │
└─────────────┴───────┴──────────┴──────┴──────┴───────┘Polars provides string methods through the str namespace. As an alternative to the approach used with Pandas, in which we defined a list of corrected names, we use str.to_titlecase() to capitalize the first letter of each word in the genre names. Once the names are fixed, the duplicates are removed from the summary.
movies_pl = movies_pl.with_columns(pl.col("Genre").str.to_titlecase())
print(movies_pl["Genre"].unique().sort().to_list())['Action', 'Classic', 'Comedy', 'Documentary', 'Drama', 'Family', 'Fantasy', 'Foreign', 'Horror', 'Independent', 'Romance', 'Sci-Fi', 'Sports', 'Thriller', 'War', 'Western']genre_stats_pl = (
movies_pl.group_by("Genre")
.agg(
pl.col("My_Rating").count().alias("Count"),
pl.col("My_Rating").mean().alias("Mean"),
pl.col("My_Rating").max().alias("Max"),
pl.col("My_Rating").min().alias("Min")
)
.with_columns((pl.col("Max") - pl.col("Min")).alias("Range"))
.sort("Genre")
)
print(genre_stats_pl)shape: (16, 6)
┌─────────────┬───────┬──────────┬──────┬──────┬───────┐
│ Genre ┆ Count ┆ Mean ┆ Max ┆ Min ┆ Range │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ u32 ┆ f64 ┆ f64 ┆ f64 ┆ f64 │
╞═════════════╪═══════╪══════════╪══════╪══════╪═══════╡
│ Action ┆ 197 ┆ 6.651878 ┆ 9.87 ┆ 0.67 ┆ 9.2 │
│ Classic ┆ 47 ┆ 6.741064 ┆ 8.33 ┆ 4.03 ┆ 4.3 │
│ Comedy ┆ 276 ┆ 6.735978 ┆ 9.96 ┆ 1.76 ┆ 8.2 │
│ Documentary ┆ 78 ┆ 7.283718 ┆ 8.87 ┆ 5.43 ┆ 3.44 │
│ Drama ┆ 321 ┆ 7.140561 ┆ 9.99 ┆ 0.88 ┆ 9.11 │
│ Family ┆ 84 ┆ 6.670714 ┆ 9.86 ┆ 1.01 ┆ 8.85 │
│ Fantasy ┆ 21 ┆ 7.47381 ┆ 9.85 ┆ 6.21 ┆ 3.64 │
│ Foreign ┆ 157 ┆ 7.453439 ┆ 9.63 ┆ 3.76 ┆ 5.87 │
│ Horror ┆ 96 ┆ 6.523021 ┆ 9.6 ┆ 1.65 ┆ 7.95 │
│ Independent ┆ 190 ┆ 6.940263 ┆ 9.92 ┆ 1.81 ┆ 8.11 │
│ Romance ┆ 94 ┆ 7.238723 ┆ 9.84 ┆ 3.45 ┆ 6.39 │
│ Sci-Fi ┆ 62 ┆ 6.872419 ┆ 9.95 ┆ 1.92 ┆ 8.03 │
│ Sports ┆ 23 ┆ 6.391304 ┆ 8.78 ┆ 2.94 ┆ 5.84 │
│ Thriller ┆ 165 ┆ 7.087818 ┆ 9.91 ┆ 3.11 ┆ 6.8 │
│ War ┆ 27 ┆ 6.542593 ┆ 8.67 ┆ 4.37 ┆ 4.3 │
│ Western ┆ 14 ┆ 6.96 ┆ 8.65 ┆ 4.53 ┆ 4.12 │
└─────────────┴───────┴──────────┴──────┴──────┴───────┘Similar to Pandas, the describe() method returns a set of default summary statistics.
print(movies_pl.select("Release_Year", "My_Rating").describe())shape: (9, 3)
┌────────────┬──────────────┬───────────┐
│ statistic ┆ Release_Year ┆ My_Rating │
│ --- ┆ --- ┆ --- │
│ str ┆ f64 ┆ f64 │
╞════════════╪══════════════╪═══════════╡
│ count ┆ 1852.0 ┆ 1852.0 │
│ null_count ┆ 0.0 ┆ 0.0 │
│ mean ┆ 1999.191145 ┆ 6.952527 │
│ std ┆ 14.946878 ┆ 1.402167 │
│ min ┆ 1921.0 ┆ 0.67 │
│ 25% ┆ 1995.0 ┆ 6.32 │
│ 50% ┆ 2004.0 ┆ 7.02 │
│ 75% ┆ 2009.0 ┆ 7.85 │
│ max ┆ 2014.0 ┆ 9.99 │
└────────────┴──────────────┴───────────┘4.9.6 Concatenate and Join
Tables with the same columns are concatenated using pl.concat(). Rows can be extracted by position using bracket notation, as in the Pandas example, or using slice(), which accepts a starting position and the number of rows to extract.
movies_sub1 = movies_pl.slice(100, 400)
movies_sub2 = movies_pl[900:1300]
movies_subc = pl.concat([movies_sub1, movies_sub2])
print(movies_subc.height)800Table joins are performed using the join() method. Since Polars DataFrames do not have an index, we use with_row_index() to add a column containing the row number, which serves as a unique ID. We then repeat the Pandas example in which the movies released in 2008 are joined to all of the movies using an inner join. Other options for the how argument include “left”, “right”, and “full”.
movies_pl = movies_pl.with_row_index("id")
movies_first = movies_pl.select("id", "Movie_Name")
movies_third = movies_pl.filter(pl.col("Release_Year")==2008).select("id", "Director", "Release_Year")
movies_join = movies_first.join(movies_third, on="id", how="inner")
print(movies_join.head())shape: (5, 4)
┌─────┬──────────────────────┬───────────────────┬──────────────┐
│ id ┆ Movie_Name ┆ Director ┆ Release_Year │
│ --- ┆ --- ┆ --- ┆ --- │
│ u32 ┆ str ┆ str ┆ i64 │
╞═════╪══════════════════════╪═══════════════════╪══════════════╡
│ 11 ┆ Wall-E ┆ Andrew Stanton ┆ 2008 │
│ 38 ┆ The Dark Knight ┆ Christopher Nolan ┆ 2008 │
│ 100 ┆ In Bruges ┆ Martin McDonagh ┆ 2008 │
│ 102 ┆ Gran Torino ┆ Clint Eastwood ┆ 2008 │
│ 104 ┆ Let the Right One In ┆ Tomas Alfredson ┆ 2008 │
└─────┴──────────────────────┴───────────────────┴──────────────┘print(movies_join.height)934.9.7 Lazy Evaluation
All of the examples above used Polars in eager mode, in which each step is executed immediately. In lazy mode, operations are combined into a query plan that is optimized and then executed when collect() is called. A DataFrame can be converted to a LazyFrame using the lazy() method. Data can also be read directly into a LazyFrame using scan_csv(); however, scan_csv() only supports UTF-8 encoded files, so we use lazy() here.
In the example, we calculate the mean rating and number of dramas for each release year. The explain() method prints the optimized query plan. Note that Polars has determined that only three of the columns are needed for the query, so the remaining columns are not processed. The pl.len() function returns the number of rows in each group. For a dataset of this size, the speed difference is not noticeable; however, these optimizations can be very beneficial when working with large datasets.
drama_by_year = (
movies_pl.lazy()
.filter(pl.col("Genre")=="Drama")
.group_by("Release_Year")
.agg(pl.col("My_Rating").mean().alias("Mean_Rating"), pl.len().alias("Count"))
.sort("Release_Year", descending=True)
)
print(drama_by_year.explain())SORT BY [descending: [true]] [col("Release_Year")]
AGGREGATE[maintain_order: false]
[col("My_Rating").mean().alias("Mean_Rating"), len().alias("Count")] BY [col("Release_Year")]
FROM
simple π 2/2 ["Release_Year", "My_Rating"]
FILTER col("Genre") == "Drama"
FROM
DF ["id", "Movie_Name", "Director", "Release_Year", ...]; PROJECT["Release_Year", "My_Rating", "Genre"] 3/7 COLUMNSprint(drama_by_year.collect().head(10))shape: (10, 3)
┌──────────────┬─────────────┬───────┐
│ Release_Year ┆ Mean_Rating ┆ Count │
│ --- ┆ --- ┆ --- │
│ i64 ┆ f64 ┆ u32 │
╞══════════════╪═════════════╪═══════╡
│ 2014 ┆ 9.75 ┆ 1 │
│ 2013 ┆ 7.682727 ┆ 22 │
│ 2012 ┆ 6.914 ┆ 10 │
│ 2011 ┆ 6.676522 ┆ 23 │
│ 2010 ┆ 7.254545 ┆ 11 │
│ 2009 ┆ 6.9375 ┆ 16 │
│ 2008 ┆ 7.7325 ┆ 16 │
│ 2007 ┆ 7.161053 ┆ 19 │
│ 2006 ┆ 7.065 ┆ 18 │
│ 2005 ┆ 6.65 ┆ 12 │
└──────────────┴─────────────┴───────┘4.9.8 Converting Between Polars and Pandas
Since many libraries expect Pandas DataFrames, it is useful to be able to convert between the two. The to_pandas() method converts a Polars DataFrame to a Pandas DataFrame, and pl.from_pandas() converts a Pandas DataFrame to a Polars DataFrame. These conversions require the pyarrow library, which is included in the pybook environment. Polars DataFrames can also be converted to NumPy arrays using to_numpy().
movies_pd = movies_pl.to_pandas()
print(type(movies_pd))<class 'pandas.DataFrame'>movies_pl3 = pl.from_pandas(movies_df)
print(type(movies_pl3))<class 'polars.dataframe.frame.DataFrame'>print(movies_pl3.shape)(1852, 7)4.10 Concluding Remarks
There are many more topics that could be explored relating to Pandas. However, we have found the methods and techniques discussed here to be the ones we use most often and that are most useful. Polars offers an alternative to Pandas that is often faster and has a consistent, expression-based syntax. Since Pandas is more widely supported by other libraries, including those used in the remainder of this book, we primarily use Pandas moving forward. However, if you work with large datasets, Polars is worth exploring.
For more examples and details, please consult the documentation for Pandas and Polars.
If you need to perform a specific task that we did not cover, search for it online! Python and Pandas have a large user base, so it is likely that you will find examples similar to your needs with a simple search.
In Chapter 5, we discuss methods for graphing and visualizing data using matplotlib, Seaborn, and Pandas.
4.11 Questions
- Explain the difference between a Series and a DataFrame.
- Explain the difference between the
locandilocindexers. - Explain the difference between bracket notation and dot notation for referencing columns. When can dot notation not be used?
- How are missing values represented in Pandas? Explain the purpose of the
dropna()andfillna()methods. - Explain what the
groupby()method does and how it is combined with a summary statistic. - Explain the difference between concatenating and merging DataFrames. How does an inner join differ from a left join?
- List three differences between Pandas and Polars.
- Explain the difference between eager and lazy evaluation. Why can lazy evaluation be faster?
4.12 Exercises
Exercise 4.1: Marvel vs. DC Comparison
30 Points scaled to 20 Points
Introduction
This assignment makes use of a dataset that is made available on Kaggle (https://www.kaggle.com/datasets/jcraggy/marvel-vs-dc-imdb-rotten-tomatoes), which consists of data relating to DC and Marvel movies. The dataset has been provided in the datasets folder of the book download (the link above may no longer be active). In this assignment, you will only use movies that were released during or after the year 2000. So, you will need to filter the dataset prior to answering the questions. Your goal is to compare the DC and Marvel franchises by answering the 15 posed questions. Note that the questions assist you in determining what fields to use. The “entity” field differentiates the two groups: “MARVEL” and “DC”.
Objectives
Query and filter datasets
Obtain summary statistics
Compare groups
Deliverables
- Jupyter Notebook (Python) or R Markdown file (R) with all code and answers to the questions embedded. Files can be rendered to HTML webpages if your instructor requires this. Questions should be stated and be answered within Markdown cells.
Questions
This assignment can be conducted using either Python or R, whichever you prefer or whichever your instructor requires. Generate code to perform the following tasks or answer the questions posed.
*Only use movies released during or after the year 2000 in this assignment.
Question 1. How many of the movies in the original table were released during or after 2000 (“year”)? (2 Points)
Question 2. How many movies did DC release during or after 2000? How many movies did Marvel release during or after 2000? (2 Points)
Question 3. Does DC or Marvel have the highest average rating on IMDB (“imdb_rating”)? (2 Points)
Question 4. Was the lowest rated movie on IMDB (“imdb_rating”) a DC or a Marvel movie? (2 Points)
Question 5. Was the highest rated movie on IMDB (“imdb_rating”) a DC or a Marvel movie? (2 Points)
Question 6. Does DC or Marvel have the highest average critic’s rating on Rotten Tomatoes (“tomato_meter”)? (2 Points)
Question 7. Does DC or Marvel have the highest average audience rating on Rotten Tomatoes (“tom_aud_score”)? (2 Points)
Question 8. Which movie had the largest difference between the critic’s score (“tomato_meter”) and audience score (“tom_aud_score”) on Rotten Tomatoes in which the audience score was higher than the critic’s score? (2 Points)
Question 9. Which movie had the largest difference between the critic’s score (“tomato_meter”) and audience score (“tom_aud_score”) on Rotten Tomatoes in which the audience score was lower than the critic’s score? (2 Points)
Question 10. From the movies included in the database and those released during or after 2000, did DC or Marvel have the highest total gross earnings (“imdb_gross”)? (2 Points)
Question 11. From the movies included in the database and those released during or after 2000, did DC or Marvel have the highest earning movie and what was the movie (“imdb_gross”)? (2 Points)
Question 12. Does DC or Marvel movies have a longer average runtime (“runtime”)? (2 Points)
Question 13. Does DC or Marvel have the larger total runtime for all movies released during or after 2000 (“runtime”)? (2 Points)
Question 14. Calculate the gross earnings per runtime for DC and Marvel (total gross earnings (“imdb_gross”) divided by total runtime (“runtime”)). Does DC or Marvel have the highest gross earnings per unit runtime? (2 Points)
Question 15. Does Marvel of DC have a higher percentage of movies with a PG-13 rating vs. all other ratings (“mpa_rating”)? (2 Points)
Exercise 4.2: US County Summary
30 Points scaled to 20 Points
Introduction
This assignment asks you to answer a series of questions relating to county-level statistics for the contiguous United States. You will make use of the us_counties_data.csv dataset. Make sure to read the associated description of the data (us_counties_data_ DESCRIPTION.pdf) prior to undertaking the assignment. The description file explains the fields available and provides the data source.
Objectives
Query and filter datasets
Obtain summary statistics
Compare groups
Deliverables
- Jupyter Notebook (Python) or R Markdown file (R) with all code and answers to the questions embedded. Files can be rendered to HTML webpages if your instructor requires this. Questions should be stated and be answered within Markdown cells.
Questions
This assignment can be conducted using either Python or R, whichever you prefer or your instructor requires. Generate code to perform the following tasks or answer the questions posed.
Question 1: What county has the highest recorded mean elevation? (2 Points)
Question 2: What state has the lowest mean county elevation? (2 Points)
Question 3: How many counties have a mean elevation greater than 1,000 meters? (2 Points)
Question 4: How many counties have a mean elevation greater than 1,000 meters and a percent forest cover greater than 40%? (2 Points)
Question 5: How many counties have a mean elevation lower than 100 meters and a percent developed larger than 35%? (2 Points)
Question 6: Which subregion of the country has the largest number of counties? (2 Points)
Question 7: Which subregion of the country has the highest average county mean annual temperature? (2 Points)
Question 8: Does Nebraska or Kansas have the highest average county percent cropland? (2 Points)
Question 9: How many counties have a percent cropland greater than 25%, a mean temperature higher than 10°Celsius, and are not in the West South Central subregion? (2 Points)
Question 10: Which county in the country has the highest percent cropland? (2 Points)
Question 11: Which county in the country has the highest percent developed land? (2 Points)
Question 12: Which subregion has the highest total population? (2 Points)
Question 13: Which state has a higher percent of households with internet access (all internet access, not just broadband), West Virginia or North Dakota? (2 Points)
Question 14: Of all the counties with a mean elevation greater than 1,200 meters, which has the highest total annual precipitation? (2 Points)
Question 15: What county has the largest difference between its annual lowest and highest temperatures? (2 Points)