Create Column with ONLY the value from the last Date in R-CodePudding

I have a dataframe with I need to create a new column by finding the value of the last day, I was able to do so, but I was only able to extract values to all dates in this columns, I want to leave the rest of the dates blank. my code is

df %>% 
 arrange(as.Date(DATE)) %>% 
 group_by(cow) %>% 
 mutate(last = last(VALUE))->df

Please see my dataframe here

| DATE| cow| value|
|:---- |:------:| -----:|
| 2010-01-01 | public   | 54|
| 2019-01-01 | public   | 60|
| 2010-01-01 | private| 154|
| 2019-01-01 | private| 20|

Ideally I want to end with

| DATE| cow| value|LAST|
|:---- |:------:| -----:|-----:|
| 2010-01-01 | public   | 54| |
| 2019-01-01 | public   | 60|60|
| 2010-01-01 | private| 154| |
| 2019-01-01 | private| 20|20 |

CodePudding user response：

With dplyr:

library(dplyr)
df %>% group_by(cow) %>%
  mutate(LAST = ifelse(row_number() == n(), value, NA))

CodePudding user response：

Here is an option with data.table:

library(data.table)

dt <- as.data.table(df)
dt[ , lastconv := ifelse(.I == .I[.N],value,NA ), by = cow]

Output

         DATE     cow value lastconv
1: 2010-01-01  public    54       NA
2: 2019-01-01  public    60       60
3: 2010-01-01 private   154       NA
4: 2019-01-01 private    20       20

Data

df <- structure(list(DATE = c("2010-01-01", "2019-01-01", "2010-01-01", 
"2019-01-01"), cow = c("public", "public", "private", "private"
), value = c(54L, 60L, 154L, 20L)), class = "data.frame", row.names = c(NA, 
-4L))