I need to set the value of one column based on the value of another in a Pandas dataframe. This is the logic:
if df['c1'] == 'Value':
df['c2'] = 10
else:
df['c2'] = df['c3']
I am unable to get this to do what I want, which is to simply create a column with new values (or change the value of an existing column: either one works for me).
If I try to run the code above or if I write it as a function and use the apply method, I get the following:
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
one way to do this would be to use indexing with .loc.
Example
In the absence of an example dataframe, I'll make one up here:
import numpy as np
import pandas as pd
df = pd.DataFrame({'c1': list('abcdefg')})
df.loc[5, 'c1'] = 'Value'
>>> df
c1
0 a
1 b
2 c
3 d
4 e
5 Value
6 g
Assuming you wanted to create a new column c2, equivalent to c1 except where c1 is Value, in which case, you would like to assign it to 10:
First, you could create a new column c2, and set it to equivalent as c1, using one of the following two lines (they essentially do the same thing):
df = df.assign(c2 = df['c1'])
# OR:
df['c2'] = df['c1']
Then, find all the indices where c1 is equal to 'Value' using .loc, and assign your desired value in c2 at those indices:
df.loc[df['c1'] == 'Value', 'c2'] = 10
And you end up with this:
>>> df
c1 c2
0 a a
1 b b
2 c c
3 d d
4 e e
5 Value 10
6 g g
If, as you suggested in your question, you would perhaps sometimes just want to replace the values in the column you already have, rather than create a new column, then just skip the column creation, and do the following:
df['c1'].loc[df['c1'] == 'Value'] = 10
# or:
df.loc[df['c1'] == 'Value', 'c1'] = 10
Giving you:
>>> df
c1
0 a
1 b
2 c
3 d
4 e
5 10
6 g
You can use np.where() to set values based on a specified condition:
#df
c1 c2 c3
0 4 2 1
1 8 7 9
2 1 5 8
3 3 3 5
4 3 6 8
Now change values (or set) in column ['c2'] based on your condition.
df['c2'] = np.where(df.c1 == 8,'X', df.c3)
c1 c2 c3
0 4 1 1
1 8 X 9
2 1 8 8
3 3 5 5
4 3 8 8
try:
df['c2'] = df['c1'].apply(lambda x: 10 if x == 'Value' else x)