Menu
Apologies if this has been asked before, but I couldn't find any question which answers this exactly. I have a data like this:
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 last-instance prices by group. For example, for row 2, the last instance price for same group will be 1642. The final data will look somewhat 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 this? The main issue I'm facing is that the data may not be ordered by date so its not as if I can just take the last cell.
228
First, you need to write a CTE in which you assign a number to each row within each group. To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).
Retrieving the last record in each group using GROUP BY There are two solutions explained here using the GROUP BY clause. In both these solutions, we will be using the MAX() function to get the maximum value of id and then retrieving the other columns corresponding to this maximum id.
Here's an option. I'd also recommend to use NAs instead if 0 because 0 could be 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 devel version of 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 is better to keep your date column in a Date class in the first place, so I'd recommend doing df$Date <- as.Date(df$Date, format = "%d/%m/%Y") once and for all.
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
