每组n行和n-2行之间的差异

Question

我想确定每组每n行和n-2行之间的差值的计算方法。假设下面是我的数据：

Month, Laptop, Sales
Jan, HP, 1000
Feb, HP, 2000
Mar, HP, 1300
April, HP, 5000
Jan, Samsung, 1200
Feb, Samsung, 2500
Mar, Samsung, 1100
April, Samsung, 4500

现在，我需要如下所示的输出，其中我取每n行和n-2行之间的差值，并根据每个组的每月数据找到每个组的结果。

Month, Laptop, Sales, difference
Jan, HP, 1000 , NA
Feb, HP, 2000 , NA
Mar, HP, 1300 , 300
April, HP, 5000 , 3000,
Jan, Samsung, 1200 , NA
Feb, Samsung, 2500 , NA
Mar, Samsung, 1100 , -100
April, Samsung, 4500 , 2000

Answer 1

使用dplyr::lag函数，您可以执行以下操作

df <- df %>%
  group_by(Laptop) %>%
  mutate(difference = Sales - lag(Sales, 2))
df

# # A tibble: 8 x 4
# # Groups:   Laptop [2]
#   Month Laptop  Sales difference
#   <chr> <chr>   <int>      <int>
# 1 Jan   HP       1000         NA
# 2 Feb   HP       2000         NA
# 3 Mar   HP       1300        300
# 4 April HP       5000       3000
# 5 Jan   Samsung  1200         NA
# 6 Feb   Samsung  2500         NA
# 7 Mar   Samsung  1100       -100
# 8 April Samsung  4500       2000

数据

t <- "Month, Laptop, Sales
Jan, HP, 1000
Feb, HP, 2000
Mar, HP, 1300
April, HP, 5000
Jan, Samsung, 1200
Feb, Samsung, 2500
Mar, Samsung, 1100
April, Samsung, 4500"

df <- read.table(text = t, header = T, sep = ",", strip.white = TRUE)

Answer 2

您可以结合使用dplyr和diff函数来获得所需的输出。

lag = 2
df %>% group_by(Laptop) %>%
  mutate(difference = c(rep(NA,lag), diff(Sales, lag)))

# # A tibble: 8 x 4
# # Groups: Laptop [2]
#   Month Laptop  Sales difference
#   <chr> <chr>   <int>      <int>
# 1 Jan   HP       1000         NA
# 2 Feb   HP       2000         NA
# 3 Mar   HP       1300        300
# 4 April HP       5000       3000
# 5 Jan   Samsung  1200         NA
# 6 Feb   Samsung  2500         NA
# 7 Mar   Samsung  1100      - 100
# 8 April Samsung  4500       2000

数据：

df <- read.table(text =
"Month, Laptop, Sales
Jan, HP, 1000
Feb, HP, 2000
Mar, HP, 1300
April, HP, 5000
Jan, Samsung, 1200
Feb, Samsung, 2500
Mar, Samsung, 1100
April, Samsung, 4500",
header = TRUE, sep = ",", strip.white = TRUE, stringsAsFactors = FALSE)

Answer 3

您可以使用n()来访问dplyr中的组的最后一个行号，以及data.table中的等效.N

library(dplyr)    
df %>%
  group_by(Laptop) %>%
  mutate(difference =  c(NA,NA,Sales[3:n()]) - c(NA,NA,Sales[1:(n()-2)]) )

# A tibble: 8 x 4
# Groups:   Laptop [2]
   Month  Laptop Sales difference
  <fctr>  <fctr> <int>      <int>
1    Jan      HP  1000         NA
2    Feb      HP  2000         NA
3    Mar      HP  1300        300
4  April      HP  5000       3000
5    Jan Samsung  1200         NA
6    Feb Samsung  2500         NA
7    Mar Samsung  1100       -100
8  April Samsung  4500       2000

使用data.table

library(data.table)

setDT(df)[,.(difference =  c(NA,NA,Sales[3:.N]) - c(NA,NA,Sales[1:(.N-2)]) ), by = Laptop]

    Laptop difference
1:      HP         NA
2:      HP         NA
3:      HP        300
4:      HP       3000
5: Samsung         NA
6: Samsung         NA
7: Samsung       -100
8: Samsung       2000