CODEWITHSUNDEEP
pythonpandasindexing
user3659451
user3659451

Reputation: 2073

What is the point of indexing in pandas?

Can someone point me to a link or provide an explanation of the benefits of indexing in pandas? I routinely deal with tables and join them based on columns, and this joining/merging process seems to re-index things anyway, so it's a bit cumbersome to apply index criteria considering I don't think I need to.

Any thoughts on best-practices around indexing?

Upvotes: 89

Views: 27605

Answers (2)

qwr
qwr

Reputation: 10919

In my experience, indexing only serves to make the library more complicated for data science tasks. Indexes are supposed to be more performant, somewhat like a SQL db index, however this deviates from the tried-and-trusted relational model by having a special case that is more of an implementation detail. Dplyr makes a point to not use row labels because it adds unnecessary complexity as a special-case; in pandas this becomes confusing functions like:

  • .loc for labels vs square brackets [] for subsetting
  • merge() what you would call join() in relational databases
  • filter() and in() not doing what you'd think they do because they only operate on an index
  • groupby() matches columns and index levels, and makes the grouping variable the index unless as_index=False
  • Hierarchical MultiIndex which I'm sure has some niche use but serves to make the rest of the library functions more complicated

So far, I've never had any practical performance issues ignoring the index. Pandas, like dplyr, can't handle large datasets that don't fit into memory anyway, and Spark or an SQL database scales much better. Polars can maybe handle it although it looks still experimental.

Upvotes: 2

unutbu
unutbu

Reputation: 879621

Like a dict, a DataFrame's index is backed by a hash table. Looking up rows based on index values is like looking up dict values based on a key.

In contrast, the values in a column are like values in a list.

Looking up rows based on index values is faster than looking up rows based on column values.

For example, consider

df = pd.DataFrame({'foo':np.random.random(), 'index':range(10000)})
df_with_index = df.set_index(['index'])

Here is how you could look up any row where the df['index'] column equals 999. Pandas has to loop through every value in the column to find the ones equal to 999.

df[df['index'] == 999]

#           foo  index
# 999  0.375489    999

Here is how you could lookup any row where the index equals 999. With an index, Pandas uses the hash value to find the rows:

df_with_index.loc[999]
# foo        0.375489
# index    999.000000
# Name: 999, dtype: float64

Looking up rows by index is much faster than looking up rows by column value:

In [254]: %timeit df[df['index'] == 999]
1000 loops, best of 3: 368 µs per loop

In [255]: %timeit df_with_index.loc[999]
10000 loops, best of 3: 57.7 µs per loop

Note however, it takes time to build the index:

In [220]: %timeit df.set_index(['index'])
1000 loops, best of 3: 330 µs per loop

So having the index is only advantageous when you have many lookups of this type to perform.

Sometimes the index plays a role in reshaping the DataFrame. Many functions, such as set_index, stack, unstack, pivot, pivot_table, melt, lreshape, and crosstab, all use or manipulate the index. Sometimes we want the DataFrame in a different shape for presentation purposes, or for join, merge or groupby operations. (As you note joining can also be done based on column values, but joining based on the index is faster.) Behind the scenes, join, merge and groupby take advantage of fast index lookups when possible.

Time series have resample, asfreq and interpolate methods whose underlying implementations take advantage of fast index lookups too.

So in the end, I think the origin of the index's usefulness, why it shows up in so many functions, is due to its ability to perform fast hash lookups.

Upvotes: 126

Related Questions