Overwrite values in a dataframe with values from other dataframe in pandas python

Question

I have a two dataframes. One with a coordinates of sites, the other with with connections between them. For example:

df1:

SITE_ID     LAT       LON
101       23.4244   31.5356
102       45.0090   14.2424
103       35.3444   19.4242
104       42.0000   18.0002

df2

SITE_ID  FIRST  SECOND  THIRD
101       102    104     NaN 
102       103    NaN     NaN
103       104    NaN     NaN
104       NaN    NaN     NaN

Now I wanna to change all value(in all columns) in df2 with coordinates from df1. Desired output:

 SITE_ID              FIRST               SECOND       THIRD
23.4244 31.5356    45.0090 14.2424   42.0000 18.0002   NaN 
45.0090 14.2424    35.3444 19.4242          NaN        NaN
35.3444 19.4242    42.0000 18.0002          NaN        NaN
42.0000 18.0002        NaN                  NaN        NaN

It's ok to have a little variation on this white comma or putting in lists. Later I'm gonna transform this to json and use it with javascript google maps api.. To be honest, I don't have so much idea, I've tried with transforming into dict, or overwrite while merging, but nothing really works till the end.

Zero · Accepted Answer · 2017-07-14 13:39:23Z

3

Here's one way, create mapping from df1 and replace values in df2

In [1120]: mapping = df1.set_index('SITE_ID').astype(str).apply(' '.join, axis=1).to_dict()

In [1121]: mapping
Out[1121]:
{101: '23.4244 31.5356',
 102: '45.009 14.2424',
 103: '35.3444 19.4242',
 104: '42.0 18.0002'}

In [1122]: df2.replace(mapping)
Out[1122]:
           SITE_ID            FIRST        SECOND  THIRD
0  23.4244 31.5356   45.009 14.2424  42.0 18.0002    NaN
1   45.009 14.2424  35.3444 19.4242           NaN    NaN
2  35.3444 19.4242     42.0 18.0002           NaN    NaN
3     42.0 18.0002              NaN           NaN    NaN

answered Jul 14, 2017 at 13:39

Zero

77.4k22 gold badges154 silver badges154 bronze badges

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

Collectives™ on Stack Overflow

Overwrite values in a dataframe with values from other dataframe in pandas python

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