So I have a data frame that looks like this:
"date","id_station","id_parameter","valor","unit","year","day","month","hour","zona"
2019-01-01 00:00:00,"AJM","CO",NA,15,2019,1,1,0,"SO"
2019-01-01 00:00:00,"ATI","CO",NA,15,2019,1,1,0,"NO"
2019-01-01 00:00:00,"BJU","CO",NA,15,2019,1,1,0,"CE"
2019-01-01 00:00:00,"CAM","CO",NA,15,2019,1,1,0,"NO"
2019-01-01 00:00:00,"CCA","CO",NA,15,2019,1,1,0,"SO"
2019-01-01 00:00:00,"CHO","CO",NA,15,2019,1,1,0,"SE"
2019-01-01 00:00:00,"CUA","CO",NA,15,2019,1,1,0,"SO"
2019-01-01 00:00:00,"FAC","CO",NA,15,2019,1,1,0,"NO"
2019-01-01 00:00:00,"HGM","CO",NA,15,2019,1,1,0,"CE"
2019-01-01 00:00:00,"IZT","CO",NA,15,2019,1,1,0,"CE"
2019-01-01 00:00:00,"LLA","CO",NA,15,2019,1,1,0,"NE"
2019-01-01 00:00:00,"LPR","CO",NA,15,2019,1,1,0,"NE"
2019-01-01 00:00:00,"MER","CO",NA,15,2019,1,1,0,"CE"
2019-01-01 00:00:00,"MGH","CO",NA,15,2019,1,1,0,"SO"
2019-01-01 00:00:00,"NEZ","CO",NA,15,2019,1,1,0,"NE"
2019-01-01 00:00:00,"PED","CO",NA,15,2019,1,1,0,"SO"
2019-01-01 00:00:00,"SAG","CO",NA,15,2019,1,1,0,"NE"
2019-01-01 00:00:00,"SFE","CO",NA,15,2019,1,1,0,"SO"
2019-01-01 00:00:00,"SJA","CO",NA,15,2019,1,1,0,"NO"
2019-01-01 00:00:00,"TAH","CO",NA,15,2019,1,1,0,"SE"
2019-01-01 00:00:00,"TLA","CO",NA,15,2019,1,1,0,"NO"
2019-01-01 00:00:00,"TLI","CO",NA,15,2019,1,1,0,"NO"
2019-01-01 00:00:00,"UAX","CO",NA,15,2019,1,1,0,"SE"
2019-01-01 00:00:00,"UIZ","CO",NA,15,2019,1,1,0,"SE"
2019-01-01 00:00:00,"VIF","CO",NA,15,2019,1,1,0,"NE"
2019-01-01 00:00:00,"XAL","CO",NA,15,2019,1,1,0,"NE"
2019-01-01 01:00:00,"AJM","CO",NA,15,2019,1,1,1,"SO"
2019-01-01 01:00:00,"ATI","CO",NA,15,2019,1,1,1,"NO"
2019-01-01 01:00:00,"BJU","CO",NA,15,2019,1,1,1,"CE"
2019-01-01 01:00:00,"CAM","CO",NA,15,2019,1,1,1,"NO"
2019-01-01 01:00:00,"CCA","CO",NA,15,2019,1,1,1,"SO"
2019-01-01 01:00:00,"CHO","CO",NA,15,2019,1,1,1,"SE"
2019-01-01 01:00:00,"CUA","CO",NA,15,2019,1,1,1,"SO"
2019-01-01 01:00:00,"FAC","CO",NA,15,2019,1,1,1,"NO"
2019-01-01 01:00:00,"HGM","CO",NA,15,2019,1,1,1,"CE"
2019-01-01 01:00:00,"IZT","CO",NA,15,2019,1,1,1,"CE"
2019-01-01 01:00:00,"LLA","CO",NA,15,2019,1,1,1,"NE"
2019-01-01 01:00:00,"LPR","CO",NA,15,2019,1,1,1,"NE"
2019-01-01 01:00:00,"MER","CO",NA,15,2019,1,1,1,"CE"
2019-01-01 01:00:00,"MGH","CO",NA,15,2019,1,1,1,"SO"
2019-01-01 01:00:00,"NEZ","CO",NA,15,2019,1,1,1,"NE"
2019-01-01 01:00:00,"PED","CO",NA,15,2019,1,1,1,"SO"
2019-01-01 01:00:00,"SAG","CO",NA,15,2019,1,1,1,"NE"
2019-01-01 01:00:00,"SFE","CO",NA,15,2019,1,1,1,"SO"
2019-01-01 01:00:00,"SJA","CO",NA,15,2019,1,1,1,"NO"
2019-01-01 01:00:00,"TAH","CO",NA,15,2019,1,1,1,"SE"
2019-01-01 01:00:00,"TLA","CO",NA,15,2019,1,1,1,"NO"
2019-01-01 01:00:00,"TLI","CO",NA,15,2019,1,1,1,"NO"
2019-01-01 01:00:00,"UAX","CO",NA,15,2019,1,1,1,"SE"
2019-01-01 01:00:00,"UIZ","CO",NA,15,2019,1,1,1,"SE"
2019-01-01 01:00:00,"VIF","CO",NA,15,2019,1,1,1,"NE"
2019-01-01 01:00:00,"XAL","CO",NA,15,2019,1,1,1,"NE"
And what I want to do is to group all based on id_station, id_parameter, year, day, and month. Afterwards, I want to count the number of rows that are not NA in "valor" for each day.
Finally, I want to determine how many days had at least 18 non-NA values for each day of each id_station. If there are less than 274 days, I want to delete ALL values associated to that id_station
How can I do this?
After grouping by the columns of interest, get the sum of logical vector as the count i.e. - is.na(valor) returns a logical vector with TRUE where there are NA and FALSE for non-NA, negate (!) to reverse it and get the sum of the logical such as each TRUE (-> 1) represents one non-NA element
library(dplyr)
df1 %>%
group_by(id_station, id_parameter, year, day, month) %>%
summarise(Count = sum(!is.na(valor)))