Dividing each row by the previous one in R

Question

I have R dataframe:

   city hour value
0   NY  0   12
1   NY  12  24
2   LA  0   3
3   LA  12  9

I want, for each city, to divide each row by the previous one and write the result into a new dataframe. The desired output is:

city ratio
NY   2
LA   3

Have a look at this answer https://stackoverflow.com/questions/14689424/use-a-value-from-the-previous-row-in-an-r-data-table-calculation — at80, Sep 23 '20 at 11:50

score 4 · Accepted Answer · answered Sep 23 '20 at 11:52

You can try aggregate like below

aggregate(value ~city,df, function(x) x[-1]/x[1])

which gives

  city value
1   LA     3
2   NY     2

Data

> dput(df)
structure(list(city = c("NY", "NY", "LA", "LA"), hour = c(0L, 
12L, 0L, 12L), value = c(12L, 24L, 3L, 9L)), class = "data.frame", row.names = c("0",
"1", "2", "3"))

score 4 · Answer 2 · answered Sep 23 '20 at 12:04

You can use lag to get previous value, divide each value by it's previous value for each city and drop NA rows.

library(dplyr)
df %>%
  arrange(city, hour) %>%
  group_by(city) %>%
  summarise(value = value/lag(value)) %>%
  na.omit()

#  city  value
#  <chr> <dbl>
#1 LA        3
#2 NY        2

In data.table we can do this via shift :

library(data.table)
setDT(df)[order(city, hour), value := value/shift(value), city]
na.omit(df)

Dividing each row by the previous one in R

2 Answers2