CODEWITHSUNDEEP
pythonpandasmerge
emax
emax

Reputation: 7235

Python: how to find values in another dataframe?

I have two DataFrames, df1: 

df1.head(6)
             t day  node    s   e   i   r   sm  em  im
    491325  100 0   0       9   12  21  95  2   4   17
    491326  100 0   1       8   16  17  140 7   5   40
    491327  100 0   2       8   9   17  143 0   4   38
    491328  100 0   3      20   15  37  259 4   7   60
    491329  100 0   4       8   13  11  85  1   1   20
    491330  100 0   5       0   4   6   37  0   0   10

and df2:

df2.head(6)
   node original     lat        lon     r
0   0   50405      1.299033 103.83828   0
1   1   50404      1.299033 103.83828   0
2   2   50405      1.299033 103.83828   0
3   3   50402      1.299033 103.83828   0
4   4   6101       1.299033 103.83828   0
5   5   9821       1.332867 103.95767   0

I want to update df2.r based on df1.r and I am doing a loop:

for j in df1.index:
    tmp = df2[df2.node == df1.node[j]]
    df2.r[tmp.index[0] = df1.r[j]

Is there a better way to merge the two DataFrames?

Upvotes: 1

Views: 2305

Answers (2)

jezrael
jezrael

Reputation: 862611

I think you need merge, first select in df1 only columns r and node and then drop column r from df2:

print (pd.merge(df2.drop('r', axis=1), df1[['r','node']],  on='node'))
   node  original       lat        lon    r
0     0     50405  1.299033  103.83828   95
1     1     50404  1.299033  103.83828  140
2     2     50405  1.299033  103.83828  143
3     3     50402  1.299033  103.83828  259
4     4      6101  1.299033  103.83828   85
5     5      9821  1.332867  103.95767   37

Upvotes: 0

Andy Hayden
Andy Hayden

Reputation: 375475

You can use merge, but first you have to delete the dummy r column from df2:

In [11]: del df2["r"]

In [12]: df2.merge(df1[["r", "node"]])
Out[12]:
   node  original       lat        lon    r
0     0     50405  1.299033  103.83828   95
1     1     50404  1.299033  103.83828  140
2     2     50405  1.299033  103.83828  143
3     3     50402  1.299033  103.83828  259
4     4      6101  1.299033  103.83828   85
5     5      9821  1.332867  103.95767   37

Upvotes: 1

Related Questions