Essential basic functionality

Here we discuss a lot of the essential functionality common to the pandas datastructures. Here’s how to create some of the objects used in the examples fromthe previous section:

In [1]: index = pd.date_range('1/1/2000', periods=8)
 
In [2]: s = pd.Series(np.random.randn(5), index=['a', 'b', 'c', 'd', 'e'])
 
In [3]: df = pd.DataFrame(np.random.randn(8, 3), index=index,
   ...:                   columns=['A', 'B', 'C'])
   ...: 

Head and tail

To view a small sample of a Series or DataFrame object, use thehead() and tail() methods. The default numberof elements to display is five, but you may pass a custom number.

In [4]: long_series = pd.Series(np.random.randn(1000))
 
In [5]: long_series.head()
Out[5]: 
0   -1.157892
1   -1.344312
2    0.844885
3    1.075770
4   -0.109050
dtype: float64
 
In [6]: long_series.tail(3)
Out[6]: 
997   -0.289388
998   -1.020544
999    0.589993
dtype: float64

Attributes and underlying data

pandas objects have a number of attributes enabling you to access the metadata

• shape: gives the axis dimensions of the object, consistent with ndarray
• • Axis labels
  • • Series: index (only axis)
    • DataFrame: index (rows) and columns

Note, these attributes can be safely assigned to!

In [7]: df[:2]
Out[7]: 
                   A         B         C
2000-01-01 -0.173215  0.119209 -1.044236
2000-01-02 -0.861849 -2.104569 -0.494929
 
In [8]: df.columns = [x.lower() for x in df.columns]
 
In [9]: df
Out[9]: 
                   a         b         c
2000-01-01 -0.173215  0.119209 -1.044236
2000-01-02 -0.861849 -2.104569 -0.494929
2000-01-03  1.071804  0.721555 -0.706771
2000-01-04 -1.039575  0.271860 -0.424972
2000-01-05  0.567020  0.276232 -1.087401
2000-01-06 -0.673690  0.113648 -1.478427
2000-01-07  0.524988  0.404705  0.577046
2000-01-08 -1.715002 -1.039268 -0.370647

Pandas objects (Index, Series, DataFrame) can bethought of as containers for arrays, which hold the actual data and do theactual computation. For many types, the underlying array is anumpy.ndarray. However, pandas and 3rd party libraries may _extend_NumPy’s type system to add support for custom arrays(see dtypes).

To get the actual data inside a Index or Series, usethe .array property

In [10]: s.array
Out[10]: 
<PandasArray>
[ 0.4691122999071863, -0.2828633443286633, -1.5090585031735124,
 -1.1356323710171934,  1.2121120250208506]
Length: 5, dtype: float64
 
In [11]: s.index.array
Out[11]: 
<PandasArray>
['a', 'b', 'c', 'd', 'e']
Length: 5, dtype: object

array will always be an ExtensionArray.The exact details of what an ExtensionArray is and why pandas uses them is a bitbeyond the scope of this introduction. See dtypes for more.

If you know you need a NumPy array, use to_numpy()or numpy.asarray().

In [12]: s.to_numpy()
Out[12]: array([ 0.4691, -0.2829, -1.5091, -1.1356,  1.2121])
 
In [13]: np.asarray(s)
Out[13]: array([ 0.4691, -0.2829, -1.5091, -1.1356,  1.2121])

When the Series or Index is backed byan ExtensionArray, to_numpy()may involve copying data and coercing values. See dtypes for more.

to_numpy() gives some control over the dtype of theresulting numpy.ndarray. For example, consider datetimes with timezones.NumPy doesn’t have a dtype to represent timezone-aware datetimes, so thereare two possibly useful representations:

• An object-dtype numpy.ndarray with Timestamp objects, eachwith the correct tz
• A datetime64[ns] -dtype numpy.ndarray, where the values havebeen converted to UTC and the timezone discardedTimezones may be preserved with dtype=object

In [14]: ser = pd.Series(pd.date_range('2000', periods=2, tz="CET"))
 
In [15]: ser.to_numpy(dtype=object)
Out[15]: 
array([Timestamp('2000-01-01 00:00:00+0100', tz='CET', freq='D'),
       Timestamp('2000-01-02 00:00:00+0100', tz='CET', freq='D')],
      dtype=object)

Or thrown away with dtype='datetime64[ns]'

In [16]: ser.to_numpy(dtype="datetime64[ns]")
Out[16]: 
array(['1999-12-31T23:00:00.000000000', '2000-01-01T23:00:00.000000000'],
      dtype='datetime64[ns]')

Getting the “raw data” inside a DataFrame is possibly a bit morecomplex. When your DataFrame only has a single data type for all thecolumns, DataFrame.to_numpy() will return the underlying data:

In [17]: df.to_numpy()
Out[17]: 
array([[-0.1732,  0.1192, -1.0442],
       [-0.8618, -2.1046, -0.4949],
       [ 1.0718,  0.7216, -0.7068],
       [-1.0396,  0.2719, -0.425 ],
       [ 0.567 ,  0.2762, -1.0874],
       [-0.6737,  0.1136, -1.4784],
       [ 0.525 ,  0.4047,  0.577 ],
       [-1.715 , -1.0393, -0.3706]])

If a DataFrame contains homogeneously-typed data, the ndarray canactually be modified in-place, and the changes will be reflected in the datastructure. For heterogeneous data (e.g. some of the DataFrame’s columns are notall the same dtype), this will not be the case. The values attribute itself,unlike the axis labels, cannot be assigned to.

Note

When working with heterogeneous data, the dtype of the resulting ndarraywill be chosen to accommodate all of the data involved. For example, ifstrings are involved, the result will be of object dtype. If there are onlyfloats and integers, the resulting array will be of float dtype.

In the past, pandas recommended Series.values or DataFrame.valuesfor extracting the data from a Series or DataFrame. You’ll still find referencesto these in old code bases and online. Going forward, we recommend avoiding.values and using .array or .to_numpy(). .values has the followingdrawbacks:

• When your Series contains an extension type, it’sunclear whether Series.values returns a NumPy array or the extension array.Series.array will always return an ExtensionArray, and will nevercopy data. Series.to_numpy() will always return a NumPy array,potentially at the cost of copying / coercing values.
• When your DataFrame contains a mixture of data types, DataFrame.values mayinvolve copying data and coercing values to a common dtype, a relatively expensiveoperation. DataFrame.to_numpy(), being a method, makes it clearer that thereturned NumPy array may not be a view on the same data in the DataFrame.

Accelerated operations

pandas has support for accelerating certain types of binary numerical and boolean operations usingthe numexpr library and the bottleneck libraries.

These libraries are especially useful when dealing with large data sets, and provide largespeedups. numexpr uses smart chunking, caching, and multiple cores. bottleneck isa set of specialized cython routines that are especially fast when dealing with arrays that havenans.

Here is a sample (using 100 column x 100,000 row DataFrames):

You are highly encouraged to install both libraries. See the sectionRecommended Dependencies for more installation info.

These are both enabled to be used by default, you can control this by setting the options:

New in version 0.20.0.

pd.set_option('compute.use_bottleneck', False)
pd.set_option('compute.use_numexpr', False)

Flexible binary operations

With binary operations between pandas data structures, there are two key pointsof interest:

• Broadcasting behavior between higher- (e.g. DataFrame) andlower-dimensional (e.g. Series) objects.
• Missing data in computations.

We will demonstrate how to manage these issues independently, though they canbe handled simultaneously.

Matching / broadcasting behavior

DataFrame has the methods add(), sub(),mul(), div() and related functionsradd(), rsub(), …for carrying out binary operations. For broadcasting behavior,Series input is of primary interest. Using these functions, you can use toeither match on the index or columns via the axis keyword:

In [18]: df = pd.DataFrame({
   ....:     'one': pd.Series(np.random.randn(3), index=['a', 'b', 'c']),
   ....:     'two': pd.Series(np.random.randn(4), index=['a', 'b', 'c', 'd']),
   ....:     'three': pd.Series(np.random.randn(3), index=['b', 'c', 'd'])})
   ....: 
 
In [19]: df
Out[19]: 
        one       two     three
a  1.394981  1.772517       NaN
b  0.343054  1.912123 -0.050390
c  0.695246  1.478369  1.227435
d       NaN  0.279344 -0.613172
 
In [20]: row = df.iloc[1]
 
In [21]: column = df['two']
 
In [22]: df.sub(row, axis='columns')
Out[22]: 
        one       two     three
a  1.051928 -0.139606       NaN
b  0.000000  0.000000  0.000000
c  0.352192 -0.433754  1.277825
d       NaN -1.632779 -0.562782
 
In [23]: df.sub(row, axis=1)
Out[23]: 
        one       two     three
a  1.051928 -0.139606       NaN
b  0.000000  0.000000  0.000000
c  0.352192 -0.433754  1.277825
d       NaN -1.632779 -0.562782
 
In [24]: df.sub(column, axis='index')
Out[24]: 
        one  two     three
a -0.377535  0.0       NaN
b -1.569069  0.0 -1.962513
c -0.783123  0.0 -0.250933
d       NaN  0.0 -0.892516
 
In [25]: df.sub(column, axis=0)
Out[25]: 
        one  two     three
a -0.377535  0.0       NaN
b -1.569069  0.0 -1.962513
c -0.783123  0.0 -0.250933
d       NaN  0.0 -0.892516

Furthermore you can align a level of a MultiIndexed DataFrame with a Series.

In [26]: dfmi = df.copy()
 
In [27]: dfmi.index = pd.MultiIndex.from_tuples([(1, 'a'), (1, 'b'),
   ....:                                         (1, 'c'), (2, 'a')],
   ....:                                        names=['first', 'second'])
   ....: 
 
In [28]: dfmi.sub(column, axis=0, level='second')
Out[28]: 
                   one       two     three
first second                              
1     a      -0.377535  0.000000       NaN
      b      -1.569069  0.000000 -1.962513
      c      -0.783123  0.000000 -0.250933
2     a            NaN -1.493173 -2.385688

Series and Index also support the divmod() builtin. This function takesthe floor division and modulo operation at the same time returning a two-tupleof the same type as the left hand side. For example:

In [29]: s = pd.Series(np.arange(10))
 
In [30]: s
Out[30]: 
0    0
1    1
2    2
3    3
4    4
5    5
6    6
7    7
8    8
9    9
dtype: int64
 
In [31]: div, rem = divmod(s, 3)
 
In [32]: div
Out[32]: 
0    0
1    0
2    0
3    1
4    1
5    1
6    2
7    2
8    2
9    3
dtype: int64
 
In [33]: rem
Out[33]: 
0    0
1    1
2    2
3    0
4    1
5    2
6    0
7    1
8    2
9    0
dtype: int64
 
In [34]: idx = pd.Index(np.arange(10))
 
In [35]: idx
Out[35]: Int64Index([0, 1, 2, 3, 4, 5, 6, 7, 8, 9], dtype='int64')
 
In [36]: div, rem = divmod(idx, 3)
 
In [37]: div
Out[37]: Int64Index([0, 0, 0, 1, 1, 1, 2, 2, 2, 3], dtype='int64')
 
In [38]: rem
Out[38]: Int64Index([0, 1, 2, 0, 1, 2, 0, 1, 2, 0], dtype='int64')

We can also do elementwise divmod():

In [39]: div, rem = divmod(s, [2, 2, 3, 3, 4, 4, 5, 5, 6, 6])
 
In [40]: div
Out[40]: 
0    0
1    0
2    0
3    1
4    1
5    1
6    1
7    1
8    1
9    1
dtype: int64
 
In [41]: rem
Out[41]: 
0    0
1    1
2    2
3    0
4    0
5    1
6    1
7    2
8    2
9    3
dtype: int64

Missing data / operations with fill values

In Series and DataFrame, the arithmetic functions have the option of inputtinga fill_value, namely a value to substitute when at most one of the values ata location are missing. For example, when adding two DataFrame objects, you maywish to treat NaN as 0 unless both DataFrames are missing that value, in whichcase the result will be NaN (you can later replace NaN with some other valueusing fillna if you wish).

In [42]: df
Out[42]: 
        one       two     three
a  1.394981  1.772517       NaN
b  0.343054  1.912123 -0.050390
c  0.695246  1.478369  1.227435
d       NaN  0.279344 -0.613172
 
In [43]: df2
Out[43]: 
        one       two     three
a  1.394981  1.772517  1.000000
b  0.343054  1.912123 -0.050390
c  0.695246  1.478369  1.227435
d       NaN  0.279344 -0.613172
 
In [44]: df + df2
Out[44]: 
        one       two     three
a  2.789963  3.545034       NaN
b  0.686107  3.824246 -0.100780
c  1.390491  2.956737  2.454870
d       NaN  0.558688 -1.226343
 
In [45]: df.add(df2, fill_value=0)
Out[45]: 
        one       two     three
a  2.789963  3.545034  1.000000
b  0.686107  3.824246 -0.100780
c  1.390491  2.956737  2.454870
d       NaN  0.558688 -1.226343

Flexible comparisons

Series and DataFrame have the binary comparison methods eq, ne, lt, gt,le, and ge whose behavior is analogous to the binaryarithmetic operations described above:

In [46]: df.gt(df2)
Out[46]: 
     one    two  three
a  False  False  False
b  False  False  False
c  False  False  False
d  False  False  False
 
In [47]: df2.ne(df)
Out[47]: 
     one    two  three
a  False  False   True
b  False  False  False
c  False  False  False
d   True  False  False

These operations produce a pandas object of the same type as the left-hand-sideinput that is of dtype bool. These boolean objects can be used inindexing operations, see the section on Boolean indexing.

Boolean reductions

You can apply the reductions: empty, any(),all(), and bool() to provide away to summarize a boolean result.

In [48]: (df > 0).all()
Out[48]: 
one      False
two       True
three    False
dtype: bool
 
In [49]: (df > 0).any()
Out[49]: 
one      True
two      True
three    True
dtype: bool

You can reduce to a final boolean value.

In [50]: (df > 0).any().any()
Out[50]: True

You can test if a pandas object is empty, via the empty property.

In [51]: df.empty
Out[51]: False
 
In [52]: pd.DataFrame(columns=list('ABC')).empty
Out[52]: True

To evaluate single-element pandas objects in a boolean context, use the methodbool():

In [53]: pd.Series([True]).bool()
Out[53]: True
 
In [54]: pd.Series([False]).bool()
Out[54]: False
 
In [55]: pd.DataFrame([[True]]).bool()
Out[55]: True
 
In [56]: pd.DataFrame([[False]]).bool()
Out[56]: False

Warning

You might be tempted to do the following:

>>> if df:
...     pass

Or

>>> df and df2

These will both raise errors, as you are trying to compare multiple values.:

ValueError: The truth value of an array is ambiguous. Use a.empty, a.any() or a.all().

See gotchas for a more detailed discussion.

Comparing if objects are equivalent

Often you may find that there is more than one way to compute the sameresult. As a simple example, consider df + df and df 2. To testthat these two computations produce the same result, given the toolsshown above, you might imagine using (df + df == df 2).all(). But infact, this expression is False:

In [57]: df + df == df * 2
Out[57]: 
     one   two  three
a   True  True  False
b   True  True   True
c   True  True   True
d  False  True   True
 
In [58]: (df + df == df * 2).all()
Out[58]: 
one      False
two       True
three    False
dtype: bool

Notice that the boolean DataFrame df + df == df * 2 contains some False values!This is because NaNs do not compare as equals:

In [59]: np.nan == np.nan
Out[59]: False

So, NDFrames (such as Series and DataFrames)have an equals() method for testing equality, with NaNs incorresponding locations treated as equal.

In [60]: (df + df).equals(df * 2)
Out[60]: True

Note that the Series or DataFrame index needs to be in the same order forequality to be True:

In [61]: df1 = pd.DataFrame({'col': ['foo', 0, np.nan]})
 
In [62]: df2 = pd.DataFrame({'col': [np.nan, 0, 'foo']}, index=[2, 1, 0])
 
In [63]: df1.equals(df2)
Out[63]: False
 
In [64]: df1.equals(df2.sort_index())
Out[64]: True

Comparing array-like objects

You can conveniently perform element-wise comparisons when comparing a pandasdata structure with a scalar value:

In [65]: pd.Series(['foo', 'bar', 'baz']) == 'foo'
Out[65]: 
0     True
1    False
2    False
dtype: bool
 
In [66]: pd.Index(['foo', 'bar', 'baz']) == 'foo'
Out[66]: array([ True, False, False])

Pandas also handles element-wise comparisons between different array-likeobjects of the same length:

In [67]: pd.Series(['foo', 'bar', 'baz']) == pd.Index(['foo', 'bar', 'qux'])
Out[67]: 
0     True
1     True
2    False
dtype: bool
 
In [68]: pd.Series(['foo', 'bar', 'baz']) == np.array(['foo', 'bar', 'qux'])
Out[68]: 
0     True
1     True
2    False
dtype: bool

Trying to compare Index or Series objects of different lengths willraise a ValueError:

In [55]: pd.Series(['foo', 'bar', 'baz']) == pd.Series(['foo', 'bar'])
ValueError: Series lengths must match to compare
 
In [56]: pd.Series(['foo', 'bar', 'baz']) == pd.Series(['foo'])
ValueError: Series lengths must match to compare

Note that this is different from the NumPy behavior where a comparison canbe broadcast:

In [69]: np.array([1, 2, 3]) == np.array([2])
Out[69]: array([False,  True, False])

or it can return False if broadcasting can not be done:

In [70]: np.array([1, 2, 3]) == np.array([1, 2])
Out[70]: False

Combining overlapping data sets

A problem occasionally arising is the combination of two similar data setswhere values in one are preferred over the other. An example would be two dataseries representing a particular economic indicator where one is considered tobe of “higher quality”. However, the lower quality series might extend furtherback in history or have more complete data coverage. As such, we would like tocombine two DataFrame objects where missing values in one DataFrame areconditionally filled with like-labeled values from the other DataFrame. Thefunction implementing this operation is combine_first(),which we illustrate:

In [71]: df1 = pd.DataFrame({'A': [1., np.nan, 3., 5., np.nan],
   ....:                     'B': [np.nan, 2., 3., np.nan, 6.]})
   ....: 
 
In [72]: df2 = pd.DataFrame({'A': [5., 2., 4., np.nan, 3., 7.],
   ....:                     'B': [np.nan, np.nan, 3., 4., 6., 8.]})
   ....: 
 
In [73]: df1
Out[73]: 
     A    B
0  1.0  NaN
1  NaN  2.0
2  3.0  3.0
3  5.0  NaN
4  NaN  6.0
 
In [74]: df2
Out[74]: 
     A    B
0  5.0  NaN
1  2.0  NaN
2  4.0  3.0
3  NaN  4.0
4  3.0  6.0
5  7.0  8.0
 
In [75]: df1.combine_first(df2)
Out[75]: 
     A    B
0  1.0  NaN
1  2.0  2.0
2  3.0  3.0
3  5.0  4.0
4  3.0  6.0
5  7.0  8.0

General DataFrame combine

The combine_first() method above calls the more generalDataFrame.combine(). This method takes another DataFrameand a combiner function, aligns the input DataFrame and then passes the combinerfunction pairs of Series (i.e., columns whose names are the same).

So, for instance, to reproduce combine_first() as above:

In [76]: def combiner(x, y):
   ....:     return np.where(pd.isna(x), y, x)
   ....: 

Descriptive statistics

There exists a large number of methods for computing descriptive statistics andother related operations on Series, DataFrame. Most of theseare aggregations (hence producing a lower-dimensional result) likesum(), mean(), and quantile(),but some of them, like cumsum() and cumprod(),produce an object of the same size. Generally speaking, these methods take anaxis argument, just like ndarray.{sum, std, …}, but the axis can bespecified by name or integer:

• Series: no axis argument needed
• DataFrame: “index” (axis=0, default), “columns” (axis=1)

For example:

In [77]: df
Out[77]: 
        one       two     three
a  1.394981  1.772517       NaN
b  0.343054  1.912123 -0.050390
c  0.695246  1.478369  1.227435
d       NaN  0.279344 -0.613172
 
In [78]: df.mean(0)
Out[78]: 
one      0.811094
two      1.360588
three    0.187958
dtype: float64
 
In [79]: df.mean(1)
Out[79]: 
a    1.583749
b    0.734929
c    1.133683
d   -0.166914
dtype: float64

All such methods have a skipna option signaling whether to exclude missingdata (True by default):

In [80]: df.sum(0, skipna=False)
Out[80]: 
one           NaN
two      5.442353
three         NaN
dtype: float64
 
In [81]: df.sum(axis=1, skipna=True)
Out[81]: 
a    3.167498
b    2.204786
c    3.401050
d   -0.333828
dtype: float64

Combined with the broadcasting / arithmetic behavior, one can describe variousstatistical procedures, like standardization (rendering data zero mean andstandard deviation 1), very concisely:

In [82]: ts_stand = (df - df.mean()) / df.std()
 
In [83]: ts_stand.std()
Out[83]: 
one      1.0
two      1.0
three    1.0
dtype: float64
 
In [84]: xs_stand = df.sub(df.mean(1), axis=0).div(df.std(1), axis=0)
 
In [85]: xs_stand.std(1)
Out[85]: 
a    1.0
b    1.0
c    1.0
d    1.0
dtype: float64

Note that methods like cumsum() and cumprod()preserve the location of NaN values. This is somewhat different fromexpanding() and rolling().For more details please see this note.

In [86]: df.cumsum()
Out[86]: 
        one       two     three
a  1.394981  1.772517       NaN
b  1.738035  3.684640 -0.050390
c  2.433281  5.163008  1.177045
d       NaN  5.442353  0.563873

Here is a quick reference summary table of common functions. Each also takes anoptional level parameter which applies only if the object has ahierarchical index.

Note that by chance some NumPy methods, like mean, std, and sum,will exclude NAs on Series input by default:

In [87]: np.mean(df['one'])
Out[87]: 0.8110935116651192
 
In [88]: np.mean(df['one'].to_numpy())
Out[88]: nan

Series.nunique() will return the number of unique non-NA values in aSeries:

In [89]: series = pd.Series(np.random.randn(500))
 
In [90]: series[20:500] = np.nan
 
In [91]: series[10:20] = 5
 
In [92]: series.nunique()
Out[92]: 11

Summarizing data: describe

There is a convenient describe() function which computes a variety of summarystatistics about a Series or the columns of a DataFrame (excluding NAs ofcourse):

In [93]: series = pd.Series(np.random.randn(1000))
 
In [94]: series[::2] = np.nan
 
In [95]: series.describe()
Out[95]: 
count    500.000000
mean      -0.021292
std        1.015906
min       -2.683763
25%       -0.699070
50%       -0.069718
75%        0.714483
max        3.160915
dtype: float64
 
In [96]: frame = pd.DataFrame(np.random.randn(1000, 5),
   ....:                      columns=['a', 'b', 'c', 'd', 'e'])
   ....: 
 
In [97]: frame.iloc[::2] = np.nan
 
In [98]: frame.describe()
Out[98]: 
                a           b           c           d           e
count  500.000000  500.000000  500.000000  500.000000  500.000000
mean     0.033387    0.030045   -0.043719   -0.051686    0.005979
std      1.017152    0.978743    1.025270    1.015988    1.006695
min     -3.000951   -2.637901   -3.303099   -3.159200   -3.188821
25%     -0.647623   -0.576449   -0.712369   -0.691338   -0.691115
50%      0.047578   -0.021499   -0.023888   -0.032652   -0.025363
75%      0.729907    0.775880    0.618896    0.670047    0.649748
max      2.740139    2.752332    3.004229    2.728702    3.240991

You can select specific percentiles to include in the output:

In [99]: series.describe(percentiles=[.05, .25, .75, .95])
Out[99]: 
count    500.000000
mean      -0.021292
std        1.015906
min       -2.683763
5%        -1.645423
25%       -0.699070
50%       -0.069718
75%        0.714483
95%        1.711409
max        3.160915
dtype: float64

By default, the median is always included.

For a non-numerical Series object, describe() will give a simplesummary of the number of unique values and most frequently occurring values:

In [100]: s = pd.Series(['a', 'a', 'b', 'b', 'a', 'a', np.nan, 'c', 'd', 'a'])
 
In [101]: s.describe()
Out[101]: 
count     9
unique    4
top       a
freq      5
dtype: object

Note that on a mixed-type DataFrame object, describe() willrestrict the summary to include only numerical columns or, if none are, onlycategorical columns:

In [102]: frame = pd.DataFrame({'a': ['Yes', 'Yes', 'No', 'No'], 'b': range(4)})
 
In [103]: frame.describe()
Out[103]: 
              b
count  4.000000
mean   1.500000
std    1.290994
min    0.000000
25%    0.750000
50%    1.500000
75%    2.250000
max    3.000000

This behavior can be controlled by providing a list of types as include/excludearguments. The special value all can also be used:

In [104]: frame.describe(include=['object'])
Out[104]: 
          a
count     4
unique    2
top     Yes
freq      2
 
In [105]: frame.describe(include=['number'])
Out[105]: 
              b
count  4.000000
mean   1.500000
std    1.290994
min    0.000000
25%    0.750000
50%    1.500000
75%    2.250000
max    3.000000
 
In [106]: frame.describe(include='all')
Out[106]: 
          a         b
count     4  4.000000
unique    2       NaN
top     Yes       NaN
freq      2       NaN
mean    NaN  1.500000
std     NaN  1.290994
min     NaN  0.000000
25%     NaN  0.750000
50%     NaN  1.500000
75%     NaN  2.250000
max     NaN  3.000000

That feature relies on select_dtypes. Refer tothere for details about accepted inputs.

Index of min/max values

The idxmin() and idxmax() functions on Seriesand DataFrame compute the index labels with the minimum and maximumcorresponding values:

In [107]: s1 = pd.Series(np.random.randn(5))
 
In [108]: s1
Out[108]: 
0    1.118076
1   -0.352051
2   -1.242883
3   -1.277155
4   -0.641184
dtype: float64
 
In [109]: s1.idxmin(), s1.idxmax()
Out[109]: (3, 0)
 
In [110]: df1 = pd.DataFrame(np.random.randn(5, 3), columns=['A', 'B', 'C'])
 
In [111]: df1
Out[111]: 
          A         B         C
0 -0.327863 -0.946180 -0.137570
1 -0.186235 -0.257213 -0.486567
2 -0.507027 -0.871259 -0.111110
3  2.000339 -2.430505  0.089759
4 -0.321434 -0.033695  0.096271
 
In [112]: df1.idxmin(axis=0)
Out[112]: 
A    2
B    3
C    1
dtype: int64
 
In [113]: df1.idxmax(axis=1)
Out[113]: 
0    C
1    A
2    C
3    A
4    C
dtype: object

When there are multiple rows (or columns) matching the minimum or maximumvalue, idxmin() and idxmax() return the firstmatching index:

In [114]: df3 = pd.DataFrame([2, 1, 1, 3, np.nan], columns=['A'], index=list('edcba'))
 
In [115]: df3
Out[115]: 
     A
e  2.0
d  1.0
c  1.0
b  3.0
a  NaN
 
In [116]: df3['A'].idxmin()
Out[116]: 'd'

Note

idxmin and idxmax are called argmin and argmax in NumPy.

Value counts (histogramming) / mode

The value_counts() Series method and top-level function computes a histogramof a 1D array of values. It can also be used as a function on regular arrays:

In [117]: data = np.random.randint(0, 7, size=50)
 
In [118]: data
Out[118]: 
array([6, 6, 2, 3, 5, 3, 2, 5, 4, 5, 4, 3, 4, 5, 0, 2, 0, 4, 2, 0, 3, 2,
       2, 5, 6, 5, 3, 4, 6, 4, 3, 5, 6, 4, 3, 6, 2, 6, 6, 2, 3, 4, 2, 1,
       6, 2, 6, 1, 5, 4])
 
In [119]: s = pd.Series(data)
 
In [120]: s.value_counts()
Out[120]: 
6    10
2    10
4     9
5     8
3     8
0     3
1     2
dtype: int64
 
In [121]: pd.value_counts(data)
Out[121]: 
6    10
2    10
4     9
5     8
3     8
0     3
1     2
dtype: int64

Similarly, you can get the most frequently occurring value(s) (the mode) of the values in a Series or DataFrame:

In [122]: s5 = pd.Series([1, 1, 3, 3, 3, 5, 5, 7, 7, 7])
 
In [123]: s5.mode()
Out[123]: 
0    3
1    7
dtype: int64
 
In [124]: df5 = pd.DataFrame({"A": np.random.randint(0, 7, size=50),
   .....:                     "B": np.random.randint(-10, 15, size=50)})
   .....: 
 
In [125]: df5.mode()
Out[125]: 
     A   B
0  1.0  -9
1  NaN  10
2  NaN  13

Discretization and quantiling

Continuous values can be discretized using the cut() (bins based on values)and qcut() (bins based on sample quantiles) functions:

In [126]: arr = np.random.randn(20)
 
In [127]: factor = pd.cut(arr, 4)
 
In [128]: factor
Out[128]: 
[(-0.251, 0.464], (-0.968, -0.251], (0.464, 1.179], (-0.251, 0.464], (-0.968, -0.251], ..., (-0.251, 0.464], (-0.968, -0.251], (-0.968, -0.251], (-0.968, -0.251], (-0.968, -0.251]]
Length: 20
Categories (4, interval[float64]): [(-0.968, -0.251] < (-0.251, 0.464] < (0.464, 1.179] <
                                    (1.179, 1.893]]
 
In [129]: factor = pd.cut(arr, [-5, -1, 0, 1, 5])
 
In [130]: factor
Out[130]: 
[(0, 1], (-1, 0], (0, 1], (0, 1], (-1, 0], ..., (-1, 0], (-1, 0], (-1, 0], (-1, 0], (-1, 0]]
Length: 20
Categories (4, interval[int64]): [(-5, -1] < (-1, 0] < (0, 1] < (1, 5]]

qcut() computes sample quantiles. For example, we could slice up somenormally distributed data into equal-size quartiles like so:

In [131]: arr = np.random.randn(30)
 
In [132]: factor = pd.qcut(arr, [0, .25, .5, .75, 1])
 
In [133]: factor
Out[133]: 
[(0.569, 1.184], (-2.278, -0.301], (-2.278, -0.301], (0.569, 1.184], (0.569, 1.184], ..., (-0.301, 0.569], (1.184, 2.346], (1.184, 2.346], (-0.301, 0.569], (-2.278, -0.301]]
Length: 30
Categories (4, interval[float64]): [(-2.278, -0.301] < (-0.301, 0.569] < (0.569, 1.184] <
                                    (1.184, 2.346]]
 
In [134]: pd.value_counts(factor)
Out[134]: 
(1.184, 2.346]      8
(-2.278, -0.301]    8
(0.569, 1.184]      7
(-0.301, 0.569]     7
dtype: int64

We can also pass infinite values to define the bins:

In [135]: arr = np.random.randn(20)
 
In [136]: factor = pd.cut(arr, [-np.inf, 0, np.inf])
 
In [137]: factor
Out[137]: 
[(-inf, 0.0], (0.0, inf], (0.0, inf], (-inf, 0.0], (-inf, 0.0], ..., (-inf, 0.0], (-inf, 0.0], (-inf, 0.0], (0.0, inf], (0.0, inf]]
Length: 20
Categories (2, interval[float64]): [(-inf, 0.0] < (0.0, inf]]

Function application

To apply your own or another library’s functions to pandas objects,you should be aware of the three methods below. The appropriatemethod to use depends on whether your function expects to operateon an entire DataFrame or Series, row- or column-wise, or elementwise.

• Tablewise Function Application: pipe()
• Row or Column-wise Function Application: apply()
• Aggregation API: agg() and transform()
• Applying Elementwise Functions: applymap()

Tablewise function application

DataFrames and Series can of course just be passed into functions.However, if the function needs to be called in a chain, consider using the pipe() method.Compare the following

# f, g, and h are functions taking and returning ``DataFrames``
>>> f(g(h(df), arg1=1), arg2=2, arg3=3)

with the equivalent

>>> (df.pipe(h)
...    .pipe(g, arg1=1)
...    .pipe(f, arg2=2, arg3=3))

Pandas encourages the second style, which is known as method chaining.pipe makes it easy to use your own or another library’s functionsin method chains, alongside pandas’ methods.

In the example above, the functions f, g, and h each expected the DataFrame as the first positional argument.What if the function you wish to apply takes its data as, say, the second argument?In this case, provide pipe with a tuple of (callable, data_keyword)..pipe will route the DataFrame to the argument specified in the tuple.

For example, we can fit a regression using statsmodels. Their API expects a formula first and a DataFrame as the second argument, data. We pass in the function, keyword pair (sm.ols, 'data') to pipe:

In [138]: import statsmodels.formula.api as sm
 
In [139]: bb = pd.read_csv('data/baseball.csv', index_col='id')
 
In [140]: (bb.query('h > 0')
   .....:    .assign(ln_h=lambda df: np.log(df.h))
   .....:    .pipe((sm.ols, 'data'), 'hr ~ ln_h + year + g + C(lg)')
   .....:    .fit()
   .....:    .summary()
   .....:  )
   .....: 
Out[140]: 
<class 'statsmodels.iolib.summary.Summary'>
"""
                            OLS Regression Results                            
==============================================================================
Dep. Variable:                     hr   R-squared:                       0.685
Model:                            OLS   Adj. R-squared:                  0.665
Method:                 Least Squares   F-statistic:                     34.28
Date:                Sat, 09 Nov 2019   Prob (F-statistic):           3.48e-15
Time:                        19:46:29   Log-Likelihood:                -205.92
No. Observations:                  68   AIC:                             421.8
Df Residuals:                      63   BIC:                             432.9
Df Model:                           4                                         
Covariance Type:            nonrobust                                         
===============================================================================
                  coef    std err          t      P>|t|      [0.025      0.975]
-------------------------------------------------------------------------------
Intercept   -8484.7720   4664.146     -1.819      0.074   -1.78e+04     835.780
C(lg)[T.NL]    -2.2736      1.325     -1.716      0.091      -4.922       0.375
ln_h           -1.3542      0.875     -1.547      0.127      -3.103       0.395
year            4.2277      2.324      1.819      0.074      -0.417       8.872
g               0.1841      0.029      6.258      0.000       0.125       0.243
==============================================================================
Omnibus:                       10.875   Durbin-Watson:                   1.999
Prob(Omnibus):                  0.004   Jarque-Bera (JB):               17.298
Skew:                           0.537   Prob(JB):                     0.000175
Kurtosis:                       5.225   Cond. No.                     1.49e+07
==============================================================================
 
Warnings:
[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.
[2] The condition number is large, 1.49e+07. This might indicate that there are
strong multicollinearity or other numerical problems.
"""

The pipe method is inspired by unix pipes and more recently dplyr and magrittr, whichhave introduced the popular (%>%) (read pipe) operator for R.The implementation of pipe here is quite clean and feels right at home in python.We encourage you to view the source code of pipe().

Row or column-wise function application

Arbitrary functions can be applied along the axes of a DataFrameusing the apply() method, which, like the descriptivestatistics methods, takes an optional axis argument:

In [141]: df.apply(np.mean)
Out[141]: 
one      0.811094
two      1.360588
three    0.187958
dtype: float64
 
In [142]: df.apply(np.mean, axis=1)
Out[142]: 
a    1.583749
b    0.734929
c    1.133683
d   -0.166914
dtype: float64
 
In [143]: df.apply(lambda x: x.max() - x.min())
Out[143]: 
one      1.051928
two      1.632779
three    1.840607
dtype: float64
 
In [144]: df.apply(np.cumsum)
Out[144]: 
        one       two     three
a  1.394981  1.772517       NaN
b  1.738035  3.684640 -0.050390
c  2.433281  5.163008  1.177045
d       NaN  5.442353  0.563873
 
In [145]: df.apply(np.exp)
Out[145]: 
        one       two     three
a  4.034899  5.885648       NaN
b  1.409244  6.767440  0.950858
c  2.004201  4.385785  3.412466
d       NaN  1.322262  0.541630

The apply() method will also dispatch on a string method name.

In [146]: df.apply('mean')
Out[146]: 
one      0.811094
two      1.360588
three    0.187958
dtype: float64
 
In [147]: df.apply('mean', axis=1)
Out[147]: 
a    1.583749
b    0.734929
c    1.133683
d   -0.166914
dtype: float64

The return type of the function passed to apply() affects thetype of the final output from DataFrame.apply for the default behaviour:

• If the applied function returns a Series, the final output is a DataFrame.The columns match the index of the Series returned by the applied function.
• If the applied function returns any other type, the final output is a Series.

This default behaviour can be overridden using the result_type, whichaccepts three options: reduce, broadcast, and expand.These will determine how list-likes return values expand (or not) to a DataFrame.

apply() combined with some cleverness can be used to answer many questionsabout a data set. For example, suppose we wanted to extract the date where themaximum value for each column occurred:

In [148]: tsdf = pd.DataFrame(np.random.randn(1000, 3), columns=['A', 'B', 'C'],
   .....:                     index=pd.date_range('1/1/2000', periods=1000))
   .....: 
 
In [149]: tsdf.apply(lambda x: x.idxmax())
Out[149]: 
A   2000-08-06
B   2001-01-18
C   2001-07-18
dtype: datetime64[ns]

You may also pass additional arguments and keyword arguments to the apply()method. For instance, consider the following function you would like to apply:

def subtract_and_divide(x, sub, divide=1):
    return (x - sub) / divide

You may then apply this function as follows:

df.apply(subtract_and_divide, args=(5,), divide=3)

Another useful feature is the ability to pass Series methods to carry out someSeries operation on each column or row:

In [150]: tsdf
Out[150]: 
                   A         B         C
2000-01-01 -0.158131 -0.232466  0.321604
2000-01-02 -1.810340 -3.105758  0.433834
2000-01-03 -1.209847 -1.156793 -0.136794
2000-01-04       NaN       NaN       NaN
2000-01-05       NaN       NaN       NaN
2000-01-06       NaN       NaN       NaN
2000-01-07       NaN       NaN       NaN
2000-01-08 -0.653602  0.178875  1.008298
2000-01-09  1.007996  0.462824  0.254472
2000-01-10  0.307473  0.600337  1.643950
 
In [151]: tsdf.apply(pd.Series.interpolate)
Out[151]: 
                   A         B         C
2000-01-01 -0.158131 -0.232466  0.321604
2000-01-02 -1.810340 -3.105758  0.433834
2000-01-03 -1.209847 -1.156793 -0.136794
2000-01-04 -1.098598 -0.889659  0.092225
2000-01-05 -0.987349 -0.622526  0.321243
2000-01-06 -0.876100 -0.355392  0.550262
2000-01-07 -0.764851 -0.088259  0.779280
2000-01-08 -0.653602  0.178875  1.008298
2000-01-09  1.007996  0.462824  0.254472
2000-01-10  0.307473  0.600337  1.643950