ValueError: could not convert string to float: 'Abs sens value' and TypeError: unsupported operand type(s) for -: 'float' and 'str'

Question

I am creating a data treatment for my thesis. When I print str1 values like 2.802176590012075 come up but I can't convert then to float or use it in operations. Any help in understanding the issue would be deeply appreciated. The excel file has values like 2,802176590012075.

import openpyxl

# Give the location of the file
path = "C:\\Users\\Acer\\.spyder-py3\\output_sens_1660[K]_1.7[atm]_phi=0.5_SORTED_descending_DATA_FILE_Perturbation=0.5_.xlsx"
  
# to open the workbook 
# workbook object is created
wb_obj = openpyxl.load_workbook(path)
sheet_obj = wb_obj.active

e=0.55

wb_obj.save(filename="teste.xlsx")
i=0
L=int(len(sheet_obj['D']))
erro=[]*L

for cell in sheet_obj['D']:
    i=i+1
    str1=cell.value
    # changing ',' to '.' 
    def Replace(str1):
        maketrans = str1.maketrans
        final = str1[i].translate(maketrans(',.', '.,', ' '))
        return final.replace(',', ", ")
    x=float(str1)
    #desired operation
    erro[i]=1-(e-str1)/e

Answer 1

The fastest :

i=1

Change the starting index from 0 (the first line : the header cell) to 1 (the second line : the first data cell after the header). But it will impact your erro variable : the length of the column 'D' is the number of cells of content, but the number of data cells is that minus 1. But that means that your indices are shifted by one, which is a source of future errors.

So I recommend you to instead skip the first line in the loop :

for cell in sheet_obj['D']:
    i=i+1
    if i == 1:
        continue  # skip the header
    str1=cell.value

I frequently have to work with Excel files, and I prefer to use lengthy names to not be confused, and differentiate between indices and numbers :

column_cells = sheet_obj['D']
column_cells_count = len(column_cells)
header_line_number = 1
starting_scan_line_number = 1

for current_index in range(column_cells_count):
   current_line_number = current_index + 1
   if current_line_number == header_line_number:

    # skip the header row       
    continue

   else:
       ...

Finally, I don't think your erro variable has to value you meant it to have :

>>> L = 4
>>> [] * L  # multiplying an empty list by an integer
[]  # gives an empty list

If what you want is a list of lists, do NOT use [[]] * L but use instead [[] for _ in range(L)] because Lists are mutable, and multiplying a list by a number doesn't copy its elements.