传入函数的Python DataFrame出现KeyError问题求助

我有一个整理邮件账单的Python程序，原本使用全局变量bill_data时运行正常，改为将该DataFrame作为函数参数传入后，出现了KeyError: 'Fatura'错误。

bill_data的结构 #

函数代码 #

函数逻辑：获取唯一的Fatura值，读取对应xlsx文件，基于xlsx中Valor列的总和匹配bill_data中的Fatura、Nota和vencimento信息，合并数据。

def compile_extract(bill: pd.DataFrame, folder: Path):    # ! erro com o dataframe
    ## Final product will look like this
    extract = pd.DataFrame(
        columns = [
            'REL', 'ID', 'ND', 'FAT', 'VencimentoFatura', 'StatusFatura',
            'StatusRelatorio', 'StatusSAP', 'Unidade de Negócio',
            'CNPJ Unidade de Negócio', 'Relatório', 'Descrição',
            'Motivo', 'Viajante', 'Serviço', 'Data de Emissão',
            'Destino', 'Fornecedor', 'Data Início da Viagem',
            'Data Fim da Viagem', 'Localizador',
            'Código Centro de Custo',
            'Descrição do Centro de Custo',
            'Rateio de Centro de Custo',
            'Valor', 'Código Projeto',
            'Nome Projeto'
        ]
    )
    
    ## Reutnrs only unique Fatura values
    bills = bill['Fatura'].unique()
    
    for i in range(len(bills)):

        ## Current Fatura number
        bill_num = bills[i]

        ## Ignores zero values in Fatura
        if bill_num != 0:
            ## Read the extract
            data = pd.read_excel(folder / str(str(bill_num) + ".xlsx"))
    
            ## Hotel services
            bill_hotel = data.loc[data['Serviço'] == str("Hotel")]
            bill_hotel_sum = round(bill_hotel['Valor'].sum(), 2)
    
            ## Other services
            bill_else = data.loc[data['Serviço']!=str("Hotel")]
            bill_else_sum = round(bill_else['Valor'].sum(), 2)
            
            ## Maturity date
            bill_maturity = bill.loc[bill['Fatura'] == int(bill_num)]['vencimento'].iloc[0]
    
            # Hotel services data
            ## Nota number
            invoice_hotel_num = bill.loc[(bill['Fatura'] == int(bill_num)) & (bill['Valor'] == bill_hotel_sum)]['Nota']
            if invoice_hotel_num.empty == False:
                invoice_hotel_num.values[0]
                ## Add extra columns
                bill_hotel.insert(0, "REL", None,True)
                bill_hotel.insert(1, "ID", None,True)
                bill_hotel.insert(2, "ND", int(invoice_hotel_num),True)
                bill_hotel.insert(3, "FAT", int(bill_num), True)
                bill_hotel.insert(4, "VencimentoFatura", bill_maturity, True)
                bill_hotel.insert(5, "StatusFatura", None, True)
                bill_hotel.insert(6, "StatusRelatorio", None, True)
                bill_hotel.insert(7, "StatusSAP", None, True)
    
            # Other services data
            ## Nota number
            invoice_else_num = bill.loc[(bill['Fatura'] == int(bill_num)) & (bill['Valor'] == bill_else_sum)]['Nota']
            if invoice_else_num.empty == False:
                invoice_else_num.values[0]
                ## Add extra columns
                bill_else.insert(0, "REL", None,True)
                bill_else.insert(1, "ID", None,True)
                bill_else.insert(2, "ND", int(invoice_else_num),True)
                bill_else.insert(3, "FAT", int(bill_num), True)
                bill_else.insert(4, "VencimentoFatura", bill_maturity, True)
                bill_else.insert(5, "StatusFatura", None, True)
                bill_else.insert(6, "StatusRelatorio", None, True)
                bill_else.insert(7, "StatusSAP", None, True)
            
            ## Concat both services
            bill = pd.concat([bill_hotel, bill_else]).sort_index()
            extract = pd.concat([extract, bill]).reset_index(drop = True)

    ## Removes NaN values
    extract = extract.where(pd.notnull(extract), None)
    return extract

调用方式 #

bill_comp = compile_extract(bill_data, download_folder)

报错栈 #

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\pandas\core\indexes\base.py:3653, in Index.get_loc(self, key)
   3652 try:
-> 3653     return self._engine.get_loc(casted_key)
   3654 except KeyError as err:

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\pandas\_libs\index.pyx:147, in pandas._libs.index.IndexEngine.get_loc()

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\pandas\_libs\index.pyx:176, in pandas._libs.index.IndexEngine.get_loc()

File pandas\_libs\hashtable_class_helper.pxi:7080, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas\_libs\hashtable_class_helper.pxi:7088, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'Fatura'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
c:\Users\jec\OneDrive - XP Investimentos\Financeiro\X - Teste\Automações\600000\DEV.ipynb Célula 21 line 2
     21 # bill_file = donwload_extract(bill_data, download_folder)
     22 bill_file = [
     23     '22582.xlsx',
     24     '22578.xlsx',
     25     '22579.xlsx'
     26 ]
---> 28 bill_comp = compile_extract(bill_data, download_folder)

c:\Users\jec\OneDrive - XP Investimentos\Financeiro\X - Teste\Automações\600000\DEV.ipynb Célula 21 line 3
     33 bill_else_sum = round(bill_else['Valor'].sum(), 2)
     35 # Recupera a data de vencimento
---> 36 bill_maturity = bill.loc[bill['Fatura'] == int(bill_num)]['vencimento'].iloc[0]
     38 # Dados dos realatórios de hoteis
     39 # Retorna o número da nota (Array)
     40 invoice_hotel_num = bill.loc[(bill['Fatura'] == int(bill_num)) & (bill['Valor'] == bill_hotel_sum)]['Nota']

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\pandas\core\frame.py:3761, in DataFrame.__getitem__(self, key)
   3759 if self.columns.nlevels > 1:
   3760     return self._getitem_multilevel(key)
-> 3761 indexer = self.columns.get_loc(key)
   3762 if is_integer(indexer):
   3763     indexer = [indexer]

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\pandas\core\indexes\base.py:3655, in Index.get_loc(self, key)
   3653     return self._engine.get_loc(casted_key)
   3654 except KeyError as err:
-> 3655     raise KeyError(key) from err
   3656 except TypeError:
   3657     # If we have a listlike key, _check_indexing_error will raise
   3658     #  InvalidIndexError. Otherwise we fall through and re-raise
   3659     #  the TypeError.
   3660     self._check_indexing_error(key)

KeyError: 'Fatura'

测试发现，若在函数内直接使用全局变量bill_data则程序正常运行，请问问题出在哪里？

问题原因与修复 #

原因 #

循环内的这行代码导致了问题：

bill = pd.concat([bill_hotel, bill_else]).sort_index()

你把函数参数bill（原本指向传入的原始账单DataFrame）重新赋值成了拼接后的bill_hotel和bill_else。而这两个DataFrame来自读取的xlsx文件，没有Fatura列。第一次循环结束后，bill变量已经不再是原始的账单DataFrame，第二次循环执行到bill.loc[bill['Fatura'] == int(bill_num)]时，自然找不到Fatura列，触发KeyError。

而使用全局变量时，你在函数里创建的局部变量bill覆盖的只是局部作用域的变量，全局的bill_data始终保持原始结构，每次循环都能正常访问Fatura列，所以不会报错。

修复方案 #

把拼接后的结果变量名改成其他名称，比如temp_bill，避免覆盖原始的参数bill：

## Concat both services
temp_bill = pd.concat([bill_hotel, bill_else]).sort_index()
extract = pd.concat([extract, temp_bill]).reset_index(drop = True)

这样函数参数bill始终指向传入的原始账单DataFrame，循环中每次访问bill['Fatura']都能找到对应的列。

内容的提问来源于stack exchange，提问作者Caio Pavesi