Python Pandas：移除dt1中存在于dt2的DataFrame行

我是Python新手，需要用Pandas实现移除dt1中所有在dt2里已存在的行，以下是示例数据和预期结果：

示例数据dt1 #

Client  Acct#   Last Note   Last Message
XXY  F1000  completed      Payer
XXY  F1001  not completed  Payer
XXY  F1002                 Payer
XXY  F1003  complete      Payer end
XXY F1004                Payer end
XXY F1005   moved        value
XXY F1006                value
XXY F1007   moved       payer

示例数据dt2 #

Client  Acct#   Last Note   Last Message
XXY    F1002               Payer
XXY    F1004               Payer end

预期结果 #

Client  Acct#   Last Note   Last Message
XXY F1000   completed       Payer
XXY F1001   not completed   Payer
XXY F1003   complete        Payer end
XXY F1005   moved          value
XXY F1006                  value
XXY F1007   moved          payer

解决方案 #

方法1：使用merge的indicator参数（推荐，空值处理更稳定） #

通过左连接两个数据框并标记行的来源，筛选出仅存在于dt1的行：

import pandas as pd

# 构造dt1数据框
dt1_data = """Client  Acct#   Last Note   Last Message
XXY  F1000  completed      Payer
XXY  F1001  not completed  Payer
XXY  F1002                 Payer
XXY  F1003  complete      Payer end
XXY F1004                Payer end
XXY F1005   moved        value
XXY F1006                value
XXY F1007   moved       payer"""
dt1 = pd.read_csv(pd.compat.StringIO(dt1_data), sep='\s+')

# 构造dt2数据框
dt2_data = """Client  Acct#   Last Note   Last Message
XXY    F1002               Payer
XXY    F1004               Payer end"""
dt2 = pd.read_csv(pd.compat.StringIO(dt2_data), sep='\s+')

# 左连接并标记每行来源
merged_df = dt1.merge(dt2, how='left', indicator=True)
# 筛选仅在dt1中存在的行，删除标记列
result_df = merged_df[merged_df['_merge'] == 'left_only'].drop(columns='_merge')

# 打印结果
print(result_df)

方法2：通过行元组匹配 #

将每行转换为元组，判断是否不在dt2的行集合中：

import pandas as pd

# 构造数据框（同方法1，省略重复代码）
dt1_data = """Client  Acct#   Last Note   Last Message
XXY  F1000  completed      Payer
XXY  F1001  not completed  Payer
XXY  F1002                 Payer
XXY  F1003  complete      Payer end
XXY F1004                Payer end
XXY F1005   moved        value
XXY F1006                value
XXY F1007   moved       payer"""
dt1 = pd.read_csv(pd.compat.StringIO(dt1_data), sep='\s+')

dt2_data = """Client  Acct#   Last Note   Last Message
XXY    F1002               Payer
XXY    F1004               Payer end"""
dt2 = pd.read_csv(pd.compat.StringIO(dt2_data), sep='\s+')

# 将dt2的行转为元组集合
dt2_row_set = set(dt2.apply(tuple, axis=1))
# 筛选dt1中不在dt2的行
result_df = dt1[~dt1.apply(tuple, axis=1).isin(dt2_row_set)]

# 打印结果
print(result_df)

两种方法均可得到预期结果，方法1对空值的兼容性更强，适合大多数场景。

内容的提问来源于stack exchange，提问作者V Santhosh Kumar