CODEWITHSUNDEEP
rmissing-datadata-manipulation
InspectorSands
InspectorSands

Reputation: 2949

Delete consecutive empty rows in R

df presents possible name matches. Each pair of matches should be divided by an empty row. However, in some cases my output includes several empty rows between the matching pairs:

> df <- data.frame(id = c(1,2,NA,3,4,NA,NA,NA,5,6,NA), name = c("john jones", "john joners", 
                   NA, "clara prat", "klara prat", NA, NA, NA, "alan turing", "allan turing", 
                   NA), stringsAsFactors = F)
> df
   id         name
1   1   john jones
2   2  john joners
3  NA         <NA>
4   3   clara prat
5   4   klara prat
6  NA         <NA>
7  NA         <NA>
8  NA         <NA>
9   5  alan turing
10  6 allan turing
11 NA         <NA>

The desired output is:

> df
   id         name
1   1   john jones
2   2  john joners
3  NA         <NA>
4   3   clara prat
5   4   klara prat
6  NA         <NA>
7   5  alan turing
8   6 allan turing
9  NA         <NA>

I can do this with a for loop, which I understand is less than optimal.

Upvotes: 3

Views: 401

Answers (4)

G. Cocca
G. Cocca

Reputation: 2541

Surely not the best solution but easy to follow..

miss <- rowSums(is.na(df))
r <- sum(rle(miss)[[2]])

 for(i in 2:length(df$id)){
  while(is.na(df$id[i-1]) & is.na(df$id[i])){
   df <- df[-(i),] 
  if(sum(is.na(df$id)) == r) break
  }
 }

Upvotes: 1

user20650
user20650

Reputation: 25914

Here is another approach using rle to look for runs of missing

miss <- rowSums(is.na(df))

# get runs of missing 
r <- rle(miss)
r$values <- seq_along(r$values)

# subset data, removing rows when all columns are missing 
# and rows sequentially missing
df[!(miss == ncol(df) & duplicated(inverse.rle(r))), ]
#   id         name
# 1   1   john jones
# 2   2  john joners
# 3  NA         <NA>
# 4   3   clara prat
# 5   4   klara prat
# 6  NA         <NA>
# 9   5  alan turing
# 10  6 allan turing
# 11 NA         <NA>

As mentioned by Akrun, you can use data.table::rleid to avoid some of the explicit rle calculations

df[!(rowSums(is.na(df)) == ncol(df) & duplicated(data.table::rleid(is.na(df[[1]])))) , ]

Upvotes: 2

Ven Yao
Ven Yao

Reputation: 3710

Using the IRanges package.

df <- data.frame(id = c(1,2,NA,3,4,NA,NA,NA,5,6,NA), name = c("john jones", "john joners", 
                  NA, "clara prat", "klara prat", NA, NA, NA, "alan turing", "allan turing", 
                                         NA), stringsAsFactors = F)

library(IRanges)
na.rs <- which(is.na(df$id) & is.na(df$name))
na.rs.re <- reduce(IRanges(na.rs, na.rs))
na.rs.rm <- na.rs.re[width(na.rs.re)>1]
start(na.rs.rm) <- start(na.rs.rm) + 1

df[-as.integer(na.rs.rm), ]
#    id         name
# 1   1   john jones
# 2   2  john joners
# 3  NA         <NA>
# 4   3   clara prat
# 5   4   klara prat
# 6  NA         <NA>
# 9   5  alan turing
# 10  6 allan turing
# 11 NA         <NA>

Upvotes: 1

akrun
akrun

Reputation: 887981

Perhaps this helps

v1 <- rowSums(!is.na(df))
df[unlist(lapply(split(seq_along(v1),
     cumsum(c(1, diff(!v1))<0)), function(i) 
                    i[seq(which.max(v1[i]==0))])),]
#   id         name
#1   1   john jones
#2   2  john joners
#3  NA         <NA>
#4   3   clara prat
#5   4   klara prat
#6  NA         <NA>
#9   5  alan turing
#10  6 allan turing
#11 NA         <NA>

Upvotes: 3

Related Questions