Pandas compare one column from df1 with multiple columns in df2

Question

I have 2 df.

df1 has records of actions performed by users, and df2 has actions owned by users. compare particular action field with its respective owner field and flag with boolean value. Can this be done without using loops for each filed and action for user. Please help!!

df1 =

 owner    field           value
0   Rick      cin          234011
1  Daryl    email  [email protected]
2  Negan    phone         9087654
3  Carol  address         Hilltop

df2 =

   owner      cin             email    phone     address
0   Rick   543216  [email protected]  9654356  Alexandria
1  Daryl   675432    [email protected]  9876590     kingdom
2  Negan   234123    [email protected]  9087654  Ocean side
3  Carol  0987809   [email protected]  9076535     Atlanta

Expected Result =

owner      field    value           Flag
0   Rick    cin     234011          False
1   Daryl   email   [email protected]  True
2   Negan   phone   9087654         True
3   Carol   address Hilltop         False

Since Daryl and Negan owns those entities, we flagged them true. I have tried using merge and getting the identical entities but it needs to be looped across all fields and values.

Any help is appreciated Thanks !

sammywemmy · Accepted Answer · 2020-10-07 04:53:12Z

3

Melt df1 :

    df1_melt = (
    df1.melt(["owner", "field"])
    .drop("variable", 1)
    .rename(columns={"field": "variable"})
)
df1_melt

owner   variable    value
0   Rick    cin 234011
1   Daryl   email   [email protected]
2   Negan   phone   9087654
3   Carol   address Hilltop

Melt df2, merge with df1_melt, and finally rename and change values in Flag to True/False :

    (
    df2.melt("owner")
    .astype(str)
    .merge(df1_melt, how="right", indicator=True)
    .rename(columns={"_merge": "Flag"})
    .assign(Flag=lambda x: np.where(x.Flag == "both", True, False))
)



   owner    variable    value           Flag
0   Rick    cin       234011            False
1   Daryl   email     [email protected]    True
2   Negan   phone     9087654           True
3   Carol   address   Hilltop           False

answered Oct 7, 2020 at 4:53

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Pandas compare one column from df1 with multiple columns in df2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related