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

24

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

edited Oct 4, 2017 at 3:13

answered Oct 4, 2017 at 3:07

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tikku Over a year ago

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

9

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)

edited Oct 4, 2017 at 3:14

answered Oct 4, 2017 at 3:05

cs95

406k106 gold badges744 silver badges797 bronze badges

4 Comments

piRSquared Over a year ago

reduce(np.intersect1d, [df1.A, df2.A, df3.A])

Tikku Over a year ago

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.

Tikku Over a year ago

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.

kiyas Over a year ago

does this still work if the dataframes have different length values?

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

1

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:

edited Jun 12, 2022 at 8:41

Edward Ji

80011 silver badges20 bronze badges

answered Jun 11, 2022 at 17:25

Voluman

112 bronze badges

Collectives™ on Stack Overflow

Finding common elements between multiple dataframe columns

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related