ENH: str.extractall for several matches
State: closed

For a series `S`, the excellent `S.str.extract` method returns the first match in each subject of the series:

``` python
>>> import re
>>> import pandas as pd
>>> import numpy as np
>>> data = {
...     'Dave': 'dave@google.com',
...     'multiple': 'rob@gmail.com some text steve@gmail.com',
...     'none': np.nan,
...     }
>>> pattern = r'''
... (?P<user>[a-z]+)
... @
... (?P<domain>[a-z]+)
... \.
... (?P<tld>[a-z]{2,4})
... '''
>>> S = pd.Series(data)
>>> S.str.extract(pattern, re.VERBOSE)
          user  domain  tld
Dave      dave  google  com
multiple   rob   gmail  com
none       NaN     NaN  NaN
>>> 
```

That's great, but sometimes we want to extract all matches in each element of the series. You can do that with `S.str.findall` but its result does not include the names specified in the capturing groups of the regular expression:

``` python
>>> S.str.findall(pattern, re.VERBOSE)
Dave                           [(dave, google, com)]
multiple    [(rob, gmail, com), (steve, gmail, com)]
none                                             NaN
dtype: object
>>> 
```

I propose the `S.str.extractall` method which returns a `Series` the same length as the subject `S`. Each element of the series is a `DataFrame` with a row for each match and a column for each group:

``` python
>>> result = S.str.extractall(pattern, re.VERBOSE)
>>> result[0]
   user  domain  tld
0  dave  google  com
>>> result[1]
    user domain  tld
0    rob  gmail  com
1  steve  gmail  com
>>> result[2]
Empty DataFrame
Columns: [user, domain, tld]
Index: []
>>> 
```

Before I write any more testing code, can we start a discussion about whether or not this is an acceptable design choice, in relation to the other functionality of pandas? @sinhrks @jorisvandenbossche @jreback @mortada since you seem to be discussing extract in #10103

Also do you have any ideas about how to get the result (a Series of DataFrames) to print more nicely? With my current fork we have

``` python
>>> result
Dave                   user  domain  tld
0  dave  google  com
multiple        user domain  tld
0    rob  gmail  com
1  s...
none        Empty DataFrame
Columns: [user, domain, tld]
I...
dtype: object
>>> 
```

In R the equivalent functionality is provided by the https://github.com/tdhock/namedCapture package (str_match_all_named returns a list of data.frames), and the resulting printout is readable because of the way that R prints lists:

``` r
> library(namedCapture)
> S <- c(
+   Dave='dave@google.com',
+   multiple='rob@gmail.com some text steve@gmail.com',
+   none=NA)
> pattern <- paste0(
+   "(?P<user>[a-z]+)",
+   "@",
+   "(?P<domain>[a-z]+)",
+   "[.]",
+   "(?P<tld>[a-z]{2,4})")
> str_match_all_named(S, pattern)
$Dave
     user   domain   tld  
[1,] "dave" "google" "com"

$multiple
     user    domain  tld  
[1,] "rob"   "gmail" "com"
[2,] "steve" "gmail" "com"

$none
<0 x 0 matrix>

> 
```


Labels:
name: Enhancement
name: Strings
description: String extension data type and string data

Comments:
never embed DataFrames within a Series!

instead you _could_ simply return a multi-indexed frame, e.g. the first level are the original indexes of the Series, the 2nd level are the number of matches the columns as you have them

```
In [13]: DataFrame({'user' : ['dave','rob','steve',np.nan],
                              'domain' : ['google.com','gmail.com','gmail.com',np.nan]},
                              index=pd.MultiIndex.from_tuples([('Dave',0),('multiple',0),('multiple',1),('none',0)]))
Out[13]: 
                domain   user
Dave     0  google.com   dave
multiple 0   gmail.com    rob
         1   gmail.com  steve
none     0         NaN    NaN
```

@tdhock further you would prob add a parameter on how to handle partial matches (e.g. the `some` and `text` fields), e.g. would you be greedy then nan-fill or skip unless all filled (e.g. what you are doing now)

Thanks for the suggestions @jreback. Now extractall returns a DataFrame with a MultiIndex:

``` python
>>> import re
>>> import pandas as pd
>>> import numpy as np
>>> data_dict = {
...     'single': {
...         "Dave":'dave@google.com',
...         "Toby":'tdhock5@gmail.com',
...         "Maude":'maudelaperriere@gmail.com',
...         },
...     'multiple': {
...         "robAndSteve": 'rob@gmail.com some text steve@gmail.com',
...         "abcdef": 'a@b.com some text c@d.com and e@f.com',
...         },
...     'none': {
...         "missing":np.nan,
...         "empty":"",
...         },
...     }
>>> tuple_list = []
>>> subject_list = []
>>> for k1, d in data_dict.iteritems():
...     for k2, subject in d.iteritems():
...         k = (k1, k2)
...         tuple_list.append(k)
...         subject_list.append(subject)
... 
>>> index = pd.MultiIndex.from_tuples(tuple_list, names=("matches", "subject"))
>>> Si = pd.Series(subject_list, index)
>>> named_pattern = r'''
... (?P<user>[a-z0-9]+)
... @
... (?P<domain>[a-z]+)
... \.
... (?P<tld>[a-z]{2,4})
... '''
>>> iresult = Si.str.extractall(named_pattern, re.VERBOSE)
>>> iresult
                                 user  domain  tld
matches  subject                                  
single   Dave                    dave  google  com
         Maude        maudelaperriere   gmail  com
         Toby                 tdhock5   gmail  com
multiple robAndSteve              rob   gmail  com
         robAndSteve            steve   gmail  com
         abcdef                     a       b  com
         abcdef                     c       d  com
         abcdef                     e       f  com
>>> S = pd.Series(subject_list)
>>> result = S.str.extractall(named_pattern, re.VERBOSE)
>>> result
              user  domain  tld
0             dave  google  com
1  maudelaperriere   gmail  com
2          tdhock5   gmail  com
3              rob   gmail  com
3            steve   gmail  com
4                a       b  com
4                c       d  com
4                e       f  com
>>>
```

So then we can access all the matches for the subject string with rob and steve via:

``` python
>>> iresult.loc["multiple", "robAndSteve"]
                       user domain  tld
matches  subject                       
multiple robAndSteve    rob  gmail  com
         robAndSteve  steve  gmail  com
>>> result.loc[3]
    user domain  tld
3    rob  gmail  com
3  steve  gmail  com
```

For subjects that have 0 matches I think it would be more consistent and user-friendly if the following would return a DataFrame with 0 rows rather than an exception. Is that possible using some index options?

``` python
>>> result.loc[5]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pandas/core/indexing.py", line 1198, in __getitem__
    return self._getitem_axis(key, axis=0)
  File "pandas/core/indexing.py", line 1342, in _getitem_axis
    self._has_valid_type(key, axis)
  File "pandas/core/indexing.py", line 1304, in _has_valid_type
    error()
  File "pandas/core/indexing.py", line 1291, in error
    (key, self.obj._get_axis_name(axis)))
KeyError: 'the label [5] is not in the [index]'
>>> iresult.loc["none", "empty"]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pandas/core/indexing.py", line 1196, in __getitem__
    return self._getitem_tuple(key)
  File "pandas/core/indexing.py", line 709, in _getitem_tuple
    return self._getitem_lowerdim(tup)
  File "pandas/core/indexing.py", line 822, in _getitem_lowerdim
    result = self._handle_lowerdim_multi_index_axis0(tup)
  File "pandas/core/indexing.py", line 804, in _handle_lowerdim_multi_index_axis0
    raise e1
KeyError: 'none'
>>> 
```

I further propose the `extractiter` method which returns an iterator over DataFrames, one for each subject: one row for each match, one column for each group:

``` python
>>> for df in Si.str.extractiter(named_pattern, re.VERBOSE):
...   print df
... 
   user  domain  tld
0  dave  google  com
              user domain  tld
0  maudelaperriere  gmail  com
      user domain  tld
0  tdhock5  gmail  com
    user domain  tld
0    rob  gmail  com
1  steve  gmail  com
  user domain  tld
0    a      b  com
1    c      d  com
2    e      f  com
Empty DataFrame
Columns: [user, domain, tld]
Index: []
Empty DataFrame
Columns: [user, domain, tld]
Index: []
>>> for df in Si.str.extractiter(named_pattern, re.VERBOSE):
...   print df["domain"]
... 
0    google
Name: domain, dtype: object
0    gmail
Name: domain, dtype: object
0    gmail
Name: domain, dtype: object
0    gmail
1    gmail
Name: domain, dtype: object
0    b
1    d
2    f
Name: domain, dtype: object
Series([], Name: domain, dtype: object)
Series([], Name: domain, dtype: object)
>>> 
```

@tdhock let's just keep it straightforward for now
we don't support iterators like this so it would be a big API change

OK, in that case I deleted extractiter, and added some more tests and examples for extractall.

can you rebase / squash and i'll take a look

OK @jreback this is my first time doing a rebase / squash. Did I do it correctly?

yes that looks right

- add to `api.rst`
- add a note in `v0.18.0.txt` (use this PR number as the issue number), in Enhancement
- update `text.rst`
- update test in `test_categorical` if needed (this implements `.str` as well)

TODO update docstrings

TODOs
- [x] double check api.rst
- [x] add a note in v0.18.0.txt (use this PR number as the issue number), in Enhancement
- [x] double check text.rst
- [x] add extractall tests to test_categorical? (this implements .str as well) Is this needed? There are no tests for extract in test_categorical.

.str functions are all tested in test--categorical - only the ones that need args are special cased

@tdhock need a big note in whatsnew API breaking changes to show off the prior / new behavior in `.extract`

@tdhock looks pretty good, just some minor comments.

@jorisvandenbossche comments?

I am adding a test for extractall when there are no matches at all. In that case it should return an Empty DataFrame with columns named according to group names.

@jreback would you suggest any other changes?

@jorisvandenbossche can you have a look

@sinhrks @TomAugspurger comments?

Just gave a quick skim, looks like a great addition.

@tdhock @jreback, is breaking the API to always return a DataFrame a necessary part of this change? How much messier would your code have to get to support maybe returning an Index or Series? I agree that always returning a DataFrame is nice, but maybe not nice enough to warrant breaking API without a deprecation cycle.

Could we deprecate returning an Index/Series for a release? In the code you could check the output shape a squeeze to a Series for now, and raise a FutureWarning in that case. Again I haven't looked closely, so you'll know better than me how much of a hassle this will be.

@TomAugspurger the API change on `str.extract` is not necessary, but IMHO it is just simply confusing. Depdending on the data (e.g. whether you have 1 or more capture groups), you get an Index/Series or a DataFrame. Better to just be consistent and always return a DataFrame.

We could deprecate this, but that is just kicking the can and not actually providing an upgrade path.

Agreed that the current behavior is confusing, and it bites me often. Always returning a DataFame is a better behavior.

> We could deprecate this, but that is just kicking the can and not actually providing an upgrade path.

The deprecation path would need to include a flag like `squeeze` that defaults to `True` for a release before flipping to default, and being removed further down the road. So we'll require people to change their code once before 0.19, but at least the have a chance to adapt over the 0.18 series.

I don't like introducing a keyword just for the deprecation cycle, so I'm really on the fence here. I just know that `extractall` is a widely used method.

actually we use the `expand` flag for several other method, so not w/o precedent. I suppose we could introduce here with a deprecation warning (and default it)

Yes, `expand=False` for now to match `str.split` would be better than `squeeze=True`. Is `expand` on `str.split` slated for an API change to flip to True? Would make sense to do those together, if we were wanting to flip that (I don't think I've ever intentionally kept `expand=False` on `str.split`).

ok, so here's a proposal. How about adding `expand=False` in 0.18.0; then adding deprecation warnings for both `.extract` and `.split` (shown if its an `Index/Series`) that going to change for 0.19.0.

@tdhock I think adding the parameter `expand=False` (and restoring the functionailiy for `.extract` depending on the kw), then another PR for the deprecations.

:+1:

A question: as it is a bit difficult to track in the long discussion, can somebody summarize the reasoning to change the return type to a DataFrame? Or is the only reason that is 'is confusing'? (the reason given in the whatsnew docs)

As I personally don't find this so confusing. I rather find it clear: doing an extract on a Series (Series.str.extract) gives you a Series (1 group) or a DataFrame (more groups). Note that this does 
_not_ depend on your data, but only on the regular expression you provide to the function. 

Further, the fact that the extract on an Index no longer returns an Index but a DataFrame, I don't know if this is an improvement. To be consistent with the other methods, shouldn't it rather return a Index/Multi-Index?

@jorisvandenbossche I agree that an `Index` _should_ return a `MultiIndex` (but this is not implemented, so for sure we should do that).

But, this is data dependent on the moment, which is very confusing. If you have a regex with 1 group you get a `Series`, or 2 you get a `DataFrame`. So if the passed regex is unknown to the writer at that time (say its a user variable), then you don't know what you are getting back.

We try to be very predictable, this IMHO is pretty unpredictable.

I agree that always returning a frame would be a better API. It's pretty common to use variables for regexs.

On Sat, Dec 5, 2015 at 7:47 AM, Jeff Reback notifications@github.com
wrote:

> @jorisvandenbossche I agree that an `Index` _should_ return a `MultiIndex` (but this is not implemented, so for sure we should do that).
> But, this is data dependent on the moment, which is very confusing. If you have a regex with 1 group you get a `Series`, or 2 you get a `DataFrame`. So if the passed regex is unknown to the writer at that time (say its a user variable), then you don't know what you are getting back.
> 
> ## We try to be very predictable, this IMHO is pretty unpredictable.
> 
> Reply to this email directly or view it on GitHub:
> https://github.com/pydata/pandas/pull/11386#issuecomment-162213238