Your sample data is not complete enough to give you a specific solution, but this is how I would approach the task.
First of all, I wouldn't use a loop, I would read all the .xlsx files into a single data frame using something like this
library(tidyverse)
library(readxl)
list_of_files <- list.files(path = "patath\to\your\files",
pattern = ".xlsx$",
full.names = TRUE)
all_df <- list_of_files %>%
set_names() %>%
map_dfr(read_xlsx, .id = "ID")
Then, I would perform the filtering over all the data at once with something like this example
library(tidyverse)
library(lubridate)
library(fuzzyjoin)
df.summary <- data.frame(stringsAsFactors=FALSE,
ID = c("df1", "df2", "df3"),
Length = c(10, 4, 13),
Start = c("1/10/2001", "8/13/2001", "3/21/1982"),
End = c("1/9/2011", "8/12/2005", "8/5/1995")
)
all_df <- data.frame(
ID = c("df3", "df3", "df3", "df2", "df2", "df1", "df1"),
Year = c(1985, 1985, 1985, 1985, 1985, 2009, 2009),
Month = c(1, 1, 1, 1, 1, 12, 12),
Day = c(1, 2, 5, 6, 7, 30, 31),
Value_total_day = c(10, 12, 11.8, NA, 21, 22, 20)
)
df.summary %>%
mutate_at(vars(Start, End), mdy) %>%
mutate(Start = ceiling_date(Start, unit = "years"),
End = floor_date(End, unit = "years") - 1) %>%
fuzzy_left_join(all_df %>% mutate(date = make_date(year = Year, month = Month, day = Day)),
by = c("Start" = "date", "End" = "date", "ID" = "ID"),
match_fun = c(`<=`, `>=`, `==`)) %>%
select(ID = ID.x, Year, Month, Day, Value_total_day)
#> ID Year Month Day Value_total_day
#> 1 df1 2009 12 30 22.0
#> 2 df1 2009 12 31 20.0
#> 3 df2 NA NA NA NA
#> 4 df3 1985 1 1 10.0
#> 5 df3 1985 1 2 12.0
#> 6 df3 1985 1 5 11.8