The fastest way to compare strings of two pandas data frames?

Question

So I have two pandas frames, A and B.

A - 1000 rows of 500 columns filled with binary values ​​indicating either presence or absence.

B is 1024 rows x 10 columns and represents a complete iteration of 0 and 1, therefore it has 1024 rows.

I am trying to find which rows in in the specific 10 columns of A correspond to a given row in B. I need the whole row to match, not element by element.

For example, I would like

A[(A.ix[:,(1,2,3,4,5,6,7,8,9,10)==(1,0,1,0,1,0,0,1,0,0)).all(axis=1)]

To return that the rows (3,5,8,11,15)in coincide with that row (1,0,1,0,1,0,0,1,0,0)B in these specific columns(1,2,3,4,5,6,7,8,9,10)

And I want to do this on every line in B. The best way I could do was this:

import numpy as np
for i in B:
    B_array = np.array(i)
    Matching_Rows = A[(A.ix[:,(1,2,3,4,5,6,7,8,9,10)] == B_array).all(axis=1)]
    Matching_Rows_Index = Matching_Rows.index

, while, 20 000 ; .

DataFrame.apply . ?

, - - , python.

!

Answer 1

merge reset_index - - B, A :

A = pd.DataFrame({'A':[1,0,1,1],
                  'B':[0,0,1,1],
                  'C':[1,0,1,1],
                  'D':[1,1,1,0],
                  'E':[1,1,0,1]})

print (A)
   A  B  C  D  E
0  1  0  1  1  1
1  0  0  0  1  1
2  1  1  1  1  0
3  1  1  1  0  1

B = pd.DataFrame({'0':[1,0,1],
                  '1':[1,0,1],
                  '2':[1,0,0]})

print (B)
   0  1  2
0  1  1  1
1  0  0  0
2  1  1  0

print (pd.merge(B.reset_index(), 
                A.reset_index(), 
                left_on=B.columns.tolist(), 
                right_on=A.columns[[0,1,2]].tolist(),
                suffixes=('_B','_A')))

   index_B  0  1  2  index_A  A  B  C  D  E
0        0  1  1  1        2  1  1  1  1  0
1        0  1  1  1        3  1  1  1  0  1
2        1  0  0  0        1  0  0  0  1  1    

print (pd.merge(B.reset_index(), 
                A.reset_index(), 
                left_on=B.columns.tolist(), 
                right_on=A.columns[[0,1,2]].tolist(),
                suffixes=('_B','_A'))[['index_B','index_A']])    

   index_B  index_A
0        0        2
1        0        3
2        1        1   

Answer 2

, 0 1 A B 1D , , . , . , 1D , np.in1d B A , , np.where, .

, -

# Setup 1D arrays corresponding to selected cols from A and entire B
S = 2**np.arange(10)
A_ID = np.dot(A[range(1,11)],S)
B_ID = np.dot(B,S)

# Look for matches that exist from B_ID in A_ID, whose indices 
# would be desired row indices that have matched from B
out_row_idx = np.where(np.in1d(A_ID,B_ID))[0]

-

In [157]: # Setup dataframes A and B with rows 0, 4 in A having matches from B
     ...: A_arr = np.random.randint(0,2,(10,14))
     ...: B_arr = np.random.randint(0,2,(7,10))
     ...: 
     ...: B_arr[2] = A_arr[4,1:11]
     ...: B_arr[4] = A_arr[4,1:11]
     ...: B_arr[5] = A_arr[0,1:11]
     ...: 
     ...: A = pd.DataFrame(A_arr)
     ...: B = pd.DataFrame(B_arr)
     ...: 

In [158]: S = 2**np.arange(10)
     ...: A_ID = np.dot(A[range(1,11)],S)
     ...: B_ID = np.dot(B,S)
     ...: out_row_idx = np.where(np.in1d(A_ID,B_ID))[0]
     ...: 

In [159]: out_row_idx
Out[159]: array([0, 4])

Answer 3

pandas loc ix , , . :

A.loc[(A[1]==B[1]) & (A[2]==B[2]) & (A[3]==B[3]) & A[4]==B[4]) & (A[5]==B[5]) & (A[6]==B[6]) & (A[7]==B[7]) & (A[8]==B[8]) & (A[9]==B[9]) & (A[10]==B[10])]

, , , . , - .

Answer 4

10 10- . B , 0 1023. , , 10 , .

, .

twos = pd.Series(np.power(2, np.arange(10)))

, MultiIndex stack, 10.

A = pd.DataFrame(np.random.binomial(1, .5, (1000, 500)))
A.columns = pd.MultiIndex.from_tuples(zip((A.columns / 10).tolist(), (A.columns % 10).tolist()))
A_ = A.stack(0)

A_.head()

Finally, I will multiply A_by twosto get an integer representation of each line and unstack.

A_.dot(twos).unstack()

Now this is a 1000 x 50 DataFrame, where each cell represents which row B we map for this particular 10 column column for this particular row A. There is not even a need for B.