panel data: How to remove IDs with missing yearly information

user_9354181 0
Viewed 466

I have a dataset with Id-year observations. I want to compare the change before and after/in 2015. Therefore I need all firms to have observations before and after/in 2015 so that I could compare. 

ID year diesese
1 2012  3
1 2016  4
3 2013  3
3 2015  4
2 2012  3
2 2013  4

My question is how to delete firm with observations only before 2015, or after 2015? so in data above, only ID=1 and ID=3 match my need, ID=2 does not.

panel-data time-series r
3 Answers

An idea is to use ave with a function that counts the number of values greater or equal to 2015. !! converts it to logical so we can index, i.e.

df[!!with(df, ave(year, ID, FUN = function(i)length(i[i >= 2015]) >= 1)),]

which gives,

ID year disease
1  1 2012       3
2  1 2016       4
3  3 2013       3
4  3 2015       4

A couple of more options by @RonakShah and @Jaap,

df[!with(df, ave(year, ID, FUN = function(x) all(x > 2015) | all(x < 2015)))), ]
df[with(df, ave(year, ID, FUN = function(y) any(y >= 2015))),]
user_56355800

With tidyverse:

df%>%
   mutate_all(as.numeric)%>%
   group_by(ID)%>%
   filter(ID %in% ID[any(year>=2015) & any(year<2015)])
# A tibble: 4 x 3
# Groups:   ID [2]
     ID  year diesese
  <dbl> <dbl>   <dbl>
1    1. 2012.      3.
2    1. 2016.      4.
3    3. 2013.      3.
4    3. 2015.      4.

or this one 

df%>%
  mutate_all(as.numeric)%>%
  group_by(ID)%>%
  filter(!ID %in% ID[all(year>2015) | all(year<2015)])
user_73907380

Here is another option. We loop through the data in each ID and filter any group that does not have any data 2015 or later.

library(tidyverse)

df %>%
  nest(-ID) %>%
  filter(map_dbl(data, ~length(which(.x$year >= 2015))) > 0) %>%
  unnest
#> # A tibble: 4 x 3
#>      ID  year diesese
#>   <int> <int>   <int>
#> 1     1  2012       3
#> 2     1  2016       4
#> 3     3  2013       3
#> 4     3  2015       4

Created on 2018-09-21 by the reprex package (v0.2.0).

user_97785130
Related