Python: How to apply pandas styling for a unique ID based on comparison between two days?

Question

I am trying to color cells based on the value of the specific cell. I have a dataframe that includes data for the same product for two different dates. I want to highlight the cell for the latest date green or red depending on whether it has increased or decreased.

I am unable to figure out how to do this for each column in a way that functions as it should. It doesn't help that I am new to pandas style and am essentially learning by doing.

Any help is greatly appreciated

import pandas as pd
import numpy as np

# Create a sample dataframe (replace it with your actual data)
data = {
    'ID': ['B-1', 'B-1', 'F-1', 'F-1'],
    'Analysis Date': ['31/05/2023', '21/05/2023', '31/05/2023', '21/05/2023'],
    'Col 1': [10.91, 10.89, 7.24, 7.27],
    'Col 2': [1, 2, 0.76, 0.80]
}
df = pd.DataFrame(data)

# Set the 'Security' and 'Analysis Date' columns as the index
df.set_index(['ID', 'Analysis Date'], inplace=True)

# Define the custom function to highlight increased values using lambda
highlight_increased_risk = lambda x: ['background-color: red' if x[i] > x[i-1] else '' for i in range(len(x))]

# Apply the style function using applymap to each cell
styled_df = df.style.apply(highlight_increased_risk, axis=0)

# Display the styled dataframe
styled_df

dataframe

I am unable to find an efficient method to identify cells which need to be highlighted and to apply styling to those specific ones. Especially since I am trying to isolate comparisons based on ID and date.

I also appreciate any critiques

ayz123
– ayz123

2023-06-22 15:13:08 +00:00
Commented Jun 22, 2023 at 15:13 — ayz123
– ayz123, Commented Jun 22, 2023 at 15:13

Timeless · Accepted Answer · 2023-06-22 19:53:43Z

0

You can apply a DataFrame of CSS styles made with diff and boolean indexing :

axis : {0 or "index", 1 or "columns", None}, default 0 Apply to each column (axis=0 or 'index'), to each row (axis=1 or 'columns'), or to the entire DataFrame at once with axis=None.

def color(df):
    d = {
        False: "background-color: lightcoral", # data has decreased
        True: "background-color: lightgreen"   # data has increased
    }
    
    skey = lambda x: pd.to_datetime(x, dayfirst=True)

    diffs = (
        df.groupby(level=0, group_keys=False)
            .apply(lambda g: g.sort_index(level=1, key=skey).diff())
    )

    return diffs.gt(0).where(diffs.notnull()).fillna("").replace(d)

out = df.style.apply(color, axis=None).format(precision=2)

Output :

edited Jun 22, 2023 at 19:53

answered Jun 22, 2023 at 16:41

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python: How to apply pandas styling for a unique ID based on comparison between two days?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related