ENH: add regex functionality to DataFrame.replace
State: closed

addresses #2285. cc @jreback and #3582.


Labels:

Comments:
if you are passed a non-compiled regex, e.g. `r'b'` can you tell this is a regex or do you need to pass `regex=True`?

the difference between `r'b'` and `'b'` is at the syntactic level (sugar, raw strings are just a way to avoid 1e9 backslashes) not at the representation level (unlike e.g., `u'\u22ee'`, which indicates a representation level change)  so u must pass `regex=True`.

ok in order to use a regex I have to pass to_replace=my_regex, and `regex=True` (or pass a compiled regex)

alternatively could make `to_replace` default to None, and allow thru an actual regex in `regex=myregex`?

so:

```
df.replace(value='b',regex='a')
```

is would be equiv of:

```
df.replace('a','b',regex=True)
```

?

I chose to not detect compiled regexes, so those must also have a `regex=True` ATM. I will change that and make them detect. Currently regex can only be bool. I thought that would place less of a burden on the user. I know having args with many type options can sometimes tax my working memory and this method has a bunch of variable type arguments, but I like your suggestion. will change. will also allows rege to be compiled.

@jreback Requested functionality added, along with more tests for it. `regex`/`value` calling works and now `to_replace` can be a regex (if it's compiled) without having to pass `regex=True`. Need to add release notes/few notes to docs.

possible to change this to 0.11.1 or can I add `v0.12.0.txt`?

@y-p what do you think, 0.11.1 or 0.12?

I take that back. Probably 2 API changes should be left for 12.0. I also need a bit more time to document interpolate because of this. 

@jreback @y-p This API might be more drastic than I thought, (exposing `interpolate` and disallowing `value=None` unless `to_replace` is a `dict`). The tests suggest that `replace` with `value=None` is equivalent to calling `fillna` with `method=a_valid_method` which does nothing (at least on simple frames), BUT it does break a lot of tests which makes me think there might be people using it in this way. Well, okay, not a **lot** of tests but enough to make me a bit dubious.

ok how about we add your new functionality
and the interpolate API change for 0.12?

cool. will also allow me doc the rlnshp btwn fillna/interpolate/replace more thoroughly.

oh sweetness, got nested dicts of regexes working, i.e.,

``` python
df = DataFrame({'a': list(letters[:4]), 'b': list(letters[4:8]), 'c': range(4)})
df.replace({'b': {'.*e.*': nan}}, regex=True)
# or
df.replace(regex={'b': {'.*e.*': nan}})
```

Pushing even though the next build will fail because of 2 empty tests, just in case someone wants to play with it.

@jreback So far, I'm not seeing the point of having interpolate and replace since they are virtually the same thing. I could see interpolate being a `DataFrame` version of the `Series` version, but keeping interpolate along with the new `replace` seems pointless. If we are going to keep it, `int` dtypes must be converted to `float64` since the padding/filling functions in `common` don't work with ints. For now I'll assume the latter is what u want.

I agree, though `interpolate` functionaily is really encompassesd in `fillna`

And I would be ok with removing the `method` arg on `replace` for that matter then

interpolating is just a form of filling

okay. sounds good. will remove `method` and no new `interpolate` frame method will be added.

I've also added an `infer_types` argument that allows conversion to a "better" type if possible, e.g., a regular expression replaces an entire column with some numeric value.

and forever more you get to be `mr. replace`, fixing bugs.....

i hope that is a good thing...fyi goodbye `limit` arg as well, it was only used in interpolate

yep :)

so you r going to do a pr for 0.11.1

then all this stuff in separate for 0.12?

oh crap...i thought u meant combine them in your previous message :( oh well, git cherry pick here i come.

although the current state of regex replace gh is solid, the failing tests have to do with interpolate; i will remove them so u can merge and then a separate pr for the stuff we just discussed.

sorry for the confusion...go ahead and leave that for 0.12 (the interpolate stuff)...that is API change, while your other stuff is just added functionaility...(and seems to be done anyhow)

I am a little iffy on the `infer_types` kw, why do we need that in here?

a string -> string replacement (that is actually a number)?

No to your last question. Here's an example:

``` python
from string import ascii_letters as ltrs
a, b, c = list(ltrs[:4]), list(ltrs[4:8]), list(ltrs[:3]) + [4]
df = DataFrame({'a': a, 'b': b, 'c': c})
df.replace(regex={'c': {'[a-c]': nan}}, infer_types=True, inplace=True)
print df.c.dtype # should be float64
```

(This won't work yet, I forgot to add the `infer_types` argument in the recursive calls to `replace`, (about to push the fix)).

I think you can dispense with it, and just do a `df.convert_objects()` at the end. The `convert_objects` with the arguments are pretty forceful, all I think you need is to do soft-conversions. E.g. a column is full of floats but happens to be `object` dtype. Try with this first. 

Right now there's a call into the blkmgr method `convert`. Is that what u mean by soft-conversions? If not, how do I do those, or do u just mean get rid of the arg altogether.

```
 In [10]: df = DataFrame([[nan,1.,1.],[nan,1.,'foo']],dtype=object)

In [11]: df
Out[11]: 
     0  1    2
0  NaN  1    1
1  NaN  1  foo

In [12]: df.dtypes
Out[12]: 
0    object
1    object
2    object
dtype: object

In [13]: df.convert_objects().dtypes
Out[13]: 
0    float64
1    float64
2     object
dtype: object
```

sure ok, will remove the param.

``convert_objects` just call the block method `convert`, so you can do it there too (just no need to pass any of the options, let them default)

I dont think you need this (though if you wanted to get really fancy, maybe, but shouldn't be by default)

```
In [14]: df = DataFrame([['1','1.']])

In [15]: df
Out[15]: 
   0   1
0  1  1.

In [16]: df.dtypes
Out[16]: 
0    object
1    object
dtype: object

In [17]: df.convert_objects()
Out[17]: 
   0   1
0  1  1.

In [18]: df.convert_objects(convert_numeric=True)
Out[18]: 
   0  1
0  1  1

In [19]: df.convert_objects(convert_numeric=True).dtypes
Out[19]: 
0      int64
1    float64
dtype: object
```