Background:

1st Table:

2nd Table:

What I want: I want to write a nested for loop where R is able to find the smallest date value in table two and attach it to the variable "New" in table 1.

My current code looks like this:

for(i in 1:nrow(merged_final)){
  for (j in 1:nrow(merged_final)){
    if(is.na(merged_final$New[j]))
      merged_final[merged_final$New[i] == min(query1.1$Coach340BClaimsID == query1.1$Coach340BClaimsID[j])]
  }
}

This does not seem to be working. Any tips would be appreciated.

CodePudding user response：

You have two common operations: (a) find the smallest value by group, and (b) join two data sets. You don't need a loop, much less a nested loop.

With dplyr:

library(dplyr)
# find min vals by group
min_val = data2 %>%
  group_by(ID) %>%
  slice(which.min(date)) %>%
  rename(New2 = date)   ## rename the column to be ready to merge

# join to original data
data1 = data1 %>%
  left_join(min_val, by = "ID") %>%
  mutate(New = coalesce(New, New2))