compare different pandas dataframes to find matching rows

Question

I have two dataframes: One contains of name and year.

**name** **year**

ram       1873

rob       1900

Second contains names and texts.

**name** **text**

ram      A good kid

ram     He was born on 1873

rob     He is tall

rob     He is 12 yrs old

rob     His father died at 1900

I want to find the indices of the rows of second dataframe where the name of second dataframe matches with name of the first df and the text in second df contains the year in first df.

The result should be indices 1,4

My Code:

ind_list = []
for ind1, old in enumerate(A.name):
    for ind2, new in enumerate(B.name):
        if A.name[ind1] == B.name[ind2]:
            if A.year[ind1] in B.text[ind2]:
                ind_list.append(ind2)

Any better way to write the above code?

Answer 1

Here is what we start with.

In [16]: df1
Out[16]:
  name  year
0  ram  1873
1  rob  1900

In [17]: df2
Out[17]:
  name                     text
0  ram               A good kid
1  ram      He was born on 1873
2  rob               He is tall
3  rob         He is 12 yrs old
4  rob  His father died at 1900

What you probably want to do is merge your two DataFrames. If you're familiar with SQL, this is just like a table join. The pd.merge step essentially "adds" the columns from df1 to df2 by checking where the two DataFrames match on the column "name". Then, once you have the columns you want ("year" and "text") matching according to the "name" column, we apply the function lambda x: str(x.year) in x.text (which checks if the year is present in the text) across the rows (axis=1).

In [18]: cond = pd.merge(
    ...:     left=df2,
    ...:     right=df1,
    ...:     how="left",
    ...:     left_on="name",
    ...:     right_on="name",
    ...: ).apply(lambda x: str(x.year) in x.text, axis=1)

This gives us a Series which has the same index as your second DataFrame, and contains boolean values telling you if your desired condition is met or not.

In [19]: cond
Out[19]:
0    False
1     True
2    False
3    False
4     True
dtype: bool

Then, we filter your Series to where the condition is true, and give the index, optionally converting it to a list.

In [20]: cond[cond].index
Out[20]: Int64Index([1, 4], dtype='int64')
In [21]: cond[cond].index.tolist()
Out[21]: [1, 4]

If all you need later on is to iterate over the indices you've gotten, In [18] and In [20] will suffice.