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.
data mining - Pandas change value of a column based another column condition - Data Science Stack Exchange
python - Change values in one column on the basis of the values in another column - Stack Overflow
how to update a pandas dataframe column value, when a specific string appears in another column?
python - Replace column value based on value in other column, for all rows in a pandas dataframe - Stack Overflow
Original range:
| old_items | new_items |
|---|---|
| item1 | item6 |
| item2 | 0 |
| item3 | item7 |
| item4 | 0 |
| item5 | item8 |
Desired output:
| old_items | new_items |
|---|---|
| item6 | item6 |
| item2 | 0 |
| item7 | item7 |
| item4 | 0 |
| item8 | item8 |
My stupid solution:
old_items = list(df['old_items'])
new_items = list(df['new_items'])
proper_items = []
for x in range(len(old_items)):
if new_items[x] != 0:
proper_items.append(new_items[x])
else:
proper_items.append(old_items[x])
df['old_items'] = proper_itemsWhat 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.
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.
One option is to use Python's slicing and indexing features to logically evaluate the places where your condition holds and overwrite the data there.
Assuming you can load your data directly into pandas with pandas.read_csv then the following code might be helpful for you.
import pandas
df = pandas.read_csv("test.csv")
df.loc[df.ID == 103, 'FirstName'] = "Matt"
df.loc[df.ID == 103, 'LastName'] = "Jones"
As mentioned in the comments, you can also do the assignment to both columns in one shot:
df.loc[df.ID == 103, ['FirstName', 'LastName']] = 'Matt', 'Jones'
Note that you'll need pandas version 0.11 or newer to make use of loc for overwrite assignment operations. Indeed, for older versions like 0.8 (despite what critics of chained assignment may say), chained assignment is the correct way to do it, hence why it's useful to know about even if it should be avoided in more modern versions of pandas.
Another way to do it is to use what is called chained assignment. The behavior of this is less stable and so it is not considered the best solution (it is explicitly discouraged in the docs), but it is useful to know about:
import pandas
df = pandas.read_csv("test.csv")
df['FirstName'][df.ID == 103] = "Matt"
df['LastName'][df.ID == 103] = "Jones"
You can use map, it can map vales from a dictonairy or even a custom function.
Suppose this is your df:
df = pd.DataFrame({"ID":[103,104], "First_Name":["a","c"], "Last_Name":["b","d"]})
ID First_Name Last_Name
0 103 a b
1 104 c d
Create the dicts:
fnames = {103: "Matt", 104: "Mr"}
lnames = {103: "Jones", 104: "X"}
And map:
df['First_Name'] = df['ID'].map(fnames)
df['Last_Name'] = df['ID'].map(lnames)
The result will be:
ID First_Name Last_Name
0 103 Matt Jones
1 104 Mr X
Or use a custom function:
names = {103: ("Matt", "Jones"), 104: ("Mr", "X")}
df['First_Name'] = df['ID'].map(lambda x: names[x][0])
So, i've figured out how to use the pandas apply method to update/change the values of a column, row-wise based on multiple comparisons like this:
# for each row, if the value of both 'columns to check' are 'SOME STRING', change to 'NEW STRING # otherwise leave it as is my_df ['column_to_change'] = df.apply(lambda row: 'NEW STRING' if row['column_to_check_1'] and row['column_to_check_2'] == 'SOME STRING' else row['column_to_change'], axis=1)
Now, I can't figure out how to expand that beyond simple comparison operators. The specific example I'm trying to solve is:
" for each row, if the string value in COLUMN A contains 'foo', change the value in COLUMN B to 'bar', otherwise leave it as is"
I think this is all right, except the ##parts between the hashmarks##
my_df ['columb_b'] = df.apply(lambda row: 'bar' if ##column A contains 'foo'## else row['columb_b'], axis=1)