Filter data based on multiple columns in second dataframe python

Question

I have a Dataframe df1 (original table) with multiple columns.

I have a filtered DataFrame df2 with columns date, agent_id, gps1, gps2 only 4 columns.

In df1, I have date, agent_id and final_gps along with other columns.

I want to filter all the data from df1 which exists in df2, I want to compare based on.

Df1.date == df2.date & df1.agent_id == df2.agent_id & (df1.final_gps == df2.gps1 or df1.final_gps == df2.gps2)

df2 sample

date       agent_id  gps1    gps2
14-02-2020  12abc   (1,2)   (7,6)
14-02-2020  12abc   (3,4)   (7,6)
14-02-2020  33bcd   (6,7)   (8,9)
20-02-2020  44hgf   (1,6)   (3,7)
20-02-2020  12abc   (3,5)   (3,1)
20-02-2020  33bcd   (3,4)   (3,6)
21-02-2020  12abc   (4,5)   (5,4)

df1 sample

date       agent_id final_gps   ….
10-02-2020  12abc   (1,2)       …
10-02-2020  33bcd   (8,9)       …
14-02-2020  12abc   (1,2)       …
14-02-2020  12abc   (7,6)       …
14-02-2020  12abc   (3,4)       …
14-02-2020  33bcd   (6,7)       …
14-02-2020  33bcd   (8,9)       …
14-02-2020  33bcd   (1,1)       …
14-02-2020  33bcd   (2,2)       …
18-02-2020  12abc   (1,2)       …
19-02-2020  44hgf   (6,7)       …
20-02-2020  12abc   (3,5)       …
20-02-2020  12abc   (3,1)       …
20-02-2020  44hgf   (1,6)       …
20-02-2020  44hgf   (3,7)       …

required output:-

date       agent_id final_gps   ….
14-02-2020  12abc   (1,2)       …
14-02-2020  12abc   (7,6)       …
14-02-2020  12abc   (3,4)       …
14-02-2020  33bcd   (6,7)       …
14-02-2020  33bcd   (8,9)       …
20-02-2020  12abc   (3,5)       …
20-02-2020  12abc   (3,1)       …
20-02-2020  44hgf   (1,6)       …
20-02-2020  44hgf   (3,7)       …

I tried this but it is giving me all the matching records which exists in df2, but I want strictly data only for those agent_id on that particular date and particular gps matching condition from df1.

df = df1[df1['date'].isin(df2['date']) & 
         df1['agent_id'].isin(df2['agent_id']) & 
        (df1['final_gps'].isin(df2['gps1']) | df1['final_gps'].isin(df2['gps2']))]

Is same output of both solutions?

jezrael
– jezrael

2022-03-11 09:15:19 +00:00
Commented Mar 11, 2022 at 9:15 — jezrael
– jezrael, Commented Mar 11, 2022 at 9:15

jezrael · Accepted Answer · 2022-03-11 07:18:59Z

2

Use DataFrame.melt for reshape gps1 and gps2 to final_gps first, so possible merge by all 3 columns (not necessary define on), remove duplicates by all columns and last sorting:

df = (df2.melt(id_vars=['date','agent_id'],
               value_vars=['gps1','gps2'],
               value_name='final_gps')
        .drop('variable', axis=1)
        .merge(df1)
        .drop_duplicates()
        .sort_values(by=['date','agent_id'], ignore_index=True))
print (df)
         date agent_id final_gps
0  14-02-2020    12abc     (1,2)
1  14-02-2020    12abc     (3,4)
2  14-02-2020    12abc     (7,6)
3  14-02-2020    33bcd     (6,7)
4  14-02-2020    33bcd     (8,9)
5  20-02-2020    12abc     (3,5)
6  20-02-2020    12abc     (3,1)
7  20-02-2020    44hgf     (1,6)
8  20-02-2020    44hgf     (3,7)

Details:

print (df2.melt(id_vars=['date','agent_id'],
               value_vars=['gps1','gps2'],
               value_name='final_gps'))

          date agent_id variable final_gps
0   14-02-2020    12abc     gps1     (1,2)
1   14-02-2020    12abc     gps1     (3,4)
2   14-02-2020    33bcd     gps1     (6,7)
3   20-02-2020    44hgf     gps1     (1,6)
4   20-02-2020    12abc     gps1     (3,5)
5   20-02-2020    33bcd     gps1     (3,4)
6   21-02-2020    12abc     gps1     (4,5)
7   14-02-2020    12abc     gps2     (7,6)
8   14-02-2020    12abc     gps2     (7,6)
9   14-02-2020    33bcd     gps2     (8,9)
10  20-02-2020    44hgf     gps2     (3,7)
11  20-02-2020    12abc     gps2     (3,1)
12  20-02-2020    33bcd     gps2     (3,6)
13  21-02-2020    12abc     gps2     (5,4)

edited Mar 11, 2022 at 7:18

answered Mar 11, 2022 at 7:12

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

pquest Over a year ago

Could you please confirm why you are not using column gps1 and gps2 in df2 while filtering data from df2

jezrael Over a year ago

@pquest - because are melted to final_gps

jezrael Over a year ago

@pquest - added ouput of melt to answer for see whats happens

pquest Over a year ago

Thanks..I will study about melt function..

adir abargil Over a year ago

the moment i saw “melt” i knew its jezreal

score 1 · Accepted Answer · 2022-03-11 07:34:11Z

1

You could use multiple isin and chain them using & operator. Since final_gps can be either gps1 or gps2, we use | operator in brackets:

out = (df1[df1['date'].isin(df2['date']) & 
           df1['agent_id'].isin(df2['agent_id']) & 
           (df1['final_gps'].isin(df2['gps1']) | df1['final_gps'].isin(df2['gps2']))]
       .reset_index(drop=True))

Output:

         date agent_id final_gps ….
0  14-02-2020    12abc    (1, 2)  …
1  14-02-2020    12abc    (7, 6)  …
2  14-02-2020    12abc    (3, 4)  …
3  14-02-2020    33bcd    (6, 7)  …
4  14-02-2020    33bcd    (8, 9)  …
5  20-02-2020    12abc    (3, 5)  …
6  20-02-2020    12abc    (3, 1)  …
7  20-02-2020    44hgf    (1, 6)  …
8  20-02-2020    44hgf    (3, 7)  …

edited Mar 11, 2022 at 7:34

answered Mar 11, 2022 at 7:16

user7864386

Collectives™ on Stack Overflow

Filter data based on multiple columns in second dataframe python

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related