What is the point of indexing in pandas?

by

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.

More Related Contents:

  1. How are iloc and loc different?
  2. How to convert index of a pandas dataframe into a column
  3. pandas loc vs. iloc vs. at vs. iat?
  4. How to reset index in a pandas dataframe? [duplicate]
  5. What rules does Pandas use to generate a view vs a copy?
  6. Pandas split DataFrame by column value
  7. Selecting a row of pandas series/dataframe by integer index
  8. How to avoid Python/Pandas creating an index in a saved csv?
  9. What is the difference between using squared brackets or dot to access a column?
  10. how do I insert a column at a specific column index in pandas?
  11. What is the performance impact of non-unique indexes in pandas?
  12. Remove index name in pandas
  13. Accessing Pandas column using squared brackets vs using a dot (like an attribute)
  14. Python pandas slice dataframe by multiple index ranges
  15. Update row values where certain condition is met in pandas
  16. How to query MultiIndex index columns values in pandas
  17. Get column index from column name in python pandas
  18. Pandas – Compute z-score for all columns
  19. How to avoid pandas creating an index in a saved csv
  20. Python Pandas: Get index of rows where column matches certain value
  21. Selecting pandas column by location
  22. Pandas max value index
  23. Get first row value of a given column
  24. Python: Removing Rows on Count condition
  25. Using replace efficiently in pandas
  26. In Python pandas, start row index from 1 instead of zero without creating additional column
  27. Selecting a range of columns in a dataframe
  28. How to efficiently assign unique ID to individuals with multiple entries based on name in very large df
  29. What’s the alternative to pandas chain indexing?
  30. start index at 1 for Pandas DataFrame

Leave a Comment