CODEWITHSUNDEEP
pythonpandas
user2747058
user2747058

Reputation: 119

pandas merge returning more rows than either dataframe contains

I am trying to merge two dataframe where the resulting dataframe is only the duplicate rows where columns 1-8 are the same. These are my dataframes: 

check_df  0   1   2     3          4      5           6            7       8        
     0    1  794  1  192.168.0.1  qrf   default    SQL Server  Windows  cilaric98
     1    2  2558 1  192.168.0.2  qrf   default    Oracle      Windows  cilaric98
     2    3  2840 1  192.168.0.3  qrf   default    Oracle      Windows  cilaric008
     3    4  3290 1  192.168.0.4  qrf   default    SQL Server  Windows  cilaric98
     4    5  3175 1  192.168.0.5  a2d2  default    SQL Server  Windows  cilaric98
     5    6  4189 1  192.168.0.21 a2d2  default    SQL Server  Windows  cilaric98 

dataframe  0   1   2     3          4      5           6            7       8        
     0    1.0 794  1  192.168.0.1  qrf   default    SQL Server  Windows  cilaric98
     1    2.0 2558 1  192.168.0.2  qrf   default    Oracle      Windows  cilaric98
     2    3.0 2840 1  192.168.0.3  qrf   default    Oracle      Windows  cilaric008
     3    4.0 3290 1  192.168.0.4  qrf   default    SQL Server  Windows  cilaric98
     4    5.0 3175 1  192.168.0.5  a2d2  default    SQL Server  Windows  cilaric98 
     5    6.0 3915 1  192.168.0.6  a2d2  default    SQL Server  Windows  cilaric98 
     6    7.0 3886 1  192.168.0.7  a2d2  default    SQL Server  Windows  cilaric98

Running this:

dupes_df = check_df.merge(dataframe, how='inner', on=[1,2,3,4,5,6,7], indicator=True).drop_duplicates(keep='first')
dupes_df = dupes_df[dupes_df['_merge'] == 'both']

returns almost double the columns and more rows then either dataframe contains. What I am expecting for an output is:

    check_df  0   1   2     3          4      5           6            7       8        
         0    1  794  1  192.168.0.1  qrf   default    SQL Server  Windows  cilaric98
         1    2  2558 1  192.168.0.2  qrf   default    Oracle      Windows  cilaric98
         2    3  2840 1  192.168.0.3  qrf   default    Oracle      Windows  cilaric008
         3    4  3290 1  192.168.0.4  qrf   default    SQL Server  Windows  cilaric98
         4    5  3175 1  192.168.0.5  a2d2  default    SQL Server  Windows  cilaric98

Upvotes: 0

Views: 85

Answers (1)

Scott Boston
Scott Boston

Reputation: 153500

You can try this for a one-liner:

check_df.merge(dataframe, on=[1,2,3,4,5,6,7], suffixes=('','_y'))[check_df.columns]

Output:

   0       1            2            3        4           5           6  \
0  1     794            1  192.168.0.1      qrf     default  SQL Server   
1  2  2558 1  192.168.0.2          qrf  default      Oracle     Windows   
2  3  2840 1  192.168.0.3          qrf  default      Oracle     Windows   
3  4  3290 1  192.168.0.4          qrf  default  SQL Server     Windows   
4  5  3175 1  192.168.0.5         a2d2  default  SQL Server     Windows   

            7          8  
0     Windows  cilaric98  
1   cilaric98       None  
2  cilaric008       None  
3   cilaric98       None  
4   cilaric98       None

Upvotes: 2

Related Questions