CODEWITHSUNDEEP
pythonpandas
Guillermo Izquierdo
Guillermo Izquierdo

Reputation: 121

How to match values in a DataFrame with some conditions?

I have two dataframes and I haven't been able to accomplish this simple task, I want to match 2 columns and if that condition is True then append the value in the respective row. Something like this.

      0        1        weight
0     Pablo    Frida       2
1     Pablo     Andy       1
2     Pablo  Vincent       1
3    Claude  Georgia       3
4    Claude     Andy       1
5   Georgia     Andy       1
6      Andy    Frida       1
7      Andy     Joan       1
8      Andy      Lee       1
9   Vincent    Frida       2
10     Joan      Lee       3 

         0       1      2    weight
0      Andy   Claude   0     0.0
1      Andy    Frida  20     0.0
2      Andy  Georgia -10     0.0
3      Andy     Joan  30     0.0
4      Andy      Lee -10     0.0
..
..
17    Frida  Vincent  60     0.0
18  Georgia     Joan   0     0.0

And the expected result would be something like this:

        0       1      2    weight
0      Andy   Claude   0     1
1      Andy    Frida  20     1
2      Andy  Georgia -10     1
3      Andy     Joan  30     1
4      Andy      Lee -10     1
..
..
17    Frida  Vincent  60     2
18  Georgia     Joan   0     0.0

So the problem I am facing is that the columns could be in different order i.e. it is a set of names. The first DataFrame has a dimension of (11,3) The second DataFrame has a dimension of (19,4)

How can I deal with this problem?? I am trying something like this:

for i in len(df2):
    if df1[0][i] == df2[0][i] or df1[0][i] == df2[1][i] and df1[1][i] == df2[0][i]  or df1[0][i] == df2[0][i]:
        df2['weight'][i] = df1['weight'][i]

Upvotes: 1

Views: 60

Answers (1)

cs95
cs95

Reputation: 402493

Sort the columns - 

df1.iloc[:, :2] = np.sort(df1.iloc[:, :2], axis=1)
df2.iloc[:, :2] = np.sort(df2.iloc[:, :2], axis=1)

Now, perform a inner merge - 

df2 = df2.drop('weight', 1).merge(df1, on=['0', '1'])
df2.head()

      0        1   2  weight
0  Andy   Claude   0       1
1  Andy    Frida  20       1
2  Andy  Georgia -10       1
3  Andy     Joan  30       1
4  Andy      Lee -10       1

Upvotes: 1

Related Questions