CODEWITHSUNDEEP
pythonpython-3.xxlsxwriter
Thomas meriaux
Thomas meriaux

Reputation: 87

xlsxwriter conditional formatting icon_set with mid value= 0 not showing proper arrow

The issue : I want to apply conditional formatting icon_set using xlsx to a column but do not get the right arrows for the right values

This is my desired output :

desired output

This is my current output:

enter image description here

This is my code:

writer.sheets[sheet].conditional_format('J54:K200', {'type': 'icon_set',
                                                         'icon_style': '3_arrows',

                                                         'icons': [
                  {'criteria': '>=', 'type': 'number', 'value': 1},
                  {'criteria': '>=', 'type': 'number', 'value': 0},
                  {'criteria': '<', 'type': 'number', 'value': -1}

                                                         ]}
                                            )

This is what I have looked at :

Besides similar questions here, this is what I have done :

I looked at Excel for the formula and compared to my own work, to start from my output, and figure out the correct rule. The closest I got so far is that when I change my icons 'value'to 2, 1, 0 respectively, I get the 1 to have the middle orange arrow:

when the 1ns get the orange arrow

This tells me that my equality must be correct, yet it doesn't produce the expected result. Thanks for any help provided!

Upvotes: 5

Views: 1224

Answers (1)

patrickjlong1
patrickjlong1

Reputation: 3823

If you eliminate the {'criteria': '>=', 'type': 'number', 'value': 0}, from your code it should work fine. I have a reproducible example of this below with the expected output.

import pandas as pd
import numpy as np

#Creating a sample dataframe for example demonstration
df = pd.DataFrame({'col1': [0, -1, 10, -2], 'col2': [-11, 0, -3, 1]})


writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)

writer.sheets['Sheet1'].conditional_format('A2:B5', {'type': 'icon_set',
                                                         'icon_style': '3_arrows',
                                                         'icons': [
                  {'criteria': '>=', 'type': 'number', 'value': 0.001},
                  {'criteria': '<=', 'type': 'number', 'value': -0.001}
                                                         ]}
                                            )


writer.save()

Expected test.xlsx:

Expected Output

Upvotes: 3

Related Questions