Subset a df by the last non-NA value in a column

user_13153658 0
Viewed 40

My dataframe looks like this:

Year   aquil_7  aquil_8  aquil_9
2018   NA       201      222
2019   192      145      209
2020   166      121      NA
2021   190      NA       NA

I want to subset this dataframe so as to include only those columns where the last non-NA year is equal to or less then 2020. In the example above, this means deleting the aquil_7 column since it's last non-NA year is 2021.

How could I do this?

r
2 Answers

Not sure if there's a better way to implement this (but I do hope so). In the meantime, you could e.g. do

library(tidyverse)
cols_to_keep <- df %>%
  pivot_longer(-Year) %>%
  group_by(name) %>%
  summarize(var = min(Year[is.na(value)]) >= 2020) %>%
  filter(var) %>%
  pull(name)

df %>%
  select(Year, cols_to_keep)
user_55085000

A simple baseR answer.

Explanation - columnwise (that explaining arg 2 in apply) iteration to check given conditions on all database except first column. cbinding the result with T so that the result includes first column.

df <- read.table(text = "Year aquil_7 aquil_8 aquil_9 
2018 NA 201 222 
2019 192 145 209 
2020 166 121 NA 
2021 190 NA NA", header = T)

df[c(T, apply((!is.na(df[-1]))*df$Year, 2, function(x){max(x) < 2021}))]

Year aquil_8 aquil_9
1 2018     201     222
2 2019     145     209
3 2020     121      NA
4 2021      NA      NA
user_28848590
Related