Data aggregation and group operations in pandas

 

Data aggregation and group operations in pandas

After loading,merging and preparing a dataset,you may need to compute group statistics or possibly pivot tables for reporting or visualization purpose.pandas provides a flexible groupby interface,enabling you to slice,dice and summarize datasets in a natural way.

import pandas as pd

import numpy as np

import matplotlib.pyplot as plt

Groupby mechanics

split-apply-combine:
In the first stage of the process,data contained in a pandas object,whether a Series,DataFrame,or otherwise,is split into groups based on one or more keys that you provide.The splitting is performed on a particular axis off an object.For example,a DataFrame can be grouped on its rows(axis=0) or its columns(axis=1).
Once the first step is done, a function is applied to each group,producing a new value.
Finally,the results of all those function applications are combined into a result object.

help(pd.Series.groupby)

Help on function groupby in module pandas.core.generic:

groupby(self, by=None, axis=0, level=None, as_index=True, sort=True, group_keys=True, squeeze=False, **kwargs)
    Group series using mapper (dict or key function, apply given function
    to group, return result as series) or by a series of columns.
    
    Parameters
    ----------
    by : mapping function / list of functions, dict, Series, or tuple /
        list of column names.
        Called on each element of the object index to determine the groups.
        If a dict or Series is passed, the Series or dict VALUES will be
        used to determine the groups
    axis : int, default 0
    level : int, level name, or sequence of such, default None
        If the axis is a MultiIndex (hierarchical), group by a particular
        level or levels
    as_index : boolean, default True
        For aggregated output, return object with group labels as the
        index. Only relevant for DataFrame input. as_index=False is
        effectively "SQL-style" grouped output
    sort : boolean, default True
        Sort group keys. Get better performance by turning this off.
        Note this does not influence the order of observations within each
        group.  groupby preserves the order of rows within each group.
    group_keys : boolean, default True
        When calling apply, add group keys to index to identify pieces
    squeeze : boolean, default False
        reduce the dimensionality of the return type if possible,
        otherwise return a consistent type
    
    Examples
    --------
    DataFrame results
    
    >>> data.groupby(func, axis=0).mean()
    >>> data.groupby(['col1', 'col2'])['col3'].mean()
    
    DataFrame with hierarchical index
    
    >>> data.groupby(['col1', 'col2']).mean()
    
    Returns
    -------
    GroupBy object

df=pd.DataFrame({'key1':['a','a','b','b','a'],
                'key2':['one','two','one','two','one'],
                'data1':np.random.randn(5),
                'data2':np.random.randn(5)});df
data1 data2 key1 key2
0 -0.288087 -1.355619 a one
1 0.804061 0.654039 a two
2 -1.672009 1.780028 b one
3 0.444115 0.988676 b two
4 -1.110942 0.581883 a one
  • The first method:
    Suppose you wanted to compute the mean of the data column using the labels from key1.There are a number of ways to do this.One is to access data1 and call groupby with the column(a Series) at key1:
grouped=df['data1'].groupby(df['key1'])

help(grouped)

Help on SeriesGroupBy in module pandas.core.groupby object:

class SeriesGroupBy(GroupBy)
 |  Class for grouping and aggregating relational data. See aggregate,
 |  transform, and apply functions on this object.
 |  
 |  It's easiest to use obj.groupby(...) to use GroupBy, but you can also do:
 |  
 |  ::
 |  
 |      grouped = groupby(obj, ...)
 |  
 |  Parameters
 |  ----------
 |  obj : pandas object
 |  axis : int, default 0
 |  level : int, default None
 |      Level of MultiIndex
 |  groupings : list of Grouping objects
 |      Most users should ignore this
 |  exclusions : array-like, optional
 |      List of columns to exclude
 |  name : string
 |      Most users should ignore this
 |  
 |  Notes
 |  -----
 |  After grouping, see aggregate, apply, and transform functions. Here are
 |  some other brief notes about usage. When grouping by multiple groups, the
 |  result index will be a MultiIndex (hierarchical) by default.
 |  
 |  Iteration produces (key, group) tuples, i.e. chunking the data by group. So
 |  you can write code like:
 |  
 |  ::
 |  
 |      grouped = obj.groupby(keys, axis=axis)
 |      for key, group in grouped:
 |          # do something with the data
 |  
 |  Function calls on GroupBy, if not specially implemented, "dispatch" to the
 |  grouped data. So if you group a DataFrame and wish to invoke the std()
 |  method on each group, you can simply do:
 |  
 |  ::
 |  
 |      df.groupby(mapper).std()
 |  
 |  rather than
 |  
 |  ::
 |  
 |      df.groupby(mapper).aggregate(np.std)
 |  
 |  You can pass arguments to these "wrapped" functions, too.
 |  
 |  See the online documentation for full exposition on these topics and much
 |  more
 |  
 |  Returns
 |  -------
 |  **Attributes**
 |  groups : dict
 |      {group name -> group labels}
 |  len(grouped) : int
 |      Number of groups
 |  
 |  Method resolution order:
 |      SeriesGroupBy
 |      GroupBy
 |      _GroupBy
 |      pandas.core.base.PandasObject
 |      pandas.core.base.StringMixin
 |      pandas.core.base.SelectionMixin
 |      builtins.object
 |  
 |  Methods defined here:
 |  
 |  agg = aggregate(self, func_or_funcs, *args, **kwargs)
 |  
 |  aggregate(self, func_or_funcs, *args, **kwargs)
 |      Apply aggregation function or functions to groups, yielding most likely
 |      Series but in some cases DataFrame depending on the output of the
 |      aggregation function
 |      
 |      Parameters
 |      ----------
 |      func_or_funcs : function or list / dict of functions
 |          List/dict of functions will produce DataFrame with column names
 |          determined by the function names themselves (list) or the keys in
 |          the dict
 |      
 |      Notes
 |      -----
 |      agg is an alias for aggregate. Use it.
 |      
 |      Examples
 |      --------
 |      >>> series
 |      bar    1.0
 |      baz    2.0
 |      qot    3.0
 |      qux    4.0
 |      
 |      >>> mapper = lambda x: x[0] # first letter
 |      >>> grouped = series.groupby(mapper)
 |      
 |      >>> grouped.aggregate(np.sum)
 |      b    3.0
 |      q    7.0
 |      
 |      >>> grouped.aggregate([np.sum, np.mean, np.std])
 |         mean  std  sum
 |      b  1.5   0.5  3
 |      q  3.5   0.5  7
 |      
 |      >>> grouped.agg({'result' : lambda x: x.mean() / x.std(),
 |      ...              'total' : np.sum})
 |         result  total
 |      b  2.121   3
 |      q  4.95    7
 |      
 |      See also
 |      --------
 |      apply, transform
 |      
 |      Returns
 |      -------
 |      Series or DataFrame
 |  
 |  count(self)
 |      Compute count of group, excluding missing values
 |  
 |  filter(self, func, dropna=True, *args, **kwargs)
 |      Return a copy of a Series excluding elements from groups that
 |      do not satisfy the boolean criterion specified by func.
 |      
 |      Parameters
 |      ----------
 |      func : function
 |          To apply to each group. Should return True or False.
 |      dropna : Drop groups that do not pass the filter. True by default;
 |          if False, groups that evaluate False are filled with NaNs.
 |      
 |      Examples
 |      --------
 |      >>> grouped.filter(lambda x: x.mean() > 0)
 |      
 |      Returns
 |      -------
 |      filtered : Series
 |  
 |  nlargest(self, n=5, keep='first')
 |      Return the largest `n` elements.
 |      
 |      Parameters
 |      ----------
 |      n : int
 |          Return this many descending sorted values
 |      keep : {'first', 'last', False}, default 'first'
 |          Where there are duplicate values:
 |          - ``first`` : take the first occurrence.
 |          - ``last`` : take the last occurrence.
 |      take_last : deprecated
 |      
 |      Returns
 |      -------
 |      top_n : Series
 |          The n largest values in the Series, in sorted order
 |      
 |      Notes
 |      -----
 |      Faster than ``.sort_values(ascending=False).head(n)`` for small `n`
 |      relative to the size of the ``Series`` object.
 |      
 |      See Also
 |      --------
 |      Series.nsmallest
 |      
 |      Examples
 |      --------
 |      >>> import pandas as pd
 |      >>> import numpy as np
 |      >>> s = pd.Series(np.random.randn(1e6))
 |      >>> s.nlargest(10)  # only sorts up to the N requested
 |  
 |  nsmallest(self, n=5, keep='first')
 |      Return the smallest `n` elements.
 |      
 |      Parameters
 |      ----------
 |      n : int
 |          Return this many ascending sorted values
 |      keep : {'first', 'last', False}, default 'first'
 |          Where there are duplicate values:
 |          - ``first`` : take the first occurrence.
 |          - ``last`` : take the last occurrence.
 |      take_last : deprecated
 |      
 |      Returns
 |      -------
 |      bottom_n : Series
 |          The n smallest values in the Series, in sorted order
 |      
 |      Notes
 |      -----
 |      Faster than ``.sort_values().head(n)`` for small `n` relative to
 |      the size of the ``Series`` object.
 |      
 |      See Also
 |      --------
 |      Series.nlargest
 |      
 |      Examples
 |      --------
 |      >>> import pandas as pd
 |      >>> import numpy as np
 |      >>> s = pd.Series(np.random.randn(1e6))
 |      >>> s.nsmallest(10)  # only sorts up to the N requested
 |  
 |  nunique(self, dropna=True)
 |      Returns number of unique elements in the group
 |  
 |  transform(self, func, *args, **kwargs)
 |      Call function producing a like-indexed Series on each group and return
 |      a Series with the transformed values
 |      
 |      Parameters
 |      ----------
 |      func : function
 |          To apply to each group. Should return a Series with the same index
 |      
 |      Examples
 |      --------
 |      >>> grouped.transform(lambda x: (x - x.mean()) / x.std())
 |      
 |      Returns
 |      -------
 |      transformed : Series
 |  
 |  value_counts(self, normalize=False, sort=True, ascending=False, bins=None, dropna=True)
 |  
 |  ----------------------------------------------------------------------
 |  Data descriptors defined here:
 |  
 |  all
 |      
 |      Return whether all elements are True over requested axis
 |      
 |      Parameters
 |      ----------
 |      axis : {index (0)}
 |      skipna : boolean, default True
 |          Exclude NA/null values. If an entire row/column is NA, the result
 |          will be NA
 |      level : int or level name, default None
 |          If the axis is a MultiIndex (hierarchical), count along a
 |          particular level, collapsing into a scalar
 |      bool_only : boolean, default None
 |          Include only boolean columns. If None, will attempt to use everything,
 |          then use only boolean data. Not implemented for Series.
 |      
 |      Returns
 |      -------
 |      all : scalar or Series (if level specified)
 |  
 |  any
 |      
 |      Return whether any element is True over requested axis
 |      
 |      Parameters
 |      ----------
 |      axis : {index (0)}
 |      skipna : boolean, default True
 |          Exclude NA/null values. If an entire row/column is NA, the result
 |          will be NA
 |      level : int or level name, default None
 |          If the axis is a MultiIndex (hierarchical), count along a
 |          particular level, collapsing into a scalar
 |      bool_only : boolean, default None
 |          Include only boolean columns. If None, will attempt to use everything,
 |          then use only boolean data. Not implemented for Series.
 |      
 |      Returns
 |      -------
 |      any : scalar or Series (if level specified)
 |  
 |  corr
 |      Compute correlation with `other` Series, excluding missing values
 |      
 |      Parameters
 |      ----------
 |      other : Series
 |      method : {'pearson', 'kendall', 'spearman'}
 |          * pearson : standard correlation coefficient
 |          * kendall : Kendall Tau correlation coefficient
 |          * spearman : Spearman rank correlation
 |      min_periods : int, optional
 |          Minimum number of observations needed to have a valid result
 |      
 |      
 |      Returns
 |      -------
 |      correlation : float
 |  
 |  cov
 |      Compute covariance with Series, excluding missing values
 |      
 |      Parameters
 |      ----------
 |      other : Series
 |      min_periods : int, optional
 |          Minimum number of observations needed to have a valid result
 |      
 |      Returns
 |      -------
 |      covariance : float
 |      
 |      Normalized by N-1 (unbiased estimator).
 |  
 |  cummax
 |          Return cumulative max over requested axis.
 |      
 |      Parameters
 |      ----------
 |      axis : {index (0)}
 |      skipna : boolean, default True
 |          Exclude NA/null values. If an entire row/column is NA, the result
 |          will be NA
 |      
 |      Returns
 |      -------
 |      cummax : scalar
 |  
 |  cummin
 |          Return cumulative minimum over requested axis.
 |      
 |      Parameters
 |      ----------
 |      axis : {index (0)}
 |      skipna : boolean, default True
 |          Exclude NA/null values. If an entire row/column is NA, the result
 |          will be NA
 |      
 |      Returns
 |      -------
 |      cummin : scalar
 |  
 |  describe
 |      Generate various summary statistics, excluding NaN values.
 |      
 |      Parameters
 |      ----------
 |      percentiles : array-like, optional
 |          The percentiles to include in the output. Should all
 |          be in the interval [0, 1]. By default `percentiles` is
 |          [.25, .5, .75], returning the 25th, 50th, and 75th percentiles.
 |      include, exclude : list-like, 'all', or None (default)
 |          Specify the form of the returned result. Either:
 |      
 |          - None to both (default). The result will include only
 |            numeric-typed columns or, if none are, only categorical columns.
 |          - A list of dtypes or strings to be included/excluded.
 |            To select all numeric types use numpy numpy.number. To select
 |            categorical objects use type object. See also the select_dtypes
 |            documentation. eg. df.describe(include=['O'])
 |          - If include is the string 'all', the output column-set will
 |            match the input one.
 |      
 |      Returns
 |      -------
 |      summary: NDFrame of summary statistics
 |      
 |      Notes
 |      -----
 |      The output DataFrame index depends on the requested dtypes:
 |      
 |      For numeric dtypes, it will include: count, mean, std, min,
 |      max, and lower, 50, and upper percentiles.
 |      
 |      For object dtypes (e.g. timestamps or strings), the index
 |      will include the count, unique, most common, and frequency of the
 |      most common. Timestamps also include the first and last items.
 |      
 |      For mixed dtypes, the index will be the union of the corresponding
 |      output types. Non-applicable entries will be filled with NaN.
 |      Note that mixed-dtype outputs can only be returned from mixed-dtype
 |      inputs and appropriate use of the include/exclude arguments.
 |      
 |      If multiple values have the highest count, then the
 |      `count` and `most common` pair will be arbitrarily chosen from
 |      among those with the highest count.
 |      
 |      The include, exclude arguments are ignored for Series.
 |      
 |      See Also
 |      --------
 |      DataFrame.select_dtypes
 |  
 |  diff
 |      1st discrete difference of object
 |      
 |      Parameters
 |      ----------
 |      periods : int, default 1
 |          Periods to shift for forming difference
 |      
 |      Returns
 |      -------
 |      diffed : Series
 |  
 |  dtype
 |      return the dtype object of the underlying data
 |  
 |  fillna
 |      Fill NA/NaN values using the specified method
 |      
 |      Parameters
 |      ----------
 |      value : scalar, dict, Series, or DataFrame
 |          Value to use to fill holes (e.g. 0), alternately a
 |          dict/Series/DataFrame of values specifying which value to use for
 |          each index (for a Series) or column (for a DataFrame). (values not
 |          in the dict/Series/DataFrame will not be filled). This value cannot
 |          be a list.
 |      method : {'backfill', 'bfill', 'pad', 'ffill', None}, default None
 |          Method to use for filling holes in reindexed Series
 |          pad / ffill: propagate last valid observation forward to next valid
 |          backfill / bfill: use NEXT valid observation to fill gap
 |      axis : {0, 'index'}
 |      inplace : boolean, default False
 |          If True, fill in place. Note: this will modify any
 |          other views on this object, (e.g. a no-copy slice for a column in a
 |          DataFrame).
 |      limit : int, default None
 |          If method is specified, this is the maximum number of consecutive
 |          NaN values to forward/backward fill. In other words, if there is
 |          a gap with more than this number of consecutive NaNs, it will only
 |          be partially filled. If method is not specified, this is the
 |          maximum number of entries along the entire axis where NaNs will be
 |          filled.
 |      downcast : dict, default is None
 |          a dict of item->dtype of what to downcast if possible,
 |          or the string 'infer' which will try to downcast to an appropriate
 |          equal type (e.g. float64 to int64 if possible)
 |      
 |      See Also
 |      --------
 |      reindex, asfreq
 |      
 |      Returns
 |      -------
 |      filled : Series
 |  
 |  hist
 |      Draw histogram of the input series using matplotlib
 |      
 |      Parameters
 |      ----------
 |      by : object, optional
 |          If passed, then used to form histograms for separate groups
 |      ax : matplotlib axis object
 |          If not passed, uses gca()
 |      grid : boolean, default True
 |          Whether to show axis grid lines
 |      xlabelsize : int, default None
 |          If specified changes the x-axis label size
 |      xrot : float, default None
 |          rotation of x axis labels
 |      ylabelsize : int, default None
 |          If specified changes the y-axis label size
 |      yrot : float, default None
 |          rotation of y axis labels
 |      figsize : tuple, default None
 |          figure size in inches by default
 |      bins: integer, default 10
 |          Number of histogram bins to be used
 |      kwds : keywords
 |          To be passed to the actual plotting function
 |      
 |      Notes
 |      -----
 |      See matplotlib documentation online for more on this
 |  
 |  idxmax
 |      Index of first occurrence of maximum of values.
 |      
 |      Parameters
 |      ----------
 |      skipna : boolean, default True
 |          Exclude NA/null values
 |      
 |      Returns
 |      -------
 |      idxmax : Index of maximum of values
 |      
 |      Notes
 |      -----
 |      This method is the Series version of ``ndarray.argmax``.
 |      
 |      See Also
 |      --------
 |      DataFrame.idxmax
 |      numpy.ndarray.argmax
 |  
 |  idxmin
 |      Index of first occurrence of minimum of values.
 |      
 |      Parameters
 |      ----------
 |      skipna : boolean, default True
 |          Exclude NA/null values
 |      
 |      Returns
 |      -------
 |      idxmin : Index of minimum of values
 |      
 |      Notes
 |      -----
 |      This method is the Series version of ``ndarray.argmin``.
 |      
 |      See Also
 |      --------
 |      DataFrame.idxmin
 |      numpy.ndarray.argmin
 |  
 |  mad
 |      
 |      Return the mean absolute deviation of the values for the requested axis
 |      
 |      Parameters
 |      ----------
 |      axis : {index (0)}
 |      skipna : boolean, default True
 |          Exclude NA/null values. If an entire row/column is NA, the result
 |          will be NA
 |      level : int or level name, default None
 |          If the axis is a MultiIndex (hierarchical), count along a
 |          particular level, collapsing into a scalar
 |      numeric_only : boolean, default None
 |          Include only float, int, boolean columns. If None, will attempt to use
 |          everything, then use only numeric data. Not implemented for Series.
 |      
 |      Returns
 |      -------
 |      mad : scalar or Series (if level specified)
 |  
 |  name
 |      since we are a series, we by definition only have
 |      a single name, but may be the result of a selection or
 |      the name of our object
 |  
 |  pct_change
 |      Percent change over given number of periods.
 |      
 |      Parameters
 |      ----------
 |      periods : int, default 1
 |          Periods to shift for forming percent change
 |      fill_method : str, default 'pad'
 |          How to handle NAs before computing percent changes
 |      limit : int, default None
 |          The number of consecutive NAs to fill before stopping
 |      freq : DateOffset, timedelta, or offset alias string, optional
 |          Increment to use from time series API (e.g. 'M' or BDay())
 |      
 |      Returns
 |      -------
 |      chg : NDFrame
 |      
 |      Notes
 |      -----
 |      
 |      By default, the percentage change is calculated along the stat
 |      axis: 0, or ``Index``, for ``DataFrame`` and 1, or ``minor`` for
 |      ``Panel``. You can change this with the ``axis`` keyword argument.
 |  
 |  quantile
 |      Return value at the given quantile, a la numpy.percentile.
 |      
 |      Parameters
 |      ----------
 |      q : float or array-like, default 0.5 (50% quantile)
 |          0 <= q <= 1, the quantile(s) to compute
 |      interpolation : {'linear', 'lower', 'higher', 'midpoint', 'nearest'}
 |          .. versionadded:: 0.18.0
 |      
 |          This optional parameter specifies the interpolation method to use,
 |          when the desired quantile lies between two data points `i` and `j`:
 |      
 |              * linear: `i + (j - i) * fraction`, where `fraction` is the
 |                fractional part of the index surrounded by `i` and `j`.
 |              * lower: `i`.
 |              * higher: `j`.
 |              * nearest: `i` or `j` whichever is nearest.
 |              * midpoint: (`i` + `j`) / 2.
 |      
 |      Returns
 |      -------
 |      quantile : float or Series
 |          if ``q`` is an array, a Series will be returned where the
 |          index is ``q`` and the values are the quantiles.
 |      
 |      Examples
 |      --------
 |      >>> s = Series([1, 2, 3, 4])
 |      >>> s.quantile(.5)
 |      2.5
 |      >>> s.quantile([.25, .5, .75])
 |      0.25    1.75
 |      0.50    2.50
 |      0.75    3.25
 |      dtype: float64
 |  
 |  rank
 |      Compute numerical data ranks (1 through n) along axis. Equal values are
 |      assigned a rank that is the average of the ranks of those values
 |      
 |      Parameters
 |      ----------
 |      axis: {0 or 'index', 1 or 'columns'}, default 0
 |          index to direct ranking
 |      method : {'average', 'min', 'max', 'first', 'dense'}
 |          * average: average rank of group
 |          * min: lowest rank in group
 |          * max: highest rank in group
 |          * first: ranks assigned in order they appear in the array
 |          * dense: like 'min', but rank always increases by 1 between groups
 |      numeric_only : boolean, default None
 |          Include only float, int, boolean data. Valid only for DataFrame or
 |          Panel objects
 |      na_option : {'keep', 'top', 'bottom'}
 |          * keep: leave NA values where they are
 |          * top: smallest rank if ascending
 |          * bottom: smallest rank if descending
 |      ascending : boolean, default True
 |          False for ranks by high (1) to low (N)
 |      pct : boolean, default False
 |          Computes percentage rank of data
 |      
 |      Returns
 |      -------
 |      ranks : same type as caller
 |  
 |  skew
 |      
 |      Return unbiased skew over requested axis
 |      Normalized by N-1
 |      
 |      Parameters
 |      ----------
 |      axis : {index (0)}
 |      skipna : boolean, default True
 |          Exclude NA/null values. If an entire row/column is NA, the result
 |          will be NA
 |      level : int or level name, default None
 |          If the axis is a MultiIndex (hierarchical), count along a
 |          particular level, collapsing into a scalar
 |      numeric_only : boolean, default None
 |          Include only float, int, boolean columns. If None, will attempt to use
 |          everything, then use only numeric data. Not implemented for Series.
 |      
 |      Returns
 |      -------
 |      skew : scalar or Series (if level specified)
 |  
 |  take
 |      return Series corresponding to requested indices
 |      
 |      Parameters
 |      ----------
 |      indices : list / array of ints
 |      convert : translate negative to positive indices (default)
 |      
 |      Returns
 |      -------
 |      taken : Series
 |      
 |      See also
 |      --------
 |      numpy.ndarray.take
 |  
 |  tshift
 |      Shift the time index, using the index's frequency if available.
 |      
 |      Parameters
 |      ----------
 |      periods : int
 |          Number of periods to move, can be positive or negative
 |      freq : DateOffset, timedelta, or time rule string, default None
 |          Increment to use from the tseries module or time rule (e.g. 'EOM')
 |      axis : int or basestring
 |          Corresponds to the axis that contains the Index
 |      
 |      Notes
 |      -----
 |      If freq is not specified then tries to use the freq or inferred_freq
 |      attributes of the index. If neither of those attributes exist, a
 |      ValueError is thrown
 |      
 |      Returns
 |      -------
 |      shifted : NDFrame
 |  
 |  unique
 |      Return np.ndarray of unique values in the object.
 |      Significantly faster than numpy.unique. Includes NA values.
 |      The order of the original is preserved.
 |      
 |      Returns
 |      -------
 |      uniques : np.ndarray
 |  
 |  ----------------------------------------------------------------------
 |  Methods inherited from GroupBy:
 |  
 |  backfill(self, limit=None)
 |      Backward fill the values
 |      
 |      Parameters
 |      ----------
 |      limit : integer, optional
 |          limit of how many values to fill
 |      
 |      See Also
 |      --------
 |      Series.fillna
 |      DataFrame.fillna
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  bfill = backfill(self, limit=None)
 |  
 |  cumcount(self, ascending=True)
 |      Number each item in each group from 0 to the length of that group - 1.
 |      
 |      Essentially this is equivalent to
 |      
 |      >>> self.apply(lambda x: Series(np.arange(len(x)), x.index))
 |      
 |      Parameters
 |      ----------
 |      ascending : bool, default True
 |          If False, number in reverse, from length of group - 1 to 0.
 |      
 |      Examples
 |      --------
 |      
 |      >>> df = pd.DataFrame([['a'], ['a'], ['a'], ['b'], ['b'], ['a']],
 |      ...                   columns=['A'])
 |      >>> df
 |         A
 |      0  a
 |      1  a
 |      2  a
 |      3  b
 |      4  b
 |      5  a
 |      >>> df.groupby('A').cumcount()
 |      0    0
 |      1    1
 |      2    2
 |      3    0
 |      4    1
 |      5    3
 |      dtype: int64
 |      >>> df.groupby('A').cumcount(ascending=False)
 |      0    3
 |      1    2
 |      2    1
 |      3    1
 |      4    0
 |      5    0
 |      dtype: int64
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  cumprod(self, axis=0, *args, **kwargs)
 |      Cumulative product for each group
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  cumsum(self, axis=0, *args, **kwargs)
 |      Cumulative sum for each group
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  expanding(self, *args, **kwargs)
 |      Return an expanding grouper, providing expanding
 |      functionaility per group
 |      
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  ffill = pad(self, limit=None)
 |  
 |  first = f(self)
 |      Compute first of group values
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  head(self, n=5)
 |      Returns first n rows of each group.
 |      
 |      Essentially equivalent to ``.apply(lambda x: x.head(n))``,
 |      except ignores as_index flag.
 |      
 |      Examples
 |      --------
 |      
 |      >>> df = DataFrame([[1, 2], [1, 4], [5, 6]],
 |                         columns=['A', 'B'])
 |      >>> df.groupby('A', as_index=False).head(1)
 |         A  B
 |      0  1  2
 |      2  5  6
 |      >>> df.groupby('A').head(1)
 |         A  B
 |      0  1  2
 |      2  5  6
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  irow(self, i)
 |      DEPRECATED. Use ``.nth(i)`` instead
 |  
 |  last = f(self)
 |      Compute last of group values
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  max = f(self)
 |      Compute max of group values
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  mean(self, *args, **kwargs)
 |      Compute mean of groups, excluding missing values
 |      
 |      For multiple groupings, the result index will be a MultiIndex
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  median(self)
 |      Compute median of groups, excluding missing values
 |      
 |      For multiple groupings, the result index will be a MultiIndex
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  min = f(self)
 |      Compute min of group values
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  nth(self, n, dropna=None)
 |      Take the nth row from each group if n is an int, or a subset of rows
 |      if n is a list of ints.
 |      
 |      If dropna, will take the nth non-null row, dropna is either
 |      Truthy (if a Series) or 'all', 'any' (if a DataFrame);
 |      this is equivalent to calling dropna(how=dropna) before the
 |      groupby.
 |      
 |      Parameters
 |      ----------
 |      n : int or list of ints
 |          a single nth value for the row or a list of nth values
 |      dropna : None or str, optional
 |          apply the specified dropna operation before counting which row is
 |          the nth row. Needs to be None, 'any' or 'all'
 |      
 |      Examples
 |      --------
 |      
 |      >>> df = pd.DataFrame({'A': [1, 1, 2, 1, 2],
 |      ...                    'B': [np.nan, 2, 3, 4, 5]}, columns=['A', 'B'])
 |      >>> g = df.groupby('A')
 |      >>> g.nth(0)
 |           B
 |      A
 |      1  NaN
 |      2  3.0
 |      >>> g.nth(1)
 |           B
 |      A
 |      1  2.0
 |      2  5.0
 |      >>> g.nth(-1)
 |           B
 |      A
 |      1  4.0
 |      2  5.0
 |      >>> g.nth([0, 1])
 |           B
 |      A
 |      1  NaN
 |      1  2.0
 |      2  3.0
 |      2  5.0
 |      
 |      Specifying ``dropna`` allows count ignoring NaN
 |      
 |      >>> g.nth(0, dropna='any')
 |           B
 |      A
 |      1  2.0
 |      2  3.0
 |      
 |      NaNs denote group exhausted when using dropna
 |      
 |      >>> g.nth(3, dropna='any')
 |          B
 |      A
 |      1 NaN
 |      2 NaN
 |      
 |      Specifying ``as_index=False`` in ``groupby`` keeps the original index.
 |      
 |      >>> df.groupby('A', as_index=False).nth(1)
 |         A    B
 |      1  1  2.0
 |      4  2  5.0
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  ohlc(self)
 |      Compute sum of values, excluding missing values
 |      For multiple groupings, the result index will be a MultiIndex
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  pad(self, limit=None)
 |      Forward fill the values
 |      
 |      Parameters
 |      ----------
 |      limit : integer, optional
 |          limit of how many values to fill
 |      
 |      See Also
 |      --------
 |      Series.fillna
 |      DataFrame.fillna
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  prod = f(self)
 |      Compute prod of group values
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  resample(self, rule, *args, **kwargs)
 |      Provide resampling when using a TimeGrouper
 |      Return a new grouper with our resampler appended
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  rolling(self, *args, **kwargs)
 |      Return a rolling grouper, providing rolling
 |      functionaility per group
 |      
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  sem(self, ddof=1)
 |      Compute standard error of the mean of groups, excluding missing values
 |      
 |      For multiple groupings, the result index will be a MultiIndex
 |      
 |      Parameters
 |      ----------
 |      ddof : integer, default 1
 |          degrees of freedom
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  shift(self, periods=1, freq=None, axis=0)
 |      Shift each group by periods observations
 |      
 |      Parameters
 |      ----------
 |      periods : integer, default 1
 |          number of periods to shift
 |      freq : frequency string
 |      axis : axis to shift, default 0
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  size(self)
 |      Compute group sizes
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  std(self, ddof=1, *args, **kwargs)
 |      Compute standard deviation of groups, excluding missing values
 |      
 |      For multiple groupings, the result index will be a MultiIndex
 |      
 |      Parameters
 |      ----------
 |      ddof : integer, default 1
 |          degrees of freedom
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  sum = f(self)
 |      Compute sum of group values
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  tail(self, n=5)
 |      Returns last n rows of each group
 |      
 |      Essentially equivalent to ``.apply(lambda x: x.tail(n))``,
 |      except ignores as_index flag.
 |      
 |      Examples
 |      --------
 |      
 |      >>> df = DataFrame([['a', 1], ['a', 2], ['b', 1], ['b', 2]],
 |                         columns=['A', 'B'])
 |      >>> df.groupby('A').tail(1)
 |         A  B
 |      1  a  2
 |      3  b  2
 |      >>> df.groupby('A').head(1)
 |         A  B
 |      0  a  1
 |      2  b  1
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  var(self, ddof=1, *args, **kwargs)
 |      Compute variance of groups, excluding missing values
 |      
 |      For multiple groupings, the result index will be a MultiIndex
 |      
 |      Parameters
 |      ----------
 |      ddof : integer, default 1
 |          degrees of freedom
 |      
 |      
 |      See also
 |      --------
 |      pandas.Series.groupby
 |      pandas.DataFrame.groupby
 |      pandas.Panel.groupby
 |  
 |  ----------------------------------------------------------------------
 |  Methods inherited from _GroupBy:
 |  
 |  __getattr__(self, attr)
 |  
 |  __init__(self, obj, keys=None, axis=0, level=None, grouper=None, exclusions=None, selection=None, as_index=True, sort=True, group_keys=True, squeeze=False, **kwargs)
 |      Initialize self.  See help(type(self)) for accurate signature.
 |  
 |  __iter__(self)
 |      Groupby iterator
 |      
 |      Returns
 |      -------
 |      Generator yielding sequence of (name, subsetted object)
 |      for each group
 |  
 |  __len__(self)
 |  
 |  __unicode__(self)
 |      Return a string representation for a particular object.
 |      
 |      Invoked by unicode(obj) in py2 only. Yields a Unicode String in both
 |      py2/py3.
 |  
 |  apply(self, func, *args, **kwargs)
 |      Apply function and combine results together in an intelligent way. The
 |      split-apply-combine combination rules attempt to be as common sense
 |      based as possible. For example:
 |      
 |      case 1:
 |      group DataFrame
 |      apply aggregation function (f(chunk) -> Series)
 |      yield DataFrame, with group axis having group labels
 |      
 |      case 2:
 |      group DataFrame
 |      apply transform function ((f(chunk) -> DataFrame with same indexes)
 |      yield DataFrame with resulting chunks glued together
 |      
 |      case 3:
 |      group Series
 |      apply function with f(chunk) -> DataFrame
 |      yield DataFrame with result of chunks glued together
 |      
 |      Parameters
 |      ----------
 |      func : function
 |      
 |      Notes
 |      -----
 |      See online documentation for full exposition on how to use apply.
 |      
 |      In the current implementation apply calls func twice on the
 |      first group to decide whether it can take a fast or slow code
 |      path. This can lead to unexpected behavior if func has
 |      side-effects, as they will take effect twice for the first
 |      group.
 |      
 |      
 |      See also
 |      --------
 |      aggregate, transform
 |  
 |  get_group(self, name, obj=None)
 |      Constructs NDFrame from group with provided name
 |      
 |      Parameters
 |      ----------
 |      name : object
 |          the name of the group to get as a DataFrame
 |      obj : NDFrame, default None
 |          the NDFrame to take the DataFrame out of.  If
 |          it is None, the object groupby was called on will
 |          be used
 |      
 |      Returns
 |      -------
 |      group : type of obj
 |  
 |  ----------------------------------------------------------------------
 |  Data descriptors inherited from _GroupBy:
 |  
 |  groups
 |      dict {group name -> group labels}
 |  
 |  indices
 |      dict {group name -> group indices}
 |  
 |  ngroups
 |  
 |  plot
 |      Class implementing the .plot attribute for groupby objects
 |  
 |  ----------------------------------------------------------------------
 |  Methods inherited from pandas.core.base.PandasObject:
 |  
 |  __dir__(self)
 |      Provide method name lookup and completion
 |      Only provide 'public' methods
 |  
 |  __sizeof__(self)
 |      Generates the total memory usage for a object that returns
 |      either a value or Series of values
 |  
 |  ----------------------------------------------------------------------
 |  Methods inherited from pandas.core.base.StringMixin:
 |  
 |  __bytes__(self)
 |      Return a string representation for a particular object.
 |      
 |      Invoked by bytes(obj) in py3 only.
 |      Yields a bytestring in both py2/py3.
 |  
 |  __repr__(self)
 |      Return a string representation for a particular object.
 |      
 |      Yields Bytestring in Py2, Unicode String in py3.
 |  
 |  __str__(self)
 |      Return a string representation for a particular Object
 |      
 |      Invoked by str(df) in both py2/py3.
 |      Yields Bytestring in Py2, Unicode String in py3.
 |  
 |  ----------------------------------------------------------------------
 |  Data descriptors inherited from pandas.core.base.StringMixin:
 |  
 |  __dict__
 |      dictionary for instance variables (if defined)
 |  
 |  __weakref__
 |      list of weak references to the object (if defined)
 |  
 |  ----------------------------------------------------------------------
 |  Methods inherited from pandas.core.base.SelectionMixin:
 |  
 |  __getitem__(self, key)
 |  
 |  ----------------------------------------------------------------------
 |  Data descriptors inherited from pandas.core.base.SelectionMixin:
 |  
 |  ndim

grouped.mean()

key1
a   -0.198323
b   -0.613947
Name: data1, dtype: float64

df['data1'].groupby(['a','a','b','b','a']).mean()

a   -0.198323
b   -0.613947
Name: data1, dtype: float64

p=df['data1'];p

0   -0.288087
1    0.804061
2   -1.672009
3    0.444115
4   -1.110942
Name: data1, dtype: float64

p.groupby(df['key1']).mean()

key1
a   -0.198323
b   -0.613947
Name: data1, dtype: float64

The important thing above is that the data(Series) has been aggregated according to the group key,producing a new Series that is now indexed by the unique values in the key1 column.

The result index has the name 'key1' because the DataFrame column df['key1'] did!

If instead we had passed multiple arrays as a list,we'd get something different:

means=df['data1'].groupby([df['key1'],df['key2']]).mean();means

key1  key2
a     one    -0.699514
      two     0.804061
b     one    -1.672009
      two     0.444115
Name: data1, dtype: float64

df['data1'].groupby([df['key2'],df['key1']]).mean()

key2  key1
one   a      -0.699514
      b      -1.672009
two   a       0.804061
      b       0.444115
Name: data1, dtype: float64

Here we grouped the data using two keys, and the resulting Series now has a hierarchical index consisting of the unique pairs of keys observed:

means.unstack()
key2 one two
key1
a -0.699514 0.804061
b -1.672009 0.444115

In this example,the group keys are all Series,though they could be any arrays of the right length:

states=np.array(['Ohio','Califonia','Ohio','Ohio','Califonia'])

years=np.array([2005,2005,2006,2005,2006])

df['data1'].groupby([states,years]).mean()

Califonia  2005    0.804061
           2006   -1.110942
Ohio       2005    0.078014
           2006   -1.672009
Name: data1, dtype: float64
  • The second method:
    Frequently the grouping information is found in the same DataFrame as the data you want to work on.In this case,you can pass column names(whether those are strings,numbers or other python objects) as the group keys:
df.groupby('key1').mean()
data1 data2
key1
a -0.198323 -0.039899
b -0.613947 1.384352 
df.groupby(['key1','key2']).mean()
data1 data2
key1 key2
a one -0.699514 -0.386868
two 0.804061 0.654039
b one -1.672009 1.780028
two 0.444115 0.988676

You may have noticed in the first case of df.groupby('key').mean() that there is no key2 column in the result.**Because df['key2'] is not numeric data,it is said to be a nuisance column,which is therefor excluded from the result.By default,all of teh numeric columns are aggregated,though it is possible to filter down to a subset.

help(df.groupby(['key1','key2']).size)

Help on method size in module pandas.core.groupby:

size() method of pandas.core.groupby.DataFrameGroupBy instance
    Compute group sizes
    
    See also
    --------
    pandas.Series.groupby
    pandas.DataFrame.groupby
    pandas.Panel.groupby

A generally useful Groupby method is size,which returns a Series containing group sizes:

df.groupby(['key1','key2']).size()

key1  key2
a     one     2
      two     1
b     one     1
      two     1
dtype: int64

df
data1 data2 key1 key2
0 -0.288087 -1.355619 a one
1 0.804061 0.654039 a two
2 -1.672009 1.780028 b one
3 0.444115 0.988676 b two
4 -1.110942 0.581883 a one

We can see from above,the number of ('a','one') is 2 indeed...

Iterating over groups

The Groupby object supports iteration,generating a sequence of 2-tuples containning the group name along with the chunk of data.

for name,group in df.groupby('key1'):
    print(name)
    print(group)

a
      data1     data2 key1 key2
0 -0.288087 -1.355619    a  one
1  0.804061  0.654039    a  two
4 -1.110942  0.581883    a  one
b
      data1     data2 key1 key2
2 -1.672009  1.780028    b  one
3  0.444115  0.988676    b  two

In the case of multiple keys,the first element in the tuple will be a tuple of key values.

for (k1,k2),group in df.groupby(['key1','key2']):
    print((k1,k2))
    print(group)

('a', 'one')
      data1     data2 key1 key2
0 -0.288087 -1.355619    a  one
4 -1.110942  0.581883    a  one
('a', 'two')
      data1     data2 key1 key2
1  0.804061  0.654039    a  two
('b', 'one')
      data1     data2 key1 key2
2 -1.672009  1.780028    b  one
('b', 'two')
      data1     data2 key1 key2
3  0.444115  0.988676    b  two

df
data1 data2 key1 key2
0 -0.288087 -1.355619 a one
1 0.804061 0.654039 a two
2 -1.672009 1.780028 b one
3 0.444115 0.988676 b two
4 -1.110942 0.581883 a one

A useful recipe to get the pieces of data through computing a dict of the data pieces as a one-liner:

pieces=dict(list(df.groupby('key1')))

pieces

{'a':       data1     data2 key1 key2
 0 -0.288087 -1.355619    a  one
 1  0.804061  0.654039    a  two
 4 -1.110942  0.581883    a  one, 'b':       data1     data2 key1 key2
 2 -1.672009  1.780028    b  one
 3  0.444115  0.988676    b  two}

pieces['b']
data1 data2 key1 key2
2 -1.672009 1.780028 b one
3 0.444115 0.988676 b two 
list(df.groupby('key1'))

[('a',       data1     data2 key1 key2
  0 -0.288087 -1.355619    a  one
  1  0.804061  0.654039    a  two
  4 -1.110942  0.581883    a  one), ('b',       data1     data2 key1 key2
  2 -1.672009  1.780028    b  one
  3  0.444115  0.988676    b  two)]

dict([('a',1),('b',2)])

{'a': 1, 'b': 2}

dict([('a',1)])

{'a': 1}

dict([(1,2)])

{1: 2}

Above: dict's built-in creation.

df
data1 data2 key1 key2
0 -0.288087 -1.355619 a one
1 0.804061 0.654039 a two
2 -1.672009 1.780028 b one
3 0.444115 0.988676 b two
4 -1.110942 0.581883 a one 
df.dtypes

data1    float64
data2    float64
key1      object
key2      object
dtype: object

grouped=df.groupby(df.dtypes,axis=1)  # notice here,axis=1,df is divided into 2 DataFrame accroding `dtype`.

for dtype,group in grouped:
    print(dtype)
    print(group)

float64
      data1     data2
0 -0.288087 -1.355619
1  0.804061  0.654039
2 -1.672009  1.780028
3  0.444115  0.988676
4 -1.110942  0.581883
object
  key1 key2
0    a  one
1    a  two
2    b  one
3    b  two
4    a  one

pp=dict(list(grouped))

pp

{dtype('float64'):       data1     data2
 0 -0.288087 -1.355619
 1  0.804061  0.654039
 2 -1.672009  1.780028
 3  0.444115  0.988676
 4 -1.110942  0.581883, dtype('O'):   key1 key2
 0    a  one
 1    a  two
 2    b  one
 3    b  two
 4    a  one}

i,j=pp.keys()

pp[i]
data1 data2
0 -0.288087 -1.355619
1 0.804061 0.654039
2 -1.672009 1.780028
3 0.444115 0.988676
4 -1.110942 0.581883 
pp[j]
key1 key2
0 a one
1 a two
2 b one
3 b two
4 a one 
type(i)

numpy.dtype

pp[np.dtype('object')]
key1 key2
0 a one
1 a two
2 b one
3 b two
4 a one

Selecting a column or subset of columns

a=list(df.groupby('key1')['data1']);a

[('a', 0   -0.288087
  1    0.804061
  4   -1.110942
  Name: data1, dtype: float64), ('b', 2   -1.672009
  3    0.444115
  Name: data1, dtype: float64)]

list(df['data1'].groupby(df['key1']))

[('a', 0   -0.288087
  1    0.804061
  4   -1.110942
  Name: data1, dtype: float64), ('b', 2   -1.672009
  3    0.444115
  Name: data1, dtype: float64)]

df
data1 data2 key1 key2
0 -0.288087 -1.355619 a one
1 0.804061 0.654039 a two
2 -1.672009 1.780028 b one
3 0.444115 0.988676 b two
4 -1.110942 0.581883 a one 
df.groupby('key1').size()

key1
a    3
b    2
dtype: int64

a=df.groupby('key1')['data1'];a

<pandas.core.groupby.SeriesGroupBy object at 0x000001DDB5670F60>

b=df.groupby('key1')[['data1']];a

<pandas.core.groupby.SeriesGroupBy object at 0x000001DDB5670F60>

aa=df['data1'].groupby(df['key1']);aa

<pandas.core.groupby.SeriesGroupBy object at 0x000001DDB56707B8>

bb=df[['data1']].groupby(df['key1']);bb

<pandas.core.groupby.DataFrameGroupBy object at 0x000001DDB56703C8>

Indexing a groupby object created from a DataFrame with a column name or array of column names has the efffect of column subsetting for aggregation.

df
data1 data2 key1 key2
0 -0.288087 -1.355619 a one
1 0.804061 0.654039 a two
2 -1.672009 1.780028 b one
3 0.444115 0.988676 b two
4 -1.110942 0.581883 a one 
list(df.groupby(['key1','key2'])['data2'])

[(('a', 'one'), 0   -1.355619
  4    0.581883
  Name: data2, dtype: float64), (('a', 'two'), 1    0.654039
  Name: data2, dtype: float64), (('b', 'one'), 2    1.780028
  Name: data2, dtype: float64), (('b', 'two'), 3    0.988676
  Name: data2, dtype: float64)]

df.groupby(['key1','key2'])[['data2']].mean()
data2
key1 key2
a one -0.386868
two 0.654039
b one 1.780028
two 0.988676 
df[['data1','data2']].mean()

data1   -0.364572
data2    0.529801
dtype: float64

Grouping with Dicts and Series

Grouping information may exist in a form other than an array.

people=pd.DataFrame(np.random.randn(5,5),columns=list('abcde'),index=['Joe','Steve','Wes','Jim','Travis']);people
a b c d e
Joe 0.696631 -0.491001 -0.065796 -1.392325 -0.651280
Steve 0.965853 -1.694954 2.142343 -0.969325 1.129057
Wes -0.145624 -0.548971 -0.893291 2.421140 0.305064
Jim 0.434581 1.189071 0.455694 -0.929888 1.193285
Travis -0.321174 -1.400983 -0.529033 0.037673 1.221623

Now suppose we have a group correspondence for the columns and want to sum together the columns by group.

mapping={'a':'red','b':'red',
        'c':'blue','d':'blue',
        'e':'red','f':'orange'}

by_column=people.groupby(mapping,axis=1)

by_column.sum()
blue red
Joe -1.458121 -0.445651
Steve 1.173019 0.399957
Wes 1.527849 -0.389532
Jim -0.474194 2.816937
Travis -0.491360 -0.500533

The same functionality holds for Series,which can be viewed as a fixed-size mapping.


map_series=pd.Series(mapping);map_series

a       red
b       red
c      blue
d      blue
e       red
f    orange
dtype: object

people.groupby(map_series,axis=1).count()
blue red
Joe 2 3
Steve 2 3
Wes 2 3
Jim 2 3
Travis 2 3 
people.groupby(['red','red','blue','blue','red']).mean()
a b c d e
blue 0.144479 0.320050 -0.218799 0.745626 0.749174
red 0.447103 -1.195646 0.515838 -0.774659 0.566467 
people.groupby(['red','red','blue','blue','red'],axis=1).mean()
blue red
Joe -0.729060 -0.148550
Steve 0.586509 0.133319
Wes 0.763924 -0.129844
Jim -0.237097 0.938979
Travis -0.245680 -0.166844

Grouping with function

Using python functions is a more generic way of defining a group mappingg compared with a dict or Series.Any function passed as a group key will be called once per index value,with the return values being used as the group names.

Suppose you want to group by the length of the names;while you compute an array of string lengths,it's simpler to just pass the len function.

people.groupby(len).sum()
a b c d e
3 0.985588 0.149098 -0.503394 0.098927 0.847068
5 0.965853 -1.694954 2.142343 -0.969325 1.129057
6 -0.321174 -1.400983 -0.529033 0.037673 1.221623

Mixing functions with arrays,dicts,or Series is not a problem as everything gets converted to arrays internaly:

key_list=['one','one','one','two','two']

key_test=[1,1,1,1,1]

people.groupby([len,key_list]).min()
a b c d e
3 one -0.145624 -0.548971 -0.893291 -1.392325 -0.651280
two 0.434581 1.189071 0.455694 -0.929888 1.193285
5 one 0.965853 -1.694954 2.142343 -0.969325 1.129057
6 two -0.321174 -1.400983 -0.529033 0.037673 1.221623 
people.groupby([len,key_test]).min()
a b c d e
3 1 -0.145624 -0.548971 -0.893291 -1.392325 -0.651280
5 1 0.965853 -1.694954 2.142343 -0.969325 1.129057
6 1 -0.321174 -1.400983 -0.529033 0.037673 1.221623

Grouping by Index levels

A final vonvenience for hierarchically indexed datasets is the ability to aggregate using one of the levels of an index.

columns=pd.MultiIndex.from_arrays([['US','US','US','JP','JP'],[1,3,5,1,3,]],names=['City','tenor'])

hier_df=pd.DataFrame(np.random.randn(4,5),columns=columns);hier_df
City US JP
tenor 1 3 5 1 3
0 -1.878462 1.360321 -1.444771 0.883927 0.995222
1 -1.082905 1.061055 -0.938387 -0.391143 1.383284
2 -0.626484 -0.298127 1.144298 0.941223 1.163550
3 -0.272500 -3.441699 0.804467 -0.029275 -0.003555

To group by level,pass the level number or name using level keyword.

hier_df.groupby(level='City',axis=1).count()
City JP US
0 2 3
1 2 3
2 2 3
3 2 3

Data aggregation

Aggregations refer to any data transformation that produces scalar values from arrays.

description=['Number of non-NAN values in the group',
            'Sum of non-NA values',
            'Mean of non-NA values',
            'Arithmetic median off non-NAN values',
            'Unbiased(n-1 denominator)standard deviation and variance',
            'Minimun and maximum of non-NA values',
            'Product of non-NA values',
            'First and last non-NA value']

Tips:Custom aggregation functions are generally much slower than the optimized functions found in above table.This is because there is some extra overhead(function calls,data rearrangement) in constructing the intermeediate chunks.

index=pd.Index(['count','sum','mean','median','std,var','min,max','prod','first,last'],names='Function name')

pd.DataFrame(description,index=index)
0
count Number of non-NAN values in the group
sum Sum of non-NA values
mean Mean of non-NA values
median Arithmetic median off non-NAN values
std,var Unbiased(n-1 denominator)standard deviation an...
min,max Minimun and maximum of non-NA values
prod Product of non-NA values
first,last First and last non-NA value

To use your own aggregation functions,pass any function that aggregates an array to the aggregate or agg method.

grouped=df.groupby('key1')

def peak_to_peak(arr):
    return arr.max()-arr.min()

grouped.agg(peak_to_peak)
data1 data2
key1
a 1.915003 2.009658
b 2.116124 0.791351 
df
data1 data2 key1 key2
0 -0.288087 -1.355619 a one
1 0.804061 0.654039 a two
2 -1.672009 1.780028 b one
3 0.444115 0.988676 b two
4 -1.110942 0.581883 a one 
def length(arr):
    l=0
    for i in arr:
        l+=i**2
    return l**0.5
        

grouped.agg(length)
data1 data2
key1
a 1.401321 1.613710
b 1.729986 2.036168 
grouped.describe()
data1 data2
key1
a count 3.000000 3.000000
mean -0.198323 -0.039899
std 0.960652 1.140018
min -1.110942 -1.355619
25% -0.699514 -0.386868
50% -0.288087 0.581883
75% 0.257987 0.617961
max 0.804061 0.654039
b count 2.000000 2.000000
mean -0.613947 1.384352
std 1.496326 0.559570
min -1.672009 0.988676
25% -1.142978 1.186514
50% -0.613947 1.384352
75% -0.084916 1.582190
max 0.444115 1.780028

You may notice some methods like describe also work,even though they are not aggregations.

Column-wise and multiple function application

tips=pd.read_csv(r'./pydata-book-2nd-edition/examples/tips.csv')

tips.head()
total_bill tip smoker day time size
0 16.99 1.01 No Sun Dinner 2
1 10.34 1.66 No Sun Dinner 3
2 21.01 3.50 No Sun Dinner 3
3 23.68 3.31 No Sun Dinner 2
4 24.59 3.61 No Sun Dinner 4 
tips['tip_pct']=tips['tip']/tips['total_bill']

As we have seen,aggregating a Series or all of the columns of a DataFrame is a matter of using aggregae with the desired function or calling a method like mean or std.However,you may want to aggregate using a different function depending on the column,or multiple functions at once.

grouped=tips.groupby(['day','smoker'])

group_pct=grouped['tip_pct']

grouped.size()

day   smoker
Fri   No         4
      Yes       15
Sat   No        45
      Yes       42
Sun   No        57
      Yes       19
Thur  No        45
      Yes       17
dtype: int64

group_pct.agg('mean')

day   smoker
Fri   No        0.151650
      Yes       0.174783
Sat   No        0.158048
      Yes       0.147906
Sun   No        0.160113
      Yes       0.187250
Thur  No        0.160298
      Yes       0.163863
Name: tip_pct, dtype: float64

If you pass a list of functions or function names instead,you get back a DataFrame with column names taken from the functions:

group_pct.agg(['mean','std',peak_to_peak])
mean std peak_to_peak
day smoker
Fri No 0.151650 0.028123 0.067349
Yes 0.174783 0.051293 0.159925
Sat No 0.158048 0.039767 0.235193
Yes 0.147906 0.061375 0.290095
Sun No 0.160113 0.042347 0.193226
Yes 0.187250 0.154134 0.644685
Thur No 0.160298 0.038774 0.193350
Yes 0.163863 0.039389 0.151240 
df=pd.DataFrame(np.arange(12).reshape(3,4),index=['one','two','three'],columns=['o','t','th','f']);df
o t th f
one 0 1 2 3
two 4 5 6 7
three 8 9 10 11 
list(df.groupby([0,0,1])['f'])

[(0, one    3
  two    7
  Name: f, dtype: int32), (1, three    11
  Name: f, dtype: int32)]

If you pass a list of (name,function) tuples,the first element of each tuple will be used as the DataFrame column names.

group_pct.agg([('foo','mean'),('bar',np.std)])
foo bar
day smoker
Fri No 0.151650 0.028123
Yes 0.174783 0.051293
Sat No 0.158048 0.039767
Yes 0.147906 0.061375
Sun No 0.160113 0.042347
Yes 0.187250 0.154134
Thur No 0.160298 0.038774
Yes 0.163863 0.039389

With a DataFrame you have more options,as you can specify a list of functions to apply to all of the columns or different functions per column.To start ,suppose we wanted to compute the same three statistics for the tip_pct and total_bill columns:

functions=['count','mean','max']

result=grouped['tip_pct','total_bill'].agg(functions);result
tip_pct total_bill
count mean max count mean max
day smoker
Fri No 4 0.151650 0.187735 4 18.420000 22.75
Yes 15 0.174783 0.263480 15 16.813333 40.17
Sat No 45 0.158048 0.291990 45 19.661778 48.33
Yes 42 0.147906 0.325733 42 21.276667 50.81
Sun No 57 0.160113 0.252672 57 20.506667 48.17
Yes 19 0.187250 0.710345 19 24.120000 45.35
Thur No 45 0.160298 0.266312 45 17.113111 41.19
Yes 17 0.163863 0.241255 17 19.190588 43.11

As you can see,the resulting DataFrame has a hierarchical columns,the same as you would get aggregating each column separately and using concat to glue the results together using the column names as the keys argument

As before,a list of tuples with custom names can be passed:

ftuples=[('Durchs','mean'),('adkfd',np.var)]

grouped['tip_pct','total_bill'].agg(ftuples)
tip_pct total_bill
Durchs adkfd Durchs adkfd
day smoker
Fri No 0.151650 0.000791 18.420000 25.596333
Yes 0.174783 0.002631 16.813333 82.562438
Sat No 0.158048 0.001581 19.661778 79.908965
Yes 0.147906 0.003767 21.276667 101.387535
Sun No 0.160113 0.001793 20.506667 66.099980
Yes 0.187250 0.023757 24.120000 109.046044
Thur No 0.160298 0.001503 17.113111 59.625081
Yes 0.163863 0.001551 19.190588 69.808518

Now,suppose you want to apply potentially different functions to one or more of the columns.To do this,pass a dict to agg that contains a mappingg of column names to any of the function specifications listed so far:

grouped.agg({'tip':np.max,'size':'sum'})
tip size
day smoker
Fri No 3.50 9
Yes 4.73 31
Sat No 9.00 115
Yes 10.00 104
Sun No 6.00 167
Yes 6.50 49
Thur No 6.70 112
Yes 5.00 40 
grouped.agg(ftuples)
total_bill tip size tip_pct
Durchs adkfd Durchs adkfd Durchs adkfd Durchs adkfd
day smoker
Fri No 18.420000 25.596333 2.812500 0.807292 2.250000 0.250000 0.151650 0.000791
Yes 16.813333 82.562438 2.714000 1.161369 2.066667 0.352381 0.174783 0.002631
Sat No 19.661778 79.908965 3.102889 2.696453 2.555556 0.616162 0.158048 0.001581
Yes 21.276667 101.387535 2.875476 2.658791 2.476190 0.743322 0.147906 0.003767
Sun No 20.506667 66.099980 3.167895 1.500099 2.929825 1.066416 0.160113 0.001793
Yes 24.120000 109.046044 3.516842 1.590501 2.578947 0.812865 0.187250 0.023757
Thur No 17.113111 59.625081 2.673778 1.645997 2.488889 1.391919 0.160298 0.001503
Yes 19.190588 69.808518 3.030000 1.239863 2.352941 0.492647 0.163863 0.001551 
grouped.agg({'tip_pct':['min','max','mean','std'],'size':'sum'})
tip_pct size
min max mean std sum
day smoker
Fri No 0.120385 0.187735 0.151650 0.028123 9
Yes 0.103555 0.263480 0.174783 0.051293 31
Sat No 0.056797 0.291990 0.158048 0.039767 115
Yes 0.035638 0.325733 0.147906 0.061375 104
Sun No 0.059447 0.252672 0.160113 0.042347 167
Yes 0.065660 0.710345 0.187250 0.154134 49
Thur No 0.072961 0.266312 0.160298 0.038774 112
Yes 0.090014 0.241255 0.163863 0.039389 40

A DataFrame will have hierarchical columns only if multiple functions are applied to at least one column.

Returning Aggregated data without row index

In all of the examples up until now,the aggregated data comes back with an index,potentially hierarchical,composed from the unique group key combinations.Since this is not always desirable,you can disable this behaviour in most cases by passing as_index=False to groupby.

tips.groupby(['day','smoker'],as_index=False).mean()
day smoker total_bill tip size tip_pct
0 Fri No 18.420000 2.812500 2.250000 0.151650
1 Fri Yes 16.813333 2.714000 2.066667 0.174783
2 Sat No 19.661778 3.102889 2.555556 0.158048
3 Sat Yes 21.276667 2.875476 2.476190 0.147906
4 Sun No 20.506667 3.167895 2.929825 0.160113
5 Sun Yes 24.120000 3.516842 2.578947 0.187250
6 Thur No 17.113111 2.673778 2.488889 0.160298
7 Thur Yes 19.190588 3.030000 2.352941 0.163863 
tips.groupby(['day','smoker']).mean()
total_bill tip size tip_pct
day smoker
Fri No 18.420000 2.812500 2.250000 0.151650
Yes 16.813333 2.714000 2.066667 0.174783
Sat No 19.661778 3.102889 2.555556 0.158048
Yes 21.276667 2.875476 2.476190 0.147906
Sun No 20.506667 3.167895 2.929825 0.160113
Yes 24.120000 3.516842 2.578947 0.187250
Thur No 17.113111 2.673778 2.488889 0.160298
Yes 19.190588 3.030000 2.352941 0.163863

Apply:General split-apply-combine

The most general-purpose GroupBy method is apply,which is the subject of the rest of this section.

tips.head()
total_bill tip smoker day time size tip_pct
0 16.99 1.01 No Sun Dinner 2 0.059447
1 10.34 1.66 No Sun Dinner 3 0.160542
2 21.01 3.50 No Sun Dinner 3 0.166587
3 23.68 3.31 No Sun Dinner 2 0.139780
4 24.59 3.61 No Sun Dinner 4 0.146808

Returning to the tipping dataset from before,suppose you want to select the top five tip_pct values by group.First,write a function that selects the rows with the largest values in a particular column:

def top(df,n=5,column='tip_pct'):
    return df.sort_values(by=column)[-n:]

tips.sort_values(by='tip_pct')[-5:]
total_bill tip smoker day time size tip_pct
183 23.17 6.50 Yes Sun Dinner 4 0.280535
232 11.61 3.39 No Sat Dinner 2 0.291990
67 3.07 1.00 Yes Sat Dinner 1 0.325733
178 9.60 4.00 Yes Sun Dinner 2 0.416667
172 7.25 5.15 Yes Sun Dinner 2 0.710345 
top(tips,n=6)
total_bill tip smoker day time size tip_pct
109 14.31 4.00 Yes Sat Dinner 2 0.279525
183 23.17 6.50 Yes Sun Dinner 4 0.280535
232 11.61 3.39 No Sat Dinner 2 0.291990
67 3.07 1.00 Yes Sat Dinner 1 0.325733
178 9.60 4.00 Yes Sun Dinner 2 0.416667
172 7.25 5.15 Yes Sun Dinner 2 0.710345

Now,if we group by smoker,and call apply with this function.we get:

tips.groupby('smoker').apply(top)
total_bill tip smoker day time size tip_pct
smoker
No 88 24.71 5.85 No Thur Lunch 2 0.236746
185 20.69 5.00 No Sun Dinner 5 0.241663
51 10.29 2.60 No Sun Dinner 2 0.252672
149 7.51 2.00 No Thur Lunch 2 0.266312
232 11.61 3.39 No Sat Dinner 2 0.291990
Yes 109 14.31 4.00 Yes Sat Dinner 2 0.279525
183 23.17 6.50 Yes Sun Dinner 4 0.280535
67 3.07 1.00 Yes Sat Dinner 1 0.325733
178 9.60 4.00 Yes Sun Dinner 2 0.416667
172 7.25 5.15 Yes Sun Dinner 2 0.710345

If you pass a function to apply that takes other arguments or keywords,you can pass these after the function:

tips.groupby(['smoker','day']).apply(top,n=2,column='total_bill')
total_bill tip smoker day time size tip_pct
smoker day
No Fri 91 22.49 3.50 No Fri Dinner 2 0.155625
94 22.75 3.25 No Fri Dinner 2 0.142857
Sat 59 48.27 6.73 No Sat Dinner 4 0.139424
212 48.33 9.00 No Sat Dinner 4 0.186220
Sun 112 38.07 4.00 No Sun Dinner 3 0.105070
156 48.17 5.00 No Sun Dinner 6 0.103799
Thur 85 34.83 5.17 No Thur Lunch 4 0.148435
142 41.19 5.00 No Thur Lunch 5 0.121389
Yes Fri 90 28.97 3.00 Yes Fri Dinner 2 0.103555
95 40.17 4.73 Yes Fri Dinner 4 0.117750
Sat 102 44.30 2.50 Yes Sat Dinner 3 0.056433
170 50.81 10.00 Yes Sat Dinner 3 0.196812
Sun 184 40.55 3.00 Yes Sun Dinner 2 0.073983
182 45.35 3.50 Yes Sun Dinner 3 0.077178
Thur 83 32.68 5.00 Yes Thur Lunch 2 0.152999
197 43.11 5.00 Yes Thur Lunch 4 0.115982

Suppressing the Group keys

In the preceding examples,you see that the resulting object has a hierarchical index formed from the group keys along the indexes of each piece of the original object.You can disable this by passing group_keys=False to groupby.

tips.groupby('smoker',group_keys=False).apply(top)
total_bill tip smoker day time size tip_pct
88 24.71 5.85 No Thur Lunch 2 0.236746
185 20.69 5.00 No Sun Dinner 5 0.241663
51 10.29 2.60 No Sun Dinner 2 0.252672
149 7.51 2.00 No Thur Lunch 2 0.266312
232 11.61 3.39 No Sat Dinner 2 0.291990
109 14.31 4.00 Yes Sat Dinner 2 0.279525
183 23.17 6.50 Yes Sun Dinner 4 0.280535
67 3.07 1.00 Yes Sat Dinner 1 0.325733
178 9.60 4.00 Yes Sun Dinner 2 0.416667
172 7.25 5.15 Yes Sun Dinner 2 0.710345

**Notice the difference between agg and apply:agg aggregates GroupBy object's every column into one value,while apply apply function to every column of GroupBy object and the result may not to be just one value like agg.

tips.groupby('smoker').agg('max')
total_bill tip day time size tip_pct
smoker
No 48.33 9.0 Thur Lunch 6 0.291990
Yes 50.81 10.0 Thur Lunch 5 0.710345 
tips.groupby('smoker').apply(max)
total_bill tip smoker day time size tip_pct
smoker
No 48.33 9.0 No Thur Lunch 6 0.291990
Yes 50.81 10.0 Yes Thur Lunch 5 0.710345

The above two methods return just the same result.To some extent,aggg is a specific version of apply.

Quantile and bucket analysis

Combining qcut or cut with groupby makes it convenient to perform bucket or quantile analysis on a dataset.

frame=pd.DataFrame({'data1':np.random.randn(1000),
                   'data2':np.random.randn(1000)})

quantiles=pd.cut(frame.data1,4)

quantiles[:10]

0    (0.00174, 1.893]
1    (0.00174, 1.893]
2    (0.00174, 1.893]
3     (-3.788, -1.89]
4    (0.00174, 1.893]
5    (0.00174, 1.893]
6    (0.00174, 1.893]
7     (-3.788, -1.89]
8    (0.00174, 1.893]
9    (0.00174, 1.893]
Name: data1, dtype: category
Categories (4, object): [(-3.788, -1.89] < (-1.89, 0.00174] < (0.00174, 1.893] < (1.893, 3.784]]

help(pd.cut)

Help on function cut in module pandas.tools.tile:

cut(x, bins, right=True, labels=None, retbins=False, precision=3, include_lowest=False)
    Return indices of half-open bins to which each value of `x` belongs.
    
    Parameters
    ----------
    x : array-like
        Input array to be binned. It has to be 1-dimensional.
    bins : int or sequence of scalars
        If `bins` is an int, it defines the number of equal-width bins in the
        range of `x`. However, in this case, the range of `x` is extended
        by .1% on each side to include the min or max values of `x`. If
        `bins` is a sequence it defines the bin edges allowing for
        non-uniform bin width. No extension of the range of `x` is done in
        this case.
    right : bool, optional
        Indicates whether the bins include the rightmost edge or not. If
        right == True (the default), then the bins [1,2,3,4] indicate
        (1,2], (2,3], (3,4].
    labels : array or boolean, default None
        Used as labels for the resulting bins. Must be of the same length as
        the resulting bins. If False, return only integer indicators of the
        bins.
    retbins : bool, optional
        Whether to return the bins or not. Can be useful if bins is given
        as a scalar.
    precision : int
        The precision at which to store and display the bins labels
    include_lowest : bool
        Whether the first interval should be left-inclusive or not.
    
    Returns
    -------
    out : Categorical or Series or array of integers if labels is False
        The return type (Categorical or Series) depends on the input: a Series
        of type category if input is a Series else Categorical. Bins are
        represented as categories when categorical data is returned.
    bins : ndarray of floats
        Returned only if `retbins` is True.
    
    Notes
    -----
    The `cut` function can be useful for going from a continuous variable to
    a categorical variable. For example, `cut` could convert ages to groups
    of age ranges.
    
    Any NA values will be NA in the result.  Out of bounds values will be NA in
    the resulting Categorical object
    
    
    Examples
    --------
    >>> pd.cut(np.array([.2, 1.4, 2.5, 6.2, 9.7, 2.1]), 3, retbins=True)
    ([(0.191, 3.367], (0.191, 3.367], (0.191, 3.367], (3.367, 6.533],
      (6.533, 9.7], (0.191, 3.367]]
    Categories (3, object): [(0.191, 3.367] < (3.367, 6.533] < (6.533, 9.7]],
    array([ 0.1905    ,  3.36666667,  6.53333333,  9.7       ]))
    >>> pd.cut(np.array([.2, 1.4, 2.5, 6.2, 9.7, 2.1]), 3,
               labels=["good","medium","bad"])
    [good, good, good, medium, bad, good]
    Categories (3, object): [good < medium < bad]
    >>> pd.cut(np.ones(5), 4, labels=False)
    array([1, 1, 1, 1, 1], dtype=int64)

help(pd.qcut)

Help on function qcut in module pandas.tools.tile:

qcut(x, q, labels=None, retbins=False, precision=3)
    Quantile-based discretization function. Discretize variable into
    equal-sized buckets based on rank or based on sample quantiles. For example
    1000 values for 10 quantiles would produce a Categorical object indicating
    quantile membership for each data point.
    
    Parameters
    ----------
    x : ndarray or Series
    q : integer or array of quantiles
        Number of quantiles. 10 for deciles, 4 for quartiles, etc. Alternately
        array of quantiles, e.g. [0, .25, .5, .75, 1.] for quartiles
    labels : array or boolean, default None
        Used as labels for the resulting bins. Must be of the same length as
        the resulting bins. If False, return only integer indicators of the
        bins.
    retbins : bool, optional
        Whether to return the bins or not. Can be useful if bins is given
        as a scalar.
    precision : int
        The precision at which to store and display the bins labels
    
    Returns
    -------
    out : Categorical or Series or array of integers if labels is False
        The return type (Categorical or Series) depends on the input: a Series
        of type category if input is a Series else Categorical. Bins are
        represented as categories when categorical data is returned.
    bins : ndarray of floats
        Returned only if `retbins` is True.
    
    Notes
    -----
    Out of bounds values will be NA in the resulting Categorical object
    
    Examples
    --------
    >>> pd.qcut(range(5), 4)
    [[0, 1], [0, 1], (1, 2], (2, 3], (3, 4]]
    Categories (4, object): [[0, 1] < (1, 2] < (2, 3] < (3, 4]]
    >>> pd.qcut(range(5), 3, labels=["good","medium","bad"])
    [good, good, medium, bad, bad]
    Categories (3, object): [good < medium < bad]
    >>> pd.qcut(range(5), 4, labels=False)
    array([0, 0, 1, 2, 3], dtype=int64)

The Categorical object returned by cut can be passed directly to groupby.So we could compute a set of statistics for the data2 like so:

def get_status(group):
    return {'min':group.min(),'max':group.max(),
           'count':group.count(),'mean':group.mean()}

grouped=frame.data2.groupby(quantiles)

all(frame.data2==frame['data2'])

True

df=pd.Series([1,2,3,5])

pd.cut(df,2)

0    (0.996, 3]
1    (0.996, 3]
2    (0.996, 3]
3        (3, 5]
dtype: category
Categories (2, object): [(0.996, 3] < (3, 5]]

pd.qcut(df,2)

0    [1, 2.5]
1    [1, 2.5]
2    (2.5, 5]
3    (2.5, 5]
dtype: category
Categories (2, object): [[1, 2.5] < (2.5, 5]]

fig=plt.figure()

df.plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1ddb56f2908>

grouped=frame.data2.groupby(quantiles)

grouped.size()

data1
(-3.788, -1.89]      34
(-1.89, 0.00174]    443
(0.00174, 1.893]    492
(1.893, 3.784]       31
dtype: int64

Here,pd.cut(frame.data1,4) returns 1000 rows of Categorical object dividing data1 into 4 groups

grouped.apply(get_status)

data1                  
(-3.788, -1.89]   count     34.000000
                  max        1.935178
                  mean      -0.147920
                  min       -1.843208
(-1.89, 0.00174]  count    443.000000
                  max        2.547017
                  mean      -0.020845
                  min       -2.779875
(0.00174, 1.893]  count    492.000000
                  max        2.729491
                  mean      -0.066996
                  min       -3.854003
(1.893, 3.784]    count     31.000000
                  max        1.533001
                  mean      -0.070277
                  min       -1.639600
Name: data2, dtype: float64

grouped.apply(get_status).unstack()
count max mean min
data1
(-3.788, -1.89] 34.0 1.935178 -0.147920 -1.843208
(-1.89, 0.00174] 443.0 2.547017 -0.020845 -2.779875
(0.00174, 1.893] 492.0 2.729491 -0.066996 -3.854003
(1.893, 3.784] 31.0 1.533001 -0.070277 -1.639600

There were equal-length buckets;to compute equal-size buckets based on sample quantiles,use qcut.We will pass labels=False to jsut get quantile numbers:

grouping=pd.qcut(frame.data1,10,labels=False)

grouped=frame.data2.groupby(grouping)

grouped.size()

data1
0    100
1    100
2    100
3    100
4    100
5    100
6    100
7    100
8    100
9    100
dtype: int64

grouped.apply(get_status).unstack()
count max mean min
data1
0 100.0 2.547017 -0.013468 -2.357562
1 100.0 2.462236 -0.042549 -2.698867
2 100.0 2.546495 0.084219 -2.423998
3 100.0 2.225054 0.035150 -2.778011
4 100.0 1.669557 -0.134327 -2.779875
5 100.0 2.649044 -0.071834 -1.871068
6 100.0 2.729491 0.036419 -2.505988
7 100.0 2.014786 -0.009873 -2.671353
8 100.0 2.043135 -0.193331 -3.854003
9 100.0 2.491035 -0.184446 -2.033173

Example:Filling missing values with group-specific values

When cleaning up missing data,in some cases you will replace data observations using dropna,but in others you may want to impute(fill in) the null(NA) values using a fixed value or some value derived from the data.fillna is the right tool to use:

s=pd.Series(np.random.randn(6))

s[::2]=np.nan

s

0         NaN
1   -0.875438
2         NaN
3    1.106316
4         NaN
5    0.020065
dtype: float64

s.fillna(s.mean())

0    0.083648
1   -0.875438
2    0.083648
3    1.106316
4    0.083648
5    0.020065
dtype: float64

Suppose you need the fill value to vary by group.One way to do this is to group the data and use apply with a function that calls fillna on each data chunk.

states=['Ohio','New york','Vermont','Florida','Oregon','Nevada','California','Idaho']

group_key=['East']*4+['West']*4

data=pd.Series(np.random.randn(8),index=states)

data[['Vermont','Nevada','Idaho']]=np.nan

data.groupby(group_key).mean()

East    0.199033
West   -0.704578
dtype: float64

We can fill the NA values using the group means like so:

fill_mean=lambda g : g.fillna(g.mean())

data.groupby(group_key).apply(fill_mean)

Ohio         -0.953675
New york      0.531946
Vermont       0.199033
Florida       1.018829
Oregon       -0.539930
Nevada       -0.704578
California   -0.869226
Idaho        -0.704578
dtype: float64

In another case,you might have predefined fill values in your code that vary by group.

fill_values={'East':0.5,'West':-1}

fill_func=lambda g:g.fillna(fill_values[g.name])

data.groupby(group_key).apply(fill_func)

Ohio         -0.953675
New york      0.531946
Vermont       0.500000
Florida       1.018829
Oregon       -0.539930
Nevada       -1.000000
California   -0.869226
Idaho        -1.000000
dtype: float64

Example: Random sampling and permutation

Suppose you wanted to draw a random sample(with or without replacement) from a large dataset for Monte Carlo simulation purpose or some other application.There are a number of ways to perform the 'draws':Here we use sample method for Series.

suits=list('HSCD')

card_val=(list(range(1,11))+[10]*3)*4

base_names=['A']+list(range(2,11))+['J','K','Q'];base_names

['A', 2, 3, 4, 5, 6, 7, 8, 9, 10, 'J', 'K', 'Q']

cards=[]

for suit in ['H','S','C','D']:
    cards.extend(str(num)+suit for num in base_names)

deck=pd.Series(card_val,index=cards)

deck

AH      1
2H      2
3H      3
4H      4
5H      5
6H      6
7H      7
8H      8
9H      9
10H    10
JH     10
KH     10
QH     10
AS      1
2S      2
3S      3
4S      4
5S      5
6S      6
7S      7
8S      8
9S      9
10S    10
JS     10
KS     10
QS     10
AC      1
2C      2
3C      3
4C      4
5C      5
6C      6
7C      7
8C      8
9C      9
10C    10
JC     10
KC     10
QC     10
AD      1
2D      2
3D      3
4D      4
5D      5
6D      6
7D      7
8D      8
9D      9
10D    10
JD     10
KD     10
QD     10
dtype: int64

def draw(deck,n=5):
    return deck.sample(n)

draw(deck)

4H     4
AD     1
8S     8
3C     3
KH    10
dtype: int64

Suppose you want two random cards from each suit.Because the suit is the last character of each card name,we can group based on this and use apply.

get_suit=lambda card:card[-1]

deck.groupby(get_suit).apply(draw,n=2)

C  KC     10
   8C      8
D  5D      5
   8D      8
H  QH     10
   5H      5
S  4S      4
   10S    10
dtype: int64

Alternatively,we could write:

deck.groupby(get_suit,group_keys=False).apply(draw,2)

8C      8
KC     10
8D      8
9D      9
8H      8
QH     10
10S    10
4S      4
dtype: int64

Example:Group weighted average and correlation

Under the split-apply-combine paradigm of groupby operations between columns in a DataFrame or two Series,such as a group weighted average,are possible.

df=pd.DataFrame({'category':['a','a','a','a','b','b','b','b'],
                'data':np.random.randn(8),'weights':np.random.randn(8)});df
category data weights
0 a 0.902349 1.730949
1 a 1.287855 0.768677
2 a -0.751881 -0.571836
3 a 1.208645 0.229177
4 b 0.986127 -0.262788
5 b 1.566069 -0.252579
6 b 0.285959 0.184662
7 b 1.059962 1.804769

The group weighted average by category would then be:

grouped=df.groupby('category')

get_wavg=lambda g:np.average(g['data'],weights=g['weights'])

list(grouped)

[('a',   category      data   weights
  0        a  0.902349  1.730949
  1        a  1.287855  0.768677
  2        a -0.751881 -0.571836
  3        a  1.208645  0.229177), ('b',   category      data   weights
  4        b  0.986127 -0.262788
  5        b  1.566069 -0.252579
  6        b  0.285959  0.184662
  7        b  1.059962  1.804769)]

grouped.apply(get_wavg)

category
a    1.510830
b    0.889441
dtype: float64

close_px=pd.read_csv('./pydata-book-2nd-edition/examples/stock_px_2.csv',parse_dates=True,index_col=0)

close_px.head()
AAPL MSFT XOM SPX
2003-01-02 7.40 21.11 29.22 909.03
2003-01-03 7.45 21.14 29.24 908.59
2003-01-06 7.45 21.52 29.96 929.01
2003-01-07 7.43 21.93 28.95 922.93
2003-01-08 7.28 21.31 28.83 909.93 
help(pd.read_csv)

Help on function read_csv in module pandas.io.parsers:

read_csv(filepath_or_buffer, sep=',', delimiter=None, header='infer', names=None, index_col=None, usecols=None, squeeze=False, prefix=None, mangle_dupe_cols=True, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skipinitialspace=False, skiprows=None, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, skip_blank_lines=True, parse_dates=False, infer_datetime_format=False, keep_date_col=False, date_parser=None, dayfirst=False, iterator=False, chunksize=None, compression='infer', thousands=None, decimal=b'.', lineterminator=None, quotechar='"', quoting=0, escapechar=None, comment=None, encoding=None, dialect=None, tupleize_cols=False, error_bad_lines=True, warn_bad_lines=True, skipfooter=0, skip_footer=0, doublequote=True, delim_whitespace=False, as_recarray=False, compact_ints=False, use_unsigned=False, low_memory=True, buffer_lines=None, memory_map=False, float_precision=None)
    Read CSV (comma-separated) file into DataFrame
    
    Also supports optionally iterating or breaking of the file
    into chunks.
    
    Additional help can be found in the `online docs for IO Tools
    <http://pandas.pydata.org/pandas-docs/stable/io.html>`_.
    
    Parameters
    ----------
    filepath_or_buffer : str, pathlib.Path, py._path.local.LocalPath or any object with a read() method (such as a file handle or StringIO)
        The string could be a URL. Valid URL schemes include http, ftp, s3, and
        file. For file URLs, a host is expected. For instance, a local file could
        be file ://localhost/path/to/table.csv
    sep : str, default ','
        Delimiter to use. If sep is None, will try to automatically determine
        this. Separators longer than 1 character and different from ``'\s+'`` will
        be interpreted as regular expressions, will force use of the python parsing
        engine and will ignore quotes in the data. Regex example: ``'\r\t'``
    delimiter : str, default ``None``
        Alternative argument name for sep.
    delim_whitespace : boolean, default False
        Specifies whether or not whitespace (e.g. ``' '`` or ``'    '``) will be
        used as the sep. Equivalent to setting ``sep='\s+'``. If this option
        is set to True, nothing should be passed in for the ``delimiter``
        parameter.
    
        .. versionadded:: 0.18.1 support for the Python parser.
    
    header : int or list of ints, default 'infer'
        Row number(s) to use as the column names, and the start of the data.
        Default behavior is as if set to 0 if no ``names`` passed, otherwise
        ``None``. Explicitly pass ``header=0`` to be able to replace existing
        names. The header can be a list of integers that specify row locations for
        a multi-index on the columns e.g. [0,1,3]. Intervening rows that are not
        specified will be skipped (e.g. 2 in this example is skipped). Note that
        this parameter ignores commented lines and empty lines if
        ``skip_blank_lines=True``, so header=0 denotes the first line of data
        rather than the first line of the file.
    names : array-like, default None
        List of column names to use. If file contains no header row, then you
        should explicitly pass header=None. Duplicates in this list are not
        allowed unless mangle_dupe_cols=True, which is the default.
    index_col : int or sequence or False, default None
        Column to use as the row labels of the DataFrame. If a sequence is given, a
        MultiIndex is used. If you have a malformed file with delimiters at the end
        of each line, you might consider index_col=False to force pandas to _not_
        use the first column as the index (row names)
    usecols : array-like, default None
        Return a subset of the columns. All elements in this array must either
        be positional (i.e. integer indices into the document columns) or strings
        that correspond to column names provided either by the user in `names` or
        inferred from the document header row(s). For example, a valid `usecols`
        parameter would be [0, 1, 2] or ['foo', 'bar', 'baz']. Using this parameter
        results in much faster parsing time and lower memory usage.
    as_recarray : boolean, default False
        DEPRECATED: this argument will be removed in a future version. Please call
        `pd.read_csv(...).to_records()` instead.
    
        Return a NumPy recarray instead of a DataFrame after parsing the data.
        If set to True, this option takes precedence over the `squeeze` parameter.
        In addition, as row indices are not available in such a format, the
        `index_col` parameter will be ignored.
    squeeze : boolean, default False
        If the parsed data only contains one column then return a Series
    prefix : str, default None
        Prefix to add to column numbers when no header, e.g. 'X' for X0, X1, ...
    mangle_dupe_cols : boolean, default True
        Duplicate columns will be specified as 'X.0'...'X.N', rather than
        'X'...'X'. Passing in False will cause data to be overwritten if there
        are duplicate names in the columns.
    dtype : Type name or dict of column -> type, default None
        Data type for data or columns. E.g. {'a': np.float64, 'b': np.int32}
        (Unsupported with engine='python'). Use `str` or `object` to preserve and
        not interpret dtype.
    engine : {'c', 'python'}, optional
        Parser engine to use. The C engine is faster while the python engine is
        currently more feature-complete.
    converters : dict, default None
        Dict of functions for converting values in certain columns. Keys can either
        be integers or column labels
    true_values : list, default None
        Values to consider as True
    false_values : list, default None
        Values to consider as False
    skipinitialspace : boolean, default False
        Skip spaces after delimiter.
    skiprows : list-like or integer, default None
        Line numbers to skip (0-indexed) or number of lines to skip (int)
        at the start of the file
    skipfooter : int, default 0
        Number of lines at bottom of file to skip (Unsupported with engine='c')
    skip_footer : int, default 0
        DEPRECATED: use the `skipfooter` parameter instead, as they are identical
    nrows : int, default None
        Number of rows of file to read. Useful for reading pieces of large files
    na_values : scalar, str, list-like, or dict, default None
        Additional strings to recognize as NA/NaN. If dict passed, specific
        per-column NA values.  By default the following values are interpreted as
        NaN: '', '#N/A', '#N/A N/A', '#NA', '-1.#IND', '-1.#QNAN', '-NaN', '-nan',
    '1.#IND', '1.#QNAN', 'N/A', 'NA', 'NULL', 'NaN', 'nan'`.
    keep_default_na : bool, default True
        If na_values are specified and keep_default_na is False the default NaN
        values are overridden, otherwise they're appended to.
    na_filter : boolean, default True
        Detect missing value markers (empty strings and the value of na_values). In
        data without any NAs, passing na_filter=False can improve the performance
        of reading a large file
    verbose : boolean, default False
        Indicate number of NA values placed in non-numeric columns
    skip_blank_lines : boolean, default True
        If True, skip over blank lines rather than interpreting as NaN values
    parse_dates : boolean or list of ints or names or list of lists or dict, default False
    
        * boolean. If True -> try parsing the index.
        * list of ints or names. e.g. If [1, 2, 3] -> try parsing columns 1, 2, 3
          each as a separate date column.
        * list of lists. e.g.  If [[1, 3]] -> combine columns 1 and 3 and parse as
            a single date column.
        * dict, e.g. {'foo' : [1, 3]} -> parse columns 1, 3 as date and call result
          'foo'
    
        Note: A fast-path exists for iso8601-formatted dates.
    infer_datetime_format : boolean, default False
        If True and parse_dates is enabled, pandas will attempt to infer the format
        of the datetime strings in the columns, and if it can be inferred, switch
        to a faster method of parsing them. In some cases this can increase the
        parsing speed by ~5-10x.
    keep_date_col : boolean, default False
        If True and parse_dates specifies combining multiple columns then
        keep the original columns.
    date_parser : function, default None
        Function to use for converting a sequence of string columns to an array of
        datetime instances. The default uses ``dateutil.parser.parser`` to do the
        conversion. Pandas will try to call date_parser in three different ways,
        advancing to the next if an exception occurs: 1) Pass one or more arrays
        (as defined by parse_dates) as arguments; 2) concatenate (row-wise) the
        string values from the columns defined by parse_dates into a single array
        and pass that; and 3) call date_parser once for each row using one or more
        strings (corresponding to the columns defined by parse_dates) as arguments.
    dayfirst : boolean, default False
        DD/MM format dates, international and European format
    iterator : boolean, default False
        Return TextFileReader object for iteration or getting chunks with
        ``get_chunk()``.
    chunksize : int, default None
        Return TextFileReader object for iteration. `See IO Tools docs for more
        information
        <http://pandas.pydata.org/pandas-docs/stable/io.html#io-chunking>`_ on
        ``iterator`` and ``chunksize``.
    compression : {'infer', 'gzip', 'bz2', 'zip', 'xz', None}, default 'infer'
        For on-the-fly decompression of on-disk data. If 'infer', then use gzip,
        bz2, zip or xz if filepath_or_buffer is a string ending in '.gz', '.bz2',
        '.zip', or 'xz', respectively, and no decompression otherwise. If using
        'zip', the ZIP file must contain only one data file to be read in.
        Set to None for no decompression.
    
        .. versionadded:: 0.18.1 support for 'zip' and 'xz' compression.
    
    thousands : str, default None
        Thousands separator
    decimal : str, default '.'
        Character to recognize as decimal point (e.g. use ',' for European data).
    float_precision : string, default None
        Specifies which converter the C engine should use for floating-point
        values. The options are `None` for the ordinary converter,
        `high` for the high-precision converter, and `round_trip` for the
        round-trip converter.
    lineterminator : str (length 1), default None
        Character to break file into lines. Only valid with C parser.
    quotechar : str (length 1), optional
        The character used to denote the start and end of a quoted item. Quoted
        items can include the delimiter and it will be ignored.
    quoting : int or csv.QUOTE_* instance, default 0
        Control field quoting behavior per ``csv.QUOTE_*`` constants. Use one of
        QUOTE_MINIMAL (0), QUOTE_ALL (1), QUOTE_NONNUMERIC (2) or QUOTE_NONE (3).
    doublequote : boolean, default ``True``
       When quotechar is specified and quoting is not ``QUOTE_NONE``, indicate
       whether or not to interpret two consecutive quotechar elements INSIDE a
       field as a single ``quotechar`` element.
    escapechar : str (length 1), default None
        One-character string used to escape delimiter when quoting is QUOTE_NONE.
    comment : str, default None
        Indicates remainder of line should not be parsed. If found at the beginning
        of a line, the line will be ignored altogether. This parameter must be a
        single character. Like empty lines (as long as ``skip_blank_lines=True``),
        fully commented lines are ignored by the parameter `header` but not by
        `skiprows`. For example, if comment='#', parsing '#empty\na,b,c\n1,2,3'
        with `header=0` will result in 'a,b,c' being
        treated as the header.
    encoding : str, default None
        Encoding to use for UTF when reading/writing (ex. 'utf-8'). `List of Python
        standard encodings
        <https://docs.python.org/3/library/codecs.html#standard-encodings>`_
    dialect : str or csv.Dialect instance, default None
        If None defaults to Excel dialect. Ignored if sep longer than 1 char
        See csv.Dialect documentation for more details
    tupleize_cols : boolean, default False
        Leave a list of tuples on columns as is (default is to convert to
        a Multi Index on the columns)
    error_bad_lines : boolean, default True
        Lines with too many fields (e.g. a csv line with too many commas) will by
        default cause an exception to be raised, and no DataFrame will be returned.
        If False, then these "bad lines" will dropped from the DataFrame that is
        returned. (Only valid with C parser)
    warn_bad_lines : boolean, default True
        If error_bad_lines is False, and warn_bad_lines is True, a warning for each
        "bad line" will be output. (Only valid with C parser).
    low_memory : boolean, default True
        Internally process the file in chunks, resulting in lower memory use
        while parsing, but possibly mixed type inference.  To ensure no mixed
        types either set False, or specify the type with the `dtype` parameter.
        Note that the entire file is read into a single DataFrame regardless,
        use the `chunksize` or `iterator` parameter to return the data in chunks.
        (Only valid with C parser)
    buffer_lines : int, default None
        DEPRECATED: this argument will be removed in a future version because its
        value is not respected by the parser
    compact_ints : boolean, default False
        DEPRECATED: this argument will be removed in a future version
    
        If compact_ints is True, then for any column that is of integer dtype,
        the parser will attempt to cast it as the smallest integer dtype possible,
        either signed or unsigned depending on the specification from the
        `use_unsigned` parameter.
    use_unsigned : boolean, default False
        DEPRECATED: this argument will be removed in a future version
    
        If integer columns are being compacted (i.e. `compact_ints=True`), specify
        whether the column should be compacted to the smallest signed or unsigned
        integer dtype.
    memory_map : boolean, default False
        If a filepath is provided for `filepath_or_buffer`, map the file object
        directly onto memory and access the data directly from there. Using this
        option can improve performance because there is no longer any I/O overhead.
    
    Returns
    -------
    result : DataFrame or TextParser

close_px.info()

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 2214 entries, 2003-01-02 to 2011-10-14
Data columns (total 4 columns):
AAPL    2214 non-null float64
MSFT    2214 non-null float64
XOM     2214 non-null float64
SPX     2214 non-null float64
dtypes: float64(4)
memory usage: 86.5 KB

close_px[-4:]
AAPL MSFT XOM SPX
2011-10-11 400.29 27.00 76.27 1195.54
2011-10-12 402.19 26.96 77.16 1207.25
2011-10-13 408.43 27.18 76.37 1203.66
2011-10-14 422.00 27.27 78.11 1224.58 
help(pd.DataFrame.info)

Help on function info in module pandas.core.frame:

info(self, verbose=None, buf=None, max_cols=None, memory_usage=None, null_counts=None)
    Concise summary of a DataFrame.
    
    Parameters
    ----------
    verbose : {None, True, False}, optional
        Whether to print the full summary.
        None follows the `display.max_info_columns` setting.
        True or False overrides the `display.max_info_columns` setting.
    buf : writable buffer, defaults to sys.stdout
    max_cols : int, default None
        Determines whether full summary or short summary is printed.
        None follows the `display.max_info_columns` setting.
    memory_usage : boolean/string, default None
        Specifies whether total memory usage of the DataFrame
        elements (including index) should be displayed. None follows
        the `display.memory_usage` setting. True or False overrides
        the `display.memory_usage` setting. A value of 'deep' is equivalent
        of True, with deep introspection. Memory usage is shown in
        human-readable units (base-2 representation).
    null_counts : boolean, default None
        Whether to show the non-null counts
    
        - If None, then only show if the frame is smaller than
          max_info_rows and max_info_columns.
        - If True, always show counts.
        - If False, never show counts.

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 3 columns):
category    8 non-null object
data        8 non-null float64
weights     8 non-null float64
dtypes: float64(2), object(1)
memory usage: 272.0+ bytes

One task of interest might be to compute a DataFrame consisting of the yearly correlations of daily returns(computed from the percent changes) with SPX. As one way to do this,we first create a function that computes the pairwise correlation of each column with SPX column:

spx_corr=lambda x:x.corrwith(x['SPX'])

help(pd.DataFrame.pct_change)

Help on function pct_change in module pandas.core.generic:

pct_change(self, periods=1, fill_method='pad', limit=None, freq=None, **kwargs)
    Percent change over given number of periods.
    
    Parameters
    ----------
    periods : int, default 1
        Periods to shift for forming percent change
    fill_method : str, default 'pad'
        How to handle NAs before computing percent changes
    limit : int, default None
        The number of consecutive NAs to fill before stopping
    freq : DateOffset, timedelta, or offset alias string, optional
        Increment to use from time series API (e.g. 'M' or BDay())
    
    Returns
    -------
    chg : NDFrame
    
    Notes
    -----
    
    By default, the percentage change is calculated along the stat
    axis: 0, or ``Index``, for ``DataFrame`` and 1, or ``minor`` for
    ``Panel``. You can change this with the ``axis`` keyword argument.

df=pd.DataFrame(np.arange(12).reshape((3,4)));df
0 1 2 3
0 0 1 2 3
1 4 5 6 7
2 8 9 10 11 
df.pct_change()
0 1 2 3
0 NaN NaN NaN NaN
1 inf 4.0 2.000000 1.333333
2 1.000000 0.8 0.666667 0.571429

Above
(7-3)/3=1.33333,(6-2)/2=2,(5-1)/1=4,
(4-0)/0=inf,(8-4)/4=1,(9-5)/5=0.6667,(11-7)/7=0.57143

rets=close_px.pct_change().dropna()

rets.head()
AAPL MSFT XOM SPX
2003-01-03 0.006757 0.001421 0.000684 -0.000484
2003-01-06 0.000000 0.017975 0.024624 0.022474
2003-01-07 -0.002685 0.019052 -0.033712 -0.006545
2003-01-08 -0.020188 -0.028272 -0.004145 -0.014086
2003-01-09 0.008242 0.029094 0.021159 0.019386

Lastly,we group these percent changed by year,which can be extracted from each row label with a one-line function that returns the year attribute of each datetime laebl:

get_year=lambda x:x.year

by_year=rets.groupby(get_year) # get_year will be applied to rets.index

list(by_year)

[(2003,                 AAPL      MSFT       XOM       SPX
  2003-01-03  0.006757  0.001421  0.000684 -0.000484
  2003-01-06  0.000000  0.017975  0.024624  0.022474
  2003-01-07 -0.002685  0.019052 -0.033712 -0.006545
  2003-01-08 -0.020188 -0.028272 -0.004145 -0.014086
  2003-01-09  0.008242  0.029094  0.021159  0.019386
  2003-01-10  0.002725  0.001824 -0.013927  0.000000
  2003-01-13 -0.005435  0.008648 -0.004134 -0.001412
  2003-01-14 -0.002732  0.010379  0.008993  0.005830
  2003-01-15 -0.010959 -0.012506 -0.013713 -0.014426
  2003-01-16  0.012465 -0.016282  0.004519 -0.003942
  2003-01-17 -0.035568 -0.070345 -0.010381 -0.014017
  2003-01-21 -0.005674 -0.002473 -0.023077 -0.015702
  2003-01-22 -0.009986 -0.006445 -0.012885 -0.010432
  2003-01-23  0.021614  0.024950 -0.002175  0.010224
  2003-01-24 -0.026798 -0.046251 -0.021439 -0.029233
  2003-01-27  0.024638 -0.013783 -0.026736 -0.016160
  2003-01-28  0.031117 -0.007246  0.026326  0.013050
  2003-01-29  0.024691  0.022419  0.036431  0.006779
  2003-01-30 -0.041499 -0.033656 -0.018293 -0.022849
  2003-01-31  0.002793 -0.015831  0.027768  0.013130
  2003-02-03  0.020891  0.023056  0.013864  0.005399
  2003-02-04 -0.004093 -0.025681  0.000000 -0.014088
  2003-02-05 -0.010959 -0.007531 -0.014376 -0.005435
  2003-02-06  0.000000  0.009756 -0.008538 -0.006449
  2003-02-07 -0.020776 -0.017713 -0.007535 -0.010094
  2003-02-10  0.015559  0.017486  0.007592  0.007569
  2003-02-11  0.000000 -0.019871 -0.007176 -0.008098
  2003-02-12  0.002786  0.000000 -0.019877 -0.012687
  2003-02-13  0.009722  0.011507  0.012906 -0.001600
  2003-02-14  0.009629  0.028169  0.009465  0.021435
  ...              ...       ...       ...       ...
  2003-11-18 -0.034091  0.000000 -0.012604 -0.009084
  2003-11-19  0.000980  0.008024  0.008062  0.008016
  2003-11-20 -0.001959 -0.009950 -0.003665 -0.008432
  2003-11-21 -0.004907  0.000503  0.001003  0.001577
  2003-11-24  0.042406  0.024611  0.006014  0.016227
  2003-11-25 -0.021760 -0.012745  0.007307  0.001720
  2003-11-26  0.001934  0.001986  0.012859  0.004327
  2003-11-28  0.008687  0.009911 -0.002279 -0.000236
  2003-12-01  0.038278  0.005397  0.005546  0.011264
  2003-12-02 -0.007373 -0.007321  0.002920 -0.003271
  2003-12-03 -0.023213  0.000492  0.000971 -0.001772
  2003-12-04  0.004753  0.020639  0.013251  0.004687
  2003-12-05 -0.013245 -0.008185 -0.006380 -0.007684
  2003-12-08  0.008629  0.009709  0.016693  0.007348
  2003-12-09 -0.027567  0.005288  0.006315 -0.008529
  2003-12-10 -0.003910  0.008130  0.004079 -0.001066
  2003-12-11  0.040236  0.000949  0.003125  0.011482
  2003-12-12 -0.015094  0.001422  0.004050  0.002735
  2003-12-15 -0.033525  0.003313 -0.006826 -0.005679
  2003-12-16 -0.002973  0.011792  0.017807  0.006638
  2003-12-17 -0.011928 -0.000466  0.010436  0.001256
  2003-12-18  0.008048  0.013060  0.013670  0.011798
  2003-12-19 -0.016966 -0.001381  0.004795 -0.000477
  2003-12-22  0.008122 -0.006455 -0.003281  0.003931
  2003-12-23 -0.003021 -0.001392  0.007181  0.002818
  2003-12-24  0.030303 -0.003717  0.002080 -0.001807
  2003-12-26  0.018627  0.006063  0.005336  0.001691
  2003-12-29  0.017324  0.009272  0.013270  0.012401
  2003-12-30  0.006623  0.002297  0.002619  0.000144
  2003-12-31  0.004699 -0.005500  0.007837  0.002055
  
  [251 rows x 4 columns]),
 (2004,                 AAPL      MSFT       XOM       SPX
  2004-01-02 -0.004677  0.002765 -0.008929 -0.003094
  2004-01-05  0.042293  0.025276  0.023249  0.012395
  2004-01-06 -0.003607  0.003586 -0.006816  0.001292
  2004-01-07  0.022624 -0.001340 -0.007149  0.002367
  2004-01-08  0.033628 -0.001342 -0.002592  0.004963
  2004-01-09 -0.015411 -0.017913 -0.014727 -0.008888
  2004-01-12  0.031304 -0.003192  0.014068  0.004787
  2004-01-13  0.016863 -0.005032 -0.004335 -0.005332
  2004-01-14  0.003317  0.009655  0.000000  0.008295
  2004-01-15 -0.055372 -0.005920 -0.009869  0.001353
  2004-01-16 -0.006124  0.010078  0.005570  0.006872
  2004-01-20  0.000000  0.010431  0.009329 -0.000930
  2004-01-21 -0.004401  0.007181  0.013287  0.007772
  2004-01-22 -0.019452 -0.010250 -0.003421 -0.003207
  2004-01-23  0.017133  0.016659 -0.006007 -0.002089
  2004-01-26  0.020390  0.011072  0.012374  0.012106
  2004-01-27  0.001738 -0.018835 -0.008528 -0.009798
  2004-01-28 -0.023417 -0.019196 -0.009174 -0.013610
  2004-01-29  0.007105  0.007283  0.016204  0.004989
  2004-01-30 -0.005291 -0.009489 -0.016515 -0.002628
  2004-02-02 -0.010638 -0.009124 -0.002316  0.003651
  2004-02-03 -0.002688 -0.003683 -0.006965  0.000678
  2004-02-04 -0.020665 -0.010628 -0.001461 -0.008371
  2004-02-05  0.028440 -0.001868  0.000585  0.001838
  2004-02-06  0.012489  0.004679  0.003217  0.012555
  2004-02-09 -0.000881 -0.006521  0.011662 -0.002581
  2004-02-10  0.013228  0.004219  0.008934  0.005027
  2004-02-11  0.035683  0.004669  0.020280  0.010667
  2004-02-12 -0.003361 -0.006970 -0.000280 -0.004880
  2004-02-13 -0.030354 -0.013570  0.000000 -0.005468
  ...              ...       ...       ...       ...
  2004-11-18  0.008743 -0.003738  0.000922  0.001362
  2004-11-19 -0.003973 -0.007920  0.006680 -0.011161
  2004-11-22  0.112038 -0.007563  0.010526  0.005896
  2004-11-23 -0.000978 -0.004657  0.005661 -0.000255
  2004-11-24  0.045366  0.004254  0.005629  0.004095
  2004-11-26  0.007805 -0.001694  0.003135  0.000753
  2004-11-29  0.060099  0.006364 -0.005357 -0.003450
  2004-11-30 -0.020164  0.001686 -0.002469 -0.004030
  2004-12-01  0.011035  0.016414 -0.001800  0.014951
  2004-12-02 -0.038348 -0.005797 -0.019157 -0.000873
  2004-12-03 -0.038650  0.004998  0.001838  0.000706
  2004-12-06  0.049458  0.003730 -0.002523 -0.000772
  2004-12-07 -0.044086 -0.009496 -0.008738 -0.011073
  2004-12-08  0.006361  0.010838  0.006495  0.004877
  2004-12-09  0.011378 -0.004948  0.005992  0.005436
  2004-12-10  0.018125 -0.005387 -0.003436 -0.001043
  2004-12-13 -0.003683  0.006250  0.010345  0.008990
  2004-12-14  0.005853 -0.000828 -0.000228  0.003921
  2004-12-15 -0.000613 -0.004144 -0.002959  0.001945
  2004-12-16  0.020533  0.001665 -0.007989 -0.002082
  2004-12-17 -0.024324 -0.007478 -0.005983 -0.007488
  2004-12-20 -0.034780 -0.000419  0.025231  0.000377
  2004-12-21  0.015306  0.004606  0.012192  0.009040
  2004-12-22  0.001256 -0.003752 -0.007584  0.003418
  2004-12-23  0.004078  0.001674  0.013261  0.000463
  2004-12-27 -0.013433 -0.006266 -0.021074 -0.004305
  2004-12-28  0.016149  0.003783  0.003399  0.007154
  2004-12-29  0.004051 -0.001675  0.001581 -0.000074
  2004-12-30  0.005587 -0.005034 -0.002029  0.000082
  2004-12-31 -0.006173 -0.001686  0.004519 -0.001343
  
  [252 rows x 4 columns]),
 (2005,                 AAPL      MSFT       XOM       SPX
  2005-01-03 -0.017081  0.000845 -0.022717 -0.008119
  2005-01-04  0.010111  0.003797 -0.006904 -0.011671
  2005-01-05  0.008758 -0.002522 -0.005098 -0.003628
  2005-01-06  0.000930 -0.000843  0.012579  0.003506
  2005-01-07  0.072491 -0.002952 -0.006441 -0.001431
  2005-01-10 -0.004044  0.004653  0.003705  0.003423
  2005-01-11 -0.063805 -0.002526 -0.002537 -0.006100
  2005-01-12  0.013941  0.001688  0.014801  0.003981
  2005-01-13  0.066300 -0.018963 -0.000684 -0.008630
  2005-01-14  0.005731 -0.005584  0.010262  0.006005
  2005-01-18  0.006553  0.007775  0.008352  0.009675
  2005-01-19 -0.011039 -0.013288 -0.008731 -0.009490
  2005-01-20  0.008300 -0.004344 -0.006549 -0.007783
  2005-01-21  0.000284 -0.008290 -0.005456 -0.006415
  2005-01-24  0.003973  0.000880  0.013714 -0.003528
  2005-01-25  0.018372  0.013626  0.003833  0.004004
  2005-01-26  0.002498 -0.000434  0.007188  0.004844
  2005-01-27  0.005537  0.003905  0.001115  0.000409
  2005-01-28  0.018447  0.002593 -0.009356 -0.002716
  2005-01-31  0.039470  0.003879  0.006521  0.008460
  2005-02-01  0.008062  0.004294  0.032395  0.006891
  2005-02-02  0.027090  0.002565  0.012335  0.003178
  2005-02-03 -0.022859 -0.010661  0.010902 -0.002766
  2005-02-04  0.013368  0.005603  0.014168  0.011043
  2005-02-07  0.001268 -0.006429  0.001877 -0.001089
  2005-02-08  0.024829  0.003020  0.011863  0.000483
  2005-02-09 -0.026700 -0.006452 -0.004319 -0.008575
  2005-02-10 -0.004826 -0.000433  0.014460  0.004211
  2005-02-11  0.036243 -0.003032 -0.004072  0.006926
  2005-02-14  0.042118  0.001303  0.012676  0.000697
  ...              ...       ...       ...       ...
  2005-11-17 -0.006620  0.008441  0.003360  0.009414
  2005-11-18  0.000620  0.003587  0.015166  0.004401
  2005-11-21  0.006196  0.003177  0.019208  0.005271
  2005-11-22  0.024015 -0.008709  0.004950  0.005084
  2005-11-23  0.008870  0.000399  0.003599  0.003473
  2005-11-25  0.033229 -0.005988  0.003964  0.002086
  2005-11-28  0.004615 -0.000402 -0.022749 -0.008508
  2005-11-29 -0.022394 -0.002411 -0.006926  0.000016
  2005-11-30 -0.004112  0.000000 -0.005231 -0.006362
  2005-12-01  0.055736  0.007652  0.022785  0.012157
  2005-12-02  0.014385  0.004396 -0.004760  0.000324
  2005-12-05 -0.011152 -0.005969  0.007461 -0.002363
  2005-12-06  0.031050 -0.005604  0.003038  0.001276
  2005-12-07 -0.001350  0.002013 -0.011359 -0.005009
  2005-12-08  0.001758 -0.002009  0.006894 -0.001217
  2005-12-09  0.003375  0.000805 -0.015595  0.002811
  2005-12-12  0.007803 -0.009654  0.006182  0.000842
  2005-12-13  0.000934 -0.011373  0.003456  0.005554
  2005-12-14 -0.039611 -0.001643  0.013586  0.004190
  2005-12-15  0.002361 -0.006173 -0.006230 -0.001414
  2005-12-16 -0.014824 -0.000828 -0.024126 -0.002848
  2005-12-19  0.003797 -0.002487 -0.006035 -0.005839
  2005-12-20  0.010227  0.000831  0.003917 -0.000238
  2005-12-21  0.019276 -0.004566 -0.005657  0.002517
  2005-12-22  0.007075 -0.005421 -0.008633  0.004221
  2005-12-23 -0.009052  0.002096  0.000000  0.000426
  2005-12-27  0.011997 -0.006695 -0.021571 -0.009553
  2005-12-28 -0.008891 -0.002949  0.006675  0.001297
  2005-12-29 -0.028816 -0.004225 -0.000201 -0.002981
  2005-12-30  0.006158 -0.004667 -0.001206 -0.004887
  
  [252 rows x 4 columns]),
 (2006,                 AAPL      MSFT       XOM       SPX
  2006-01-03  0.039783  0.026428  0.041046  0.016430
  2006-01-04  0.002943  0.004568  0.001739  0.003673
  2006-01-05 -0.007870  0.000827 -0.005016  0.000016
  2006-01-06  0.025813 -0.002891  0.019779  0.009399
  2006-01-09 -0.003277 -0.002071 -0.000570  0.003656
  2006-01-10  0.063248  0.005396  0.007801 -0.000357
  2006-01-11  0.037596  0.010735  0.006796  0.003481
  2006-01-12  0.004648 -0.005310 -0.010501 -0.006274
  2006-01-13  0.015423  0.001643  0.022361  0.001205
  2006-01-17 -0.010282 -0.007380  0.009268 -0.003557
  2006-01-18 -0.026207 -0.005783 -0.013958 -0.003975
  2006-01-19 -0.041823  0.007063  0.013597  0.005564
  2006-01-20 -0.037323 -0.022690 -0.015803 -0.018326
  2006-01-23  0.020765 -0.002111  0.011016  0.001847
  2006-01-24 -0.020986 -0.002961 -0.003878  0.002405
  2006-01-25 -0.024198  0.004667 -0.012236 -0.001721
  2006-01-26 -0.025202  0.003801 -0.004317  0.007235
  2006-01-27 -0.004148  0.048801  0.022243  0.007764
  2006-01-30  0.041233  0.007621  0.029688  0.001145
  2006-01-31  0.006800  0.005175 -0.005731 -0.003976
  2006-02-01 -0.001192 -0.003960 -0.012608  0.001859
  2006-02-02 -0.044020 -0.012724  0.000000 -0.009061
  2006-02-03 -0.003467 -0.005236 -0.009121 -0.005359
  2006-02-06 -0.063326 -0.013360  0.009389  0.000783
  2006-02-07  0.004458 -0.008207 -0.022798 -0.008095
  2006-02-08  0.017899 -0.001241  0.002613  0.008663
  2006-02-09 -0.056096 -0.009114 -0.007818 -0.001478
  2006-02-10  0.036336  0.000836 -0.008068  0.002540
  2006-02-13 -0.038627 -0.011278  0.002837 -0.003260
  2006-02-14  0.045279  0.010139 -0.000943  0.010033
  ...              ...       ...       ...       ...
  2006-11-16  0.018560  0.012075 -0.028457  0.002284
  2006-11-17  0.002803 -0.002610  0.005645  0.001029
  2006-11-20  0.007222  0.016822 -0.008799 -0.000500
  2006-11-21  0.024633  0.001103  0.013162  0.001649
  2006-11-22  0.019300  0.000000 -0.006495  0.002338
  2006-11-24  0.014616 -0.005509 -0.007298 -0.003656
  2006-11-27 -0.022809 -0.009232  0.001225 -0.013555
  2006-11-28  0.025352 -0.002982  0.023405  0.003444
  2006-11-29 -0.000109  0.005981  0.025112  0.009202
  2006-11-30 -0.001525 -0.007061  0.010353  0.000822
  2006-12-01 -0.003709 -0.008234  0.005051 -0.002799
  2006-12-04 -0.002190  0.007170  0.007323  0.008885
  2006-12-05  0.001646 -0.006744  0.003849  0.004002
  2006-12-06 -0.015777 -0.004904 -0.022437 -0.001315
  2006-12-07 -0.031059 -0.004928 -0.007844 -0.003971
  2006-12-08  0.014017  0.019048 -0.002782  0.001812
  2006-12-11  0.005552  0.004860 -0.001909  0.002270
  2006-12-12 -0.029408 -0.003720  0.011768 -0.001047
  2006-12-13  0.033782  0.004108  0.014539  0.001169
  2006-12-14 -0.005615  0.017479  0.017770  0.008689
  2006-12-15 -0.009373  0.004020 -0.018164  0.001122
  2006-12-18 -0.025650 -0.009829 -0.023089 -0.003230
  2006-12-19  0.009828  0.003309  0.019524  0.002158
  2006-12-20 -0.017959  0.003298 -0.011807 -0.001417
  2006-12-21 -0.021944 -0.003652 -0.002768 -0.003674
  2006-12-22 -0.008444 -0.011364 -0.005991 -0.005316
  2006-12-26 -0.008394  0.011865  0.008379  0.004352
  2006-12-27  0.000123  0.001099  0.011953  0.007015
  2006-12-28 -0.007974 -0.001464  0.004898 -0.001479
  2006-12-29  0.049091 -0.004032 -0.009031 -0.004513
  
  [251 rows x 4 columns]),
 (2007,                 AAPL      MSFT       XOM       SPX
  2007-01-03 -0.012258  0.000000 -0.032981 -0.001199
  2007-01-04  0.022196 -0.001472 -0.018699  0.001228
  2007-01-05 -0.007121 -0.005898  0.007165 -0.006085
  2007-01-08  0.004938  0.010011 -0.008022  0.002220
  2007-01-09  0.083070  0.000734 -0.007782 -0.000517
  2007-01-10  0.047856 -0.009905 -0.015224  0.001940
  2007-01-11 -0.012371  0.035198 -0.000156  0.006340
  2007-01-12 -0.012317  0.016464  0.023583  0.004853
  2007-01-16  0.026210 -0.001408 -0.014037  0.000818
  2007-01-17 -0.022142 -0.002116  0.011452 -0.000894
  2007-01-18 -0.061927 -0.003180 -0.006885 -0.002971
  2007-01-19 -0.006399  0.003545  0.021876  0.002895
  2007-01-22 -0.019322 -0.012363 -0.008593 -0.005278
  2007-01-23 -0.012559  0.000358  0.021898  0.003542
  2007-01-24  0.011669  0.011441  0.005506  0.008501
  2007-01-25 -0.005190 -0.020502 -0.018499 -0.011270
  2007-01-26 -0.010087  0.005052  0.001206 -0.001208
  2007-01-29  0.006559 -0.002513 -0.005572 -0.001097
  2007-01-30 -0.004538 -0.001440  0.016356  0.005772
  2007-01-31  0.002104  0.012257 -0.004023  0.006593
  2007-02-01 -0.011548 -0.009615  0.013315  0.005354
  2007-02-02  0.000118 -0.012226  0.006053  0.001694
  2007-02-05 -0.009558 -0.018930  0.001761 -0.000967
  2007-02-06  0.002502 -0.003711 -0.002783  0.000698
  2007-02-07  0.023767 -0.004469 -0.004701  0.001395
  2007-02-08  0.000348 -0.003741  0.009004 -0.001179
  2007-02-09 -0.033767 -0.009763 -0.003218 -0.007077
  2007-02-12  0.019335 -0.001138 -0.008218 -0.003261
  2007-02-13 -0.002121  0.005695  0.011394  0.007597
  2007-02-14  0.007084  0.013590  0.002048  0.007644
  ...              ...       ...       ...       ...
  2007-11-16  0.012721  0.009634  0.007228  0.005230
  2007-11-19 -0.014664 -0.003817 -0.011661 -0.017460
  2007-11-20  0.029887  0.018519  0.044081  0.004486
  2007-11-21 -0.002310 -0.010345 -0.008817 -0.015927
  2007-11-23  0.018283 -0.003484  0.014282  0.016891
  2007-11-26  0.005830 -0.033376 -0.029521 -0.023239
  2007-11-27  0.013156  0.002631  0.008146  0.014930
  2007-11-28  0.030948  0.019351  0.017801  0.028560
  2007-11-29  0.022584 -0.003218  0.007690  0.000477
  2007-11-30 -0.011232  0.000323  0.006401  0.007770
  2007-12-03 -0.018439 -0.020329 -0.003425 -0.005887
  2007-12-04  0.005311 -0.004282 -0.008223 -0.006540
  2007-12-05  0.031645  0.042011  0.020418  0.015190
  2007-12-06  0.023989  0.011746  0.016857  0.015037
  2007-12-07  0.022901 -0.000628  0.000716 -0.001778
  2007-12-10 -0.000463  0.006593  0.005720  0.007510
  2007-12-11 -0.029195 -0.019027 -0.018960 -0.025271
  2007-12-12  0.012305  0.010811  0.018118  0.006050
  2007-12-13  0.005082  0.022020  0.008779  0.001224
  2007-12-14 -0.007507  0.002462 -0.016700 -0.013746
  2007-12-17 -0.031462 -0.026098 -0.014113 -0.015021
  2007-12-18 -0.007701  0.010088  0.016984  0.006280
  2007-12-19  0.000765  0.001561  0.000119 -0.001361
  2007-12-20  0.022335  0.020879  0.007634  0.004900
  2007-12-21  0.035789  0.015263  0.014205  0.016670
  2007-12-24  0.025218  0.014432  0.002451  0.008077
  2007-12-26  0.000755  0.000889  0.012225  0.000809
  2007-12-27 -0.001910 -0.017471 -0.011962 -0.014282
  2007-12-28  0.006345  0.004219  0.014203  0.001504
  2007-12-31 -0.008757 -0.014406 -0.013889 -0.006852
  
  [251 rows x 4 columns]),
 (2008,                 AAPL      MSFT       XOM       SPX
  2008-01-02 -0.016357 -0.010658 -0.001862 -0.014438
  2008-01-03  0.000462  0.004001  0.003382  0.000000
  2008-01-04 -0.076335 -0.027897 -0.018596 -0.024552
  2008-01-07 -0.013385  0.006623 -0.009356  0.003223
  2008-01-08 -0.035972 -0.033521 -0.012791 -0.018352
  2008-01-09  0.047591  0.029822  0.016711  0.013624
  2008-01-10 -0.007692 -0.003148  0.001072  0.007948
  2008-01-11 -0.029940 -0.012314 -0.014753 -0.013595
  2008-01-14  0.035266  0.014066  0.005796  0.010871
  2008-01-15 -0.054480 -0.011349 -0.019930 -0.024925
  2008-01-16 -0.055608 -0.022640 -0.027931 -0.005612
  2008-01-17  0.007830 -0.003589 -0.030246 -0.029093
  2008-01-18  0.002921 -0.002947  0.013905 -0.006045
  2008-01-22 -0.035449 -0.031856 -0.030890 -0.011085
  2008-01-23 -0.106464 -0.001018  0.012035  0.021442
  2008-01-24 -0.024951  0.041426  0.030580  0.010063
  2008-01-25 -0.041224 -0.009455 -0.023967 -0.015872
  2008-01-28  0.000000 -0.006583  0.014291  0.017548
  2008-01-29  0.011768 -0.003645 -0.000640  0.006160
  2008-01-30  0.004865 -0.012305  0.002307 -0.004764
  2008-01-31  0.024058  0.012458  0.004987  0.016772
  2008-02-01 -0.011894 -0.065846  0.002927  0.012237
  2008-02-04 -0.015701 -0.008544 -0.005963 -0.010463
  2008-02-05 -0.017395 -0.037343 -0.038928 -0.031995
  2008-02-06 -0.056895 -0.018650 -0.008234 -0.007624
  2008-02-07 -0.006230 -0.014063  0.009909  0.007886
  2008-02-08  0.034972  0.015420 -0.002254 -0.004204
  2008-02-11  0.031639 -0.012149  0.018472  0.005889
  2008-02-12 -0.035458  0.004612  0.013961  0.007266
  2008-02-13  0.036361  0.021806  0.013126  0.013604
  ...              ...       ...       ...       ...
  2008-11-18  0.020082  0.022185  0.040222  0.009838
  2008-11-19 -0.040262 -0.067824 -0.038106 -0.061156
  2008-11-20 -0.067215 -0.041327 -0.066851 -0.067123
  2008-11-21  0.025966  0.122647  0.106446  0.063248
  2008-11-24  0.125575  0.051379  0.039498  0.064723
  2008-11-25 -0.023131 -0.033951 -0.008685  0.006551
  2008-11-26  0.046256  0.025027  0.035455  0.035328
  2008-11-28 -0.024526 -0.013506 -0.009122  0.009643
  2008-12-01 -0.040358 -0.079516 -0.072849 -0.089295
  2008-12-02  0.039807  0.029176  0.044467  0.039941
  2008-12-03  0.037093  0.037799  0.016947  0.025836
  2008-12-04 -0.046820 -0.038565 -0.033735 -0.029308
  2008-12-05  0.028334  0.040111  0.004347  0.036499
  2008-12-08  0.060851  0.057311  0.039229  0.038387
  2008-12-09  0.003410 -0.019757 -0.017598 -0.023118
  2008-12-10 -0.018489  0.000517  0.023930  0.011894
  2008-12-11 -0.032685 -0.056302 -0.000668 -0.028524
  2008-12-12  0.034421 -0.004379  0.005345  0.007028
  2008-12-15 -0.035820 -0.016493 -0.006115 -0.012686
  2008-12-16  0.007177  0.055897  0.039856  0.051360
  2008-12-17 -0.065703 -0.022234 -0.025080 -0.009593
  2008-12-18  0.003028 -0.018408 -0.050000 -0.021163
  2008-12-19  0.006374 -0.009377 -0.025691  0.002937
  2008-12-22 -0.047333  0.003341 -0.001568 -0.018302
  2008-12-23  0.007464  0.004994  0.002570 -0.009717
  2008-12-24 -0.015513 -0.005522  0.009113  0.005781
  2008-12-26  0.009055 -0.002221  0.018485  0.005356
  2008-12-29  0.009323 -0.008904  0.010806 -0.003873
  2008-12-30 -0.003695  0.020213  0.007264  0.024407
  2008-12-31 -0.010893  0.004953  0.015784  0.014158
  
  [253 rows x 4 columns]),
 (2009,                 AAPL      MSFT       XOM       SPX
  2009-01-02  0.063269  0.046002  0.022773  0.031608
  2009-01-05  0.042204  0.009424 -0.000131 -0.004668
  2009-01-06 -0.016494  0.011411 -0.016374  0.007817
  2009-01-07 -0.021608 -0.060000 -0.025436 -0.030010
  2009-01-08  0.018569  0.031097  0.010659  0.003397
  2009-01-09 -0.022869 -0.029630 -0.019200 -0.021303
  2009-01-12 -0.021197 -0.002726 -0.013234 -0.022564
  2009-01-13 -0.010715  0.018043  0.018022  0.001758
  2009-01-14 -0.027135 -0.037057 -0.036229 -0.033460
  2009-01-15 -0.022852  0.007808  0.020789  0.001329
  2009-01-16 -0.012593  0.024903  0.018831  0.007562
  2009-01-20 -0.050164 -0.062635 -0.023275 -0.052816
  2009-01-21  0.059207  0.048963  0.038968  0.043491
  2009-01-22  0.066763 -0.117518 -0.012952 -0.015162
  2009-01-23  0.000000  0.005600 -0.002460  0.005378
  2009-01-26  0.014486  0.024752  0.007125  0.005553
  2009-01-27  0.012160  0.001812  0.004082  0.010926
  2009-01-28  0.038245  0.021700  0.004201  0.033558
  2009-01-29 -0.012739 -0.025369 -0.028336 -0.033120
  2009-01-30 -0.030860 -0.027845 -0.006805 -0.022789
  2009-02-02  0.015311  0.042964  0.002796 -0.000533
  2009-02-03  0.016064  0.037612  0.018544  0.015834
  2009-02-04  0.006130  0.006904 -0.005886 -0.007489
  2009-02-05  0.031106  0.022286  0.027403  0.016366
  2009-02-06  0.033796  0.032420  0.012063  0.026896
  2009-02-09  0.027978 -0.011370 -0.010727  0.001485
  2009-02-10 -0.045654 -0.032859 -0.042035 -0.049121
  2009-02-11 -0.010324  0.022084 -0.020402  0.007955
  2009-02-12  0.025305  0.002216  0.008559  0.001739
  2009-02-13 -0.001108 -0.008845 -0.008487 -0.009998
  ...              ...       ...       ...       ...
  2009-11-18 -0.005024  0.003814  0.003204 -0.000468
  2009-11-19 -0.026461 -0.011054 -0.008331 -0.013426
  2009-11-20 -0.002942 -0.005239 -0.003500 -0.003215
  2009-11-23  0.029812  0.010885  0.017704  0.013616
  2009-11-24 -0.006994 -0.001042  0.003590 -0.000533
  2009-11-25 -0.001223 -0.004172  0.006603  0.004504
  2009-11-27 -0.017631 -0.019204 -0.021047 -0.017233
  2009-11-30 -0.003390  0.006764  0.002792  0.003793
  2009-12-01 -0.014707  0.020156  0.012808  0.012075
  2009-12-02 -0.003757 -0.007626 -0.003162  0.000343
  2009-12-03  0.001274  0.001746 -0.010618 -0.008402
  2009-12-04 -0.016083  0.004881 -0.009895  0.005509
  2009-12-07 -0.022605 -0.006246 -0.006475 -0.002468
  2009-12-08  0.004869 -0.007332 -0.011051 -0.010252
  2009-12-09  0.041765  0.004573 -0.002292  0.003672
  2009-12-10 -0.006926  0.005602 -0.005313  0.005840
  2009-12-11 -0.008960 -0.000696  0.005919  0.003683
  2009-12-14  0.011866  0.008711 -0.043054  0.006959
  2009-12-15 -0.014265 -0.003109 -0.007499 -0.005547
  2009-12-16  0.004429  0.002772 -0.010728  0.001128
  2009-12-17 -0.016254 -0.016586 -0.003055 -0.011811
  2009-12-18  0.018607  0.025650 -0.000153  0.005830
  2009-12-21  0.014327  0.005139  0.004444  0.010504
  2009-12-22  0.010745  0.009884  0.000915  0.003564
  2009-12-23  0.008684  0.003375 -0.004572  0.002299
  2009-12-24  0.034339  0.002355  0.005818  0.005256
  2009-12-28  0.012294  0.005705  0.006241  0.001154
  2009-12-29 -0.011861  0.007007 -0.003480 -0.001401
  2009-12-30  0.012147 -0.013585 -0.001063  0.000195
  2009-12-31 -0.004300 -0.015788 -0.008511 -0.010050
  
  [252 rows x 4 columns]),
 (2010,                 AAPL      MSFT       XOM       SPX
  2010-01-04  0.015565  0.015700  0.014102  0.016043
  2010-01-05  0.001729  0.000336  0.003930  0.003116
  2010-01-06 -0.015906 -0.006382  0.008582  0.000546
  2010-01-07 -0.001849 -0.010142 -0.003135  0.004001
  2010-01-08  0.006648  0.006831 -0.003893  0.002882
  2010-01-11 -0.008822 -0.012890  0.011124  0.001747
  2010-01-12 -0.011375 -0.006529 -0.004906 -0.009381
  2010-01-13  0.014106  0.009339 -0.004034  0.008326
  2010-01-14 -0.005792  0.020219  0.000150  0.002427
  2010-01-15 -0.016712 -0.003359 -0.008250 -0.010823
  2010-01-19  0.044238  0.007752  0.002420  0.012500
  2010-01-20 -0.015392 -0.016388 -0.017954 -0.010598
  2010-01-21 -0.017286 -0.019041 -0.019511 -0.018945
  2010-01-22 -0.049599 -0.035009 -0.009088 -0.022141
  2010-01-25  0.026903  0.012572 -0.003795  0.004598
  2010-01-26  0.014133  0.006031  0.001111 -0.004203
  2010-01-27  0.009420  0.005994 -0.005708  0.004880
  2010-01-28 -0.041322 -0.017175 -0.008930 -0.011818
  2010-01-29 -0.036279 -0.033880 -0.008045 -0.009829
  2010-02-01  0.013902  0.008121  0.027088  0.014266
  2010-02-02  0.005803  0.001831  0.011845  0.012973
  2010-02-03  0.017206  0.006213 -0.005463 -0.005474
  2010-02-04 -0.036039 -0.027606 -0.028249 -0.031141
  2010-02-05  0.017756  0.006350  0.001292  0.002897
  2010-02-08 -0.006856 -0.010765 -0.000484 -0.008863
  2010-02-09  0.010664  0.010507  0.013232  0.013040
  2010-02-10 -0.005454 -0.000743 -0.005415 -0.002233
  2010-02-11  0.018194  0.004831  0.006085  0.009680
  2010-02-12  0.008607 -0.007027 -0.006844 -0.002745
  2010-02-16  0.015071  0.019739  0.022917  0.018001
  ...              ...       ...       ...       ...
  2010-11-18  0.026389  0.010359  0.018876  0.015357
  2010-11-19 -0.005512 -0.005915  0.003329  0.002540
  2010-11-22  0.021615  0.001587 -0.005049 -0.001575
  2010-11-23 -0.014775 -0.023762 -0.017254 -0.014284
  2010-11-24  0.019661  0.010142  0.013131  0.014923
  2010-11-26  0.000635 -0.004819 -0.009320 -0.007469
  2010-11-29  0.005937  0.002421  0.003234 -0.001379
  2010-11-30 -0.018052 -0.002013  0.001465 -0.006070
  2010-12-01  0.016873  0.031061  0.025457  0.021617
  2010-12-02  0.005531  0.032473  0.002140  0.012818
  2010-12-03 -0.002232  0.004926 -0.003986  0.002603
  2010-12-06  0.008537 -0.006787  0.001572 -0.001298
  2010-12-07 -0.006060  0.001139  0.002141  0.000515
  2010-12-08  0.008799  0.013652  0.005412  0.003702
  2010-12-09 -0.003894 -0.005612  0.002125  0.003843
  2010-12-10  0.002502  0.009406  0.002544  0.006002
  2010-12-13  0.003463 -0.003354  0.002538  0.000048
  2010-12-14 -0.004290  0.013837 -0.002531  0.000911
  2010-12-15  0.000219  0.008115 -0.004652 -0.005122
  2010-12-16  0.002778  0.005123  0.005241  0.006185
  2010-12-17 -0.001992 -0.003276 -0.000705  0.000837
  2010-12-20  0.004990 -0.003287  0.000705  0.002548
  2010-12-21  0.006176  0.009527  0.006904  0.006030
  2010-12-22  0.002961  0.004356  0.001120  0.003380
  2010-12-23 -0.004798  0.003975  0.005451 -0.001644
  2010-12-27  0.003337 -0.008279 -0.002641  0.000613
  2010-12-28  0.002433 -0.002178  0.005715  0.000771
  2010-12-29 -0.000553 -0.001455 -0.000693  0.001009
  2010-12-30 -0.005011 -0.004372 -0.000139 -0.001508
  2010-12-31 -0.003399  0.002195 -0.003329 -0.000191
  
  [252 rows x 4 columns]),
 (2011,                 AAPL      MSFT       XOM       SPX
  2011-01-03  0.021732  0.002556  0.019624  0.011315
  2011-01-04  0.005219  0.004006  0.004641 -0.001313
  2011-01-05  0.008180 -0.003264 -0.002717  0.005007
  2011-01-06 -0.000808  0.029476  0.006540 -0.002123
  2011-01-07  0.007161 -0.007777  0.005414 -0.001845
  2011-01-10  0.018833 -0.013181 -0.006058 -0.001376
  2011-01-11 -0.002365 -0.003971  0.007450  0.003725
  2011-01-12  0.008137  0.015585  0.011697  0.009008
  2011-01-13  0.003658 -0.012491  0.001728 -0.001711
  2011-01-14  0.008100  0.003975  0.014725  0.007385
  2011-01-18 -0.022469  0.012599  0.011243  0.001376
  2011-01-19 -0.005313 -0.006754 -0.006076 -0.010116
  2011-01-20 -0.018180 -0.004295 -0.006243 -0.001295
  2011-01-21 -0.017915 -0.011503  0.015838  0.002414
  2011-01-24  0.032842  0.012727 -0.004896  0.005836
  2011-01-25  0.011705  0.002513  0.001165  0.000263
  2011-01-26  0.007176  0.011819  0.012416  0.004221
  2011-01-27 -0.001861  0.002832  0.002683  0.002244
  2011-01-28 -0.020716 -0.038475 -0.011084 -0.017852
  2011-01-31  0.009580 -0.000734  0.021386  0.007663
  2011-02-01  0.016828  0.009184  0.039985  0.016694
  2011-02-02 -0.002058 -0.001820 -0.005943 -0.002723
  2011-02-03 -0.002556 -0.010212  0.000366  0.002354
  2011-02-04  0.008910  0.004422 -0.001829  0.002884
  2011-02-07  0.015527  0.015407  0.007698  0.006240
  2011-02-08  0.009435  0.002890 -0.006063  0.004185
  2011-02-09  0.008333 -0.011167 -0.005124 -0.002786
  2011-02-10 -0.010107 -0.016758  0.007848  0.000750
  2011-02-11  0.006515 -0.009263 -0.004623  0.005507
  2011-02-14  0.006529 -0.000374  0.025303  0.002385
  ...              ...       ...       ...       ...
  2011-09-02 -0.018319 -0.015643 -0.018370 -0.025282
  2011-09-06  0.015212 -0.011240 -0.013723 -0.007436
  2011-09-07  0.011034  0.019208  0.035137  0.028646
  2011-09-08  0.000547  0.008462 -0.011270 -0.010612
  2011-09-09 -0.017337 -0.018307 -0.024856 -0.026705
  2011-09-12  0.006517  0.005828  0.011688  0.006966
  2011-09-13  0.012318  0.005794 -0.002645  0.009120
  2011-09-14  0.012168  0.017665  0.013817  0.013480
  2011-09-15  0.009401  0.018491  0.018860  0.017187
  2011-09-16  0.019188  0.004817  0.007296  0.005707
  2011-09-19  0.027790  0.003319 -0.011402 -0.009803
  2011-09-20  0.004421 -0.008453  0.004206 -0.001661
  2011-09-21 -0.003168 -0.036694 -0.027564 -0.029390
  2011-09-22 -0.025040 -0.035783 -0.037932 -0.031883
  2011-09-23  0.006172  0.000000  0.001011  0.006082
  2011-09-26 -0.002795  0.015164  0.034771  0.023336
  2011-09-27 -0.009698  0.009041  0.016592  0.010688
  2011-09-28 -0.005635 -0.003506 -0.011521 -0.020691
  2011-09-29 -0.016221 -0.005082  0.025114  0.008114
  2011-09-30 -0.023683 -0.022004 -0.016919 -0.024974
  2011-10-03 -0.017623 -0.014464 -0.020377 -0.028451
  2011-10-04 -0.005606  0.033021  0.023612  0.022488
  2011-10-05  0.015436  0.021705  0.015378  0.017866
  2011-10-06 -0.002327  0.017381 -0.000811  0.018304
  2011-10-07 -0.020060 -0.003417 -0.004466 -0.008163
  2011-10-10  0.051406  0.026286  0.036977  0.034125
  2011-10-11  0.029526  0.002227 -0.000131  0.000544
  2011-10-12  0.004747 -0.001481  0.011669  0.009795
  2011-10-13  0.015515  0.008160 -0.010238 -0.002974
  2011-10-14  0.033225  0.003311  0.022784  0.017380
  
  [199 rows x 4 columns])]

rets.index[0].year

2003

by_year.apply(spx_corr)
AAPL MSFT XOM SPX
2003 0.541124 0.745174 0.661265 1.0
2004 0.374283 0.588531 0.557742 1.0
2005 0.467540 0.562374 0.631010 1.0
2006 0.428267 0.406126 0.518514 1.0
2007 0.508118 0.658770 0.786264 1.0
2008 0.681434 0.804626 0.828303 1.0
2009 0.707103 0.654902 0.797921 1.0
2010 0.710105 0.730118 0.839057 1.0
2011 0.691931 0.800996 0.859975 1.0

We could also compute inter-column correlations.Here we compute the annual correlation between Apple and Microsoft.

rets['AAPL'].corr(rets['MSFT'])

0.44469678861732237

df
0 1 2 3
0 0 1 2 3
1 4 5 6 7
2 8 9 10 11 
df[0].corr(df[1])

1.0

help(pd.Series.corr)

Help on function corr in module pandas.core.series:

corr(self, other, method='pearson', min_periods=None)
    Compute correlation with `other` Series, excluding missing values
    
    Parameters
    ----------
    other : Series
    method : {'pearson', 'kendall', 'spearman'}
        * pearson : standard correlation coefficient
        * kendall : Kendall Tau correlation coefficient
        * spearman : Spearman rank correlation
    min_periods : int, optional
        Minimum number of observations needed to have a valid result
    
    
    Returns
    -------
    correlation : float

Example:Group-wise Linear regression

You can use groupby to perform more complex group-wise statistical analysis,as long as the function returns a pandas object or scalar value.For example,I can define the following regress function(using the statsmodels econometrics libraby),which executes an ordinary least quares(OLS) regression on each chunk of data:

import statsmodels.api as sm

def regress(data,yvar,xvars):
    Y=data[yvar]
    X=data[xvars]
    X['intercept']=1
    result=sm.OLS(Y,X).fit()
    return result.params

by_year.apply(regress,'AAPL',['SPX'])
SPX intercept
2003 1.195406 0.000710
2004 1.363463 0.004201
2005 1.766415 0.003246
2006 1.645496 0.000080
2007 1.198761 0.003438
2008 0.968016 -0.001110
2009 0.879103 0.002954
2010 1.052608 0.001261
2011 0.806605 0.001514

Pivot tables and cross-tabulation

help(pd.DataFrame.pivot_table)

Help on function pivot_table in module pandas.tools.pivot:

pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All')
    Create a spreadsheet-style pivot table as a DataFrame. The levels in the
    pivot table will be stored in MultiIndex objects (hierarchical indexes) on
    the index and columns of the result DataFrame
    
    Parameters
    ----------
    data : DataFrame
    values : column to aggregate, optional
    index : column, Grouper, array, or list of the previous
        If an array is passed, it must be the same length as the data. The list
        can contain any of the other types (except list).
        Keys to group by on the pivot table index.  If an array is passed, it
        is being used as the same manner as column values.
    columns : column, Grouper, array, or list of the previous
        If an array is passed, it must be the same length as the data. The list
        can contain any of the other types (except list).
        Keys to group by on the pivot table column.  If an array is passed, it
        is being used as the same manner as column values.
    aggfunc : function or list of functions, default numpy.mean
        If list of functions passed, the resulting pivot table will have
        hierarchical columns whose top level are the function names (inferred
        from the function objects themselves)
    fill_value : scalar, default None
        Value to replace missing values with
    margins : boolean, default False
        Add all row / columns (e.g. for subtotal / grand totals)
    dropna : boolean, default True
        Do not include columns whose entries are all NaN
    margins_name : string, default 'All'
        Name of the row / column that will contain the totals
        when margins is True.
    
    Examples
    --------
    >>> df
       A   B   C      D
    0  foo one small  1
    1  foo one large  2
    2  foo one large  2
    3  foo two small  3
    4  foo two small  3
    5  bar one large  4
    6  bar one small  5
    7  bar two small  6
    8  bar two large  7
    
    >>> table = pivot_table(df, values='D', index=['A', 'B'],
    ...                     columns=['C'], aggfunc=np.sum)
    >>> table
              small  large
    foo  one  1      4
         two  6      NaN
    bar  one  5      4
         two  6      7
    
    Returns
    -------
    table : DataFrame

ex1=pd.read_csv('ex1.csv')

ex1
sex age grade province
0 m 20 89 H
1 f 23 90 B
2 m 21 86 B
3 m 22 56 H
4 f 23 78 J
5 f 22 90 B
6 m 21 100 H
7 m 20 98 J
8 m 25 67 B
9 f 23 89 J 
ex1.pivot_table(index='sex',aggfunc=[np.mean,np.sum,np.min,np.max],margins=True)
mean sum amin amax
age grade age grade age grade province age grade province
sex
f 22.75 86.750000 91.0 347.0 22 78 B 23 90 J
m 21.50 82.666667 129.0 496.0 20 56 B 25 100 J
All 22.00 84.300000 220.0 843.0 20 56 B 25 100 J 
ex1.pivot_table(index='sex')
age grade
sex
f 22.75 86.750000
m 21.50 82.666667

Attention :min and max will discard non-number column 'province`.

df=pd.DataFrame(np.arange(4).reshape((2,2)),index=['a','b'],columns=['one','two']);df
one two
a 0 1
b 2 3 
df.pivot_table(index='one',aggfunc=np.sum,margins=True)
two
one
0 1.0
2 3.0
All 4.0 
tips.head()
total_bill tip smoker day time size tip_pct
0 16.99 1.01 No Sun Dinner 2 0.059447
1 10.34 1.66 No Sun Dinner 3 0.160542
2 21.01 3.50 No Sun Dinner 3 0.166587
3 23.68 3.31 No Sun Dinner 2 0.139780
4 24.59 3.61 No Sun Dinner 4 0.146808 
tips.pivot_table(index=['day','smoker'])
size tip tip_pct total_bill
day smoker
Fri No 2.250000 2.812500 0.151650 18.420000
Yes 2.066667 2.714000 0.174783 16.813333
Sat No 2.555556 3.102889 0.158048 19.661778
Yes 2.476190 2.875476 0.147906 21.276667
Sun No 2.929825 3.167895 0.160113 20.506667
Yes 2.578947 3.516842 0.187250 24.120000
Thur No 2.488889 2.673778 0.160298 17.113111
Yes 2.352941 3.030000 0.163863 19.190588 
tips.pivot_table(values=['tip_pct','size'],index=['time','day'],columns='smoker')
tip_pct size
smoker No Yes No Yes
time day
Dinner Fri 0.139622 0.165347 2.000000 2.222222
Sat 0.158048 0.147906 2.555556 2.476190
Sun 0.160113 0.187250 2.929825 2.578947
Thur 0.159744 NaN 2.000000 NaN
Lunch Fri 0.187735 0.188937 3.000000 1.833333
Thur 0.160311 0.163863 2.500000 2.352941 
tips.pivot_table(['tip_pct','size'],index=['time','day'],columns='smoker') #The same with above
tip_pct size
smoker No Yes No Yes
time day
Dinner Fri 0.139622 0.165347 2.000000 2.222222
Sat 0.158048 0.147906 2.555556 2.476190
Sun 0.160113 0.187250 2.929825 2.578947
Thur 0.159744 NaN 2.000000 NaN
Lunch Fri 0.187735 0.188937 3.000000 1.833333
Thur 0.160311 0.163863 2.500000 2.352941

We could augment this table to include partial totals by passing margins=True.This has the effect of adding All row and column labels,with corresponding values being the group statistics for all the data within a single tier:

tips.pivot_table(['tip_pct','size'],index=['time','day'],columns='smoker',margins=True)
tip_pct size
smoker No Yes All No Yes All
time day
Dinner Fri 0.139622 0.165347 0.158916 2.000000 2.222222 2.166667
Sat 0.158048 0.147906 0.153152 2.555556 2.476190 2.517241
Sun 0.160113 0.187250 0.166897 2.929825 2.578947 2.842105
Thur 0.159744 NaN 0.159744 2.000000 NaN 2.000000
Lunch Fri 0.187735 0.188937 0.188765 3.000000 1.833333 2.000000
Thur 0.160311 0.163863 0.161301 2.500000 2.352941 2.459016
All 0.159328 0.163196 0.160803 2.668874 2.408602 2.569672

Here,the All values are means without taking into account smoker versus non-smoker or any of the two levels of grouping on the rows.

if some combinations are empty,you may wish to pass a fill_value.

tips.pivot_table('tip_pct',index=['time','size','smoker'],columns='day',aggfunc='mean',fill_value=0)
day Fri Sat Sun Thur
time size smoker
Dinner 1 No 0.000000 0.137931 0.000000 0.000000
Yes 0.000000 0.325733 0.000000 0.000000
2 No 0.139622 0.162705 0.168859 0.159744
Yes 0.171297 0.148668 0.207893 0.000000
3 No 0.000000 0.154661 0.152663 0.000000
Yes 0.000000 0.144995 0.152660 0.000000
4 No 0.000000 0.150096 0.148143 0.000000
Yes 0.117750 0.124515 0.193370 0.000000
5 No 0.000000 0.000000 0.206928 0.000000
Yes 0.000000 0.106572 0.065660 0.000000
6 No 0.000000 0.000000 0.103799 0.000000
Lunch 1 No 0.000000 0.000000 0.000000 0.181728
Yes 0.223776 0.000000 0.000000 0.000000
2 No 0.000000 0.000000 0.000000 0.166005
Yes 0.181969 0.000000 0.000000 0.158843
3 No 0.187735 0.000000 0.000000 0.084246
Yes 0.000000 0.000000 0.000000 0.204952
4 No 0.000000 0.000000 0.000000 0.138919
Yes 0.000000 0.000000 0.000000 0.155410
5 No 0.000000 0.000000 0.000000 0.121389
6 No 0.000000 0.000000 0.000000 0.173706 
data

Ohio         -0.953675
New york      0.531946
Vermont            NaN
Florida       1.018829
Oregon       -0.539930
Nevada             NaN
California   -0.869226
Idaho              NaN
dtype: float64

Cross-tabulation:Crosstab

A cross-tabulation is a special case of a pivot table that computes group frequencies.

data=pd.read_csv('ex2.csv')

data
Sample Nationality Handeness
0 1 USA R
1 2 JAPAN L
2 3 USA R
3 4 JAPAN R
4 5 JAPAN L
5 6 JAPAN R
6 7 USA R
7 8 USA L
8 9 JAPAN R
9 10 USA R 
pd.crosstab(data.Nationality,data.Handeness,margins=True)
Handeness L R All
Nationality
JAPAN 2 3 5
USA 1 4 5
All 3 7 10 
pd.crosstab([tips.time,tips.day],tips.smoker,margins=True)
smoker No Yes All
time day
Dinner Fri 3 9 12
Sat 45 42 87
Sun 57 19 76
Thur 1 0 1
Lunch Fri 1 6 7
Thur 44 17 61
All 151 93 244 
help(pd.crosstab)

Help on function crosstab in module pandas.tools.pivot:

crosstab(index, columns, values=None, rownames=None, colnames=None, aggfunc=None, margins=False, dropna=True, normalize=False)
    Compute a simple cross-tabulation of two (or more) factors. By default
    computes a frequency table of the factors unless an array of values and an
    aggregation function are passed
    
    Parameters
    ----------
    index : array-like, Series, or list of arrays/Series
        Values to group by in the rows
    columns : array-like, Series, or list of arrays/Series
        Values to group by in the columns
    values : array-like, optional
        Array of values to aggregate according to the factors.
        Requires `aggfunc` be specified.
    aggfunc : function, optional
        If specified, requires `values` be specified as well
    rownames : sequence, default None
        If passed, must match number of row arrays passed
    colnames : sequence, default None
        If passed, must match number of column arrays passed
    margins : boolean, default False
        Add row/column margins (subtotals)
    dropna : boolean, default True
        Do not include columns whose entries are all NaN
    normalize : boolean, {'all', 'index', 'columns'}, or {0,1}, default False
        Normalize by dividing all values by the sum of values.
    
        - If passed 'all' or `True`, will normalize over all values.
        - If passed 'index' will normalize over each row.
        - If passed 'columns' will normalize over each column.
        - If margins is `True`, will also normalize margin values.
    
        .. versionadded:: 0.18.1
    
    
    Notes
    -----
    Any Series passed will have their name attributes used unless row or column
    names for the cross-tabulation are specified.
    
    Any input passed containing Categorical data will have **all** of its
    categories included in the cross-tabulation, even if the actual data does
    not contain any instances of a particular category.
    
    In the event that there aren't overlapping indexes an empty DataFrame will
    be returned.
    
    Examples
    --------
    >>> a
    array([foo, foo, foo, foo, bar, bar,
           bar, bar, foo, foo, foo], dtype=object)
    >>> b
    array([one, one, one, two, one, one,
           one, two, two, two, one], dtype=object)
    >>> c
    array([dull, dull, shiny, dull, dull, shiny,
           shiny, dull, shiny, shiny, shiny], dtype=object)
    
    >>> crosstab(a, [b, c], rownames=['a'], colnames=['b', 'c'])
    b    one          two
    c    dull  shiny  dull  shiny
    a
    bar  1     2      1     0
    foo  2     2      1     2
    
    >>> foo = pd.Categorical(['a', 'b'], categories=['a', 'b', 'c'])
    >>> bar = pd.Categorical(['d', 'e'], categories=['d', 'e', 'f'])
    >>> crosstab(foo, bar)  # 'c' and 'f' are not represented in the data,
                            # but they still will be counted in the output
    col_0  d  e  f
    row_0
    a      1  0  0
    b      0  1  0
    c      0  0  0
    
    Returns
    -------
    crosstab : DataFrame
posted @   JohnYang819  阅读(1205)  评论(0)  编辑  收藏  举报
编辑推荐：
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
点击右上角即可分享
微信分享提示

目录导航