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.
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]])
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
np.any(df['first'].T.values[:, :, None] == df['second'].values, axis=-1).T
array([[ True, True, False, True],
[ True, False, True, True]])
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