11
$\begingroup$

I have values in column1, I have columns in column2. What I want to achieve: Condition: where column2 == 2 leave to be 2 if column1 < 30 elsif change to 3 if column1 > 90.

Here is what i did so far, the problem is 2 does not change to 3 where column1 > 90.

filter1 = data['column1']
for x in filter1:
    if x < 30:
        data['column2'] = data['column2'].replace([2], [2])
    else:
        data['column2'] = data['Output'].replace([2], [3])
$\endgroup$
1
  • $\begingroup$ Thanks peter for the edit! $\endgroup$ – Koko Jul 31 '19 at 15:14
12
$\begingroup$

What I want to achieve: Condition: where column2 == 2 leave to be 2 if column1 < 30 elsif change to 3 if column1 > 90

This can be simplified into where (column2 == 2 and column1 > 90) set column2 to 3. The column1 < 30 part is redundant, since the value of column2 is only going to change from 2 to 3 if column1 > 90.

In the code that you provide, you are using pandas function replace, which operates on the entire Series, as stated in the reference:

Values of the Series are replaced with other values dynamically. This differs from updating with .loc or .iloc, which require you to specify a location to update with some value.

This means that for each iteration of for x in filter1 your code performs global replacement, which is not what you want to do - you want to update the specific row of column2 that corresponds to x from column1 (which you are iterating over).

the problem is 2 does not change to 3 where column1 > 90

This is truly strange. I would expect the code you provided to have changed every instance of 2 in column2 to 3 as soon as it encountered an x >= 30, as dictated by your code conditional statement (the execution of the else branch). This discrepancy may stem from the fact that you are assigning to column2 the result of global replacement performed on the column Output (the contents of which are unknown). In any case, if you want your program to do something under a specific condition, such as x > 90, it should be explicitly stated in the code. You should also note that the statement data['column2'] = data['column2'].replace([2], [2]) achieves nothing, since 2 is being replaced with 2 and the same column is both the source and the destination.

What you could use to solve this particular task is a boolean mask (or the query method). Both are explained in an excellent manner in this question.

Using a boolean mask would be the easiest approach in your case:

mask = (data['column2'] == 2) & (data['column1'] > 90)
data['column2'][mask] = 3

The first line builds a Series of booleans (True/False) that indicate whether the supplied condition is satisfied. The second line assigns the value 3 to those rows of column2 where the mask is True.

$\endgroup$
5
  • $\begingroup$ Hi Vlad_Z, i really do appreciate your time and the insights. Thank you. However, the problem still persist. There are other values in column2 that shouldn't change, the changes should only be at places where column2 == 2. $\endgroup$ – Koko Jul 31 '19 at 15:13
  • $\begingroup$ Sad to hear that the problem persists. Could you perhaps post the modified code that didn't work for you? and a couple of rows of data in question to demonstrate the issue? I'm really intrigued as to why this didn't work on your end, since the boolean mask approach is quite a standard way of performing selective updates in pandas. $\endgroup$ – Vlad_Z Jul 31 '19 at 17:42
  • $\begingroup$ Oh mine! Having seen your post i checked again and it really worked as well. $\endgroup$ – Koko Aug 2 '19 at 14:55
  • $\begingroup$ Thank you! I'm really glad that you got your own solution going as well. $\endgroup$ – Vlad_Z Aug 2 '19 at 15:47
  • $\begingroup$ @Vlad_Z I get A value is trying to be set on a copy of a slice from a DataFrame, what I guess happens is that the mask is applied to a copy. However it seems to work. $\endgroup$ – loco.loop Aug 10 '20 at 13:59
8
$\begingroup$

I've had success approaching this in a slightly different way.

import numpy as np

data['column2'] = np.where((data['column1'] < 30)
                           & (data['column2'] ==2), #Identifies the case to apply to
                           data['column2'],      #This is the value that is inserted
                           data['column2'])      #This is the column that is affected
data['column2'] = np.where((data['column1'] > 90)
                           & (data['column2'] ==2), #For rows with column1 > 90
                           data['column3'],      #We place column3 values
                           data['column2'])      #In column two

This is a little wordier than a loop, but I've found it to be the most intuitive way to do this sort of data manipulation with pandas.

$\endgroup$
3
  • $\begingroup$ Thank you Abram, the code is not quite there yet because there are other values in column2 that shouldn't change, the changes should only be at places where column2 == 2. $\endgroup$ – Koko Jul 31 '19 at 15:12
  • $\begingroup$ I modified the code a little, this should leave those other values alone and only change column2 where it is equal to 2. $\endgroup$ – Abram Moats Jul 31 '19 at 15:19
  • $\begingroup$ Thank you so much, Abram. It sure worked as desired. $\endgroup$ – Koko Jul 31 '19 at 17:03
8
$\begingroup$

While i was waiting for answer, i got something working too

def myfunc(x,y):
    if x <= 30 and y == 2:
        return y
    elif x > 90 and y == 2:
        return y + 1
    else:
        return y

data['column2'] = data.apply(lambda x: myfunc(x.column1, x.column2), axis=1)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.