How do I compare columns in different data frames?

Question

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.

Answer 1

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.

Answer 2

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:

True = common
False = different

Answer 3

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.

Answer 4

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'}

Answer 5

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.

Answer 6

"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

Answer 7

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.

Answer 8

@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:)