51
$\begingroup$

I would like to compare one column of a df with other df's. The columns are names and last names. I'd like to check if a person in one data frame is in another one.

$\endgroup$
4
  • $\begingroup$ Could you please indicate how you want the result to look like? Is it a df with names appearing in both dfs, and whether you also need anything else such as count, or matching column in df2 ,etc. Thanks! $\endgroup$
    – The Lyrist
    Jun 12, 2018 at 22:39
  • $\begingroup$ check out pandas.pydata.org/pandas-docs/stable/generated/… $\endgroup$
    – oW_
    Jun 12, 2018 at 22:56
  • $\begingroup$ You could inner join the two data frames on the columns you care about and check if the number of rows in the result is positive. $\endgroup$
    – dsaxton
    Jul 13, 2018 at 13:41
  • $\begingroup$ FYI, comparing on first and last name on any decently large set of names will end up with pain - lots of people have the same name! $\endgroup$
    – Ken Syme
    Jul 13, 2018 at 20:31

8 Answers 8

39
$\begingroup$

If you want to check equal values on a certain column, let's say Name, you can merge both DataFrames to a new one:

mergedStuff = pd.merge(df1, df2, on=['Name'], how='inner')
mergedStuff.head()

I think this is more efficient and faster than where if you have a big data set.

$\endgroup$
1
  • 2
    $\begingroup$ I think we want to use an inner join here and then check its shape. $\endgroup$
    – dsaxton
    Jul 13, 2018 at 13:43
24
$\begingroup$

You can double check the exact number of common and different positions between two df by using isin and value_counts().

Like that:

df['your_column_name'].isin(df2['your_column_name']).value_counts()

Result:

example isin

True = common
False = different

$\endgroup$
2
  • 1
    $\begingroup$ This should be the answer in my opinion. $\endgroup$ Jan 16, 2020 at 9:12
  • 1
    $\begingroup$ elegant, without creating a new df $\endgroup$
    – arilwan
    Nov 6, 2021 at 15:59
22
$\begingroup$
df1.where(df1.values==df2.values).notna()

True entries show common elements. This also reveals the position of the common elements, unlike the solution with merge.

$\endgroup$
2
  • $\begingroup$ what is df. in your answer? There are only df1 and df2 but no df $\endgroup$
    – LearneR
    Jul 24, 2019 at 10:43
  • $\begingroup$ when some values are NaN values, it shows False. for other cases OK. need to fillna first. hope there is a shortcut to compare both NaN as True. $\endgroup$
    – ihightower
    May 3, 2020 at 19:53
15
$\begingroup$

Comparing values in two different columns

Using set, get unique values in each column. The intersection of these two sets will provide the unique values in both the columns.

Example:

df1 = pd.DataFrame({'c1': [1, 4, 7], 'c2': [2, 5, 1], 'c3': [3, 1, 1]})
 df2 = pd.DataFrame({'c4': [1, 4, 7], 'c2': [3, 5, 2], 'c3': [3, 7, 5]})
 set(df1['c2']).intersection(set(df2['c2']))

Output: {2, 5}


Comparing column names of two dataframes

Incase you are trying to compare the column names of two dataframes:

If df1 and df2 are the two dataframes: set(df1.columns).intersection(set(df2.columns))

This will provide the unique column names which are contained in both the dataframes.

Example:

df1 = pd.DataFrame({'c1': [1, 4, 7], 'c2': [2, 5, 1], 'c3': [3, 1, 1]})
df2 = pd.DataFrame({'c4': [1, 4, 7], 'c2': [3, 5, 2], 'c3': [3, 7, 5]})

set(df1.columns).intersection(set(df2.columns))

Output: {'c2', 'c3'}

$\endgroup$
2
  • 2
    $\begingroup$ I think the the question is about comparing the values in two different columns in different dataframes as question person wants to check if a person in one data frame is in another one. $\endgroup$ Jun 13, 2018 at 7:04
  • 1
    $\begingroup$ Thanks, I got the question wrong. I've updated the answer now. $\endgroup$
    – aathiraks
    Jun 13, 2018 at 9:24
0
$\begingroup$

Note that the columns of dataframes are data series. So if you take two columns as pandas series, you may compare them just like you would do with numpy arrays.

$\endgroup$
0
$\begingroup$

"I'd like to check if a person in one data frame is in another one."

The condition is for both name and first name be present in both dataframes and in the same row.

import pandas as pd
lst =["Juan","Pedro","Carlos"]
lst2=["Cabrera","Olivera","Paredes"]
lst3 =["Juan","Pedro","Carlos","Joselo"]
lst4=["Cabrera","Olivera","Rubianes"]
df = pd.DataFrame(list(zip(lst, lst2)),
           columns =['Name', 'First_name'])
df1 = pd.DataFrame(list(zip(lst3, lst4)),
           columns =['Name', 'First_name'])
column1 = "Name"
column2= "First_name"

def check_if_a_person_in_one_data_frame_is_in_another_one(df,df1,column1,column2,first_name,name):
    """This function check if two paired elements are present in two different dataframes."""
    #check that the name is in both columns
    col1=  name in list(df[column1]) and name in list(df1[column1])
    #check that first_name is in both columns
    col2 = first_name in list(df[column2]) and first_name in list(df1[column2])
    #check that both name and first_name are in the same row in the first dataframe
    try:
          both =(df[df["First_name"]==first_name].index[0]) == (df[df["Name"]==name].index[0])
    except:
          # if name or first_name does not exist
          pass
    #check that both name and first_name are in the same row in the second dataframe
    try:
          both =(df1[df1["First_name"]==first_name].index[0]) == (df1[df1["Name"]==name].index[0])
    except:
          # if name or first_name does not exist
          pass

    return col1 and col2 and both

column1 = "Name"
column2= "First_name" 
first_name = "Cabrera"
name ="Juan"  


check_if_a_person_in_one_data_frame_is_in_another_one(df,df1,column1,column2,first_name,name) 


True
$\endgroup$
0
$\begingroup$

You can get the whole common dataframe by using loc and isin.

df_common = df1.loc[df1['set1'].isin(df2['set2'])]

df_common now has only the rows which are the same col value in other dataframe.

$\endgroup$
0
$\begingroup$

@Hermes Morales your code will fail for this:

lst =["Juan","Pedro","Carlos"]
lst2=["Cabrera","Paredes", "Olivera"]
lst3 =["Juan","Pedro","Carlos","Joselo"]
lst4=["Cabrera","Olivera","Rubianes"]
df = pd.DataFrame(list(zip(lst, lst2)),
           columns =['Name', 'First_name'])
df1 = pd.DataFrame(list(zip(lst3, lst4)),
           columns =['Name', 'First_name'])
# I want to get a name and first_name which are there in df1 in a single row but not in df.
column1 = "Name"
column2= "First_name" 
first_name = "Olivera"
name ="Pedro" 

My suggestion would be to consider both the boths while returning the answer.

def check_for_both_names(df, df1, column1, column2, first_name, name):
    """This function check if two paired elements are present in two different dataframes."""
    # check that the name is in both columns
    col1 = name in list(df[column1]) and name in list(df1[column1])
    # check that first_name is in both columns
    col2 = first_name in list(df[column2]) and first_name in list(df1[column2])
    # check that both name and first_name are in the same row in the first dataframe
    try:
        both = (df[df["First_name"] == first_name].index[0]) == (
            df[df["Name"] == name].index[0])
    except:
        # if name or first_name does not exist
        pass
    # check that both name and first_name are in the same row in the second dataframe
    try:
        both1 = (df1[df1["First_name"] == first_name].index[0]) == (
            df1[df1["Name"] == name].index[0])
    except:
        # if name or first_name does not exist
        pass

    return col1 and col2 and both and both1

Please correct me if I'm wrong:)

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.