Indexing and selecting data

The axis labeling information in pandas objects serves many purposes:

  • Identifies data (i.e. provides metadata) using known indicators,important for analysis, visualization, and interactive console display.
  • Enables automatic and explicit data alignment.
  • Allows intuitive getting and setting of subsets of the data set.

In this section, we will focus on the final point: namely, how to slice, dice,and generally get and set subsets of pandas objects. The primary focus will beon Series and DataFrame as they have received more development attention inthis area.

Note

The Python and NumPy indexing operators [] and attribute operator .provide quick and easy access to pandas data structures across a wide rangeof use cases. This makes interactive work intuitive, as there’s little newto learn if you already know how to deal with Python dictionaries and NumPyarrays. However, since the type of the data to be accessed isn’t known inadvance, directly using standard operators has some optimization limits. Forproduction code, we recommended that you take advantage of the optimizedpandas data access methods exposed in this chapter.

Warning

Whether a copy or a reference is returned for a setting operation, maydepend on the context. This is sometimes called chained assignment andshould be avoided. See Returning a View versus Copy.

Warning

Indexing on an integer-based Index with floats has been clarified in 0.18.0, for a summary of the changes, see here.

See the MultiIndex / Advanced Indexing for MultiIndex and more advanced indexing documentation.

See the cookbook for some advanced strategies.

Different choices for indexing

Object selection has had a number of user-requested additions in order tosupport more explicit location based indexing. Pandas now supports three typesof multi-axis indexing.

  • .loc is primarily label based, but may also be used with a boolean array. .loc will raise KeyError when the items are not found. Allowed inputs are:

  • A single label, e.g. 5 or 'a' (Note that 5 is interpreted as alabel of the index. This use is not an integer position along theindex.).

  • A list or array of labels ['a', 'b', 'c'].

  • A slice object with labels 'a':'f' (Note that contrary to usual pythonslices, both the start and the stop are included, when present in theindex! See Slicing with labelsand Endpoints are inclusive.)

  • A boolean array

  • A callable function with one argument (the calling Series or DataFrame) andthat returns valid output for indexing (one of the above).

    New in version 0.18.1.

See more at Selection by Label.

  • .iloc is primarily integer position based (from 0 tolength-1 of the axis), but may also be used with a booleanarray. .iloc will raise IndexError if a requestedindexer is out-of-bounds, except slice indexers which allowout-of-bounds indexing. (this conforms with Python/NumPy _slice_semantics). Allowed inputs are:

  • An integer e.g. 5.

  • A list or array of integers [4, 3, 0].

  • A slice object with ints 1:7.

  • A boolean array.

  • A callable function with one argument (the calling Series or DataFrame) andthat returns valid output for indexing (one of the above).

    New in version 0.18.1.

See more at Selection by Position,Advanced Indexing and AdvancedHierarchical.

Getting values from an object with multi-axes selection uses the followingnotation (using .loc as an example, but the following applies to .iloc aswell). Any of the axes accessors may be the null slice :. Axes left out ofthe specification are assumed to be :, e.g. p.loc['a'] is equivalent top.loc['a', :, :].

Object TypeIndexers
Seriess.loc[indexer]
DataFramedf.loc[row_indexer,column_indexer]

Basics

As mentioned when introducing the data structures in the last section, the primary function of indexing with [] (a.k.a. getitemfor those familiar with implementing class behavior in Python) is selecting outlower-dimensional slices. The following table shows return type values whenindexing pandas objects with []:

Object TypeSelectionReturn Value Type
Seriesseries[label]scalar value
DataFrameframe[colname]Series corresponding to colname

Here we construct a simple time series data set to use for illustrating theindexing functionality:

  1. In [1]: dates = pd.date_range('1/1/2000', periods=8)
  2.  
  3. In [2]: df = pd.DataFrame(np.random.randn(8, 4),
  4.    ...:                   index=dates, columns=['A', 'B', 'C', 'D'])
  5.    ...: 
  6.  
  7. In [3]: df
  8. Out[3]: 
  9.                    A         B         C         D
  10. 2000-01-01  0.469112 -0.282863 -1.509059 -1.135632
  11. 2000-01-02  1.212112 -0.173215  0.119209 -1.044236
  12. 2000-01-03 -0.861849 -2.104569 -0.494929  1.071804
  13. 2000-01-04  0.721555 -0.706771 -1.039575  0.271860
  14. 2000-01-05 -0.424972  0.567020  0.276232 -1.087401
  15. 2000-01-06 -0.673690  0.113648 -1.478427  0.524988
  16. 2000-01-07  0.404705  0.577046 -1.715002 -1.039268
  17. 2000-01-08 -0.370647 -1.157892 -1.344312  0.844885

Note

None of the indexing functionality is time series specific unlessspecifically stated.

Thus, as per above, we have the most basic indexing using []:

  1. In [4]: s = df['A']
  2.  
  3. In [5]: s[dates[5]]
  4. Out[5]: -0.6736897080883706

You can pass a list of columns to [] to select columns in that order.If a column is not contained in the DataFrame, an exception will beraised. Multiple columns can also be set in this manner:

  1. In [6]: df
  2. Out[6]: 
  3.                    A         B         C         D
  4. 2000-01-01  0.469112 -0.282863 -1.509059 -1.135632
  5. 2000-01-02  1.212112 -0.173215  0.119209 -1.044236
  6. 2000-01-03 -0.861849 -2.104569 -0.494929  1.071804
  7. 2000-01-04  0.721555 -0.706771 -1.039575  0.271860
  8. 2000-01-05 -0.424972  0.567020  0.276232 -1.087401
  9. 2000-01-06 -0.673690  0.113648 -1.478427  0.524988
  10. 2000-01-07  0.404705  0.577046 -1.715002 -1.039268
  11. 2000-01-08 -0.370647 -1.157892 -1.344312  0.844885
  12.  
  13. In [7]: df[['B', 'A']] = df[['A', 'B']]
  14.  
  15. In [8]: df
  16. Out[8]: 
  17.                    A         B         C         D
  18. 2000-01-01 -0.282863  0.469112 -1.509059 -1.135632
  19. 2000-01-02 -0.173215  1.212112  0.119209 -1.044236
  20. 2000-01-03 -2.104569 -0.861849 -0.494929  1.071804
  21. 2000-01-04 -0.706771  0.721555 -1.039575  0.271860
  22. 2000-01-05  0.567020 -0.424972  0.276232 -1.087401
  23. 2000-01-06  0.113648 -0.673690 -1.478427  0.524988
  24. 2000-01-07  0.577046  0.404705 -1.715002 -1.039268
  25. 2000-01-08 -1.157892 -0.370647 -1.344312  0.844885

You may find this useful for applying a transform (in-place) to a subset of thecolumns.

Warning

pandas aligns all AXES when setting Series and DataFrame from .loc, and .iloc.

This will not modify df because the column alignment is before value assignment.

  1. In [9]: df[['A', 'B']]
  2. Out[9]: 
  3.                    A         B
  4. 2000-01-01 -0.282863  0.469112
  5. 2000-01-02 -0.173215  1.212112
  6. 2000-01-03 -2.104569 -0.861849
  7. 2000-01-04 -0.706771  0.721555
  8. 2000-01-05  0.567020 -0.424972
  9. 2000-01-06  0.113648 -0.673690
  10. 2000-01-07  0.577046  0.404705
  11. 2000-01-08 -1.157892 -0.370647
  12.  
  13. In [10]: df.loc[:, ['B', 'A']] = df[['A', 'B']]
  14.  
  15. In [11]: df[['A', 'B']]
  16. Out[11]: 
  17.                    A         B
  18. 2000-01-01 -0.282863  0.469112
  19. 2000-01-02 -0.173215  1.212112
  20. 2000-01-03 -2.104569 -0.861849
  21. 2000-01-04 -0.706771  0.721555
  22. 2000-01-05  0.567020 -0.424972
  23. 2000-01-06  0.113648 -0.673690
  24. 2000-01-07  0.577046  0.404705
  25. 2000-01-08 -1.157892 -0.370647

The correct way to swap column values is by using raw values:

  1. In [12]: df.loc[:, ['B', 'A']] = df[['A', 'B']].to_numpy()
  2.  
  3. In [13]: df[['A', 'B']]
  4. Out[13]: 
  5.                    A         B
  6. 2000-01-01  0.469112 -0.282863
  7. 2000-01-02  1.212112 -0.173215
  8. 2000-01-03 -0.861849 -2.104569
  9. 2000-01-04  0.721555 -0.706771
  10. 2000-01-05 -0.424972  0.567020
  11. 2000-01-06 -0.673690  0.113648
  12. 2000-01-07  0.404705  0.577046
  13. 2000-01-08 -0.370647 -1.157892

Attribute access

You may access an index on a Series or column on a DataFrame directlyas an attribute:

  1. In [14]: sa = pd.Series([1, 2, 3], index=list('abc'))
  2.  
  3. In [15]: dfa = df.copy()
  1. In [16]: sa.b
  2. Out[16]: 2
  3.  
  4. In [17]: dfa.A
  5. Out[17]: 
  6. 2000-01-01    0.469112
  7. 2000-01-02    1.212112
  8. 2000-01-03   -0.861849
  9. 2000-01-04    0.721555
  10. 2000-01-05   -0.424972
  11. 2000-01-06   -0.673690
  12. 2000-01-07    0.404705
  13. 2000-01-08   -0.370647
  14. Freq: D, Name: A, dtype: float64
  1. In [18]: sa.a = 5
  2.  
  3. In [19]: sa
  4. Out[19]: 
  5. a    5
  6. b    2
  7. c    3
  8. dtype: int64
  9.  
  10. In [20]: dfa.A = list(range(len(dfa.index)))  # ok if A already exists
  11.  
  12. In [21]: dfa
  13. Out[21]: 
  14.             A         B         C         D
  15. 2000-01-01  0 -0.282863 -1.509059 -1.135632
  16. 2000-01-02  1 -0.173215  0.119209 -1.044236
  17. 2000-01-03  2 -2.104569 -0.494929  1.071804
  18. 2000-01-04  3 -0.706771 -1.039575  0.271860
  19. 2000-01-05  4  0.567020  0.276232 -1.087401
  20. 2000-01-06  5  0.113648 -1.478427  0.524988
  21. 2000-01-07  6  0.577046 -1.715002 -1.039268
  22. 2000-01-08  7 -1.157892 -1.344312  0.844885
  23.  
  24. In [22]: dfa['A'] = list(range(len(dfa.index)))  # use this form to create a new column
  25.  
  26. In [23]: dfa
  27. Out[23]: 
  28.             A         B         C         D
  29. 2000-01-01  0 -0.282863 -1.509059 -1.135632
  30. 2000-01-02  1 -0.173215  0.119209 -1.044236
  31. 2000-01-03  2 -2.104569 -0.494929  1.071804
  32. 2000-01-04  3 -0.706771 -1.039575  0.271860
  33. 2000-01-05  4  0.567020  0.276232 -1.087401
  34. 2000-01-06  5  0.113648 -1.478427  0.524988
  35. 2000-01-07  6  0.577046 -1.715002 -1.039268
  36. 2000-01-08  7 -1.157892 -1.344312  0.844885

Warning

  • You can use this access only if the index element is a valid Python identifier, e.g. s.1 is not allowed.See here for an explanation of valid identifiers.
  • The attribute will not be available if it conflicts with an existing method name, e.g. s.min is not allowed.
  • Similarly, the attribute will not be available if it conflicts with any of the following list: index,major_axis, minor_axis, items.
  • In any of these cases, standard indexing will still work, e.g. s['1'], s['min'], and s['index'] willaccess the corresponding element or column.

If you are using the IPython environment, you may also use tab-completion tosee these accessible attributes.

You can also assign a dict to a row of a DataFrame:

  1. In [24]: x = pd.DataFrame({'x': [1, 2, 3], 'y': [3, 4, 5]})
  2.  
  3. In [25]: x.iloc[1] = {'x': 9, 'y': 99}
  4.  
  5. In [26]: x
  6. Out[26]: 
  7.    x   y
  8. 0  1   3
  9. 1  9  99
  10. 2  3   5

You can use attribute access to modify an existing element of a Series or column of a DataFrame, but be careful;if you try to use attribute access to create a new column, it creates a new attribute rather than anew column. In 0.21.0 and later, this will raise a UserWarning:

  1. In [1]: df = pd.DataFrame({'one': [1., 2., 3.]})
  2. In [2]: df.two = [4, 5, 6]
  3. UserWarning: Pandas doesn't allow Series to be assigned into nonexistent columns - see https://pandas.pydata.org/pandas-docs/stable/indexing.html#attribute_access
  4. In [3]: df
  5. Out[3]:
  6.    one
  7. 0  1.0
  8. 1  2.0
  9. 2  3.0

Slicing ranges

The most robust and consistent way of slicing ranges along arbitrary axes isdescribed in the Selection by Position sectiondetailing the .iloc method. For now, we explain the semantics of slicing using the [] operator.

With Series, the syntax works exactly as with an ndarray, returning a slice ofthe values and the corresponding labels:

  1. In [27]: s[:5]
  2. Out[27]: 
  3. 2000-01-01    0.469112
  4. 2000-01-02    1.212112
  5. 2000-01-03   -0.861849
  6. 2000-01-04    0.721555
  7. 2000-01-05   -0.424972
  8. Freq: D, Name: A, dtype: float64
  9.  
  10. In [28]: s[::2]
  11. Out[28]: 
  12. 2000-01-01    0.469112
  13. 2000-01-03   -0.861849
  14. 2000-01-05   -0.424972
  15. 2000-01-07    0.404705
  16. Freq: 2D, Name: A, dtype: float64
  17.  
  18. In [29]: s[::-1]
  19. Out[29]: 
  20. 2000-01-08   -0.370647
  21. 2000-01-07    0.404705
  22. 2000-01-06   -0.673690
  23. 2000-01-05   -0.424972
  24. 2000-01-04    0.721555
  25. 2000-01-03   -0.861849
  26. 2000-01-02    1.212112
  27. 2000-01-01    0.469112
  28. Freq: -1D, Name: A, dtype: float64

Note that setting works as well:

  1. In [30]: s2 = s.copy()
  2.  
  3. In [31]: s2[:5] = 0
  4.  
  5. In [32]: s2
  6. Out[32]: 
  7. 2000-01-01    0.000000
  8. 2000-01-02    0.000000
  9. 2000-01-03    0.000000
  10. 2000-01-04    0.000000
  11. 2000-01-05    0.000000
  12. 2000-01-06   -0.673690
  13. 2000-01-07    0.404705
  14. 2000-01-08   -0.370647
  15. Freq: D, Name: A, dtype: float64

With DataFrame, slicing inside of [] slices the rows. This is providedlargely as a convenience since it is such a common operation.

  1. In [33]: df[:3]
  2. Out[33]: 
  3.                    A         B         C         D
  4. 2000-01-01  0.469112 -0.282863 -1.509059 -1.135632
  5. 2000-01-02  1.212112 -0.173215  0.119209 -1.044236
  6. 2000-01-03 -0.861849 -2.104569 -0.494929  1.071804
  7.  
  8. In [34]: df[::-1]
  9. Out[34]: 
  10.                    A         B         C         D
  11. 2000-01-08 -0.370647 -1.157892 -1.344312  0.844885
  12. 2000-01-07  0.404705  0.577046 -1.715002 -1.039268
  13. 2000-01-06 -0.673690  0.113648 -1.478427  0.524988
  14. 2000-01-05 -0.424972  0.567020  0.276232 -1.087401
  15. 2000-01-04  0.721555 -0.706771 -1.039575  0.271860
  16. 2000-01-03 -0.861849 -2.104569 -0.494929  1.071804
  17. 2000-01-02  1.212112 -0.173215  0.119209 -1.044236
  18. 2000-01-01  0.469112 -0.282863 -1.509059 -1.135632

Selection by label

Warning

Whether a copy or a reference is returned for a setting operation, may depend on the context.This is sometimes called chained assignment and should be avoided.See Returning a View versus Copy.

Warning

.loc is strict when you present slicers that are not compatible (or convertible) with the index type. For exampleusing integers in a DatetimeIndex. These will raise a TypeError.
  1. In [35]: dfl = pd.DataFrame(np.random.randn(5, 4),
  2.    ....:                    columns=list('ABCD'),
  3.    ....:                    index=pd.date_range('20130101', periods=5))
  4.    ....: 
  5.  
  6. In [36]: dfl
  7. Out[36]: 
  8.                    A         B         C         D
  9. 2013-01-01  1.075770 -0.109050  1.643563 -1.469388
  10. 2013-01-02  0.357021 -0.674600 -1.776904 -0.968914
  11. 2013-01-03 -1.294524  0.413738  0.276662 -0.472035
  12. 2013-01-04 -0.013960 -0.362543 -0.006154 -0.923061
  13. 2013-01-05  0.895717  0.805244 -1.206412  2.565646
  1. In [4]: dfl.loc[2:3]
  2. TypeError: cannot do slice indexing on <class 'pandas.tseries.index.DatetimeIndex'> with these indexers [2] of <type 'int'>

String likes in slicing can be convertible to the type of the index and lead to natural slicing.

  1. In [37]: dfl.loc['20130102':'20130104']
  2. Out[37]: 
  3.                    A         B         C         D
  4. 2013-01-02  0.357021 -0.674600 -1.776904 -0.968914
  5. 2013-01-03 -1.294524  0.413738  0.276662 -0.472035
  6. 2013-01-04 -0.013960 -0.362543 -0.006154 -0.923061

Warning

Starting in 0.21.0, pandas will show a FutureWarning if indexing with a list with missing labels. In the futurethis will raise a KeyError. See list-like Using loc with missing keys in a list is Deprecated.

pandas provides a suite of methods in order to have purely label based indexing. This is a strict inclusion based protocol.Every label asked for must be in the index, or a KeyError will be raised.When slicing, both the start bound AND the stop bound are included, if present in the index.Integers are valid labels, but they refer to the label and not the position.

The .loc attribute is the primary access method. The following are valid inputs:

  • A single label, e.g. 5 or 'a' (Note that 5 is interpreted as a label of the index. This use is not an integer position along the index.).
  • A list or array of labels ['a', 'b', 'c'].
  • A slice object with labels 'a':'f' (Note that contrary to usual pythonslices, both the start and the stop are included, when present in theindex! See Slicing with labels.
  • A boolean array.
  • A callable, see Selection By Callable.
  1. In [38]: s1 = pd.Series(np.random.randn(6), index=list('abcdef'))
  2.  
  3. In [39]: s1
  4. Out[39]: 
  5. a    1.431256
  6. b    1.340309
  7. c   -1.170299
  8. d   -0.226169
  9. e    0.410835
  10. f    0.813850
  11. dtype: float64
  12.  
  13. In [40]: s1.loc['c':]
  14. Out[40]: 
  15. c   -1.170299
  16. d   -0.226169
  17. e    0.410835
  18. f    0.813850
  19. dtype: float64
  20.  
  21. In [41]: s1.loc['b']
  22. Out[41]: 1.3403088497993827

Note that setting works as well:

  1. In [42]: s1.loc['c':] = 0
  2.  
  3. In [43]: s1
  4. Out[43]: 
  5. a    1.431256
  6. b    1.340309
  7. c    0.000000
  8. d    0.000000
  9. e    0.000000
  10. f    0.000000
  11. dtype: float64

With a DataFrame:

  1. In [44]: df1 = pd.DataFrame(np.random.randn(6, 4),
  2.    ....:                    index=list('abcdef'),
  3.    ....:                    columns=list('ABCD'))
  4.    ....: 
  5.  
  6. In [45]: df1
  7. Out[45]: 
  8.           A         B         C         D
  9. a  0.132003 -0.827317 -0.076467 -1.187678
  10. b  1.130127 -1.436737 -1.413681  1.607920
  11. c  1.024180  0.569605  0.875906 -2.211372
  12. d  0.974466 -2.006747 -0.410001 -0.078638
  13. e  0.545952 -1.219217 -1.226825  0.769804
  14. f -1.281247 -0.727707 -0.121306 -0.097883
  15.  
  16. In [46]: df1.loc[['a', 'b', 'd'], :]
  17. Out[46]: 
  18.           A         B         C         D
  19. a  0.132003 -0.827317 -0.076467 -1.187678
  20. b  1.130127 -1.436737 -1.413681  1.607920
  21. d  0.974466 -2.006747 -0.410001 -0.078638

Accessing via label slices:

  1. In [47]: df1.loc['d':, 'A':'C']
  2. Out[47]: 
  3.           A         B         C
  4. d  0.974466 -2.006747 -0.410001
  5. e  0.545952 -1.219217 -1.226825
  6. f -1.281247 -0.727707 -0.121306

For getting a cross section using a label (equivalent to df.xs('a')):

  1. In [48]: df1.loc['a']
  2. Out[48]: 
  3. A    0.132003
  4. B   -0.827317
  5. C   -0.076467
  6. D   -1.187678
  7. Name: a, dtype: float64

For getting values with a boolean array:

  1. In [49]: df1.loc['a'] > 0
  2. Out[49]: 
  3. A     True
  4. B    False
  5. C    False
  6. D    False
  7. Name: a, dtype: bool
  8.  
  9. In [50]: df1.loc[:, df1.loc['a'] > 0]
  10. Out[50]: 
  11.           A
  12. a  0.132003
  13. b  1.130127
  14. c  1.024180
  15. d  0.974466
  16. e  0.545952
  17. f -1.281247

For getting a value explicitly (equivalent to deprecated df.get_value('a','A')):

  1. # this is also equivalent to ``df1.at['a','A']``
  2. In [51]: df1.loc['a', 'A']
  3. Out[51]: 0.13200317033032932

Slicing with labels

When using .loc with slices, if both the start and the stop labels arepresent in the index, then elements located between the two (including them)are returned:

  1. In [52]: s = pd.Series(list('abcde'), index=[0, 3, 2, 5, 4])
  2.  
  3. In [53]: s.loc[3:5]
  4. Out[53]: 
  5. 3    b
  6. 2    c
  7. 5    d
  8. dtype: object

If at least one of the two is absent, but the index is sorted, and can becompared against start and stop labels, then slicing will still work asexpected, by selecting labels which rank between the two:

  1. In [54]: s.sort_index()
  2. Out[54]: 
  3. 0    a
  4. 2    c
  5. 3    b
  6. 4    e
  7. 5    d
  8. dtype: object
  9.  
  10. In [55]: s.sort_index().loc[1:6]
  11. Out[55]: 
  12. 2    c
  13. 3    b
  14. 4    e
  15. 5    d
  16. dtype: object

However, if at least one of the two is absent and the index is not sorted, anerror will be raised (since doing otherwise would be computationally expensive,as well as potentially ambiguous for mixed type indexes). For instance, in theabove example, s.loc[1:6] would raise KeyError.

For the rationale behind this behavior, seeEndpoints are inclusive.

Selection by position

Warning

Whether a copy or a reference is returned for a setting operation, may depend on the context.This is sometimes called chained assignment and should be avoided.See Returning a View versus Copy.

Pandas provides a suite of methods in order to get purely integer based indexing. The semantics follow closely Python and NumPy slicing. These are 0-based indexing. When slicing, the start bound is included, while the upper bound is excluded. Trying to use a non-integer, even a valid label will raise an IndexError.

The .iloc attribute is the primary access method. The following are valid inputs:

  • An integer e.g. 5.
  • A list or array of integers [4, 3, 0].
  • A slice object with ints 1:7.
  • A boolean array.
  • A callable, see Selection By Callable.
  1. In [56]: s1 = pd.Series(np.random.randn(5), index=list(range(0, 10, 2)))
  2.  
  3. In [57]: s1
  4. Out[57]: 
  5. 0    0.695775
  6. 2    0.341734
  7. 4    0.959726
  8. 6   -1.110336
  9. 8   -0.619976
  10. dtype: float64
  11.  
  12. In [58]: s1.iloc[:3]
  13. Out[58]: 
  14. 0    0.695775
  15. 2    0.341734
  16. 4    0.959726
  17. dtype: float64
  18.  
  19. In [59]: s1.iloc[3]
  20. Out[59]: -1.110336102891167

Note that setting works as well:

  1. In [60]: s1.iloc[:3] = 0
  2.  
  3. In [61]: s1
  4. Out[61]: 
  5. 0    0.000000
  6. 2    0.000000
  7. 4    0.000000
  8. 6   -1.110336
  9. 8   -0.619976
  10. dtype: float64

With a DataFrame:

  1. In [62]: df1 = pd.DataFrame(np.random.randn(6, 4),
  2.    ....:                    index=list(range(0, 12, 2)),
  3.    ....:                    columns=list(range(0, 8, 2)))
  4.    ....: 
  5.  
  6. In [63]: df1
  7. Out[63]: 
  8.            0         2         4         6
  9. 0   0.149748 -0.732339  0.687738  0.176444
  10. 2   0.403310 -0.154951  0.301624 -2.179861
  11. 4  -1.369849 -0.954208  1.462696 -1.743161
  12. 6  -0.826591 -0.345352  1.314232  0.690579
  13. 8   0.995761  2.396780  0.014871  3.357427
  14. 10 -0.317441 -1.236269  0.896171 -0.487602

Select via integer slicing:

  1. In [64]: df1.iloc[:3]
  2. Out[64]: 
  3.           0         2         4         6
  4. 0  0.149748 -0.732339  0.687738  0.176444
  5. 2  0.403310 -0.154951  0.301624 -2.179861
  6. 4 -1.369849 -0.954208  1.462696 -1.743161
  7.  
  8. In [65]: df1.iloc[1:5, 2:4]
  9. Out[65]: 
  10.           4         6
  11. 2  0.301624 -2.179861
  12. 4  1.462696 -1.743161
  13. 6  1.314232  0.690579
  14. 8  0.014871  3.357427

Select via integer list:

  1. In [66]: df1.iloc[[1, 3, 5], [1, 3]]
  2. Out[66]: 
  3.            2         6
  4. 2  -0.154951 -2.179861
  5. 6  -0.345352  0.690579
  6. 10 -1.236269 -0.487602
  1. In [67]: df1.iloc[1:3, :]
  2. Out[67]: 
  3.           0         2         4         6
  4. 2  0.403310 -0.154951  0.301624 -2.179861
  5. 4 -1.369849 -0.954208  1.462696 -1.743161
  1. In [68]: df1.iloc[:, 1:3]
  2. Out[68]: 
  3.            2         4
  4. 0  -0.732339  0.687738
  5. 2  -0.154951  0.301624
  6. 4  -0.954208  1.462696
  7. 6  -0.345352  1.314232
  8. 8   2.396780  0.014871
  9. 10 -1.236269  0.896171
  1. # this is also equivalent to ``df1.iat[1,1]``
  2. In [69]: df1.iloc[1, 1]
  3. Out[69]: -0.1549507744249032

For getting a cross section using an integer position (equiv to df.xs(1)):

  1. In [70]: df1.iloc[1]
  2. Out[70]: 
  3. 0    0.403310
  4. 2   -0.154951
  5. 4    0.301624
  6. 6   -2.179861
  7. Name: 2, dtype: float64

Out of range slice indexes are handled gracefully just as in Python/Numpy.

  1. # these are allowed in python/numpy.
  2. In [71]: x = list('abcdef')
  3.  
  4. In [72]: x
  5. Out[72]: ['a', 'b', 'c', 'd', 'e', 'f']
  6.  
  7. In [73]: x[4:10]
  8. Out[73]: ['e', 'f']
  9.  
  10. In [74]: x[8:10]
  11. Out[74]: []
  12.  
  13. In [75]: s = pd.Series(x)
  14.  
  15. In [76]: s
  16. Out[76]: 
  17. 0    a
  18. 1    b
  19. 2    c
  20. 3    d
  21. 4    e
  22. 5    f
  23. dtype: object
  24.  
  25. In [77]: s.iloc[4:10]
  26. Out[77]: 
  27. 4    e
  28. 5    f
  29. dtype: object
  30.  
  31. In [78]: s.iloc[8:10]
  32. Out[78]: Series([], dtype: object)

Note that using slices that go out of bounds can result inan empty axis (e.g. an empty DataFrame being returned).

  1. In [79]: dfl = pd.DataFrame(np.random.randn(5, 2), columns=list('AB'))
  2.  
  3. In [80]: dfl
  4. Out[80]: 
  5.           A         B
  6. 0 -0.082240 -2.182937
  7. 1  0.380396  0.084844
  8. 2  0.432390  1.519970
  9. 3 -0.493662  0.600178
  10. 4  0.274230  0.132885
  11.  
  12. In [81]: dfl.iloc[:, 2:3]
  13. Out[81]: 
  14. Empty DataFrame
  15. Columns: []
  16. Index: [0, 1, 2, 3, 4]
  17.  
  18. In [82]: dfl.iloc[:, 1:3]
  19. Out[82]: 
  20.           B
  21. 0 -2.182937
  22. 1  0.084844
  23. 2  1.519970
  24. 3  0.600178
  25. 4  0.132885
  26.  
  27. In [83]: dfl.iloc[4:6]
  28. Out[83]: 
  29.          A         B
  30. 4  0.27423  0.132885

A single indexer that is out of bounds will raise an IndexError.A list of indexers where any element is out of bounds will raise anIndexError.

  1. >>> dfl.iloc[[4, 5, 6]]
  2. IndexError: positional indexers are out-of-bounds
  3.  
  4. >>> dfl.iloc[:, 4]
  5. IndexError: single positional indexer is out-of-bounds

Selection by callable

New in version 0.18.1.

.loc, .iloc, and also [] indexing can accept a callable as indexer.The callable must be a function with one argument (the calling Series or DataFrame) that returns valid output for indexing.

  1. In [84]: df1 = pd.DataFrame(np.random.randn(6, 4),
  2.    ....:                    index=list('abcdef'),
  3.    ....:                    columns=list('ABCD'))
  4.    ....: 
  5.  
  6. In [85]: df1
  7. Out[85]: 
  8.           A         B         C         D
  9. a -0.023688  2.410179  1.450520  0.206053
  10. b -0.251905 -2.213588  1.063327  1.266143
  11. c  0.299368 -0.863838  0.408204 -1.048089
  12. d -0.025747 -0.988387  0.094055  1.262731
  13. e  1.289997  0.082423 -0.055758  0.536580
  14. f -0.489682  0.369374 -0.034571 -2.484478
  15.  
  16. In [86]: df1.loc[lambda df: df.A > 0, :]
  17. Out[86]: 
  18.           A         B         C         D
  19. c  0.299368 -0.863838  0.408204 -1.048089
  20. e  1.289997  0.082423 -0.055758  0.536580
  21.  
  22. In [87]: df1.loc[:, lambda df: ['A', 'B']]
  23. Out[87]: 
  24.           A         B
  25. a -0.023688  2.410179
  26. b -0.251905 -2.213588
  27. c  0.299368 -0.863838
  28. d -0.025747 -0.988387
  29. e  1.289997  0.082423
  30. f -0.489682  0.369374
  31.  
  32. In [88]: df1.iloc[:, lambda df: [0, 1]]
  33. Out[88]: 
  34.           A         B
  35. a -0.023688  2.410179
  36. b -0.251905 -2.213588
  37. c  0.299368 -0.863838
  38. d -0.025747 -0.988387
  39. e  1.289997  0.082423
  40. f -0.489682  0.369374
  41.  
  42. In [89]: df1[lambda df: df.columns[0]]
  43. Out[89]: 
  44. a   -0.023688
  45. b   -0.251905
  46. c    0.299368
  47. d   -0.025747
  48. e    1.289997
  49. f   -0.489682
  50. Name: A, dtype: float64

You can use callable indexing in Series.

  1. In [90]: df1.A.loc[lambda s: s > 0]
  2. Out[90]: 
  3. c    0.299368
  4. e    1.289997
  5. Name: A, dtype: float64

Using these methods / indexers, you can chain data selection operationswithout using a temporary variable.

  1. In [91]: bb = pd.read_csv('data/baseball.csv', index_col='id')
  2.  
  3. In [92]: (bb.groupby(['year', 'team']).sum()
  4.    ....:    .loc[lambda df: df.r > 100])
  5.    ....: 
  6. Out[92]: 
  7.            stint    g    ab    r    h  X2b  X3b  hr    rbi    sb   cs   bb     so   ibb   hbp    sh    sf  gidp
  8. year team                                                                                                      
  9. 2007 CIN       6  379   745  101  203   35    2  36  125.0  10.0  1.0  105  127.0  14.0   1.0   1.0  15.0  18.0
  10.      DET       5  301  1062  162  283   54    4  37  144.0  24.0  7.0   97  176.0   3.0  10.0   4.0   8.0  28.0
  11.      HOU       4  311   926  109  218   47    6  14   77.0  10.0  4.0   60  212.0   3.0   9.0  16.0   6.0  17.0
  12.      LAN      11  413  1021  153  293   61    3  36  154.0   7.0  5.0  114  141.0   8.0   9.0   3.0   8.0  29.0
  13.      NYN      13  622  1854  240  509  101    3  61  243.0  22.0  4.0  174  310.0  24.0  23.0  18.0  15.0  48.0
  14.      SFN       5  482  1305  198  337   67    6  40  171.0  26.0  7.0  235  188.0  51.0   8.0  16.0   6.0  41.0
  15.      TEX       2  198   729  115  200   40    4  28  115.0  21.0  4.0   73  140.0   4.0   5.0   2.0   8.0  16.0
  16.      TOR       4  459  1408  187  378   96    2  58  223.0   4.0  2.0  190  265.0  16.0  12.0   4.0  16.0  38.0

IX indexer is deprecated

Warning

Starting in 0.20.0, the .ix indexer is deprecated, in favor of the more strict .ilocand .loc indexers.

.ix offers a lot of magic on the inference of what the user wants to do. To wit, .ix can decideto index positionally OR via labels depending on the data type of the index. This has caused quite abit of user confusion over the years.

The recommended methods of indexing are:

  • .loc if you want to label index.
  • .iloc if you want to positionally index.
  1. In [93]: dfd = pd.DataFrame({'A': [1, 2, 3],
  2.    ....:                     'B': [4, 5, 6]},
  3.    ....:                    index=list('abc'))
  4.    ....: 
  5.  
  6. In [94]: dfd
  7. Out[94]: 
  8.    A  B
  9. a  1  4
  10. b  2  5
  11. c  3  6

Previous behavior, where you wish to get the 0th and the 2nd elements from the index in the ‘A’ column.

  1. In [3]: dfd.ix[[0, 2], 'A']
  2. Out[3]:
  3. a    1
  4. c    3
  5. Name: A, dtype: int64

Using .loc. Here we will select the appropriate indexes from the index, then use label indexing.

  1. In [95]: dfd.loc[dfd.index[[0, 2]], 'A']
  2. Out[95]: 
  3. a    1
  4. c    3
  5. Name: A, dtype: int64

This can also be expressed using .iloc, by explicitly getting locations on the indexers, and usingpositional indexing to select things.

  1. In [96]: dfd.iloc[[0, 2], dfd.columns.get_loc('A')]
  2. Out[96]: 
  3. a    1
  4. c    3
  5. Name: A, dtype: int64

For getting multiple indexers, using .get_indexer:

  1. In [97]: dfd.iloc[[0, 2], dfd.columns.get_indexer(['A', 'B'])]
  2. Out[97]: 
  3.    A  B
  4. a  1  4
  5. c  3  6

Indexing with list with missing labels is deprecated

Warning

Starting in 0.21.0, using .loc or [] with a list with one or more missing labels, is deprecated, in favor of .reindex.

In prior versions, using .loc[list-of-labels] would work as long as at least 1 of the keys was found (otherwise itwould raise a KeyError). This behavior is deprecated and will show a warning message pointing to this section. Therecommended alternative is to use .reindex().

For example.

  1. In [98]: s = pd.Series([1, 2, 3])
  2.  
  3. In [99]: s
  4. Out[99]: 
  5. 0    1
  6. 1    2
  7. 2    3
  8. dtype: int64

Selection with all keys found is unchanged.

  1. In [100]: s.loc[[1, 2]]
  2. Out[100]: 
  3. 1    2
  4. 2    3
  5. dtype: int64

Previous behavior

  1. In [4]: s.loc[[1, 2, 3]]
  2. Out[4]:
  3. 1    2.0
  4. 2    3.0
  5. 3    NaN
  6. dtype: float64

Current behavior

  1. In [4]: s.loc[[1, 2, 3]]
  2. Passing list-likes to .loc with any non-matching elements will raise
  3. KeyError in the future, you can use .reindex() as an alternative.
  4.  
  5. See the documentation here:
  6. http://pandas.pydata.org/pandas-docs/stable/indexing.html#deprecate-loc-reindex-listlike
  7.  
  8. Out[4]:
  9. 1    2.0
  10. 2    3.0
  11. 3    NaN
  12. dtype: float64

Reindexing

The idiomatic way to achieve selecting potentially not-found elements is via .reindex(). See also the section on reindexing.

  1. In [101]: s.reindex([1, 2, 3])
  2. Out[101]: 
  3. 1    2.0
  4. 2    3.0
  5. 3    NaN
  6. dtype: float64

Alternatively, if you want to select only valid keys, the following is idiomatic and efficient; it is guaranteed to preserve the dtype of the selection.

  1. In [102]: labels = [1, 2, 3]
  2.  
  3. In [103]: s.loc[s.index.intersection(labels)]
  4. Out[103]: 
  5. 1    2
  6. 2    3
  7. dtype: int64

Having a duplicated index will raise for a .reindex():

  1. In [104]: s = pd.Series(np.arange(4), index=['a', 'a', 'b', 'c'])
  2.  
  3. In [105]: labels = ['c', 'd']
  1. In [17]: s.reindex(labels)
  2. ValueError: cannot reindex from a duplicate axis

Generally, you can intersect the desired labels with the currentaxis, and then reindex.

  1. In [106]: s.loc[s.index.intersection(labels)].reindex(labels)
  2. Out[106]: 
  3. c    3.0
  4. d    NaN
  5. dtype: float64

However, this would still raise if your resulting index is duplicated.

  1. In [41]: labels = ['a', 'd']
  2.  
  3. In [42]: s.loc[s.index.intersection(labels)].reindex(labels)
  4. ValueError: cannot reindex from a duplicate axis

Selecting random samples

A random selection of rows or columns from a Series or DataFrame with the sample() method. The method will sample rows by default, and accepts a specific number of rows/columns to return, or a fraction of rows.

  1. In [107]: s = pd.Series([0, 1, 2, 3, 4, 5])
  2.  
  3. # When no arguments are passed, returns 1 row.
  4. In [108]: s.sample()
  5. Out[108]: 
  6. 4    4
  7. dtype: int64
  8.  
  9. # One may specify either a number of rows:
  10. In [109]: s.sample(n=3)
  11. Out[109]: 
  12. 0    0
  13. 4    4
  14. 1    1
  15. dtype: int64
  16.  
  17. # Or a fraction of the rows:
  18. In [110]: s.sample(frac=0.5)
  19. Out[110]: 
  20. 5    5
  21. 3    3
  22. 1    1
  23. dtype: int64

By default, sample will return each row at most once, but one can also sample with replacementusing the replace option:

  1. In [111]: s = pd.Series([0, 1, 2, 3, 4, 5])
  2.  
  3. # Without replacement (default):
  4. In [112]: s.sample(n=6, replace=False)
  5. Out[112]: 
  6. 0    0
  7. 1    1
  8. 5    5
  9. 3    3
  10. 2    2
  11. 4    4
  12. dtype: int64
  13.  
  14. # With replacement:
  15. In [113]: s.sample(n=6, replace=True)
  16. Out[113]: 
  17. 0    0
  18. 4    4
  19. 3    3
  20. 2    2
  21. 4    4
  22. 4    4
  23. dtype: int64

By default, each row has an equal probability of being selected, but if you want rowsto have different probabilities, you can pass the sample function sampling weights asweights. These weights can be a list, a NumPy array, or a Series, but they must be of the same length as the object you are sampling. Missing values will be treated as a weight of zero, and inf values are not allowed. If weights do not sum to 1, they will be re-normalized by dividing all weights by the sum of the weights. For example:

  1. In [114]: s = pd.Series([0, 1, 2, 3, 4, 5])
  2.  
  3. In [115]: example_weights = [0, 0, 0.2, 0.2, 0.2, 0.4]
  4.  
  5. In [116]: s.sample(n=3, weights=example_weights)
  6. Out[116]: 
  7. 5    5
  8. 4    4
  9. 3    3
  10. dtype: int64
  11.  
  12. # Weights will be re-normalized automatically
  13. In [117]: example_weights2 = [0.5, 0, 0, 0, 0, 0]
  14.  
  15. In [118]: s.sample(n=1, weights=example_weights2)
  16. Out[118]: 
  17. 0    0
  18. dtype: int64

When applied to a DataFrame, you can use a column of the DataFrame as sampling weights(provided you are sampling rows and not columns) by simply passing the name of the columnas a string.

  1. In [119]: df2 = pd.DataFrame({'col1': [9, 8, 7, 6],
  2.    .....:                     'weight_column': [0.5, 0.4, 0.1, 0]})
  3.    .....: 
  4.  
  5. In [120]: df2.sample(n=3, weights='weight_column')
  6. Out[120]: 
  7.    col1  weight_column
  8. 1     8            0.4
  9. 0     9            0.5
  10. 2     7            0.1

sample also allows users to sample columns instead of rows using the axis argument.

  1. In [121]: df3 = pd.DataFrame({'col1': [1, 2, 3], 'col2': [2, 3, 4]})
  2.  
  3. In [122]: df3.sample(n=1, axis=1)
  4. Out[122]: 
  5.    col1
  6. 0     1
  7. 1     2
  8. 2     3

Finally, one can also set a seed for sample’s random number generator using the random_state argument, which will accept either an integer (as a seed) or a NumPy RandomState object.

  1. In [123]: df4 = pd.DataFrame({'col1': [1, 2, 3], 'col2': [2, 3, 4]})
  2.  
  3. # With a given seed, the sample will always draw the same rows.
  4. In [124]: df4.sample(n=2, random_state=2)
  5. Out[124]: 
  6.    col1  col2
  7. 2     3     4
  8. 1     2     3
  9.  
  10. In [125]: df4.sample(n=2, random_state=2)
  11. Out[125]: 
  12.    col1  col2
  13. 2     3     4
  14. 1     2     3

Setting with enlargement

The .loc/[] operations can perform enlargement when setting a non-existent key for that axis.

In the Series case this is effectively an appending operation.

  1. In [126]: se = pd.Series([1, 2, 3])
  2.  
  3. In [127]: se
  4. Out[127]: 
  5. 0    1
  6. 1    2
  7. 2    3
  8. dtype: int64
  9.  
  10. In [128]: se[5] = 5.
  11.  
  12. In [129]: se
  13. Out[129]: 
  14. 0    1.0
  15. 1    2.0
  16. 2    3.0
  17. 5    5.0
  18. dtype: float64

A DataFrame can be enlarged on either axis via .loc.

  1. In [130]: dfi = pd.DataFrame(np.arange(6).reshape(3, 2),
  2.    .....:                    columns=['A', 'B'])
  3.    .....: 
  4.  
  5. In [131]: dfi
  6. Out[131]: 
  7.    A  B
  8. 0  0  1
  9. 1  2  3
  10. 2  4  5
  11.  
  12. In [132]: dfi.loc[:, 'C'] = dfi.loc[:, 'A']
  13.  
  14. In [133]: dfi
  15. Out[133]: 
  16.    A  B  C
  17. 0  0  1  0
  18. 1  2  3  2
  19. 2  4  5  4

This is like an append operation on the DataFrame.

  1. In [134]: dfi.loc[3] = 5
  2.  
  3. In [135]: dfi
  4. Out[135]: 
  5.    A  B  C
  6. 0  0  1  0
  7. 1  2  3  2
  8. 2  4  5  4
  9. 3  5  5  5

Fast scalar value getting and setting

Since indexing with [] must handle a lot of cases (single-label access,slicing, boolean indexing, etc.), it has a bit of overhead in order to figureout what you’re asking for. If you only want to access a scalar value, thefastest way is to use the at and iat methods, which are implemented onall of the data structures.

Similarly to loc, at provides label based scalar lookups, while, iat provides integer based lookups analogously to iloc

  1. In [136]: s.iat[5]
  2. Out[136]: 5
  3.  
  4. In [137]: df.at[dates[5], 'A']
  5. Out[137]: -0.6736897080883706
  6.  
  7. In [138]: df.iat[3, 0]
  8. Out[138]: 0.7215551622443669

You can also set using these same indexers.

  1. In [139]: df.at[dates[5], 'E'] = 7
  2.  
  3. In [140]: df.iat[3, 0] = 7

at may enlarge the object in-place as above if the indexer is missing.

  1. In [141]: df.at[dates[-1] + pd.Timedelta('1 day'), 0] = 7
  2.  
  3. In [142]: df
  4. Out[142]: 
  5.                    A         B         C         D    E    0
  6. 2000-01-01  0.469112 -0.282863 -1.509059 -1.135632  NaN  NaN
  7. 2000-01-02  1.212112 -0.173215  0.119209 -1.044236  NaN  NaN
  8. 2000-01-03 -0.861849 -2.104569 -0.494929  1.071804  NaN  NaN
  9. 2000-01-04  7.000000 -0.706771 -1.039575  0.271860  NaN  NaN
  10. 2000-01-05 -0.424972  0.567020  0.276232 -1.087401  NaN  NaN
  11. 2000-01-06 -0.673690  0.113648 -1.478427  0.524988  7.0  NaN
  12. 2000-01-07  0.404705  0.577046 -1.715002 -1.039268  NaN  NaN
  13. 2000-01-08 -0.370647 -1.157892 -1.344312  0.844885  NaN  NaN
  14. 2000-01-09       NaN       NaN       NaN       NaN  NaN  7.0

Boolean indexing

Another common operation is the use of boolean vectors to filter the data.The operators are: | for or, & for and, and ~ for not.These must be grouped by using parentheses, since by default Python willevaluate an expression such as df.A > 2 & df.B < 3 asdf.A > (2 & df.B) < 3, while the desired evaluation order is(df.A > 2) & (df.B < 3).

Using a boolean vector to index a Series works exactly as in a NumPy ndarray:

  1. In [143]: s = pd.Series(range(-3, 4))
  2.  
  3. In [144]: s
  4. Out[144]: 
  5. 0   -3
  6. 1   -2
  7. 2   -1
  8. 3    0
  9. 4    1
  10. 5    2
  11. 6    3
  12. dtype: int64
  13.  
  14. In [145]: s[s > 0]
  15. Out[145]: 
  16. 4    1
  17. 5    2
  18. 6    3
  19. dtype: int64
  20.  
  21. In [146]: s[(s < -1) | (s > 0.5)]
  22. Out[146]: 
  23. 0   -3
  24. 1   -2
  25. 4    1
  26. 5    2
  27. 6    3
  28. dtype: int64
  29.  
  30. In [147]: s[~(s < 0)]
  31. Out[147]: 
  32. 3    0
  33. 4    1
  34. 5    2
  35. 6    3
  36. dtype: int64

You may select rows from a DataFrame using a boolean vector the same length asthe DataFrame’s index (for example, something derived from one of the columnsof the DataFrame):

  1. In [148]: df[df['A'] > 0]
  2. Out[148]: 
  3.                    A         B         C         D   E   0
  4. 2000-01-01  0.469112 -0.282863 -1.509059 -1.135632 NaN NaN
  5. 2000-01-02  1.212112 -0.173215  0.119209 -1.044236 NaN NaN
  6. 2000-01-04  7.000000 -0.706771 -1.039575  0.271860 NaN NaN
  7. 2000-01-07  0.404705  0.577046 -1.715002 -1.039268 NaN NaN

List comprehensions and the map method of Series can also be used to producemore complex criteria:

  1. In [149]: df2 = pd.DataFrame({'a': ['one', 'one', 'two', 'three', 'two', 'one', 'six'],
  2.    .....:                     'b': ['x', 'y', 'y', 'x', 'y', 'x', 'x'],
  3.    .....:                     'c': np.random.randn(7)})
  4.    .....: 
  5.  
  6. # only want 'two' or 'three'
  7. In [150]: criterion = df2['a'].map(lambda x: x.startswith('t'))
  8.  
  9. In [151]: df2[criterion]
  10. Out[151]: 
  11.        a  b         c
  12. 2    two  y  0.041290
  13. 3  three  x  0.361719
  14. 4    two  y -0.238075
  15.  
  16. # equivalent but slower
  17. In [152]: df2[[x.startswith('t') for x in df2['a']]]
  18. Out[152]: 
  19.        a  b         c
  20. 2    two  y  0.041290
  21. 3  three  x  0.361719
  22. 4    two  y -0.238075
  23.  
  24. # Multiple criteria
  25. In [153]: df2[criterion & (df2['b'] == 'x')]
  26. Out[153]: 
  27.        a  b         c
  28. 3  three  x  0.361719

With the choice methods Selection by Label, Selection by Position,and Advanced Indexing you may select along more than one axis using boolean vectors combined with other indexing expressions.

  1. In [154]: df2.loc[criterion & (df2['b'] == 'x'), 'b':'c']
  2. Out[154]: 
  3.    b         c
  4. 3  x  0.361719

Indexing with isin

Consider the isin() method of Series, which returns a booleanvector that is true wherever the Series elements exist in the passed list.This allows you to select rows where one or more columns have values you want:

  1. In [155]: s = pd.Series(np.arange(5), index=np.arange(5)[::-1], dtype='int64')
  2.  
  3. In [156]: s
  4. Out[156]: 
  5. 4    0
  6. 3    1
  7. 2    2
  8. 1    3
  9. 0    4
  10. dtype: int64
  11.  
  12. In [157]: s.isin([2, 4, 6])
  13. Out[157]: 
  14. 4    False
  15. 3    False
  16. 2     True
  17. 1    False
  18. 0     True
  19. dtype: bool
  20.  
  21. In [158]: s[s.isin([2, 4, 6])]
  22. Out[158]: 
  23. 2    2
  24. 0    4
  25. dtype: int64

The same method is available for Index objects and is useful for the caseswhen you don’t know which of the sought labels are in fact present:

  1. In [159]: s[s.index.isin([2, 4, 6])]
  2. Out[159]: 
  3. 4    0
  4. 2    2
  5. dtype: int64
  6.  
  7. # compare it to the following
  8. In [160]: s.reindex([2, 4, 6])
  9. Out[160]: 
  10. 2    2.0
  11. 4    0.0
  12. 6    NaN
  13. dtype: float64

In addition to that, MultiIndex allows selecting a separate level to usein the membership check:

  1. In [161]: s_mi = pd.Series(np.arange(6),
  2.    .....:                  index=pd.MultiIndex.from_product([[0, 1], ['a', 'b', 'c']]))
  3.    .....: 
  4.  
  5. In [162]: s_mi
  6. Out[162]: 
  7. 0  a    0
  8.    b    1
  9.    c    2
  10. 1  a    3
  11.    b    4
  12.    c    5
  13. dtype: int64
  14.  
  15. In [163]: s_mi.iloc[s_mi.index.isin([(1, 'a'), (2, 'b'), (0, 'c')])]
  16. Out[163]: 
  17. 0  c    2
  18. 1  a    3
  19. dtype: int64
  20.  
  21. In [164]: s_mi.iloc[s_mi.index.isin(['a', 'c', 'e'], level=1)]
  22. Out[164]: 
  23. 0  a    0
  24.    c    2
  25. 1  a    3
  26.    c    5
  27. dtype: int64

DataFrame also has an isin() method. When calling isin, pass a set ofvalues as either an array or dict. If values is an array, isin returnsa DataFrame of booleans that is the same shape as the original DataFrame, with Truewherever the element is in the sequence of values.

  1. In [165]: df = pd.DataFrame({'vals': [1, 2, 3, 4], 'ids': ['a', 'b', 'f', 'n'],
  2.    .....:                    'ids2': ['a', 'n', 'c', 'n']})
  3.    .....: 
  4.  
  5. In [166]: values = ['a', 'b', 1, 3]
  6.  
  7. In [167]: df.isin(values)
  8. Out[167]: 
  9.     vals    ids   ids2
  10. 0   True   True   True
  11. 1  False   True  False
  12. 2   True  False  False
  13. 3  False  False  False

Oftentimes you’ll want to match certain values with certain columns.Just make values a dict where the key is the column, and the value isa list of items you want to check for.

  1. In [168]: values = {'ids': ['a', 'b'], 'vals': [1, 3]}
  2.  
  3. In [169]: df.isin(values)
  4. Out[169]: 
  5.     vals    ids   ids2
  6. 0   True   True  False
  7. 1  False   True  False
  8. 2   True  False  False
  9. 3  False  False  False

Combine DataFrame’s isin with the any() and all() methods toquickly select subsets of your data that meet a given criteria.To select a row where each column meets its own criterion:

  1. In [170]: values = {'ids': ['a', 'b'], 'ids2': ['a', 'c'], 'vals': [1, 3]}
  2.  
  3. In [171]: row_mask = df.isin(values).all(1)
  4.  
  5. In [172]: df[row_mask]
  6. Out[172]: 
  7.    vals ids ids2
  8. 0     1   a    a

The where() Method and Masking

Selecting values from a Series with a boolean vector generally returns asubset of the data. To guarantee that selection output has the same shape asthe original data, you can use the where method in Series and DataFrame.

To return only the selected rows:

  1. In [173]: s[s > 0]
  2. Out[173]: 
  3. 3    1
  4. 2    2
  5. 1    3
  6. 0    4
  7. dtype: int64

To return a Series of the same shape as the original:

  1. In [174]: s.where(s > 0)
  2. Out[174]: 
  3. 4    NaN
  4. 3    1.0
  5. 2    2.0
  6. 1    3.0
  7. 0    4.0
  8. dtype: float64

Selecting values from a DataFrame with a boolean criterion now also preservesinput data shape. where is used under the hood as the implementation.The code below is equivalent to df.where(df < 0).

  1. In [175]: df[df < 0]
  2. Out[175]: 
  3.                    A         B         C         D
  4. 2000-01-01 -2.104139 -1.309525       NaN       NaN
  5. 2000-01-02 -0.352480       NaN -1.192319       NaN
  6. 2000-01-03 -0.864883       NaN -0.227870       NaN
  7. 2000-01-04       NaN -1.222082       NaN -1.233203
  8. 2000-01-05       NaN -0.605656 -1.169184       NaN
  9. 2000-01-06       NaN -0.948458       NaN -0.684718
  10. 2000-01-07 -2.670153 -0.114722       NaN -0.048048
  11. 2000-01-08       NaN       NaN -0.048788 -0.808838

In addition, where takes an optional other argument for replacement ofvalues where the condition is False, in the returned copy.

  1. In [176]: df.where(df < 0, -df)
  2. Out[176]: 
  3.                    A         B         C         D
  4. 2000-01-01 -2.104139 -1.309525 -0.485855 -0.245166
  5. 2000-01-02 -0.352480 -0.390389 -1.192319 -1.655824
  6. 2000-01-03 -0.864883 -0.299674 -0.227870 -0.281059
  7. 2000-01-04 -0.846958 -1.222082 -0.600705 -1.233203
  8. 2000-01-05 -0.669692 -0.605656 -1.169184 -0.342416
  9. 2000-01-06 -0.868584 -0.948458 -2.297780 -0.684718
  10. 2000-01-07 -2.670153 -0.114722 -0.168904 -0.048048
  11. 2000-01-08 -0.801196 -1.392071 -0.048788 -0.808838

You may wish to set values based on some boolean criteria.This can be done intuitively like so:

  1. In [177]: s2 = s.copy()
  2.  
  3. In [178]: s2[s2 < 0] = 0
  4.  
  5. In [179]: s2
  6. Out[179]: 
  7. 4    0
  8. 3    1
  9. 2    2
  10. 1    3
  11. 0    4
  12. dtype: int64
  13.  
  14. In [180]: df2 = df.copy()
  15.  
  16. In [181]: df2[df2 < 0] = 0
  17.  
  18. In [182]: df2
  19. Out[182]: 
  20.                    A         B         C         D
  21. 2000-01-01  0.000000  0.000000  0.485855  0.245166
  22. 2000-01-02  0.000000  0.390389  0.000000  1.655824
  23. 2000-01-03  0.000000  0.299674  0.000000  0.281059
  24. 2000-01-04  0.846958  0.000000  0.600705  0.000000
  25. 2000-01-05  0.669692  0.000000  0.000000  0.342416
  26. 2000-01-06  0.868584  0.000000  2.297780  0.000000
  27. 2000-01-07  0.000000  0.000000  0.168904  0.000000
  28. 2000-01-08  0.801196  1.392071  0.000000  0.000000

By default, where returns a modified copy of the data. There is anoptional parameter inplace so that the original data can be modifiedwithout creating a copy:

  1. In [183]: df_orig = df.copy()
  2.  
  3. In [184]: df_orig.where(df > 0, -df, inplace=True)
  4.  
  5. In [185]: df_orig
  6. Out[185]: 
  7.                    A         B         C         D
  8. 2000-01-01  2.104139  1.309525  0.485855  0.245166
  9. 2000-01-02  0.352480  0.390389  1.192319  1.655824
  10. 2000-01-03  0.864883  0.299674  0.227870  0.281059
  11. 2000-01-04  0.846958  1.222082  0.600705  1.233203
  12. 2000-01-05  0.669692  0.605656  1.169184  0.342416
  13. 2000-01-06  0.868584  0.948458  2.297780  0.684718
  14. 2000-01-07  2.670153  0.114722  0.168904  0.048048
  15. 2000-01-08  0.801196  1.392071  0.048788  0.808838

Note

The signature for DataFrame.where() differs from numpy.where().Roughly df1.where(m, df2) is equivalent to np.where(m, df1, df2).

  1. In [186]: df.where(df < 0, -df) == np.where(df < 0, df, -df)
  2. Out[186]: 
  3.                A     B     C     D
  4. 2000-01-01  True  True  True  True
  5. 2000-01-02  True  True  True  True
  6. 2000-01-03  True  True  True  True
  7. 2000-01-04  True  True  True  True
  8. 2000-01-05  True  True  True  True
  9. 2000-01-06  True  True  True  True
  10. 2000-01-07  True  True  True  True
  11. 2000-01-08  True  True  True  True

Alignment

Furthermore, where aligns the input boolean condition (ndarray or DataFrame),such that partial selection with setting is possible. This is analogous topartial setting via .loc (but on the contents rather than the axis labels).

  1. In [187]: df2 = df.copy()
  2.  
  3. In [188]: df2[df2[1:4] > 0] = 3
  4.  
  5. In [189]: df2
  6. Out[189]: 
  7.                    A         B         C         D
  8. 2000-01-01 -2.104139 -1.309525  0.485855  0.245166
  9. 2000-01-02 -0.352480  3.000000 -1.192319  3.000000
  10. 2000-01-03 -0.864883  3.000000 -0.227870  3.000000
  11. 2000-01-04  3.000000 -1.222082  3.000000 -1.233203
  12. 2000-01-05  0.669692 -0.605656 -1.169184  0.342416
  13. 2000-01-06  0.868584 -0.948458  2.297780 -0.684718
  14. 2000-01-07 -2.670153 -0.114722  0.168904 -0.048048
  15. 2000-01-08  0.801196  1.392071 -0.048788 -0.808838

Where can also accept axis and level parameters to align the input whenperforming the where.

  1. In [190]: df2 = df.copy()
  2.  
  3. In [191]: df2.where(df2 > 0, df2['A'], axis='index')
  4. Out[191]: 
  5.                    A         B         C         D
  6. 2000-01-01 -2.104139 -2.104139  0.485855  0.245166
  7. 2000-01-02 -0.352480  0.390389 -0.352480  1.655824
  8. 2000-01-03 -0.864883  0.299674 -0.864883  0.281059
  9. 2000-01-04  0.846958  0.846958  0.600705  0.846958
  10. 2000-01-05  0.669692  0.669692  0.669692  0.342416
  11. 2000-01-06  0.868584  0.868584  2.297780  0.868584
  12. 2000-01-07 -2.670153 -2.670153  0.168904 -2.670153
  13. 2000-01-08  0.801196  1.392071  0.801196  0.801196

This is equivalent to (but faster than) the following.

  1. In [192]: df2 = df.copy()
  2.  
  3. In [193]: df.apply(lambda x, y: x.where(x > 0, y), y=df['A'])
  4. Out[193]: 
  5.                    A         B         C         D
  6. 2000-01-01 -2.104139 -2.104139  0.485855  0.245166
  7. 2000-01-02 -0.352480  0.390389 -0.352480  1.655824
  8. 2000-01-03 -0.864883  0.299674 -0.864883  0.281059
  9. 2000-01-04  0.846958  0.846958  0.600705  0.846958
  10. 2000-01-05  0.669692  0.669692  0.669692  0.342416
  11. 2000-01-06  0.868584  0.868584  2.297780  0.868584
  12. 2000-01-07 -2.670153 -2.670153  0.168904 -2.670153
  13. 2000-01-08  0.801196  1.392071  0.801196  0.801196

New in version 0.18.1.

Where can accept a callable as condition and other arguments. The function mustbe with one argument (the calling Series or DataFrame) and that returns valid outputas condition and other argument.

  1. In [194]: df3 = pd.DataFrame({'A': [1, 2, 3],
  2.    .....:                     'B': [4, 5, 6],
  3.    .....:                     'C': [7, 8, 9]})
  4.    .....: 
  5.  
  6. In [195]: df3.where(lambda x: x > 4, lambda x: x + 10)
  7. Out[195]: 
  8.     A   B  C
  9. 0  11  14  7
  10. 1  12   5  8
  11. 2  13   6  9

Mask

mask() is the inverse boolean operation of where.

  1. In [196]: s.mask(s >= 0)
  2. Out[196]: 
  3. 4   NaN
  4. 3   NaN
  5. 2   NaN
  6. 1   NaN
  7. 0   NaN
  8. dtype: float64
  9.  
  10. In [197]: df.mask(df >= 0)
  11. Out[197]: 
  12.                    A         B         C         D
  13. 2000-01-01 -2.104139 -1.309525       NaN       NaN
  14. 2000-01-02 -0.352480       NaN -1.192319       NaN
  15. 2000-01-03 -0.864883       NaN -0.227870       NaN
  16. 2000-01-04       NaN -1.222082       NaN -1.233203
  17. 2000-01-05       NaN -0.605656 -1.169184       NaN
  18. 2000-01-06       NaN -0.948458       NaN -0.684718
  19. 2000-01-07 -2.670153 -0.114722       NaN -0.048048
  20. 2000-01-08       NaN       NaN -0.048788 -0.808838

The query() Method

DataFrame objects have a query()method that allows selection using an expression.

You can get the value of the frame where column b has valuesbetween the values of columns a and c. For example:

  1. In [198]: n = 10
  2.  
  3. In [199]: df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))
  4.  
  5. In [200]: df
  6. Out[200]: 
  7.           a         b         c
  8. 0  0.438921  0.118680  0.863670
  9. 1  0.138138  0.577363  0.686602
  10. 2  0.595307  0.564592  0.520630
  11. 3  0.913052  0.926075  0.616184
  12. 4  0.078718  0.854477  0.898725
  13. 5  0.076404  0.523211  0.591538
  14. 6  0.792342  0.216974  0.564056
  15. 7  0.397890  0.454131  0.915716
  16. 8  0.074315  0.437913  0.019794
  17. 9  0.559209  0.502065  0.026437
  18.  
  19. # pure python
  20. In [201]: df[(df.a < df.b) & (df.b < df.c)]
  21. Out[201]: 
  22.           a         b         c
  23. 1  0.138138  0.577363  0.686602
  24. 4  0.078718  0.854477  0.898725
  25. 5  0.076404  0.523211  0.591538
  26. 7  0.397890  0.454131  0.915716
  27.  
  28. # query
  29. In [202]: df.query('(a < b) & (b < c)')
  30. Out[202]: 
  31.           a         b         c
  32. 1  0.138138  0.577363  0.686602
  33. 4  0.078718  0.854477  0.898725
  34. 5  0.076404  0.523211  0.591538
  35. 7  0.397890  0.454131  0.915716

Do the same thing but fall back on a named index if there is no columnwith the name a.

  1. In [203]: df = pd.DataFrame(np.random.randint(n / 2, size=(n, 2)), columns=list('bc'))
  2.  
  3. In [204]: df.index.name = 'a'
  4.  
  5. In [205]: df
  6. Out[205]: 
  7.    b  c
  8. a      
  9. 0  0  4
  10. 1  0  1
  11. 2  3  4
  12. 3  4  3
  13. 4  1  4
  14. 5  0  3
  15. 6  0  1
  16. 7  3  4
  17. 8  2  3
  18. 9  1  1
  19.  
  20. In [206]: df.query('a < b and b < c')
  21. Out[206]: 
  22.    b  c
  23. a      
  24. 2  3  4

If instead you don’t want to or cannot name your index, you can use the nameindex in your query expression:

  1. In [207]: df = pd.DataFrame(np.random.randint(n, size=(n, 2)), columns=list('bc'))
  2.  
  3. In [208]: df
  4. Out[208]: 
  5.    b  c
  6. 0  3  1
  7. 1  3  0
  8. 2  5  6
  9. 3  5  2
  10. 4  7  4
  11. 5  0  1
  12. 6  2  5
  13. 7  0  1
  14. 8  6  0
  15. 9  7  9
  16.  
  17. In [209]: df.query('index < b < c')
  18. Out[209]: 
  19.    b  c
  20. 2  5  6

Note

If the name of your index overlaps with a column name, the column name isgiven precedence. For example,

  1. In [210]: df = pd.DataFrame({'a': np.random.randint(5, size=5)})
  2.  
  3. In [211]: df.index.name = 'a'
  4.  
  5. In [212]: df.query('a > 2')  # uses the column 'a', not the index
  6. Out[212]: 
  7.    a
  8. a   
  9. 1  3
  10. 3  3

You can still use the index in a query expression by using the specialidentifier ‘index’:

  1. In [213]: df.query('index > 2')
  2. Out[213]: 
  3.    a
  4. a   
  5. 3  3
  6. 4  2

If for some reason you have a column named index, then you can refer tothe index as ilevel_0 as well, but at this point you should considerrenaming your columns to something less ambiguous.

MultiIndex query() Syntax

You can also use the levels of a DataFrame with aMultiIndex as if they were columns in the frame:

  1. In [214]: n = 10
  2.  
  3. In [215]: colors = np.random.choice(['red', 'green'], size=n)
  4.  
  5. In [216]: foods = np.random.choice(['eggs', 'ham'], size=n)
  6.  
  7. In [217]: colors
  8. Out[217]: 
  9. array(['red', 'red', 'red', 'green', 'green', 'green', 'green', 'green',
  10.        'green', 'green'], dtype='<U5')
  11.  
  12. In [218]: foods
  13. Out[218]: 
  14. array(['ham', 'ham', 'eggs', 'eggs', 'eggs', 'ham', 'ham', 'eggs', 'eggs',
  15.        'eggs'], dtype='<U4')
  16.  
  17. In [219]: index = pd.MultiIndex.from_arrays([colors, foods], names=['color', 'food'])
  18.  
  19. In [220]: df = pd.DataFrame(np.random.randn(n, 2), index=index)
  20.  
  21. In [221]: df
  22. Out[221]: 
  23.                    0         1
  24. color food                    
  25. red   ham   0.194889 -0.381994
  26.       ham   0.318587  2.089075
  27.       eggs -0.728293 -0.090255
  28. green eggs -0.748199  1.318931
  29.       eggs -2.029766  0.792652
  30.       ham   0.461007 -0.542749
  31.       ham  -0.305384 -0.479195
  32.       eggs  0.095031 -0.270099
  33.       eggs -0.707140 -0.773882
  34.       eggs  0.229453  0.304418
  35.  
  36. In [222]: df.query('color == "red"')
  37. Out[222]: 
  38.                    0         1
  39. color food                    
  40. red   ham   0.194889 -0.381994
  41.       ham   0.318587  2.089075
  42.       eggs -0.728293 -0.090255

If the levels of the MultiIndex are unnamed, you can refer to them usingspecial names:

  1. In [223]: df.index.names = [None, None]
  2.  
  3. In [224]: df
  4. Out[224]: 
  5.                    0         1
  6. red   ham   0.194889 -0.381994
  7.       ham   0.318587  2.089075
  8.       eggs -0.728293 -0.090255
  9. green eggs -0.748199  1.318931
  10.       eggs -2.029766  0.792652
  11.       ham   0.461007 -0.542749
  12.       ham  -0.305384 -0.479195
  13.       eggs  0.095031 -0.270099
  14.       eggs -0.707140 -0.773882
  15.       eggs  0.229453  0.304418
  16.  
  17. In [225]: df.query('ilevel_0 == "red"')
  18. Out[225]: 
  19.                  0         1
  20. red ham   0.194889 -0.381994
  21.     ham   0.318587  2.089075
  22.     eggs -0.728293 -0.090255

The convention is ilevel_0, which means “index level 0” for the 0th levelof the index.

query() Use Cases

A use case for query() is when you have a collection ofDataFrame objects that have a subset of column names (or indexlevels/names) in common. You can pass the same query to both frames _without_having to specify which frame you’re interested in querying

  1. In [226]: df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))
  2.  
  3. In [227]: df
  4. Out[227]: 
  5.           a         b         c
  6. 0  0.224283  0.736107  0.139168
  7. 1  0.302827  0.657803  0.713897
  8. 2  0.611185  0.136624  0.984960
  9. 3  0.195246  0.123436  0.627712
  10. 4  0.618673  0.371660  0.047902
  11. 5  0.480088  0.062993  0.185760
  12. 6  0.568018  0.483467  0.445289
  13. 7  0.309040  0.274580  0.587101
  14. 8  0.258993  0.477769  0.370255
  15. 9  0.550459  0.840870  0.304611
  16.  
  17. In [228]: df2 = pd.DataFrame(np.random.rand(n + 2, 3), columns=df.columns)
  18.  
  19. In [229]: df2
  20. Out[229]: 
  21.            a         b         c
  22. 0   0.357579  0.229800  0.596001
  23. 1   0.309059  0.957923  0.965663
  24. 2   0.123102  0.336914  0.318616
  25. 3   0.526506  0.323321  0.860813
  26. 4   0.518736  0.486514  0.384724
  27. 5   0.190804  0.505723  0.614533
  28. 6   0.891939  0.623977  0.676639
  29. 7   0.480559  0.378528  0.460858
  30. 8   0.420223  0.136404  0.141295
  31. 9   0.732206  0.419540  0.604675
  32. 10  0.604466  0.848974  0.896165
  33. 11  0.589168  0.920046  0.732716
  34.  
  35. In [230]: expr = '0.0 <= a <= c <= 0.5'
  36.  
  37. In [231]: map(lambda frame: frame.query(expr), [df, df2])
  38. Out[231]: <map at 0x7f4527f58c90>

query() Python versus pandas Syntax Comparison

Full numpy-like syntax:

  1. In [232]: df = pd.DataFrame(np.random.randint(n, size=(n, 3)), columns=list('abc'))
  2.  
  3. In [233]: df
  4. Out[233]: 
  5.    a  b  c
  6. 0  7  8  9
  7. 1  1  0  7
  8. 2  2  7  2
  9. 3  6  2  2
  10. 4  2  6  3
  11. 5  3  8  2
  12. 6  1  7  2
  13. 7  5  1  5
  14. 8  9  8  0
  15. 9  1  5  0
  16.  
  17. In [234]: df.query('(a < b) & (b < c)')
  18. Out[234]: 
  19.    a  b  c
  20. 0  7  8  9
  21.  
  22. In [235]: df[(df.a < df.b) & (df.b < df.c)]
  23. Out[235]: 
  24.    a  b  c
  25. 0  7  8  9

Slightly nicer by removing the parentheses (by binding making comparisonoperators bind tighter than & and |).

  1. In [236]: df.query('a < b & b < c')
  2. Out[236]: 
  3.    a  b  c
  4. 0  7  8  9

Use English instead of symbols:

  1. In [237]: df.query('a < b and b < c')
  2. Out[237]: 
  3.    a  b  c
  4. 0  7  8  9

Pretty close to how you might write it on paper:

  1. In [238]: df.query('a < b < c')
  2. Out[238]: 
  3.    a  b  c
  4. 0  7  8  9

The in and not in operators

query() also supports special use of Python’s in andnot in comparison operators, providing a succinct syntax for calling theisin method of a Series or DataFrame.

  1. # get all rows where columns "a" and "b" have overlapping values
  2. In [239]: df = pd.DataFrame({'a': list('aabbccddeeff'), 'b': list('aaaabbbbcccc'),
  3.    .....:                    'c': np.random.randint(5, size=12),
  4.    .....:                    'd': np.random.randint(9, size=12)})
  5.    .....: 
  6.  
  7. In [240]: df
  8. Out[240]: 
  9.     a  b  c  d
  10. 0   a  a  2  6
  11. 1   a  a  4  7
  12. 2   b  a  1  6
  13. 3   b  a  2  1
  14. 4   c  b  3  6
  15. 5   c  b  0  2
  16. 6   d  b  3  3
  17. 7   d  b  2  1
  18. 8   e  c  4  3
  19. 9   e  c  2  0
  20. 10  f  c  0  6
  21. 11  f  c  1  2
  22.  
  23. In [241]: df.query('a in b')
  24. Out[241]: 
  25.    a  b  c  d
  26. 0  a  a  2  6
  27. 1  a  a  4  7
  28. 2  b  a  1  6
  29. 3  b  a  2  1
  30. 4  c  b  3  6
  31. 5  c  b  0  2
  32.  
  33. # How you'd do it in pure Python
  34. In [242]: df[df.a.isin(df.b)]
  35. Out[242]: 
  36.    a  b  c  d
  37. 0  a  a  2  6
  38. 1  a  a  4  7
  39. 2  b  a  1  6
  40. 3  b  a  2  1
  41. 4  c  b  3  6
  42. 5  c  b  0  2
  43.  
  44. In [243]: df.query('a not in b')
  45. Out[243]: 
  46.     a  b  c  d
  47. 6   d  b  3  3
  48. 7   d  b  2  1
  49. 8   e  c  4  3
  50. 9   e  c  2  0
  51. 10  f  c  0  6
  52. 11  f  c  1  2
  53.  
  54. # pure Python
  55. In [244]: df[~df.a.isin(df.b)]
  56. Out[244]: 
  57.     a  b  c  d
  58. 6   d  b  3  3
  59. 7   d  b  2  1
  60. 8   e  c  4  3
  61. 9   e  c  2  0
  62. 10  f  c  0  6
  63. 11  f  c  1  2

You can combine this with other expressions for very succinct queries:

  1. # rows where cols a and b have overlapping values
  2. # and col c's values are less than col d's
  3. In [245]: df.query('a in b and c < d')
  4. Out[245]: 
  5.    a  b  c  d
  6. 0  a  a  2  6
  7. 1  a  a  4  7
  8. 2  b  a  1  6
  9. 4  c  b  3  6
  10. 5  c  b  0  2
  11.  
  12. # pure Python
  13. In [246]: df[df.b.isin(df.a) & (df.c < df.d)]
  14. Out[246]: 
  15.     a  b  c  d
  16. 0   a  a  2  6
  17. 1   a  a  4  7
  18. 2   b  a  1  6
  19. 4   c  b  3  6
  20. 5   c  b  0  2
  21. 10  f  c  0  6
  22. 11  f  c  1  2

Note

Note that in and not in are evaluated in Python, since numexprhas no equivalent of this operation. However, only the in/not inexpression itself is evaluated in vanilla Python. For example, in theexpression

  1. df.query('a in b + c + d')

(b + c + d) is evaluated by numexpr and then the inoperation is evaluated in plain Python. In general, any operations that canbe evaluated using numexpr will be.

Special use of the == operator with list objects

Comparing a list of values to a column using ==/!= works similarlyto in/not in.

  1. In [247]: df.query('b == ["a", "b", "c"]')
  2. Out[247]: 
  3.     a  b  c  d
  4. 0   a  a  2  6
  5. 1   a  a  4  7
  6. 2   b  a  1  6
  7. 3   b  a  2  1
  8. 4   c  b  3  6
  9. 5   c  b  0  2
  10. 6   d  b  3  3
  11. 7   d  b  2  1
  12. 8   e  c  4  3
  13. 9   e  c  2  0
  14. 10  f  c  0  6
  15. 11  f  c  1  2
  16.  
  17. # pure Python
  18. In [248]: df[df.b.isin(["a", "b", "c"])]
  19. Out[248]: 
  20.     a  b  c  d
  21. 0   a  a  2  6
  22. 1   a  a  4  7
  23. 2   b  a  1  6
  24. 3   b  a  2  1
  25. 4   c  b  3  6
  26. 5   c  b  0  2
  27. 6   d  b  3  3
  28. 7   d  b  2  1
  29. 8   e  c  4  3
  30. 9   e  c  2  0
  31. 10  f  c  0  6
  32. 11  f  c  1  2
  33.  
  34. In [249]: df.query('c == [1, 2]')
  35. Out[249]: 
  36.     a  b  c  d
  37. 0   a  a  2  6
  38. 2   b  a  1  6
  39. 3   b  a  2  1
  40. 7   d  b  2  1
  41. 9   e  c  2  0
  42. 11  f  c  1  2
  43.  
  44. In [250]: df.query('c != [1, 2]')
  45. Out[250]: 
  46.     a  b  c  d
  47. 1   a  a  4  7
  48. 4   c  b  3  6
  49. 5   c  b  0  2
  50. 6   d  b  3  3
  51. 8   e  c  4  3
  52. 10  f  c  0  6
  53.  
  54. # using in/not in
  55. In [251]: df.query('[1, 2] in c')
  56. Out[251]: 
  57.     a  b  c  d
  58. 0   a  a  2  6
  59. 2   b  a  1  6
  60. 3   b  a  2  1
  61. 7   d  b  2  1
  62. 9   e  c  2  0
  63. 11  f  c  1  2
  64.  
  65. In [252]: df.query('[1, 2] not in c')
  66. Out[252]: 
  67.     a  b  c  d
  68. 1   a  a  4  7
  69. 4   c  b  3  6
  70. 5   c  b  0  2
  71. 6   d  b  3  3
  72. 8   e  c  4  3
  73. 10  f  c  0  6
  74.  
  75. # pure Python
  76. In [253]: df[df.c.isin([1, 2])]
  77. Out[253]: 
  78.     a  b  c  d
  79. 0   a  a  2  6
  80. 2   b  a  1  6
  81. 3   b  a  2  1
  82. 7   d  b  2  1
  83. 9   e  c  2  0
  84. 11  f  c  1  2

Boolean operators

You can negate boolean expressions with the word not or the ~ operator.

  1. In [254]: df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))
  2.  
  3. In [255]: df['bools'] = np.random.rand(len(df)) > 0.5
  4.  
  5. In [256]: df.query('~bools')
  6. Out[256]: 
  7.           a         b         c  bools
  8. 2  0.697753  0.212799  0.329209  False
  9. 7  0.275396  0.691034  0.826619  False
  10. 8  0.190649  0.558748  0.262467  False
  11.  
  12. In [257]: df.query('not bools')
  13. Out[257]: 
  14.           a         b         c  bools
  15. 2  0.697753  0.212799  0.329209  False
  16. 7  0.275396  0.691034  0.826619  False
  17. 8  0.190649  0.558748  0.262467  False
  18.  
  19. In [258]: df.query('not bools') == df[~df.bools]
  20. Out[258]: 
  21.       a     b     c  bools
  22. 2  True  True  True   True
  23. 7  True  True  True   True
  24. 8  True  True  True   True

Of course, expressions can be arbitrarily complex too:

  1. # short query syntax
  2. In [259]: shorter = df.query('a < b < c and (not bools) or bools > 2')
  3.  
  4. # equivalent in pure Python
  5. In [260]: longer = df[(df.a < df.b) & (df.b < df.c) & (~df.bools) | (df.bools > 2)]
  6.  
  7. In [261]: shorter
  8. Out[261]: 
  9.           a         b         c  bools
  10. 7  0.275396  0.691034  0.826619  False
  11.  
  12. In [262]: longer
  13. Out[262]: 
  14.           a         b         c  bools
  15. 7  0.275396  0.691034  0.826619  False
  16.  
  17. In [263]: shorter == longer
  18. Out[263]: 
  19.       a     b     c  bools
  20. 7  True  True  True   True

Performance of query()

DataFrame.query() using numexpr is slightly faster than Python forlarge frames.../_images/query-perf.png

Note

You will only see the performance benefits of using the numexpr enginewith DataFrame.query() if your frame has more than approximately 200,000rows.

../_images/query-perf-small.png

This plot was created using a DataFrame with 3 columns each containingfloating point values generated using numpy.random.randn().

Duplicate data

If you want to identify and remove duplicate rows in a DataFrame, there aretwo methods that will help: duplicated and drop_duplicates. Eachtakes as an argument the columns to use to identify duplicated rows.

  • duplicated returns a boolean vector whose length is the number of rows, and which indicates whether a row is duplicated.
  • drop_duplicates removes duplicate rows.

By default, the first observed row of a duplicate set is considered unique, buteach method has a keep parameter to specify targets to be kept.

  • keep='first' (default): mark / drop duplicates except for the first occurrence.
  • keep='last': mark / drop duplicates except for the last occurrence.
  • keep=False: mark / drop all duplicates.
  1. In [264]: df2 = pd.DataFrame({'a': ['one', 'one', 'two', 'two', 'two', 'three', 'four'],
  2.    .....:                     'b': ['x', 'y', 'x', 'y', 'x', 'x', 'x'],
  3.    .....:                     'c': np.random.randn(7)})
  4.    .....: 
  5.  
  6. In [265]: df2
  7. Out[265]: 
  8.        a  b         c
  9. 0    one  x -1.067137
  10. 1    one  y  0.309500
  11. 2    two  x -0.211056
  12. 3    two  y -1.842023
  13. 4    two  x -0.390820
  14. 5  three  x -1.964475
  15. 6   four  x  1.298329
  16.  
  17. In [266]: df2.duplicated('a')
  18. Out[266]: 
  19. 0    False
  20. 1     True
  21. 2    False
  22. 3     True
  23. 4     True
  24. 5    False
  25. 6    False
  26. dtype: bool
  27.  
  28. In [267]: df2.duplicated('a', keep='last')
  29. Out[267]: 
  30. 0     True
  31. 1    False
  32. 2     True
  33. 3     True
  34. 4    False
  35. 5    False
  36. 6    False
  37. dtype: bool
  38.  
  39. In [268]: df2.duplicated('a', keep=False)
  40. Out[268]: 
  41. 0     True
  42. 1     True
  43. 2     True
  44. 3     True
  45. 4     True
  46. 5    False
  47. 6    False
  48. dtype: bool
  49.  
  50. In [269]: df2.drop_duplicates('a')
  51. Out[269]: 
  52.        a  b         c
  53. 0    one  x -1.067137
  54. 2    two  x -0.211056
  55. 5  three  x -1.964475
  56. 6   four  x  1.298329
  57.  
  58. In [270]: df2.drop_duplicates('a', keep='last')
  59. Out[270]: 
  60.        a  b         c
  61. 1    one  y  0.309500
  62. 4    two  x -0.390820
  63. 5  three  x -1.964475
  64. 6   four  x  1.298329
  65.  
  66. In [271]: df2.drop_duplicates('a', keep=False)
  67. Out[271]: 
  68.        a  b         c
  69. 5  three  x -1.964475
  70. 6   four  x  1.298329

Also, you can pass a list of columns to identify duplications.

  1. In [272]: df2.duplicated(['a', 'b'])
  2. Out[272]: 
  3. 0    False
  4. 1    False
  5. 2    False
  6. 3    False
  7. 4     True
  8. 5    False
  9. 6    False
  10. dtype: bool
  11.  
  12. In [273]: df2.drop_duplicates(['a', 'b'])
  13. Out[273]: 
  14.        a  b         c
  15. 0    one  x -1.067137
  16. 1    one  y  0.309500
  17. 2    two  x -0.211056
  18. 3    two  y -1.842023
  19. 5  three  x -1.964475
  20. 6   four  x  1.298329

To drop duplicates by index value, use Index.duplicated then perform slicing.The same set of options are available for the keep parameter.

  1. In [274]: df3 = pd.DataFrame({'a': np.arange(6),
  2.    .....:                     'b': np.random.randn(6)},
  3.    .....:                    index=['a', 'a', 'b', 'c', 'b', 'a'])
  4.    .....: 
  5.  
  6. In [275]: df3
  7. Out[275]: 
  8.    a         b
  9. a  0  1.440455
  10. a  1  2.456086
  11. b  2  1.038402
  12. c  3 -0.894409
  13. b  4  0.683536
  14. a  5  3.082764
  15.  
  16. In [276]: df3.index.duplicated()
  17. Out[276]: array([False,  True, False, False,  True,  True])
  18.  
  19. In [277]: df3[~df3.index.duplicated()]
  20. Out[277]: 
  21.    a         b
  22. a  0  1.440455
  23. b  2  1.038402
  24. c  3 -0.894409
  25.  
  26. In [278]: df3[~df3.index.duplicated(keep='last')]
  27. Out[278]: 
  28.    a         b
  29. c  3 -0.894409
  30. b  4  0.683536
  31. a  5  3.082764
  32.  
  33. In [279]: df3[~df3.index.duplicated(keep=False)]
  34. Out[279]: 
  35.    a         b
  36. c  3 -0.894409

Dictionary-like get() method

Each of Series or DataFrame have a get method which can return adefault value.

  1. In [280]: s = pd.Series([1, 2, 3], index=['a', 'b', 'c'])
  2.  
  3. In [281]: s.get('a')  # equivalent to s['a']
  4. Out[281]: 1
  5.  
  6. In [282]: s.get('x', default=-1)
  7. Out[282]: -1

The lookup() method

Sometimes you want to extract a set of values given a sequence of row labelsand column labels, and the lookup method allows for this and returns aNumPy array. For instance:

  1. In [283]: dflookup = pd.DataFrame(np.random.rand(20, 4), columns = ['A', 'B', 'C', 'D'])
  2.  
  3. In [284]: dflookup.lookup(list(range(0, 10, 2)), ['B', 'C', 'A', 'B', 'D'])
  4. Out[284]: array([0.3506, 0.4779, 0.4825, 0.9197, 0.5019])

Index objects

The pandas Index class and its subclasses can be viewed asimplementing an ordered multiset. Duplicates are allowed. However, if you tryto convert an Index object with duplicate entries into aset, an exception will be raised.

Index also provides the infrastructure necessary forlookups, data alignment, and reindexing. The easiest way to create anIndex directly is to pass a list or other sequence toIndex:

  1. In [285]: index = pd.Index(['e', 'd', 'a', 'b'])
  2.  
  3. In [286]: index
  4. Out[286]: Index(['e', 'd', 'a', 'b'], dtype='object')
  5.  
  6. In [287]: 'd' in index
  7. Out[287]: True

You can also pass a name to be stored in the index:

  1. In [288]: index = pd.Index(['e', 'd', 'a', 'b'], name='something')
  2.  
  3. In [289]: index.name
  4. Out[289]: 'something'

The name, if set, will be shown in the console display:

  1. In [290]: index = pd.Index(list(range(5)), name='rows')
  2.  
  3. In [291]: columns = pd.Index(['A', 'B', 'C'], name='cols')
  4.  
  5. In [292]: df = pd.DataFrame(np.random.randn(5, 3), index=index, columns=columns)
  6.  
  7. In [293]: df
  8. Out[293]: 
  9. cols         A         B         C
  10. rows                              
  11. 0     1.295989  0.185778  0.436259
  12. 1     0.678101  0.311369 -0.528378
  13. 2    -0.674808 -1.103529 -0.656157
  14. 3     1.889957  2.076651 -1.102192
  15. 4    -1.211795 -0.791746  0.634724
  16.  
  17. In [294]: df['A']
  18. Out[294]: 
  19. rows
  20. 0    1.295989
  21. 1    0.678101
  22. 2   -0.674808
  23. 3    1.889957
  24. 4   -1.211795
  25. Name: A, dtype: float64

Setting metadata

Indexes are “mostly immutable”, but it is possible to set and change theirmetadata, like the index name (or, for MultiIndex, levels andcodes).

You can use the rename, set_names, set_levels, and set_codesto set these attributes directly. They default to returning a copy; however,you can specify inplace=True to have the data change in place.

See Advanced Indexing for usage of MultiIndexes.

  1. In [295]: ind = pd.Index([1, 2, 3])
  2.  
  3. In [296]: ind.rename("apple")
  4. Out[296]: Int64Index([1, 2, 3], dtype='int64', name='apple')
  5.  
  6. In [297]: ind
  7. Out[297]: Int64Index([1, 2, 3], dtype='int64')
  8.  
  9. In [298]: ind.set_names(["apple"], inplace=True)
  10.  
  11. In [299]: ind.name = "bob"
  12.  
  13. In [300]: ind
  14. Out[300]: Int64Index([1, 2, 3], dtype='int64', name='bob')

set_names, set_levels, and set_codes also take an optionallevel argument

  1. In [301]: index = pd.MultiIndex.from_product([range(3), ['one', 'two']], names=['first', 'second'])
  2.  
  3. In [302]: index
  4. Out[302]: 
  5. MultiIndex([(0, 'one'),
  6.             (0, 'two'),
  7.             (1, 'one'),
  8.             (1, 'two'),
  9.             (2, 'one'),
  10.             (2, 'two')],
  11.            names=['first', 'second'])
  12.  
  13. In [303]: index.levels[1]
  14. Out[303]: Index(['one', 'two'], dtype='object', name='second')
  15.  
  16. In [304]: index.set_levels(["a", "b"], level=1)
  17. Out[304]: 
  18. MultiIndex([(0, 'a'),
  19.             (0, 'b'),
  20.             (1, 'a'),
  21.             (1, 'b'),
  22.             (2, 'a'),
  23.             (2, 'b')],
  24.            names=['first', 'second'])

Set operations on Index objects

The two main operations are union (|) and intersection (&).These can be directly called as instance methods or used via overloadedoperators. Difference is provided via the .difference() method.

  1. In [305]: a = pd.Index(['c', 'b', 'a'])
  2.  
  3. In [306]: b = pd.Index(['c', 'e', 'd'])
  4.  
  5. In [307]: a | b
  6. Out[307]: Index(['a', 'b', 'c', 'd', 'e'], dtype='object')
  7.  
  8. In [308]: a & b
  9. Out[308]: Index(['c'], dtype='object')
  10.  
  11. In [309]: a.difference(b)
  12. Out[309]: Index(['a', 'b'], dtype='object')

Also available is the symmetric_difference (^) operation, which returns elementsthat appear in either idx1 or idx2, but not in both. This isequivalent to the Index created by idx1.difference(idx2).union(idx2.difference(idx1)),with duplicates dropped.

  1. In [310]: idx1 = pd.Index([1, 2, 3, 4])
  2.  
  3. In [311]: idx2 = pd.Index([2, 3, 4, 5])
  4.  
  5. In [312]: idx1.symmetric_difference(idx2)
  6. Out[312]: Int64Index([1, 5], dtype='int64')
  7.  
  8. In [313]: idx1 ^ idx2
  9. Out[313]: Int64Index([1, 5], dtype='int64')

Note

The resulting index from a set operation will be sorted in ascending order.

When performing Index.union() between indexes with different dtypes, the indexesmust be cast to a common dtype. Typically, though not always, this is object dtype. Theexception is when performing a union between integer and float data. In this case, theinteger values are converted to float

  1. In [314]: idx1 = pd.Index([0, 1, 2])
  2.  
  3. In [315]: idx2 = pd.Index([0.5, 1.5])
  4.  
  5. In [316]: idx1 | idx2
  6. Out[316]: Float64Index([0.0, 0.5, 1.0, 1.5, 2.0], dtype='float64')

Missing values

Important

Even though Index can hold missing values (NaN), it should be avoidedif you do not want any unexpected results. For example, some operationsexclude missing values implicitly.

Index.fillna fills missing values with specified scalar value.

  1. In [317]: idx1 = pd.Index([1, np.nan, 3, 4])
  2.  
  3. In [318]: idx1
  4. Out[318]: Float64Index([1.0, nan, 3.0, 4.0], dtype='float64')
  5.  
  6. In [319]: idx1.fillna(2)
  7. Out[319]: Float64Index([1.0, 2.0, 3.0, 4.0], dtype='float64')
  8.  
  9. In [320]: idx2 = pd.DatetimeIndex([pd.Timestamp('2011-01-01'),
  10.    .....:                          pd.NaT,
  11.    .....:                          pd.Timestamp('2011-01-03')])
  12.    .....: 
  13.  
  14. In [321]: idx2
  15. Out[321]: DatetimeIndex(['2011-01-01', 'NaT', '2011-01-03'], dtype='datetime64[ns]', freq=None)
  16.  
  17. In [322]: idx2.fillna(pd.Timestamp('2011-01-02'))
  18. Out[322]: DatetimeIndex(['2011-01-01', '2011-01-02', '2011-01-03'], dtype='datetime64[ns]', freq=None)

Set / reset index

Occasionally you will load or create a data set into a DataFrame and want toadd an index after you’ve already done so. There are a couple of differentways.

Set an index

DataFrame has a set_index() method which takes a column name(for a regular Index) or a list of column names (for a MultiIndex).To create a new, re-indexed DataFrame:

  1. In [323]: data
  2. Out[323]: 
  3.      a    b  c    d
  4. 0  bar  one  z  1.0
  5. 1  bar  two  y  2.0
  6. 2  foo  one  x  3.0
  7. 3  foo  two  w  4.0
  8.  
  9. In [324]: indexed1 = data.set_index('c')
  10.  
  11. In [325]: indexed1
  12. Out[325]: 
  13.      a    b    d
  14. c               
  15. z  bar  one  1.0
  16. y  bar  two  2.0
  17. x  foo  one  3.0
  18. w  foo  two  4.0
  19.  
  20. In [326]: indexed2 = data.set_index(['a', 'b'])
  21.  
  22. In [327]: indexed2
  23. Out[327]: 
  24.          c    d
  25. a   b          
  26. bar one  z  1.0
  27.     two  y  2.0
  28. foo one  x  3.0
  29.     two  w  4.0

The append keyword option allow you to keep the existing index and appendthe given columns to a MultiIndex:

  1. In [328]: frame = data.set_index('c', drop=False)
  2.  
  3. In [329]: frame = frame.set_index(['a', 'b'], append=True)
  4.  
  5. In [330]: frame
  6. Out[330]: 
  7.            c    d
  8. c a   b          
  9. z bar one  z  1.0
  10. y bar two  y  2.0
  11. x foo one  x  3.0
  12. w foo two  w  4.0

Other options in set_index allow you not drop the index columns or to addthe index in-place (without creating a new object):

  1. In [331]: data.set_index('c', drop=False)
  2. Out[331]: 
  3.      a    b  c    d
  4. c                  
  5. z  bar  one  z  1.0
  6. y  bar  two  y  2.0
  7. x  foo  one  x  3.0
  8. w  foo  two  w  4.0
  9.  
  10. In [332]: data.set_index(['a', 'b'], inplace=True)
  11.  
  12. In [333]: data
  13. Out[333]: 
  14.          c    d
  15. a   b          
  16. bar one  z  1.0
  17.     two  y  2.0
  18. foo one  x  3.0
  19.     two  w  4.0

Reset the index

As a convenience, there is a new function on DataFrame calledreset_index() which transfers the index values into theDataFrame’s columns and sets a simple integer index.This is the inverse operation of set_index().

  1. In [334]: data
  2. Out[334]: 
  3.          c    d
  4. a   b          
  5. bar one  z  1.0
  6.     two  y  2.0
  7. foo one  x  3.0
  8.     two  w  4.0
  9.  
  10. In [335]: data.reset_index()
  11. Out[335]: 
  12.      a    b  c    d
  13. 0  bar  one  z  1.0
  14. 1  bar  two  y  2.0
  15. 2  foo  one  x  3.0
  16. 3  foo  two  w  4.0

The output is more similar to a SQL table or a record array. The names for thecolumns derived from the index are the ones stored in the names attribute.

You can use the level keyword to remove only a portion of the index:

  1. In [336]: frame
  2. Out[336]: 
  3.            c    d
  4. c a   b          
  5. z bar one  z  1.0
  6. y bar two  y  2.0
  7. x foo one  x  3.0
  8. w foo two  w  4.0
  9.  
  10. In [337]: frame.reset_index(level=1)
  11. Out[337]: 
  12.          a  c    d
  13. c b               
  14. z one  bar  z  1.0
  15. y two  bar  y  2.0
  16. x one  foo  x  3.0
  17. w two  foo  w  4.0

reset_index takes an optional parameter drop which if true simplydiscards the index, instead of putting index values in the DataFrame’s columns.

Adding an ad hoc index

If you create an index yourself, you can just assign it to the index field:

  1. data.index = index

Returning a view versus a copy

When setting values in a pandas object, care must be taken to avoid what is calledchained indexing. Here is an example.

  1. In [338]: dfmi = pd.DataFrame([list('abcd'),
  2.    .....:                      list('efgh'),
  3.    .....:                      list('ijkl'),
  4.    .....:                      list('mnop')],
  5.    .....:                     columns=pd.MultiIndex.from_product([['one', 'two'],
  6.    .....:                                                         ['first', 'second']]))
  7.    .....: 
  8.  
  9. In [339]: dfmi
  10. Out[339]: 
  11.     one          two       
  12.   first second first second
  13. 0     a      b     c      d
  14. 1     e      f     g      h
  15. 2     i      j     k      l
  16. 3     m      n     o      p

Compare these two access methods:

  1. In [340]: dfmi['one']['second']
  2. Out[340]: 
  3. 0    b
  4. 1    f
  5. 2    j
  6. 3    n
  7. Name: second, dtype: object
  1. In [341]: dfmi.loc[:, ('one', 'second')]
  2. Out[341]: 
  3. 0    b
  4. 1    f
  5. 2    j
  6. 3    n
  7. Name: (one, second), dtype: object

These both yield the same results, so which should you use? It is instructive to understand the orderof operations on these and why method 2 (.loc) is much preferred over method 1 (chained []).

dfmi['one'] selects the first level of the columns and returns a DataFrame that is singly-indexed.Then another Python operation dfmiwithone['second'] selects the series indexed by 'second'.This is indicated by the variable dfmi_with_one because pandas sees these operations as separate events.e.g. separate calls to __getitem, so it has to treat them as linear operations, they happen one after another.

Contrast this to df.loc[:,('one','second')] which passes a nested tuple of (slice(None),('one','second')) to a single call togetitem. This allows pandas to deal with this as a single entity. Furthermore this order of operations can be significantlyfaster, and allows one to index both axes if so desired.

Why does assignment fail when using chained indexing?

The problem in the previous section is just a performance issue. What’s up withthe SettingWithCopy warning? We don’t usually throw warnings around whenyou do something that might cost a few extra milliseconds!

But it turns out that assigning to the product of chained indexing hasinherently unpredictable results. To see this, think about how the Pythoninterpreter executes this code:

  1. dfmi.loc[:, ('one', 'second')] = value
  2. # becomes
  3. dfmi.loc.__setitem__((slice(None), ('one', 'second')), value)

But this code is handled differently:

  1. dfmi['one']['second'] = value
  2. # becomes
  3. dfmi.__getitem__('one').__setitem__('second', value)

See that getitem in there? Outside of simple cases, it’s very hard topredict whether it will return a view or a copy (it depends on the memory layoutof the array, about which pandas makes no guarantees), and therefore whetherthe setitem will modify dfmi or a temporary object that gets thrownout immediately afterward. That’s what SettingWithCopy is warning youabout!

Note

You may be wondering whether we should be concerned about the locproperty in the first example. But dfmi.loc is guaranteed to be dfmiitself with modified indexing behavior, so dfmi.loc.getitem /dfmi.loc.setitem operate on dfmi directly. Of course,dfmi.loc.getitem(idx) may be a view or a copy of dfmi.

Sometimes a SettingWithCopy warning will arise at times when there’s noobvious chained indexing going on. These are the bugs thatSettingWithCopy is designed to catch! Pandas is probably trying to warn youthat you’ve done this:

  1. def do_something(df):
  2.     foo = df[['bar', 'baz']]  # Is foo a view? A copy? Nobody knows!
  3.     # ... many lines here ...
  4.     # We don't know whether this will modify df or not!
  5.     foo['quux'] = value
  6.     return foo

Yikes!

Evaluation order matters

When you use chained indexing, the order and type of the indexing operationpartially determine whether the result is a slice into the original object, ora copy of the slice.

Pandas has the SettingWithCopyWarning because assigning to a copy of aslice is frequently not intentional, but a mistake caused by chained indexingreturning a copy where a slice was expected.

If you would like pandas to be more or less trusting about assignment to achained indexing expression, you can set the optionmode.chained_assignment to one of these values:

  • 'warn', the default, means a SettingWithCopyWarning is printed.
  • 'raise' means pandas will raise a SettingWithCopyExceptionyou have to deal with.
  • None will suppress the warnings entirely.
  1. In [342]: dfb = pd.DataFrame({'a': ['one', 'one', 'two',
  2.    .....:                           'three', 'two', 'one', 'six'],
  3.    .....:                     'c': np.arange(7)})
  4.    .....: 
  5.  
  6. # This will show the SettingWithCopyWarning
  7. # but the frame values will be set
  8. In [343]: dfb['c'][dfb.a.str.startswith('o')] = 42

This however is operating on a copy and will not work.

  1. >>> pd.set_option('mode.chained_assignment','warn')
  2. >>> dfb[dfb.a.str.startswith('o')]['c'] = 42
  3. Traceback (most recent call last)
  4.      ...
  5. SettingWithCopyWarning:
  6.      A value is trying to be set on a copy of a slice from a DataFrame.
  7.      Try using .loc[row_index,col_indexer] = value instead

A chained assignment can also crop up in setting in a mixed dtype frame.

Note

These setting rules apply to all of .loc/.iloc.

This is the correct access method:

  1. In [344]: dfc = pd.DataFrame({'A': ['aaa', 'bbb', 'ccc'], 'B': [1, 2, 3]})
  2.  
  3. In [345]: dfc.loc[0, 'A'] = 11
  4.  
  5. In [346]: dfc
  6. Out[346]: 
  7.      A  B
  8. 0   11  1
  9. 1  bbb  2
  10. 2  ccc  3

This can work at times, but it is not guaranteed to, and therefore should be avoided:

  1. In [347]: dfc = dfc.copy()
  2.  
  3. In [348]: dfc['A'][0] = 111
  4.  
  5. In [349]: dfc
  6. Out[349]: 
  7.      A  B
  8. 0  111  1
  9. 1  bbb  2
  10. 2  ccc  3

This will not work at all, and so should be avoided:

  1. >>> pd.set_option('mode.chained_assignment','raise')
  2. >>> dfc.loc[0]['A'] = 1111
  3. Traceback (most recent call last)
  4.      ...
  5. SettingWithCopyException:
  6.      A value is trying to be set on a copy of a slice from a DataFrame.
  7.      Try using .loc[row_index,col_indexer] = value instead

Warning

The chained assignment warnings / exceptions are aiming to inform the user of a possibly invalidassignment. There may be false positives; situations where a chained assignment is inadvertentlyreported.