Merge pandas data frame based on specific conditions

user_8901845 0
Viewed 50

I have a df as shown below

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

Explanation About the data

ID is the primary key of df1.

ID is the primary key of df2.

df3 does not have any primary key.

From the above, I would like to prepare below dfs.

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
  1. IDs which are there in df1 and not in df2

output2:

ID   Job   Salary
3    B     20
5    A     500
6    A     600
7    A     200
  1. IDs which are there in df1 and df3

output3:

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.

output4:

ID   Job   Salary
5    A     500
6    A     600
7    A     200
8    B     150
pandas dataframe python-3.x
2 Answers

Actually, your expected results aren't any merges, but rather selections, based on whether df1.ID is (or is not) in ID column of the second DataFrame.

To get your expected results, run the following commands:

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)]
user_73884770 
>>> # 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
user_89174260
Related