R Add the value of the previous month as a column
I have a datasheet with date and price by month:
set.seed(128)
dat <- data.table(DATE=c("2014-01-01","2014-02-01","2014-03-01","2014-04-01",
"2014-05-01","2014-06-01","2014-07-01","2014-08-01",
"2014-09-01","2014-10-01","2014-11-01","2014-12-01"),
PRICE=rnorm(n = 12, mean=100, sd=15))
dat
DATE PRICE
1: 2014-01-01 108.83590
2: 2014-02-01 67.69042
3: 2014-03-01 122.28905
4: 2014-04-01 93.50617
5: 2014-05-01 109.57878
6: 2014-06-01 128.81537
7: 2014-07-01 82.67295
8: 2014-08-01 68.25281
9: 2014-09-01 82.98265
10: 2014-10-01 124.28960
11: 2014-11-01 84.61275
12: 2014-12-01 93.14938
I would like to add the price of previous months as a column in the data table, so the table would look like this:
DATE PRICE PRICE_PREV
1: 2014-01-01 108.83590
2: 2014-02-01 67.69042 108.83590
3: 2014-03-01 122.28905 67.69042
...
10: 2014-10-01 124.28960 82.98265
11: 2014-11-01 84.61275 124.28960
12: 2014-12-01 93.14938 84.61275
How would I do this in R?
+3
source to share
2 answers
Use shift
from data.table
v> = 1.9.5 (on GitHub)
dat[, PRICE_PREV := shift(PRICE)]
dat
# DATE PRICE PRICE_PREV
# 1: 2014-01-01 108.95158 NA
# 2: 2014-02-01 107.23918 108.95158
# 3: 2014-03-01 124.96608 107.23918
# 4: 2014-04-01 99.61007 124.96608
# 5: 2014-05-01 118.13540 99.61007
# 6: 2014-06-01 108.70528 118.13540
# 7: 2014-07-01 93.12482 108.70528
# 8: 2014-08-01 100.06829 93.12482
# 9: 2014-09-01 103.14932 100.06829
# 10: 2014-10-01 122.66710 103.14932
# 11: 2014-11-01 104.84064 122.66710
# 12: 2014-12-01 113.37610 104.84064
If you have an older version (v <1.9.5) you can also do
dat[, PRICE_PREV := c(NA, PRICE[-.N])]
+6
source to share
The first term has no known price for the previous month (I guess if so, you can replace NA with it).
The following code should work fine. The first line creates a new vector, the second adds it as a column to dat, and the third removes the vector (like dat is now, and we don't need it separately).
PRICE_PREV <- c(NA, dat$PRICE[1:length(dat$PRICE)-1])
dat <- cbind(dat, PRICE_PREV)
rm(PRICE_PREV)
0
source to share