python 列中字符串值的部分名称

我在A列中有一长串名称，在B列和C列中有其x，y坐标。我希望脚本找到：

A列中的名称的部分字符串匹配，并且还挑选具有相同y坐标的名称：
1.从x坐标中微调最大值和最小值
1.将“start”追加到最大x值单元格名称。将“end”追加到最小x值单元格名称
Sheet

import xlwings as xw

  wb = xw.books.active

  def rename():

    sht = wb.sheets['new Sheet']

    for cell in range('A2').expand('down'):

      if cell.value == 'DAD': 

        #includes 'DAD' and have the same Y coordinates

        cell.max(range('B2')).value = 'DAD_start'

        #After max value the next two should have the same name
        Cell(2).value = 'DAD_2' 
        Cell(3).value = 'DAD_2'
        #Repeat the step until reach the last min value
        Cell(4).value = 'DAD_3'
        Cell(5).value = 'DAD_3'
        cell.min(range('B2')).value = 'DAD_End'

您的需求和您正在使用的数据更加清晰，因此现在我可以给予一个更好的示例。
代码将创建一个字典，使用Y坐标作为键，x坐标作为值。
使用您的最新信息;字典“dict 1”将具有三个关键字67475、57475和47475。将具有包括文本“DAD”的名称的所有X坐标作为值添加到对应的Y坐标关键字。
然后简单地迭代每个键，反向排序值，这样我们就知道第一项是MAX值，最后一项是MIN值，然后分别打印每个Y坐标组和生成的名称和X坐标。

def rename(fn):
    out_sheet = 'Output'  # Name of sheet to write output to
    dict1 = {}  # Dictionary for unsorted data
    header_list = []  # List for headers

    ### Use a context manager with Xlwings
    with xw.App() as app:
        ### Open Workbook and input sheet
        wb = xw.Book(fn)
        sht = wb.sheets['new Sheet']

        ### Get maximum rows in the sheet
        mr = sht.range('A' + str(wb.sheets[0].cells.last_cell.row)).end('up').row

                          ### Extract the data ###

        ### Iterate column A rows 1 - max rows
        for cell in sht.range(f'A1:A{mr}'):
            ### For row 1 capture the Header text in to list header_list
            if cell.row == 1:
                for header_cell in sht.range(f'A1:C1'):
                    header_list.append(header_cell.value)
                continue
            ### Checks if the text 'DAD' exists in the current cell in the Name column (A)
            ### then adds the x coord to a dictionary with y coord as keys
            if 'DAD' in cell.value.upper():
                ### Create dictionary of cell values that match the criteria using X coord as key
                x_coord = int(sht.range(f'B{cell.row}').value)
                y_coord = int(sht.range(f'C{cell.row}').value)
                if y_coord in dict1:
                    dict1[y_coord] += [x_coord]
                else:
                    dict1[y_coord] = [x_coord]

                            ### Output the data ###

        ### dict1 has 1 key per unique Y coord, Values are all the x coords for that Y coord
        #
        ### Create the sheet 'Output' for write if not exist and assign variable sht2
        if out_sheet not in wb.sheet_names:
            sht2 = wb.sheets.add(out_sheet, after=wb.sheets[0])
            ### Add headers to new sheet
            sht2['A1'].value = header_list
            sht2.range('A1:C1').font.bold = True
        else:
            sht2 = wb.sheets['Output']

        ### Print and write data to the screen and output sheet
        count = 0
        start = 2
        for key, value in dict1.items():
            ### Sort x cords into descending order
            value.sort(reverse=True)
            for enum, i in enumerate(value,start):
                if enum == start:
                    ### First x cord is max value
                    count = 2
                    tstr_start = ['DAD_START', i, key]
                    print(tstr_start, end=' ')
                    sht2.range(f'A{enum}').value = tstr_start
                elif enum == len(value)+start-1:
                    ### Last x cord is min value
                    tstr_end = ['DAD_END', i, key]
                    print(tstr_end)
                    sht2.range(f'A{enum}').value = tstr_end
                else:
                    ### In Between cells use count for the naming
                    ### Use the 'count value that gets incremented every 2nd loop
                    tstr_mid = [f'DAD_{count}', i, key]
                    print(tstr_mid, end=' ')
                    sht2.range(f'A{enum}').value = tstr_mid
                    if enum % 2 == 0:
                        count += 1

                print('')
            start = enum+2
            print('\n--------------\n')

            ### Save Workbook
            wb.save(filename)

if __name__ == '__main__':
    filename = 'foo.xlsx'
    rename(filename)

python 列中字符串值的部分名称

1条答案

相关问题

热门标签

最新问答