# These two lines are added so that not all the warnings are rendered in the cell.
# We do this not to confuse you during your learning journey with some of the warnings but
# normally you would want them turned on since they can tell you something about things that might not be working as expected.
import warnings
warnings.filterwarnings('ignore')6 Lab: Pandas
Last week we loaded data using pandas’ read.csv() method, but Pandas can do way more than that. Pandas is an essential library for data science, as it provides the data structures (nameley, series -1D- and data frames -2D) and operations for manipulating tabular data.
In this unit’s labs we will be using pandas to read in, process and explore data (this notebook); create (basic) visualisations capabilities (Chapter 7) as well as transforming data (Chapter 8). To do so, we will be using a dataset about “The Office”.
6.1 Dataset
The Office is a humoristic TV series originally created in 2001 by Ricky Gervais and Stephen Merchant that has received several adaptations. The dataset that we will be using contains information (i.e., title, date and ratings from IMBD) about every episode of the 9 seaons of the very successful USA’s adaptation aired between 2005 and 2013.
The dataset is stored in a csv file that has the following columns: season, episode, title, imdb_rating, total_votes, air_date.
6.2 Starting
To work with the dataset we will need to import pandas so we can use every feature provided by the library, as well as loading the dataset stored in the office_ratings.csv.
# this following import will always be needed whenever you want to work with Pandas.
import pandas as pd
df = pd.read_csv('data/raw/office_ratings.csv', encoding='UTF-8')df.info()<class 'pandas.DataFrame'>
RangeIndex: 188 entries, 0 to 187
Data columns (total 6 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 season 188 non-null int64
1 episode 188 non-null int64
2 title 188 non-null str
3 imdb_rating 188 non-null float64
4 total_votes 188 non-null int64
5 air_date 188 non-null str
dtypes: float64(1), int64(3), str(2)
memory usage: 8.9 KB6.3 Help!
Python has inbuilt documentation. To access this add a ? before an object or method.
Note
The output of the help function has been omitted in the handbook. Please run the cells in your notebook to read the different outputs
For example, our dataframe
?dfor the dtypes property
Tip
Properties of object are values associated with the object and are not called with a () at the end.
?df.dtypesThe info method for dataframes.
?df.infoIf you would like to get help in-line like the examples above, that can give a very long help message that might not be always convenient. If you like, you can try to get the help for this following function like this:
?pd.read_csv
However, the below will be quite long – it provides you the various arguments (options) you can use with the method.
Instead of this approach, a much better way to get help is to refer to the documentation and the API of the library that you are using. For instance, for read_csv(), this page is much more useful – https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html
We recommend that you use a search engine very frequently.
?pd.read_csvThe Pandas documentation is rather good. Relevent to our below work is:
- What kind of data does pandas handle?
- How to calculate summary statistics?
- How to create plots in pandas?
- How to handle time series data with ease?
I also found a rather nice series of lessons a kind person put together. There are lots of online tutorials which will help you.
6.4 Structure
In Section 2.3 we introduced Python’s data types as well as how to use the function type() to retrieve an object’s data type. Pandas expands python’s data types by creating a new one called data frame
Do you remember what are Python’s data types? You can refer to Section 2.3 for a refresher and to know more about them.
Data frames
Data frames are 2-dimensional data structures that store information in columns and rows, very much like data is stored in a spreadsheet or a database. Typically, every column will contain variables (or sometimes called attributes) whereas every row represents an observation. This is known as wide data frames, as opposed to long data frames.
In pandas, every column has a name and rows can be named, too.
So let’s check the what our newly created object’s (df) data type:
type(df)pandas.DataFrameUnsurprisingly, df is a `DataFrame`` object, provided by pandas.
The DataFrame object has lots of built in methods and attributes.
The info method gives us information about datatypes, dimensions and the presence of null values in our dataframe. Let’s see how can we use it and what information is returned:
df.info()<class 'pandas.DataFrame'>
RangeIndex: 188 entries, 0 to 187
Data columns (total 6 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 season 188 non-null int64
1 episode 188 non-null int64
2 title 188 non-null str
3 imdb_rating 188 non-null float64
4 total_votes 188 non-null int64
5 air_date 188 non-null str
dtypes: float64(1), int64(3), str(2)
memory usage: 8.9 KBWe can just dtypes to check the data types of every variable in the data frame.
df.dtypesseason int64
episode int64
title str
imdb_rating float64
total_votes int64
air_date str
dtype: objectOr just the dimensions (e.g., rows and columns).
df.shape(188, 6)In this case, there are only 188 rows. But for larger datasets we might want to look at the head (top 5) and tail (bottom 5) rows using .head() and .tail(), respectively.
df.head()| season | episode | title | imdb_rating | total_votes | air_date | |
|---|---|---|---|---|---|---|
| 0 | 1 | 1 | Pilot | 7.6 | 3706 | 2005-03-24 |
| 1 | 1 | 2 | Diversity Day | 8.3 | 3566 | 2005-03-29 |
| 2 | 1 | 3 | Health Care | 7.9 | 2983 | 2005-04-05 |
| 3 | 1 | 4 | The Alliance | 8.1 | 2886 | 2005-04-12 |
| 4 | 1 | 5 | Basketball | 8.4 | 3179 | 2005-04-19 |
df.tail()| season | episode | title | imdb_rating | total_votes | air_date | |
|---|---|---|---|---|---|---|
| 183 | 9 | 19 | Stairmageddon | 8.0 | 1484 | 2013-04-11 |
| 184 | 9 | 20 | Paper Airplane | 8.0 | 1482 | 2013-04-25 |
| 185 | 9 | 21 | Livin' the Dream | 8.9 | 2041 | 2013-05-02 |
| 186 | 9 | 22 | A.A.R.M. | 9.3 | 2860 | 2013-05-09 |
| 187 | 9 | 23 | Finale | 9.7 | 7934 | 2013-05-16 |
6.5 Summary
To get an overview of our data we can ask Python to ‘describe our (numeric) data’
df.describe()| season | episode | imdb_rating | total_votes | |
|---|---|---|---|---|
| count | 188.000000 | 188.000000 | 188.000000 | 188.000000 |
| mean | 5.468085 | 11.877660 | 8.257447 | 2126.648936 |
| std | 2.386245 | 7.024855 | 0.538067 | 787.098275 |
| min | 1.000000 | 1.000000 | 6.700000 | 1393.000000 |
| 25% | 3.000000 | 6.000000 | 7.900000 | 1631.500000 |
| 50% | 6.000000 | 11.500000 | 8.200000 | 1952.500000 |
| 75% | 7.250000 | 18.000000 | 8.600000 | 2379.000000 |
| max | 9.000000 | 26.000000 | 9.700000 | 7934.000000 |
or we can pull out specific statistics for numeric columns.
df.mean()--------------------------------------------------------------------------- TypeError Traceback (most recent call last) Cell In[15], line 1 ----> 1 df.mean() File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/util/_decorators.py:336, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs) 330 if len(args) > num_allow_args: 331 warnings.warn( 332 msg.format(arguments=_format_argument_list(allow_args)), 333 klass, 334 stacklevel=find_stack_level(), 335 ) --> 336 return func(*args, **kwargs) File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/frame.py:14475, in DataFrame.mean(self, axis, skipna, numeric_only, **kwargs) 14471 >>> df.mean(numeric_only=True) 14472 a 1.5 14473 dtype: float64 14474 """ > 14475 result = super().mean( 14476 axis=axis, skipna=skipna, numeric_only=numeric_only, **kwargs 14477 ) 14478 if isinstance(result, Series): File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/generic.py:11819, in NDFrame.mean(self, axis, skipna, numeric_only, **kwargs) 11815 skipna: bool = True, 11816 numeric_only: bool = False, 11817 **kwargs, 11818 ) -> Series | float: > 11819 return self._stat_function( 11820 "mean", nanops.nanmean, axis, skipna, numeric_only, **kwargs 11821 ) File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/generic.py:11773, in NDFrame._stat_function(self, name, func, axis, skipna, numeric_only, **kwargs) 11769 nv.validate_func(name, (), kwargs) 11770 11771 validate_bool_kwarg(skipna, "skipna", none_allowed=False) 11772 > 11773 return self._reduce( 11774 func, name=name, axis=axis, skipna=skipna, numeric_only=numeric_only 11775 ) File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/frame.py:13635, in DataFrame._reduce(self, op, name, axis, skipna, numeric_only, filter_type, **kwds) 13631 df = df.T 13632 13633 # After possibly _get_data and transposing, we are now in the 13634 # simple case where we can use BlockManager.reduce > 13635 res = df._mgr.reduce(blk_func) 13636 out = df._constructor_from_mgr(res, axes=res.axes).iloc[0] 13637 out.name = None 13638 if out_dtype is not None and out.dtype != "boolean": File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/internals/managers.py:1694, in BlockManager.reduce(self, func) 1691 # If 2D, we assume that we're operating column-wise 1692 assert self.ndim == 2 -> 1694 res_blocks = [blk.reduce(func) for blk in self.blocks] 1695 index = default_index(1) # placeholder 1696 # shallow copy self.items not needed because DataFrame._reduce does a getitem File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/internals/blocks.py:358, in Block.reduce(self, func) 352 @final 353 def reduce(self, func) -> Block: 354 # We will apply the function and reshape the result into a single-row 355 # Block with the same mgr_locs; squeezing will be done at a higher level 356 assert self.ndim == 2 --> 358 result = func(self.values) 360 if self.values.ndim == 1: 361 res_values = result File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/frame.py:13563, in DataFrame._reduce.<locals>.blk_func(values, axis) 13559 def blk_func(values, axis: Axis = 1): 13560 if isinstance(values, ExtensionArray): 13561 if not is_1d_only_ea_dtype(values.dtype): 13562 return values._reduce(name, axis=1, skipna=skipna, **kwds) > 13563 return values._reduce(name, skipna=skipna, keepdims=True, **kwds) 13564 else: 13565 return op(values, axis=axis, skipna=skipna, **kwds) File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/arrays/string_.py:968, in StringArray._reduce(self, name, skipna, keepdims, axis, **kwargs) 965 return self._from_sequence([result], dtype=self.dtype) 966 return result --> 968 raise TypeError(f"Cannot perform reduction '{name}' with string dtype") TypeError: Cannot perform reduction 'mean' with string dtype
Note the error triggered above due to pandas attempting to calculate the mean of the wrong type (i.e. non-numeric values). We can address that by only computing the mean of numeric values (see below):
df.mean(numeric_only=True)season 5.468085
episode 11.877660
imdb_rating 8.257447
total_votes 2126.648936
dtype: float64or the sum of every value within the same column:
df.sum()season 1028
episode 2233
title PilotDiversity DayHealth CareThe AllianceBaske...
imdb_rating 1552.4
total_votes 399810
air_date 2005-03-242005-03-292005-04-052005-04-122005-0...
dtype: objectSimilarly to what happened with mean(), sum() is adding all values in every observation of every attribute, regardless of their type, but this time is not producing an error. Can you see what happens with strings? And with dates?
Again, we can force to use numeric values only:
df.sum(numeric_only=True)season 1028.0
episode 2233.0
imdb_rating 1552.4
total_votes 399810.0
dtype: float646.6 Subsetting
Often times we may have a large dataset and we only need to work with just a part of it (a subset) consisting of certain columns and/or rows. Selecting specific columns and/or rows is known as subsetting.
6.6.1 Selecting columns
Because in pandas every column has a name, we can select columns by their name or their position.
6.6.1.1 Selecting by name
To select by name we will use the syntax df['<column_name>']. For example, if we wanted to select the ratings:
df['imdb_rating']0 7.6
1 8.3
2 7.9
3 8.1
4 8.4
...
183 8.0
184 8.0
185 8.9
186 9.3
187 9.7
Name: imdb_rating, Length: 188, dtype: float64or we could select the date in which the chapters were first aired:
df['air_date']0 2005-03-24
1 2005-03-29
2 2005-04-05
3 2005-04-12
4 2005-04-19
...
183 2013-04-11
184 2013-04-25
185 2013-05-02
186 2013-05-09
187 2013-05-16
Name: air_date, Length: 188, dtype: strWe can even select more than one column!
df[['imdb_rating', 'total_votes']]| imdb_rating | total_votes | |
|---|---|---|
| 0 | 7.6 | 3706 |
| 1 | 8.3 | 3566 |
| 2 | 7.9 | 2983 |
| 3 | 8.1 | 2886 |
| 4 | 8.4 | 3179 |
| ... | ... | ... |
| 183 | 8.0 | 1484 |
| 184 | 8.0 | 1482 |
| 185 | 8.9 | 2041 |
| 186 | 9.3 | 2860 |
| 187 | 9.7 | 7934 |
188 rows × 2 columns
Important
Did you notice that we used two sets of squared brackets ([[]])? This is needed because we need to passing a list of the column names to the __getitem__ method of the pandas dataframe object, and as you may remember from Section 2.3.2, this is the syntax used for lists (thank this stackoverflow question).
This is what we’d get otherwise:
df['imdb_rating', 'total_votes']--------------------------------------------------------------------------- KeyError Traceback (most recent call last) File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/indexes/base.py:3641, in Index.get_loc(self, key) 3640 try: -> 3641 return self._engine.get_loc(casted_key) 3642 except KeyError as err: File pandas/_libs/index.pyx:168, in pandas._libs.index.IndexEngine.get_loc() --> 168 'Could not get source, probably due dynamically evaluated source code.' File pandas/_libs/index.pyx:176, in pandas._libs.index.IndexEngine.get_loc() --> 176 'Could not get source, probably due dynamically evaluated source code.' File pandas/_libs/index.pyx:583, in pandas._libs.index.StringObjectEngine._check_type() --> 583 'Could not get source, probably due dynamically evaluated source code.' KeyError: ('imdb_rating', 'total_votes') The above exception was the direct cause of the following exception: KeyError Traceback (most recent call last) Cell In[22], line 1 ----> 1 df['imdb_rating', 'total_votes'] File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/frame.py:4378, in DataFrame.__getitem__(self, key) 4374 4375 if is_single_key: 4376 if self.columns.nlevels > 1: 4377 return self._getitem_multilevel(key) -> 4378 indexer = self.columns.get_loc(key) 4379 if is_integer(indexer): 4380 indexer = [indexer] 4381 else: File /opt/anaconda3/envs/IM939/lib/python3.14/site-packages/pandas/core/indexes/base.py:3648, in Index.get_loc(self, key) 3643 if isinstance(casted_key, slice) or ( 3644 isinstance(casted_key, abc.Iterable) 3645 and any(isinstance(x, slice) for x in casted_key) 3646 ): 3647 raise InvalidIndexError(key) from err -> 3648 raise KeyError(key) from err 3649 except TypeError: 3650 # If we have a listlike key, _check_indexing_error will raise 3651 # InvalidIndexError. Otherwise we fall through and re-raise 3652 # the TypeError. 3653 self._check_indexing_error(key) KeyError: ('imdb_rating', 'total_votes')
You can also check out the pandas documentation on indexing and selecting data.
We can also apply methods to subset, such as this one to get the average rating:
df['imdb_rating'].mean()np.float64(8.25744680851064)Or to calculate the total number of votes:
df['total_votes'].sum()np.int64(399810)Or a combination of multiple columns:
df[['imdb_rating', 'total_votes']].mean()imdb_rating 8.257447
total_votes 2126.648936
dtype: float646.6.1.2 Selecting by position
If we do not want to use column names, we can use iloc method by using the syntax <object>.iloc[<row slice>, <column slice>], where a slice is a range of numbers separated by a colon :. So, if we were to select the value in the 5th row and 3rd column (remember Python starts indexing from 0), we’d use:
df.iloc[4,2]'Basketball'But if we just wanted to select a column? In that case, we can use the same method but instead of specifiying a row, we will need to use : to indicate that we are selecting all the rows, such as:
df.iloc[:,2]0 Pilot
1 Diversity Day
2 Health Care
3 The Alliance
4 Basketball
...
183 Stairmageddon
184 Paper Airplane
185 Livin' the Dream
186 A.A.R.M.
187 Finale
Name: title, Length: 188, dtype: strConversely, if we just wanted to select all the columns from a given row, we’d use : on the right side of the , like this:
df.iloc[4,:]season 1
episode 5
title Basketball
imdb_rating 8.4
total_votes 3179
air_date 2005-04-19
Name: 4, dtype: objectWe can use negative values in indexes to indicate ‘from the end’. So, an index of [-10, :] returns the 10th from last row.
df.iloc[-10,:]season 9
episode 14
title Vandalism
imdb_rating 7.6
total_votes 1402
air_date 2013-01-31
Name: 178, dtype: objectInstead of using tail, we could ask for the last 5 rows with an index of [-5:, :]. I read : as ‘and everything else’ in these cases.
df.iloc[-5:,:]| season | episode | title | imdb_rating | total_votes | air_date | |
|---|---|---|---|---|---|---|
| 183 | 9 | 19 | Stairmageddon | 8.0 | 1484 | 2013-04-11 |
| 184 | 9 | 20 | Paper Airplane | 8.0 | 1482 | 2013-04-25 |
| 185 | 9 | 21 | Livin' the Dream | 8.9 | 2041 | 2013-05-02 |
| 186 | 9 | 22 | A.A.R.M. | 9.3 | 2860 | 2013-05-09 |
| 187 | 9 | 23 | Finale | 9.7 | 7934 | 2013-05-16 |
df.tail()| season | episode | title | imdb_rating | total_votes | air_date | |
|---|---|---|---|---|---|---|
| 183 | 9 | 19 | Stairmageddon | 8.0 | 1484 | 2013-04-11 |
| 184 | 9 | 20 | Paper Airplane | 8.0 | 1482 | 2013-04-25 |
| 185 | 9 | 21 | Livin' the Dream | 8.9 | 2041 | 2013-05-02 |
| 186 | 9 | 22 | A.A.R.M. | 9.3 | 2860 | 2013-05-09 |
| 187 | 9 | 23 | Finale | 9.7 | 7934 | 2013-05-16 |
Note that the row is shown on the left. That will stop you getting lost in slices of the data.
For the top ten rows
df.iloc[:10,:]| season | episode | title | imdb_rating | total_votes | air_date | |
|---|---|---|---|---|---|---|
| 0 | 1 | 1 | Pilot | 7.6 | 3706 | 2005-03-24 |
| 1 | 1 | 2 | Diversity Day | 8.3 | 3566 | 2005-03-29 |
| 2 | 1 | 3 | Health Care | 7.9 | 2983 | 2005-04-05 |
| 3 | 1 | 4 | The Alliance | 8.1 | 2886 | 2005-04-12 |
| 4 | 1 | 5 | Basketball | 8.4 | 3179 | 2005-04-19 |
| 5 | 1 | 6 | Hot Girl | 7.8 | 2852 | 2005-04-26 |
| 6 | 2 | 1 | The Dundies | 8.7 | 3213 | 2005-09-20 |
| 7 | 2 | 2 | Sexual Harassment | 8.2 | 2736 | 2005-09-27 |
| 8 | 2 | 3 | Office Olympics | 8.4 | 2742 | 2005-10-04 |
| 9 | 2 | 4 | The Fire | 8.4 | 2713 | 2005-10-11 |
Of course, we can run methods on these slices. We could, if we wanted to, calculate the mean imdb rating of only the first and last 100 episodes. Note the indexing starts at 0 so we want the column index of 3 (0:season, 1:episode, 2:title, 3:imdb_rating).
df.iloc[:100,3].mean()np.float64(8.483)df.iloc[-100:,3].mean()np.float64(8.062)If you are unsure how many rows you have then the count method comes to the rescue.
df.iloc[-100:,3].count()np.int64(100)df.describe()| season | episode | imdb_rating | total_votes | |
|---|---|---|---|---|
| count | 188.000000 | 188.000000 | 188.000000 | 188.000000 |
| mean | 5.468085 | 11.877660 | 8.257447 | 2126.648936 |
| std | 2.386245 | 7.024855 | 0.538067 | 787.098275 |
| min | 1.000000 | 1.000000 | 6.700000 | 1393.000000 |
| 25% | 3.000000 | 6.000000 | 7.900000 | 1631.500000 |
| 50% | 6.000000 | 11.500000 | 8.200000 | 1952.500000 |
| 75% | 7.250000 | 18.000000 | 8.600000 | 2379.000000 |
| max | 9.000000 | 26.000000 | 9.700000 | 7934.000000 |
So it looks like the last 100 episodes were less good than the first 100. I guess that is why it was cancelled.
Our data is organised by season. Looking at the average by season might help.
df[['season', 'imdb_rating']].groupby('season').mean()| imdb_rating | |
|---|---|
| season | |
| 1 | 8.016667 |
| 2 | 8.436364 |
| 3 | 8.573913 |
| 4 | 8.600000 |
| 5 | 8.492308 |
| 6 | 8.219231 |
| 7 | 8.316667 |
| 8 | 7.666667 |
| 9 | 7.956522 |
The above line groups our dataframe by values in the season column and then displays the mean for each group. Pretty nifty.
Season 8 looks pretty bad. We can look at just the rows for season 8.
df[df['season'] == 8]| season | episode | title | imdb_rating | total_votes | air_date | |
|---|---|---|---|---|---|---|
| 141 | 8 | 1 | The List | 8.2 | 1829 | 2011-09-22 |
| 142 | 8 | 2 | The Incentive | 8.2 | 1668 | 2011-09-29 |
| 143 | 8 | 3 | Lotto | 7.3 | 1601 | 2011-10-06 |
| 144 | 8 | 4 | Garden Party | 8.1 | 1717 | 2011-10-13 |
| 145 | 8 | 5 | Spooked | 7.6 | 1543 | 2011-10-27 |
| 146 | 8 | 6 | Doomsday | 7.8 | 1476 | 2011-11-03 |
| 147 | 8 | 7 | Pam's Replacement | 7.7 | 1563 | 2011-11-10 |
| 148 | 8 | 8 | Gettysburg | 7.0 | 1584 | 2011-11-17 |
| 149 | 8 | 9 | Mrs. California | 7.7 | 1553 | 2011-12-01 |
| 150 | 8 | 10 | Christmas Wishes | 8.0 | 1547 | 2011-12-08 |
| 151 | 8 | 11 | Trivia | 7.9 | 1488 | 2012-01-12 |
| 152 | 8 | 12 | Pool Party | 8.0 | 1612 | 2012-01-19 |
| 153 | 8 | 13 | Jury Duty | 7.5 | 1478 | 2012-02-02 |
| 154 | 8 | 14 | Special Project | 7.8 | 1432 | 2012-02-09 |
| 155 | 8 | 15 | Tallahassee | 7.9 | 1522 | 2012-02-16 |
| 156 | 8 | 16 | After Hours | 8.1 | 1567 | 2012-02-23 |
| 157 | 8 | 17 | Test the Store | 7.8 | 1478 | 2012-03-01 |
| 158 | 8 | 18 | Last Day in Florida | 7.8 | 1429 | 2012-03-08 |
| 159 | 8 | 19 | Get the Girl | 6.7 | 1642 | 2012-03-15 |
| 160 | 8 | 20 | Welcome Party | 7.2 | 1489 | 2012-04-12 |
| 161 | 8 | 21 | Angry Andy | 7.1 | 1585 | 2012-04-19 |
| 162 | 8 | 22 | Fundraiser | 7.1 | 1453 | 2012-04-26 |
| 163 | 8 | 23 | Turf War | 7.7 | 1393 | 2012-05-03 |
| 164 | 8 | 24 | Free Family Portrait Studio | 7.8 | 1464 | 2012-05-10 |
6.6.2 Filtering rows
We can filter rows matching some criteria by using the syntax <object>.loc[<criteria>]. So, if we wanted to filter all the episodes from the 8th season, we would do the following:
df.loc[df['season'] == 8]| season | episode | title | imdb_rating | total_votes | air_date | |
|---|---|---|---|---|---|---|
| 141 | 8 | 1 | The List | 8.2 | 1829 | 2011-09-22 |
| 142 | 8 | 2 | The Incentive | 8.2 | 1668 | 2011-09-29 |
| 143 | 8 | 3 | Lotto | 7.3 | 1601 | 2011-10-06 |
| 144 | 8 | 4 | Garden Party | 8.1 | 1717 | 2011-10-13 |
| 145 | 8 | 5 | Spooked | 7.6 | 1543 | 2011-10-27 |
| 146 | 8 | 6 | Doomsday | 7.8 | 1476 | 2011-11-03 |
| 147 | 8 | 7 | Pam's Replacement | 7.7 | 1563 | 2011-11-10 |
| 148 | 8 | 8 | Gettysburg | 7.0 | 1584 | 2011-11-17 |
| 149 | 8 | 9 | Mrs. California | 7.7 | 1553 | 2011-12-01 |
| 150 | 8 | 10 | Christmas Wishes | 8.0 | 1547 | 2011-12-08 |
| 151 | 8 | 11 | Trivia | 7.9 | 1488 | 2012-01-12 |
| 152 | 8 | 12 | Pool Party | 8.0 | 1612 | 2012-01-19 |
| 153 | 8 | 13 | Jury Duty | 7.5 | 1478 | 2012-02-02 |
| 154 | 8 | 14 | Special Project | 7.8 | 1432 | 2012-02-09 |
| 155 | 8 | 15 | Tallahassee | 7.9 | 1522 | 2012-02-16 |
| 156 | 8 | 16 | After Hours | 8.1 | 1567 | 2012-02-23 |
| 157 | 8 | 17 | Test the Store | 7.8 | 1478 | 2012-03-01 |
| 158 | 8 | 18 | Last Day in Florida | 7.8 | 1429 | 2012-03-08 |
| 159 | 8 | 19 | Get the Girl | 6.7 | 1642 | 2012-03-15 |
| 160 | 8 | 20 | Welcome Party | 7.2 | 1489 | 2012-04-12 |
| 161 | 8 | 21 | Angry Andy | 7.1 | 1585 | 2012-04-19 |
| 162 | 8 | 22 | Fundraiser | 7.1 | 1453 | 2012-04-26 |
| 163 | 8 | 23 | Turf War | 7.7 | 1393 | 2012-05-03 |
| 164 | 8 | 24 | Free Family Portrait Studio | 7.8 | 1464 | 2012-05-10 |
Understanding the criteria
To understand why we have to write the name of the dataframe twice, we can focus on the output provided by the filtering criteria only:
df['season'] == 90 False
1 False
2 False
3 False
4 False
...
183 True
184 True
185 True
186 True
187 True
Name: season, Length: 188, dtype: boolAs you can see, it returns a boolean series specifiying which rows are matching the criteria (True) and which ones are not (False)
As a side note, while writing the name of the dataframe twice may seem redundant, this means that we could filter rows based on other objects.
We can get an overview of the rating of all chapters within season 8 by:
df.loc[df['season'] == 8, 'imdb_rating'].describe()count 24.000000
mean 7.666667
std 0.405041
min 6.700000
25% 7.450000
50% 7.800000
75% 7.925000
max 8.200000
Name: imdb_rating, dtype: float64Generally pretty bad, but there is clearly one very disliked episode.
6.7 Adding columns
We can add new columns pretty simply.
df['x'] = 44
df.head()| season | episode | title | imdb_rating | total_votes | air_date | x | |
|---|---|---|---|---|---|---|---|
| 0 | 1 | 1 | Pilot | 7.6 | 3706 | 2005-03-24 | 44 |
| 1 | 1 | 2 | Diversity Day | 8.3 | 3566 | 2005-03-29 | 44 |
| 2 | 1 | 3 | Health Care | 7.9 | 2983 | 2005-04-05 | 44 |
| 3 | 1 | 4 | The Alliance | 8.1 | 2886 | 2005-04-12 | 44 |
| 4 | 1 | 5 | Basketball | 8.4 | 3179 | 2005-04-19 | 44 |
Our new column can be an operation on other columns
df['rating_div_total_votes'] = df['imdb_rating'] / df['total_votes']
df.head()| season | episode | title | imdb_rating | total_votes | air_date | x | rating_div_total_votes | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 1 | Pilot | 7.6 | 3706 | 2005-03-24 | 44 | 0.002051 |
| 1 | 1 | 2 | Diversity Day | 8.3 | 3566 | 2005-03-29 | 44 | 0.002328 |
| 2 | 1 | 3 | Health Care | 7.9 | 2983 | 2005-04-05 | 44 | 0.002648 |
| 3 | 1 | 4 | The Alliance | 8.1 | 2886 | 2005-04-12 | 44 | 0.002807 |
| 4 | 1 | 5 | Basketball | 8.4 | 3179 | 2005-04-19 | 44 | 0.002642 |
or as simple as adding one to every value.
df['y'] = df['season'] + 1
df.iloc[0:5,:]| season | episode | title | imdb_rating | total_votes | air_date | x | rating_div_total_votes | y | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 1 | Pilot | 7.6 | 3706 | 2005-03-24 | 44 | 0.002051 | 2 |
| 1 | 1 | 2 | Diversity Day | 8.3 | 3566 | 2005-03-29 | 44 | 0.002328 | 2 |
| 2 | 1 | 3 | Health Care | 7.9 | 2983 | 2005-04-05 | 44 | 0.002648 | 2 |
| 3 | 1 | 4 | The Alliance | 8.1 | 2886 | 2005-04-12 | 44 | 0.002807 | 2 |
| 4 | 1 | 5 | Basketball | 8.4 | 3179 | 2005-04-19 | 44 | 0.002642 | 2 |
#can you guess what the difference is between the above operation and the one below?
y = df['season'] + 16.8 Writing data
Pandas supports writing out data frames to various formats.
?df.to_csvNow you can uncomment the code below to save your dataframe into a csv file. But before doing so, check that your data/output folder is empty, as it would override its content:
#df.to_csv('data/output/my_output_ratings.csv', encoding='UTF-8')Likewise, we could export our dataset to an excel file by using to_excel:
?df.to_excelNow you can uncomment the code below to save your dataframe into an excel file. But before doing so, check that your data/output folder is empty:
# df.to_excel('data/output/my_output_ratings.xlsx')6.9 Combining datasets
In this notebook, our dataset was created from a single file that contained all the data that was needed. However, often times data will be spread into different files that we will need to combine to create our own dataset.
Consider the two dataframes below:
df_1 = pd.read_csv('data/raw/office1.csv', encoding='UTF-8')
df_2 = pd.read_csv('data/raw/office2.csv', encoding='UTF-8')df_1.head()| id | season | episode | imdb_rating | |
|---|---|---|---|---|
| 0 | 5-1 | 5 | 1 | 8.8 |
| 1 | 9-13 | 9 | 13 | 7.7 |
| 2 | 5-6 | 5 | 6 | 8.5 |
| 3 | 3-23 | 3 | 23 | 9.3 |
| 4 | 9-16 | 9 | 16 | 8.2 |
df_2.head()| id | total_votes | |
|---|---|---|
| 0 | 4-10 | 2095 |
| 1 | 3-21 | 2403 |
| 2 | 7-24 | 2040 |
| 3 | 6-18 | 1769 |
| 4 | 8-8 | 1584 |
As can be seen, the total votes and imdb ratings data are split between files that we will need to combine. Usually this is done by using a shared column between the two datasets that works as an index. Gladly, head() reveals that in both cases there is a common column called id. We can join the two dataframes together using the common column.
inner_join_office_df = pd.merge(df_1, df_2, on='id', how='inner')
inner_join_office_df| id | season | episode | imdb_rating | total_votes | |
|---|---|---|---|---|---|
| 0 | 5-1 | 5 | 1 | 8.8 | 2501 |
| 1 | 9-13 | 9 | 13 | 7.7 | 1394 |
| 2 | 5-6 | 5 | 6 | 8.5 | 2018 |
| 3 | 3-23 | 3 | 23 | 9.3 | 3010 |
| 4 | 9-16 | 9 | 16 | 8.2 | 1572 |
| ... | ... | ... | ... | ... | ... |
| 183 | 5-21 | 5 | 21 | 8.7 | 2032 |
| 184 | 2-13 | 2 | 13 | 8.3 | 2363 |
| 185 | 9-6 | 9 | 6 | 7.8 | 1455 |
| 186 | 2-2 | 2 | 2 | 8.2 | 2736 |
| 187 | 3-4 | 3 | 4 | 8.0 | 2311 |
188 rows × 5 columns
In this way you can combine datasets using common columns and an inner join. We will leave that for the moment. If you want more information about merging data then see this page and the pandas documentation.
6.9.1 Well done!
Well done! You’ve reached the end of a pretty long notebook that went through a lot of details about how to work with this pretty critical package called Pandas. We haven’t really done a lot of detailed analysis in this one yet but we will use Pandas a lot and frequently.
Your best friend will be the Pandas documentation – https://pandas.pydata.org/docs/index.html
This documentation is great. We particuarly recommend the User Guide that will answer most of your questions and will give you a lot of code to copy and paste first and then modify to do what you need to do – https://pandas.pydata.org/docs/user_guide/index.html#user-guide