Is there an R or Python function for separating information in non-delimited strings, where the information varies?

Question

I am currently cleaning up a messy data sheet in which information is given in one excel cell where the different characteristics are not delimited (no comma, spaces are random). Thus, my problem is to separate the different information without a delimitation I could use in my code (can't use a split command)

I assume that I need to include some characteristics of each part of information, such that the corresponding characteristic is recognized. However, I don't have a clue how to do that since I am quite new to Python and I only worked with R in the framework of regression models and other statistical analysis.

Short data example: INPUT:

"WMIN CBOND12/05/2022 23554132121"

or

"WalMaInCBND 12/05/2022-23554132121"

or

"WalmartI CorpBond12/05/2022|23554132121"

EXPECTED OUTPUT:

"Walmart Inc.", "Corporate Bond", "12/05/2022", "23554132121"

So each of the "x" should be classified in a new column with the corresponding header (Company, Security, Maturity, Account Number)

As you can see the input varies randomly but I want to have the same output for each of the three inputs given above (I have over 200k data points with different companies, securities etc.)

First Problem is how to separate the information effectively without being able to use a systematic pattern.

Second Problem (lower priority) is how to identify the company without setting up a dictionary with 50 different inputs for 50k companies.

Thanks for your help!

Answer 1

edit

s = c("WMIN CBOND12/05/2022 23554132121",
      "WalMaInCBND 12/05/2022-23554132121",
      "WalmartI CorpBond12/05/2022|23554132121")


ID = gsub("([a-zA-Z]+).*","\\1",s)
ID2 = gsub(".* ([a-zA-Z]+).*","\\1",s)
date = gsub("[a-zA-Z ]+(\\d+\\/\\d+\\/\\d+).*","\\1",s)
num = gsub("^.*[^0-9](.*$)","\\1",s)

data.frame(ID=ID,ID2=ID2,date=date,num=num,stringsAsFactors=FALSE)

           ID                                ID2       date         num
1        WMIN                              CBOND 12/05/2022 23554132121
2 WalMaInCBND WalMaInCBND 12/05/2022-23554132121 12/05/2022 23554132121
3    WalmartI                           CorpBond 12/05/2022 23554132121

Works for cases 1 and 3 but I haven't figured out a logic for the second case, how can we know where to split the string containing the company and security if they are not separated?

Answer 2

Using python and regular expressions:

import re

def make_filter(pattern):
    pattern = re.compile(pattern)
    def filter(s):
        filtered = pattern.match(s)
        return filtered.group(1), filtered.group(2), filtered.group(3), filtered.group(4)
    return filter

filter = make_filter("^([a-zA-Z]+)\s([a-zA-Z]+)(\d+/\d+/\d+)\s(\d+)$")

filter("WMIN CBOND12/05/2022 23554132121")

The make_filter function is just an utility to allow you to modify the pattern. It returns a function that will filter the output according to that pattern. I use it with the "^([a-zA-Z]+)\s([a-zA-Z]+)(\d+/\d+/\d+)\s(\d+)$" pattern that considers some text, an space, some text, a date, an space, and a number. If you want to kodify this pattern provide more info about it. The output will be ("WMIN", "CBOND", "12/05/2022", "23554132121").

Answer 3

I recommend to first introduce useful seperators where possible and construct a dictionary of replacements for processing with regular expressions.

import re
s = 'WMIN CBOND12/05/2022 23554132121'

# CAREFUL this not a real date regex, this should just
# illustrate the principle of regex
# see https://stackoverflow.com/a/15504877/5665958 for
# a good US date regex
date_re = re.compile('([0-9]{2}/[0-9]{2}/[0-9]{4})')

# prepend a whitespace before the date
# this is achieved by searching the date within the string
# and replacing it with itself with a prepended whitespace
# /1 means "insert the first capture group", which in our
# case is the date
s = re.sub(date_re, r' \1', s)

# split by one or more whitespaces and insert
# a seperator (';') to make working with the string
# easier
s = ';'.join(s.split())

# build a dictionary of replacements
replacements = {
    'WMIN': 'Walmart Inc.',
    'CBOND': 'Corporate Bond',
}

# for each replacement apply subsitution
# a better, but more replicated solution for
# this is given here:
# https://stackoverflow.com/a/15175239/5665958
for pattern, r in replacements.items():
    s = re.sub(pattern, r, s)

# use our custom separator to split the parts
out = s.split(';')
print(out)

Answer 4

welcome! Yeah, we would definitely need to see more examples and regex seems to be the way to go... but since there seems to be no structure, I think it's better to think of this as seperate steps.

1. We KNOW there's a date which is (X)X/(X)X/XXXX (ie. one or two digit day, one or two digit month, four digit year, maybe with or without the slashes, right?) and after that there's numbers. So solve that part first, leaving only the first two categories. That's actually the easy part :) but don't lose heart!
2. if these two categories might not have ANY delimiter (for example WMINCBOND 12/05/202223554132121, or delimiters are not always delimiters for example IMAGINARY COMPANY X CBOND, then you're in deep trouble. :) BUT this is what we can do:
   1. Gather a list of all the codes (hopefully you have that).
   2. use str_detect() on each code and see if you can recognize the exact string in any of the dataset (if you do have the codes lemme know I'll write the code to do this part).
3. What's left after identifying the code will be the CBOND, whatever that is... so do that part last... what's left of the string will be that. Alternatively, you can use the same str_detect() if you have a list of whatever CBOND stuff is.
4. ONLY AFTER YOU'VE IDENTIFIED EVERYTHING, you can then replace the codes for what they stand for. If you have the code-list let me know and I'll post the code.