<jupyter_start><jupyter_text>Cleaning the Philippine Standard Geographic Code Dataset<jupyter_code>import pandas as pd
import xlrd
import re<jupyter_output><empty_output><jupyter_text>Import the PSGC Excel file.The Philippine Statistics Authority publishes an updated PSGC file every quarter in the form of an Excel file. The latest link is here: https://psa.gov.ph/classification/psgc/<jupyter_code>psgc_excel = pd.read_excel("data/raw/PSGC_Publication_Sept2018.xlsx",sheet_name="PSGC")
psgc_excel.to_csv('data/raw/raw-psgc.csv.gz',encoding="utf-8",compression="gzip")
psgc = pd.read_csv('data/raw/raw-psgc.csv.gz',encoding="utf-8")
psgc.info()<jupyter_output><empty_output><jupyter_text>Convert "Code" column to a string and ensure it has leading zeros and is 9-char long.<jupyter_code>psgc.loc[:,"Code"] = psgc.Code.astype(str).str.zfill(9)<jupyter_output><empty_output><jupyter_text>Drop unused columns:<jupyter_code>psgc = psgc.loc[:,['Code','Name','Inter-Level']]<jupyter_output><empty_output><jupyter_text>Normalize column names<jupyter_code>psgc.columns = ['code','location','interlevel']
psgc.head()
psgc['interlevel'].value_counts()
psgc.head()<jupyter_output><empty_output><jupyter_text>Create a duplicate of the original PSGC dataframe<jupyter_code>og_psgc = psgc.copy()<jupyter_output><empty_output><jupyter_text>Helpers We see that a lot of the locations in the PSGC have alternate names or aliases for each location contained in parentheses. Let's create a regular expression pattern that will extract these as aliases and append these as additional rows to each subset of the data.<jupyter_code>extract_in_paren = re.compile(r'\(+([^\(\)]+)\)*')
remove_in_paren = "\(.+\)"
def expand_in_paren(df):
    
    '''
    Denotes original locations
    '''
    df['original'] = True
    
    '''
    Creates a copy of the rows that contain parentheses or have aliases.
    '''
    has_paren = df[df.location.str.contains("[\(\)]")]
    has_paren['original'] = False
    
    '''
    Splits locations that contain parentheses into two elements -- what's before the parentheses, and what's within them
    Each of these items is treated as a separate possible alias and appended to the original datasete
    '''
    for i in [0,1]:
        aliases = has_paren.copy()
        aliases['location'] = has_paren.location.str.replace("\)","").str.split("\(").str.get(i).str.strip()
        df = df.append(aliases,ignore_index=True)
        
    
    return df.sort_values(by=["code","original"]).reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Clean regions<jupyter_code>regions = psgc[psgc['interlevel'] == 'Reg'].copy()<jupyter_output><empty_output><jupyter_text>Alternate names inside parens so we expand those out to a new column named `alias`.<jupyter_code>regions = expand_in_paren(regions)
regions<jupyter_output><empty_output><jupyter_text>Clean provinces<jupyter_code>provinces = psgc[psgc['interlevel'] == 'Prov'].copy()
provinces.head()<jupyter_output><empty_output><jupyter_text>Seems normal... But let's check for parens just in case:<jupyter_code>provinces[provinces['location'].str.contains('[\(\)]')]<jupyter_output><empty_output><jupyter_text>Sneaky alternate names!<jupyter_code>provinces = expand_in_paren(provinces)
provinces<jupyter_output><empty_output><jupyter_text>Clean districts<jupyter_code>districts = psgc[psgc['interlevel'] == 'Dist'].copy()
districts<jupyter_output><empty_output><jupyter_text>No one writes `NTH DISTRICT (Not a Province)` in their addresses. Let's remove these instances altogether rather than extract these as aliases.<jupyter_code>districts['location'] = (districts['location']
                         .str.replace('\(Not a Province\)', '')
                         .str.strip()
                         .str.split(',',n=1)
                         .str.get(1))
districts<jupyter_output><empty_output><jupyter_text>Clean municipalities<jupyter_code>municipalities = psgc[psgc['interlevel'] == 'Mun'].copy()<jupyter_output><empty_output><jupyter_text>Checking for alternate names in parentheses:<jupyter_code>municipalities[municipalities['location'].str.contains('[\(\)]')]<jupyter_output><empty_output><jupyter_text>In some cases the words "Capital" are contained in parentheses but these are not aliases. Safe to strip!<jupyter_code>municipalities['location'] = municipalities['location'].str.replace('\(Capital\)', '').str.strip()
municipalities
municipalities = expand_in_paren(municipalities)
municipalities.head(30)<jupyter_output><empty_output><jupyter_text>Clean cities<jupyter_code>cities = psgc[psgc['interlevel'] == 'City'].copy()
cities.head(30)<jupyter_output><empty_output><jupyter_text>Here we go with the `(Capital)` thing again.<jupyter_code>cities['location'] =  cities['location'].str.replace('\(Capital\)', '').str.strip()<jupyter_output><empty_output><jupyter_text>Checking if there are still stuff with parens:<jupyter_code>cities[cities['location'].str.contains('[\(\)]')].head()<jupyter_output><empty_output><jupyter_text>A few alterate names!<jupyter_code>cities = expand_in_paren(cities)
cities<jupyter_output><empty_output><jupyter_text>Now what about those `CITY` pre/suffixes?<jupyter_code>cities[cities['location'].str.contains('CITY')]<jupyter_output><empty_output><jupyter_text>Let's strip any prefixes of "CITY OF" and suffixes of "CITY."<jupyter_code>cities['location'] = (cities['location']
 .str.replace('^.*CITY OF', '') #stripping prefixes
 .str.strip()
 .str.replace('CITY$', '') #stripping suffixes
 .str.strip())
cities<jupyter_output><empty_output><jupyter_text>Clean sub-municipalitiesManila is the only city-slash-district that has submunicipalities.<jupyter_code>sub_municipalities = psgc[psgc['interlevel'] == 'SubMun'].copy()
sub_municipalities<jupyter_output><empty_output><jupyter_text>Nothing special!  Clean barangays<jupyter_code>barangays = psgc[psgc['interlevel'] == 'Bgy'].copy()
barangays<jupyter_output><empty_output><jupyter_text>We see alternate names again but notice the `(Pob.)` suffixes. A quick Google search shows that it's short for `Poblacion` which is used to denote the commercial and industrial center of a city. Let's stash those and add them as aliases<jupyter_code>barangays_pob = barangays[barangays.location.str.contains('\(Pob.\)')].copy()
barangays['location'] = (barangays['location']
                         .str.replace('(\(Pob\.\))', '') #totally do away with any poblacion suffixes
                         .str.strip())
barangays['location'].head(30)<jupyter_output><empty_output><jupyter_text>How many other barangay names contain parentheses?<jupyter_code>barangays[barangays.location.str.contains(r'[\(\)]')]<jupyter_output><empty_output><jupyter_text>While parentheses often contain aliases, sometimes, these are not aliases but the name of the municipality in which the barangay is located. For example, barangays in the municipality of Dumalneg have the `(Dumalneg)` denoted in parentheses. We'll go ahead and extract parenthetical names as aliases for now, but we'll later remove instances in which aliases are equal to the municipality name.<jupyter_code>barangays = expand_in_paren(barangays)<jupyter_output><empty_output><jupyter_text>Let's check for more weird characters:<jupyter_code>barangays[barangays['location'].str.contains(r'[^a-zA-Z0-9\sÑñ\(\)]')]<jupyter_output><empty_output><jupyter_text>Lets extract the strings that follow a "Brgy No. X" as aliases.<jupyter_code>pat_barangay = re.compile('(B[gr]y. No. \d+\-?\w?),? (.+)')
len(barangays[barangays.location.str.contains(pat_barangay)])
def expand_barangays(df):
    
    '''
    Denotes original locations
    '''
    df['original'] = True
    
    '''
    Creates a copy of the rows that contain barangay pattern
    '''
    matches_pattern = df[df.location.str.contains(pat_barangay)]
    matches_pattern['original'] = False
    
    '''
    Splits locations that into two elements -- Brgy No X and the name that comes after it
    Each of these items is treated as a separate possible alias and appended to the original datasete
    '''
    for i in [0,1]:
        aliases = matches_pattern.copy()
        aliases['location'] = matches_pattern.location.str.extract(pat_barangay)[i]#.str.get(i).str.strip()
        aliases['location'] = aliases['location'].str.strip()
        df = df.append(aliases,ignore_index=True)
        
    return df.sort_values(by=["code","original"]).reset_index(drop=True)
#print len(barangays)
barangays = expand_barangays(barangays)
#print len(barangays)
barangays.head()<jupyter_output><empty_output><jupyter_text>Add barangays that are `Poblacion` as aliases<jupyter_code>barangays_pob['original'] = False
barangays = barangays.append(barangays_pob, ignore_index=True)
barangays[barangays.code == '012801001']<jupyter_output><empty_output><jupyter_text>Last check!<jupyter_code>barangays.info()
barangays[barangays.code == "012812026"]<jupyter_output><empty_output><jupyter_text>ARMM: Cotabato and Isabela City<jupyter_code>armm = psgc[psgc['interlevel'].isnull()].copy()
armm
armm['location'] = armm['location'].str.replace('\(Not a Province\)', '')
armm
armm['location'] = (armm['location']
 .str.replace('^.*CITY OF', '')
 .str.strip()
 .str.replace('CITY$', '')
 .str.strip())
armm
armm['original'] = True
armm<jupyter_output><empty_output><jupyter_text>All together now<jupyter_code>merged = pd.concat([
    regions,
    provinces,
    districts,
    municipalities,
    cities,
    sub_municipalities,
    barangays,
    armm
],ignore_index=True).sort_index().fillna('')
merged.info()<jupyter_output><empty_output><jupyter_text>Are counts still correct?<jupyter_code>psgc['interlevel'].value_counts()
merged['interlevel'].value_counts()
merged.code.nunique(), psgc.code.nunique()<jupyter_output><empty_output><jupyter_text>Normalize numbers:<jupyter_code>spanish = merged[merged['location'].str.contains(' (UNO|DOS|TRES|KUATRO|SINGKO)$',case=False)].copy()
spanish
for i, s in enumerate([
    'Uno',
    'Dos',
    'Tres',
    'Kuatro',
    'Singko',
]):
    spanish['location'] = spanish['location'].str.replace(' {}$'.format(s), ' {}'.format(i + 1))
spanish
spanish['original'] = False
spanish
roman = merged[merged['location'].str.contains('\s(X{0,3})(IX|IV|V?I{0,3})$')].copy()
for i, s in enumerate('I,II,III,IV,V,VI,VII,VIII,IX,X,XI,XII,XIII,XIV,XV,XVI,XVII,XVIII,XIX,XX,XXI,XXII'.split(',')):
    roman['location'] = roman['location'].str.replace(' {}$'.format(s), ' {}'.format(i + 1))
roman['original'] = False
roman<jupyter_output><empty_output><jupyter_text>Provide alternate names for locations with President names<jupyter_code>president = merged[merged.location.str.contains('PRES\.', flags=re.IGNORECASE)].copy()
president['location'] = president['location'].str.replace('^PRES\.', 'PRESIDENT')
president['location'] = president['location'].str.replace('^Pres\.', 'President')
president['original'] = False
president<jupyter_output><empty_output><jupyter_text>Add alternative names to Metro Manila<jupyter_code>metro_manila = pd.DataFrame([{"code":"130000000","interlevel":"Reg","location":"Metro Manila","original":False},
              {"code":"130000000","interlevel":"Reg","location":"Metropolitan Manila","original":False}])

metro_manila<jupyter_output><empty_output><jupyter_text>Add Ñ -> N as an alternate name<jupyter_code>merged[merged.location.str.contains('Las Piñas',case=False)]
enye = merged[merged.location.str.contains(r'[Ññ]')].copy()
enye.head()
enye['location'] = (enye['location'].str.replace('Ñ', 'N')
                    .str.replace('ñ','n'))
enye.head()<jupyter_output><empty_output><jupyter_text>Concat the alternates to the main dataframe<jupyter_code>clean_psgc = (pd.concat([merged, spanish, roman, president], ignore_index=True)
              .sort_values('code')
              .reset_index(drop=True))<jupyter_output><empty_output><jupyter_text>Last check for weird stuff!<jupyter_code>clean_psgc[clean_psgc['location'].str.contains('[^a-zA-Z0-9 \-.,\']')]<jupyter_output><empty_output><jupyter_text>We can probably still split with `&` and `/` but this is good enough for now.  Combine the cleaned up PSGC and remove the duplicates<jupyter_code>clean_psgc.drop_duplicates(subset=['code', 'location', 'interlevel'], inplace=True)
clean_psgc.reset_index(drop=True).sort_values('code', inplace=True)<jupyter_output><empty_output><jupyter_text>Check that we have both the original name and the alternate ones<jupyter_code>clean_psgc[clean_psgc.code.str.contains('086000000')]
clean_psgc[clean_psgc.code.str.contains('012801001')]
clean_psgc.info()<jupyter_output><empty_output><jupyter_text>Cleaning out rows in which the alternate name of the barangay was just the name of its parent municipality or city<jupyter_code>clean_psgc['municipality_code'] = clean_psgc.code.str.slice(0,6)+"000"
clean_psgc['municipality'] = clean_psgc['municipality_code'].map(municipalities[municipalities.original==True].set_index('code').location)
clean_psgc.head(10)
clean_psgc['drop'] = (clean_psgc.municipality == clean_psgc.location.str.upper()) & (clean_psgc.interlevel == "Bgy")
barangay_and_muni_same_name = clean_psgc.groupby('code').drop.value_counts().unstack()[False][clean_psgc.groupby('code').drop.value_counts().unstack()[False].isnull()].index
clean_psgc.loc[clean_psgc.code.isin(barangay_and_muni_same_name),"drop"] = False
clean_psgc[clean_psgc.code == '013301034']
clean_psgc = clean_psgc.loc[clean_psgc['drop'] ==False,['code','interlevel','location','original']].reset_index(drop=True)
clean_psgc[clean_psgc.code == "133900000"]<jupyter_output><empty_output><jupyter_text>Create aliases for Legazpi and Ozamiz<jupyter_code>zplaces = clean_psgc[clean_psgc.location.str.upper().isin(["LEGAZPI","OZAMIZ"])].copy()
zplaces.loc[:,'location'] = ["LEGASPI","OZAMIS"]
zplaces
clean_psgc = clean_psgc.append(zplaces,ignore_index=True)
clean_psgc
clean_psgc.to_csv('data/processed/clean-psgc.csv.gz', index=False, compression='gzip')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15202291442892946, 'fraction_numerical': 0.007805227354099535, 'mean_word_length': 6.711761457758144, 'pattern_counts': {'":': 8, '<': 181, '<?xml version=': 0, '>': 182, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '45', 'dir': 'jupyter-structured-clean-dedup', 'id': '667831', 'license': 'MIT', 'n_tokens_mistral': 4994, 'n_tokens_neox': 4797, 'n_words': 828, 'path': 'Cleaning_PSGC.ipynb', 'repo_name': 'thinkingmachines/psgc', 'size': '13965'}