extract a pattern from a text in R from a subset of patterns

Question

I have list of codes as below

ccode<-c('S','PD','CH','ML','MD','VA','BVI','DB','KD','KE','PW','COL','AD','MET','VP','SI','VR','GAO','LK','RP','PAD','WAN','PWD','PMP','PBR','VN','PPC','NK','K','AH','I','JP','JU','UDZ','CHM','DDN','LN','CL','CLH','DKM','GK','WD','ED','DDK','DLN','DRN','DFD','GZB','DVV','GUR','GGN','ND','HHN','HAS','HYD','HKP','BWF','BBW','BKM','BSN','BL','BIN','ST','KN')

Now, I want to extract a string from below sample that starts with the code

consolidated_csv_v2 <- c("pt paid rs-8488/-  remaining amt","Credit Card Sales","ML 2926 VARSHA LAKHANI (AG)","IMRAN KHAN-PW-4798","Deepali Mishra Ah-5564 Tst", "MANJU S-11226 T","SNEHA S-16191","SUMIT SETHI AH-5747 AG","SUJATA VORA AH-5361 AG","Deepali Mishra Ah-5564 Tst")

data is spread across 477326 rows

Expected output is code followed by number.

str_extract(consolidated_csv_v2, "AH.*$")

[1] NA           NA           NA           NA           NA           NA          
[7] NA           "AH-5747 AG" "AH-5361 AG" NA  AG"

This formula worked only on static code "AH". How can I do the same for match with any of codes in ccode.

How can you have code 'S' and code 'SI'? So strings beginning with SA for example? — Sotos, Apr 15 '16 at 08:05
@Sotos Yeah I know that is a challenge! At least I can figure out for other codes right? — Shankar Pandala, Apr 15 '16 at 08:08
Perhaps use the pattern `str_extract(consolidated_csv_v2[,4],paste0("^(", paste(ccode, collapse="|"),").*"))` — akrun, Apr 15 '16 at 08:09
Not a challenge... You have conditions that contradict each other, so you cannot satisfy them — Sotos, Apr 15 '16 at 08:10
Also, 'CH' is a code. Strings beginning with 'Ch' or 'ch' are selected? — Sotos, Apr 15 '16 at 08:13
@Sotos Yes, But that is the requirement. No other exact pattern for words starts with the code and ends with a numeric. There can be a hypen, space or noting between the code and number — Shankar Pandala, Apr 15 '16 at 09:32

akrun · Answer 1 · 2016-04-15T08:20:57.077

2

We can try

pat <- paste0("(?i)\\b(", paste(ccode, collapse="|"),")-.*")
str_extract(v1, pat)
#[1] NA            NA            NA            NA            "Ah-5564 Tst" NA            "AH-2445 AG"  "AH-5747 AG"  "AH-5361 AG"  "Ah-5564 Tst"

data

v1 <- c("Head Office", "(cancelled)", "(cancelled)", "(cancelled)", 
"Deepali Mishra Ah-5564 Tst", "(cancelled)", "SHRUTI BHAGAT AH-2445 AG", 
"SUMIT SETHI AH-5747 AG", "SUJATA VORA AH-5361 AG", "Deepali Mishra Ah-5564 Tst")

edited Apr 15 '16 at 08:20

answered Apr 15 '16 at 08:14

akrun

874,273
37
540
662

Thanks for your help! – Shankar Pandala Apr 15 '16 at 09:29

Wiktor Stribiżew · Accepted Answer · 2016-04-15T12:20:41.317

I assume you need to extract the substrings starting with a "code" after a word boundary and followed with a hyphen.

Then, use

 "\\b(?:S|PD|CH|ML|MD|VA|BVI|DB|KD|KE|PW|COL|AD|MET|VP|SI|VR|GAO|LK|RP|PAD|WAN|PWD|PMP|PBR|VN|PPC|NK|K|AH|I|JP|JU|UDZ|CHM|DDN|LN|CL|CLH|DKM|GK|WD|ED|DDK|DLN|DRN|DFD|GZB|DVV|GUR|GGN|ND|HHN|HAS|HYD|HKP|BWF|BBW|BKM|BSN|BL|BIN|ST|KN)-\\w*"

where \b stands for a word boundary, then a group of code alternatives follows ((?:...)), and then a hyphen (-) followed with zero or more alphanumeric/underscore symbols (\w*).

And here is a demo code:

> consolidated_csv_v2 <- c("Head Office","(cancelled)","(cancelled)","(cancelled)","Deepali Mishra Ah-5564 Tst", "(cancelled)","SHRUTI BHAGAT AH-2445 AG","SUMIT SETHI AH-5747 AG","SUJATA VORA AH-5361 AG","Deepali Mishra Ah-5564 Tst")
> ccode<-c('S','PD','CH','ML','MD','VA','BVI','DB','KD','KE','PW','COL','AD','MET','VP','SI','VR','GAO','LK','RP','PAD','WAN','PWD','PMP','PBR','VN','PPC','NK','K','AH','I','JP','JU','UDZ','CHM','DDN','LN','CL','CLH','DKM','GK','WD','ED','DDK','DLN','DRN','DFD','GZB','DVV','GUR','GGN','ND','HHN','HAS','HYD','HKP','BWF','BBW','BKM','BSN','BL','BIN','ST','KN')
> reg <- paste0("\\b(?:", paste(ccode, collapse="|"),")-\\w*")
> str_extract(consolidated_csv_v2, reg)
 [1] NA        NA        NA        NA        NA        NA        "AH-2445"
 [8] "AH-5747" "AH-5361" NA       
>

UPDATE

not all the words are followed by '-', some are follwed by a space and some don't have any character in between.

The requirement is rather a general one, but we can meet it using a lazy dot matching (.*?) after the group of alternations to match any 0+ characters other than a newline as few as possible up to the first set of digits (\d+) that are followed with a word boundary (\b). Use

reg <- paste0("(?i)\\b(?:", paste(ccode, collapse="|"),").*?\\d+\\b")

See the regex demo

To make this pattern case-insensitive, just add a (?i) in front of the first \b.

Thank you so much. not all the words are followed by '-', some are follwed by a space and some don't have any character in between. but all ends with a number only. It would be helpful if you can provide solution for that too. And, any help on how to deal with the single characters like 'S' etc,. — Shankar Pandala, Apr 15 '16 at 09:29
Try `reg <- paste0("\\b(?:", paste(ccode, collapse="|"),").*?\\d+\\b")` — Wiktor Stribiżew, Apr 15 '16 at 09:31
I do not like `.*?` very much, but your *some are followed by a space and some don't have any character in between* sounds not precise. Please check the actual requirements and update the question so that we could help you with the safest pattern. — Wiktor Stribiżew, Apr 15 '16 at 09:34
Thank you So much for your efforts again. I've updated the test set that match all my requirements. — Shankar Pandala, Apr 15 '16 at 09:51
Done! Thank you so much! This is case sensitive. How to make it not case sensitive? — Shankar Pandala, Apr 15 '16 at 10:51
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/109244/discussion-between-shankar-pandala-and-wiktor-stribizew). — Shankar Pandala, Apr 15 '16 at 12:16

extract a pattern from a text in R from a subset of patterns

2 Answers2

data