Filter tibble in R if variable observation appears in all groups

user_4167503 0
Viewed 49
library(tidyverse)

dta <- tibble(
  date = c("2000-01-01", "2000-01-02", "2000-01-03", "2000-01-02","2000-01-03"),
  stock = c("A", "A", "A", "B", "B"),
  price = c("price_{A1}","price_{A2}","price_{A3}","price_{B1}","price_{B2}" )
)

The data is given in this tibble (code above):

# A tibble: 5 x 3
  date       stock price     
  <chr>      <chr> <chr>     
1 2000-01-01 A     price_{A1}
2 2000-01-02 A     price_{A2}
3 2000-01-03 A     price_{A3}
4 2000-01-02 B     price_{B1}
5 2000-01-03 B     price_{B2}

I want to exclude row 1, since date 2000-01-01 is not available for all stocks in the tibble.

dplyr filter r
4 Answers

One option could be:

library(dplyr)

dta %>%
  group_by(date) %>%
  filter(n_distinct(stock) != 1)

# A tibble: 4 x 3
# Groups:   date [2]
  date       stock price     
  <chr>      <chr> <chr>     
1 2000-01-02 A     price_{A2}
2 2000-01-03 A     price_{A3}
3 2000-01-02 B     price_{B1}
4 2000-01-03 B     price_{B2}
user_59645570

This code should get you what you want.

library(tidyverse)

dta <- tibble(
  date = c("2000-01-01", "2000-01-02", "2000-01-03", "2000-01-02","2000-01-03"),
  stock = c("A", "A", "A", "B", "B"),
  price = c("price_{A1}","price_{A2}","price_{A3}","price_{B1}","price_{B2}" )
)

dta %>% 
  pivot_wider(names_from = stock, values_from = price) %>% 
  na.omit() %>% 
  pivot_longer(cols = A:B, names_to = "stock", values_to = "price")
user_135520000

Or:

library(tidyverse)

dta <- tibble(
  date = c("2000-01-01", "2000-01-02", "2000-01-03", "2000-01-02","2000-01-03"),
  stock = c("A", "A", "A", "B", "B"),
  price = c("price_{A1}","price_{A2}","price_{A3}","price_{B1}","price_{B2}" )
)

dta %>%
  filter(date %in% reduce(split(.$date, .$stock), intersect))

# date       stock price     
 <chr>      <chr> <chr>     
# 1 2000-01-02 A     price_{A2}
# 2 2000-01-03 A     price_{A3}
# 3 2000-01-02 B     price_{B1}
# 4 2000-01-03 B     price_{B2}
user_61620110

An option with data.table

library(data.table)
setDT(dta)[, .SD[uniqueN(stock) !=1], date]
#         date stock      price
#1: 2000-01-02     A price_{A2}
#2: 2000-01-02     B price_{B1}
#3: 2000-01-03     A price_{A3}
#4: 2000-01-03     B price_{B2}

Or with base R

dta[with(dta, ave(stock, date, FUN = function(x) length(unique(x))) !=1),]
user_37322710
Related