Geek Answers Handbook

Receive (t-1) data within groups

Sorry if this was asked before, but I could not find the answer to this question. I have the following data:

Project Date price A 30/3/2013 2082 B 19/3/2013 1567 B 22/2/2013 1642 C 12/4/2013 1575 C 5/6/2013 1582

I want to have a column with prices of the last instance by group. For example, for line 2, the price of the last instance for the same group will be 1642. The final data will look something like this:

 Project Date price lastPrice A 30/3/2013 2082 0 B 19/3/2013 1567 1642 B 22/2/2013 1642 0 C 12/4/2013 1575 0 C 5/6/2013 1582 1575

How to do it? The main problem that I am facing is that the data cannot be sorted by date, so it’s not as if I can just take the last cell.

+5
source share
1 answer

Here is an option. I would also recommend using NA instead of NA if 0 , because 0 might be the actual price.

 library(dplyr) df %>% arrange(as.Date(Date, format = "%d/%m/%Y")) %>% group_by(Project) %>% mutate(lastPrice = lag(price)) # Source: local data frame [5 x 4] # Groups: Project # # Project Date price lastPrice # 1 B 22/2/2013 1642 NA # 2 B 19/3/2013 1567 1642 # 3 A 30/3/2013 2082 NA # 4 C 12/4/2013 1575 NA # 5 C 5/6/2013 1582 1575

Another option is to use shift from the version of devel data.table

 library(data.table) ## v >= 1.9.5 setDT(df)[order(as.Date(Date, format = "%d/%m/%Y")), lastPrice := shift(price), by = Project] # Project Date price lastPrice # 1: A 30/3/2013 2082 NA # 2: B 19/3/2013 1567 1642 # 3: B 22/2/2013 1642 NA # 4: C 12/4/2013 1575 NA # 5: C 5/6/2013 1582 1575

Or with base R

 df <- df[order(df$Project, as.Date(df$Date, format = "%d/%m/%Y")), ] within(df, lastPrice <- ave(price, Project, FUN = function(x) c(NA, x[-length(x)]))) # Project Date price lastPrice # 1 A 30/3/2013 2082 NA # 3 B 22/2/2013 1642 NA # 2 B 19/3/2013 1567 1642 # 4 C 12/4/2013 1575 NA # 5 C 5/6/2013 1582 1575

As a side note, it's best to keep the date column in the Date class first, so I recommend doing df$Date <- as.Date(df$Date, format = "%d/%m/%Y") once and for all.

+7
source

More articles:


All Articles