Finding common elements between multiple dataframe columns

Question

Hope you could help me. I am new to python and pandas, so please bear with me. I am trying to find the common word between three data frames and I am using Jupiter Notebook.

Just for example:

df1= A dog cat cow duck snake df2= A pig snail bird dog df3= A eagle dog snail monkey

There is only one column in all data frames that is A. I would like to find

the common word among all columns
the words that are unique to their own columns and not in common.

Example:

duck is unique to df1, snail is unique to df2 and monkey is unique to df3.

I am using the below code to some use but not getting what I want straightforward,

df1[df1['A'].isin(df2['A']) & (df2['A']) & (df3['A'])]

Kindly let me know where I am going wrong. Cheers

piRSquared · Accepted Answer · 2017-10-04 03:13:11Z

Simplest way is to use set intersection

list(set(df1.A) & set(df2.A) & set(df3.A)) ['dog']

However if you have a long list of these things, I'd use reduce from functools. This same technique can be used with @cᴏʟᴅsᴘᴇᴇᴅ's use of np.intersect1d as well.

from functools import reduce list(reduce(set.intersection, map(set, [df1.A, df2.A, df3.A]))) ['dog']

Hi there, thanks very much for your inout. Yes, it does work however when I get the output they all come within single quotes separated by commas. Is it possible to retrieve them as a clean list. Many thanks for your time and help.

cs95 · Accepted Answer · 2017-10-04 03:14:39Z

The problem with your current approach is that you need to chain multiple isin calls. What's worse is that you'd need to keep track of which dataframe is the largest, and you call isin on that one. Otherwise, it doesn't work.

To make things easy, you can use np.intersect1d:

>>> np.intersect1d(df3.A, np.intersect1d(df1.A, df2.A)) array(['dog'], dtype=object)

Similar method using functools.reduce + intersect1d by piRSquared:

>>> from functools import reduce # python 3 only >>> reduce(np.intersect1d, [df1.A, df2.A, df3.A]) array(['dog'], dtype=object)

hi coldspeed, it works!! thanks for that. I would like to ask you the same question I have asked above. How to get the output without quotes or commas? many thanks for your time and help.
thanks for helping. Finally managed to figure it temporarily. Just converted the list into a data frame and that looks neat. Thanks for all your help.
does this still work if the dataframes have different length values?

Edward Ji · Accepted Answer · 2022-06-12 08:41:08Z

You can use the how='inner' keyword parameter in pd.merge.

For example,

dfs = [df1,df2,df3] import functools as ft df_common = ft.reduce(lambda left, right: pd.merge(left, right, on='A', how='inner'), dfs)

References:

Collectives™ on Stack Overflow

Finding common elements between multiple dataframe columns

3 Answers 3

1 Comment

4 Comments

Comments

Linked

Hot Network Questions