Pandas check if value in one multiindex column is in any column, same row of different multiindex

user_15479264 0
Viewed 552

I have the following dataframe:

df = pd.DataFrame([[0, 1, 7, 0, 1, 8, 3, 0],
                   [7, 3, 4, 0, 4, 9, 7, 0]], 
                  columns=pd.MultiIndex.from_product([["first", "second"], 
                                                      ["A", "B", "C", "D"]]))
print(df)

  first          second         
      A  B  C  D      A  B  C  D
0     0  1  7  0      1  8  3  0
1     7  3  4  0      4  9  7  0

I want to check, whether the values in first are present in any of the columns of second. Only the same row should be compared.

The resulting dataframe should look like this:

      A      B      C     D
0  True   True  False  True
1  True  False   True  True

What is the best way of doing this? I have already played around with df["first"].isin(df["second"] but it only compares A with A, B with B, ... Also tried it in combination with .any() but I can't seem to make it work.

Your help is greatly appreciated!

Thank you in advance.

pandas dataframe python
4 Answers

Numpy broadcasting

np.any(df['first'].T.values[:, :, None] == df['second'].values, axis=-1).T

array([[ True,  True, False,  True],
       [ True, False,  True,  True]])
user_128331660

Another solution:

df['first'].apply(lambda x: x.isin(df.loc[x.name, ('second')]), axis=1)

Output:

    A         B       C      D
0   True    True    False   True
1   True    False   True    True
user_131313200

Here's an option with isin and a for loop on A,B,C,D:

seconds = df['second']
np.any([df['first'].isin(seconds[c]) for c in seconds], axis=0)

Output:

array([[ True,  True, False,  True],
       [ True, False,  True,  True]])
user_42384080

You could use a combination of numpy functions, along with zip:

In [79]: step1 = zip(np.split(df['first'].to_numpy(),2), 
                     np.split(df['second'].to_numpy(), 2))

In [80]: step2 = [np.isin(arr1, arr2) for arr1, arr2 in step1]

In [81]: np.vstack(step2)
Out[81]: 
array([[ True,  True, False,  True],
       [ True, False,  True,  True]])

You can pass it to the DataFrame constructor to return a dataframe:

pd.DataFrame(np.vstack(step2), columns = df['first'].columns)
Out[82]: 
      A      B      C     D
0  True   True  False  True
1  True  False   True  True
user_71757130
Related