在Pandas中实现Excel的SUMIF和COUNTIF函数功能

标签：Python与Excel协同，pandas

本文介绍如何使用Python pandas库实现Excel中的SUMIF函数和COUNTIF函数功能。

SUMIF可能是Excel中最常用的函数之一。顾名思义，该函数对满足特定条件的数字相加。

示例数据集

本文使用从Kaggle找到的一个有趣的数据集。它包含纽约警方2016年收到的与“喧闹音乐/派对”相关的噪音投诉电话，让我们来看看在纽约哪里玩得开心。

为了方便起见，已经将数据集上传到Github上，你可以直接用pandas读取文件。

装载该数据集：

import pandas as pd

df =pd.read_csv('https://raw.githubusercontent.com/pythoninoffice/pandas_sumif_tut/main/modified_bar_locations.csv')

图1：读取数据到pandas

数据集和标签非常简单，这里不再解释。

pandas中的SUMIF

使用布尔索引

要查找Manhattan区的电话总数。布尔索引是pandas中非常常见的技术。本质上，它对数据框架应用筛选，只选择符合条件的记录。例如，如果想要Manhattan区的所有记录：

df[df['Borough']=='MANHATTAN']

图2：使用pandas布尔索引选择行

在整个数据集中，看到来自Manhattan的1076条记录。

在df[]中，这个表达式df['Borough']=='MANHATTAN'返回一个完整的True值或False值列表（2440个条目），因此命名为“布尔索引”。一旦将这个布尔索引传递到df[]中，只有具有True值的记录才会返回。这就是上图2中获得1076个条目的原因。

图3：Python pandas布尔索引

使用已筛选的数据框架，可以选择num_calls列并计算总和sum()。

df[df['Borough']=='MANHATTAN']['num_calls'].sum()

图4

如果想了解纽约所有5个行政区的投诉电话数量，该怎么办？可以使用上面的方法循环五个行政区的名称，然后逐个计算，但这有点低效。

使用groupby()方法

pandas库有一个groupby()方法，允许对组进行简单的操作（例如求和）。要使用此函数，需要提供组名、数据列和要执行的操作。在示例中：

• 组： Borough列
• 数据列：num_calls列
• 操作：sum()

df.groupby('Borough')['num_calls'].sum()

图5：pandas groupby函数

这样，我们发现，住在Manhattan的人拨打了39926个投诉电话来获得最大的乐趣。“未指定”类别可能是由于缺少一些数据，这里不重点讨论这些数据。

Pandas中的SUMIFS

SUMIFS是另一个在Excel中经常使用的函数，允许在执行求和计算时使用多个条件。

这一次，将通过组合Borough和Location列来精确定位搜索。注：位置类型列中的数据是为演示目的随机生成的。

使用布尔索引

看看有多少投诉是针对Manhattan区和位置类型“Store/Commercial”。

目前我们已经熟悉了布尔索引，下面的内容应该很简单。本质上是使用按位与运算符&将两个条件结合起来。注意，这两个条件周围的括号是必不可少的。

图6

与只传递1个条件Borough==‘Manhattan’的SUMIF示例类似，在SUMIFS中，传递多个条件（根据需要）。在这个示例中，只需要两个。

使用groupby()方法

如果对所有的Borough和LocationType组合感兴趣，仍将使用groupby()方法，而不是循环遍历所有可能的组合。只需将列名列表传递给groupby函数。

df.groupby(['Borough','LocationType'])['num_calls'].sum()

图7

Pandas中的COUNTIF，COUNTIFS和其它

现在，已经掌握了pandas中的SUMIF和SUMIFS，要进行COUNTIF，只需要将sum()操作替换为count()操作。

事实上，如果将上述示例中的sum()替换为：

• mean()——将提供AVERAGEIF(S)
• max()——将提供MAXIFS
• min()——将提供MINIFS
• median()——将提供MEDIANIF(S)，虽然这个函数在Excel中不存在
• mode()——将提供MODEIF(S)，虽然这个函数在Excel中不存在

小结

Python和pandas是多才多艺的。虽然pandas中没有SUMIF函数，但只要我们了解这些值是如何计算的，就可以自己复制/创建相同功能的公式。