CODEWITHSUNDEEP
rdplyr
KNOCK
KNOCK

Reputation: 33

how to mutate for grouped table

i have 'abc' table like this,

> abc
country   date      count
A      2020-03-01     1
A      2020-03-02     2
A      2020-03-03     3
B      2020-03-01     2
B      2020-03-02     5
B      2020-03-03     10
C      2020-03-01     1
C      2020-03-02     3
C      2020-03-03     6

and I want to mutate 'abc' table with 'rate' that meaning "x-day's count/(x-1)-day's count".

like this.

> abc
country   date      count  rate
A      2020-03-01     1     NA
A      2020-03-02     2     2
A      2020-03-03     3     1.5
B      2020-03-01     2     NA
B      2020-03-02     5     2.5
B      2020-03-03     10    2
C      2020-03-01     1     NA
C      2020-03-02     3     3
C      2020-03-03     6     2

how can i make it?

waiting for your solution :)

Upvotes: 1

Views: 55

Answers (2)

akrun
akrun

Reputation: 887118

With dplyr

library(dplyr)
abc %>%
   group_by(country) %>%
   mutate(rate = c(NA, count[-n()]/count[-1]))

Or using data.table

library(data.table)
setDT(abc)[, rate := c(NA, count[.N]/count[-1]), country]

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388982

You can use lag in dplyr

library(dplyr)
abc %>% group_by(country) %>% mutate(rate = count/lag(count))

#  country date       count  rate
#  <fct>   <fct>      <int> <dbl>
#1 A       2020-03-01     1  NA  
#2 A       2020-03-02     2   2  
#3 A       2020-03-03     3   1.5
#4 B       2020-03-01     2  NA  
#5 B       2020-03-02     5   2.5
#6 B       2020-03-03    10   2  
#7 C       2020-03-01     1  NA  
#8 C       2020-03-02     3   3  
#9 C       2020-03-03     6   2

shift in data.table

library(data.table)
setDT(abc)[, rate := count/shift(count), country]

Or head/tail in base R to do this. 

abc$rate <- with(abc, ave(count, country, FUN = function(x) 
                 c(NA, tail(x, -1)/head(x, -1))))

Upvotes: 1

Related Questions