A couple of weeks ago in my inaugural blog post I wrote about the state of GroupBy in pandas and gave an example application. However, I was dissatisfied with the limited expressiveness (see the end of the article), so I decided to invest some serious time in the groupby functionality in pandas over the last 2 weeks in beefing up what you can do. So this article is a part show-and-tell, part quick tutorial on the new features. Note that I haven't added a lot of this to the official documentation yet.
GroupBy primer
GroupBy may be one of the least well-understood features in pandas. Let's consider a DataFrame (basically a labeled 2D array):
In [18]: df Out[18]: A B C D 2000-03-31 00:00:00 0.6304 -1.493 0.2082 0.3378 2000-06-30 00:00:00 -0.1041 1.093 0.1227 -0.2362 2000-09-29 00:00:00 -1.822 0.7325 -0.4964 -1.707 2000-12-29 00:00:00 0.3325 -0.2213 0.5465 1.265 2001-03-30 00:00:00 0.8422 -0.1265 0.1234 0.7743 2001-06-29 00:00:00 -0.07774 -0.3906 -0.6328 -1.259 2001-09-28 00:00:00 -0.3417 -0.4209 1.724 0.3069 2001-12-31 00:00:00 0.493 -0.2815 1.314 1.004 2002-03-29 00:00:00 1.003 -0.4807 0.7232 1.079 2002-06-28 00:00:00 0.1415 -2.275 0.6178 0.8817 2002-09-30 00:00:00 -0.6855 0.3921 -1.468 1.522 2002-12-31 00:00:00 -1.635 0.3083 0.1209 -0.5606 2003-03-31 00:00:00 0.5707 -0.7125 -0.07677 0.5154 2003-06-30 00:00:00 0.3646 -0.8574 -0.562 0.2176 2003-09-30 00:00:00 1.024 -0.8113 0.6771 -1.261
Here, the index (row labels) contains dates and the columns are names for each time series. When performing a groupby operation, we may different goals:
- Perform an aggregation, like computing the sum of mean of each group. Functionally this means applying a function to each group and putting the aggregated results into a DataFrame
- Slicing the DataFrame into chunks (groups) and then doing something with the slices
- Performing a transformation, like standardizing each group (computing the zscore)
So there are two tasks: first, grouping the data; second, doing something with the grouped data. As far as grouping, in a totally abstract sense we have to define some kind of mapping that assigns labels (one of the axes) into group buckets. A mapping could be one of many things:
- A Python function, to be called on each label
- A dict, containing {label : group_name} mappings
- An array the same length as the axis containing the group correspondences. This may often be one of the columns in a DataFrame, but need not be. Every other mapping type gets rendered to this form eventually.
So in the above data, let's say we wanted to group the data by year. Since the row labels are Python datetime objects, we can access the year attribute when calling groupby
In [21]: grouped = df.groupby(lambda x: x.year)
This returns an object of type GroupBy. With this object, you can do a lot of things including: iteration, aggregation, or transformation. So we could iterate like so:
In [20]: for year, group in grouped: ....: print year ....: print group ....: 2000 A B C D 2000-03-31 00:00:00 0.6304 -1.493 0.2082 0.3378 2000-06-30 00:00:00 -0.1041 1.093 0.1227 -0.2362 2000-09-29 00:00:00 -1.822 0.7325 -0.4964 -1.707 2000-12-29 00:00:00 0.3325 -0.2213 0.5465 1.265 2001 A B C D 2001-03-30 00:00:00 0.8422 -0.1265 0.1234 0.7743 2001-06-29 00:00:00 -0.07774 -0.3906 -0.6328 -1.259 2001-09-28 00:00:00 -0.3417 -0.4209 1.724 0.3069 2001-12-31 00:00:00 0.493 -0.2815 1.314 1.004 2002 A B C D 2002-03-29 00:00:00 1.003 -0.4807 0.7232 1.079 2002-06-28 00:00:00 0.1415 -2.275 0.6178 0.8817 2002-09-30 00:00:00 -0.6855 0.3921 -1.468 1.522 2002-12-31 00:00:00 -1.635 0.3083 0.1209 -0.5606 2003 A B C D 2003-03-31 00:00:00 0.5707 -0.7125 -0.07677 0.5154 2003-06-30 00:00:00 0.3646 -0.8574 -0.562 0.2176 2003-09-30 00:00:00 1.024 -0.8113 0.6771 -1.261
Or aggregate like so:
In [21]: grouped.aggregate(np.sum) Out[21]: A B C D 2000 -0.9629 0.1108 0.3811 -0.3405 2001 0.9159 -1.219 2.529 0.8268 2002 -1.176 -2.055 -0.006253 2.921 2003 1.959 -2.381 0.03838 -0.528
Or transform groups like so (here I standardize each group):
In [22]: zscore = lambda x: (x - x.mean()) / x.std() In [23]: transformed = grouped.transform(zscore) Out[23]: A B C D 2000-03-31 00:00:00 0.7946 -1.316 0.2598 0.3396 2000-06-30 00:00:00 0.1246 0.9216 0.06319 -0.1213 2000-09-29 00:00:00 -1.442 0.61 -1.361 -1.302 2000-12-29 00:00:00 0.5228 -0.2155 1.038 1.084 2001-03-30 00:00:00 1.142 1.34 -0.47 0.5569 2001-06-29 00:00:00 -0.571 -0.644 -1.168 -1.438 2001-09-28 00:00:00 -1.062 -0.8715 1.009 0.0983 2001-12-31 00:00:00 0.4917 0.1758 0.6299 0.7827 2002-03-29 00:00:00 1.149 0.02675 0.7159 0.3864 2002-06-28 00:00:00 0.3857 -1.422 0.6118 0.168 2002-09-30 00:00:00 -0.3469 0.7316 -1.449 0.878 2002-12-31 00:00:00 -1.188 0.664 0.1209 -1.432 2003-03-31 00:00:00 -0.2443 1.097 -0.1434 0.7267 2003-06-30 00:00:00 -0.8552 -0.8598 -0.9205 0.4138 2003-09-30 00:00:00 1.099 -0.2376 1.064 -1.14
Some aggregations are so common that they're provided as instance methods on the GroupBy object:
In [24]: grouped.sum() Out[24]: A B C D 2000 -0.9629 0.1108 0.3811 -0.3405 2001 0.9159 -1.219 2.529 0.8268 2002 -1.176 -2.055 -0.006253 2.921 2003 1.959 -2.381 0.03838 -0.528 In [25]: grouped.mean() Out[25]: A B C D 2000 -0.2407 0.02769 0.09526 -0.08512 2001 0.229 -0.3049 0.6324 0.2067 2002 -0.2939 -0.5138 -0.001563 0.7303 2003 0.6531 -0.7938 0.01279 -0.176
For those with SQL experience, a DataFrame can be treated like an SQL table. As such, grouping 'by columns" is quite natural:
In [27]: df2 Out[27]: A B C D 0 foo one -0.7883 0.7743 1 bar one -0.5866 0.06009 2 foo two 0.9312 1.2 3 bar three -0.6417 0.3444 4 foo two -0.8841 -0.08126 5 bar two 1.194 -0.7933 6 foo one -1.624 -0.1403 7 foo three 0.5046 0.5833 In [28]: for name, group in df2.groupby('A'): ....: print name ....: print group ....: bar A B C D 1 bar one -0.5866 0.06009 3 bar three -0.6417 0.3444 5 bar two 1.194 -0.7933 foo A B C D 0 foo one -0.7883 0.7743 2 foo two 0.9312 1.2 4 foo two -0.8841 -0.08126 6 foo one -1.624 -0.1403 7 foo three 0.5046 0.5833
Note the presence of the "nuisance" B column when we group by A. I want this to work:
In [30]: df2.groupby('A').mean() Out[30]: C D bar -0.01137 -0.1296 foo -0.3722 0.4671
More on dealing with this below.
New: Column selection
In my last post I mentioned wanting to be able to do a groupby-operation on a single column of a DataFrame using another column or set of columns as the groupers. So in the above example, what I want is:
grouped = df['C'].groupby(df['A']) # or grouped = df['C'].groupby([df['A'], df['B']])
This is too verbose / awkward for my tastes. So now you can do:
In [111]: df2.groupby('A')['C'].mean() Out[111]: bar -0.447919901908 foo -0.158016186685
Behind the scenes, this simply passes the C column to a Series GroupBy object along with the already-computed grouping(s).
New: Group by multiple columns / key functions
The ability to group by multiple criteria (just like SQL) has been one of my most desired GroupBy features for a long time. It's also very hard to implement efficiently. So I bit the bullet and carefully crafted Cython code to speedily do aggregations on data grouped by multiple columns. So now you can do:
In [113]: df2.groupby(['A', 'B']).mean() Out[113]: A B C D 0 foo one 0.3558 -0.4818 1 foo two -0.2758 -1.271 2 foo three -0.95 1.709 3 bar one 1.916 -0.5093 4 bar two -2.315 0.1177 5 bar three -0.9447 0.2519
Iteration with multiple groups flattens the key pairs:
In [114]: for k1, k2, group in df2.groupby(['A', 'B']): .....: print k1, k2 .....: print group .....: foo one A B C D 0 foo one 0.8248 0.3856 6 foo one -0.1133 -1.349 foo two A B C D 2 foo two -1.175 -1.036 4 foo two 0.6239 -1.506 foo three A B C D 7 foo three -0.95 1.709 bar one A B C D 1 bar one 1.916 -0.5093 bar two A B C D 5 bar two -2.315 0.1177 bar three A B C D 3 bar three -0.9447 0.2519
As a less trivial example, I loaded some data from the Bureau of Labor Statistics, which I'll write more about another time:
In [126]: df = read_table('ce.data.44.FinActOtherCurr.txt', sep='\s+', index_col=None) In [127]: df Out[127]: <class 'pandas.core.frame.DataFrame'> Index: 32806 entries, 0 to 32805 Data columns: series_id 32806 non-null values year 32806 non-null values period 32806 non-null values value 32806 non-null values dtypes: int64(1), float64(1), object(2) In [128]: df[:15] Out[128]: series_id year period value 0 CEU5500000034 1990 M01 83 1 CEU5500000034 1990 M02 83.6 2 CEU5500000034 1990 M03 83.4 3 CEU5500000034 1990 M04 84.9 4 CEU5500000034 1990 M05 84.1 5 CEU5500000034 1990 M06 85.4 6 CEU5500000034 1990 M07 86.5 7 CEU5500000034 1990 M08 86 8 CEU5500000034 1990 M09 85.5 9 CEU5500000034 1990 M10 83.6 10 CEU5500000034 1990 M11 83.6 11 CEU5500000034 1990 M12 84.7 12 CEU5500000034 1990 M13 84.5 13 CEU5500000034 1991 M01 82.9 14 CEU5500000034 1991 M02 83.1
This data set has 118 unique series_id's and spans 22 years (the period column gives the month of observation), so we're talking about 2596 unique groups. Let's say we wanted to compute the mean value for each year, it's pretty simple with the improved groupby:
In [129]: df.groupby(['series_id', 'year']).mean() Out[129]: <class 'pandas.core.frame.DataFrame'> Index: 2596 entries, 0 to 2595 Data columns: series_id 2596 non-null values value 2596 non-null values year 2596 non-null values dtypes: int64(1), float64(1), object(1)
I was also pretty impressed with how fast this operation is (the power of Cython!):
In [130]: timeit df.groupby(['series_id', 'year']).mean() 10 loops, best of 3: 22.9 ms per loop
I haven't compared this with an SQL engine (SQLite, MySQL, Postgres, ...) but I hope that it's competitive. If you call a non-Cythonized function, the speed goes down considerably:
In [132]: timeit df.groupby(['series_id', 'year'])['value'].std() 1 loops, best of 3: 232 ms per loop
Edit: It's also possible to pass multiple functions instead of column names or to mix and match column names with functions:
In [22]: df.groupby([lambda x: x.year, lambda x: x.month]).mean() Out[22]: A B C D key_0 key_1 0 0.09433 0.06146 0.1891 0.2079 2000 1 1 0.02674 -0.09411 0.1557 -0.07926 2000 2 2 0.07089 0.3938 -0.08026 -0.416 2000 3 3 0.133 -0.04532 0.03341 -0.2866 2000 4 4 0.1301 0.1803 -0.4241 0.1482 2000 5 5 -0.3804 -0.08935 0.04791 0.08268 2000 6 6 0.2211 0.2483 -0.3559 -0.06205 2000 7 7 -0.2908 0.07606 -0.2001 -0.05225 2000 8 8 -0.07888 -0.3915 0.06442 0.5109 2000 9 9 0.3809 0.4037 -0.2431 -0.2892 2000 10 10 -0.2765 0.01461 -0.2653 0.1335 2000 11 11 0.6983 -0.1167 0.3964 0.03776 2000 12 12 -0.2347 0.2333 -0.06508 0.3 2001 1 13 0.1751 0.05545 -0.004038 0.06194 2001 2 14 0.08792 0.03517 0.05583 -0.4356 2001 3 15 -0.08408 0.05567 -0.1705 0.1914 2001 4 16 0.008912 0.2317 0.08875 0.08637 2001 5 17 0.3304 0.3635 0.005158 0.3588 2001 6 18 -0.1324 0.3419 -0.2527 -0.2249 2001 7 19 0.0472 0.2552 -0.07589 -0.07992 2001 8 20 0.07582 -0.2295 0.1002 0.1077 2001 9 21 0.2368 0.02214 -0.3866 -0.2671 2001 10 22 0.2116 -0.07872 -0.06399 0.2309 2001 11
New: Omitting "nuisance" columns
It's pretty common to group by a column and ignore other columns containing non-floating point data. It used to be that you'd get an error, forcing you to first drop the "nuisance" columns, e.g. suppose we only want to group by the A column here:
In [133]: df2 Out[133]: A B C D 0 foo one 0.8248 0.3856 1 bar one 1.916 -0.5093 2 foo two -1.175 -1.036 3 bar three -0.9447 0.2519 4 foo two 0.6239 -1.506 5 bar two -2.315 0.1177 6 foo one -0.1133 -1.349 7 foo three -0.95 1.709
If you try to compute the mean of the B column, things won't quite work out:
In [134]: grouped = df2.groupby('A')['B'].mean() TypeError: unsupported operand type(s) for /: 'str' and 'float'
To cope with this, so-called "nuisance" columns are now automatically dropped. While I don't like discarding information, I think "practicality beats purity" in this case:
In [135]: df2.groupby('A').mean() Out[135]: C D bar -0.4479 -0.0466 foo -0.158 -0.3593
New: “auto-dispatching” to DataFrame / Series
Since very few functions in DataFrame and Series/TimeSeries are implemented on the GroupBy classes, you may wish to simply invoke a Series instance method on each group, let's say:
In [140]: df.groupby('series_id')['value'].agg(Series.skew) Out[140]: CEU5500000034 -0.253887792525 CEU5500000035 0.0938252241898 CEU5552200034 0.387563548587 CEU5552200035 0.0868489117242 CEU5552210034 0.74506367536 CEU5552210035 0.551290226183 <snip>
But since we're got Python at our disposal, it's relatively straightforward to override __getattribute__ to wrap and dispatch calls to Series/DataFrame instance methods so you can do this:
In [141]: df.groupby('series_id')['value'].skew() Out[141]: CEU5500000034 -0.253887792525 CEU5500000035 0.0938252241898 CEU5552200034 0.387563548587 CEU5552200035 0.0868489117242 CEU5552210034 0.74506367536 <snip>
Some of the more complicated methods, like the super-useful describe method can be used in fact:
In [142]: df.groupby('series_id')['value'].describe() Out[142]: count mean std min 10% 50% 90% max CEU5500000034 279 96.54 8.317 81.7 84 98.6 107.2 112 CEU5500000035 279 93.88 28.9 50.4 55.28 91.9 134.5 140.2 CEU5552200034 278 97.95 6.839 86.9 89.47 97.15 108.2 113.9 CEU5552200035 278 93.58 26.18 58 60.34 89.2 127.4 137.3 CEU5552210034 278 100.7 3.95 94.1 95.87 99.8 106 113.1 CEU5552210035 278 98.84 23.86 71.6 73.77 92.6 137.2 145.8 ...
For those interested, the internal code to do this dispatching is short and simple, by the way:
def __getattribute__(self, attr): try: return object.__getattribute__(self, attr) except AttributeError: if hasattr(self.obj, attr): return self._make_wrapper(attr) raise def _make_wrapper(self, name): f = getattr(self.obj, name) if not isinstance(f, types.MethodType): return self.aggregate(lambda self: getattr(self, name)) # return the class reference f = getattr(type(self.obj), name) def wrapper(*args, **kwargs): curried = lambda self: f(self, *args, **kwargs) return self.aggregate(curried) return wrapper
Old but not well known: apply multiple functions
This has always been around but not well-known by many people. Suppose you want to apply multiple functions to a group and collect all the results. You can pass a dict of functions:
In [144]: grouped.agg({'mean' : np.mean, 'variance' : np.var}) Out[144]: mean variance CEU5500000034 96.54 68.92 CEU5500000035 93.88 832.3 CEU5552200034 97.95 46.6 CEU5552200035 93.58 683.1 CEU5552210034 100.7 15.54 CEU5552210035 98.84 567.1 CEU5552211034 100.9 13.73 CEU5552211035 99.34 622 <snip>
I have a mind to expand on this idea for DataFrame objects to get us closer to the full flexibility of SQL with Pythonic syntax. If anyone has ideas or suggestions please let me know.
Conclusions
GroupBy is certainly not done. If you use these tools and find them useful, please let me know. If you can think of ways to make them better, that would be nice information too. I think it would be great to implement a full SQL engine on top of pandas (similar to the SAS "proc sql"), and this new GroupBy functionality gets us closer to that goal.