CODEWITHSUNDEEP
pythonpandasdataframeleft-joindata-mining
Finito
Finito

Reputation: 25

Left join two pandas dataframes under multiple conditions

I have two dataframes. One in which are the search queries of a user in a webshop (102377 rows) and another in which are the clicks of the user out of the search (8004 rows).

    queries:
index   term                         timestamp
...
10      tight                        2018-09-27 20:09:23
11      differential pressure        2018-09-27 20:09:30
12      soot pump                    2018-09-27 20:09:32
13      gas pressure                 2018-09-27 20:09:46
14      case                         2018-09-27 20:11:29
15      backpack                     2018-09-27 20:18:35
...

clicks
    index   term             timestamp               artnr
    ...
    245     soot pump        2018-09-27 20:09:25    9150.0
    246     dungarees        2018-09-27 20:10:38    7228.0
    247     db23             2018-09-27 20:10:40    7966.0
    248     db23             2018-09-27 20:10:55    7971.0
    249     sealing blister  2018-09-27 20:12:05    7971.0
    250     backpack         2018-09-27 20:18:40    8739.0
    ...

what I want to do, is join the clicks in the queries. If queries.term equals clicks.term and the difference between clicks.timestamp - queries.timestamp is under 10 and above 0 seconds, the term of the queries dataframe should be replaced by the artnr of the clicks dataframe, so that it looks like:

    queries:
index   term                         timestamp
...
10      tight                        2018-09-27 20:09:23
11      differential pressure        2018-09-27 20:09:30
12      9150.0                       2018-09-27 20:09:32
13      gas pressure                 2018-09-27 20:09:46
14      case                         2018-09-27 20:11:29
15      8739.0                       2018-09-27 20:18:35
...

My first approach was the following:

df_Q['term'] = np.where(((((df_CS.timestamp-df_Q.timestamp).dt.total_seconds() <= 10.0) & 
                       (df_CS.timestamp-df_Q.timestamp).dt.total_seconds() >= 0) & 
                       (df_CS.term.str == df_Q.term.str)), df_CS['artnr'], df_CS['term'])

But this just generated the following error:

ValueError: operands could not be broadcast together with shapes (102377,) (8004,) (8004,)

Is anyone having an idea on how to solve this with a left join, or another solution?

Upvotes: 0

Views: 241

Answers (1)

Rajesh
Rajesh

Reputation: 786

queries = pd.DataFrame({'term': ['tight', 'differential pressure', 'soot pump', 'gas pressure', 'case', 'backpack'],
                        'timestamp': ['2018-09-27 20:09:23', '2018-09-27 20:09:30', '2018-09-27 20:09:32', '2018-09-27 20:09:46', '2018-09-27 20:11:29', '2018-09-27 20:18:35']})
print(queries)
                    term            timestamp
0                  tight  2018-09-27 20:09:23
1  differential pressure  2018-09-27 20:09:30
2              soot pump  2018-09-27 20:09:32
3           gas pressure  2018-09-27 20:09:46
4                   case  2018-09-27 20:11:29
5               backpack  2018-09-27 20:18:35

clicks = pd.DataFrame({'term': ['soot pump', 'dungarees', 'db23', 'db23', 'sealing blister', 'backpack'],
                       'timestamp': ['2018-09-27 20:09:25', '2018-09-27 20:10:38', '2018-09-27 20:10:40', '2018-09-27 20:10:55', '2018-09-27 20:12:05', '2018-09-27 20:18:40'],
                       'artnr':[9150.0, 7228.0, 7966.0, 7971.0, 7971.0, 8739.0]})
print(clicks)
              term            timestamp   artnr
0        soot pump  2018-09-27 20:09:25  9150.0
1        dungarees  2018-09-27 20:10:38  7228.0
2             db23  2018-09-27 20:10:40  7966.0
3             db23  2018-09-27 20:10:55  7971.0
4  sealing blister  2018-09-27 20:12:05  7971.0
5         backpack  2018-09-27 20:18:40  8739.0

First, sort both data frames on the timestamp

queries['timestamp'] = pd.to_datetime(queries['timestamp'])
clicks['timestamp'] = pd.to_datetime(clicks['timestamp'])

queries.sort_values('timestamp', ascending=True, inplace=True)
clicks.sort_values('timestamp', ascending=True, inplace=True)

Then use pd.merge_asof() to join on 'term' column and only if the time difference of 'timestamp' is within the 10 seconds

df = pd.merge_asof(
     queries, # left data
     clicks, # right data
     on="timestamp", # column to check time differnece
     by="term", # column to join on
     tolerance=pd.Timedelta("10s"), # time difference
     direction='forward', # join only if timestamp in right data after timestamp in left data
     )

The 'artnr' column will have NA if no match was found. So use non NA values of 'artnr' to replace in 'term'

df['term'][df['artnr'].notna()] = df['artnr']
print(df)

                    term           timestamp   artnr
0                  tight 2018-09-27 20:09:23     NaN
1  differential pressure 2018-09-27 20:09:30     NaN
2              soot pump 2018-09-27 20:09:32     NaN
3           gas pressure 2018-09-27 20:09:46     NaN
4                   case 2018-09-27 20:11:29     NaN
5                   8739 2018-09-27 20:18:35  8739.0

Upvotes: 2

Related Questions