12

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

  1. the common word among all columns
  2. 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

Rushdi Shams
  • 2,423
  • 19
  • 31
Tikku
  • 137
  • 1
  • 1
  • 6

3 Answers3

22

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']
piRSquared
  • 285,575
  • 57
  • 475
  • 624
  • 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. – Tikku Oct 04 '17 at 08:10
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)
cs95
  • 379,657
  • 97
  • 704
  • 746
  • 1
    `reduce(np.intersect1d, [df1.A, df2.A, df3.A])` – piRSquared Oct 04 '17 at 03:13
  • 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 Oct 04 '17 at 08:12
  • 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. – Tikku Oct 04 '17 at 09:58
  • does this still work if the dataframes have different length values? – kiyas Jun 15 '20 at 11:29
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:

Edward Ji
  • 745
  • 8
  • 19
Voluman
  • 11
  • 2