import pandas as pd import numpy as np import re from typing import Type, Dict, List, Tuple from datetime import datetime import warnings warnings.filterwarnings("ignore", 'This pattern is interpreted as a regular expression') PandasDataFrame = Type[pd.DataFrame] PandasSeries = Type[pd.Series] MatchedResults = Dict[str,Tuple[str,int]] array = List[str] today = datetime.now().strftime("%d%m%Y") today_rev = datetime.now().strftime("%Y%m%d") # # Standardisation functions def standardise_wrapper_func(search_df_cleaned:PandasDataFrame, ref_df_cleaned:PandasDataFrame,\ standardise = False, filter_to_lambeth_pcodes = True, match_task = "fuzzy"): ''' Initial standardisation of search and reference dataframes before passing addresses and postcodes to the main standardisation function ''' ## Search df - lower case addresses, replace spaces in postcode search_df_cleaned["full_address_search"] = search_df_cleaned["full_address"].str.lower().str.strip() search_df_cleaned['postcode_search'] = search_df_cleaned['postcode'].str.lower().str.strip().str.replace("\s+", "", regex=True) # Filter out records where 'Excluded from search' is not a postal address by making the postcode blank search_df_cleaned.loc[search_df_cleaned['Excluded from search'] == "Excluded - non-postal address", 'postcode_search'] = "" # Remove nulls from ref_df postcode ref_df_cleaned = ref_df_cleaned[ref_df_cleaned['Postcode'].notna()] ref_df_cleaned["full_address_search"] = ref_df_cleaned["fulladdress"].str.lower().str.strip() ref_df_cleaned['postcode_search'] = ref_df_cleaned['Postcode'].str.lower().str.strip().str.replace("\s+", "", regex=True) # Block only on first 5 characters of postcode string - Doesn't give more matches and makes everything a bit slower #search_df_cleaned['postcode_search'] = search_df_cleaned['postcode_search'].str[:-1] #ref_df_cleaned['postcode_search'] = ref_df_cleaned['postcode_search'].str[:-1] ### Use standardise function ### Remove 'non-housing' places from the list - not included as want to check all #search_df_after_stand = remove_non_housing(search_df_cleaned, 'full_address_search') search_df_after_stand = standardise_address(search_df_cleaned, "full_address_search", "search_address_stand", standardise = standardise, out_london = True) ## Standardise ref_df addresses if match_task == "fuzzy": ref_df_after_stand = standardise_address(ref_df_cleaned, "full_address_search", "ref_address_stand", standardise = standardise, out_london = True) else: # For the neural net matching, I didn't find that standardising the reference addresses helped at all, in fact it made things worse. So reference addresses are not standardised at this step. ref_df_after_stand = standardise_address(ref_df_cleaned, "full_address_search", "ref_address_stand", standardise = False, out_london = True) return search_df_after_stand, ref_df_after_stand#, search_df_after_stand_series, ref_df_after_stand_series def standardise_address(df:PandasDataFrame, col:str, out_col:str, standardise:bool = True, out_london = True) -> PandasDataFrame: ''' This function takes a 'full address' column and then standardises so that extraneous information is removed (i.e. postcodes & London, as this algorithm is used for London addresses only), and so that room/flat/property numbers can be accurately extracted. The standardised addresses can then be used for the fuzzy matching functions later in this notebook. The function does the following: - Removes the post code and 'london' (if not dealing with addresses outside of london) from the address to reduce the text the algorithm has to search. Postcode removal uses regex to extract a UK postcode. - Remove the word 'flat' or 'apartment' from an address that has only one number in it - Add 'flat' to the start of any address that contains 'house' or 'court' (which are generally housing association buildings) This is because in the housing list, these addresses never have the word flat in front of them - Replace any addresses that don't have a space between the comma and the next word or double spaces - Replace 'number / number' and 'number-number' with 'number' (the first number in pair) - Add 'flat' to the start of addresses that include ground floor/first floor etc. flat in the text. Replace with flat a,b,c etc. - Pull out property, flat, and room numbers from the address text - Return the data frame with the new columns included ''' df_copy = df.copy(deep=True) # Trim the address to remove leading and tailing spaces df_copy[col] = df_copy[col].str.strip() ''' Remove the post code and 'london' from the address to reduce the text the algorithm has to search Using a regex to extract a UK postcode. I got the regex from the following. Need to replace their \b in the solution with \\b https://stackoverflow.com/questions/51828712/r-regular-expression-for-extracting-uk-postcode-from-an-address-is-not-ordered The following will pick up whole postcodes, postcodes with just the first part, and postcodes with the first part and the first number of the second half ''' df_copy['add_no_pcode'] = remove_postcode(df_copy, col) if out_london == False: df_copy['add_no_pcode'] = df_copy['add_no_pcode'].str.replace("london","").str.replace(r",,|, ,","", regex=True) # If the user wants to standardise the address if standardise: df_copy['add_no_pcode'] = df_copy['add_no_pcode'].str.lower() # If there are dates at the start of the address, change this df_copy['add_no_pcode'] = replace_mistaken_dates(df_copy, 'add_no_pcode') # Replace flat name variations with flat, abbreviations with full name of item (e.g. rd to road) df_copy['add_no_pcode'] = df_copy['add_no_pcode'].str.replace(r"\brd\b","road", regex=True).\ str.replace(r"\bst\b","street", regex=True).\ str.replace(r"\bave\b","avenue", regex=True).\ str.replace("'", "", regex=False).\ str.replace(r"\bat\b ", " ",regex=True).\ str.replace("apartment", "flat",regex=False).\ str.replace("studio flat", "flat",regex=False).\ str.replace("cluster flat", "flats",regex=False).\ str.replace(r"\bflr\b", "floor", regex=True).\ str.replace(r"\bflrs\b", "floors", regex=True).\ str.replace(r"\blwr\b", "lower", regex=True).\ str.replace(r"\bgnd\b", "ground", regex=True).\ str.replace(r"\blgnd\b", "lower ground", regex=True).\ str.replace(r"\bgrd\b", "ground", regex=True).\ str.replace(r"\bmais\b", "flat", regex=True).\ str.replace(r"\bmaisonette\b", "flat", regex=True).\ str.replace(r"\bpt\b", "penthouse", regex=True).\ str.replace(r"\bbst\b","basement", regex=True).\ str.replace(r"\bbsmt\b","basement", regex=True) df_copy["add_no_pcode_house"] = move_flat_house_court(df_copy) # Replace any addresses that don't have a space between the comma and the next word. and double spaces # df_copy['add_no_pcode_house'] df_copy['add_no_pcode_house_comma'] = df_copy['add_no_pcode_house'].str.replace(r',(\w)', r', \1', regex=True).str.replace(' ', ' ', regex=False) # Replace number / number and number-number with number df_copy['add_no_pcode_house_comma_no'] = df_copy['add_no_pcode_house_comma'].str.replace(r'(\d+)\/(\d+)', r'\1', regex=True\ ).str.replace(r'(\d+)-(\d+)', r'\1', regex=True\ ).str.replace(r'(\d+) - (\d+)', r'\1', regex=True) # Add 'flat' to the start of addresses that include ground/first/second etc. floor flat in the text df_copy['floor_replacement'] = replace_floor_flat(df_copy, 'add_no_pcode_house_comma_no') df_copy['flat_added_to_start_addresses_begin_letter'] = add_flat_addresses_start_with_letter(df_copy, 'floor_replacement') df_copy[out_col] = merge_series(df_copy['add_no_pcode_house_comma_no'], df_copy['flat_added_to_start_addresses_begin_letter']) # Write stuff back to the original df df[out_col] = df_copy[out_col] else: df_copy[out_col] = df_copy['add_no_pcode'] df[out_col] = df_copy['add_no_pcode'] ## POST STANDARDISATION CLEANING AND INFORMATION EXTRACTION # Remove trailing spaces df[out_col] = df[out_col].str.strip() # Pull out property, flat, and room numbers from the address text df['property_number'] = extract_prop_no(df_copy, out_col) # Extract flat, apartment numbers df = extract_flat_and_other_no(df, out_col) df['flat_number'] = merge_series(df['flat_number'], df['apart_number']) df['flat_number'] = merge_series(df['flat_number'], df['prop_number']) df['flat_number'] = merge_series(df['flat_number'], df['first_sec_number']) df['flat_number'] = merge_series(df['flat_number'], df['first_letter_flat_number']) df['flat_number'] = merge_series(df['flat_number'], df['first_letter_no_more_numbers']) # Extract room numbers df['room_number'] = extract_room_no(df, out_col) # Extract block and unit names df = extract_block_and_unit_name(df, out_col) # Extract house or court name df['house_court_name'] = extract_house_or_court_name(df, out_col) return df def move_flat_house_court(df:PandasDataFrame): ''' Remove 'flat' from any address that contains 'house' or 'court' From the df address, remove the word 'flat' from any address that contains the word 'house' or 'court' This is because in the housing list, these addresses never have the word flat in front of them ''' # Remove the word flat or apartment from addresses that have only one number in it. 'Flat' will be re-added later to relevant addresses # that need it (replace_floor_flat) df['flat_removed'] = remove_flat_one_number_address(df, 'add_no_pcode') remove_flat_house = df['flat_removed'].str.lower().str.contains(r"\bhouse\b")#(?=\bhouse\b)(?!.*house road)") remove_flat_court = df['flat_removed'].str.lower().str.contains(r"\bcourt\b")#(?=\bcourt\b)(?!.*court road)") remove_flat_terrace = df['flat_removed'].str.lower().str.contains(r"\bterrace\b")#(?=\bterrace\b)(?!.*terrace road)") remove_flat_house_or_court = (remove_flat_house | remove_flat_court | remove_flat_terrace == 1) df['remove_flat_house_or_court'] = remove_flat_house_or_court # Assuming 'df' is your DataFrame df = df[~df.index.duplicated(keep='first')] df['house_court_replacement'] = "flat " + df.loc[df['remove_flat_house_or_court'] == True, 'flat_removed'].str.replace(r"\bflat\b","", regex=True).str.strip().map(str) #df["add_no_pcode_house"] = merge_columns(df, "add_no_pcode_house", 'flat_removed', "house_court_replacement") #merge_columns(df, "new_col", col1, 'letter_after_number') df["add_no_pcode_house"] = merge_series(df['flat_removed'], df["house_court_replacement"]) return df["add_no_pcode_house"] def extract_street_name(address:str) -> str: """ Extracts the street name from the given address. Args: address (str): The input address string. Returns: str: The extracted street name, or an empty string if no match is found. Examples: >>> address1 = "1 Ash Park Road SE54 3HB" >>> extract_street_name(address1) 'Ash Park Road' >>> address2 = "Flat 14 1 Ash Park Road SE54 3HB" >>> extract_street_name(address2) 'Ash Park Road' >>> address3 = "123 Main Blvd" >>> extract_street_name(address3) 'Main Blvd' >>> address4 = "456 Maple AvEnUe" >>> extract_street_name(address4) 'Maple AvEnUe' >>> address5 = "789 Oak Street" >>> extract_street_name(address5) 'Oak Street' """ street_types = [ 'Street', 'St', 'Boulevard', 'Blvd', 'Highway', 'Hwy', 'Broadway', 'Freeway', 'Causeway', 'Cswy', 'Expressway', 'Way', 'Walk', 'Lane', 'Ln', 'Road', 'Rd', 'Avenue', 'Ave', 'Circle', 'Cir', 'Cove', 'Cv', 'Drive', 'Dr', 'Parkway', 'Pkwy', 'Park', 'Court', 'Ct', 'Square', 'Sq', 'Loop', 'Place', 'Pl', 'Parade', 'Estate', 'Alley', 'Arcade','Avenue', 'Ave','Bay','Bend','Brae','Byway','Close','Corner','Cove', 'Crescent', 'Cres','Cul-de-sac','Dell','Drive', 'Dr','Esplanade','Glen','Green','Grove','Heights', 'Hts', 'Mews','Parade','Path','Piazza','Promenade','Quay','Ridge','Row','Terrace', 'Ter','Track','Trail','View','Villas', 'Marsh', 'Embankment', 'Cut', 'Hill', 'Passage', 'Rise', 'Vale', 'Side' ] # Dynamically construct the regex pattern with all possible street types street_types_pattern = '|'.join(rf"{re.escape(street_type)}" for street_type in street_types) # The overall regex pattern to capture the street name pattern = rf'(?:\d+\s+|\w+\s+\d+\s+|.*\d+[a-z]+\s+|.*\d+\s+)*(?P[\w\s]+(?:{street_types_pattern}))' def replace_postcode(address): pattern = r'\b(?:[A-Z][A-HJ-Y]?[0-9][0-9A-Z]? ?[0-9][A-Z]{2}|GIR ?0A{2})\b$|(?:[A-Z][A-HJ-Y]?[0-9][0-9A-Z]? ?[0-9]{1}?)$|\b(?:[A-Z][A-HJ-Y]?[0-9][0-9A-Z]?)\b$' return re.sub(pattern, "", address) modified_address = replace_postcode(address.upper()) #print(modified_address) #print(address) # Perform a case-insensitive search match = re.search(pattern, modified_address, re.IGNORECASE) if match: street_name = match.group('street_name') return street_name.strip() else: return "" def remove_flat_one_number_address(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: ''' If there is only one number in the address, and there is no letter after the number, remove the word flat from the address ''' df['contains_letter_after_number'] = df[col1].str.lower().str.contains(r"\d+(?:[a-z]|[A-Z])(?!.*\d+)", regex = True) df['contains_single_letter_before_number'] = df[col1].str.lower().str.contains(r'\b[A-Za-z]\b[^\d]* \d', regex = True) df['two_numbers_in_address'] = df[col1].str.lower().str.contains(r"(?:\d+.*?)[^a-zA-Z0-9_].*?\d+", regex = True) df['contains_apartment'] = df[col1].str.lower().str.contains(r"\bapartment\b \w+|\bapartments\b \w+", "", regex = True) df['contains_flat'] = df[col1].str.lower().str.contains(r"\bflat\b \w+|\bflats\b \w+", "", regex = True) df['contains_room'] = df[col1].str.lower().str.contains(r"\broom\b \w+|\brooms\b \w+", "", regex = True) df['selected_rows'] = (df['contains_letter_after_number'] == False) &\ (df['two_numbers_in_address'] == False) &\ (df['contains_single_letter_before_number'] == False) &\ ((df['contains_flat'] == True) |\ (df['contains_apartment'] == True) |\ (df['contains_room'] == True)) df['one_number_no_flat'] = df[df['selected_rows'] == True][col1] df['one_number_no_flat'] = df['one_number_no_flat'].str.replace(r"(\bapartment\b)|(\bapartments\b)", "", regex=True).str.replace(r"(\bflat\b)|(\bflats\b)", "", regex=True).str.replace(r"(\broom\b)|(\brooms\b)", "", regex=True) df["new_col"] = merge_series(df[col1], df["one_number_no_flat"]) return df['new_col'] def add_flat_addresses_start_with_letter(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: ''' Add the word flat to addresses that start with a letter. ''' df['contains_single_letter_at_start_before_number'] = df[col1].str.lower().str.contains(r'^\b[A-Za-z]\b[^\d]* \d', regex = True) df['selected_rows'] = (df['contains_single_letter_at_start_before_number'] == True) df['flat_added_to_string_start'] = "flat " + df[df['selected_rows'] == True][col1] #merge_columns(df, "new_col", col1, 'flat_added_to_string_start') df["new_col"] = merge_series(df[col1], df['flat_added_to_string_start']) return df['new_col'] def extract_letter_one_number_address(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: ''' This function looks for addresses that have a letter after a number, but ONLY one number in the string, and doesn't already have a flat, apartment, or room number. It then extracts this letter and returns this. This is for addresses such as '2b sycamore road', changes it to flat b 2 sycamore road so that 'b' is selected as the flat number ''' df['contains_no_numbers_without_letter'] = df[col1].str.lower().str.contains(r"^(?:(?!\d+ ).)*$") df['contains_letter_after_number'] = df[col1].str.lower().str.contains(r"\d+(?:[a-z]|[A-Z])(?!.*\d+)") df['contains_apartment'] = df[col1].str.lower().str.contains(r"\bapartment\b \w+|\bapartments\b \w+", "") df['contains_flat'] = df[col1].str.lower().str.contains(r"\bflat\b \w+|\bflats\b \w+", "") df['contains_room'] = df[col1].str.lower().str.contains(r"\broom\b \w+|\brooms\b \w+", "") df['selected_rows'] = (df['contains_no_numbers_without_letter'] == True) &\ (df['contains_letter_after_number'] == True) &\ (df['contains_flat'] == False) &\ (df['contains_apartment'] == False) &\ (df['contains_room'] == False) df['extract_letter'] = df[(df['selected_rows'] == True)\ ][col1].str.extract(r"\d+([a-z]|[A-Z])") df['extract_number'] = df[(df['selected_rows'] == True)\ ][col1].str.extract(r"(\d+)[a-z]|[A-Z]") df['letter_after_number'] = "flat " +\ df[(df['selected_rows'] == True)\ ]['extract_letter'] +\ " " +\ df[(df['selected_rows'] == True)\ ]['extract_number'] +\ " " +\ df[(df['selected_rows'])\ ][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\d+([a-z]|[A-Z])","", regex=True).map(str) #merge_columns(df, "new_col", col1, 'letter_after_number') df["new_col"] = merge_series(df[col1], df['letter_after_number']) return df['new_col'] # def extract_letter_one_number_address(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: # ''' # This function extracts a letter after a single number in an address, excluding cases with existing flat, apartment, or room numbers. # It transforms addresses like '2b sycamore road' to 'flat b 2 sycamore road' to designate 'b' as the flat number. # ''' # df['selected_rows'] = (df[col1].str.lower().str.contains(r"^(?:(?!\d+ ).)*$") & \ # df[col1].str.lower().str.contains(r"\d+(?:[a-z]|[A-Z])(?!.*\d+)") & \ # ~df[col1].str.lower().str.contains(r"\bflat\b \w+|\bflats\b \w+|\bapartment\b \w+|\bapartments\b \w+|\broom\b \w+|\brooms\b \w+")) # df['extract_letter'] = df.loc[df['selected_rows'], col1].str.extract(r"\d+([a-z]|[A-Z])") # df['extract_number'] = df.loc[df['selected_rows'], col1].str.extract(r"(\d+)[a-z]|[A-Z]") # df['letter_after_number'] = "flat " + df['extract_letter'] + " " + df['extract_number'] + " " + \ # df.loc[df['selected_rows'], col1].str.replace(r"\bflat\b", "", regex=True).str.replace(r"\d+([a-z]|[A-Z])", "", regex=True).map(str) # df["new_col"] = df[col1].copy() # df.loc[df['selected_rows'], "new_col"] = df['letter_after_number'] # return df['new_col'] def replace_floor_flat(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: ''' In references to basement, ground floor, first floor, second floor, and top floor flats, this function moves the word 'flat' to the front of the address. This is so that the following word (e.g. basement, ground floor) is recognised as the flat number in the 'extract_flat_and_other_no' function ''' df['letter_after_number'] = extract_letter_one_number_address(df, col1) df['basement'] = "flat basement" + df[df[col1].str.lower().str.contains(r"basement"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bbasement\b","", regex=True).map(str) df['ground_floor'] = "flat a " + df[df[col1].str.lower().str.contains(r"\bground floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bground floor\b","", regex=True).map(str) df['first_floor'] = "flat b " + df[df[col1].str.lower().str.contains(r"\bfirst floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bfirst floor\b","", regex=True).map(str) df['ground_and_first_floor'] = "flat ab " + df[df[col1].str.lower().str.contains(r"\bground and first floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bground and first floor\b","", regex=True).map(str) df['basement_ground_and_first_floor'] = "flat basementab " + df[df[col1].str.lower().str.contains(r"\bbasement ground and first floors\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bbasement and ground and first floors\b","", regex=True).map(str) df['basement_ground_and_first_floor2'] = "flat basementab " + df[df[col1].str.lower().str.contains(r"\bbasement ground and first floors\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bbasement ground and first floors\b","", regex=True).map(str) df['second_floor'] = "flat c " + df[df[col1].str.lower().str.contains(r"\bsecond floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bsecond floor\b","", regex=True).map(str) df['first_and_second_floor'] = "flat bc " + df[df[col1].str.lower().str.contains(r"\bfirst and second floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bfirst and second floor\b","", regex=True).map(str) df['first1_floor'] = "flat b " + df[df[col1].str.lower().str.contains(r"\b1st floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\b1st floor\b","", regex=True).map(str) df['second2_floor'] = "flat c " + df[df[col1].str.lower().str.contains(r"\b2nd floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\b2nd floor\b","", regex=True).map(str) df['ground_first_second_floor'] = "flat abc " + df[df[col1].str.lower().str.contains(r"\bground and first and second floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bground and first and second floor\b","", regex=True).map(str) df['third_floor'] = "flat d " + df[df[col1].str.lower().str.contains(r"\bthird floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\bthird floor\b","", regex=True).map(str) df['third3_floor'] = "flat d " + df[df[col1].str.lower().str.contains(r"\b3rd floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\b3rd floor\b","", regex=True).map(str) df['top_floor'] = "flat top " + df[df[col1].str.lower().str.contains(r"\btop floor\b"\ )][col1].str.replace(r"\bflat\b","", regex=True).str.replace(r"\btop floor\b","", regex=True).map(str) #merge_columns(df, "new_col", col1, 'letter_after_number') df["new_col"] = merge_series(df[col1], df['letter_after_number']) df["new_col"] = merge_series(df["new_col"], df['basement']) df["new_col"] = merge_series(df["new_col"], df['ground_floor']) df["new_col"] = merge_series(df["new_col"], df['first_floor']) df["new_col"] = merge_series(df["new_col"], df['first1_floor']) df["new_col"] = merge_series(df["new_col"], df['ground_and_first_floor']) df["new_col"] = merge_series(df["new_col"], df['basement_ground_and_first_floor']) df["new_col"] = merge_series(df["new_col"], df['basement_ground_and_first_floor2']) df["new_col"] = merge_series(df["new_col"], df['second_floor']) df["new_col"] = merge_series(df["new_col"], df['second2_floor']) df["new_col"] = merge_series(df["new_col"], df['first_and_second_floor']) df["new_col"] = merge_series(df["new_col"], df['ground_first_second_floor']) df["new_col"] = merge_series(df["new_col"], df['third_floor']) df["new_col"] = merge_series(df["new_col"], df['third3_floor']) df["new_col"] = merge_series(df["new_col"], df['top_floor']) return df['new_col'] # def replace_floor_flat(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: # '''Moves the word 'flat' to the front of addresses with floor references like basement, ground floor, etc.''' # floor_mapping = { # 'basement': 'basement', # 'ground floor': 'a', # 'first floor': 'b', # 'ground and first floor': 'ab', # 'basement ground and first floors': 'basementab', # 'second floor': 'c', # 'first and second floor': 'bc', # '1st floor': 'b', # '2nd floor': 'c', # 'ground and first and second floor': 'abc', # 'third floor': 'd', # '3rd floor': 'd', # 'top floor': 'top' # } # for key, value in floor_mapping.items(): # df[key] = f"flat {value} " + df[df[col1].str.lower().str.contains(fr"\b{key}\b")][col1].str.replace(r"\bflat\b", "", regex=True).str.replace(fr"\b{key}\b", "", regex=True).map(str) # df["new_col"] = df[col1].copy() # for key in floor_mapping.keys(): # df["new_col"] = merge_series(df["new_col"], df[key]) # return df["new_col"] def remove_non_housing(df:PandasDataFrame, col1:PandasSeries) -> PandasDataFrame: ''' Remove items from the housing list that are not housing. Includes addresses including the text 'parking', 'garage', 'store', 'visitor bay', 'visitors room', and 'bike rack', 'yard', 'workshop' ''' df_copy = df.copy()[~df[col1].str.lower().str.contains(\ r"parking|garage|\bstore\b|\bstores\b|\bvisitor bay\b|visitors room|\bbike rack\b|\byard\b|\bworkshop\b")] return df_copy def extract_prop_no(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: ''' Extract property number from an address. Remove flat/apartment/room numbers, then extract the last number/number + letter in the string. ''' try: prop_no = df[col1].str.replace(r"(^\bapartment\b \w+)|(^\bapartments\b \w+)", "", regex=True\ ).str.replace(r"(^\bflat\b \w+)|(^\bflats\b \w+)", "", regex=True\ ).str.replace(r"(^\broom\b \w+)|(^\brooms\b \w+)", "", regex=True\ ).str.replace(",", "", regex=True\ ).str.extract(r"(\d+\w+|\d+)(?!.*\d+)") #"(\d+\w+|\d+)(?!.*\d+)" except: prop_no = np.nan return prop_no def extract_room_no(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: ''' Extract room number from an address. Find rows where the address contains 'room', then extract the next word after 'room' in the string. ''' try: room_no = df[df[col1].str.lower().str.contains(r"\broom\b|\brooms\b",regex=True\ )][col1].str.replace("no.","").str.extract(r'room. (\w+)',regex=True\ ).rename(columns = {0:"room_number"}) except: room_no = np.nan return room_no def extract_flat_and_other_no(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: ''' Extract flat number from an address. It looks for letters after a property number IF THERE ARE NO MORE NUMBERS IN THE STRING, the words following the words 'flat' or 'apartment', or the last regex selects all characters in a word containing a digit if there are two numbers in the address # ^\d+([a-z]|[A-Z]) ''' # the regex essentially matches strings that satisfy any of the following conditions: # Start with a number followed by a single letter (either lowercase or uppercase) and not followed by any other number. # Contain the word "flat" or "apartment". # Start with a number, followed by any characters that are not alphanumeric (denoted by [^a-zA-Z0-9_]), and then another number. replaced_series = df[df[col1].str.lower().str.replace(r"^\bflats\b","flat", regex=True).\ str.contains(\ r"^\d+([a-z]|[A-Z])(?!.*\d+)|^([a-z] |[A-Z] )(?!.*\d+)|\bflat\b|\bapartment\b|(\d+.*?)[^a-zA-Z0-9_].*?\d+")][col1].str.replace("no.","", regex=True) extracted_series = replaced_series.str.extract(r'^\d+([a-z]|[A-Z])(?!.*\d+)')[0] extracted_series = extracted_series[~extracted_series.index.duplicated()] df = df[~df.index.duplicated(keep='first')] df["prop_number"] = extracted_series extracted_series = replaced_series.str.extract(r'(?i)(?:flat|flats) (\w+)') if 1 in extracted_series.columns: df["flat_number"] = extracted_series[0].fillna(extracted_series[1]).infer_objects(copy=False) else: df["flat_number"] = extracted_series[0] extracted_series = replaced_series.str.extract(r'(?i)(?:apartment|apartments) (\w+)') if 1 in extracted_series.columns: df["apart_number"] = extracted_series[0].fillna(extracted_series[1]).infer_objects(copy=False) else: df["apart_number"] = extracted_series[0] df["first_sec_number"] = replaced_series.str.extract(r'(\d+.*?)[^a-zA-Z0-9_].*?\d+') df["first_letter_flat_number"] = replaced_series.str.extract(r'\b([A-Za-z])\b[^\d]* \d') df["first_letter_no_more_numbers"] = replaced_series.str.extract(r'^([a-z] |[A-Z] )(?!.*\d+)') return df def extract_house_or_court_name(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: ''' Extract house or court name. Extended to include estate, buildings, and mansions ''' extracted_series = df[col1].str.extract(r"(\w+)\s+(house|court|estate|buildings|mansions)") if 1 in extracted_series.columns: df["house_court_name"] = extracted_series[0].fillna(extracted_series[1]).infer_objects(copy=False) else: df["house_court_name"] = extracted_series[0] return df["house_court_name"] def extract_block_and_unit_name(df:PandasDataFrame, col1:PandasSeries) -> PandasSeries: ''' Extract house or court name. Extended to include estate, buildings, and mansions ''' extracted_series = df[col1].str.extract(r'(?i)(?:block|blocks) (\w+)') if 1 in extracted_series.columns: df["block_number"] = extracted_series[0].fillna(extracted_series[1]).infer_objects(copy=False) else: df["block_number"] = extracted_series[0] extracted_series = df[col1].str.extract(r'(?i)(?:unit|units) (\w+)') if 1 in extracted_series.columns: df["unit_number"] = extracted_series[0].fillna(extracted_series[1]).infer_objects(copy=False) else: df["unit_number"] = extracted_series[0] return df def extract_postcode(df:PandasDataFrame, col:str) -> PandasSeries: ''' Extract a postcode from a string column in a dataframe ''' postcode_series = df[col].str.upper().str.extract(pat = \ "(\\b(?:[A-Z][A-HJ-Y]?[0-9][0-9A-Z]? ?[0-9][A-Z]{2})|((GIR ?0A{2})\\b$)|(?:[A-Z][A-HJ-Y]?[0-9][0-9A-Z]? ?[0-9]{1}?)$)|(\\b(?:[A-Z][A-HJ-Y]?[0-9][0-9A-Z]?)\\b$)") return postcode_series def remove_postcode(df:PandasDataFrame, col:str) -> PandasSeries: ''' Remove a postcode from a string column in a dataframe ''' address_series_no_pcode = df[col].str.upper().str.replace(\ "\\b(?:[A-Z][A-HJ-Y]?[0-9][0-9A-Z]? ?[0-9][A-Z]{2}|GIR ?0A{2})\\b$|(?:[A-Z][A-HJ-Y]?[0-9][0-9A-Z]? ?[0-9]{1}?)$|\\b(?:[A-Z][A-HJ-Y]?[0-9][0-9A-Z]?)\\b$","",\ regex=True ).str.lower() return address_series_no_pcode # Remove addresses with no numbers in at all - too high a risk of badly assigning an address def check_no_number_addresses(df:PandasDataFrame, in_address_series:PandasSeries) -> PandasSeries: ''' Highlight addresses from a pandas df where there are no numbers in the address. ''' df["in_address_series_temp"] = df[in_address_series].str.lower() no_numbers_series = df["in_address_series_temp"].str.contains("^(?!.*\d+).*$", regex=True) df.loc[no_numbers_series == True, 'Excluded from search'] = "Excluded - no numbers in address" df = df.drop("in_address_series_temp", axis = 1) print(df[["full_address", "Excluded from search"]]) return df # Exclude non-postal addresses def remove_non_postal(df, in_address_series): ''' Highlight non-postal addresses from a pandas df where a string series that contain specific substrings indicating non-postal addresses like 'garage', 'parking', 'shed', etc. ''' df["in_address_series_temp"] = df[in_address_series].str.lower() garage_address_series = df["in_address_series_temp"].str.contains("(?i)(?:\\bgarage\\b|\\bgarages\\b)", regex=True) parking_address_series = df["in_address_series_temp"].str.contains("(?i)(?:\\bparking\\b)", regex=True) shed_address_series = df["in_address_series_temp"].str.contains("(?i)(?:\\bshed\\b|\\bsheds\\b)", regex=True) bike_address_series = df["in_address_series_temp"].str.contains("(?i)(?:\\bbike\\b|\\bbikes\\b)", regex=True) bicycle_store_address_series = df["in_address_series_temp"].str.contains("(?i)(?:\\bbicycle store\\b|\\bbicycle store\\b)", regex=True) non_postal_series = (garage_address_series | parking_address_series | shed_address_series | bike_address_series | bicycle_store_address_series) df.loc[non_postal_series == True, 'Excluded from search'] = "Excluded - non-postal address" df = df.drop("in_address_series_temp", axis = 1) return df def replace_mistaken_dates(df:PandasDataFrame, col:str) -> PandasSeries: ''' Identify addresses that mistakenly have dates in them and replace these dates with number values ''' # Regex pattern to identify the date-month format pattern = r'(\d{2})-(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)' # Dictionary to map month abbreviations to numbers month_map = { 'jan': '1', 'feb': '2', 'mar': '3', 'apr': '4', 'may': '5', 'jun': '6', 'jul': '7', 'aug': '8', 'sep': '9', 'oct': '10', 'nov': '11', 'dec': '12' } # Custom replacement function def replace_month(match): day = match.group(1).lstrip('0') # Get the day and remove leading zeros month = month_map[match.group(2)] # Convert month abbreviation to number return f"{day}-{month}" # Apply the regex replacement corrected_addresses = df[col].str.replace(pattern, replace_month, regex = True) return corrected_addresses def merge_series(full_series: pd.Series, partially_filled_series: pd.Series) -> pd.Series: ''' Merge two series. The 'full_series' is the series you want to replace values in 'partially_filled_series' is the replacer series. ''' replacer_series_is_null = partially_filled_series.isnull() # Start with full_series values merged_series = full_series.copy() # Replace values in merged_series where partially_filled_series is not null merged_series[~replacer_series_is_null] = partially_filled_series.dropna() return merged_series def clean_cols(col:str) -> str: return col.lower().strip().replace(r" ", "_").strip()