0

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)]]
The Great
  • 7,215
  • 7
  • 40
  • 128
  • The problem starts with the very complex structure of the sheet, I'm not sure that there is a way for `pandas.read_excel` to handle that. Since `styleframe` uses it, I'm not sure there's much that `styleframe` can do. See output.xlsx after executing `import pandas as pd ; df = pd.read_excel('DUMMY_DATA_TEST.xlsx') ; df.to_excel('output.xlsx')` – DeepSpace Feb 22 '22 at 14:05
  • @DeepSpace - Ah yes. I see that now the file has read the actual raw value underneath that color. Meaning that color is chosen based on a value. Meaning, user chooses a value from dropdown and it gets converted to color automatically. Based on your code above, I see that the underlying value is read? – The Great Feb 22 '22 at 14:09
  • but with this, we can't get back to the original format of the file (where I can append the average column)? – The Great Feb 22 '22 at 14:10
  • Again, there is a bigger problem here than just reading the colors. The entire format of the sheet is lost because `pandas.read_excel` does not know how to handle the merged cells. – DeepSpace Feb 22 '22 at 14:11
  • So, what do you suggest? I didn't understand. Should I use any other read function? or nothing more can be done? – The Great Feb 22 '22 at 14:16
  • Let's continue that discussion on the github issue you created ;) – DeepSpace Feb 22 '22 at 14:28

1 Answers1

1

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]])
The Great
  • 7,215
  • 7
  • 40
  • 128
xris23
  • 353
  • 1
  • 8
  • how do I identify the cell that I want to color? because to identify the cell, I need to first recognize the color object? If you can guide me, would really be helpful. thanks, however for the suggestion – The Great Feb 22 '22 at 14:03
  • Sorry I see the shapes can be calculated as well. So you only need do enter in the average formula the sum of Region 1 till 4 / COUNT of Region 1 till 4. The red ones you marked as 10 and the tacao as 60, so that is why the total is 490 and not 460. Why don't you make zero the red drop down and 50 the tacao one? – xris23 Feb 22 '22 at 14:12
  • Sorry the dropdown values for color are different. They should not be used for average computation. Please refer the score for each color given in the post – The Great Feb 22 '22 at 14:14
  • but everywhere the iconset is the same? >= 66 green, <66 >=33 tacao and <33 red. Then you can make a formula based on this. =(COUNTIFS(Region 1 till 4,”=>33″,Region 1 till 4,”<66″)*50+COUNTIF(Region 1 till 4,<33)*0+COUNTIF(Region 1 till 4,”=>66″)*90)/COUNT(Region 1 till 4) – xris23 Feb 22 '22 at 14:21
  • sorry, may I check what do you mean by iconset? if you ask whether we will have only 3 colors. Yes, 3 colors. – The Great Feb 22 '22 at 14:21
  • Iconset are the dots you have set with conditional formatting. I have modified my previous reply. If they all have the same range, you can calculate the average. – xris23 Feb 22 '22 at 14:28
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/242270/discussion-between-the-great-and-xris23). – The Great Feb 22 '22 at 14:29
  • I just edited your answer to put the formula (in indent block) – The Great Feb 24 '22 at 02:09
  • hi @xris23 - I have similar excel problem. Will you be able to help? https://stackoverflow.com/questions/71515157/how-to-filter-and-merge-data-from-multiple-sheets-using-excel – The Great Mar 17 '22 at 15:41