DataFrame.replace(dict) has weird behaviour in some cases

[jankatins]

import pandas as pd
df = pd.DataFrame({"color":[1,2,3,4]})
print df
   color
0      1
1      2
2      3
3      4
print df.replace({"color":{"1":"2","3":"4",}}) # works but shouldn't?
  color
0     2
1     2
2     4
3     4
print df.replace({"color":{"1":"2","2":"3","3":"4","4":"5"}}) # strange
  color
0     2
1     4
2     3
3     5
print df.replace({"color":{1:"2",2:"3",3:"4",4:"5"}}) # works by replacing each cell once
  color
0     2
1     3
2     4
3     5

df = pd.DataFrame({"color":["1","2","3","4"]})
print df
  color
0     1
1     2
2     3
3     4
print df.replace({"color":{"1":"2","3":"4",}}) # works
  color
0     2
1     2
2     4
3     4
print df.replace({"color":{"1":"2","2":"3","3":"4","4":"5"}}) # works not
  color
0     3
1     3
2     5
3     5
print df.replace({"color":{1:"2",2:"3",3:"4",4:"5"}}) # works as expected: shouldn't replace anything!
  color
0     1
1     2
2     3
3     4

So, my expected behaviour would be:

• don't replace a cell if the type of the cell does not match the key (as it is the case when a string cell is replaced by a int key)
• if a value of a cell is replaced, the cell shouldn't be replaced a second time in the same replace call

I found the problem when I tried to match string values to colors and got blown up color values: like {"3":"#123456","4":"#000000"} wouldn't convert "3" into "#123#00000056"

Edit: insert string cell cases and my expected behaviour and deleted the intial comments which had these examples

[jreback]

@cpcloud can you look at this?

[cpcloud]

yep

[cpcloud]

I guess I'm mr. replace now

[cpcloud]

:)

[cpcloud]

@JanSchulz @jreback As I'm sure you both know

df.replace({"color":{"1":"2","2":"3","3":"4","4":"5"}}) 

is not well-defined. For example, replace might replace '1' with '2' first, resulting in [2, 2, 3, 4] then replace '2' with '3' and so on ...

[cpcloud]

What should pandas do here? I don't think it's easy to detect this kind of "chained" replacement. I suppose you could keep track of and incrementally replace not sure if that's worth it tho

[cpcloud]

With a non-nested dict like

df.replace({'1': '2', '2': '3'})

exactly what I described above is happening

[jreback]

I think u should raise on a dict that had keys and values overlap

however an ordereddict would be ok

[jreback]

I think if

set(d.keys()) & set(d.values()) is not empty then u raise

[cpcloud]

ordereddict would still have the same problem, since you'll just end up replace each current value with the next replacement

[cpcloud]

suppose they're iterated over in order. then

1->2 [2, 2, 3, 4]
2->3 [3, 3, 3, 4]
3->4 [4, 4, 4, 4]
4->5 [5, 5, 5, 5]

[jreback]

ok then just raise if their is overlap then
much simpler

[cpcloud]

yep good call

[cpcloud]

Hm okay it seems to be only happening with strings .. . let me investigate some more

[jankatins]

I would find it interesting if the individual replacements are not done one after another to the whole row but all together so that that [1,2,3,4,5] ends up as [2,3,4,5,6].

Right now (and if that raises),I have to figure out the replacement order in python when I construct a replacement dict (probably via a loop), but I would like to have the code which does that in pandas so that it is fast.

[cpcloud]

Yep. I'll check it out tonight. It should work the way you expect, since it works for ints.

[cpcloud]

It looks like there's a somewhat subtle bug here. It's only apparent in certain cases (dict replace with a nested dict).

This "works":

In [6]: df = DataFrame({'a': range(4)})

In [7]: df.replace({'a': {0: 1, 1: 2, 2: 3, 3: 4}})
Out[7]:
   a
0  1
1  2
2  3
3  4

[4 rows x 1 columns]

But this is broken (it shouldn't return the same as the previous call to replace:

In [8]: df = DataFrame({'a': permutation(4)})

In [9]: df
Out[9]:
   a
0  1
1  0
2  3
3  2

[4 rows x 1 columns]

In [10]: df.replace({'a': {0: 1, 1: 2, 2: 3, 3: 4}})
Out[10]:
   a
0  1
1  2
2  3
3  4

[4 rows x 1 columns]

So, in fact, it doesn't work except in this trivial case, which inadvertently depends on how the dict is ordered.

I vote for banning this as @jreback suggested above by raising if the keys and values overlap.

[jreback]

agreed
punt to the user they can always iteratively replace at a higher level

[cpcloud]

i'm kind of down on these nested dict replaces right now 😡 (probably just debugging anger) Is there a use case for them other than convenience?

[cpcloud]

@JanSchulz If you want to do this kind of replacement you can do

In [7]: df = DataFrame({'a': permutation(4)})

In [8]: df
Out[8]:
   a
0  3
1  1
2  2
3  0

[4 rows x 1 columns]

In [9]: df['a'] = df.a.replace({0: 1, 1: 2, 3: 4, 2: 3})

In [10]: df
Out[10]:
   a
0  4
1  2
2  3
3  1

[4 rows x 1 columns]

[jankatins]

So given a replacementdict, where where keys and values overlap (the above example), df.replace({col: replacementdict}) throws and error but df.col.replace(replacementdict) does not? Wouldn't it then be easier to switch to a loop internally than throwing an error in some data specific cases (read: not obvious and only reproduceable with certain data sets)?

[cpcloud]

That seems like it should be possible I'll investigate tonight.

[jankatins]

@cpcloud This issue is closed, but your last comment indicates that something is still to be done here.

[auxiliary]

Any updates on this? Apparently it shouldn't be closed.