I have dataset that contains 3 columns (Group, Date, Value). For each row (combination of Group and Date), I would like to evaluate when (in days) does the value increase by 1%.
df <- read.table(text =
"Group Date value
A 11/1/17 56
A 11/2/17 51
A 11/3/17 58
A 11/4/17 62
A 11/5/17 60
A 11/6/17 55
A 11/7/17 56
A 11/8/17 56
A 11/9/17 53
B 11/1/17 56
B 11/2/17 63
B 11/3/17 50
B 11/4/17 62
B 11/5/17 65
B 11/6/17 61
B 11/7/17 56
B 11/8/17 62
C 11/1/17 50
C 11/2/17 62 ", header = T)
I would like the output to look something like this… I am interested in the column increase_by_1%
For example, the logic for the 1st row would be
# if group == A & (value on 11/2/17 = 51 – value on 11/1/17 = 56)/ value on 11/1/17 = 56 >= .01 THEN FALSE
# if group == A & (value on 11/3/17 = 58 – value on 11/2/17 = 56)/ value on 11/1/17 = 56 >= .01 = TRUE THEN 2
The logic for the 2nd row...
# if group == A & (value on 11/3/17 = 58 – value on 11/2/17 = 51)/ value on 11/2/17 = 51 >= .01 = TRUE THEN 1
the logic for the 4th row...
# if group == A & (value on 11/5/17 = 60 – value on 11/4/17 = 62)/ value on 11/4/17 = 62 >= .01 = TRUE THEN NA
# if group == A & (value on 11/6/17 = 55 – value on 11/4/17 = 62)/ value on 11/4/17 = 62 >= .01 = TRUE THEN NA
# if group == A & (value on 11/7/17 = 56 – value on 11/4/17 = 62)/ value on 11/4/17 = 62 >= .01 = TRUE THEN NA
# if group == A & (value on 11/8/17 = 56 – value on 11/4/17 = 62)/ value on 11/4/17 = 62 >= .01 = TRUE THEN NA
# if group == A & (value on 11/9/17 = 53 – value on 11/4/17 = 62)/ value on 11/4/17 = 62 >= .01 = TRUE THEN NA
+=======+=========+=======+================+
| Group | Date | value | increase_by_1% |
+=======+=========+=======+================+
| A | 11/1/17 | 56 | 2 |
+-------+---------+-------+----------------+
| A | 11/2/17 | 51 | 1 |
+-------+---------+-------+----------------+
| A | 11/3/17 | 58 | 1 |
+-------+---------+-------+----------------+
| A | 11/4/17 | 62 | NA |
+-------+---------+-------+----------------+
| A | 11/5/17 | 60 | NA |
+-------+---------+-------+----------------+
| A | 11/6/17 | 55 | 1 |
+-------+---------+-------+----------------+
| A | 11/7/17 | 56 | NA |
+-------+---------+-------+----------------+
| A | 11/8/17 | 56 | NA |
+-------+---------+-------+----------------+
| A | 11/9/17 | 53 | NA |
+-------+---------+-------+----------------+
| B | 11/1/17 | 56 | 1 |
+-------+---------+-------+----------------+
| B | 11/2/17 | 63 | 3 |
+-------+---------+-------+----------------+
| B | 11/3/17 | 50 | 1 |
+-------+---------+-------+----------------+
| B | 11/4/17 | 62 | 1 |
+-------+---------+-------+----------------+
| B | 11/5/17 | 65 | NA |
+-------+---------+-------+----------------+
| B | 11/6/17 | 61 | 2 |
+-------+---------+-------+----------------+
| B | 11/7/17 | 56 | 1 |
+-------+---------+-------+----------------+
| B | 11/8/17 | 62 | NA |
+-------+---------+-------+----------------+
| C | 11/1/17 | 50 | 1 |
+-------+---------+-------+----------------+
| C | 11/2/17 | 62 | NA |
+-------+---------+-------+----------------+
This is what I have so far, however, this is not scalable. If there are more dates, then I would need to manually add those in the if else statement.
shift <- function(x, n){
c(x[-(seq(n))], rep(NA, n))
}
df= do.call(rbind,by(df,df$Group, transform,next_1_percent_or_higher_change =
ifelse(((shift(value,1)-value)/value) >= .01,1,
ifelse(((shift(value,2)-value)/value) >= .01,2,
ifelse(((shift(value,3)-value)/value) >= .01,3,
ifelse(((shift(value,4)-value)/value) >= .01,4,
ifelse(((shift(value,5)-value)/value) >= .01,5,
ifelse(((shift(value,6)-value)/value) >= .01,6,
ifelse(((shift(value,7)-value)/value) >= .01,7,
ifelse(((shift(value,8)-value)/value) >= .01,8,
ifelse(((shift(value,9)-value)/value) >= .01,9,NA)))))))))))
Aucun commentaire:
Enregistrer un commentaire