How do I insert a row every nth row in openpyxl?

Question

I have a workbook in which I get data from a program and I was trying to automate some of the tasks, Basically there is data for a game every 4 rows and it goes for about 13 columns. I wanted to figure out a code to insert a row every 4th row with an "AVG" function in each cell that takes the average of the 4 cells below it for each column. I'm not sure if there is anything like this available.

Answer 1

Whichever you decide to use, openpyxl or xlwings or other the method is pretty much the same. Iterate through a range covering the rows with your data at every 5th row insert a new row and update each cell with the average formula.

1. The first row for formulas will presumably be row 2 with row 1 as header row
2. Note that the total number of rows will increase as you insert new rows i.e if there are 24 rows of data then ultimately 6 new rows are inserted. If the loop only covers the a range of 24 it would leave the bottom 4 rows unprocessed. Calculate the total number of rows as number of data rows + 1/4.
   The range should have a step of 5, [formula row + 4 data rows] to jump to the next row for insert.
3. For each 'row' in the loop insert a new row. You may want to 'clear' the new row also in case it inherited any formatting from the previous row. You'll need to create the range coordinates using the row variable, e.g. it will be 2, 7, 12 etc so the row number needs to be updated to the next value for each loop.
   You can insert a new row across all columns in the sheet or just the columns with the data as above.
4. After the new row is inserted. Create a new loop to cycle through the columns on that new row and insert the formula for each cell with the correct coordinates for the row and column.

Again the row and column coordinates for both the cell.value and the AVERAGE formula need to be created from the variables of the loops.

--------Additional Information ------------
Examples on how to do this;
Openpyxl

import openpyxl as op
from openpyxl.styles import PatternFill

file = '<filename>.xlsx'
wb = op.load_workbook(file)
ws = wb['Sheet1']

cell_top_left = 'A1'
row_steps = 4
greenbg = PatternFill(start_color='FF00FF00', end_color='FF00FF00', fill_type='solid')

first_col = list(cell_top_left)[:1][0]
last_col = op.utils.cell.get_column_letter(ws.max_column)

num_col = ws.max_column
num_row = ws.max_row
num_row += int((num_row-1)/4)-3

for row in range(2, num_row, row_steps + 1):
    ws.insert_rows(row)
    for col in range(1, num_col + 1):
        col_letter = ws.cell(row=row, column=col).column_letter
        start_cell = col_letter + str(row+1)
        end_cell = col_letter + str(row+4)
        ws.cell(row, col).value = '=AVERAGE({0}:{1})'.format(start_cell, end_cell)
        ws.cell(row, col).fill = greenbg

wb.save("modded_" + file)

---

xlwings

import xlwings as xw

file = '<filename>.xlsx'

wb = xw.Book(file)
ws = wb.sheets('Sheet1')

cell_top_left = 'A1'
row_steps = 4

first_col = list(cell_top_left)[:1][0]
last_col = list(ws.range(cell_top_left).end('right').address.replace('$', ''))[:1][0]

num_col = ws.range(cell_top_left).end('right').column
num_row = ws.range(cell_top_left).end('down').row
num_row += int((num_row - 1) / 4) - 3

for row in range(2, num_row, row_steps + 1):
    str_coord = first_col + str(row) + ':' + last_col + str(row)
    ws.range(str_coord).insert('down')
    ws.range(str_coord).clear()
    for col in range(1, num_col + 1):
        start_cell = ws.range(row + 1, col).address.replace('$', '')
        end_cell = ws.range(row + 4, col).address.replace('$', '')
        ws.range((row, col)).value = '=AVERAGE({0}:{1})'.format(start_cell, end_cell)
        ws.range((row, col)).color = (0, 255, 0)

wb.save("modded_" + file)