Trying to match each row in df1 with multiple other rows in df1

user_14593806 0
Viewed 158

So I am trying to figure out how to speed this operation up. Currently, the real dataset, takes about an hour to iterate over (~50,000 columns in df1 and df2), which doesn't seem very practical. Anyone have any suggestions? I.e pandas vectorization, pandas conditionals etc?

Basic operation: look at each row in df1, and compare to each row in df2. If the agent_id matches and the df1 'created_at_email' date is greater than or equal to the df2 'created_at' date, pull that row. Max rows allowed to pull is 4 for each row in df1 EDIT: ordered by most recent dates first.

Sample DataFrames:

df1 = pd.DataFrame({'unique_col': ['a', 'b', 'c', 'd', 'e', 'f', 'g'], 
                    'agent_id': [1, 2, 3, 1, 5, 6, 7], 
                    'created_at_email': ['1/5/2020', '1/6/2020', '1/8/2020', '1/3/2020', '1/4/2020', '1/7/2020', '1/2/2020']
                   })

df2 = pd.DataFrame({'unique_col': ['a', 'b', 'c', 'd', 'e', 'f', 'g'], 
                    'agent_id': [1, 1, 3, 1, 1, 1, 1], 
                    'created_at': ['1/4/2020', '1/5/2020', '1/6/2020', '1/9/2020', '1/2/2020', '1/3/2020', '1/4/2020']
                   })

Code (needs to be sped up):

# pre-sorting order created at column so function will iterate from most recent to least recent orders.
df2 = df2.sort_values(['created_at'], ascending=False)



# note: super not optimized
obj = []
for row in df1.itertuples():
    count = 0
    for row2 in df2.itertuples():
        if row[2] == row2[2]:
            if row2[3] <= row[3]:
                if count < 4: # returns the first 4 entries
                    c = [row2[3], row[3], row2[2], row[2], row[1], row2[1]]
                    obj.append(c)
                    count = count + 1

Output: (what it should look like)...
note: df1 can have multiple of the same agent_id and so can df2.
note: dates on right are greater than or equal to, dates on left.
note: unique_ids are just for checking if everything lines up.

cols: created_at, created_at_email, agent_id, agent_id, unique_id, unique_id 

[['1/5/2020', '1/5/2020', 1, 1, 'a', 'b'],
 ['1/4/2020', '1/5/2020', 1, 1, 'a', 'a'],
 ['1/4/2020', '1/5/2020', 1, 1, 'a', 'g'],
 ['1/3/2020', '1/5/2020', 1, 1, 'a', 'f'],
 ['1/6/2020', '1/8/2020', 3, 3, 'c', 'c'],
 ['1/3/2020', '1/3/2020', 1, 1, 'd', 'f'],
 ['1/2/2020', '1/3/2020', 1, 1, 'd', 'e']]

Thanks,

pandas dataframe tuples optimization python
2 Answers

A merge would be faster. I'm not sure about merging two 50k dataframes though:

(df1.assign(row=np.arange(len(df1)))          # record the row number in `df1`
    .merge(df2, on=['agent_id'])
    .query('created_at_email >= created_at')  # select rows with greater creat_at_email
    .groupby('row').head(4)                   # select max 4 rows for each row in df1
)

Output:

   unique_col_x  agent_id created_at_email  row unique_col_y created_at
0             a         1         1/5/2020    0            a   1/4/2020
1             a         1         1/5/2020    0            b   1/5/2020
3             a         1         1/5/2020    0            e   1/2/2020
4             a         1         1/5/2020    0            f   1/3/2020
9             d         1         1/3/2020    3            e   1/2/2020
10            d         1         1/3/2020    3            f   1/3/2020
12            c         3         1/8/2020    2            c   1/6/2020
user_42384080

Not sure if this is what you are after:

The code merges df1 and df2, filters based on the dates, creates a cumulative count, and keeps only rows where the counter is less than 4.

(
    df1.merge(
        df2.assign(agent_id_right=df2.agent_id.astype("Int64")),
        on="agent_id",
        how="left",
        suffixes=("", "_right"),
    )
    .query("created_at_email >= created_at")
    .sort_values("created_at", ascending=False)
    .assign(counter=lambda x: x.groupby(list(df1.columns), sort=False).cumcount())
    .loc[
        lambda x: x.counter.lt(4),
        [
            "created_at_email",
            "created_at",
            "agent_id",
            "agent_id_right",
            "unique_col",
            "unique_col_right",
        ],
    ]
    .sort_index(ignore_index=True)
)


  created_at_email  created_at  agent_id  agent_id_right    unique_col  unique_col_right
0   2020-01-05     2020-01-04      1           1             a             a
1   2020-01-05     2020-01-05      1           1             a             b
2   2020-01-05     2020-01-03      1           1             a             f
3   2020-01-05     2020-01-04      1           1             a             g
4   2020-01-08     2020-01-06      3           3             c             c
5   2020-01-03     2020-01-02      1           1             d             e
6   2020-01-03     2020-01-03      1           1             d             f
user_71757130
Related