Search code examples
pythonpandaspandas-styles

Apply dataframe style to specific columns on specific row indices

Context: I'd like to apply dataframe styles to specific rows and columns. For that, let's say we have a dataframe:

cmap_red_green = LinearSegmentedColormap.from_list(
    name='red_green_gradient', 
    colors=['#F28068','#FFFFFF','#ADF2C7','#4DCA7C']
)
tt = pd.read_excel(
        file_path,
        engine='openpyxl',
        header=[0,1],
        index_col=[0,1],
        sheet_name='test')

How could I make it to look like this? Basically I'd have to choose the column and rows to do so and apply the styles.

Test

Attempts:

   format_dict = {col_name:'{:,.0f}' for col_name in tt.select_dtypes(float).columns}
    
        (tt.style
        .background_gradient(
            cmap=cmap_red_green,
            axis=None)
        .format(format_dict)))

But I keep getting "Could not convert "FLAT" with type str: tried to conver to double".

So there are several things here that may not work as planned: First, (MultiIndex_1, X) should be converted to double like so {:.0f}. Then, the (MultiIndex_1, Percentage) should be {:.0%} )not mandatory). For this, only the cells that have integers in them should be converted ("FLAT" wouldn't work). Then, color in the cell values for the percentage columns with a gradient. Is there a way to do all of this?

Solution

  • Using get_loc_level and pd.IndexSlice worked for me:

    idx = pd.IndexSlice
(df.style
        .format("{:.0f}")
        .format("{:.0%}",subset=idx[:, idx[:,'Percentage']])
        .background_gradient(
            subset = df.columns.get_loc_level('Percentage',level=1)[0],
            cmap=cmap_green_red,
            axis=1
        )