pythonexcelpandasdataframestyleframe

How to read colored object in excel using python and apply formula?

I have an excel file like as shown below. File can be downloaded from the link here

enter image description here

each color has a score. ex: red color = 0, green color = 90 and Tacao = 50

So, for each row, I would like to compute the average score and store it in a new column.

For instance,

1st row contains 4 green, 4 red, 2 tacao

2nd row contains 2 green, 3 red, 3 tacao

based on the score for each color,

1st row average = 46 (obtained by the formula = ((4 * 90) + (4 * 0) + (2 * 50))/10. So, total is 460. divided by 10 colors = 46)

2nd row average = 41.25

I expect my output to be like as shown below

enter image description here

I tried the below but it doesn't work. file itself doesn't get read properly.

from styleframe import StyleFrame, utils
# from StyleFrame import StyleFrame, utils (if using version < 3.X)

sf = StyleFrame.read_excel('DUMMY_DATA_TEST.xlsx', read_style=True)
print(sf)
sf = sf[[col for col in sf.columns if col.style.fill.fgColor.rgb in ('#FF0000', utils.colors.red)]]
Solution

  • Why not work with conditional formatting to color the whole cell? Then calculate in the cell the output and put conditional formatting on it. Then you only need to calculate the average.

    Edit: The cells are iconsets conditional formatting. They can be calculated as well because there is a value behind. Because the chosen value of the drop down is not equal to the assigned value of the colors, you can count the colors and multiply them with the assigned value of the color.

    The formula to calculate the average is:

    =(COUNTIFS(Table22[@[Column1]:[Column16]];">=33";Table22[@[Column1]:[Column16]];"<66")*50+COUNTIF(Table22[@[Column1]:[Column16]];">66")*90)/COUNT(Table22[@[Column1]:[Column16]])