使用Pandas查找DataFrame中各门店缺失的连续发票编号

问题说明 #

给定包含Store（门店）和Invoice（发票编号）列的Pandas DataFrame，需要按Store分组，找出每组内缺失的连续发票编号。

输入示例 #

import pandas as pd

df1 = pd.DataFrame()
df1['Store'] = ['A','A','A','A','A','B','B','B','B','C','C','C','D','D']
df1['Invoice'] = ['1','2','5','6','8','20','23','24','30','200','202','203','204','206']

对应的表格：

解决方案代码 #

import pandas as pd

# 1. 将Invoice列转换为整数类型（原数据为字符串，无法直接处理连续数字逻辑）
df1['Invoice'] = df1['Invoice'].astype(int)

# 2. 定义处理单个门店分组的函数
def find_missing_invoices(group):
    # 获取当前门店发票编号的最小、最大值
    min_inv = group['Invoice'].min()
    max_inv = group['Invoice'].max()
    # 生成该范围内的完整连续数字序列
    full_invoice_range = pd.Series(range(min_inv, max_inv + 1))
    # 筛选出不在原发票列表中的编号，即缺失的发票号
    missing_invoices = full_invoice_range[~full_invoice_range.isin(group['Invoice'])]
    # 返回包含门店名称和缺失发票号的DataFrame
    return pd.DataFrame({
        'Store': [group.name] * len(missing_invoices),
        'MissInvoice': missing_invoices.values
    })

# 3. 按门店分组应用函数，合并结果并重置索引
result_df = df1.groupby('Store').apply(find_missing_invoices).reset_index(drop=True)

print(result_df)

输出结果 #

Store  MissInvoice
0       A            3
1       A            4
2       A            7
3       B           21
4       B           22
5       B           25
6       B           26
7       B           27
8       B           28
9       B           29
10      C          201
11      D          205

内容的提问来源于stack exchange，提问作者Gustavo A. Marín Acevedo