forked from pandas-dev/pandas
/
indexing.rst
1802 lines (1179 loc) · 50.8 KB
/
indexing.rst
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
.. _indexing:
.. currentmodule:: pandas
.. ipython:: python
:suppress:
import numpy as np
np.random.seed(123456)
np.set_printoptions(precision=4, suppress=True)
import pandas as pd
pd.options.display.max_rows=15
***************************
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 be
on Series and DataFrame as they have received more development attention in
this area. Expect more work to be invested in higher-dimensional data
structures (including ``Panel``) in the future, especially in label-based
advanced indexing.
.. note::
The Python and NumPy indexing operators ``[]`` and attribute operator ``.``
provide quick and easy access to pandas data structures across a wide range
of use cases. This makes interactive work intuitive, as there's little new
to learn if you already know how to deal with Python dictionaries and NumPy
arrays. However, since the type of the data to be accessed isn't known in
advance, directly using standard operators has some optimization limits. For
production code, we recommended that you take advantage of the optimized
pandas data access methods exposed in this chapter.
.. 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 :ref:`Returning a View versus Copy
<indexing.view_versus_copy>`
.. warning::
In 0.15.0 ``Index`` has internally been refactored to no longer subclass ``ndarray``
but instead subclass ``PandasObject``, similarly to the rest of the pandas objects. This should be
a transparent change with only very limited API implications (See the :ref:`Internal Refactoring <whatsnew_0150.refactoring>`)
.. warning::
Indexing on an integer-based Index with floats has been clarified in 0.18.0, for a summary of the changes, see :ref:`here <whatsnew_0180.float_indexers>`.
See the :ref:`MultiIndex / Advanced Indexing <advanced>` for ``MultiIndex`` and more advanced indexing documentation.
See the :ref:`cookbook<cookbook.selection>` for some advanced strategies
.. _indexing.choice:
Different Choices for Indexing
------------------------------
.. versionadded:: 0.11.0
Object selection has had a number of user-requested additions in order to
support more explicit location based indexing. Pandas now supports three types
of 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 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 python
slices, **both** the start and the stop are included!)
- A boolean array
- A ``callable`` function with one argument (the calling Series, DataFrame or Panel) and
that returns valid output for indexing (one of the above)
.. versionadded:: 0.18.1
See more at :ref:`Selection by Label <indexing.label>`
- ``.iloc`` is primarily integer position based (from ``0`` to
``length-1`` of the axis), but may also be used with a boolean
array. ``.iloc`` will raise ``IndexError`` if a requested
indexer is out-of-bounds, except *slice* indexers which allow
out-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, DataFrame or Panel) and
that returns valid output for indexing (one of the above)
.. versionadded:: 0.18.1
See more at :ref:`Selection by Position <indexing.integer>`
See more at :ref:`Advanced Indexing <advanced>` and :ref:`Advanced
Hierarchical <advanced.advanced_hierarchical>`.
- ``.loc``, ``.iloc``, and also ``[]`` indexing can accept a ``callable`` as indexer. See more at :ref:`Selection By Callable <indexing.callable>`.
Getting values from an object with multi-axes selection uses the following
notation (using ``.loc`` as an example, but applies to ``.iloc`` as
well). Any of the axes accessors may be the null slice ``:``. Axes left out of
the specification are assumed to be ``:``. (e.g. ``p.loc['a']`` is equiv to
``p.loc['a', :, :]``)
.. csv-table::
:header: "Object Type", "Indexers"
:widths: 30, 50
:delim: ;
Series; ``s.loc[indexer]``
DataFrame; ``df.loc[row_indexer,column_indexer]``
Panel; ``p.loc[item_indexer,major_indexer,minor_indexer]``
.. _indexing.basics:
Basics
------
As mentioned when introducing the data structures in the :ref:`last section
<basics>`, the primary function of indexing with ``[]`` (a.k.a. ``__getitem__``
for those familiar with implementing class behavior in Python) is selecting out
lower-dimensional slices. Thus,
.. csv-table::
:header: "Object Type", "Selection", "Return Value Type"
:widths: 30, 30, 60
:delim: ;
Series; ``series[label]``; scalar value
DataFrame; ``frame[colname]``; ``Series`` corresponding to colname
Panel; ``panel[itemname]``; ``DataFrame`` corresponding to the itemname
Here we construct a simple time series data set to use for illustrating the
indexing functionality:
.. ipython:: python
dates = pd.date_range('1/1/2000', periods=8)
df = pd.DataFrame(np.random.randn(8, 4), index=dates, columns=['A', 'B', 'C', 'D'])
df
panel = pd.Panel({'one' : df, 'two' : df - df.mean()})
panel
.. note::
None of the indexing functionality is time series specific unless
specifically stated.
Thus, as per above, we have the most basic indexing using ``[]``:
.. ipython:: python
s = df['A']
s[dates[5]]
panel['two']
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 be
raised. Multiple columns can also be set in this manner:
.. ipython:: python
df
df[['B', 'A']] = df[['A', 'B']]
df
You may find this useful for applying a transform (in-place) to a subset of the
columns.
.. 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.
.. ipython:: python
df[['A', 'B']]
df.loc[:,['B', 'A']] = df[['A', 'B']]
df[['A', 'B']]
The correct way is to use raw values
.. ipython:: python
df.loc[:,['B', 'A']] = df[['A', 'B']].values
df[['A', 'B']]
Attribute Access
----------------
.. _indexing.columns.multiple:
.. _indexing.df_cols:
.. _indexing.attribute_access:
You may access an index on a ``Series``, column on a ``DataFrame``, and an item on a ``Panel`` directly
as an attribute:
.. ipython:: python
sa = pd.Series([1,2,3],index=list('abc'))
dfa = df.copy()
.. ipython:: python
sa.b
dfa.A
panel.one
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 fails silently, creating a new attribute rather than a
new column.
.. ipython:: python
sa.a = 5
sa
dfa.A = list(range(len(dfa.index))) # ok if A already exists
dfa
dfa['A'] = list(range(len(dfa.index))) # use this form to create a new column
dfa
.. 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
<http://docs.python.org/2.7/reference/lexical_analysis.html#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``, ``labels``.
- In any of these cases, standard indexing will still work, e.g. ``s['1']``, ``s['min']``, and ``s['index']`` will
access the corresponding element or column.
- The ``Series/Panel`` accesses are available starting in 0.13.0.
If you are using the IPython environment, you may also use tab-completion to
see these accessible attributes.
You can also assign a ``dict`` to a row of a ``DataFrame``:
.. ipython:: python
x = pd.DataFrame({'x': [1, 2, 3], 'y': [3, 4, 5]})
x.iloc[1] = dict(x=9, y=99)
x
Slicing ranges
--------------
The most robust and consistent way of slicing ranges along arbitrary axes is
described in the :ref:`Selection by Position <indexing.integer>` section
detailing 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 of
the values and the corresponding labels:
.. ipython:: python
s[:5]
s[::2]
s[::-1]
Note that setting works as well:
.. ipython:: python
s2 = s.copy()
s2[:5] = 0
s2
With DataFrame, slicing inside of ``[]`` **slices the rows**. This is provided
largely as a convenience since it is such a common operation.
.. ipython:: python
df[:3]
df[::-1]
.. _indexing.label:
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 :ref:`Returning a View versus Copy <indexing.view_versus_copy>`
.. warning::
``.loc`` is strict when you present slicers that are not compatible (or convertible) with the index type. For example
using integers in a ``DatetimeIndex``. These will raise a ``TypeError``.
.. ipython:: python
dfl = pd.DataFrame(np.random.randn(5,4), columns=list('ABCD'), index=pd.date_range('20130101',periods=5))
dfl
.. code-block:: ipython
In [4]: dfl.loc[2:3]
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.
.. ipython:: python
dfl.loc['20130102':'20130104']
pandas provides a suite of methods in order to have **purely label based indexing**. This is a strict inclusion based protocol.
**At least 1** of the labels for which you ask, must be in the index or a ``KeyError`` will be raised! When slicing, the start bound is *included*, **AND** the stop bound is *included*. 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 python slices, **both** the start and the stop are included!)
- A boolean array
- A ``callable``, see :ref:`Selection By Callable <indexing.callable>`
.. ipython:: python
s1 = pd.Series(np.random.randn(6),index=list('abcdef'))
s1
s1.loc['c':]
s1.loc['b']
Note that setting works as well:
.. ipython:: python
s1.loc['c':] = 0
s1
With a DataFrame
.. ipython:: python
df1 = pd.DataFrame(np.random.randn(6,4),
index=list('abcdef'),
columns=list('ABCD'))
df1
df1.loc[['a', 'b', 'd'], :]
Accessing via label slices
.. ipython:: python
df1.loc['d':, 'A':'C']
For getting a cross section using a label (equiv to ``df.xs('a')``)
.. ipython:: python
df1.loc['a']
For getting values with a boolean array
.. ipython:: python
df1.loc['a'] > 0
df1.loc[:, df1.loc['a'] > 0]
For getting a value explicitly (equiv to deprecated ``df.get_value('a','A')``)
.. ipython:: python
# this is also equivalent to ``df1.at['a','A']``
df1.loc['a', 'A']
.. _indexing.integer:
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 :ref:`Returning a View versus Copy <indexing.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 bounds 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 :ref:`Selection By Callable <indexing.callable>`
.. ipython:: python
s1 = pd.Series(np.random.randn(5), index=list(range(0,10,2)))
s1
s1.iloc[:3]
s1.iloc[3]
Note that setting works as well:
.. ipython:: python
s1.iloc[:3] = 0
s1
With a DataFrame
.. ipython:: python
df1 = pd.DataFrame(np.random.randn(6,4),
index=list(range(0,12,2)),
columns=list(range(0,8,2)))
df1
Select via integer slicing
.. ipython:: python
df1.iloc[:3]
df1.iloc[1:5, 2:4]
Select via integer list
.. ipython:: python
df1.iloc[[1, 3, 5], [1, 3]]
.. ipython:: python
df1.iloc[1:3, :]
.. ipython:: python
df1.iloc[:, 1:3]
.. ipython:: python
# this is also equivalent to ``df1.iat[1,1]``
df1.iloc[1, 1]
For getting a cross section using an integer position (equiv to ``df.xs(1)``)
.. ipython:: python
df1.iloc[1]
Out of range slice indexes are handled gracefully just as in Python/Numpy.
.. ipython:: python
# these are allowed in python/numpy.
# Only works in Pandas starting from v0.14.0.
x = list('abcdef')
x
x[4:10]
x[8:10]
s = pd.Series(x)
s
s.iloc[4:10]
s.iloc[8:10]
.. note::
Prior to v0.14.0, ``iloc`` would not accept out of bounds indexers for
slices, e.g. a value that exceeds the length of the object being indexed.
Note that this could result in an empty axis (e.g. an empty DataFrame being
returned)
.. ipython:: python
dfl = pd.DataFrame(np.random.randn(5,2), columns=list('AB'))
dfl
dfl.iloc[:, 2:3]
dfl.iloc[:, 1:3]
dfl.iloc[4:6]
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 an
``IndexError``
.. code-block:: python
dfl.iloc[[4, 5, 6]]
IndexError: positional indexers are out-of-bounds
dfl.iloc[:, 4]
IndexError: single positional indexer is out-of-bounds
.. _indexing.callable:
Selection By Callable
---------------------
.. versionadded:: 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, DataFrame or Panel) and that returns valid output for indexing.
.. ipython:: python
df1 = pd.DataFrame(np.random.randn(6, 4),
index=list('abcdef'),
columns=list('ABCD'))
df1
df1.loc[lambda df: df.A > 0, :]
df1.loc[:, lambda df: ['A', 'B']]
df1.iloc[:, lambda df: [0, 1]]
df1[lambda df: df.columns[0]]
You can use callable indexing in ``Series``.
.. ipython:: python
df1.A.loc[lambda s: s > 0]
Using these methods / indexers, you can chain data selection operations
without using temporary variable.
.. ipython:: python
bb = pd.read_csv('data/baseball.csv', index_col='id')
(bb.groupby(['year', 'team']).sum()
.loc[lambda df: df.r > 100])
.. _indexing.deprecate_ix:
IX Indexer is Deprecated
------------------------
.. warning::
Starting in 0.20.0, the ``.ix`` indexer is deprecated, in favor of the more strict ``.iloc``
and ``.loc`` indexers.
``.ix`` offers a lot of magic on the inference of what the user wants to do. To wit, ``.ix`` can decide
to index *positionally* OR via *labels* depending on the data type of the index. This has caused quite a
bit 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.
.. ipython:: python
dfd = pd.DataFrame({'A': [1, 2, 3],
'B': [4, 5, 6]},
index=list('abc'))
dfd
Previous Behavior, where you wish to get the 0th and the 2nd elements from the index in the 'A' column.
.. code-block:: ipython
In [3]: dfd.ix[[0, 2], 'A']
Out[3]:
a 1
c 3
Name: A, dtype: int64
Using ``.loc``. Here we will select the appropriate indexes from the index, then use *label* indexing.
.. ipython:: python
dfd.loc[dfd.index[[0, 2]], 'A']
This can also be expressed using ``.iloc``, by explicitly getting locations on the indexers, and using
*positional* indexing to select things.
.. ipython:: python
dfd.iloc[[0, 2], dfd.columns.get_loc('A')]
For getting *multiple* indexers, using ``.get_indexer``
.. ipython:: python
dfd.iloc[[0, 2], dfd.columns.get_indexer(['A', 'B'])]
.. _indexing.basics.partial_setting:
Selecting Random Samples
------------------------
.. versionadded::0.16.1
A random selection of rows or columns from a Series, DataFrame, or Panel with the :meth:`~DataFrame.sample` method. The method will sample rows by default, and accepts a specific number of rows/columns to return, or a fraction of rows.
.. ipython :: python
s = pd.Series([0,1,2,3,4,5])
# When no arguments are passed, returns 1 row.
s.sample()
# One may specify either a number of rows:
s.sample(n=3)
# Or a fraction of the rows:
s.sample(frac=0.5)
By default, ``sample`` will return each row at most once, but one can also sample with replacement
using the ``replace`` option:
.. ipython :: python
s = pd.Series([0,1,2,3,4,5])
# Without replacement (default):
s.sample(n=6, replace=False)
# With replacement:
s.sample(n=6, replace=True)
By default, each row has an equal probability of being selected, but if you want rows
to have different probabilities, you can pass the ``sample`` function sampling weights as
``weights``. 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:
.. ipython :: python
s = pd.Series([0,1,2,3,4,5])
example_weights = [0, 0, 0.2, 0.2, 0.2, 0.4]
s.sample(n=3, weights=example_weights)
# Weights will be re-normalized automatically
example_weights2 = [0.5, 0, 0, 0, 0, 0]
s.sample(n=1, weights=example_weights2)
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 column
as a string.
.. ipython :: python
df2 = pd.DataFrame({'col1':[9,8,7,6], 'weight_column':[0.5, 0.4, 0.1, 0]})
df2.sample(n = 3, weights = 'weight_column')
``sample`` also allows users to sample columns instead of rows using the ``axis`` argument.
.. ipython :: python
df3 = pd.DataFrame({'col1':[1,2,3], 'col2':[2,3,4]})
df3.sample(n=1, axis=1)
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.
.. ipython :: python
df4 = pd.DataFrame({'col1':[1,2,3], 'col2':[2,3,4]})
# With a given seed, the sample will always draw the same rows.
df4.sample(n=2, random_state=2)
df4.sample(n=2, random_state=2)
Setting With Enlargement
------------------------
.. versionadded:: 0.13
The ``.loc/[]`` operations can perform enlargement when setting a non-existant key for that axis.
In the ``Series`` case this is effectively an appending operation
.. ipython:: python
se = pd.Series([1,2,3])
se
se[5] = 5.
se
A ``DataFrame`` can be enlarged on either axis via ``.loc``
.. ipython:: python
dfi = pd.DataFrame(np.arange(6).reshape(3,2),
columns=['A','B'])
dfi
dfi.loc[:,'C'] = dfi.loc[:,'A']
dfi
This is like an ``append`` operation on the ``DataFrame``.
.. ipython:: python
dfi.loc[3] = 5
dfi
.. _indexing.basics.get_value:
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 figure
out what you're asking for. If you only want to access a scalar value, the
fastest way is to use the ``at`` and ``iat`` methods, which are implemented on
all of the data structures.
Similarly to ``loc``, ``at`` provides **label** based scalar lookups, while, ``iat`` provides **integer** based lookups analogously to ``iloc``
.. ipython:: python
s.iat[5]
df.at[dates[5], 'A']
df.iat[3, 0]
You can also set using these same indexers.
.. ipython:: python
df.at[dates[5], 'E'] = 7
df.iat[3, 0] = 7
``at`` may enlarge the object in-place as above if the indexer is missing.
.. ipython:: python
df.at[dates[-1]+1, 0] = 7
df
Boolean indexing
----------------
.. _indexing.boolean:
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.
Using a boolean vector to index a Series works exactly as in a numpy ndarray:
.. ipython:: python
s = pd.Series(range(-3, 4))
s
s[s > 0]
s[(s < -1) | (s > 0.5)]
s[~(s < 0)]
You may select rows from a DataFrame using a boolean vector the same length as
the DataFrame's index (for example, something derived from one of the columns
of the DataFrame):
.. ipython:: python
df[df['A'] > 0]
List comprehensions and ``map`` method of Series can also be used to produce
more complex criteria:
.. ipython:: python
df2 = pd.DataFrame({'a' : ['one', 'one', 'two', 'three', 'two', 'one', 'six'],
'b' : ['x', 'y', 'y', 'x', 'y', 'x', 'x'],
'c' : np.random.randn(7)})
# only want 'two' or 'three'
criterion = df2['a'].map(lambda x: x.startswith('t'))
df2[criterion]
# equivalent but slower
df2[[x.startswith('t') for x in df2['a']]]
# Multiple criteria
df2[criterion & (df2['b'] == 'x')]
Note, with the choice methods :ref:`Selection by Label <indexing.label>`, :ref:`Selection by Position <indexing.integer>`,
and :ref:`Advanced Indexing <advanced>` you may select along more than one axis using boolean vectors combined with other indexing expressions.
.. ipython:: python
df2.loc[criterion & (df2['b'] == 'x'),'b':'c']
.. _indexing.basics.indexing_isin:
Indexing with isin
------------------
Consider the ``isin`` method of Series, which returns a boolean vector 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:
.. ipython:: python
s = pd.Series(np.arange(5), index=np.arange(5)[::-1], dtype='int64')
s
s.isin([2, 4, 6])
s[s.isin([2, 4, 6])]
The same method is available for ``Index`` objects and is useful for the cases
when you don't know which of the sought labels are in fact present:
.. ipython:: python
s[s.index.isin([2, 4, 6])]
# compare it to the following
s[[2, 4, 6]]
In addition to that, ``MultiIndex`` allows selecting a separate level to use
in the membership check:
.. ipython:: python
s_mi = pd.Series(np.arange(6),
index=pd.MultiIndex.from_product([[0, 1], ['a', 'b', 'c']]))
s_mi
s_mi.iloc[s_mi.index.isin([(1, 'a'), (2, 'b'), (0, 'c')])]
s_mi.iloc[s_mi.index.isin(['a', 'c', 'e'], level=1)]
DataFrame also has an ``isin`` method. When calling ``isin``, pass a set of
values as either an array or dict. If values is an array, ``isin`` returns
a DataFrame of booleans that is the same shape as the original DataFrame, with True
wherever the element is in the sequence of values.
.. ipython:: python
df = pd.DataFrame({'vals': [1, 2, 3, 4], 'ids': ['a', 'b', 'f', 'n'],
'ids2': ['a', 'n', 'c', 'n']})
values = ['a', 'b', 1, 3]
df.isin(values)
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 is
a list of items you want to check for.
.. ipython:: python
values = {'ids': ['a', 'b'], 'vals': [1, 3]}
df.isin(values)
Combine DataFrame's ``isin`` with the ``any()`` and ``all()`` methods to
quickly select subsets of your data that meet a given criteria.
To select a row where each column meets its own criterion:
.. ipython:: python
values = {'ids': ['a', 'b'], 'ids2': ['a', 'c'], 'vals': [1, 3]}
row_mask = df.isin(values).all(1)
df[row_mask]
.. _indexing.where_mask:
The :meth:`~pandas.DataFrame.where` Method and Masking
------------------------------------------------------
Selecting values from a Series with a boolean vector generally returns a
subset of the data. To guarantee that selection output has the same shape as
the original data, you can use the ``where`` method in ``Series`` and ``DataFrame``.
To return only the selected rows
.. ipython:: python
s[s > 0]
To return a Series of the same shape as the original
.. ipython:: python
s.where(s > 0)
Selecting values from a DataFrame with a boolean criterion now also preserves
input data shape. ``where`` is used under the hood as the implementation.
Equivalent is ``df.where(df < 0)``
.. ipython:: python
:suppress:
dates = pd.date_range('1/1/2000', periods=8)
df = pd.DataFrame(np.random.randn(8, 4), index=dates, columns=['A', 'B', 'C', 'D'])
.. ipython:: python
df[df < 0]
In addition, ``where`` takes an optional ``other`` argument for replacement of
values where the condition is False, in the returned copy.
.. ipython:: python
df.where(df < 0, -df)
You may wish to set values based on some boolean criteria.
This can be done intuitively like so:
.. ipython:: python
s2 = s.copy()
s2[s2 < 0] = 0
s2
df2 = df.copy()
df2[df2 < 0] = 0
df2
By default, ``where`` returns a modified copy of the data. There is an
optional parameter ``inplace`` so that the original data can be modified
without creating a copy:
.. ipython:: python
df_orig = df.copy()
df_orig.where(df > 0, -df, inplace=True);
df_orig
.. note::
The signature for :func:`DataFrame.where` differs from :func:`numpy.where`.
Roughly ``df1.where(m, df2)`` is equivalent to ``np.where(m, df1, df2)``.
.. ipython:: python
df.where(df < 0, -df) == np.where(df < 0, df, -df)
**alignment**
Furthermore, ``where`` aligns the input boolean condition (ndarray or DataFrame),
such that partial selection with setting is possible. This is analogous to
partial setting via ``.loc`` (but on the contents rather than the axis labels)
.. ipython:: python
df2 = df.copy()
df2[ df2[1:4] > 0 ] = 3
df2
.. versionadded:: 0.13
Where can also accept ``axis`` and ``level`` parameters to align the input when
performing the ``where``.
.. ipython:: python
df2 = df.copy()
df2.where(df2>0,df2['A'],axis='index')
This is equivalent (but faster than) the following.
.. ipython:: python
df2 = df.copy()
df.apply(lambda x, y: x.where(x>0,y), y=df['A'])
.. versionadded:: 0.18.1
Where can accept a callable as condition and ``other`` arguments. The function must
be with one argument (the calling Series or DataFrame) and that returns valid output
as condition and ``other`` argument.
.. ipython:: python
df3 = pd.DataFrame({'A': [1, 2, 3],
'B': [4, 5, 6],
'C': [7, 8, 9]})
df3.where(lambda x: x > 4, lambda x: x + 10)
**mask**
``mask`` is the inverse boolean operation of ``where``.
.. ipython:: python