calculating number of days between 2 columns of dates in data frame
Without your seeing your data (you can use the output of dput(head(survey))
to show us) this is a shot in the dark:
survey <- data.frame(date=c("2012/07/26","2012/07/25"),tx_start=c("2012/01/01","2012/01/01"))
survey$date_diff <- as.Date(as.character(survey$date), format="%Y/%m/%d")-
as.Date(as.character(survey$tx_start), format="%Y/%m/%d")
survey
date tx_start date_diff
1 2012/07/26 2012/01/01 207 days
2 2012/07/25 2012/01/01 206 days
count the number of days between two dates per year
Here's one approach using tidyverse
and lubridate
.
First, separate the rows by calendar year, to use to measure the number of days for each year. Each row will include dates to be counted in each calendar year, starting with January 1st and ending with December 31st if overlapping multiple years. Then, it is easy to calculate the number of days in a given year.
The results from this example are slightly different than what I have. Year 2016 is a leap year and has 366 days. If the number of days are not inclusive of either start or end dates, you would get a different answer.
library(tidyverse)
library(lubridate)
df %>%
mutate(date_int = interval(start, end),
year = map2(year(start), year(end), seq)) %>%
unnest(year) %>%
mutate(year_int = interval(as.Date(paste0(year, '-01-01')), as.Date(paste0(year, '-12-31'))),
year_sect = intersect(date_int, year_int),
start_new = as.Date(int_start(year_sect)),
end_new = as.Date(int_end(year_sect))) %>%
select(id, start_new, end_new) %>%
mutate(year = year(start_new),
days = as.numeric(end_new - start_new)) %>%
right_join(df) %>%
pivot_wider(id_cols = c(id, start, end), names_from = year, values_from = days, names_prefix = "year_", values_fill = list(days = 0)) %>%
mutate(days_number = reduce(select(., starts_with("year_")), `+`))
Output
id start end year_2015 year_2016 days_number
<dbl> <date> <date> <dbl> <dbl> <dbl>
1 1 2015-01-01 2016-12-31 364 365 729
2 2 2016-01-01 2016-12-31 0 365 365
3 3 2015-07-01 2016-12-31 183 365 548
How to calculate average number of days between series of dates?
You can take rowwise differences of 'Date' columns and take the average.
library(dplyr)
df %>%
mutate(across(starts_with('Date'), as.Date, '%m/%d/%y')) %>%
rowwise() %>%
mutate(diff = as.numeric(mean(diff(c_across(starts_with('Date'))), na.rm = TRUE)))
# Variable Date1 Date2 Date3 Date4 diff
# <chr> <date> <date> <date> <date> <dbl>
#1 VarA 2021-09-01 2021-09-02 2021-09-03 2021-09-04 1
#2 VarB 2021-08-01 2021-08-17 2021-09-02 NA 16
#3 VarC 2021-09-25 NA NA NA NaN
data
It is easier to help if you provide data in a reproducible format
df <- structure(list(Variable = c("VarA", "VarB", "VarC"), Date1 = c("09/01/21",
"08/01/21", "09/25/21"), Date2 = c("09/02/21", "08/17/21", ""
), Date3 = c("09/03/21", "09/02/21", ""), Date4 = c("09/04/21",
"", "")), class = "data.frame", row.names = c(NA, -3L))
Calculate the number of weekdays between 2 dates in R
Date1 <- as.Date("2011-01-30")
Date2 <- as.Date("2011-02-04")
sum(!weekdays(seq(Date1, Date2, "days")) %in% c("Saturday", "Sunday"))
EDIT: And Zach said, let there be Vectorize
:)
Dates1 <- as.Date("2011-01-30") + rep(0, 10)
Dates2 <- as.Date("2011-02-04") + seq(0, 9)
Nweekdays <- Vectorize(function(a, b)
sum(!weekdays(seq(a, b, "days")) %in% c("Saturday", "Sunday")))
Nweekdays(Dates1, Dates2)
How to calculate number of days between two dates in one column in R
y%>%mutate(Between=as.numeric(lead(Date2,default = last(Date2))-Date2))
Date2 concentration Between
1 2013-01-01 1.5 7
2 2013-01-08 2.5 4
3 2013-01-12 1.5 5
4 2013-01-17 3.5 0
y%>%mutate(Between=as.numeric(c(diff(Date2),0)))
Date2 concentration Between
1 2013-01-01 1.5 7
2 2013-01-08 2.5 4
3 2013-01-12 1.5 5
4 2013-01-17 3.5 0
in base R:
transform(y,Between=as.numeric(c(diff(Date2),0)))
How do I calculate the difference between two dates in dplyr (in days)? R
this is a possible solution;
date1 <- as.Date("2021-09-12")
date2 <- as.Date("2021-09-13")
datediff <- date2 - date1
If you just want the difference as a number
as.numeric(datediff)
with dplyr
library(dplyr)
df <- data.frame(date1 = c("2021-09-12","2021-09-13"),
date2 = c("2021-09-13","2021-10-14"))
df$date1 <- df$date1 %>%
as.Date()
df$date2 <- df$date2 %>%
as.Date()
df$datediff <- as.numeric(df$date2 - df$date1)
Calculate number of days between two dates in r
#Using dplyr package
library(dplyr)
df1 %>% # your dataframe
mutate(BETWEEN0=as.numeric(difftime(DATE,lag(DATE,1))),BETWEEN=ifelse(is.na(BETWEEN0),0,BETWEEN0),FIRST=cumsum(as.numeric(BETWEEN)))%>%
select(-BETWEEN0)
DATE BETWEEN FIRST
1 2013-07-08 0 0
2 2013-08-01 24 24
3 2013-08-30 29 53
4 2013-10-23 54 107
5 2013-12-16 54 161
6 2015-12-16 730 891
Related Topics
Making Plot Functions with Ggplot and Aes_String
Partially Color Histogram in R
Different Robust Standard Errors of Logit Regression in Stata and R
R - Faster Way to Calculate Rolling Statistics Over a Variable Interval
How to Manually Change the Key Labels in a Legend in Ggplot2
Asymmetric Expansion of Ggplot Axis Limits
Warning in Install.Packages: Unable to Move Temporary Installation
Convert Accented Characters into Ascii Character
Devtools::Install_Github() - Ignore Ssl Cert Verification Failure
R: Updating a Data Frame with Another Data Frame
How to Stack Only Some Columns in a Data Frame
Select Rows of a Data.Frame That Contain Only Numbers in a Certain Column