Indexing#

This notebook discusses Pandas indexing operations, and pitfalls.

Setup#

First our Python modules:

import numpy as np
import pandas as pd
import scipy.linalg as spla
import matplotlib.pyplot as plt
import seaborn as sns

And then the movie data, as usual. Movies:

movies = pd.read_csv('hetrec2011-ml/movies.dat', delimiter='\t', encoding='latin1', na_values=['\\N'])
movies.head()

	id	title	imdbID	spanishTitle	imdbPictureURL	year	rtID	rtAllCriticsRating	rtAllCriticsNumReviews	rtAllCriticsNumFresh	...	rtAllCriticsScore	rtTopCriticsRating	rtTopCriticsNumReviews	rtTopCriticsNumFresh	rtTopCriticsNumRotten	rtTopCriticsScore	rtAudienceRating	rtAudienceNumRatings	rtAudienceScore	rtPictureURL
0	1	Toy story	114709	Toy story (juguetes)	http://ia.media-imdb.com/images/M/MV5BMTMwNDU0...	1995	toy_story	9.0	73.0	73.0	...	100.0	8.5	17.0	17.0	0.0	100.0	3.7	102338.0	81.0	http://content7.flixster.com/movie/10/93/63/10...
1	2	Jumanji	113497	Jumanji	http://ia.media-imdb.com/images/M/MV5BMzM5NjE1...	1995	1068044-jumanji	5.6	28.0	13.0	...	46.0	5.8	5.0	2.0	3.0	40.0	3.2	44587.0	61.0	http://content8.flixster.com/movie/56/79/73/56...
2	3	Grumpy Old Men	107050	Dos viejos gruñones	http://ia.media-imdb.com/images/M/MV5BMTI5MTgy...	1993	grumpy_old_men	5.9	36.0	24.0	...	66.0	7.0	6.0	5.0	1.0	83.0	3.2	10489.0	66.0	http://content6.flixster.com/movie/25/60/25602...
3	4	Waiting to Exhale	114885	Esperando un respiro	http://ia.media-imdb.com/images/M/MV5BMTczMTMy...	1995	waiting_to_exhale	5.6	25.0	14.0	...	56.0	5.5	11.0	5.0	6.0	45.0	3.3	5666.0	79.0	http://content9.flixster.com/movie/10/94/17/10...
4	5	Father of the Bride Part II	113041	Vuelve el padre de la novia (Ahora también abu...	http://ia.media-imdb.com/images/M/MV5BMTg1NDc2...	1995	father_of_the_bride_part_ii	5.3	19.0	9.0	...	47.0	5.4	5.0	1.0	4.0	20.0	3.0	13761.0	64.0	http://content8.flixster.com/movie/25/54/25542...

5 rows × 21 columns

And ratings:

ratings = pd.read_csv('hetrec2011-ml/user_ratedmovies-timestamps.dat', delimiter='\t', encoding='latin1')
ratings.head()

	userID	movieID	rating	timestamp
0	75	3	1.0	1162160236000
1	75	32	4.5	1162160624000
2	75	110	4.0	1162161008000
3	75	160	2.0	1162160212000
4	75	163	4.0	1162160970000

And let’s load tags:

tags = pd.read_csv('hetrec2011-ml/tags.dat', delimiter='\t', encoding='latin1')
tags.head()

	id	value
0	1	earth
1	2	police
2	3	boxing
3	4	painter
4	5	whale

What Is an Index?#

An index is what we use to locate rows or columns in a Pandas data structure. (While conceptually we can think of a data frame as a dictionary of columns, the name-to-column mapping is actually done with an index.)

An index is two things in one data structure:

An array of key values (given a position, we can get the key value)
A hash map to look up row position(s) for a given key value

Indexes keys can be any (immutable) data type. Pandas has special support for indexing over datetime64 objects, for time series.

Range Index#

When we load the data from the CSV file, it by default it is loaded with a RangeIndex, in which the keys and the positions are the same. These indexes are very memory-efficient, because they don’t actually need to store key values. Let’s see the movie index:

movies.index

RangeIndex(start=0, stop=10197, step=1)

With a range index, loc and iloc do the same thing (for rows):

movies.loc[5, ['id', 'title']]

id          6
title    Heat
Name: 5, dtype: object

movies.iloc[5, 0:2]

id          6
title    Heat
Name: 5, dtype: object

Setting an Index#

The set_index method makes a column into the index. It will replace the current index; if the current index is a range index, that is usually fine.

There are two versions:

By default, it returns a new data frame with the new index, and leaves the original unchanged. The frame shares underlying data with the original, so it doesn’t take up that much memory.
With inplace=True, it modifies the data frame to have the new index.

Both are useful. Be very careful with inplace in a function; the caller probably doesn’t expect the data frame to be modified!

Let’s index the movies frame by movie ID:

movies = movies.set_index('id')
movies.index

Int64Index([    1,     2,     3,     4,     5,     6,     7,     8,     9,
               10,
            ...
            64997, 64999, 65006, 65011, 65037, 65088, 65091, 65126, 65130,
            65133],
           dtype='int64', name='id', length=10197)

The new index is also reflected in .info(), and the id column is gone:

movies.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 10197 entries, 1 to 65133
Data columns (total 20 columns):
 #   Column                  Non-Null Count  Dtype  
---  ------                  --------------  -----  
 title                   10197 non-null  object 
 imdbID                  10197 non-null  int64  
 spanishTitle            10197 non-null  object 
 imdbPictureURL          10016 non-null  object 
 year                    10197 non-null  int64  
 rtID                    9886 non-null   object 
 rtAllCriticsRating      9967 non-null   float64
 rtAllCriticsNumReviews  9967 non-null   float64
 rtAllCriticsNumFresh    9967 non-null   float64
 rtAllCriticsNumRotten   9967 non-null   float64
rtAllCriticsScore       9967 non-null   float64
rtTopCriticsRating      9967 non-null   float64
rtTopCriticsNumReviews  9967 non-null   float64
rtTopCriticsNumFresh    9967 non-null   float64
rtTopCriticsNumRotten   9967 non-null   float64
rtTopCriticsScore       9967 non-null   float64
rtAudienceRating        9967 non-null   float64
rtAudienceNumRatings    9967 non-null   float64
rtAudienceScore         9967 non-null   float64
rtPictureURL            9967 non-null   object 
dtypes: float64(13), int64(2), object(5)
memory usage: 1.9+ MB

We now have an index that lets us up by 64-bit integer keys. We can now look up Heat by movie ID:

movies.loc[6]

title                                                                  Heat
imdbID                                                               113277
spanishTitle                                                           Heat
imdbPictureURL            http://ia.media-imdb.com/images/M/MV5BMTM1NDc4...
year                                                                   1995
rtID                                                           1068182-heat
rtAllCriticsRating                                                      7.7
rtAllCriticsNumReviews                                                   58
rtAllCriticsNumFresh                                                     50
rtAllCriticsNumRotten                                                     8
rtAllCriticsScore                                                        86
rtTopCriticsRating                                                      7.2
rtTopCriticsNumReviews                                                   17
rtTopCriticsNumFresh                                                     14
rtTopCriticsNumRotten                                                     3
rtTopCriticsScore                                                        82
rtAudienceRating                                                        3.9
rtAudienceNumRatings                                                  42785
rtAudienceScore                                                          92
rtPictureURL              http://content9.flixster.com/movie/26/80/26809...
Name: 6, dtype: object

iloc still returns this at position 5:

movies.iloc[5]

title                                                                  Heat
imdbID                                                               113277
spanishTitle                                                           Heat
imdbPictureURL            http://ia.media-imdb.com/images/M/MV5BMTM1NDc4...
year                                                                   1995
rtID                                                           1068182-heat
rtAllCriticsRating                                                      7.7
rtAllCriticsNumReviews                                                   58
rtAllCriticsNumFresh                                                     50
rtAllCriticsNumRotten                                                     8
rtAllCriticsScore                                                        86
rtTopCriticsRating                                                      7.2
rtTopCriticsNumReviews                                                   17
rtTopCriticsNumFresh                                                     14
rtTopCriticsNumRotten                                                     3
rtTopCriticsScore                                                        82
rtAudienceRating                                                        3.9
rtAudienceNumRatings                                                  42785
rtAudienceScore                                                          92
rtPictureURL              http://content9.flixster.com/movie/26/80/26809...
Name: 6, dtype: object

The reset_index method will turn the index back into a column, and put a range index back in place:

movies.reset_index().info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10197 entries, 0 to 10196
Data columns (total 21 columns):
 #   Column                  Non-Null Count  Dtype  
---  ------                  --------------  -----  
 id                      10197 non-null  int64  
 title                   10197 non-null  object 
 imdbID                  10197 non-null  int64  
 spanishTitle            10197 non-null  object 
 imdbPictureURL          10016 non-null  object 
 year                    10197 non-null  int64  
 rtID                    9886 non-null   object 
 rtAllCriticsRating      9967 non-null   float64
 rtAllCriticsNumReviews  9967 non-null   float64
 rtAllCriticsNumFresh    9967 non-null   float64
rtAllCriticsNumRotten   9967 non-null   float64
rtAllCriticsScore       9967 non-null   float64
rtTopCriticsRating      9967 non-null   float64
rtTopCriticsNumReviews  9967 non-null   float64
rtTopCriticsNumFresh    9967 non-null   float64
rtTopCriticsNumRotten   9967 non-null   float64
rtTopCriticsScore       9967 non-null   float64
rtAudienceRating        9967 non-null   float64
rtAudienceNumRatings    9967 non-null   float64
rtAudienceScore         9967 non-null   float64
rtPictureURL            9967 non-null   object 
dtypes: float64(13), int64(3), object(5)
memory usage: 1.6+ MB

Index Properties and Operations#

We can do quite a few things with an Index.

First, it has a name:

movies.index.name

'id'

We could change the name if we wanted to, by assigning to it:

movies.index.name = 'movieId'

But we won’t actually do that right now.

Arrays and Keys#

The index is also an array - we can get the key at a certain position:

movies.index[5]

To go the other way — find the location or position for a key value — we can use the get_loc method:

movies.index.get_loc(6)

If we look for a nonexistent key, it fails:

movies.index.get_loc(30481080)

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   2645             try:
-> 2646                 return self._engine.get_loc(key)
   2647             except KeyError:

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()

KeyError: 30481080

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
<ipython-input-17-937b631c964d> in <module>
----> 1 movies.index.get_loc(30481080)

~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   2646                 return self._engine.get_loc(key)
   2647             except KeyError:
-> 2648                 return self._engine.get_loc(self._maybe_cast_indexer(key))
   2649         indexer = self.get_indexer([key], method=method, tolerance=tolerance)
   2650         if indexer.ndim > 1 or indexer.size > 1:

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()

KeyError: 30481080

This is a KeyError, meaning we tried to look up a key that does not exist.

Internally, the index uses a NumPy ndarray to store the keys. We can access this array as .values:

movies.index.values

array([    1,     2,     3, ..., 65126, 65130, 65133], dtype=int64)

Index Characteristics#

There are a few useful characteristics an index can have. It may be unique, or it may have duplicates. This is a unique index:

movies.index.is_unique

True

movies.index.has_duplicates

False

An index can also be monotonic, or sorted; the is_monotonic accessor is a shortcut for is_monotonic_increasing and returns True if the index is sorted in nondecreasing order:

movies.index.is_monotonic

True

Since this index is both monotonic and unique, it is sorted in strictly increasing order.

Since it is monotonic, we can also use ranges for key-based lookup:

movies.loc[6:10]

	title	imdbID	spanishTitle	imdbPictureURL	year	rtID	rtAllCriticsRating	rtAllCriticsNumReviews	rtAllCriticsNumFresh	rtAllCriticsNumRotten	rtAllCriticsScore	rtTopCriticsRating	rtTopCriticsNumReviews	rtTopCriticsNumFresh	rtTopCriticsNumRotten	rtTopCriticsScore	rtAudienceRating	rtAudienceNumRatings	rtAudienceScore	rtPictureURL
id
6	Heat	113277	Heat	http://ia.media-imdb.com/images/M/MV5BMTM1NDc4...	1995	1068182-heat	7.7	58.0	50.0	8.0	86.0	7.2	17.0	14.0	3.0	82.0	3.9	42785.0	92.0	http://content9.flixster.com/movie/26/80/26809...
7	Sabrina	47437	Sabrina	http://ia.media-imdb.com/images/M/MV5BMTYyNDM1...	1954	1018047-sabrina	7.4	31.0	28.0	3.0	90.0	7.2	5.0	5.0	0.0	100.0	3.8	12812.0	87.0	http://content7.flixster.com/movie/10/93/36/10...
8	Tom and Huck	112302	Tom y Huck	http://ia.media-imdb.com/images/M/MV5BMTUxNDYz...	1995	tom_and_huck	4.2	8.0	2.0	6.0	25.0	0.0	2.0	1.0	1.0	50.0	2.7	2649.0	45.0	http://content9.flixster.com/movie/26/16/26169...
9	Sudden Death	114576	Sudden Death: muerte súbita	http://ia.media-imdb.com/images/M/MV5BMTcwMTU2...	1995	1068470-sudden_death	5.2	32.0	17.0	15.0	53.0	5.6	9.0	5.0	4.0	55.0	2.6	3626.0	40.0	http://content8.flixster.com/movie/27/91/27912...
10	GoldenEye	113189	GoldenEye	http://ia.media-imdb.com/images/M/MV5BNTE1OTEx...	1995	goldeneye	6.8	41.0	33.0	8.0	80.0	6.2	11.0	7.0	4.0	63.0	3.4	28260.0	78.0	http://content9.flixster.com/movie/26/66/26669...

Note that range-based indexing in .loc includes both ends, unlike slices in almost any other part of Python.

Unsorted Indexes: While we might expect slice-based key indexing to get things in the range, that is not what it does. It locates the positions of the start and end keys, and returns everything between them. If the index is not sorted, this is usually not useful. Only use key slices on sorted indexes.

Hierarchical Indexing#

Pandas supports hierarchical indexes, with MultiIndex. Create a hierarchical index by passing multiple columns to set_index.

For example, let’s make a series that indexes ratings by user and item IDs:

idx_ratings = ratings.set_index(['userID', 'movieID'])
idx_ratings

		rating	timestamp
userID	movieID
75	3	1.0	1162160236000
	32	4.5	1162160624000
	110	4.0	1162161008000
	160	2.0	1162160212000
	163	4.0	1162160970000
...	...	...	...
71534	44555	4.0	1196647538000
	46578	4.0	1196647004000
	48516	4.5	1196646826000
	61075	5.0	1223625365000
	62049	4.5	1223625490000

855598 rows × 2 columns

We see on the left that there are two levels to the index: userID and movieID. We can see this with .names:

idx_ratings.index.names

FrozenList(['userID', 'movieID'])

The multi-level index lets us look up values by just the outermost index, getting all rows with that outer index value. For example, to get all ratings by user 190:

idx_ratings.loc[190]

	rating	timestamp
movieID
1	4.5	1057778398000
2	4.0	1053457414000
7	2.0	1049823937000
32	3.0	1151336652000
95	2.0	1078019174000
...	...	...
54001	4.5	1185219688000
54259	4.0	1217875029000
58559	4.5	1217258754000
59315	4.0	1210627067000
64497	3.0	1230991072000

296 rows × 2 columns

Since we are looking up one user, it drops the user level from the index. If we looked up multiple users, it would not:

idx_ratings.loc[[75, 190]]

		rating	timestamp
userID	movieID
75	3	1.0	1162160236000
	32	4.5	1162160624000
	110	4.0	1162161008000
	160	2.0	1162160212000
	163	4.0	1162160970000
...	...	...	...
190	54001	4.5	1185219688000
	54259	4.0	1217875029000
	58559	4.5	1217258754000
	59315	4.0	1210627067000
	64497	3.0	1230991072000

351 rows × 2 columns

Multi-indexes are one place where Python makes a distinction between lists and tuples. If we look up (75, 32), it will look up user 75’s rating for item 32:

idx_ratings.loc[(75, 32)]

rating       4.500000e+00
timestamp    1.162161e+12
Name: (75, 32), dtype: float64

We can index by a list of tuples:

idx_ratings.loc[[(75, 32), (190, 58559)]]

		rating	timestamp
userID	movieID
75	32	4.5	1162160624000
190	58559	4.5	1217258754000

So we have the following principle:

The index locator can be a key, or a list or array of keys
A key can be a single value, looking up in the outermost index level; or a tuple of values, looking up by multiple levels simultaneously

Be careful: hierarchical indexes are not as useful as they look. They are great for lookups, and joins, but they often have problems too. In particular, if you have a large data frame with a hiearchical index, there are several operations — including groupby — that will cause memory explosion. If you call melt with multiple ID columns, it will turn them into a hierarchical index.

I usually use hierarchical indexes for two purpose:

Storing data for multidimensional lookup, like the user-and-item ratings
Joining on mulitiple simultaneous columns

In both cases, once I have the data I need, I usually strip away the multi-index before moving forward (with reset_index, or with a join or lookup operation that leaves the data frame with a normal one-level index) to reduce the risk of unexpected memory explosion.

Sorting and Realigning#

We can rearrange data frames based on their indexes.

Sorting#

One useful operation to realign a data frame (or series) with respect to its index is to sort the index. Our index is already sorted by ID, but what if we wanted to use IMDB IDs?

imdb_movies = movies.reset_index().set_index('imdbID')
imdb_movies

	id	title	spanishTitle	imdbPictureURL	year	rtID	rtAllCriticsRating	rtAllCriticsNumReviews	rtAllCriticsNumFresh	rtAllCriticsNumRotten	rtAllCriticsScore	rtTopCriticsRating	rtTopCriticsNumReviews	rtTopCriticsNumFresh	rtTopCriticsNumRotten	rtTopCriticsScore	rtAudienceRating	rtAudienceNumRatings	rtAudienceScore	rtPictureURL
imdbID
114709	1	Toy story	Toy story (juguetes)	http://ia.media-imdb.com/images/M/MV5BMTMwNDU0...	1995	toy_story	9.0	73.0	73.0	0.0	100.0	8.5	17.0	17.0	0.0	100.0	3.7	102338.0	81.0	http://content7.flixster.com/movie/10/93/63/10...
113497	2	Jumanji	Jumanji	http://ia.media-imdb.com/images/M/MV5BMzM5NjE1...	1995	1068044-jumanji	5.6	28.0	13.0	15.0	46.0	5.8	5.0	2.0	3.0	40.0	3.2	44587.0	61.0	http://content8.flixster.com/movie/56/79/73/56...
107050	3	Grumpy Old Men	Dos viejos gruñones	http://ia.media-imdb.com/images/M/MV5BMTI5MTgy...	1993	grumpy_old_men	5.9	36.0	24.0	12.0	66.0	7.0	6.0	5.0	1.0	83.0	3.2	10489.0	66.0	http://content6.flixster.com/movie/25/60/25602...
114885	4	Waiting to Exhale	Esperando un respiro	http://ia.media-imdb.com/images/M/MV5BMTczMTMy...	1995	waiting_to_exhale	5.6	25.0	14.0	11.0	56.0	5.5	11.0	5.0	6.0	45.0	3.3	5666.0	79.0	http://content9.flixster.com/movie/10/94/17/10...
113041	5	Father of the Bride Part II	Vuelve el padre de la novia (Ahora también abu...	http://ia.media-imdb.com/images/M/MV5BMTg1NDc2...	1995	father_of_the_bride_part_ii	5.3	19.0	9.0	10.0	47.0	5.4	5.0	1.0	4.0	20.0	3.0	13761.0	64.0	http://content8.flixster.com/movie/25/54/25542...
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
960731	65088	Bedtime Stories	Más allá de los sueños	http://ia.media-imdb.com/images/M/MV5BMjA5Njk5...	2008	bedtime_stories	4.4	104.0	26.0	78.0	25.0	4.7	26.0	6.0	20.0	23.0	3.5	108877.0	63.0	http://content6.flixster.com/movie/10/94/33/10...
25464	65091	Manhattan Melodrama	El enemigo público número 1	http://ia.media-imdb.com/images/M/MV5BMTUyODE3...	1934	manhattan_melodrama	7.0	12.0	10.0	2.0	83.0	0.0	4.0	2.0	2.0	50.0	3.7	344.0	71.0	http://content9.flixster.com/movie/66/44/64/66...
1024715	65126	Choke	Choke	http://ia.media-imdb.com/images/M/MV5BMTMxMDI4...	2008	choke	5.6	135.0	73.0	62.0	54.0	4.9	26.0	8.0	18.0	30.0	3.3	13893.0	55.0	http://content6.flixster.com/movie/10/85/09/10...
959337	65130	Revolutionary Road	Revolutionary Road	http://ia.media-imdb.com/images/M/MV5BMTI2MzY2...	2008	revolutionary_road	6.7	194.0	133.0	61.0	68.0	6.9	36.0	25.0	11.0	69.0	3.5	46044.0	70.0	http://content8.flixster.com/movie/10/88/40/10...
212579	65133	Blackadder Back & Forth	Blackadder Back & Forth	http://ia.media-imdb.com/images/M/MV5BMjA5MjU4...	1999	blackadder-back-forth	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	http://content7.flixster.com/movie/34/10/69/34...

10197 rows × 20 columns

Now let’s sort and show again:

imdb_movies.sort_index(inplace=True)
imdb_movies

	id	title	spanishTitle	imdbPictureURL	year	rtID	rtAllCriticsRating	rtAllCriticsNumReviews	rtAllCriticsNumFresh	rtAllCriticsNumRotten	rtAllCriticsScore	rtTopCriticsRating	rtTopCriticsNumReviews	rtTopCriticsNumFresh	rtTopCriticsNumRotten	rtTopCriticsScore	rtAudienceRating	rtAudienceNumRatings	rtAudienceScore	rtPictureURL
imdbID
439	6990	The Great Train Robbery	Asalto y robo al tren	http://ia.media-imdb.com/images/M/MV5BMTk3OTQy...	1903	the-great-train-robbery1903	7.6	6.0	6.0	0.0	100.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	http://content7.flixster.com/movie/10/87/70/10...
4972	7065	The Birth of a Nation	El nacimiento de una nación	http://ia.media-imdb.com/images/M/MV5BMjI2MzU4...	1915	birth_of_a_nation	8.0	31.0	31.0	0.0	100.0	0.0	4.0	4.0	0.0	100.0	3.3	1249.0	58.0	http://content9.flixster.com/movie/10/88/58/10...
6864	7243	Intolerance: Love's Struggle Throughout the Ages	Intolerancia	http://ia.media-imdb.com/images/M/MV5BODEyNDUy...	1916	1010644-intolerance	7.8	23.0	22.0	1.0	95.0	0.0	3.0	3.0	0.0	100.0	3.8	1397.0	77.0	http://content6.flixster.com/movie/31/14/31140...
8133	8511	The Immigrant	Charlot emigrante	http://ia.media-imdb.com/images/M/MV5BMjExNDE5...	1917	immigrant	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	http://content7.flixster.com/movie/10/85/69/10...
8395	48374	Otets Sergiy	El padre Sergio	NaN	1917	otets-sergiy-father-sergius	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	http://content7.flixster.com/movie/10/91/43/10...
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1229827	4260	Jonas Brothers: The 3D Concert Experience	Jonas Brothers: The 3D Concert Experience	http://ia.media-imdb.com/images/M/MV5BMTU3NzE4...	2009	jonas_brothers_3d	4.2	71.0	16.0	55.0	22.0	4.6	22.0	7.0	15.0	31.0	3.3	74109.0	64.0	http://content7.flixster.com/movie/10/92/39/10...
1233247	757	Dung che sai duk	Este contraveneno del Oeste	http://ia.media-imdb.com/images/M/MV5BMTc0NzMx...	1994	ashes_of_time_redux	6.6	69.0	54.0	15.0	78.0	6.7	18.0	14.0	4.0	77.0	3.6	2166.0	71.0	http://content8.flixster.com/movie/10/93/19/10...
1233381	63698	Üç maymun	Tres monos	http://ia.media-imdb.com/images/M/MV5BMTMwNjM3...	2008	three_monkeys	6.7	56.0	43.0	13.0	76.0	7.7	13.0	12.0	1.0	92.0	3.6	1239.0	72.0	http://content8.flixster.com/movie/11/13/29/11...
1275532	4457	Bianca Beauchamp All Access 2: Rubberized	Bianca Beauchamp All Access 2: Rubberized	http://ia.media-imdb.com/images/M/MV5BMTI1ODQ1...	2008	bianca-beauchamp-all-access-2-rubberized	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	http://content6.flixster.com/movie/10/88/44/10...
1349938	418	Being Human	Being Human	http://ia.media-imdb.com/images/M/MV5BMjA1MzIw...	2008	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

10197 rows × 20 columns

Now the index reports that it’s sorted too:

imdb_movies.index.is_monotonic

True

Reindexing#

Another operation on an index is a reindex. When we reindex a data frame or series, we give it an array of key values, and it rearranges elements to correspond to the new array. It will insert missing values for keys that don’t exist in the original frame.

So let’s reindex the movie frame (we are not passing inplace, so it’s going to return a new frame):

movies.reindex([1, 6, 101, 2038401])

	title	imdbID	spanishTitle	imdbPictureURL	year	rtID	rtAllCriticsRating	rtAllCriticsNumReviews	rtAllCriticsNumFresh	rtAllCriticsNumRotten	rtAllCriticsScore	rtTopCriticsRating	rtTopCriticsNumReviews	rtTopCriticsNumFresh	rtTopCriticsNumRotten	rtTopCriticsScore	rtAudienceRating	rtAudienceNumRatings	rtAudienceScore	rtPictureURL
id
1	Toy story	114709.0	Toy story (juguetes)	http://ia.media-imdb.com/images/M/MV5BMTMwNDU0...	1995.0	toy_story	9.0	73.0	73.0	0.0	100.0	8.5	17.0	17.0	0.0	100.0	3.7	102338.0	81.0	http://content7.flixster.com/movie/10/93/63/10...
6	Heat	113277.0	Heat	http://ia.media-imdb.com/images/M/MV5BMTM1NDc4...	1995.0	1068182-heat	7.7	58.0	50.0	8.0	86.0	7.2	17.0	14.0	3.0	82.0	3.9	42785.0	92.0	http://content9.flixster.com/movie/26/80/26809...
101	Bottle Rocket	115734.0	Ladrón que roba a otro ladrón	http://ia.media-imdb.com/images/M/MV5BMTk1ODI3...	1996.0	bottle_rocket	6.6	42.0	33.0	9.0	78.0	6.1	9.0	5.0	4.0	55.0	3.7	16217.0	80.0	http://content6.flixster.com/movie/26/73/26732...
2038401	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

There is no movie 2038401, so it fills in NaN everywhere.

Alignment#

Another useful operation is align. It reindexes two series so that their indexes match, and we can then perform more operations on them. When you do vectorized operations on two series, Pandas first aligns their indexes and then does the operation on the corresponding values.

For example, we can treat users’ ratings as item-dimensional vectors, and compute the cosine as a measure of how similar their ratings are.

\[\mathrm{cos}(\vec{x}, \vec{y}) = \frac{\vec{x} \cdot \vec{y}}{\|\vec{x}\|_2 \|\vec{x}\|_2}\]

First, let’s get the rating vectors for two users:

u1_rates = idx_ratings.loc[75, 'rating']
u2_rates = idx_ratings.loc[190, 'rating']

Let’s multiply their ratings - Pandas will automatically align them:

u12_mult = u1_rates * u2_rates
u12_mult.describe()

count    22.000000
mean     13.534091
std       6.139400
min       2.250000
25%      12.062500
50%      15.375000
75%      17.500000
max      22.500000
Name: rating, dtype: float64

u12_mult

movieID
       NaN
       NaN
       NaN
       NaN
     13.5
         ... 
   NaN
   NaN
   NaN
   NaN
   NaN
Name: rating, Length: 329, dtype: float64

There are 22 values that aren’t NaN - those are the values where both users have a rating. This is because the alignment fills in NaN, and NaN times a rating value is NaN.

To finish the cosine, we can compute the sum of this, and the norms of the two vectors:

np.sum(u12_mult) / (spla.norm(u1_rates) * spla.norm(u2_rates))

0.19577120060037112

The scipy.linalg.norm function computes the Euclidean norm \(\| \cdot \|_2\).

But what does this alignment do? We can do it ourselves with .align. First let’s look at our vectors:

u1_rates

movieID
      1.0
     4.5
    4.0
    2.0
    4.0
    4.5
    3.5
    5.0
    3.5
    2.0
    4.0
    3.0
    4.5
    0.5
    4.5
   4.0
   3.5
   4.5
   4.0
   2.5
   4.0
   4.0
   4.0
   4.5
   2.5
   2.0
   1.5
   4.0
   4.0
   4.5
   3.0
   3.0
   4.5
   3.5
   4.5
   1.5
   3.0
   3.0
   3.5
   3.5
   3.0
   2.5
   3.5
   4.0
   0.5
   4.0
   3.5
   4.5
   3.5
  4.5
  5.0
  3.5
  3.5
  3.5
  4.5
Name: rating, dtype: float64

u2_rates

movieID
      4.5
      4.0
      2.0
     3.0
     2.0
        ... 
  4.5
  4.0
  4.5
  4.0
  3.0
Name: rating, Length: 296, dtype: float64

The align method returns a tuple of the aligned series:

u1a, u2a = u1_rates.align(u2_rates)
u1a

movieID
      NaN
      NaN
      1.0
      NaN
     4.5
        ... 
  NaN
  NaN
  NaN
  NaN
  NaN
Name: rating, Length: 329, dtype: float64

u2a

movieID
      4.5
      4.0
      NaN
      2.0
     3.0
        ... 
  4.5
  4.0
  4.5
  4.0
  3.0
Name: rating, Length: 329, dtype: float64

They now have the same index, and the values are put in place. Missing values are filled in with NaN. These vectors are now ready to be multipled, or have other operations applied!

Index Gotchas#

Indexes do not have to be unique - the same key can be in multiple rows.

I don’t recommend using non-unique indexes. They often aren’t terribly useful, and they very quickly get slow.

As described above, hierarchical indexes also have problems, particularly with groupby. They can eat an incredible amount of memory, send your system deep into swap, and slow it to a crawl.

Learning More#

The textbook
Indexing and Selecting Data in the Pandas user guide
MultiIndex / Advanced Indexing in the Pandas user guide

CS 533 Fall 2022

Indexing

Contents