How can I compare two columns values and apply conditional formatting on one of the columns based on-CodePudding

I have a data frame with scores for Q2, Q3 and Q4. If Q4's score is greater than Q3, I'd like to color the cell green. If Q4's score is less than Q3, I'd like to color the cell red. If Q3 and Q4 have the same score, I'd like to leave the cell white.

Here's the data and what I attempted to do

scores = pd.DataFrame({
    'Market': ['AL', 'AR', 'AR', 'AR', 'AR'], 
    'Health Plan': ['HP1', 'HP1', 'HP2', 'HP3', 'HP4'],
    'Network': ['Medicare', 'Ambetter', 'Medicaid', 'Medicare', 'Medicare'],
    'Q2': [72,51,49,70,62], 
    'Q3': [72,60,59,75,55],
    'Q4': [84,72,75,73,62]})


def color_cells(val):
    color = 'green' if val > scores['Q3'] else 'red' if val < scores['Q3'] else 'white'
    return 'background-color: %s' % color

scores.style.applymap(color_cells, subset='Q4')
print(scores)

And this is the error I am getting

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

CodePudding user response：

I would suggest to use np.select to create a style array based on the conditions then apply the style array on the column Q4

colors = np.select(
    [scores['Q4'] > scores['Q3'], scores['Q4'] < scores['Q3']], 
    ['background: green', 'background: red'], 'background: white'
)

scores.style.apply(lambda s: colors, subset='Q4')