Home > database >  join a data frame to a list of data frames in R
join a data frame to a list of data frames in R

Time:08-17

I have a list of participant data with heart rate (HR) and location that I need to join with weather data by the nearest location within a specific Code. Here is an example:

library(sf)
library(purrr)

#Participant1
HR<- c(60,62,61,60,60,61)
Code<- c("0_0", "0_1", "1_1", "0_2", "2_2", "0_0")
Lat <- c("1.295824", "1.295824", "1.295826", "1.295828", "1.295830", "1.295830")
Lon <- c("103.8494", "103.8494", "103.8494", "103.8494", "103.8494", "103.8494")
P1 <- data.frame(HR, Code, Lat, Lon)

#Participant2
HR<- c(71,70,69,71,72, 70)
Code<- c("0_0", "0_1", "1_1", "1_1", "0_2", "2_2")
Lat <- c("1.295995", "1.295977", "1.295995", "1.295992", "1.295987", "1.295992")
Lon <- c("103.8492", "103.8492", "103.8492", "103.8492", "103.8492", "103.8492")
P2 <- data.frame(HR, Code, Lat, Lon)

#Participant3
HR<- c(68,67,65,66,68, 68)
Code<- c("0_0", "0_1", "1_1", "0_2", "2_2", "2_2")
P3 <- data.frame(HR, Code, Lat, Lon)
Lat <- c("1.295773", "1.295770", "1.295769", "1.295769", "1.295772",  "1.295769")
Lon <- c("103.8493", "103.8493", "103.8493", "103.8493", "103.8493", "103.8493")

#Creating a list of df from participant data
ListP <- list(P1, P2, P3)
ListP <- lapply(ListP, function(x)  sf::st_as_sf(x, coords=c("Lat","Lon"))) # creating geometry from latitude and longitude

#Creating Weather df
Tair <- c(30, 31, 32, 30, 21)
Code<- c("0_0", "0_1", "1_1", "0_2", "2_2")
Lat <- c("1.296033", "1.296028", "1.296020","1.296013", "1.296008")
Lon <- c("103.8493", "103.8493", "103.8493", "103.8493", "103.8493")
Weather <- data.frame(Tair, Code, Lat, Lon)
Weather <- sf::st_as_sf(Weather, coords = c("Lat","Lon"))# creating geometry from latitude and longitude

Now I need to join Weather file to each of the participants in ListP by the nearest location point within the specific Code. For instance, Weather points with Code "0_0" must only be joined with participant's points with the same Code "0_0"

I tried splitting both ListP and Weather by the Code, so I would end up with a nested list for ListP and a list for Weather like this:

#splitting into nested data frames by Code
ListP <- lapply(ListP, function(x) split(x, f=x$Code)) 
Weather <- split(x=Weather, f=Weather$Code)

Now I'm trying to join the two using st_join, however, the participant df overwrites itself so I only end up with the last participant data joined. I feel that I need to add some function that would save the result into a new list of data frames by individual participant but not sure how to get there.

for(participant in ListP) 
  {
  merge <-purrr::map2(participant, Weather,
                          st_join,
                          join = st_nearest_feature) 
  }

I'll appreciate your help very much!

CodePudding user response:

library(sf)
library(purrr)

#Participant1
HR<- c(60,62,61,60,60,61)
Code<- c("0_0", "0_1", "1_1", "0_2", "2_2", "0_0")
Lat <- c("1.295824", "1.295824", "1.295826", "1.295828", "1.295830", "1.295830")
Lon <- c("103.8494", "103.8494", "103.8494", "103.8494", "103.8494", "103.8494")
P1 <- data.frame(HR, Code, Lat, Lon)

#Participant2
HR<- c(71,70,69,71,72, 70)
Code<- c("0_0", "0_1", "1_1", "1_1", "0_2", "2_2")
Lat <- c("1.295995", "1.295977", "1.295995", "1.295992", "1.295987", "1.295992")
Lon <- c("103.8492", "103.8492", "103.8492", "103.8492", "103.8492", "103.8492")
P2 <- data.frame(HR, Code, Lat, Lon)

#Participant3
HR<- c(68,67,65,66,68, 68)
Code<- c("0_0", "0_1", "1_1", "0_2", "2_2", "2_2")
P3 <- data.frame(HR, Code, Lat, Lon)
Lat <- c("1.295773", "1.295770", "1.295769", "1.295769", "1.295772",  "1.295769")
Lon <- c("103.8493", "103.8493", "103.8493", "103.8493", "103.8493", "103.8493")

#Creating a list of df from participant data
ListP <- list(P1, P2, P3)
ListP <- lapply(ListP, function(x)  sf::st_as_sf(x, coords=c("Lat","Lon"))) # creating geometry from latitude and longitude

#Creating Weather df
Tair <- c(30, 31, 32, 30, 21)
Code<- c("0_0", "0_1", "1_1", "0_2", "2_2")
Lat <- c("1.296033", "1.296028", "1.296020","1.296013", "1.296008")
Lon <- c("103.8493", "103.8493", "103.8493", "103.8493", "103.8493")
Weather <- data.frame(Tair, Code, Lat, Lon)
Weather <- sf::st_as_sf(Weather, coords = c("Lat","Lon"))# creating geometry from latitude and longitude

for(i in 1:length(ListP)) {

ListP[[i]] <- merge(ListP[[i]], Weather, by="Code", x.all = T)

}

CodePudding user response:

I think I solved it using the tips you've provided:

 for(i in 1:length(ListP)) 
   
  {ListP[[i]] <-purrr::map2(ListP[[i]], Weather,
                          st_join,
                          join = st_nearest_feature)}

  • Related