mardi 4 septembre 2018

Compare the current row against subsequent rows (order by date and group) in r

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