Number of intersections dependent on order of intersecting data frames

user_4247704 0
Viewed 118

I try to compute the number of intersections of two data frames but the results differ depending on whether I intersect A with B or B with A. How can that be?

a_b= a.index.intersection(b.index)
b_a= b.index.intersection(a.index)
len(a_b), len(b_a)

returns

(10735, 10927)

Unfortunately the documentation isn't quite helpful on this one.

pandas python
2 Answers

Index are often supposed to only contain unique values, and weird things happen when this requirement is not met. I assume that you are experiencing it. Here is a short example exhibiting the problem:

>>> dfa = pd.DataFrame(1, index=list('ABCDAC'), columns=['X'])
>>> dfa
>>> dfb = pd.DataFrame(1, index=list('ABCEC'), columns=['X'])
>>> dfa.index.intersection(dfb.index)
Index(['A', 'B', 'C', 'C'], dtype='object')
>>> dfb.index.intersection(dfa.index)
Index(['A', 'A', 'B', 'C', 'C'], dtype='object')
>>>

Behaviour with non unique indexes is not explicit in the documentation, and I would not rely on it without that.

So my advice (if it is relevant in your use case) is to use unique() on both indexes:

a_b= a.index.unique().intersection(b.index.unique())
b_a= b.index.unique().intersection(a.index.unique())
len(a_b), len(b_a)
user_35452730

Even though the intersection of both indices will contain the same elements in both cases, the intersection can contain duplicate elements (unlike what would happen when using set.difference). Note that indices allow for set-like operations, however the behavior can be different, as in the cases where indices are duplicated.

So the difference in lengths accounts for the fact that both indices contain different amount of duplicates. Here's an example thgat illustrates this:

a = pd.DataFrame(index=np.random.randint(0,10, 10))
b = pd.DataFrame(index=np.random.randint(0,10, 10))

int_1 = a.index.intersection(b.index)
int_2 = b.index.intersection(a.index)

len(int_1)
# 7

len(int_2)
# 5

Even though the Index.difference is empty:

int_1.difference(int_2)
# Int64Index([], dtype='int64')

As mentioned the difference in length accounts for the fact the the amount of duplicates in both indices is different:

int_1.duplicated().sum()
# 3

int_2.duplicated().sum()
# 1
user_96986840
Related