获取组内的 (t-1) 数据 [英] Get the (t-1) data within groups

问题描述

抱歉，如果之前有人问过这个问题，但我找不到任何可以准确回答这个问题的问题.我有这样的数据:

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

我想有一个按组显示最后实例价格的列.例如，对于第 2 行，同一组的最后一次实例价格为 1642.最终数据将如下所示:

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.

推荐答案

这是一个选项.如果 0，我还建议使用 NAs 代替，因为 0 可能是实际价格.

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

<小时>

另一种选择是使用 开发版本中的 shift<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

<小时>

或使用基数 R

---

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

<小时>

作为旁注，最好首先将日期列保留在 Date 类中，因此我建议您使用 df$Date <- as.Date(df$Date, format = "%d/%m/%Y") 一劳永逸.

---

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.

这篇关于获取组内的 (t-1) 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！