find last row where at least one column is not NA

user_9778946 0
Viewed 917

I have an object that has all NAs in each column after a certain number of rows. Some of the columns also have NAs prior to this point. I want to get the row index of the last row where at least one column has data in it. Here is some sample data to work with:

EDIT: For robustness, I've added NAs in the second row, following @G. Grothendieck comments. In this case, the output should still be 5.

df <- data.frame(a = 1:5, b = 6:10, c = c(1:3,rep(NA, 2)))
df <- rbind(df, rep(NA, ncol(df)), rep(NA, ncol(df)))
df[2,] <- NA

df
   a  b  c
1  1  6  1
2 NA NA NA
3  3  8  3
4  4  9 NA
5  5 10 NA
6 NA NA NA
7 NA NA NA
data.table dplyr data-manipulation dataframe r
3 Answers

1) na.trim This removes rows from the bottom which are all NA and then returns the number of rows left:

library(zoo)
nrow(na.trim(df, "right", is.na = "all"))
## [1] 5

2) Base R We can replace each non-NA with its row number and then take the maximum of those numbers:

max(ifelse(is.na(df), NA, row(df)), na.rm = TRUE)
## [1] 5

2a) If all the entries are numeric, as in the question, then this could be shortened to:

max(row(df) + 0 * df, na.rm = TRUE)
## [1] 5
user_5165480

[1] We can use rowSums to create a logical vector and wrap with which to return the index

tail(which(rowSums(!is.na(df)) > 0), 1)
#[1] 5

[2] Or another option is lengths. after removing the NA in each column

max(lengths(lapply(df, na.omit)))
#[1] 5

This option could fail in some edge cases as @G Grothendieck mentioned in the comments i.e. when a particular row is all NA before the last set of NA rows

[3] Or another option is which with arr.ind option on a logical matrix

max(which(!is.na(df), arr.ind = TRUE)[,1])
#[1] 5

[4] or with row and is.na

max(row(df) * NA^is.na(df), na.rm = TRUE)
#[1] 5

NOTE: All approaches use base R and requires no additional packages

user_37322710

Another option:

nrow(df[!apply(df, 1, function(x) all(is.na(x))), ])

# [1] 5

Note that this only works if the rows with all missing values are at the very end of your data frame, e.g. it would fail with df[2, ] <- NA as mentioned by @G.Grothendieck.

Another option for tackling these edge cases would be:

sum(cumsum(rowSums(df[rev(rownames(df)),], na.rm = TRUE)) != 0)

# [1] 5
user_83890030
Related