Creating a function that compares each row of dataframeX against each row of dataframeY. Then add a value from data frame Y to X

Question

I have two data frames (x and Y). I need to compare each row in X to every row in Y and if two logicals are met take the value from a column in Y and place it in the column of X (for that specific row). Dataframe X looks like:

Dataframes

The goal is to test each row of X against each row of Y and if X$Long == Y$Longitude & X$Lat==YLattidude for a row to assign the value of Y$Site at the row which meets the condition to X$sink for the specific row being tested.

I would like this to be in a single function with input x and y. I have tried some codes but it is not filling in the values.

This is a sample of the things I have been doing.

 **Connectivity_Matrix_function<- function(X,Y){
     X$Sink <- c("NA")
    for (z in 1:nrow(X)) {
    for (i in 1:nrow(Y)) {
    if(X[z, 4] == Y[i,2] & X[z,5] == Y[i,3]) { X[z, 11] <- Y[i, 1] }
    }
    }
    }**

I think all you need to do is to merge both data.frames `merge(X, Y, by.x = c("Long", "Lat"), by.y = c("Longitude", "Latitude"), all.x = TRUE)`. — Gilean0709, Mar 04 '20 at 15:53

dario · Accepted Answer · 2020-03-04T16:08:37.107

1

Suggested solution using dplyr:

library(dplyr)

x %>% 
  left_join(y, by=c("Long"="Longitude", "Lat"="Latitude")) %>% 
  mutate(Sink = coalesce(Site, Sink))

edited Mar 04 '20 at 16:08

answered Mar 04 '20 at 15:58

dario

6,162
2
10
25

Hm. what types are your variables? Can you add a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) in an edit to your question? – dario Mar 04 '20 at 18:59
I fixed it! I just had to change column types. How would I then save the result in additional new data frame? I have limited experience in the Tidyverse. I already tried Z – asnead94 Mar 04 '20 at 19:08
wen can assign the expression to a variable e.g. `new_x % select(Long)` or. although probably not something we *should* use: `x %>% select(Long) -> newer_x` – dario Mar 04 '20 at 19:13
I would like to create an entirely new data frame utilizing the output. Would I do something like X_new% ....)? – asnead94 Mar 04 '20 at 19:30
`new_x % select(Long)` will create a new object. – dario Mar 04 '20 at 19:34
This works outside of by function bracket, but does not work inside the function. I tried Fun % left_join(Y, by=c("Long"="Longitude", "Lat"="Latitude")) %>% mutate(Sink = coalesce(Site, Sink)) X_New$Site – asnead94 Mar 05 '20 at 00:44
Not sure what you mean, everything works when I try it... Can you add a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) in an edit to your question so that I can reproduce the problem you are having. Also not sure why you'd add the line ` X_New$Site – dario Mar 05 '20 at 06:35
X_New$Site – asnead94 Mar 05 '20 at 16:30

Creating a function that compares each row of dataframeX against each row of dataframeY. Then add a value from data frame Y to X

1 Answers1