python对excel数据列数据进行拆分组合

通过python代码如何实现下面图片效果，处理完输出excel文件

问题遇到的现象和发生背景

我想要达到的结果

效果图：

代码实现：

# -*- coding: utf-8 -*-
# @Time    : 2022/8/9 14:54
# @Author  : wn
# @Description:
import xlrd
import xlwt


def run(file_name):
    f = xlwt.Workbook()  # 创建工作簿
    new_sheet = f.add_sheet(u'sheet1', cell_overwrite_ok=True)  # 创建sheet
    cols = ['系统订单号', '运输方式', '发货产品自定义SKU', '发货产品数量', '发货时间']
    for col in range(0, len(cols)):
        new_sheet.write(0, col, cols[col])

    # 打开文件
    workbook = xlrd.open_workbook(file_name)
    # 根据sheet索引或者名称获取sheet内容
    sheet1 = workbook.sheet_by_name('Sheet1')
    index = 0
    for now in range(1, sheet1.nrows):
        skus = sheet1.cell(now, 2).value.split(',')
        counts = sheet1.cell(now, 3).value.split(',')
        for sku, count in zip(skus, counts):
            index += 1
            cols = [index, sheet1.cell(now, 1).value, sku, count, sheet1.cell(now, 4).value]
            for col in range(0, len(cols)):
                new_sheet.write(index, col, cols[col])
    f.save('ret.xlsx')  # 保存文件


if __name__ == '__main__':
    run('./test.xlsx')