在Python中实现Excel的VLOOKUP、HLOOKUP、XLOOKUP函数功能

fanjy

发布于 2022-11-16 14:03:08

7.1K0

发布于 2022-11-16 14:03:08

文章被收录于专栏：完美Excel

标签：Python与Excel,pandas

Excel的LOOKUP公式可能是最常用的公式之一，因此这里将在Python中实现Excel中查找系列公式的功能。事实上，我们可以使用相同的技术在Python中实现VLOOKUP、HLOOKUP、XLOOKUP或INDEX/MATCH等函数的功能。

示例

有两个Excel表，一个包含一些基本的客户信息，另一个包含客户订单信息。我们的任务是将一些数据从一个表带入另一个表。听起来很熟悉的情形！（可在知识星球完美Excel社群中下载本文的Excel示例工作簿）

Excel解决方案

为了解决这个问题，可以使用：查找或INDEX/MATCH公式。VLOOKUP可能是最常用的，但它受表格格式的限制，查找项必须位于我们正在执行查找的数据表最左边的列。换句话说，如果我们试图带入的值位于查找项的左侧，那么VLOOKUP函数将不起作用。此外，我们可以使用INDEX/MATCH组合，但这需要更多的输入。

在最新的Office中，Microsfot推出了XLOOKUP公式，但它只在Office 365中可用。使用XLOOKUP公式来解决这个问题，如下图所示，列F“购买物品”是我们希望从第二个表（下方的表）中得到的，列G显示了列F使用的公式。尽管表2包含相同客户的多个条目，但出于演示目的，我们仅使用第一个条目的值。例如，对于Harry，我们想带入其购买的“Kill la Kill”。

图1

在Python中实现XLOOKUP

我们将使用pandas库来复制Excel公式，该库几乎相当于Python的电子表格应用程序。

pandas提供了广泛的工具选择，因此我们可以通过多种方式复制XLOOKUP函数。这里我们将介绍一种方法：筛选和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函数背后的思想类似于INDEX/MATCH，但更少的输入。给定一个lookup_value，在lookup_array中找到它的位置，然后从return_array返回相同位置的值。下面是Excel XLOOKUP公式中的可用参数。我们将使用相同的参数名称编写Python函数，以便与Excel XLOOKUP公式进行比较。

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

Python实现

我们可以使用pandas筛选来实现。除了三个必需参数外，还将实现两个可选参数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]

上面几行代码中有很多内容，这就是为什么很多人喜欢Python的原因。它很简单，但可以表达复杂的逻辑。让我们分解上面的代码。

在第一行中，我们用一些参数定义了一个名为xlookup的函数：

lookup_value：我们感兴趣的值，这将是一个字符串值
lookup_array：这是源数据框架中的一列，我们正在查找此数组/列中的“lookup_value”
return_array：这是源数据框架中的一列，我们希望从该列返回值
if_not_found：如果未找到”lookup_value”，将返回的值

在随后的行中：

lookup_array==lookup_value返回一个布尔索引，pandas使用该索引筛选结果。
return_array.loc[]返回一个带有基于上述布尔索引的值的pandas系列，只返回True值。
pandas系列的一个优点是它的.empty属性，告诉我们该系列是否包含值或空，如果match_value为空，那么我们知道找不到匹配项，然后我们可以通知用户在数据中找不到查找值。
相反，如果match_value不为空，那么我们知道找到了一些值，此时可以通过.tolist()将match_value（pandas系列）转换为列表。
最后，因为我们只想保留第一个值（如果有多个条目），所以我们通过从返回的列表中指定[0]来选择第一个元素。

让我们测试一下这个函数，似乎工作正常！注意，df1是我们要将值带入的表，df2是我们从中查找值的源表，我们将两个数据框架列传递到函数中，用于lookup_array和return_array。

图3

公式完成，现在“向下拖动”

因为我们用代码做所有事情，而且没有GUI（图形化用户界面），所以我们不能简单地双击某个东西来“拖拽”公式。但本质上，“向下拖动”是循环部分——我们只需要将xlookup函数应用于表df1的每一行。记住，我们不应该使用for循环遍历数据框架。

apply()方法代替for循环

事实证明，pandas提供了一个方法来实现上述要求，它的名称是.apply()。让我们看看它的语法，下面是一个简化的参数列表，如果你想查看完整的参数列表，可查阅pandas的官方文档。

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

func：我们正在应用的函数
axis：我们可以将该函数应用于行或列。默认情况下，其值是=0，代表行，而axis=1表示列
args=()：这是一个元组，包含要传递到func中的位置参数

下面是如何将xlookup函数应用到数据框架的整个列。

df1['购买物品'] = df1['用户姓名'].apply(xlookup,args = (df2['顾客'], df2['购买物品']))

需要注意的一件事是，apply()如何将参数传递到原始func中，在我们的例子中是xlookup。根据设计，apply将自动传递来自调用方数据框架（系列）的所有数据。在我们的示例中，apply()将df1['用户姓名']作为第一个参数传递给函数xlookup。然而，我们的xlookup总共有三个参数，这就是参数args=()变得方便的地方。注意，我们需要以正确的顺序传递这些参数。