CODEWITHSUNDEEP
pythonpandas
Jmegan042
Jmegan042

Reputation: 251

Search for matching values in two columns using pandas

I am looking for a way to create a function that matches a given value across a dataframe, such that if a match is found in column A, check to see if column B matches as well. I have created a list of values to search for in column A and column B. 

      A       B       C       D 
0   0.65    12.05   253.41  1.53

1   0.76    9.64    206.97  1.25

2   0.80    9.82    110.98  0.67

3   0.81    12.15   637.04  386.43

The code I'm currently using to do this amounts to this: 

Sample = pd.read_csv("tableabove.csv")
Database = pd.read_csv("Databasefile.csv")
SearchVals_A = list(Sample["A"])
SearchVals_B = list(Sample["B"])
HitsColA = [Database[Database["A"].apply(np.isclose,b=i,atol=0.02)for i in SearchVals_A]
HitsColA2 = pd.concat(HitsColA)
HitsColB = [HitsColA2[HitsColA2["B"].apply(np.isclose,b=i,atol=0.02)for i in SearchVals_B]
HitsColB2 = pd.concat(HitsColB)
Final = HitsColB2.drop_duplicates(["A"])

This creates a dataframe of matched values, so that if a value is found in column A that matches it searches that dataframe for a given value in column B, but I cannot figure out a way to "associate" that BOTH values must match. When I run this method, it will find that 0.76 matches in column A and then INDEPENDENTLY search for values in column B. But if both values are associated in real life, then it will give a false hit in the results if index 1 has a value that it is also in the list for column B.

Any ideas?

Upvotes: 1

Views: 3446

Answers (1)

tooty44
tooty44

Reputation: 7089

You can use .tolist() to change your columns A and B into python lists.

Then you can simply iterate over each of the lists and append to a new list with all matching elements:

matching_vals = []

for val in specified_vals_list:
    if val in list_A:
        if val in list_B:
            matching_vals.append(val)
        else:
            continue
    else:
        continue

print (matching_vals)

Upvotes: 1

Related Questions