Pandas change column value based on other column with lambda function

December 7, 2021

Trying to replicate a simple Excel function in pandas, with no success. Haven’t tried np.where() yet, as I want to learn lambda functions and rely less on imports where possible.

Function to replicate:

=+IF([@[Coupa Type]]="freeform","Freeform","Structured PO")

Lambda I tested and works:

lambdatest = lambda x: f"{x} is even" if x % 2 == 0 else f"{x} is odd"

Lambda for pandas which is not working:

test = raw[["Coupa Type", "Structured Pos"]]
test["Structured Pos"] = test.apply(
    lambda x: "Freeform" if test["Coupa Type"] == "freeform" else "Structured PO"
)

Error:

The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

My guess that it’s trying to evaluate the entire column instead of line by line, how do I fix this?

>Solution :

I this you need to add axis=1 to your apply() call, to make the lambda function be executed for each row, instead of for each column, which is the default:

test["Structured Pos"] = test.apply(
    lambda x: "Freeform" if x["Coupa Type"] == "freeform" else "Structured PO",
    axis=1,
)

(You also need to use x["Coupa Type"] instead of test["Coupa Type"] in the Lambda function, as I’ve done above.)

A more efficient solution for this case though would be to do something link this:

test["Structured Pos"] = test["Coupa Type"].map({"freeform": "Freeform"}).fillna("Structured PO")

…because map replaces all values in the series that are keys in the dictionary with the values of the dictionary, and values in the Series that aren’t in keys in the dictionary, it replaces with NaN, so you can use fillna to supply the default.