0

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] }
    }
    }
    }**
Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
asnead94
  • 5
  • 3
  • 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

1 Answers1

1

Suggested solution using dplyr:

library(dplyr)

x %>% 
  left_join(y, by=c("Long"="Longitude", "Lat"="Latitude")) %>% 
  mutate(Sink = coalesce(Site, Sink))
dario
  • 6,415
  • 2
  • 12
  • 26
  • 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 <- followed by the code. Do I need to pipe it into a data frame? It is also creating an addtional column (Site) which I do not need. – asnead94 Mar 04 '20 at 19:08
  • wen can assign the expression to a variable e.g. `new_x <- 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<- as.dataframe( x%>% ....)? – asnead94 Mar 04 '20 at 19:30
  • `new_x <- 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 <- function(X,Y) { X_New <- X %>% left_join(Y, by=c("Long"="Longitude", "Lat"="Latitude")) %>% mutate(Sink = coalesce(Site, Sink)) X_New$Site <- NULL } – 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 <- NULL `?? – dario Mar 05 '20 at 06:35
  • X_New$Site <- Null is because I do not actually need the site column as site and sink are the same. The assignment works just fine until I try to do this within a function. When I run the function, X_New is not created in my environment. – asnead94 Mar 05 '20 at 16:30