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.
-
$\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 LyristJun 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$– dsaxtonJul 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 SymeJul 13, 2018 at 20:31
8 Answers
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.
-
2$\begingroup$ I think we want to use an inner join here and then check its shape. $\endgroup$– dsaxtonJul 13, 2018 at 13:43
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
-
1$\begingroup$ This should be the answer in my opinion. $\endgroup$ Jan 16, 2020 at 9:12
-
1
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
.
-
$\begingroup$ what is
df.
in your answer? There are onlydf1
anddf2
but nodf
$\endgroup$– LearneRJul 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$ May 3, 2020 at 19:53
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'}
-
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$ Jun 13, 2018 at 9:24
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.
"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
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.
@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:)