Faster way than nested for loops for custom conditions on multiple columns in two DataFrames

user_7590303 0
Viewed 85

I have two Dataframes as below:

df1
+------------+-------------------+-------------+
| Name       | Topic             |   Date      |
+------------+-------------------+-------------+
|        ABC |  Data Science     | 2020-01-01  |
|        DEF |  Machine Learning | 2021-03-06  |
|        ABC |  Cybersecurity    | 2021-01-05  |
|        BHL |  Cloud Computing  | 2020-11-09  |
+------------+-------------------+-------------+

It has around 50,000 rows

The second dataframe has several columns, but I am interested in only following three:

df2
+------------------------------------+------+-------------+
| Description                        | Name | Created Date|
+------------------------------------+------+-------------+
| This is good Data Science project. |  XYZ | 2021-06-04  |
| Cybersecurity is important.        |  BBB | 2021-02-03  |
| I am Data Science Professional     |  ABC | 2021-02-08  |
| Machine Learning is strategic.     |  DEF | 2021-03-01  |
+------------------------------------+------+-------------+

It has around 300,000 rows.

I want to find all the rows from df2 where:

For each unique (Name, Topic and Date) in df1, find rows in df2 where 'Name' matches and 'Created Date' is within the next six months of 'Date' from df1, as well as the 'Topic' is in 'Description'.

I have used two for loops to iterate over each dataframes' rows as shown below. But, the problem is that since there are large number of rows and iterating over each row this way is not the best method I feel. Can you please suggest any other way to do it faster and efficiently. I also want to attach 'Topic', 'Date' from df1 to each matching row of df2(some kind of merge, but not sure how).

My code is as follows:

import pandas as pd
from dateutil.relativedelta import relativedelta

df1 = df1.drop_duplicates()  # Drop duplicate entries

df_final = pd.DataFrame()

for index1, row1 in df1.iterrows():
    future_date = row1['Date'] + relativedelta(months=6)
    for index2, row2 in df2.iterrows():
        if ((row1['Name'] == row2['Name']) and (row1['Date] < row2['Created Date'] < future_date)
            and (row1['Topic'] in row2['Description'])):
            df_final = df_final.append(row2)
        else:
             continue
pandas dataframe python
1 Answers

try those steps:

# drop dup rows in df1
df1 = df1.drop_duplicates()
# merge df2 with df1 on name
df2 = df2.merge(df1, how='inner', left_on='Name', right_on='Name')
future_date = df2['Date'] + relativedelta(months=6)
# now select based on requirement
df2 = df2[(df2['Date'] > df2['Created Date']) & (df['Date'] < future_date)]
df2 = df2[df2.apply(lambda x: x['Topic'] in x['Description'], axis=1)]
user_74607020
Related