- \n
- Splitting<\/strong>\u00a0the data into groups based on some criteria<\/li>\n
- Applying<\/strong>\u00a0a function to each group independently<\/li>\n
- Combining<\/strong>\u00a0the results into a data structure<\/li>\n<\/ul>\n<\/blockquote>\n
Of these, the split step is the most straightforward. In fact, in many situations you may wish to split the data set into groups and do something with those groups yourself. In the apply step, we might wish to one of the following:<\/p>\n
\n
- \n
- Aggregation<\/strong>: computing a summary statistic (or statistics) about each group. Some examples:
\n\n
- \n
- Compute group sums or means<\/li>\n
- Compute group sizes \/ counts<\/li>\n<\/ul>\n<\/blockquote>\n<\/li>\n
- Transformation<\/strong>: perform some group-specific computations and return a like-indexed. Some examples:
\n\n
- \n
- Standardizing data (zscore) within group<\/li>\n
- Filling NAs within groups with a value derived from each group<\/li>\n<\/ul>\n<\/blockquote>\n<\/li>\n
- Some combination of the above: GroupBy will examine the results of the apply step and try to return a sensibly combined result if it doesn\u2019t fit into either of the above two categories<\/li>\n<\/ul>\n<\/blockquote>\n
Since the set of object instance method on pandas data structures are generally rich and expressive, we often simply want to invoke, say, a DataFrame function on each group. The name GroupBy should be quite familiar to those who have used a SQL-based tool (or\u00a0itertools<\/tt>), in which you can write code like:<\/p>\n
\n\nSELECT Column1, Column2, mean(Column3), sum(Column4)\nFROM SomeTable\nGROUP BY Column1, Column2<\/pre>\n<\/div>\n<\/div>\n
We aim to make operations like this natural and easy to express using pandas. We\u2019ll address each area of GroupBy functionality then provide some non-trivial examples \/ use cases.<\/p>\n
See some\u00a0cookbook examples<\/em><\/a>\u00a0for some advanced strategies<\/p>\n
\nSplitting an object into groups<\/h2>\n
pandas objects can be split on any of their axes. The abstract definition of grouping is to provide a mapping of labels to group names. To create a GroupBy object (more on what the GroupBy object is later), you do the following:<\/p>\n
\n\n>>> grouped = obj.groupby(key)\n>>> grouped = obj.groupby(key, axis=1)\n>>> grouped = obj.groupby([key1, key2])<\/pre>\n<\/div>\n<\/div>\n
The mapping can be specified many different ways:<\/p>\n
\n
- \n
- A Python function, to be called on each of the axis labels<\/li>\n
- A list or NumPy array of the same length as the selected axis<\/li>\n
- A dict or Series, providing a\u00a0label\u00a0->\u00a0group\u00a0name<\/tt>\u00a0mapping<\/li>\n
- For DataFrame objects, a string indicating a column to be used to group. Of course\u00a0df.groupby('A')<\/tt>\u00a0is just syntactic sugar for\u00a0df.groupby(df['A'])<\/tt>, but it makes life simpler<\/li>\n
- A list of any of the above things<\/li>\n<\/ul>\n<\/blockquote>\n
Collectively we refer to the grouping objects as the\u00a0keys<\/strong>. For example, consider the following DataFrame:<\/p>\n
\n\nIn [690]: df = DataFrame({'A' : ['foo', 'bar', 'foo', 'bar',\n .....: 'foo', 'bar', 'foo', 'foo'],\n .....: 'B' : ['one', 'one', 'two', 'three',\n .....: 'two', 'two', 'one', 'three'],\n .....: 'C' : randn(8), 'D' : randn(8)})\n .....:\n\nIn [691]: df\nOut[691]: \n A B C D\n0 foo one 0.469112 -0.861849\n1 bar one -0.282863 -2.104569\n2 foo two -1.509059 -0.494929\n3 bar three -1.135632 1.071804\n4 foo two 1.212112 0.721555\n5 bar two -0.173215 -0.706771\n6 foo one 0.119209 -1.039575\n7 foo three -1.044236 0.271860<\/pre>\n<\/div>\n<\/div>\nWe could naturally group by either the\u00a0A<\/tt>\u00a0or\u00a0B<\/tt>\u00a0columns or both:<\/p>\n
\n\nIn [692]: grouped = df.groupby('A')\n\nIn [693]: grouped = df.groupby(['A', 'B'])<\/pre>\n<\/div>\n<\/div>\nThese will split the DataFrame on its index (rows). We could also split by the columns:<\/p>\n
\n\nIn [694]: def get_letter_type(letter):\n .....: if letter.lower() in 'aeiou':\n .....: return 'vowel'\n .....: else:\n .....: return 'consonant'\n .....:\n\nIn [695]: grouped = df.groupby(get_letter_type, axis=1)<\/pre>\n<\/div>\n<\/div>\n
Starting with 0.8, pandas Index objects now supports duplicate values. If a non-unique index is used as the group key in a groupby operation, all values for the same index value will be considered to be in one group and thus the output of aggregation functions will only contain unique index values:<\/p>\n
\n\nIn [696]: lst = [1, 2, 3, 1, 2, 3]\n\nIn [697]: s = Series([1, 2, 3, 10, 20, 30], lst)\n\nIn [698]: grouped = s.groupby(level=0)\n\nIn [699]: grouped.first()\nOut[699]: \n1 1\n2 2\n3 3\ndtype: int64\n\nIn [700]: grouped.last()\nOut[700]: \n1 10\n2 20\n3 30\ndtype: int64\n\nIn [701]: grouped.sum()\nOut[701]: \n1 11\n2 22\n3 33\ndtype: int64<\/pre>\n<\/div>\n<\/div>\n
Note that\u00a0no splitting occurs<\/strong>\u00a0until it\u2019s needed. Creating the GroupBy object only verifies that you\u2019ve passed a valid mapping.<\/p>\n
\nNote<\/p>\n
Many kinds of complicated data manipulations can be expressed in terms of GroupBy operations (though can\u2019t be guaranteed to be the most efficient). You can get quite creative with the label mapping functions.<\/p>\n<\/div>\n
\nGroupBy object attributes<\/h3>\n
The\u00a0groups<\/tt>\u00a0attribute is a dict whose keys are the computed unique groups and corresponding values being the axis labels belonging to each group. In the above example we have:<\/p>\n
\n\nIn [702]: df.groupby('A').groups\nOut[702]: {'bar': [1, 3, 5], 'foo': [0, 2, 4, 6, 7]}\n\nIn [703]: df.groupby(get_letter_type, axis=1).groups\nOut[703]: {'consonant': ['B', 'C', 'D'], 'vowel': ['A']}<\/pre>\n<\/div>\n<\/div>\nCalling the standard Python\u00a0len<\/tt>\u00a0function on the GroupBy object just returns the length of the\u00a0groups<\/tt>\u00a0dict, so it is largely just a convenience:<\/p>\n
\n\nIn [704]: grouped = df.groupby(['A', 'B'])\n\nIn [705]: grouped.groups\nOut[705]: \n{('bar', 'one'): [1],\n ('bar', 'three'): [3],\n ('bar', 'two'): [5],\n ('foo', 'one'): [0, 6],\n ('foo', 'three'): [7],\n ('foo', 'two'): [2, 4]}\n\nIn [706]: len(grouped)\nOut[706]: 6<\/pre>\n<\/div>\n<\/div>\nBy default the group keys are sorted during the groupby operation. You may however passsort``=``False<\/tt>\u00a0for potential speedups:<\/p>\n
\n\nIn [707]: df2 = DataFrame({'X' : ['B', 'B', 'A', 'A'], 'Y' : [1, 2, 3, 4]})\n\nIn [708]: df2.groupby(['X'], sort=True).sum()\nOut[708]: \n Y\nX \nA 7\nB 3\n\nIn [709]: df2.groupby(['X'], sort=False).sum()\nOut[709]: \n Y\nX \nB 3\nA 7<\/pre>\n<\/div>\n<\/div>\n<\/div>\n\nGroupBy with MultiIndex<\/h3>\n
With\u00a0hierarchically-indexed data<\/em><\/a>, it\u2019s quite natural to group by one of the levels of the hierarchy.<\/p>\n
\n\nIn [710]: s\nOut[710]: \nfirst second\nbar one -0.424972\n two 0.567020\nbaz one 0.276232\n two -1.087401\nfoo one -0.673690\n two 0.113648\nqux one -1.478427\n two 0.524988\ndtype: float64\n\nIn [711]: grouped = s.groupby(level=0)\n\nIn [712]: grouped.sum()\nOut[712]: \nfirst\nbar 0.142048\nbaz -0.811169\nfoo -0.560041\nqux -0.953439\ndtype: float64<\/pre>\n<\/div>\n<\/div>\n
If the MultiIndex has names specified, these can be passed instead of the level number:<\/p>\n
\n\nIn [713]: s.groupby(level='second').sum()\nOut[713]: \nsecond\none -2.300857\ntwo 0.118256\ndtype: float64<\/pre>\n<\/div>\n<\/div>\n
The aggregation functions such as\u00a0sum<\/tt>\u00a0will take the level parameter directly. Additionally, the resulting index will be named according to the chosen level:<\/p>\n
\n\nIn [714]: s.sum(level='second')\nOut[714]: \nsecond\none -2.300857\ntwo 0.118256\ndtype: float64<\/pre>\n<\/div>\n<\/div>\n
Also as of v0.6, grouping with multiple levels is supported.<\/p>\n
\n\nIn [715]: s\nOut[715]: \nfirst second third\nbar doo one 0.404705\n two 0.577046\nbaz bee one -1.715002\n two -1.039268\nfoo bop one -0.370647\n two -1.157892\nqux bop one -1.344312\n two 0.844885\ndtype: float64\n\nIn [716]: s.groupby(level=['first','second']).sum()\nOut[716]: \nfirst second\nbar doo 0.981751\nbaz bee -2.754270\nfoo bop -1.528539\nqux bop -0.499427\ndtype: float64<\/pre>\n<\/div>\n<\/div>\n
More on the\u00a0sum<\/tt>\u00a0function and aggregation later.<\/p>\n<\/div>\n
\nDataFrame column selection in GroupBy<\/h3>\n
Once you have created the GroupBy object from a DataFrame, for example, you might want to do something different for each of the columns. Thus, using\u00a0[]<\/tt>\u00a0similar to getting a column from a DataFrame, you can do:<\/p>\n
\n\nIn [717]: grouped = df.groupby(['A'])\n\nIn [718]: grouped_C = grouped['C']\n\nIn [719]: grouped_D = grouped['D']<\/pre>\n<\/div>\n<\/div>\n
This is mainly syntactic sugar for the alternative and much more verbose:<\/p>\n
\n\nIn [720]: df['C'].groupby(df['A'])\nOut[720]: <pandas.core.groupby.SeriesGroupBy at 0xb60fe10><\/pre>\n<\/div>\n<\/div>\n
Additionally this method avoids recomputing the internal grouping information derived from the passed key.<\/p>\n<\/div>\n<\/div>\n
\nIterating through groups<\/h2>\n
With the GroupBy object in hand, iterating through the grouped data is very natural and functions similarly to\u00a0itertools.groupby<\/tt>:<\/p>\n
\n\nIn [721]: grouped = df.groupby('A')\n\nIn [722]: for name, group in grouped:\n .....: print name\n .....: print group\n .....:\nbar\n A B C D\n1 bar one -0.282863 -2.104569\n3 bar three -1.135632 1.071804\n5 bar two -0.173215 -0.706771\nfoo\n A B C D\n0 foo one 0.469112 -0.861849\n2 foo two -1.509059 -0.494929\n4 foo two 1.212112 0.721555\n6 foo one 0.119209 -1.039575\n7 foo three -1.044236 0.271860<\/pre>\n<\/div>\n<\/div>\nIn the case of grouping by multiple keys, the group name will be a tuple:<\/p>\n
\n\nIn [723]: for name, group in df.groupby(['A', 'B']):\n .....: print name\n .....: print group\n .....:\n('bar', 'one')\n A B C D\n1 bar one -0.282863 -2.104569\n('bar', 'three')\n A B C D\n3 bar three -1.135632 1.071804\n('bar', 'two')\n A B C D\n5 bar two -0.173215 -0.706771\n('foo', 'one')\n A B C D\n0 foo one 0.469112 -0.861849\n6 foo one 0.119209 -1.039575\n('foo', 'three')\n A B C D\n7 foo three -1.044236 0.27186\n('foo', 'two')\n A B C D\n2 foo two -1.509059 -0.494929\n4 foo two 1.212112 0.721555<\/pre>\n<\/div>\n<\/div>\nIt\u2019s standard Python-fu but remember you can unpack the tuple in the for loop statement if you wish:\u00a0for\u00a0(k1,\u00a0k2),\u00a0group\u00a0in\u00a0grouped:<\/tt>.<\/p>\n<\/div>\n
\nAggregation<\/h2>\n
Once the GroupBy object has been created, several methods are available to perform a computation on the grouped data. An obvious one is aggregation via the\u00a0aggregate<\/tt>\u00a0or equivalently\u00a0agg<\/tt>\u00a0method:<\/p>\n
\n\nIn [724]: grouped = df.groupby('A')\n\nIn [725]: grouped.aggregate(np.sum)\nOut[725]: \n C D\nA \nbar -1.591710 -1.739537\nfoo -0.752861 -1.402938\n\nIn [726]: grouped = df.groupby(['A', 'B'])\n\nIn [727]: grouped.aggregate(np.sum)\nOut[727]: \n C D\nA B \nbar one -0.282863 -2.104569\n three -1.135632 1.071804\n two -0.173215 -0.706771\nfoo one 0.588321 -1.901424\n three -1.044236 0.271860\n two -0.296946 0.226626<\/pre>\n<\/div>\n<\/div>\n - For DataFrame objects, a string indicating a column to be used to group. Of course\u00a0df.groupby('A')<\/tt>\u00a0is just syntactic sugar for\u00a0df.groupby(df['A'])<\/tt>, but it makes life simpler<\/li>\n
- Applying<\/strong>\u00a0a function to each group independently<\/li>\n
![\"_images\/groupby_transform_plot.png\"](\"http:\/\/pandas.pydata.org\/pandas-docs\/dev\/_images\/groupby_transform_plot.png\")
Another common data transform is to replace missing data with the group mean.<\/p>\n