Python Forum
Match two tables to select exactly matching items
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Match two tables to select exactly matching items
#1
I want to select the data from "table" that are exactly matched with the data in "criteria" dataframe.
import pandas as pd
table = pd.DataFrame(data = {'RowID':[1,2,3,4,5,6,7,8,9,10],
                             'CusID':[1,1,1,1,2,2,3,3,4,4],
                             'Area':['A','A','A','A','A','A','A','A','B','B'],
                             'Income':[800,900,1000,900,1000,800,400,400,900,1000],})


criteria = pd.DataFrame(data = {'CusID':[1,2,4],
                             'Area':['A','A','B'],
                             'Income':[800,1000,700],})
As per the criteria i'm expecting the rows of RowID 1 & 5 only. However according to my code i'm getting RowID 3,6 & 10 also.
That is my expected result is;
RowID   CusID      Area    Income
1            1             A        800
5            2             A        1000

tableMatched= table[(table['CusID'].isin(criteria['CusID'])) & (table['Area'].isin(criteria['Area']))& (table['Income'].isin(criteria['Income']))]
Apreciate if someone can help me to code this correctly.
Reply
#2
#!/usr/bin/python3
import pandas as pd

table = pd.DataFrame(data = {'RowID':[1,2,3,4,5,6,7,8,9,10],
                             'CusID':[1,1,1,1,2,2,3,3,4,4],
                             'Area':['A','A','A','A','A','A','A','A','B','B'],
                             'Income':[800,900,1000,900,1000,800,400,400,900,1000],})
 
 
criteria = pd.DataFrame(data = {'CusID':[1,2,4],
                                 'Area':['A','A','B'],
                               'Income':[800,1000,700],})

tableMatched = pd.merge(table, criteria, on=['CusID', 'Area', 'Income'], how='inner')
print(tableMatched)
Output:
  Area  CusID  Income  RowID 0    A      1     800      1 1    A      2    1000      5
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Adding Tables and Extracting Values from Tables jamescox11480 5 3,403 Sep-29-2018, 04:49 PM
Last Post: jamescox11480

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020