Get index of DataFrame for rows that are identical to elements of an array

user_8766822 0
Viewed 127

I have a DataFrame (temp) and an array (x), whose elements correspond to some of the lines of the DataFrame. I want to get the indexes of the DataFrame whose corresponding records are identical to the elements of the array:

For example:

 temp = pd.DataFrame({"A": [1,2,3,4], "B": [4,5,6,7], "C": [7,8,9,10]})

    A   B   C
0   1   4   7
1   2   5   8
2   3   6   9
3   4   7   10

x = np.array([[1,4,7], [3,6,9]])

It should return the indexes: 0 and 2.

I was trying unsuccessfully with this:

temp.loc[temp.isin(x[0])].index
pandas dataframe numpy python
3 Answers

I would convert to Multiindex and then to isin with np.where

i = pd.MultiIndex.from_frame(temp[['A','B','C']])
out = np.where(i.isin(pd.MultiIndex.from_arrays(x.T)))[0]

print(out)
#[0 2]

Or with merge:

cols = ['A','B','C']
out = temp.reset_index().merge(pd.DataFrame(x,columns=cols)).loc[:,'index'].tolist()

Or with np.isin and all

out = temp.index[np.isin(temp[['A','B','C']],x).all(1)]
user_98406370

Since you need to match entire rows of the DataFrame to rows in the numpy array, you can convert the DataFrame to an array and then use enumerate to loop and return the indices:

temp_arr = temp.to_numpy()

for idx, row in enumerate(temp_arr):
    if row in x:
        print(idx)

Output:

0
2

A more elegant way using list comprehension would be:

idx_list = [i for i, row in enumerate(temp_arr) if row in x ]    
print(idx_list)

Output:

[0, 2]
user_156044840

Using numpy broadcasting:

array = temp.to_numpy()[:, None]
mask = (array == x).all(axis=-1).any(axis=-1)
temp.index[mask]
user_25389390
Related