Delete successive rows under condition in R
I have a data frame
dt <- read.table(text = "
350 16
352 0
354 0
359 0
366 11
376 38
380 28
386 0
389 0
397 55
398 45
399 0
400 19
402 30")
When successive rows contain zero in the second column, I want to keep only the zero row which precedes the non zero row in the second column.
The result must be:
dt <- read.table(text = "
350 16
359 0
366 11
376 38
380 28
389 0
397 55
398 45
399 0
400 19
402 30")
r delete-row
asked Nov 22 at 18:14
Noura
544
add a comment |
I have a data frame
dt <- read.table(text = "
350 16
352 0
354 0
359 0
366 11
376 38
380 28
386 0
389 0
397 55
398 45
399 0
400 19
402 30")
When successive rows contain zero in the second column, I want to keep only the zero row which precedes the non zero row in the second column.
The result must be:
dt <- read.table(text = "
350 16
359 0
366 11
376 38
380 28
389 0
397 55
398 45
399 0
400 19
402 30")
r delete-row
asked Nov 22 at 18:14
Noura
544
add a comment |
I have a data frame
dt <- read.table(text = "
350 16
352 0
354 0
359 0
366 11
376 38
380 28
386 0
389 0
397 55
398 45
399 0
400 19
402 30")
When successive rows contain zero in the second column, I want to keep only the zero row which precedes the non zero row in the second column.
The result must be:
dt <- read.table(text = "
350 16
359 0
366 11
376 38
380 28
389 0
397 55
398 45
399 0
400 19
402 30")
r delete-row
asked Nov 22 at 18:14
Noura
544
I have a data frame
dt <- read.table(text = "
350 16
352 0
354 0
359 0
366 11
376 38
380 28
386 0
389 0
397 55
398 45
399 0
400 19
402 30")
When successive rows contain zero in the second column, I want to keep only the zero row which precedes the non zero row in the second column.
The result must be:
dt <- read.table(text = "
350 16
359 0
366 11
376 38
380 28
389 0
397 55
398 45
399 0
400 19
402 30")
r delete-row
r delete-row
asked Nov 22 at 18:14
Noura
544
asked Nov 22 at 18:14
Noura
544
asked Nov 22 at 18:14
Noura
544
asked Nov 22 at 18:14
Noura
544
asked Nov 22 at 18:14
Noura
544
544
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Simple one line solution:
dplyr::filter(dt, !(V2==0 & lead(V2)==0))
V1 V2
1 350 16
2 359 0
3 366 11
4 376 38
5 380 28
6 389 0
7 397 55
8 398 45
9 399 0
10 400 19
11 402 30
answered Nov 22 at 18:19
iod
3,4892721
add a comment |
Here is an option where we create a grouping variable with rleid based on the zero values and filter with the conditions mentioned in the OP's post
library(tidyverse)
library(data.table)
dt %>%
group_by(grp = rleid(V2 == 0)) %>%
filter(all(V2== 0) & row_number()==n() | V2 != 0) %>%
ungroup %>%
select(-grp)
# A tibble: 11 x 2
# V1 V2
# <int> <int>
# 1 350 16
# 2 359 0
# 3 366 11
# 4 376 38
# 5 380 28
# 6 389 0
# 7 397 55
# 8 398 45
# 9 399 0
#10 400 19
#11 402 30
Or using data.table, the same logic can be applied
setDT(dt)[dt[, .I[(V2 == 0 & seq_len(.N) == .N) | V2 != 0], rleid(V2 == 0)]$V1]
# V1 V2
# 1: 350 16
# 2: 359 0
# 3: 366 11
# 4: 376 38
# 5: 380 28
# 6: 389 0
# 7: 397 55
# 8: 398 45
# 9: 399 0
#10: 400 19
#11: 402 30
Or as @jogo mentioned in the comments, to create a grouping column with rleid and then subset the first row (that have only 0 values in 'V2') based on a if/else condition
setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
NOTE: These are flexible solutions which can be generalized
answered Nov 22 at 18:17
akrun
396k13187260
1
next try:setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
– jogo
Nov 22 at 19:10
add a comment |
Here is the data.table equivalent to the solution from @iod :
library("data.table")
dt <- fread(
"350 16
352 0
354 0
359 0
366 11
376 38
380 38
386 0
389 0
397 55
398 45
399 0
400 19
402 30")
dt[V2!=0 | shift(V2, type="lead")!=0]
answered Nov 25 at 17:05
jogo
9,82692135
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436337%2fdelete-successive-rows-under-condition-in-r%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Simple one line solution:
dplyr::filter(dt, !(V2==0 & lead(V2)==0))
V1 V2
1 350 16
2 359 0
3 366 11
4 376 38
5 380 28
6 389 0
7 397 55
8 398 45
9 399 0
10 400 19
11 402 30
answered Nov 22 at 18:19
iod
3,4892721
add a comment |
Simple one line solution:
dplyr::filter(dt, !(V2==0 & lead(V2)==0))
V1 V2
1 350 16
2 359 0
3 366 11
4 376 38
5 380 28
6 389 0
7 397 55
8 398 45
9 399 0
10 400 19
11 402 30
answered Nov 22 at 18:19
iod
3,4892721
add a comment |
Simple one line solution:
dplyr::filter(dt, !(V2==0 & lead(V2)==0))
V1 V2
1 350 16
2 359 0
3 366 11
4 376 38
5 380 28
6 389 0
7 397 55
8 398 45
9 399 0
10 400 19
11 402 30
answered Nov 22 at 18:19
iod
3,4892721
Simple one line solution:
dplyr::filter(dt, !(V2==0 & lead(V2)==0))
V1 V2
1 350 16
2 359 0
3 366 11
4 376 38
5 380 28
6 389 0
7 397 55
8 398 45
9 399 0
10 400 19
11 402 30
answered Nov 22 at 18:19
iod
3,4892721
edited Nov 22 at 18:42
answered Nov 22 at 18:19
iod
3,4892721
answered Nov 22 at 18:19
iod
3,4892721
answered Nov 22 at 18:19
iod
3,4892721
3,4892721
add a comment |
add a comment |
Here is an option where we create a grouping variable with rleid based on the zero values and filter with the conditions mentioned in the OP's post
library(tidyverse)
library(data.table)
dt %>%
group_by(grp = rleid(V2 == 0)) %>%
filter(all(V2== 0) & row_number()==n() | V2 != 0) %>%
ungroup %>%
select(-grp)
# A tibble: 11 x 2
# V1 V2
# <int> <int>
# 1 350 16
# 2 359 0
# 3 366 11
# 4 376 38
# 5 380 28
# 6 389 0
# 7 397 55
# 8 398 45
# 9 399 0
#10 400 19
#11 402 30
Or using data.table, the same logic can be applied
setDT(dt)[dt[, .I[(V2 == 0 & seq_len(.N) == .N) | V2 != 0], rleid(V2 == 0)]$V1]
# V1 V2
# 1: 350 16
# 2: 359 0
# 3: 366 11
# 4: 376 38
# 5: 380 28
# 6: 389 0
# 7: 397 55
# 8: 398 45
# 9: 399 0
#10: 400 19
#11: 402 30
Or as @jogo mentioned in the comments, to create a grouping column with rleid and then subset the first row (that have only 0 values in 'V2') based on a if/else condition
setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
NOTE: These are flexible solutions which can be generalized
answered Nov 22 at 18:17
akrun
396k13187260
1
next try:setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
– jogo
Nov 22 at 19:10
add a comment |
Here is an option where we create a grouping variable with rleid based on the zero values and filter with the conditions mentioned in the OP's post
library(tidyverse)
library(data.table)
dt %>%
group_by(grp = rleid(V2 == 0)) %>%
filter(all(V2== 0) & row_number()==n() | V2 != 0) %>%
ungroup %>%
select(-grp)
# A tibble: 11 x 2
# V1 V2
# <int> <int>
# 1 350 16
# 2 359 0
# 3 366 11
# 4 376 38
# 5 380 28
# 6 389 0
# 7 397 55
# 8 398 45
# 9 399 0
#10 400 19
#11 402 30
Or using data.table, the same logic can be applied
setDT(dt)[dt[, .I[(V2 == 0 & seq_len(.N) == .N) | V2 != 0], rleid(V2 == 0)]$V1]
# V1 V2
# 1: 350 16
# 2: 359 0
# 3: 366 11
# 4: 376 38
# 5: 380 28
# 6: 389 0
# 7: 397 55
# 8: 398 45
# 9: 399 0
#10: 400 19
#11: 402 30
Or as @jogo mentioned in the comments, to create a grouping column with rleid and then subset the first row (that have only 0 values in 'V2') based on a if/else condition
setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
NOTE: These are flexible solutions which can be generalized
answered Nov 22 at 18:17
akrun
396k13187260
1
next try:setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
– jogo
Nov 22 at 19:10
add a comment |
Here is an option where we create a grouping variable with rleid based on the zero values and filter with the conditions mentioned in the OP's post
library(tidyverse)
library(data.table)
dt %>%
group_by(grp = rleid(V2 == 0)) %>%
filter(all(V2== 0) & row_number()==n() | V2 != 0) %>%
ungroup %>%
select(-grp)
# A tibble: 11 x 2
# V1 V2
# <int> <int>
# 1 350 16
# 2 359 0
# 3 366 11
# 4 376 38
# 5 380 28
# 6 389 0
# 7 397 55
# 8 398 45
# 9 399 0
#10 400 19
#11 402 30
Or using data.table, the same logic can be applied
setDT(dt)[dt[, .I[(V2 == 0 & seq_len(.N) == .N) | V2 != 0], rleid(V2 == 0)]$V1]
# V1 V2
# 1: 350 16
# 2: 359 0
# 3: 366 11
# 4: 376 38
# 5: 380 28
# 6: 389 0
# 7: 397 55
# 8: 398 45
# 9: 399 0
#10: 400 19
#11: 402 30
Or as @jogo mentioned in the comments, to create a grouping column with rleid and then subset the first row (that have only 0 values in 'V2') based on a if/else condition
setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
NOTE: These are flexible solutions which can be generalized
answered Nov 22 at 18:17
akrun
396k13187260
Here is an option where we create a grouping variable with rleid based on the zero values and filter with the conditions mentioned in the OP's post
library(tidyverse)
library(data.table)
dt %>%
group_by(grp = rleid(V2 == 0)) %>%
filter(all(V2== 0) & row_number()==n() | V2 != 0) %>%
ungroup %>%
select(-grp)
# A tibble: 11 x 2
# V1 V2
# <int> <int>
# 1 350 16
# 2 359 0
# 3 366 11
# 4 376 38
# 5 380 28
# 6 389 0
# 7 397 55
# 8 398 45
# 9 399 0
#10 400 19
#11 402 30
Or using data.table, the same logic can be applied
setDT(dt)[dt[, .I[(V2 == 0 & seq_len(.N) == .N) | V2 != 0], rleid(V2 == 0)]$V1]
# V1 V2
# 1: 350 16
# 2: 359 0
# 3: 366 11
# 4: 376 38
# 5: 380 28
# 6: 389 0
# 7: 397 55
# 8: 398 45
# 9: 399 0
#10: 400 19
#11: 402 30
Or as @jogo mentioned in the comments, to create a grouping column with rleid and then subset the first row (that have only 0 values in 'V2') based on a if/else condition
setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
NOTE: These are flexible solutions which can be generalized
answered Nov 22 at 18:17
akrun
396k13187260
edited Nov 22 at 19:25
answered Nov 22 at 18:17
akrun
396k13187260
answered Nov 22 at 18:17
akrun
396k13187260
answered Nov 22 at 18:17
akrun
396k13187260
396k13187260
1
next try:setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
– jogo
Nov 22 at 19:10
add a comment |
1
next try:setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]
– jogo
Nov 22 at 19:10
1
1
next try:
setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]– jogo
Nov 22 at 19:10
next try:
setDT(dt)[, i:=rleid(V2)][, if (any(V2!=0)) .SD else .SD[.N], i]– jogo
Nov 22 at 19:10
add a comment |
Here is the data.table equivalent to the solution from @iod :
library("data.table")
dt <- fread(
"350 16
352 0
354 0
359 0
366 11
376 38
380 38
386 0
389 0
397 55
398 45
399 0
400 19
402 30")
dt[V2!=0 | shift(V2, type="lead")!=0]
answered Nov 25 at 17:05
jogo
9,82692135
add a comment |
Here is the data.table equivalent to the solution from @iod :
library("data.table")
dt <- fread(
"350 16
352 0
354 0
359 0
366 11
376 38
380 38
386 0
389 0
397 55
398 45
399 0
400 19
402 30")
dt[V2!=0 | shift(V2, type="lead")!=0]
answered Nov 25 at 17:05
jogo
9,82692135
add a comment |
Here is the data.table equivalent to the solution from @iod :
library("data.table")
dt <- fread(
"350 16
352 0
354 0
359 0
366 11
376 38
380 38
386 0
389 0
397 55
398 45
399 0
400 19
402 30")
dt[V2!=0 | shift(V2, type="lead")!=0]
answered Nov 25 at 17:05
jogo
9,82692135
Here is the data.table equivalent to the solution from @iod :
library("data.table")
dt <- fread(
"350 16
352 0
354 0
359 0
366 11
376 38
380 38
386 0
389 0
397 55
398 45
399 0
400 19
402 30")
dt[V2!=0 | shift(V2, type="lead")!=0]
answered Nov 25 at 17:05
jogo
9,82692135
answered Nov 25 at 17:05
jogo
9,82692135
answered Nov 25 at 17:05
jogo
9,82692135
answered Nov 25 at 17:05
jogo
9,82692135
9,82692135
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436337%2fdelete-successive-rows-under-condition-in-r%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
