calculate difference of value for consecutive days for all objects in sql
By : user2795021
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I have a table with an object, index, date and value: code :
SELECT t2.object,t2.index,t2.date,t2.valuet1.value
FROM table t1, table t2
WHERE t1.object=t2.object AND t1.index=t2.index
AND t2.date=DATE_ADD(t1.date, INTERVAL 1 DAY);

Consecutive Days by Group  Error in Mutate Function Dplyr
By : ginerjm
Date : March 29 2020, 07:55 AM
this one helps. The issue is that for some groups, the row for yesterday is not found. This can be fixed by defining a function that checks for that instead of inlining the function in mutate: code :
library(dplyr)
compute.consec.days < function(date, callout, yesterday, rown) {
j < which(date == yesterday)
if (length(j)==0) NA else cumsum(rev(cumprod(rev((yesterdaydate)==(jrown) & callout==callout[date == yesterday]))))
}
yesterday < as.Date("20161030")
out < employ.data %>% group_by(Child) %>%
mutate(`Consec. Days with Callout`=compute.consec.days(DATE,Callout,yesterday,row_number())) %>%
filter(DATE == yesterday)
##Source: local data frame [2 x 7]
##Groups: Child [2]
##
## DATE Parent Child avg_child_salary salary Callout Consec. Days with Callout
## <date> <fctr> <fctr> <dbl> <dbl> <fctr> <dbl>
##1 20161030 A ac 526.27 0.00 LOW 1
##2 20161030 G gb 18089.83 6973.54 LOW 1
compute.consec.days < function(date, callout, yesterday, rown) {
j < which(date == yesterday)
if (length(j)==0) NA else {
## first compute the condition
cond < (yesterdaydate)==(jrown) & callout==callout[date == yesterday]
## then evaluate consecutive days only with this vector up to
## the row corresponding to yesterday. Then add the result with NAs
## because mutate is a windowing function
c(cumsum(rev(cumprod(rev(cond[1:j[1]])))),rep(NA,length(date)j[1]))
}
}
yesterday < as.Date("20161020")
out < employ.data %>% group_by(Child) %>%
mutate(`Consec. Days with Callout`=compute.consec.days(DATE,Callout,yesterday,row_number())) %>%
filter(DATE == yesterday)
##Source: local data frame [4 x 7]
##Groups: Child [4]
##
## DATE Parent Child avg_child_salary salary Callout Consec. Days with Callout
## <date> <fctr> <fctr> <dbl> <dbl> <fctr> <dbl>
##1 20161020 A ae 1197.25 2238.56 HIGH 1
##2 20161020 B ba 21.68 43.22 HIGH 1
##3 20161020 G ga 582.66 1947.35 HIGH 2
##4 20161020 G gb 18089.83 31158.51 HIGH 2
yesterday < as.Date("20161030")
out < employ.data %>% group_by(Child) %>%
mutate(`Consec. Days with Callout`=compute.consec.days(DATE,Callout,yesterday,row_number())) %>%
filter(DATE == yesterday)
##Source: local data frame [2 x 7]
##Groups: Child [2]
##
## DATE Parent Child avg_child_salary salary Callout Consec. Days with Callout
## <date> <fctr> <fctr> <dbl> <dbl> <fctr> <dbl>
##1 20161030 A ac 526.27 0.00 LOW 1
##2 20161030 G gb 18089.83 6973.54 LOW 1

Climate: Calculate Consecutive Days of Rain
By : Tom Morrison
Date : March 29 2020, 07:55 AM
this one helps. Following from Ted D's comment... a possible formula for the second helper column would be (in cell G2): code :
=IF(F2=0,0,MATCH(0,F3:F999,0)1+F2)
=IF($G2=VALUE(SUBSTITUTE(MID(H$1,3,999),"day","")),$F2,"")

How to calculate the difference between consecutive sets of two columns in a column range using dplyr
By : Avinash Tompe
Date : March 29 2020, 07:55 AM
I hope this helps you . Here's a less advanced approach using dplyr and tidyr verbs. First I gather the columns for differencing into long format, then take their differences vs. previous column, strip out NA's for the first columns which have no previous column, rename the column, spread out, and attach onto the original. code :
library(tidyverse)
iris %>%
bind_cols(iris %>%
rowid_to_column() %>%
gather(col, val, Sepal.Width:Petal.Width) %>%
group_by(rowid) %>%
mutate(val = abs(val  lag(val))) %>%
filter(!is.na(val)) %>%
mutate(col = paste0("diff_", col)) %>%
spread(col, val) %>%
select(contains("diff"))
)
Sepal.Length Sepal.Width Petal.Length Petal.Width Species rowid diff_Petal.Length diff_Petal.Width
1 5.1 3.5 1.4 0.2 setosa 1 2.1 1.2
2 4.9 3.0 1.4 0.2 setosa 2 1.6 1.2
3 4.7 3.2 1.3 0.2 setosa 3 1.9 1.1
4 4.6 3.1 1.5 0.2 setosa 4 1.6 1.3
5 5.0 3.6 1.4 0.2 setosa 5 2.2 1.2
6 5.4 3.9 1.7 0.4 setosa 6 2.2 1.3
7 4.6 3.4 1.4 0.3 setosa 7 2.0 1.1

dplyr: calculate the days for product replenishment
By : user3345062
Date : March 29 2020, 07:55 AM
around this issue I hope someone else finds a cleaner solution. But this produces diffDate which assigns the date difference from when a negative turns positive/zero. code :
sample_df %>%
mutate(sign = ifelse(Net_Available_Qty > 0, "pos", ifelse(Net_Available_Qty < 0, "neg", "zero")),
sign_lag = lag(sign, default = sign[1]), # get previous value (exception in the first place)
change = ifelse(sign != sign_lag, 1 , 0), # check if there's a change
sequence=sequence(rle(as.character(sign))$lengths)) %>%
group_by(sequence) %>%
mutate(diffDate = as.numeric(difftime(Date, lag(Date,1))),
diffDate=ifelse(Net_Available_Qty <0, NA, ifelse((sign=='pos' sign=='zero') & sequence==1, diffDate, NA))) %>%
ungroup() %>%
select(Date, Product, Net_Available_Qty, diffDate)

