CODEWITHSUNDEEP
r
firmo23
firmo23

Reputation: 8404

Match two data frames by common lat, long pair and create a new column out of it

I have two dataframes with coordinates:

lat<-c(36.09163,36.16152,36.13027
,36.24514)
lon<-c(-95.84311,-95.83745
,-96.10115,-95.84191)
weight<-c(87,45,656,78)
df1<-data.frame(lat,lon,weight)



lat<-c(36.09163,36.16152,36.130279,36.24514)
lon<-c(-95.84311,-95.83745
       ,-96.101159,-95.841919)
df2<-data.frame(lat,lon)

What I want to achieve is find all the common lat long pairs when both of them have at least 5 common decimal digits (and of course integer) with a lat-long pair of the other dataframe. When this happens then the second dataframe should take the relative weight value of the 1st dataftame in a new column named weight. In the toy example I give all the pairs should be considered the same. If not then the new weight value shoul be 0. I need something like:

n<-merge(df1,df2,all.x=TRUE)

but I do not know how to handle the digit issue since some coordinates have 5 and other have 6 digits.

Upvotes: 1

Views: 379

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

The sqldf package might be helpful here, since your problem can be neatly formulated using a SQL join between the two data frames:

library(sqldf)

sql <- "SELECT df2.lat, df2.lon, COALESCE(df1.weight, 0) weight
        FROM df2
        LEFT JOIN df1
            ON ROUND(df1.lat - 0.5, 5) = ROUND(df2.lat - 0.5, 5) AND
               ROUND(df1.lon - 0.5, 5) = ROUND(df2.lon - 0.5, 5)"

df2 <- sqldf(sql)

Note that it appears that you want to truncate each lat/lng value and then compare it. That is, the following two latitude values from the two data frames should be considered equivalent:

36.130279
36.13027

We can do this by truncating to 5 digits and then comparing. Since SQLite does not have a floor function, we may simulate by subtracting 0.5 and then rounding to 5 digits.

Also, if a given lat/lng pair could match to more than one pair in the other data frame then your current question is not well defined, and you would need to provide other logic to handle this.

Upvotes: 2

Related Questions