在Python中實現(xiàn)Excel的VLOOKUP、HLOOKUP、XLOOKUP函數(shù)功能

hercules028 2022-10-12 發(fā)布于四川

展開全文

excelperfect

標(biāo)簽：Python與Excel,pandas

Excel的LOOKUP公式可能是最常用的公式之一，因此這里將在Python中實現(xiàn)Excel中查找系列公式的功能。事實上，我們可以使用相同的技術(shù)在Python中實現(xiàn)VLOOKUP、HLOOKUP、XLOOKUP或INDEX/MATCH等函數(shù)的功能。

示例

有兩個Excel表，一個包含一些基本的客戶信息，另一個包含客戶訂單信息。我們的任務(wù)是將一些數(shù)據(jù)從一個表帶入另一個表。聽起來很熟悉的情形！（可在知識星球完美Excel社群中下載本文的Excel示例工作簿）

Excel解決方案

為了解決這個問題，可以使用：查找或INDEX/MATCH公式。VLOOKUP可能是最常用的，但它受表格格式的限制，查找項必須位于我們正在執(zhí)行查找的數(shù)據(jù)表最左邊的列。換句話說，如果我們試圖帶入的值位于查找項的左側(cè)，那么VLOOKUP函數(shù)將不起作用。此外，我們可以使用INDEX/MATCH組合，但這需要更多的輸入。

在最新的Office中，Microsfot推出了XLOOKUP公式，但它只在Office 365中可用。使用XLOOKUP公式來解決這個問題，如下圖所示，列F“購買物品”是我們希望從第二個表（下方的表）中得到的，列G顯示了列F使用的公式。盡管表2包含相同客戶的多個條目，但出于演示目的，我們僅使用第一個條目的值。例如，對于Harry，我們想帶入其購買的“Kill la Kill”。

圖1

在Python中實現(xiàn)XLOOKUP

我們將使用pandas庫來復(fù)制Excel公式，該庫幾乎相當(dāng)于Python的電子表格應(yīng)用程序。

pandas提供了廣泛的工具選擇，因此我們可以通過多種方式復(fù)制XLOOKUP函數(shù)。這里我們將介紹一種方法：篩選和apply()的組合。

import pandas as pd

df1 =pd.read_excel(r'D:\users.xlsx', sheet_name='User_info')

df2 =pd.read_excel(r'D:\users.xlsx', sheet_name='purchase')

圖2

思考過程

XLOOKUP函數(shù)背后的思想類似于INDEX/MATCH，但更少的輸入。給定一個lookup_value，在lookup_array中找到它的位置，然后從return_array返回相同位置的值。下面是Excel XLOOKUP公式中的可用參數(shù)。我們將使用相同的參數(shù)名稱編寫Python函數(shù)，以便與Excel XLOOKUP公式進行比較。

XLOOKUP(lookup_value, lookup_array,return_array, [if_not_found], [match_mode], [search_mode])

Python實現(xiàn)

我們可以使用pandas篩選來實現(xiàn)。除了三個必需參數(shù)外，還將實現(xiàn)兩個可選參數(shù)if_not_found和search_mode（稍后更新）。下面是Python代碼：

def xlookup(lookup_value,lookup_array, return_array, if_not_found:str=''):

match_value = return_array.loc[lookup_array == lookup_value]

if match_value.empty:

return f''{lookup_value}' 沒有找到！' ifif_not_found == '' else if_not_found

else:

return match_value.tolist()[0]

上面幾行代碼中有很多內(nèi)容，這就是為什么很多人喜歡Python的原因。它很簡單，但可以表達復(fù)雜的邏輯。讓我們分解上面的代碼。

在第一行中，我們用一些參數(shù)定義了一個名為xlookup的函數(shù)：

lookup_value：我們感興趣的值，這將是一個字符串值
lookup_array：這是源數(shù)據(jù)框架中的一列，我們正在查找此數(shù)組/列中的“lookup_value”
return_array：這是源數(shù)據(jù)框架中的一列，我們希望從該列返回值
if_not_found：如果未找到”lookup_value”，將返回的值

在隨后的行中：

lookup_array==lookup_value返回一個布爾索引，pandas使用該索引篩選結(jié)果。
return_array.loc[]返回一個帶有基于上述布爾索引的值的pandas系列，只返回True值。
pandas系列的一個優(yōu)點是它的.empty屬性，告訴我們該系列是否包含值或空，如果match_value為空，那么我們知道找不到匹配項，然后我們可以通知用戶在數(shù)據(jù)中找不到查找值。
相反，如果match_value不為空，那么我們知道找到了一些值，此時可以通過.tolist()將match_value（pandas系列）轉(zhuǎn)換為列表。
最后，因為我們只想保留第一個值（如果有多個條目），所以我們通過從返回的列表中指定[0]來選擇第一個元素。

讓我們測試一下這個函數(shù)，似乎工作正常！注意，df1是我們要將值帶入的表，df2是我們從中查找值的源表，我們將兩個數(shù)據(jù)框架列傳遞到函數(shù)中，用于lookup_array和return_array。

圖3

公式完成，現(xiàn)在“向下拖動”

因為我們用代碼做所有事情，而且沒有GUI（圖形化用戶界面），所以我們不能簡單地雙擊某個東西來“拖拽”公式。但本質(zhì)上，“向下拖動”是循環(huán)部分——我們只需要將xlookup函數(shù)應(yīng)用于表df1的每一行。記住，我們不應(yīng)該使用for循環(huán)遍歷數(shù)據(jù)框架。

apply()方法代替for循環(huán)

事實證明，pandas提供了一個方法來實現(xiàn)上述要求，它的名稱是.apply()。讓我們看看它的語法，下面是一個簡化的參數(shù)列表，如果你想查看完整的參數(shù)列表，可查閱pandas的官方文檔。

dataframe.apply(func, axis = 0,args=())

func：我們正在應(yīng)用的函數(shù)
axis：我們可以將該函數(shù)應(yīng)用于行或列。默認情況下，其值是=0，代表行，而axis=1表示列
args=()：這是一個元組，包含要傳遞到func中的位置參數(shù)

下面是如何將xlookup函數(shù)應(yīng)用到數(shù)據(jù)框架的整個列。

df1['購買物品'] = df1['用戶姓名'].apply(xlookup,args = (df2['顧客'], df2['購買物品']))

需要注意的一件事是，apply()如何將參數(shù)傳遞到原始func中，在我們的例子中是xlookup。根據(jù)設(shè)計，apply將自動傳遞來自調(diào)用方數(shù)據(jù)框架（系列）的所有數(shù)據(jù)。在我們的示例中，apply()將df1['用戶姓名']作為第一個參數(shù)傳遞給函數(shù)xlookup。然而，我們的xlookup總共有三個參數(shù)，這就是參數(shù)args=()變得方便的地方。注意，我們需要以正確的順序傳遞這些參數(shù)。