How to update multiple entries in a single column in a loop

user_13940325 0
Viewed 49

I have two data frames, one is having the regex pattern to search the matching month from another.

txn<-data.frame(S.No.=c(1,2),NARRATION=c("NET TXN: KBDEC17 SALARY DEC ","NET TXN: KBJAN19 SALARY"))

date_pattern<-data.frame(CODE=c("MONTH25","MONTH34"),
                         FORMAT=c("MmmYY","Mmm"),
                         ACTIVE=c("Y","Y"),
                         SEQNO=c(71,76),
                         REGEXPAT=c("(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)[1][6-9](?=[[:punct:]]|[[:space:]]|[[:alpha:]])"," (?<=[[:punct:]]|[[:space:]])(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)(?=[[:punct:]]|[[:space:]])"))

Now, when I run the loop, it substitutes the matching month with "$", but in some cases I found more than 1 matching values in a string

date_pattern$SEQNO=as.numeric(date_pattern$SEQNO)
date_pattern <-  date_pattern[order(date_pattern$SEQNO),]

txn$MONTH<-NA

for (i in 1:length(date_pattern$REGEXPAT)) {
  pat <-  date_pattern$REGEXPAT[i]
  codetype <-  date_pattern$CODE[i]
  formattype <-  date_pattern$FORMAT[i]
  seq<-date_pattern$SEQNO[i]
  if(grepl("MONTH",codetype,ignore.case = T,perl = T)) {
    month_temp<-txn$MONTH
    result <- txn[grep(pat,ignore.case = T,txn$NARRATION,perl = T),]
    var  <-  as.character("MONTH")
    result <- result[is.na(result$MONTH),]
    res <- StrExtract(result$NARRATION,pat,ignore.case = T, perl = T)
    common <- intersect(txn$S.No.,result$S.No.)
    txn[common,][var] <- as.character(res)
    txn$NARRATION <- gsub(pat," $ ",txn$NARRATION, perl = T,ignore.case = T)
    txn$NARRATION <-  gsub("\\s+", " ", txn$NARRATION)
    print(paste(txn$NARRATION,pat,i,codetype,seq,sep = "--"))
  } else {print("Wrong Choice !!!!!!!") }
}

txn[is.na(txn)] <- ""

txn

Now, the output I am getting is :

   S.No.               NARRATION MONTH  
1     1 NET TXN: KB $ SALARY $  DEC17  
2     2    NET TXN: KB $ SALARY JAN19

But, I need output like :

   S.No.               NARRATION MONTH  
1     1 NET TXN: KB $ SALARY $  DEC17,DEC  
2     2    NET TXN: KB $ SALARY JAN19
r regex
1 Answers

Try this approach :

library(dplyr)
library(stringr)

tidyr::crossing(txn, date_pattern) %>%
  mutate(MONTH = trimws(str_extract(NARRATION, REGEXPAT))) %>%
  group_by(S.No.) %>% 
  summarise(NARRATION = str_replace_all(first(NARRATION), 
                        paste0(MONTH, collapse = '|'), '$'),
            MONTH = toString(na.omit(MONTH)))

#  S.No. NARRATION                MONTH     
#  <dbl> <chr>                    <chr>     
#1     1 "NET TXN: KB$ SALARY $ " DEC17, DEC
#2     2 "NET TXN: KB$ SALARY"    JAN19

We check for REGEXPAT pattern in every value of NARRATION and extract using str_extract. Using str_replace_all we replace the extracted pattern with $.

user_39629140
Related