using .style to conditionally format cells in pandas data frame

Question

I'm trying to use .style to conditionally format records where the actual numbers are smaller than the 'Budget' column.

I've tried using the code below:

percent_scrap = (percent_scrap.style
    .format("{0:,.2f}%")
    .apply(lambda x: x == 'background-color: red'
        if x > x['Bud Yield']
        else '',
        axis = 1
    )
)

but the lambda function produces the error:

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

I've tried creating a "mask" dataframe with False being mapped to 'background-color: red' and then passing it to the style function but could not figure out how to do this properly either.

I'm sure there's a better way.

Desired output:

Answer 1

You're lambda function mixes returning a single element and a test over an array object, hence the error.

You would be better off using something like:

styler.apply(lambda s: np.where(s>df["budget"], "color:red", None), axis=0)

This will evaluate each column as a series, s, compared against the dataframe column budget and return an array of styles to be applied for each of those columns.

You can amend the apply method to exclude the budget column:

styler.apply(lambda s: np.where(s>df["budget"], "color:red", None), axis=0, subset=[col for col in df.columns if col != "budget"])