Lookup all cells of one row in another df and return flag if all exist using pandas

user_14125685 0
Viewed 67

there are two dataframes A and B, df A like below including main nodes and their depends against each node:

 NODE          Depend
=====   =============================
T1234   T1235   T1236   T1237   T1238
-------------------------------------
B1234   B1235   B1236   B1237   B1238
-------------------------------------
N1234   N1235   N1236   N1237   N1238
-------------------------------------

I wanna lookup each rows from df A in df B to find out are they all available in the df B or not.

Node
=====
T1234
T1235
T1236
T1237
T1238
B1234
B1235
B1238
N1234
N1237

if all cells in one row including the main node of df A are available in the df B then return True flag else return False in df A as a new column like below:

 NODE          Depend                 flag
=====   ============================= =====
T1234   T1235   T1236   T1237   T1238   T
-------------------------------------------
B1234   B1235   B1236   B1237   B1238   F
-------------------------------------------
N1234   N1235   N1236   N1237   N1238   F
-------------------------------------------

what is the most efficient way to do this via pandas? i tried many ways like merging two df but found no way to can compare whole one row to lookup in the df B.

pandas row lookup python
2 Answers

I would make dfA comma separated. Derive a list of column Depend.Derive a list of dfB. Compare the lists using sets and use np.where (condition, True if Condition, False if condition). where condition here is that the length of what is common between the lists should be equal to four or juts more than 3.

print(dfA)#Separated by comma


  NODE                   Depend
0  T1234  T1235,T1236,T1237,T1238
1  B1234  B1235,B1236,B1237,B1238
2  N1234  N1235,N1236,N1237,N1238

s=list(dfB.Node.values.tolist())#dfB into a list
dfA.Depend=dfA.Depend.str.split(',')#dfA.Depend into a list


dfA['flag']=np.where(dfA.Depend.apply(lambda x:len([*{*x}&{*s}])>3), 'T','F')
print(dfA)



NODE                        Depend    flag
0  T1234  [T1235, T1236, T1237, T1238]    T
1  B1234  [B1235, B1236, B1237, B1238]    F
2  N1234  [N1235, N1236, N1237, N1238]    F
user_89869750

Here is another way. I created dummy column labels for the first data frame:

# the first data frame
print(df_A)
         d-1    d-2    d-3    d-4
Node                             
T1234  T1235  T1236  T1237  T1238
B1234  B1235  B1236  B1237  B1238
N1234  N1235  N1236  N1237  N1238

# the second data frame
print(df_B)
0    T1234
1    T1235
2    T1236
3    T1237
4    T1238
5    B1234
6    B1235
7    B1238
8    N1234
9    N1237
Name: Node, dtype: object

Check each row of A against B:

df_A['flag'] = df_A.apply(lambda x: x.isin(df_B).all(), axis=1)
print(df_A)

         d-1    d-2    d-3    d-4   flag
Node                                    
T1234  T1235  T1236  T1237  T1238   True
B1234  B1235  B1236  B1237  B1238  False
N1234  N1235  N1236  N1237  N1238  False
user_136085990
Related