CODEWITHSUNDEEP
rdplyr
jeetkamal
jeetkamal

Reputation: 409

Keeping only common rows in all groups

I have a dataset that contains ten groups. Some observations (rows) are missing from some groups. I want to keep only those observations that are common in each group. I try to make a minimal example. In that example, I have made three groups. In the first group, one observation is missing. Therefore output should me in each group there will be two observations.

library(tidyverse)
## data_set
test_df<-data.frame(groups=c(1,1,1,2,2,2,3,3,3),date=as.Date(c("2000-01-01","2000-01-02","2000-01-03","2000-01-01","2000-01-02","2000-01-03","2000-01-01","2000-01-02","2000-01-03")),data=c(1,2,NA,3,4,5,6,7,8))

## required_output
## keeping data only with common dates
test_df_new<-test_df[c(1,2,4,5,7,8),]   

## groups 
test_df_new<-test_df%>%
        group_by()%>%

Upvotes: 0

Views: 91

Answers (2)

pdrhgch
pdrhgch

Reputation: 61

First, I found the dates with NA in data column:

test_df$date[is.na(test_df$data)]

Then I filtered through dplyr:

test_df %>% filter(date != test_df$date[is.na(test_df$data)])

Upvotes: 1

zx8754
zx8754

Reputation: 56259

Remove dates where data is NA, then get intersect of remaining dates over groups, then filter:

ix <- which(!is.na(test_df$data))
test_df[ test_df$date %in% 
           Reduce(intersect,
                  split(test_df$date[ ix ], test_df$groups[ ix ])), ]
#   groups       date data
# 1      1 2000-01-01    1
# 2      1 2000-01-02    2
# 4      2 2000-01-01    3
# 5      2 2000-01-02    4
# 7      3 2000-01-01    6
# 8      3 2000-01-02    7

Upvotes: 0

Related Questions