Сместите данные в листе Excel влево, чтобы удалить пустые ячейки и включить имена заголовков

Это немного сложно, но можно сделать с помощью Pandas

Используйте Pandas, чтобы сдвинуть ячейки со значениями влево.
Перед сдвигом выгрузите заголовок каждой группы в список, чтобы данные можно было вставить после сдвига.

import pandas as pd


def shift_cols_left(df_row, ncd):
    original_columns = df_row.index.tolist()

    ### Drop NaN cells
    shifted = df_row.dropna()

    cur_row = df_row.name  # Current Row in the DataFrame being compressed
    ### Create a dictionary of the Headers to be added as new columns
    ### This will lists of the original Column Headers for the data. To be inserted into the compressed DataFrame
    for col_count, col in enumerate(shifted):
        section_header = shifted.index[shifted == col].values[0]
        if 'Unnamed' not in str(section_header):  # Use actual Header names only
            cur_col = f"col{col_count}"
            if cur_col in ncd:
                ncd[cur_col] += [section_header]
            else:
                if cur_row > 0:  # Pad the list if needed
                    for x in range(cur_row):
                        if cur_col in ncd:
                            ncd[cur_col] += ['']
                        else:
                            ncd[cur_col] = ['']
                    ncd[cur_col] += [section_header]
                else:
                    ncd[cur_col] = [section_header]

    ### Shift columns with values to the left removing gaps and update column headers
    shifted.index = [original_columns[n] for n in range(shifted.count())]

    return shifted


filepath = 'newtry.xlsx'
sheet = 'Sheet1'

new_col_dict = {}

### Read the original Data from Excel
df = pd.read_excel(filepath, sheet_name=sheet)
print(f"Original DataFrame:\n{df}\n----------------------------------\n")

### Remove empty cells and shift data to the left
df1 = df.apply(shift_cols_left, args=(new_col_dict,), axis=1)
print(f"Left shifted DataFrame\n{df1}\n----------------------------------\n")

### Insert the Header detail into the DataFrame at first row then each 3rd row as necessary
loc = 0
for k, v in new_col_dict.items():
    df1.insert(loc=loc, column=k, value=pd.Series(v))
    loc += 3

### Final DataFrame with shifted data and Header Columns inserted
### Not bothering with renaming Headers as these will be dropped when writing to Excel
print(f"Left shifted DataFrame with header columns included:\n{df1}\n----------------------------------\n")

### Write the resultant DataFrame to Excel
### Drop Index and Header
with pd.ExcelWriter('newtry2.xlsx') as writer: 
    df1.to_excel(writer, sheet_name='Sheet1', index=False, header=False)

Входной лист
Лист является дубликатом того, что показано в Вопросе. Я предполагаю, что заголовки в строке 1 представляют собой объединенные ячейки.
Т.е. Ячейки A1 и B1, C1 и D1, E1 и F1, а также G1 и H1 объединяются по отдельности.

Выходной лист

Как и в примере кода, будет создан новый файл newtry2.xlsx (перезаписывающий любой существующий файл с таким именем в том же каталоге).
При необходимости устройство записи можно изменить для записи в существующий файл без перезаписи. Также можно записать его на существующий или новый лист в любом месте.

Как показано на изображении вывода вопроса, заголовки не включены.
Заголовки можно изменить/обновить в DataFrame и включить в запись в Excel, или
Заголовки можно вставлять после записи DataFrame с помощью механизма ExcelWriter.

Допустим, мы ограничены модулем openpyxl . Поэтому я думаю, что было бы лучше создать новый лист, заполняя его строка за строкой преобразованными данными, используя метод Worksheet.append. Его единственным аргументом должен быть итерируемый объект, то есть это может быть генератор, последовательно предоставляющий непустые блоки в нужном формате. Границы блоков, полученные из первой строки (заголовка), можно использовать для разделения данных по остальным.

from openpyxl import load_workbook

file_name = '/path/to/your/file.xlsx'
data_sheet = 'Data sheet title'
blocks_sheet = 'Blocks sheet title'

book = load_workbook(file_name)
data = book[data_sheet]

columns = [cell.value for cell in data[1]]
pos = [position for position, name in enumerate(columns) if name]
pos.append(len(columns))    # include the rightmost boundary
names = [name for name in columns if name]


def shrink(row):
    '''Generate a sequence of non-empty blocks preceded by the corresponding  headers'''
    for name, left, right in zip(names, pos[:-1], pos[1:]):
        if any(row[left:right]):
            yield name
            yield from row[left:right]

            
if blocks_sheet in book.sheetnames:
    del book[blocks_sheet]
blocks = book.create_sheet(blocks_sheet)
for row in data.iter_rows(2, values_only=True):
    blocks.append(shrink(row))

book.save(file_name)

Спасибо вам за вашу помощь. Во-первых, исправление, внесенное в мой вопрос, было неверным. Я наоборот пытался внести корректировку. Я решил это так.

import pandas as pd
df = pd.read_excel("newrty.xlsx", engine='openpyxl')
with pd.ExcelWriter("news.xlsx", engine='openpyxl') as writer:
    for index, row in df.iterrows():
        start_s = index + 1
        if row[0] == 1:
            start = 0
        elif row[0] == 2:
            start = 3
        elif row[0] == 3:
            start = 6
        elif row[0] == 4:
            start = 9
        data_df = pd.DataFrame(row).T
        data_df.to_excel(writer, index=False, startrow=start_s, startcol=start, header=False)
    

Я также применю ту же строку кода к другим заголовкам, встречающимся в этих строках. Убедившись в проверке заголовков в строках, я предпочёл удалить их вручную.