如果单元格值中不存在字符串，则添加到列表中；如果存在字符串，则中断并启动新列表？

Question

我试图在Excel中遍历一列，并检查是否存在字符串。如果字符串存在，我想将列表重置为[]，然后重复这个过程。在这上面花了太多时间，我似乎不知道自己做错了什么。你知道吗

示例数据：

Open Ended Schemes(Balanced)    

Aditya Birla Sun Life Mutual Fund   

120518  Aditya Birla Sun Life Equity Hybrid'95 Fund - Direct Plan-Dividend
120517  Aditya Birla Sun Life Equity Hybrid'95 Fund - Direct Plan-Growth


Open Ended Schemes(Debt Scheme - Banking and PSU Fund)  

Axis Mutual Fund    

128953  Axis Banking & PSU Debt Fund - Bonus Option
117447  Axis Banking & PSU Debt Fund - Daily Dividend Option

代码：

from openpyxl import load_workbook
import os
wb = load_workbook('m.xlsx')
ws = wb.active

keys = ['1', '2']
m_dict = {}
scheme_codes = []
for g in groups[0:2]:
    for row in ws.iter_rows('A{}:A{}'.format(ws.min_row +1, ws.max_row)):
        # scheme_codes = []
        for cell in row:
            if cell.value != None:
                if 'Schemes' in cell.value:
                    print('Found Schemes' + str(cell.value))
                    scheme_codes=[]
                    break
                else:
                    scheme = cell.value

                    scheme_codes.append(scheme)
            m_dict[g] = scheme_codes

我每个方案只得到1个项目，我尝试过各种方法，要么就是一直通过rows。文件有18000行。你知道吗

预期产量

{1:[在'A'列中'schemes'第一次重复之前的所有项]，2:[在'A'列中'schemes'第二次重复之前的所有项]

现在当我运行代码时，我得到一个len（scheme_codes）=8069，据我所知这是错误的。第一个清单应该是80项左右。你知道吗

Answer 1

这并不是你想要的，它实际上提供了一些额外的信息。。。你知道吗

它提供了一个包含一组scheme\u代码和scheme\u名称的dict，如：

{scheme: {sub_scheme : {(code, name), (code, name), ...}}}

如果你真的只需要顶层方案和它的代码，你应该能够简化它。 只需删除一级defaultdict并使用scheme_codes[scheme].add(cell.value)。。。你知道吗

from openpyxl import load_workbook
import os

from collections import defaultdict

wb = load_workbook("mfcodes.xlsx")
ws = wb.active

scheme_codes = defaultdict(lambda: defaultdict(set))

scheme = 'N/A'
sub_scheme = 'N/A'
for row in ws[f'A{ws.min_row}:B{ws.max_row}']:
    cell = row[0]
    if not cell.value:
        continue

    if 'Schemes' in cell.value:
        scheme = cell.value
    else:
        if not cell.value.isdigit():
            sub_scheme = cell.value
        else:
            scheme_codes[scheme][sub_scheme].add((cell.value, row[1].value))

print(repr(next(iter(scheme_codes.items()))))

输出：

{'Open Ended Schemes(Balanced)' :
    {'Aditya Birla Sun Life Mutual Fund': {('120518', "Aditya Birla Sun Life Equity Hybrid'95 Fund - Direct Plan-Dividend"),
                                           ('120517', "Aditya Birla Sun Life Equity Hybrid'95 Fund - Direct Plan-Growth"),
                                           ('103154', "Aditya Birla Sun Life Equity Hybrid'95 Fund - Regular Plan-Dividend"),
                                           ('103155', "Aditya Birla Sun Life Equity Hybrid'95 Fund - Regular Plan-Growth"),
                                           ('131671', 'Aditya Birla Sun Life Balanced Advantage Fund - Direct Plan - Dividend Option'),
                                           ('131670', 'Aditya Birla Sun Life Balanced Advantage Fund - Direct Plan - Growth Option'),
                                           ('131665', 'Aditya Birla Sun Life Balanced Advantage Fund - Regular Plan - Dividend Option'),
                                           ('131666', 'Aditya Birla Sun Life Balanced Advantage Fund - Regular Plan - Growth Option')},
    'Baroda Pioneer Mutual Fund': {('125112', 'Baroda Pioneer Balance Fund - Plan A - Bonus Option'),
                                   ('101913', 'BARODA PIONEER BALANCE FUND - Plan A - Dividend Option'),
                                   ('101912', 'BARODA PIONEER BALANCE FUND - Plan A - Growth Option'),
                                   ('119325', 'BARODA PIONEER BALANCE FUND - Plan B (Direct) - Dividend Option'),
                                   ('119326', 'BARODA PIONEER BALANCE FUND - Plan B (Direct) - Growth Option')},
    # et cetera ...
    }
}

顺便说一下：第一个方案有67个代码。。。你知道吗