0

Have two dataframes

import pandas as pd

df1 = pd.DataFrame([['tom', 2, 11111]], columns=["name", "cell", "marks"])

df2 = pd.DataFrame([['tomm', 2, 11111, 2548],
                    ['matt', 2, 158416, 2483],
                    ['tonmmm', 2, 11111, 2549]
                    ], columns=["name", "cell", "marks", "passwd"])

Input

df1

    name    cell        marks
0   tom       2         11111

df2

    name    cell    marks   passwd
0   tomm    2      11111     2548
1   matt    2      158416    2483
2   tonmmm  2      11111     2549

map two dataframe which has similar columns

get columns from df2 which has match atleast a count of 2. here cell and marks matches with df1 with 2 values

expected output:

    name    cell    marks   passwd
0   tomm    2      11111     2548
1   tonmmm  2      11111     2549
Improve this question
4
  • How do you define similar ? Commented Jun 30, 2020 at 16:52
  • Are you looking for cases where marks are equal? Commented Jun 30, 2020 at 16:55
  • @Hugolmn get columns from df2 which has match atleast a count of 2. here cell and marks matches with df1 with 2 values Commented Jun 30, 2020 at 16:59
  • @NYCCoder get columns from df2 which has match atleast a count of 2. here cell and marks matches with df1 with 2 values Commented Jun 30, 2020 at 16:59

1 Answer 1

Reset to default
1

You could try this:

df1 = pd.DataFrame([['tom', 2, 11111]], columns=["name", "cell", "marks"])

df2 = pd.DataFrame([['tomm', 2, 11111, 2548],
                    ['matt', 2, 158416, 2483],
                    ['tonmmm', 2, 11111, 2549]
                    ], columns=["name", "cell", "marks", "passwd"])

temp=[len([i for i in list(row)[1:] if i in list(df1.iloc[0,:])])>=2 for row in df2[df2.columns[:len(df2.columns)-1]].to_records()]
newdf=df2[temp]
print(newdf)

Output:

     name  cell  marks  passwd
0    tomm     2  11111    2548
2  tonmmm     2  11111    2549

Edit: In the case you want to sort it base on the number of matches, you could try:

import pandas as pd
import numpy as np
df1 = pd.DataFrame([['tom', 2, 11111]], columns=["name", "cell", "marks"])  
df2 = pd.DataFrame([['tomm', 2, 11111, 2548],['matt', 2, 158416, 2483], ['tom', 2, 11111, 2549]], columns=["name", "cell", "marks", "passwd"])
temp=[len([i for i in list(row)[1:] if i in list(df1.iloc[0,:])]) for row in df2[df2.columns[:len(df2.columns)-1]].to_records()]
newdf=df2.copy().assign(val=temp).sort_values(by='val',ascending=False)
mask=np.where(newdf.val.ge(2), True, False)
newdf=newdf.drop(['val'],axis=1).reset_index(drop=True)[mask]
print(newdf)

Output:

   name  cell  marks  passwd
0   tom     2  11111    2549
1  tomm     2  11111    2548
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

i tried using this df1 = pd.DataFrame([['tom', 2, 11111]], columns=["name", "cell", "marks"]) df2 = pd.DataFrame([['tomm', 2, 11111, 2548], ['matt', 2, 158416, 2483], ['tom', 2, 11111, 2549] ], columns=["name", "cell", "marks", "passwd"]) there are three values matching in one case. it should be returned as first. how to modify it to get it in descending order of values
I just edited the answer for that case @somara4935. Hope it works for you! If you found it helpful, please accept it :)!