基于特定条件合并 Pandas 数据框

Question

我有一个如下所示的数据框

df1:

ID   Job   Salary
1    A     100
2    B     200
3    B     20
4    C     150
5    A     500
6    A     600
7    A     200
8    B     150

df2:

    ID      Type        Status       Age
    1       2           P            23
    2       1           P            28
    8       1           F            33
    4       3           P            48
    14      1           F            23
    11      2           P            28
    16      2           F            23
    41      3           P            38

df3：

ID   T_Type       Amount
1    K            20
2    L            -50
1    K            30
3    K            5
1    K            100
2    L            -50
1    L            -30
25   K            500
1    K            20
4    L            -80
19   K            30
2    K            -5

数据说明

ID is the primary key of df1.

ID is the primary key of df2.

df3 does not have any primary key.

根据上述内容，我想准备以下数据框。

1. IDs which are in df1 and df2.

Expected output1:

ID   Job   Salary   
1    A     100
2    B     200
4    C     150
8    B     150

输出2：

df1中存在但df2中不存在的ID。

ID   Job   Salary
3    B     20
5    A     500
6    A     600
7    A     200

输出3：

在df1和df3中都存在的ID

ID   Job   Salary
1    A     100
2    B     200
3    B     20
4    C     150

4. IDs which are there in df1 and not in df3.

输出4：

ID   Job   Salary
5    A     500
6    A     600
7    A     200
8    B     150

- Danish

Answer 1

实际上，您期望的结果不是任何合并，而是基于第二个数据帧的ID列中是否存在df1.ID的选择。

要获取您期望的结果，请运行以下命令：

result_1 = df1[df1.ID.isin(df2.ID)]
result_2 = df1[~df1.ID.isin(df2.ID)]
result_3 = df1[df1.ID.isin(df3.ID)]
result_4 = df1[~df1.ID.isin(df3.ID)]

- Valdi_Bo

---

非常感谢，我想接受所有的答案。 - Danish

Answer 2

>>> # 1. IDs which are in df1 and df2.
>>> df1[df1['ID'].isin(df2['ID'])]
   ID Job  Salary
0   1   A     100
1   2   B     200
3   4   C     150
7   8   B     150

>>> # 2. IDs which are there in df1 and not in df2    
>>> df1[~df1['ID'].isin(df2['ID'])]
   ID Job  Salary
2   3   B      20
4   5   A     500
5   6   A     600
6   7   A     200

>>> # 3. IDs which are there in df1 and df3
>>> df1[df1['ID'].isin(df3['ID'])]
   ID Job  Salary
0   1   A     100
1   2   B     200
2   3   B      20
3   4   C     150

>>> # 4. IDs which are there in df1 and not in df3.
>>> df1[~df1['ID'].isin(df3['ID'])]
   ID Job  Salary
4   5   A     500
5   6   A     600
6   7   A     200
7   8   B     150

- imdevskp