How to extract specific digit from pandas column using regex?-CodePudding

Given the following column in pandas dataframe:

Name: Hockey Canada; NAICS: 711211

Name: Hockey Canada; NAICS: 711211

Name: International AIDS Society; NAICS: 813212

Name: Rogers Communications Inc; NAICS: 517112, 551112; Name: Hockey Canada; NAICS: 711211

Name: Health Benefits Trust; NAICS: 524114; Name: Hockey Canada; NAICS: 711211; Name: National Equity Fund; NAICS: 523999, 531110

I'd like to extract the NAICS code from each row (where they exist) in the pandas column. The desired result is indicated in column "expected_result".

711211
711211
813212

517112; 551112; 711211

524114; 711211; 523999; 531110

I have NaN in some rows please any suggestion using regex and python will be very helpful. I tried the regex findall function but I got an error.

I write this function:

def find_number(text):
    num = re.findall(r'[0-9] ',text)
    return " ".join(num)

I used it in apply function like :

df['NAICS']=df['Company'].apply(lambda x: find_number(x))

I got this error:

KeyError Traceback (most recent call last) Input In [81], in <cell line: 1>() ----> 1 df['NAICS']=df['Company'].apply(lambda x: find_number(x))

CodePudding user response：

You can use

df['expected_result'] = df['organization'].astype(str).str.findall(r'\bNAICS:\s*(\d (?:\s*,\s*\d )*)').str.join(' ').str.findall(r'\d ').str.join("; ")

Details:

.str.findall(r'\bNAICS:\s*(\d (?:\s*,\s*\d )*)') - find all comma separated numbers after NAICS:
.str.join(' ') - joins the found matches with a space
.str.findall(r'\d ') - extracts numbers separately
.str.join("; ") - joins them with ; and space.

See a Pandas test:

import pandas as pd
df = pd.DataFrame({'organization':['NAICS: 12342; NAICS: 55555, 66667', 'NAICS:9999']})
df['expected_result'] = df['organization'].astype(str).str.findall(r'\bNAICS:\s*(\d (?:\s*,\s*\d )*)').str.join(' ').str.findall(r'\d ').str.join("; ")

Output:

>>> df
                        organization      expected_result
0  NAICS: 12342; NAICS: 55555, 66667  12342; 55555; 66667
1                         NAICS:9999                 9999

CodePudding user response：

There's likely some code golfy or more dataframe-friendly way to pull this off, but the overall logic will look something like:

import pandas as pd
import re

NAICSdf = pd.DataFrame(['Name: Hockey Canada; NAICS: 711211','Name: Hockey Canada; NAICS: 711211','Name: International AIDS Society; NAICS: 813212','Name: Rogers Communications Inc; NAICS: 517112, 551112; Name: Hockey Canada; NAICS: 711211','Name: Health Benefits Trust; NAICS: 524114; Name: Hockey Canada; NAICS: 711211; Name: National Equity Fund; NAICS: 523999, 531110'], columns=['organization'], )

def findNAICS(organization):
    NAICSList = []
    for found in re.findall(r'NAICS:\s[0-9, ]*', organization):
        for NAICS in found.split(': ')[1].split(', '):
            NAICSList.append(NAICS)
    return '; '.join(NAICSList)

NAICSdf['NAICS'] = NAICSdf['organization'].apply(findNAICS)
print(NAICSdf)

That will create a new column in your dataframe with a semicolon delimited list of NAICS codes from your string.

CodePudding user response：

If you wish to sort this by regex then you can do this: It simply looks for the recurrence of 6 digits combined together. As it seems like there are some cases of NAICS having multiple records in a row i didn't go more precise. That might cause some inaccuracy if the data involves other records with 6 digit groupings.

str1 = 'Name: Hockey Canada; NAICS: 711211'
str2 = 'Name: Rogers Communications Inc; NAICS: 517112, 551112; Name: Hockey Canada; NAICS: 711211'

data = [str1, str2]
results = [re.findall('\d{6}', entry) for entry in data]

print(results)

Ouput:

[['711211'], ['517112', '551112', '711211']]

You might also want to change the delimiter if needed, depending on how you intend on processing the data before entering it into the records. And the list stores a list of hits per row so this can be sorted as you see fit.