Shubham R
Shubham R

Reputation: 7644

Check for a value from one dataframe exists in another

I have 2 dataframes.

df1

id    marks
1      100
2      200
3      300


df2

name    score  flag
'abc'    100    T
'zxc'    300    F

What I am looking for is the from the first row of my df1, check the corresponding column score in my df2 , and get the Flag of that.

i.e

row = df1.iloc[0]
marks = row['marks']

find the corresponding value in marks from df2, and give what Flag it has.

i.e for marks 100 ====corresponds to score 100 and flag = T

I don't want to use pandas merge, as I only want to look for my first row. using iloc[0]

Upvotes: 2

Views: 7878

Answers (3)

cs95
cs95

Reputation: 403178

First Row

Option 1

You can use df.isin:

first_flag = df2[df2.score.isin([df1.marks[0]])].flag
print(first_flag)
0    T

To get the values, use .values.tolist():

print(first_flag.values.tolist())
['T']

To get a single the value as a single item, use .item:

print(first_flag.item())
'T'

Option 2

Using df.eval:

score = df1.marks[0]
first_flag = df2[df2.eval('score == {}'.format(score))].flag
print(first_flag)
0    T

Option 3

Using df.eq

score = df1.marks[0]
first_flag = df2[df2.score.eq(score)].flag
print(first_flag) 
0    T

All Rows

Use df.merge.

flags = df1.merge(df2, left_on='marks', right_on='score').flag
print(flags)
0    T
1    F
Name: flag, dtype: object

If you want to retrieve NaN for rows where no flag exists, you can do a left join:

flags = df1.merge(df2, left_on='marks', right_on='score', how='left').flag
print(flags)
0      T
1    NaN
2      F

Upvotes: 1

jezrael
jezrael

Reputation: 863631

I think you need:

a = df2.loc[df2['score'].eq(df1['marks'].iat[0]), 'flag']
print (a)
0    T
Name: flag, dtype: object

a = df2.loc[df2['score'] == df1['marks'].iat[0], 'flag']
print (a)
0    T
Name: flag, dtype: object

For scalar output:

b = 'no match' if a.empty else a.item()
print (b)
T

Upvotes: 0

Zero
Zero

Reputation: 77027

You can

In [437]: df2[df2.score.eq(df1.iloc[0].marks)]   # or  df1.marks[0]
Out[437]:
    name  score flag
0  'abc'    100    T

In [438]: df2[df2.score.eq(df1.iloc[0].marks)].flag
Out[438]:
0    T
Name: flag, dtype: object

Or, use

In [442]: df2[df2.score == df1.marks.iloc[0]].flag
Out[442]:
0    T
Name: flag, dtype: object

Or, use query

In [441]: df2.query('score == @df1.marks[0]').flag
Out[441]:
0    T
Name: flag, dtype: object

Upvotes: 0

Related Questions