Iterate through an excel file and randomize the column 3 data with the values from Column 1 and Column 2 - Python

Question

I am writing a python code to read all the data in the xls file, iterate through all the elements in the excel file and randomize the C3 value for all the c3 elements within C1 and C2 value range. I have an issue with my current python code as only the last element in the column c3 is randomized. I am able to iterate through all the elements but have trouble randomizing the values for all the elements in column3.

import xlrd
from xlutils.copy import copy
import random

book = xlrd.open_workbook('temp.xlsx')
sheet = book.sheet_by_index(0)
nbook = copy(book)
nsheet = nbook.get_sheet(0)

for i in range(0, sheet.nrows):
    for j in range(0,sheet.ncols):
        min = sheet.cell_value(j,1)
        max = sheet.cell_value(j,2)

random = random.randint(min,max)
nsheet.write(i, 3, random)
nbook.save('new_temp.xlsx')

Answer 1

The issue with your code is that you are overwriting the random value in each iteration of the inner loop, and you're only updating the last row of the Excel file with the randomized value. To randomize the values for all elements in column C3 within the C1 and C2 value range, you should compute the random value inside the inner loop and then write it to the corresponding row in column C3. Here's the corrected code:

import xlrd
from xlutils.copy import copy
import random

book = xlrd.open_workbook('temp.xlsx')
sheet = book.sheet_by_index(0)
nbook = copy(book)
nsheet = nbook.get_sheet(0)

for i in range(0, sheet.nrows):
    min_val = sheet.cell_value(i, 1)  # Get the C1 value for the current row
    max_val = sheet.cell_value(i, 2)  # Get the C2 value for the current row

    # Randomize the C3 value for the current row within the C1 and C2 value range
    random_c3 = random.randint(min_val, max_val)

    # Write the randomized C3 value to the corresponding cell in column C3
    nsheet.write(i, 3, random_c3)

nbook.save('new_temp.xlsx')