保留满足一个标准的行，如果满足另一个标准，则保留其上方的行 [英] keeping a row that meets one criterion and the row above it if it meets another

问题描述

我有一个类似于以下数据集，但比以下数据集更长更复杂:

I have a data set similar to, but much longer and complex than, the following:

df<-data.frame(ID = c(1,1,2,2,3,3,3), 
               week = c(20,21,10,15,20,21,22), 
               var1 = c(0,1,0,1,0,0,1))

  ID week var1
1  1   20    0
2  1   21    1
3  2   10    0
4  2   15    1
5  3   20    0
6  3   21    0
7  3   22    1

我想创建一个新的数据框，它保留所有 var1=1 的行，如果 ID 相同且周数正好比包含的行少一个，则保留前一行.新数据框如下所示:

I would like to create a new data frame that keeps all rows where var1=1 and keeps the previous row if the ID is the same and the week is exactly one less than the included row. The new data frame would look like this:

  ID week var1
1  1   20    0
2  1   21    1
3  2   15    1
4  3   21    0
5  3   22    1

我试图子集

df1<-df[which(df$var1 == 1) - 1, ]

但这给了我前一行是否符合我的标准.

but that gives me the previous row whether it meets my criteria or not.

我也尝试过延迟 dplyr

I have also tried lag in dplyr

df2<-filter(df, var1==1 & lag(week)==week-1)

但这只给了我满足这两个条件的行.我搜索过的所有代码都会产生这些结果中的一个.

but that gives me only lines that meet both criteria. All of the code that I have searched results in one or the other of these results.

推荐答案

使用 SQL 我们有:

Using SQL we have:

library(sqldf)

sqldf("select b.* from df a join df b on a.ID = b.ID and b.week = a.week - 1
       where a.var1 = 1
       union
       select * from df 
       where var1 = 1
       order by ID, week")

给予

  ID week var1
1  1   20    0
2  1   21    1
3  2   15    1
4  3   21    0
5  3   22    1

这篇关于保留满足一个标准的行，如果满足另一个标准，则保留其上方的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！