How can i make specific styling according to my column name (pandas)

user_17694926 0
Viewed 355

The data looks like the below

Code  Col1  Col2    col3   col4   col5  
0    123     5      66       1.7    7  
1    123      1     4        12     -8     
2    123      9      5     -7       0    
3    123      34.4    0     4.789   2

i should be able to ignore applying the style on some column(example in this case col1) doesn't have to be always col1 i should be able to ignore it by specifying the name of the column.

Not every column should have the same style, example for col 2 and 5 i want to color in green positive values and in red negative values however for col3 i want to color in purple values below 50 and yellow the rest

The actual dataset has dozens of columns which different conditions to each regarding the coloring code which is function of column name.

what i have tried:

import pandas as pd
import numpy as np

df = pd.read_excel('Data.xls', sheet_name='test')

styler = df.style

def _color_red_or_green(val):
    color = 'red' if val < 0 else 'green'
    return 'color: %s' % color


styler.applymap(_color_red_or_green)

styler.to_excel('Output.xlsx')

but this doesn't give me any way to specify column name although it did color all my data red or green, i tried to pass the column name as parameter for _color_red_or_green

for col in df.dtypes.items():
    styler.applymap(_color_red_or_green(col[0]))

and adjusted the function accordingly but then had an exception TypeError: the first argument must be callable on styler.to_excel('Output.xlsx') line.

pandas-styles pandas dataframe excel python
1 Answers

From version 1.3.0, Pandas applymap accepts a subset parameter:

subset : label, array-like, IndexSlice, optional
A valid 2d input to DataFrame.loc[], or, in the case of a 1d input or single key, to DataFrame.loc[:, ] where the columns are prioritised, to limit data to before applying the function.

So, in order, for instance, to colorize "Col1" only in the Excel output file, you can modify your code like this:

styler.applymap(_color_red_or_green, subset=["Col1"])

From there, you could define the following function:

def colorize(df, cols):
    def _color_red_or_green(val):
        color = "red" if val < 0 else "green"
        return "color: %s" % color

    styler = df.style

    styler.applymap(_color_red_or_green, subset=cols)

    styler.to_excel("Output.xlsx")

And then call it with the dataframe and the columns of your choice:

colorize(df, ["Col1", "col3"])

Which outputs an Excel file with both "Col1" en "col3" values painted as green.

user_112460560
Related