Need to fix regex statement to get correct information from excel file names

user_8359315 0
Viewed 29

I have a list of excel files whose names are formatted similarly. I need to use the information in their names as columns in a pandas Dataframe. I'm not too familiar with regex but I used google and stack overflow to figure out how to do what I needed. However, there are a few edge cases that I need help figuring out.

This is a list of names of the first 40 files I have and it can help demonstrate the challenges that I'm facing:

Maker Month Wise Data  of VADAKARA RTO - KL18 , Kerala (2020).xlsx
Maker Month Wise Data  of KATHUA RTO - JK8 , Jammu & Kashmir (2020).xlsx
Maker Month Wise Data  of KANCHEEPURAM RTO - TN21 , Tamil Nadu (2020).xlsx
Maker Month Wise Data  of KANJIRAPPALLY SRTO - KL34 , Kerala (2020).xlsx
Maker Month Wise Data  of PATHANKOT SDM - PB35 , Punjab (2020).xlsx
Maker Month Wise Data  of Chiplun Chiplun Track - MH202 , Maharashtra (2020).xlsx
Maker Month Wise Data  of ZUNHEBOTO DTO - NL6 , Nagaland (2020).xlsx
Maker Month Wise Data  of MAJITHA SDM - PB81 , Punjab (2020).xlsx
Maker Month Wise Data  of Adinath Fitness Center - RJ260 , Rajasthan (2020).xlsx
Maker Month Wise Data  of MUMBAI (EAST) - MH3 , Maharashtra (2020).xlsx
Maker Month Wise Data  of CHIDAMBARAM RTO - TN544 , Tamil Nadu (2020).xlsx
Maker Month Wise Data  of PUDUCHERRY - PY1 , Puducherry (2020).xlsx
Maker Month Wise Data  of RANIPET RTO - TN73 , Tamil Nadu (2020).xlsx
Maker Month Wise Data  of RTA, HISAR - HR39 , Haryana (2020).xlsx
Maker Month Wise Data  of AIZAWL RURAL DTO - MZ9 , Mizoram (2020).xlsx
Maker Month Wise Data  of ANANDPUR SAHIB SDM - PB16 , Punjab (2020).xlsx
Maker Month Wise Data  of PEN (RAIGAD) - MH6 , Maharashtra (2020).xlsx
Maker Month Wise Data  of PEHOWA - HR41 , Haryana (2020).xlsx
Maker Month Wise Data  of AKOLA - MH30 , Maharashtra (2020).xlsx
Maker Month Wise Data  of CANACONA RTO - GA10 , Goa (2020).xlsx
Maker Month Wise Data  of Hooghly RTO - WB15 , West Bengal (2020).xlsx
Maker Month Wise Data  of DEVIKULAM SRTO - KL68 , Kerala (2020).xlsx
Maker Month Wise Data  of KUTTANADU SRTO - KL66 , Kerala (2020).xlsx
Maker Month Wise Data  of CHENNAI (NORTH-EAST) RTO - TN3 , Tamil Nadu (2020).xlsx
Maker Month Wise Data  of RLA SHILLAI - HP85 , Himachal Pradesh (2020).xlsx
Maker Month Wise Data  of Baloda Bazar DTO - CG22 , Chhattisgarh (2020).xlsx
Maker Month Wise Data  of TC OFFICE - STA OFFICE - KL99 , Kerala (2020).xlsx
Maker Month Wise Data  of NANDURBAR - MH39 , Maharashtra (2020).xlsx
Maker Month Wise Data  of KHETRI DTO - RJ53 , Rajasthan (2020).xlsx
Maker Month Wise Data  of AHMEDGARH SDM - PB82 , Punjab (2020).xlsx
Maker Month Wise Data  of Alipurduar RTO - WB69 , West Bengal (2020).xlsx
Maker Month Wise Data  of RLA GOHAR - HP32 , Himachal Pradesh (2020).xlsx
Maker Month Wise Data  of KOLHAPUR - MH9 , Maharashtra (2020).xlsx
Maker Month Wise Data  of SILVASSA - DD1 , UT of DNH and DD (2020).xlsx
Maker Month Wise Data  of MANNARGHAT SRTO - KL50 , Kerala (2020).xlsx
Maker Month Wise Data  of SRIVILLIPUTHUR RTO - TN605 , Tamil Nadu (2020).xlsx
Maker Month Wise Data  of ZONAL OFFICE, SOUTH WEST DELHI,DWARKA - DL9 , Delhi (2020).xlsx
Maker Month Wise Data  of BUDGAM ARTO - JK4 , Jammu & Kashmir (2020).xlsx
Maker Month Wise Data  of Kolar  RTO - KA7 , Karnataka (2020).xlsx
Maker Month Wise Data  of Singtam, East Sikkim - SK8 , Sikkim (2020).xlsx

This is the snippet of code that uses regex to extract info from these file names:

# Add RTO column - WORKS
rto = re.search('\s\sof\s(.*)\s\-', file_name)
df['RTO'] = rto.group(1)

# Add registration number column - NEEDS TO BE CORRECTED - See match 27
registration_number = re.search('\s\-(.*)\s\,', file_name)
df['Registration Number'] = registration_number.group(1)

# Add state column - NEEDS TO BE CORRECTED - See match 14, 34, 37
state = re.search('\,\s(.*)\s\(', file_name)
df['State'] = state.group(1)

# Add year column - NEEDS TO BE CORRECTED - See match 10, 17, 24, 
year = re.search('\((.*)\)', file_name)
df['Year'] = year.group(1)

The regex for RTO seems to work correctly but there are certain edge cases that need to be fixed for registration number, state, and year. I've highlighted the regex error lines in the code comments. Please let me know if there is any additional input that I can provide.

I would appreciate any help I can get in fixing this!

regex
1 Answers

I think you can revamp your previous solution a bit to

pattern = r'\s+of\s+(.*?)\s+-\s+(.*?)\s+,\s+(.*?)\s+\((\d{4})\)'
df[['RTO', 'Registration Number', 'State','Year']] = df['Maker'].str.extract(pattern, expand=True)

See the regex demo.

If the registration number can only contain uppercase letters and digits, you may replace (.*?) with ([A-Z0-9]+) and use \s+of\s+(.*?)\s+-\s+([A-Z0-9]*)\s+,\s+(.*?)\s+\((\d{4})\).

Details:

  • \s+ - one or more whitespaces
  • of - a word of
  • \s+ one or more whitspaces
  • (.*?) - Group 1: any zero or more chars other than line break chars as few as possible
  • \s+,\s+ - a comma enclosed with 1+ whitespaces
  • (.*?) - Group 2: any zero or more chars other than line break chars as few as possible
  • \s+,\s+ - a comma enclosed with 1+ whitespaces
  • (.*?) - Group 3: any zero or more chars other than line break chars as few as possible (or, if [A-Z0-9]+ is used, any one or more uppercase ASCII letters or digits)
  • \s+ - 1+ whitespaces
  • \( - a ( char
  • (\d{4}) - Group 3: four digits
  • \) - a ) char.
user_38329700
Related