jeudi 9 avril 2020

Filtering Data Frame by Multiple Criteria Based on Separate Index Vector

I have a data frame with multiple column fields of data one of which is an index variable. It's essentially multiple time-series of transactions related to a particular unique identifier (the separate index vector). I'd like to filter this data frame provided the following criteria are met:

Time elapsed (in days) between the first entry for the corresponding index (top being most recent date) being less than 90 days and the transaction type being either a P-Purchase if the first entry is an S-Sale or vice versa (if Sale and then Purchase).

I'm not sure if I should use an If else statement or dplyr's case.when method so I'm struggling with how to solve this.

Here is a sample of the scripts of my work (which is generating an error) using either if else or dplyr:

filt_data <- InsiderList3 %>%    
  filter(
    if (`Insider CIK` == ciknumbers2) {
      head(InsiderList3$`Transaction Date`,1)-InsiderList3$`Transaction Date`<90 & head(InsiderList3$`Transaction Type`,1) != InsiderList3$`Transaction Type`
    } else {

    }
  ) %>%
  mutate(totalrows = nrow(.)) %>%
  summarize()

InsiderList3 %>% 
  filter(case_when(`Insider CIK` == ciknumbers2, 
                   head(InsiderList3$`Transaction Date`,1)-InsiderList3$`Transaction Date`< 90,
                  head(InsiderList3$`Transaction Type`,1) != InsiderList3$`Transaction Type`
         )) %>% 
  tail(1)

The index list is: "0001337645" "0001749420" "0001658704" "0001642765" "000852412" "0001499263" "0001769077" "0001239635" "0001790576" "0001198046"

And the data frame looks like this:

enter image description here

Help constructing a solution under either method would be much appreciated.

Aucun commentaire:

Enregistrer un commentaire