keeping table rows that match the other table

user_15582677 0
Viewed 235

I need some help with this problem: I have two tables and I want to filter the rows on the second table (Table B) so that it only shows the one's that have matching 'names' with the Table A.

An exemple:

Table A

A b c
Anne Two Three
Anne Four Five
Jhon Four Five
Olivia Four Five

Table. B

A Money Rent
Anne Two Three
Anne Four Five
Anne Four Five
Kristian Four Five
Paul Four Five
Olivia Four Five
Olivia Four Five

The result that I want to achieve is

A Money Rent
Anne Two Three
Anne Four Five
Anne Four Five
Jhon Nan Nan
Olivia Four Five
Olivia Four Five
jupyter-notebook python
4 Answers

You can isin and append:

# Create a new B table that only includes the names of A table
new_b = b.loc[b['A'].isin(a['A'].tolist())]

# Append the additional names from Table A (i.e. Jhon)
new_b = new_b.append(a.loc[~a['A'].isin(new_b['A'].tolist())]).drop(['b','c'],axis=1).reset_index(drop=True)

Prints:

        A Money   Rent
0    Anne   Two  Three
1    Anne  Four   Five
2    Anne  Four   Five
3  Olivia  Four   Five
4  Olivia  Four   Five
5    Jhon   NaN    NaN
user_91673820

Assuming you are using pandas, you could try this: dfC = dfB[dfB['A'].isin(dfA['A'].unique())]

This way, you will be filtering table B, based on it's A column, looking for values that are in the column A of table A.

user_135746350

SELECT * FROM table_B WHERE A IN (SELECT A FROM table_A)

user_137984770

using df.join could get the job done for you.

import pandas as pd

dfA = pd.DataFrame(dict(A=['Anne','Anne','Jhon','Olivia'],B=[2,2,4,5],C=[3,5,5,5]))  
dfB = pd.DataFrame(dict(A=['Anne','Anne','Anne','Kristan','Paul','Olivia','Olivia'],
                        Money=[2,4,4,4,4,4,4],Rent=[3,5,5,5,5,5,5]))
dfB = dfB.set_index('A')
dfA.groupby('A').first().join(dfB).loc[:,['Money','Rent']]
        Money  Rent
A
Anne      2.0   3.0
Anne      4.0   5.0
Anne      4.0   5.0
Jhon      NaN   NaN
Olivia    4.0   5.0
Olivia    4.0   5.0

edit: further explanation of last line

groupby('A').first() creates a dataframe of the unique names, arbitrarily choosing the first entry for columns B and C (since you want to get rid of those anyways). Then .join(dfB) does a left join using the unique values of table A as the left table, and dfB as the right table. loc[:,['Money','Rent']] chooses the columns you want (removing the B and C columns from table A).

user_76384010
Related