在df1中创建一个变量，具体取决于df1的一个变量(df1 $ var1)和一个df2的变量，该变量可以根据df1 $ var1进行更改. [英] Create a variable in `df1` depending on one variable of `df1` (`df1$var1`) and one variable of `df2` that is changeable depending on `df1$var1`
问题描述
我有数据框df1，该数据框总结了随时间变化的鱼类深度. df1$Site告诉您鱼的位置，df1$Ind告诉您个体，df1$Depth告诉您特定df1$Datetime处鱼的深度.
I have data frame df1 that summarises fish depths over time. df1$Site tells you the site where the fish was, df1$Ind tells you the individual and df1$Depth tells you the depth where the fish was at a specific df1$Datetime.
另一方面，我有df2总结了从表面到39米深度的时间间隔(每三小时)的电流强度，间隔为8米(m0-7，m8-15，m24-31和m32-39).例如:
On the other hand, I have df2 that summarises the intensity of the currents over time (EVERY THREE HOURS) from the surface to 39 meters depth at intervals of 8 meters (m0-7, m8-15, m16-23, m24-31 and m32-39). As an example:
df1<-data.frame(Datetime=c("2016-08-01 15:34:07","2016-08-01 16:25:16","2016-08-01 17:29:16","2016-08-01 18:33:16","2016-08-01 20:54:16","2016-08-01 22:48:16"),Site=c("BD","HG","BD","BD","BD","BD"),Ind=c(16,17,19,16,17,16), Depth=c(5.3,24,36.4,42,NA,22.1))
df1$Datetime<-as.POSIXct(df1$Datetime, format="%Y-%m-%d %H:%M:%S",tz="UTC")
> df1
Datetime Site Ind Depth
1 2016-08-01 15:34:07 BD 16 5.3
2 2016-08-01 16:25:16 HG 17 24.0
3 2016-08-01 17:29:16 BD 19 36.4
4 2016-08-01 18:33:16 BD 16 42.0
5 2016-08-01 20:54:16 BD 17 NA
6 2016-08-01 22:48:16 BD 16 22.1
df2<-data.frame(Datetime=c("2016-08-01 12:00:00","2016-08-01 15:00:00","2016-08-01 18:00:00","2016-08-01 21:00:00","2016-08-02 00:00:00"), Site=c("BD","BD","BD","BD","BD"),var1=c(2.75,4,6.75,2.25,4.3),var2=c(3,4,4.75,3,2.1),var3=c(2.75,4,5.75,2.25,1.4),var4=c(3.25,3,6.5,2.75,3.4),var5=c(3,4,4.75,3,1.7))
df2$Datetime<-as.POSIXct(df2$Datetime, format="%Y-%m-%d %H:%M:%S",tz="UTC")
colnames(df2)<-c("Datetime","Site","m0-7","m8-15","m16-23","m24-31","m32-39")
> df2
Datetime Site m0-7 m8-15 m16-23 m24-31 m32-39
1 2016-08-01 12:00:00 BD 2.75 3.00 2.75 3.25 3.00
2 2016-08-01 15:00:00 BD 4.00 4.00 4.00 3.00 4.00
3 2016-08-01 18:00:00 BD 6.75 4.75 5.75 6.50 4.75
4 2016-08-01 21:00:00 BD 2.25 3.00 2.25 2.75 3.00
5 2016-08-02 00:00:00 BD 4.30 2.10 1.40 3.40 1.70
我想在df1中创建一个名为df1$Current.Int的新列，以根据df2关于洋流的说法总结鱼在何时何地的当前强度.
I want to create a new column in df1 called df1$Current.Int that summarises the current intensity at the depth when and where the fish was according to what df2 says about currents.
我想得到这个:
> df1
Datetime Site Ind Depth Current.Int
1 2016-08-01 15:34:07 BD 16 5.3 4.00
2 2016-08-01 16:25:16 HG 17 24.0 NA # Currents of this site are not included in df2
3 2016-08-01 17:29:16 BD 19 36.4 4.75
4 2016-08-01 18:33:16 BD 16 42.0 4.75
5 2016-08-01 20:54:16 BD 17 NA NA
6 2016-08-01 22:48:16 BD 16 22.1 1.40
仅需指出，由于当前记录是每三个小时一次，因此df2$Datetime中指示的每小时代表一个多半小时和一个不到半小时.也就是说，在df2中在21:00:00处指出的电流强度反映了19:30:00和22:30:00之间的电流.其余时间也一样.
Just to point out that since the current records are every three hours, every hour indicated in df2$Datetime represents one hour and a half more, and one hour and a half less. That is, the current intensity pointed out in df2 at 21:00:00 reflects the currents between 19:30:00 and 22:30:00. The same with the rest of the hours.
有人知道怎么做吗?
推荐答案
日期不匹配，因此示例中的日期已更改.使用这种方法，您可以准确检查匹配的工作方式并确保匹配符合您的要求.
The dates didn't match so they are changed for the example. With this approach, you can check exactly how the match has worked and ensure it is as you want it.
df1<-data.frame(Datetime=c("2016-08-18 15:34:07","2016-08-18 16:25:16","2016-08-18 17:29:16","2016-08-18 18:33:16","2016-08-18 20:54:16","2016-08-18 22:48:16"),Site=c("BD","HG","BD","BD","BD","BD"),Ind=c(16,17,19,16,17,16), Depth=c(5.3,24,36.4,42,NA,22.1))
df1$Datetime<-as.POSIXct(df1$Datetime, format="%Y-%m-%d %H:%M:%S",tz="UTC")
df2<-data.frame(Datetime=c("2016-08-18 12:00:00","2016-08-18 15:00:00","2016-08-18 18:00:00","2016-08-18 21:00:00","2016-08-19 00:00:00"), Site=c("BD","BD","BD","BD","BD"),var1=c(2.75,4,6.75,2.25,4.3),var2=c(3,4,4.75,3,2.1),var3=c(2.75,4,5.75,2.25,1.4),var4=c(3.25,3,6.5,2.75,3.4),var5=c(3,4,4.75,3,1.7))
df2$Datetime<-as.POSIXct(df2$Datetime, format="%Y-%m-%d %H:%M:%S",tz="UTC")
colnames(df2)<-c("Datetime","Site","m0-7","m8-15","m16-23","m24-31","m32-39")
library(dplyr)
library(lubridate)
# Round the date and convert the depth to match the look-up.
df1 = df1 %>%
mutate(
Datetime_rounded = round_date(Datetime, "3 hour"),
Depth_ind = ifelse(Depth < 8, "m0-7",
ifelse(Depth > 7 & Depth < 16, "m8-15",
ifelse(Depth > 15 & Depth < 24, "m16-23",
ifelse(Depth > 23 & Depth < 32, "m24-31",
ifelse(Depth > 31 & Depth < 40, "m32-39", NA)
)
)
)
)
)
# Wide to long on the intensity columns.
df2 = df2 %>%
tidyr::gather("Depth_ind", "Intensity", 3:7)
# Join
df1 %>%
left_join(df2, by = c("Datetime_rounded" = "Datetime",
"Site",
"Depth_ind"))
Datetime Site Ind Depth Datetime_rounded Depth_ind Intensity
1 2016-08-18 15:34:07 BD 16 5.3 2016-08-18 15:00:00 m0-7 4.00
2 2016-08-18 16:25:16 HG 17 24.0 2016-08-18 15:00:00 m24-31 NA
3 2016-08-18 17:29:16 BD 19 36.4 2016-08-18 18:00:00 m32-39 4.75
4 2016-08-18 18:33:16 BD 16 42.0 2016-08-18 18:00:00 <NA> NA
5 2016-08-18 20:54:16 BD 17 NA 2016-08-18 21:00:00 <NA> NA
6 2016-08-18 22:48:16 BD 16 22.1 2016-08-19 00:00:00 m16-23 1.40
# EDIT ----
## As per the request, the width of the final depth range can be adjusted as you wish, e.g. to a max depth of 60 m.
# Round the date and convert the depth to match the look-up.
df1 = df1 %>%
mutate(
Datetime_rounded = round_date(Datetime, "3 hour"),
Depth_ind = ifelse(Depth < 8, "m0-7",
ifelse(Depth > 7 & Depth < 16, "m8-15",
ifelse(Depth > 15 & Depth < 24, "m16-23",
ifelse(Depth > 23 & Depth < 32, "m24-31",
ifelse(Depth > 31 & Depth < 60, "m32-39", NA)
)
)
)
)
)
这篇关于在df1中创建一个变量，具体取决于df1的一个变量(df1 $ var1)和一个df2的变量，该变量可以根据df1 $ var1进行更改.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
