CODEWITHSUNDEEP
pythonpython-3.xopenpyxl
smiff
smiff

Reputation: 5

Replace missing values in excel worksheet using openpyxl module

I’m trying to replace cells in my Excel worksheet that contains hyphen “-“ with the average value between the above lying cell and the below lying cell. I’ll been trying to do this by looping through each row in column 3

 import math
 from openpyxl import load_workbook
 import openpyxl

 d_filename="Snow.xlsx"
 wb = load_workbook(d_filename)

 sheet_ranges=wb["PIT 1"]'


def interpolatrion_of_empty_cell():

for i in range(7,31):
    if i =="-":
        sheet_ranges.cell(row = i, column = 3).value = mean(i-1,i+1)
    else:
        sheet_ranges.cell(row = i, column = 3).value

wb.save(filename = d_filename)

is this just to easy to do or is it not possible with openpyxl?

cheers// Smiffo

Upvotes: 0

Views: 1548

Answers (1)

Marcin
Marcin

Reputation: 238587

The reason values are not replaced is that you use i to check if its equal to -. i is an index, not the value of a cell. Also to calculate the mean, you are using indices, not the values of top and below cells.

So you could solve this in following way:

def interpolatrion_of_empty_cell():
     for i in range(7,31):
          cell_value = sheet_ranges.cell(row=i, column=3).value
          if cell_value == "-":
               top_value = sheet_ranges.cell(row=i+1, column=3).value
               bottom_value = sheet_ranges.cell(row=i - 1, column=3).value
               sheet_ranges.cell(row=i, column=3).value = (float(top_value) + float(bottom_value))/2

Not that this may require tweaking, as it does not accout for cases where tob and bottom rows are -, not numbers, or just empty cells.

Upvotes: 3

Related Questions