clean_data.py

import re

# Function to update type_of_ship2 based on keywords in description
def update_type_of_ship2(row):
    if row['description']:
        desc = row['description'].lower()
        if any([type_ship in desc for type_ship in ['lpg tanker', 'lpg carrier', 'gas tanker', 'petroleum tanker']]):
            row['type_of_ship2'] = 'Gas Tanker'
        elif any([type_ship in desc for type_ship in ['cruise ship', 'ferry', 'houseboat', 'sail boat', 'sailboat', 'sailing kacht', 'sailing yacht', 'yacht', 'schooner', 'motor yacht', 'pleasure craft', 'recreational boat', 'passenger ship', 'passenger boat', 'monohull']]):
            row['type_of_ship2'] = 'Passenger Vessel'
        elif any([type_ship in desc for type_ship in ['container carrier', 'container vessel', 'container ship', 'container']]):
            row['type_of_ship2'] = 'Container Vessel'
        elif any([type_ship in desc for type_ship in ['reefer', 'refrigerated cargo ship', 'refrigerated cargo vessel']]):
            row['type_of_ship2'] = 'Refrigered Cargo Vessel'
        elif any([type_ship in desc for type_ship in ['oil rig', 'oil platform', 'drilling rig']]):
            row['type_of_ship2'] = 'Oil Platform'
        elif any([type_ship in desc for type_ship in ['floating, production, storage, and offloading', 'fpso', 'fso', 'oil exploration vessel', 'offshore supply', 'supply vessel', 'osv', 'oiler', 'support vessel']]):
            row['type_of_ship2'] = 'Supply Vessel'
        elif any([type_ship in desc for type_ship in ['guard vessel', 'destroyer', 'patrol boat']]):
            row['type_of_ship2'] = 'Military Vessel'
        elif any([type_ship in desc for type_ship in ['oil tanker', 'product tanker', 'crude tanker', 'petroleum tanker']]):
            row['type_of_ship2'] = 'Oil Tanker'
        elif any([type_ship in desc for type_ship in ['chemical/gas tanker', 'chemical tanker']]):
            row['type_of_ship2'] = 'Chemical Tanker'
        elif 'tanker' in desc:
            row['type_of_ship2'] = 'Tanker'
        elif any([type_ship in desc for type_ship in ['roro', 'ro-ro', 'car carrier', 'vehicle carrier vessel']]):
            row['type_of_ship2'] = 'Vehicle Carrier'
        elif any([type_ship in desc for type_ship in ['fishing boat', 'fishing ship', 'trawler']]):
            row['type_of_ship2'] = 'Fishing Vessel'
        elif 'cargo ship' in desc:
            row['type_of_ship2'] = 'Cargo Vessel'
        elif 'tug' in desc:
            row['type_of_ship2'] = 'Tug'
        elif any([type_ship in desc for type_ship in ['merchant vessel', 'merchant ship']]):
            row['type_of_ship2'] = 'Merchant Vessel'
        elif any([type_ship in desc for type_ship in ['bulk cargo ship', 'bulk freighter', 'bulk cargo carrier', 'ore carrier', 'bulk carrier', 'cement carrier']]):
            row['type_of_ship2'] = 'Bulk Carrier'
        elif any([type_ship in desc for type_ship in ['heavy lift carrier', 'live stock carrier', 'research vessel', 'service vessel', 'salvage vessel', 'wood ship carrier']]):
            row['type_of_ship2'] = 'Other'
    return row

def find_substring(s):
    match = re.search(r'(?i:(?:\b(?:tanker|carrier|vessel|ship|flagged|boat|barge|AE|AFS|AHT|AHTS|AO|AOG|AOR|AOT|ASDS|ATB|CF|CRV|CS|DB|DCV|DEPV|DLB|DSV|DV|ERRV|EV|FPSO|FPV|FT|FV|GTS|HLV|HMHS|HMT|HSC|HSF|HTV|IRV|ITB|LB|LNGC|LPGC|MF|MFV|MS|MSV|MSY|MT|MTS|MV|MY|NB|NRV|NS|OSV|PS|PSV|QSMV|QTEV|RMP|RMS|RMV|RNLB|RNMB|RRS|RSV|RV|SB|SL|SS|SSCV|SSS|SSV|ST|STS|STV|SV|SY|TB|TEV|TIV|TS|TSMS|TSMV|TSS|TST|TT|TV|Tr.SMV|TrSS|ULCC|VLCC|YD|YMT|YOS|YT|YTB|YTL|YTM|YW|YWN)\b|A/E|A/F/S|A/H/T|A/H/T/S|A/O|A/O/G|A/O/R|A/O/T|A/S/D/S|A/T/B|C/F|C/R/V|C/S|D/B|D/C/V|D/E/P/V|D/L/B|D/S/V|D/V|E/R/R/V|E/V|F/P/S/O|F/P/V|F/T|F/V|G/T/S|H/L/V|H/M/H/S|H/M/T|H/S/C|H/S/F|H/T/V|I/R/V|I/T/B|L/B|LNG/C|LPG/C|M/F|M/F/V|M/S|M/S/V|M/S/Y|M/T|M/T/S|M/V|M/Y|N/B|N/R/V|N/S|O/S/V|P/S|P/S/V|Q/S/M/V|Q/T/E/V|R/M/P|R/M/S|R/M/V|R/N/L/B|R/N/M/B|R/R/S|R/S/V|R/V|S/B|S/L|S/S|S/S/C/V|S/S/S|S/S/V|S/T|S/T/S|S/T/V|S/V|S/Y|T/B|T/E/V|T/I/V|T/S|T/S/M/S|T/S/M/V|T/S/S|T/S/T|T/T|T/V|T/r/S/S|U/L/C/C|V/L/C/C|Y/D|Y/M/T|Y/O/S|Y/T|Y/T/B|Y/T/L|Y/T/M|Y/W|Y/W/N))\s+\(?([A-Z0-9]+(?:\s+[A-Z0-9]+)*)\)?(?![\w/-])', s)
    
    # Maybe come back here to handle multiple ship names case
    #ship_names = ", ".join([name for name in list(set(ship_names)) if name != "OOW"])

    ship_name = match.group(1).strip() if match else ''

    return ship_name if not ship_name.isdigit() and ship_name != 'LPG' else ''

def move_ship_names_from_description(row):
    if row['description']:
        row['name_of_ship2'] = find_substring(row['description']) # Move the matched pattern inside paranthesis to the ship column
    
    return row

# Function to populate actor columns based on keywords in the description
def populate_actors(row):
    if row['description']:  # Check if description is a string
        description = row['description'].lower()

        # Populate actor1 with 'Pirates' or 'Robbers'
        if 'pirate' in description or 'pirates' in description:
            row['actor1'] = 'Pirates'
        elif 'robber' in description or 'robbers' in description:
            row['actor1'] = 'Robbers'

        # Populate actor2 with 'Crew' or 'Passengers'
        if any(word in description for word in ['duty officer', 'duty a/b', 'master', 'watchman', 'watch man', 'duty pump man', 
                                                'duty oiler', 'motorman', 'duty deckhand', 'duty cadet', 'engineer', 
                                                'crew', 'crew member', 'crew members']):
            row['actor2'] = 'Crew'
        elif 'passenger' in description or 'passengers' in description:
            row['actor2'] = 'Passengers'

        # Populate assoc_actor2 based on keywords
        if "coast guard" not in description and any([word in description for word in ["guard", "armed guard", "security team", "naval force protection team", "armed security", "protection detachment"]]):
            row['assoc_actor_2'] = 'Embarked Guards'
        else:
            row['assoc_actor_2'] = ''

    return row

# Function to update event_type based on keywords in description
def update_event_type(row):
    if row['description']:  # Check if description is a string
        desc = row['description'].lower()

        # Replace Piracy/Robbery Distinction in event type and replace with subtypes
        row['event_type'] = row['event_subtype']
        row['event_subtype'] = ''
        
        # Define hierarchy of event types
        event_types = [
            ('casualty', ['killed', 'killing', 'died', 'injured']),
            ('abduction', ['kidnapped', 'abducted', 'kidnapping', 'abducting']),
            ('hijacking', ['hijacked', 'hijacking']),
            ('theft', ['stolen', 'stole', 'stealing', 'items were missing', 'robbed', 'rob']),
            ('boarding', ['attempt to board', 'attempting to board', 'attempted to board', 'boarded', 'boarding', 'attempted boardings']),
            ('fired upon', ['fired upon', 'fire upon', 'fired at']),
            ('suspicious approach', ['approached', 'approach', 'approaching', 'chased', 'suspicious approach']),
            ('attack', ['attack'])
        ]
        
        # Iterate over event types and assign based on first match in hierarchy
        for event, keywords in event_types:
            if any(keyword in desc for keyword in keywords):
                if event == 'theft' and any([text in desc for text in ["nothing was stolen", "nothing stolen"]]):
                    continue
                if event == 'casuality' and any([text in desc for text in ["no injuries"]]):
                    continue
                
                row['event_type'] = event.capitalize()
                break

        if row['event_type'] == '':
            row['event_type'] = 'Other'

    return row

# Function to update type_of_ship1, type_of_ship3, and actor3 based on description keywords
def update_ships_and_actors(row):
    if row['description']:  # Check if description is a string
        desc = row['description'].lower().replace("patrol boat", "").replace("tug boat", "").replace("small boat", "boat")

        # Populate type_of_ship1 or type_of_ship3 based on keywords
        if any(word in desc for word in ['skiff', 'skiffs', 'skiff(s)']):
            row['type_of_ship1'] = 'Skiff'
        
        if any([word in desc for word in ['mothership', 'motherships', 'mothership(s)', 'mother vessel', 'mother ship', 'mother ships']]):
            if row['type_of_ship1'] == "":
                row['type_of_ship1'] = 'Mothership'

            elif row['type_of_ship3'] == "":
                row['type_of_ship3'] = 'Mothership'

        if any([word in desc for word in ['speed boat', 'speed boats', 'speed boat(s)', 'speedboat', 'small fast craft']]):
            if row['type_of_ship1'] == "" or row['type_of_ship1'].lower() == "boat" or row['type_of_ship1'].lower() == "small boat":
                row['type_of_ship1'] = "Speed Boat"
                
            elif row['type_of_ship3'] == "" or row['type_of_ship3'].lower() == "boat" or row['type_of_ship3'].lower() == "small boat":
                row['type_of_ship3'] = 'Speed Boat'

            # Avoid finding assign boat for speedboats
            desc.replace("speed boat", "").replace("speedboat", "")
        
        elif any([word in desc for word in ['boat', 'boats', 'boat(s)', 'banca', "bancas", 'sampan']]):
            if row['type_of_ship1'] == "" or row['type_of_ship1'].lower() == "small boat":
                row['type_of_ship1'] = 'Boat'

            elif row['type_of_ship3'] == "" or row['type_of_ship3'].lower() == "small boat":
                row['type_of_ship3'] = 'Boat'

    return row

country_dict = {
    'Afghanistan': 'Afghanistan',
    'Afghan': 'Afghanistan',
    'Albania': 'Albania',
    'Albanian': 'Albania',
    'Algeria': 'Algeria',
    'Algerian': 'Algeria',
    'Andorra': 'Andorra',
    'Andorran': 'Andorra',
    'Angola': 'Angola',
    'Angolan': 'Angola',
    'Anguilla': 'Anguilla',
    'Anguillan': 'Anguilla',
    'Antarctica': 'Antarctica',
    'Antarctic': 'Antarctica',
    'Antigua and Barbuda': 'Antigua and Barbuda',
    'Antiguan': 'Antigua and Barbuda',
    'Barbudan': 'Antigua and Barbuda',
    'Argentina': 'Argentina',
    'Argentine': 'Argentina',
    'Argentinian': 'Argentina',
    'Armenia': 'Armenia',
    'Armenian': 'Armenia',
    'Aruba': 'Aruba',
    'Aruban': 'Aruba',
    'Australia': 'Australia',
    'Australian': 'Australia',
    'Austria': 'Austria',
    'Austrian': 'Austria',
    'Azerbaijan': 'Azerbaijan',
    'Azerbaijani': 'Azerbaijan',
    'Azeri': 'Azerbaijan',
    'Bahamas': 'Bahamas',
    'Bahamian': 'Bahamas',
    'Bahrain': 'Bahrain',
    'Bahraini': 'Bahrain',
    'Bangladesh': 'Bangladesh',
    'Bangladeshi': 'Bangladesh',
    'Barbados': 'Barbados',
    'Barbadian': 'Barbados',
    'Saint Barthelemy': 'Saint Barthelemy',
    'Barthélemois': 'Saint Barthelemy',
    'Lesotho': 'Lesotho',
    'Basotho': 'Lesotho',
    'Belarus': 'Belarus',
    'Belarusian': 'Belarus',
    'Belgium': 'Belgium',
    'Belgian': 'Belgium',
    'Belize': 'Belize',
    'Belizean': 'Belize',
    'Benin': 'Benin',
    'Beninese': 'Benin',
    'Beninois': 'Benin',
    'Bermuda': 'Bermuda',
    'Bermudian': 'Bermuda',
    'Bermudan': 'Bermuda',
    'Bhutan': 'Bhutan',
    'Bhutanese': 'Bhutan',
    'Guinea-Bissau': 'Guinea-Bissau',
    'Bissau-Guinean': 'Guinea-Bissau',
    'Bolivia': 'Bolivia',
    'Bolivian': 'Bolivia',
    'Bonaire': 'Bonaire',
    'Bonairean': 'Bonaire',
    'Bosnia and Herzegovina': 'Bosnia and Herzegovina',
    'Bosnian': 'Bosnia and Herzegovina',
    'Herzegovinian': 'Bosnia and Herzegovina',
    'Botswana': 'Botswana',
    'Botswanan': 'Botswana',
    'Bouvet Island': 'Bouvet Island',
    'Brazil': 'Brazil',
    'Brazilian': 'Brazil',
    'United Kingdom': 'United Kingdom',
    'British': 'United Kingdom',
    'British Virgin Islands': 'British Virgin Islands',
    'British Virgin Island': 'British Virgin Islands',
    'Brunei': 'Brunei',
    'Bruneian': 'Brunei',
    'Bulgaria': 'Bulgaria',
    'Bulgarian': 'Bulgaria',
    'Burkina Faso': 'Burkina Faso',
    'Burkinabé': 'Burkina Faso',
    'Burma': 'Burma',
    'Burmese': 'Burma',
    'Myanma': 'Burma',
    'Burundi': 'Burundi',
    'Burundian': 'Burundi',
    'Cabo Verde': 'Cabo Verde',
    'Cabo Verdean': 'Cabo Verde',
    'Cambodia': 'Cambodia',
    'Cambodian': 'Cambodia',
    'Cameroon': 'Cameroon',
    'Cameroonian': 'Cameroon',
    'Canada': 'Canada',
    'Canadian': 'Canada',
    'Cayman Islands': 'Cayman Islands',
    'Caymanian': 'Cayman Islands',
    'Central African Republic': 'Central African Republic',
    'Central African': 'Central African Republic',
    'Chad': 'Chad',
    'Chadian': 'Chad',
    'Chile': 'Chile',
    'Chilean': 'Chile',
    'China': 'China',
    'Chinese': 'China',
    'Colombia': 'Colombia',
    'Colombian': 'Colombia',
    'Comoros': 'Comoros',
    'Comoran': 'Comoros',
    'Comorian': 'Comoros',
    'Democratic Republic of the Congo': 'Democratic Republic of the Congo',
    'Congolese': 'Republic of the Congo',
    'Republic of the Congo': 'Republic of the Congo',
    'Cook Islands': 'Cook Islands',
    'Cook Island': 'Cook Islands',
    'Costa Rica': 'Costa Rica',
    'Costa Rican': 'Costa Rica',
    'Croatia': 'Croatia',
    'Croatian': 'Croatia',
    'Cuba': 'Cuba',
    'Cuban': 'Cuba',
    'Curacao': 'Curacao',
    'Curaçaoan': 'Curacao',
    'Cyprus': 'Cyprus',
    'Cypriot': 'Cyprus',
    'Czech Republic': 'Czech Republic',
    'Czech': 'Czech Republic',
    'Denmark': 'Denmark',
    'Danish': 'Denmark',
    'Djibouti': 'Djibouti',
    'Djiboutian': 'Djibouti',
    'Dominica': 'Dominica',
    'Dominican': 'Dominican Republic',
    'Dominican Republic': 'Dominican Republic',
    'Netherlands': 'Netherlands',
    'Dutch': 'Netherlands',
    'Ecuador': 'Ecuador',
    'Ecuadorian': 'Ecuador',
    'Egypt': 'Egypt',
    'Egyptian': 'Egypt',
    'United Arab Emirates': 'United Arab Emirates',
    'Emirati': 'United Arab Emirates',
    'Equatorial Guinea': 'Equatorial Guinea',
    'Equatorial Guinean': 'Equatorial Guinea',
    'Equatoguinean': 'Equatorial Guinea',
    'Eritrea': 'Eritrea',
    'Eritrean': 'Eritrea',
    'Estonia': 'Estonia',
    'Estonian': 'Estonia',
    'Ethiopia': 'Ethiopia',
    'Ethiopian': 'Ethiopia',
    'Falkland Islands': 'Falkland Islands',
    'Falkland Island': 'Falkland Islands',
    'Faroe Islands': 'Faroe Islands',
    'Faroese': 'Faroe Islands',
    'Fiji': 'Fiji',
    'Fijian': 'Fiji',
    'Philippines': 'Philippines',
    'Filipino': 'Philippines',
    'Philippine': 'Philippines',
    'Finland': 'Finland',
    'Finnish': 'Finland',
    'France': 'France',
    'French': 'France',
    'French Guiana': 'French Guiana',
    'French Guianese': 'French Guiana',
    'Gabon': 'Gabon',
    'Gabonese': 'Gabon',
    'Gambia': 'Gambia',
    'Gambian': 'Gambia',
    'Georgia': 'Georgia',
    'Georgian': 'Georgia',
    'Germany': 'Germany',
    'German': 'Germany',
    'Ghana': 'Ghana',
    'Ghanaian': 'Ghana',
    'Greece': 'Greece',
    'Greek': 'Greece',
    'Hellenic': 'Greece',
    'Grenada': 'Grenada',
    'Grenadian': 'Grenada',
    'Guadeloupe': 'Guadeloupe',
    'Guatemala': 'Guatemala',
    'Guatemalan': 'Guatemala',
    'Guinea': 'Guinea',
    'Guinean': 'Guinea',
    'Guyana': 'Guyana',
    'Guyanese': 'Guyana',
    'Haiti': 'Haiti',
    'Haitian': 'Haiti',
    'Honduras': 'Honduras',
    'Honduran': 'Honduras',
    'Hong Kong (China)': 'Hong Kong (China)',
    'Hong Konger': 'Hong Kong (China)',
    'Hungary': 'Hungary',
    'Hungarian': 'Hungary',
    'Iceland': 'Iceland',
    'Icelandic': 'Iceland',
    'India': 'India',
    'Indian': 'India',
    'Indonesia': 'Indonesia',
    'Indonesian': 'Indonesia',
    'Iran': 'Iran',
    'Iranian': 'Iran',
    'Iraq': 'Iraq',
    'Iraqi': 'Iraq',
    'Ireland': 'Ireland',
    'Irish': 'Ireland',
    'Israel': 'Israel',
    'Israeli': 'Israel',
    'Italy': 'Italy',
    'Italian': 'Italy',
    "Cote d'Ivoire": "Cote d'Ivoire",
    'Ivorian': "Cote d'Ivoire",
    'Jamaica': 'Jamaica',
    'Jamaican': 'Jamaica',
    'Japan': 'Japan',
    'Japanese': 'Japan',
    'Jordan': 'Jordan',
    'Jordanian': 'Jordan',
    'Kazakhstan': 'Kazakhstan',
    'Kazakhstani': 'Kazakhstan',
    'Kazakh': 'Kazakhstan',
    'Kenya': 'Kenya',
    'Kenyan': 'Kenya',
    'Kiribati': 'Kiribati',
    'Saint Kitts and Nevis': 'Saint Kitts and Nevis',
    'Kittitian': 'Saint Kitts and Nevis',
    'Nevisian': 'Saint Kitts and Nevis',
    'Kosovo': 'Kosovo',
    'Kosovar': 'Kosovo',
    'Kosovan': 'Kosovo',
    'Kuwait': 'Kuwait',
    'Kuwaiti': 'Kuwait',
    'Kyrgyzstan': 'Kyrgyzstan',
    'Kyrgyzstani': 'Kyrgyzstan',
    'Kyrgyz': 'Kyrgyzstan',
    'Laos': 'Laos',
    'Lao': 'Laos',
    'Laotian': 'Laos',
    'Latvia': 'Latvia',
    'Latvian': 'Latvia',
    'Lebanon': 'Lebanon',
    'Lebanese': 'Lebanon',
    'Liberia': 'Liberia',
    'Liberian': 'Liberia',
    'Libya': 'Libya',
    'Libyan': 'Libya',
    'Liechtenstein': 'Liechtenstein',
    'Liechtensteiner': 'Liechtenstein',
    'Lithuania': 'Lithuania',
    'Lithuanian': 'Lithuania',
    'Luxembourg': 'Luxembourg',
    'Luxembourgish': 'Luxembourg',
    'North Macedonia': 'North Macedonia',
    'Macedonian': 'North Macedonia',
    'Mayotte': 'Mayotte',
    'Mahoran': 'Mayotte',
    'Madagascar': 'Madagascar',
    'Malagasy': 'Madagascar',
    'Madagascan': 'Madagascar',
    'Malawi': 'Malawi',
    'Malawian': 'Malawi',
    'Malaysia': 'Malaysia',
    'Malaysian': 'Malaysia',
    'Maldives': 'Maldives',
    'Maldivian': 'Maldives',
    'Mali': 'Mali',
    'Malian': 'Mali',
    'Malinese': 'Mali',
    'Malta': 'Malta',
    'Maltese': 'Malta',
    'Isle of Man': 'Isle of Man',
    'Manx': 'Isle of Man',
    'Marshall Islands': 'Marshall Islands',
    'Marshallese': 'Marshall Islands',
    'Martinique': 'Martinique',
    'Martiniquais': 'Martinique',
    'Martinican': 'Martinique',
    'Mauritania': 'Mauritania',
    'Mauritanian': 'Mauritania',
    'Mauritius': 'Mauritius',
    'Mauritian': 'Mauritius',
    'Mexico': 'Mexico',
    'Mexican': 'Mexico',
    'Micronesia': 'Micronesia',
    'Micronesian': 'Micronesia',
    'Moldova': 'Moldova',
    'Moldovan': 'Moldova',
    'Monaco': 'Monaco',
    'Monégasque': 'Monaco',
    'Monacan': 'Monaco',
    'Mongolia': 'Mongolia',
    'Mongolian': 'Mongolia',
    'Montenegro': 'Montenegro',
    'Montenegrin': 'Montenegro',
    'Montserrat': 'Montserrat',
    'Montserratian': 'Montserrat',
    'Morocco': 'Morocco',
    'Moroccan': 'Morocco',
    'Mozambique': 'Mozambique',
    'Mozambican': 'Mozambique',
    'Namibia': 'Namibia',
    'Namibian': 'Namibia',
    'Nauru': 'Nauru',
    'Nauruan': 'Nauru',
    'Nepal': 'Nepal',
    'Nepali': 'Nepal',
    'Nepalese': 'Nepal',
    'New Caledonia': 'New Caledonia',
    'New Caledonian': 'New Caledonia',
    'New Zealand': 'New Zealand',
    'Vanuatu': 'Vanuatu',
    'Ni-Vanuatu': 'Vanuatu',
    'Vanuatuan': 'Vanuatu',
    'Nicaragua': 'Nicaragua',
    'Nicaraguan': 'Nicaragua',
    'Nigeria': 'Nigeria',
    'Nigerian': 'Nigeria',
    'Niger': 'Niger',
    'Nigerien': 'Niger',
    'North Korea': 'North Korea',
    'North Korean': 'North Korea',
    'Norway': 'Norway',
    'Norwegian': 'Norway',
    'Oman': 'Oman',
    'Omani': 'Oman',
    'Pakistan': 'Pakistan',
    'Pakistani': 'Pakistan',
    'Palau': 'Palau',
    'Palauan': 'Palau',
    'Palestinian Territories': 'Palestinian Territories',
    'Palestinian': 'Palestinian Territories',
    'Panama': 'Panama',
    'Panamanian': 'Panama',
    'Papua New Guinea': 'Papua New Guinea',
    'Papua New Guinean': 'Papua New Guinea',
    'Papuan': 'Papua New Guinea',
    'Paraguay': 'Paraguay',
    'Paraguayan': 'Paraguay',
    'Peru': 'Peru',
    'Peruvian': 'Peru',
    'Poland': 'Poland',
    'Polish': 'Poland',
    'Portugal': 'Portugal',
    'Portuguese': 'Portugal',
    'Qatar': 'Qatar',
    'Qatari': 'Qatar',
    'Reunion': 'Reunion',
    'Réunionese': 'Reunion',
    'Réunionnais': 'Reunion',
    'Romania': 'Romania',
    'Romanian': 'Romania',
    'Russia': 'Russia',
    'Russian': 'Russia',
    'Rwanda': 'Rwanda',
    'Rwandan': 'Rwanda',
    'Saba': 'Saba',
    'Saban': 'Saba',
    'Saint Lucia': 'Saint Lucia',
    'Saint Lucian': 'Saint Lucia',
    'Saint Vincent and the Grenadines': 'Saint Vincent and the Grenadines',
    'Saint Vincentian': 'Saint Vincent and the Grenadines',
    'Vincentian': 'Saint Vincent and the Grenadines',
    'Saint Martin': 'Saint Martin',
    'Saint-Martinoise': 'Saint Martin',
    'Saint Pierre and Miquelon': 'Saint Pierre and Miquelon',
    'Saint-Pierrais': 'Saint Pierre and Miquelon',
    'Miquelonnais': 'Saint Pierre and Miquelon',
    'El Salvador': 'El Salvador',
    'Salvadoran': 'El Salvador',
    'San Marino': 'San Marino',
    'Sammarinese': 'San Marino',
    'Samoa': 'Samoa',
    'Samoan': 'Samoa',
    'Sao Tome and Principe': 'Sao Tome and Principe',
    'São Toméan': 'Sao Tome and Principe',
    'Saudi Arabia': 'Saudi Arabia',
    'Saudi': 'Saudi Arabia',
    'Saudi Arabian': 'Saudi Arabia',
    'Senegal': 'Senegal',
    'Senegalese': 'Senegal',
    'Serbia': 'Serbia',
    'Serbian': 'Serbia',
    'Seychelles': 'Seychelles',
    'Seychellois': 'Seychelles',
    'Sierra Leone': 'Sierra Leone',
    'Sierra Leonean': 'Sierra Leone',
    'Singapore': 'Singapore',
    'Singaporean': 'Singapore',
    'Sint Eustatius': 'Sint Eustatius',
    'Statian': 'Sint Eustatius',
    'Sint Maarten': 'Sint Maarten',
    'Slovakia': 'Slovakia',
    'Slovak': 'Slovakia',
    'Slovenia': 'Slovenia',
    'Slovenian': 'Slovenia',
    'Slovene': 'Slovenia',
    'Solomon Islands': 'Solomon Islands',
    'Solomon Island': 'Solomon Islands',
    'Somalia': 'Somalia',
    'Somali': 'Somalia',
    'South Africa': 'South Africa',
    'South African': 'South Africa',
    'South Georgia and South Sandwich Islands': 'South Georgia and South Sandwich Islands',
    'South Georgia Island': 'South Georgia and South Sandwich Islands',
    'South Sandwich Island': 'South Georgia and South Sandwich Islands',
    'South Korea': 'South Korea',
    'South Korean': 'South Korea',
    'South Sudan': 'South Sudan',
    'South Sudanese': 'South Sudan',
    'Spain': 'Spain',
    'Spanish': 'Spain',
    'Sri Lanka': 'Sri Lanka',
    'Sri Lankan': 'Sri Lanka',
    'Sudan': 'Sudan',
    'Sudanese': 'Sudan',
    'Suriname': 'Suriname',
    'Surinamese': 'Suriname',
    'Eswatini': 'Eswatini',
    'Swazi': 'Eswatini',
    'Swati': 'Eswatini',
    'Sweden': 'Sweden',
    'Swedish': 'Sweden',
    'Switzerland': 'Switzerland',
    'Swiss': 'Switzerland',
    'Syria': 'Syria',
    'Syrian': 'Syria',
    'Taiwan': 'Taiwan',
    'Taiwanese': 'Taiwan',
    'Tajikistan': 'Tajikistan',
    'Tajikistani': 'Tajikistan',
    'Tanzania': 'Tanzania',
    'Tanzanian': 'Tanzania',
    'Thailand': 'Thailand',
    'Thai': 'Thailand',
    'Timor-Leste': 'Timor-Leste',
    'Timorese': 'Timor-Leste',
    'Togo': 'Togo',
    'Togolese': 'Togo',
    'Tonga': 'Tonga',
    'Tongan': 'Tonga',
    'Trinidad and Tobago': 'Trinidad and Tobago',
    'Trinidadian': 'Trinidad and Tobago',
    'Tobagonian': 'Trinidad and Tobago',
    'Tunisia': 'Tunisia',
    'Tunisian': 'Tunisia',
    'Turkey': 'Turkey',
    'Turkish': 'Turkey',
    'Turkmenistan': 'Turkmenistan',
    'Turkmen': 'Turkmenistan',
    'Turks and Caicos Islands': 'Turks and Caicos Islands',
    'Turks and Caicos Island': 'Turks and Caicos Islands',
    'Tuvalu': 'Tuvalu',
    'Tuvaluan': 'Tuvalu',
    'Uganda': 'Uganda',
    'Ugandan': 'Uganda',
    'Ukraine': 'Ukraine',
    'Ukrainian': 'Ukraine',
    'Uruguay': 'Uruguay',
    'Uruguayan': 'Uruguay',
    'Uzbekistan': 'Uzbekistan',
    'Uzbekistani': 'Uzbekistan',
    'Uzbek': 'Uzbekistan',
    'Holy See': 'Holy See',
    'Vaticanian': 'Holy See',
    'Venezuela': 'Venezuela',
    'Venezuelan': 'Venezuela',
    'Vietnam': 'Vietnam',
    'Vietnamese': 'Vietnam',
    'Yemen': 'Yemen',
    'Yemeni': 'Yemen',
    'Zambia': 'Zambia',
    'Zambian': 'Zambia',
    'Zimbabwe': 'Zimbabwe',
    'Zimbabwean': 'Zimbabwe',
    'Clipperton Island': 'Clipperton Island',
    'Gibraltar': 'Gibraltar'
}

def navy_exist_in_description(row):
    if row['description']:  # Check if description is a string
        description = row['description'].lower()
        # Populate assoc_actor2 based on keywords

        conditional = [word in description for word in ["navy", "warship", "patrol boat"]]
        if any(conditional):
            countries_found = [country for country in country_dict.keys() if country.lower() in description]

            # Conditional for navy
            if conditional[0]:
                for country in countries_found:
                    # Build regex to match "country_name" followed by "navy"
                    pattern = rf'\b{re.escape(country.lower())}\b navy\b'
                    match = re.search(pattern, description)

                    if match:
                        # Set assoc_actor_2 with "Country Navy"
                        if row['assoc_actor_2'] == "":
                            row['assoc_actor_2'] = f"{country_dict[country]} Navy"
                        else:
                            row['actor3'] = f"{country_dict[country]} Navy"

                        # Add country to flag_state_involved if not already present
                        if row['flag_states_involved'] == "":
                            row['flag_states_involved'] = country_dict[country]
                        elif country_dict[country] not in row['flag_states_involved']:
                            row['flag_states_involved'] += ", " + country_dict[country]
                            
                        return row

            # Conditional for warship
            if conditional[1]:
                for country in countries_found:
                    # Build regex to match "country_name" followed by "warship"
                    pattern = rf'\b{re.escape(country.lower())}\b warship\b'
                    match = re.search(pattern, description)

                    if match:
                        # Set actor3 with "Country Navy"
                        row['actor3'] = f"{country_dict[country]} Navy"

                        # Add country to flag_state_involved if not already present
                        if row['flag_states_involved'] == "":
                            row['flag_states_involved'] = country_dict[country]
                        elif country_dict[country] not in row['flag_states_involved']:
                            row['flag_states_involved'] += ", " + country_dict[country]

                        break

                row['type_of_ship3'] = "Military Vessel"
            
            if conditional[2]:
                row['type_of_ship3'] = "Military Vessel"

    return row

key_words = [
    "pirate", "robber", "armed pirate", "armed robber", "pirates", "robbers", "armed pirates", "armed robbers" 
]

# Compile regex to match either numerals or written-out numbers followed by target keywords
key_pattern = re.compile(
    r"\b(\d+|one|two|three|four|five|six|seven|eight|nine|ten)\s+(?:" + "|".join(word for word in key_words) + r")\b",
    re.IGNORECASE
)

# Helper function to convert written numbers to integers
written_to_number = {
    "one": 1, "two": 2, "three": 3, "four": 4, "five": 5,
    "six": 6, "seven": 7, "eight": 8, "nine": 9, "ten": 10
}

def words_exist_in_description(row):
    if row['description']:  # Check if description is a string
        description = row['description'].lower()
        # Populate assoc_actor2 based on keywords

        matches = key_pattern.findall(description)
        if matches:
            # Convert all numbers to integers and sum them
            total_assailants = sum(
                int(match) if match.isdigit() else written_to_number[match]
                for match in matches
            )
            # Populate columns if a match is found
            row['event_associated_number'] = total_assailants
            row['event_associated_number_unit'] = "Assailants"

    return row

def populate_fishermen(row):
    if "Crew" not in row['actor2'] and any([text in row['description'].lower() for text in ["fisherman", "fishermen"]]):
        row['actor2'] = "Fishermen"
        
    return row

# Function to update actor1 based on description keywords
def update_actor1_based_on_event_type(row):
    if row['actor1'] == "" and row['event_type'] != "":
        event_type = row['event_type'].lower()
        # Populate actor1 based on keywords
        if any([text in event_type for text in ["fired upon", "boarding", "theft", "hijacking", "abduction", "casualty", "attack"]]):
            row['actor1'] = 'Assailants'

    return row

def format_data(data):
    # Save original column order
    original_columns = data.columns.tolist()

    data = data.apply(update_type_of_ship2, axis=1)

    data = data.apply(move_ship_names_from_description, axis=1)

    # Apply the function to the dataset
    data = data.apply(populate_actors, axis=1)

    # Clear the current event_type and apply the function to the dataset
    #df_piracy['event_type'] = ''  # Clear existing values
    data = data.apply(update_event_type, axis=1)

    # Apply the function to the dataset
    data = data.apply(update_ships_and_actors, axis=1)

    # Apply the function to the dataset
    data = data.apply(navy_exist_in_description, axis=1)

    # Apply the function to the dataset
    data = data.apply(words_exist_in_description, axis=1)

    # Apply the function to the dataset
    data = data.apply(populate_fishermen, axis=1)

    # Apply the function to the dataset
    data = data.apply(update_actor1_based_on_event_type, axis=1)

    data["type_of_ship2"] = data["type_of_ship2"].str.replace("barge", "Tug").str.replace("Barge", "Tug")

    # Reorder DataFrame to match original column order
    data = data[original_columns]
    
    print("Data successfully cleaned")

    return data