vendredi 10 avril 2020

Multiple conditional Filter keeping first row intact

I have a data frame that I've been filtering down. It's collection of transactions and each participant has a unique identifier (CIK Number), which is contained in a column of the data frame. I've also calculated the amount of time (date index is a different column) that has transpired between the most recent transaction for a given ID and the prior ones.

I'm struggling with either using dplyr to filter out the additional continues or use an ifelse formula:

Ideally, I would like to keep the entire series related to the transaction identifier IF the time elapsed column shows an offsetting transaction occurring in in less than 90 days and the transaction is the in the same issuer as the most recent one. The types of transactions are P-Purchase and S-Sale so either a buy and then sell in less than 90 days or a sale and then buy in less than 90 days.

test <- InsiderList3 %>%
  group_by(`Insider CIK`) %>%
  mutate(rf.diff =  first(`Transaction Date`)-`Transaction Date`)

colnames(test)[15] <- "Days from Most recent filing"

test2 <- test %>%
  group_by(`Insider CIK`) %>%
  filter(first(`Security Name`)== 'Common Stock')

test3 <-  test2 %>% group_by(`Insider CIK`) %>% filter(`Days from Most recent filing`<=90 & `Issuer==first(Issuer))`

A sample of the underlying data is below:

Colnames

[1] "Insider CIK"                     "Insider Full Name and CIK"       "Acquistion or Disposition"       "Transaction Date"               
 [5] "Deemed Execution Date"           "Issuer"                          "Form"                            "Transaction Type"               
 [9] "Direct or Indirect Ownership"    "Number of Securities Transacted" "Number of Securities Owned"      "Line Number"                    
[13] "Issuer CIK"                      "Security Name"                   "Days from Most recent filing"   

Format of data:

         Insider CIK       Insider Full Name and CIK       Acquistion or Disposition                Transaction Date           Deemed Execution Date 
            "character"                       "numeric"                       "numeric"                       "numeric"                       "numeric" 
                 Issuer                            Form                Transaction Type    Direct or Indirect Ownership Number of Securities Transacted 
              "numeric"                       "numeric"                     "character"                       "numeric"                       "numeric" 

Number of Securities Owned Line Number Issuer CIK Security Name Days from Most recent filing "numeric" "numeric" "numeric" "numeric" "numeric"

I would greatly appreciate any help to get this accomplished.

Aucun commentaire:

Enregistrer un commentaire