How do I use Python to validate the product column versus its corresponding tax by the xlsx file

Question

Hello, everyone, good evening! I’m new to the community and need help to solve a code.

I have a spreadsheet in Excel with several products and their respective tax rates. Only!

Therefore, I need to return products that do not have aliquot or that have incorrect aliquot. Example: For the perfume product the correct rate is 18%, if the spreadsheet has != constando type 16% this product should be the output of my program and its incorrect rate.

On this basis of Excel I have 50,000 lines with registered products, I need to find a more automatic and quick way to execute this type of validation that can read line by line, returning only what is invalid.

I’m starting to learn Python now (total beginner) so I’d like the help to come in this language.

Import pandas as pd
#importando a planilha para um panda Datafreme
df = pd.read_excel('plan1.xls',sheet_name='Sheet1')

#Selecionando os produto em um set
CategoriaProdutos = set(list(df['CategoriaProduto']))

I could only write so far!

Since the same validation statement should be executed several times in a row for the xlsx file products, I understand that I will need a repeat loop for or while that can execute the same code block to return the desired condition.

'

Answer 1

for excel file analysis I recommend openpyxl: https://openpyxl.readthedocs.io/en/stable/usage.html

Below is a simple use of this lib.

>>> from openpyxl import load_workbook
>>> wb = load_workbook(filename = 'empty_book.xlsx')
>>> sheet_ranges = wb['range names']
>>> print(sheet_ranges['D18'].value)
3

In your specific case, in a very simple way you would do something:

prosseguir = True
x = 1
while prosseguir == True:
  campo = sheet_ranges["D" + x].value
  if campo == "":
    break
  
  # caso o valor do campo seja em float ex: 0.18, necessário converter
  elif float(campo) == 0.18:
    # aqui neste caso você teria que informar o campo com o nome do produto
    # vamos supor que esteja na coluna E
    print("Produto fora da alíquota: {}".format(sheet_ranges["E" + x].value))

  # caso texto ex: 16%
  elif campo != "18%":
    # aqui neste caso você teria que informar o campo com o nome do produto
    # vamos supor que esteja na coluna E
    print("Produto fora da alíquota: {}".format(sheet_ranges["E" + x].value))

  x += 1

I hope I helped, I rode in the simplest way I could think of