CODEWITHSUNDEEP
pandasnumpydataframe
prKn
prKn

Reputation: 27

How to use one Dataframe to search values and indices of another Dataframe

I have two Dataframes df1:

    x_a  y_a
0    39   87
1   379   97
2   280  105
3   348  107
4   275  111
5   717  124
6   338  127
7   301  129
8   351  133
9   403  134
10  714  135
11  716  136
12  451  139
13  677  154
14  430  155
15  575  160
16  738  163
17  642  165
18  463  176
19  534  184

and df2:

     x_a  y_a  x_b  y_b  distance
88   717  124  720  129  5.830952
103  338  127  338  127  0.000000
168  714  135  720  129  8.485281
170  714  135  706  136  8.062258
184  716  136  720  129  8.062258
268  738  163  734  158  6.403124
317  534  184  536  184  2.000000

I would like to search the values x_a and y_a of df2 in df1 and return the indices of df1 if they are both the same. For this example:

717 / 124 --> 5
338 / 127 --> 6
714 / 135 --> 10
...

Upvotes: 1

Views: 52

Answers (2)

Swagga Ting
Swagga Ting

Reputation: 602

You can copy the index to a new column and then merge the two dataframes:

>>> df1 = DataFrame({'x_a': [39, 379, 280, 348], 'y_a': [87, 97, 105, 107]})
>>> df1['index1'] = df1.index
>>> df1
   x_a  y_a  index1
0   39   87       0
1  379   97       1
2  280  105       2
3  348  107       3
>>> df2 = DataFrame({'x_a': [280, 1], 'y_a': [105, 2]})
>>> merged = df1.merge(df2, on=['x_a', 'y_a'])
>>> merged
   x_a  y_a  index1
0  280  105       2
>>> merged.set_index(merged['index1'], inplace=True)
>>> merged
        x_a  y_a  index1
index1
2       280  105       2

Upvotes: 2

Toby Petty
Toby Petty

Reputation: 4660

You can create a column of x_a and y_a combined into a tuple in both DataFrames:

df1["combined"] = tuple(zip(df1["x_a"], df1["y_a"]))
df2["combined"] = tuple(zip(df2["x_a"], df2["y_a"]))

Then filter DF1 on values in DF2:

df1[df1["combined"].isin(df2["combined"].unique())].index

Upvotes: 1

Related Questions