Cleanup phone numbers in dataframe column using a regex to fit a standard format

user_16578873 0
Viewed 277

I need to convert the values in my DataFrame column filled with mobile numbers that have different formats to follow one single format using RegEx.

There are 5 formats in the table and I want them all to follow the first format:

  1. +63xxxxxxxxxx #correct format
  2. 63xxxxxxxxxx #add '+'
  3. 09xxxxxxxxx #remove '0' and add '+63'
  4. 9xxxxxxxxx #add '+63'
  5. 09xx xxxx xxx #remove spaces

How do I do this? I tried using ifs and looping through the whole column of values but I keep getting a KeyError. I'm sure that there is a better way to do this so please help me.

filename = "./section2/raw-website.csv"
website_df = pd.read_csv(filename)

clean_mobile_list = []

for i in website_df['mobile']: 
    if i[0:2] == "+63":
        clean_mobile_list.append(website_df['mobile'][i])
    if i[0] == "9":
        clean_mobile = re.sub("", "+63", website_df['mobile'][i], 1)
        clean_mobile_list.append(clean_mobile)
    if i[0:1] == "09":
        clean_mobile = re.sub("0", "+63", website_df['mobile'][i], 1)
        clean_mobile_list.append(clean_mobile)
    if i[0] == "6":
        clean_mobile = re.sub("", "+", website_df['mobile'][i], 1)
        clean_mobile_list.append(clean_mobile)
    if i[4] == " ":
        clean_mobile = re.sub(" ", "", website_df['mobile'][i])
        clean_mobile_list.append(clean_mobile)

clean_mobile_list

>>>
KeyError                                  Traceback (most recent call last)
<ipython-input-42-c3202695c4eb> in <module>
      8         clean_mobile_list.append(website_df['mobile'][i])
      9     if i[0] == "9":
---> 10         clean_mobile = re.sub("", "+63", website_df['mobile'][i], 1)
     11         clean_mobile_list.append(clean_mobile)
     12     if i[0:1] == "09":

~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/series.py in __getitem__(self, key)
    851 
    852         elif key_is_scalar:
--> 853             return self._get_value(key)
    854 
    855         if is_hashable(key):

~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/series.py in _get_value(self, label, takeable)
    959 
    960         # Similar to Index.get_value, but we do not fall back to positional
--> 961         loc = self.index.get_loc(label)
    962         return self.index._get_values_for_loc(self, loc, label)
    963 

~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/indexes/range.py in get_loc(self, key, method, tolerance)
    352                 except ValueError as err:
    353                     raise KeyError(key) from err
--> 354             raise KeyError(key)
    355         return super().get_loc(key, method=method, tolerance=tolerance)
    356 

KeyError: '9087091471'

Sample data from filename:

    email            fname     lname         mobile
0   3f@hotmail.com   DNLG      JSBEXJFJCEH   +639273710560
1   ec3d@yahoo.com   VJEZSAT   TQGTVEYAL     +639287703748
2   d7a8@protonmai...QCLCMOTQ  EJRNWDKVUQVX  09176971246
3   adb74@yahoo.com  TIPOSNZB  KXTL          9161832409
pandas python regex
3 Answers

Here is a simple pipeline that does the job:

df['fixed_mobile'] = (df['mobile']
                        .str.replace('\s+', '', regex=True) # remove unwanted characters
                        .str.extract('^(?P<prefix>\+63)?0?(?P<number>\d+)') # extract prefix/number
                        .fillna({'prefix': '+63'}) # replace prefix
                        .apply(''.join, axis=1)    # join to form number
                     )

output:

             email     fname         lname         mobile   fixed_mobile
0   3f@hotmail.com      DNLG   JSBEXJFJCEH  +639273710560  +639273710560
1   ec3d@yahoo.com   VJEZSAT     TQGTVEYAL  +639287703748  +639287703748
2   d7a8@protonmai  QCLCMOTQ  EJRNWDKVUQVX    09176971246  +639176971246
3  adb74@yahoo.com  TIPOSNZB          KXTL     9161832409  +639161832409
4  adb74@yahoo.com  TIPOSNZB          KXTL   9161 832 409  +639161832409
user_163434640

You can try with:

import numpy as np
df = pd.DataFrame({'mobile': ['+6364353435345', '63654645645','0945327943', '9463945759', '0932 5432 432']})
df.mobile=np.where(df.mobile.str.startswith('63'),'+'+ df.mobile,df.mobile)
df.mobile=np.where(df.mobile.str.startswith('09'),'+63'+ 
df.mobile.str[1:],df.mobile)
df.mobile=np.where(df.mobile.str.startswith('9'),'+63'+ df.mobile,df.mobile)
df.mobile = df.mobile.str.replace(' ', '')
print (df)

Input:

           mobile
0  +6364353435345
1     63654645645
2      0945327943
3      9463945759
4   0932 5432 432

Output:

           mobile
0  +6364353435345
1    +63654645645
2    +63945327943
3   +639463945759
4   +639325432432

Or if some numbers dont start with 9:

df.mobile=np.where(df.mobile.str.startswith('63'),'+'+ 
df.mobile,df.mobile)
df.mobile=np.where(df.mobile.str.startswith('0'),'+63'+ df.mobile.str[1:],df.mobile)
df.mobile=np.where(~df.mobile.str.startswith('+63'),'+63'+ df.mobile,df.mobile)
df.mobile = df.mobile.str.replace(' ', '')
user_51789050

use this script below to clean your mobile numbers and the python package.

Give country alpha2 code like "GB","IN","US"..etc

import re
import numpy as np
import phonenumbers

NULL_VALUES = {
    np.nan,
    float("NaN"),
    "#N/A",
    "#N/A N/A",
    "#NA",
    "-1.#IND",
    "-1.#QNAN",
    "-NaN",
    "-nan",
    "1.#IND",
    "1.#QNAN",
    "<NA>",
    "N/A",
    "NA",
    "NULL",
    "NaN",
    "n/a",
    "nan",
    "null",
    "",
    None,
}

PH_REGEX = re.compile(
    # dot-atom
    r"^(\+\d{1,2}\s)?\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}$",
    re.IGNORECASE
)


def clean_phone_number(phoneNumber, country):
    """
    Clean and phone number.

    """

    if phoneNumber in NULL_VALUES:
        return None

    # remove speacial cahracters from the number
    phoneNumber = re.sub('[^A-Za-z0-9]+', '', str(phoneNumber))

    try:
        phoneNumber = phonenumbers.format_number(phonenumbers.parse(phoneNumber, country),
                                                 phonenumbers.PhoneNumberFormat.E164)
    except:
        return None

    # phoneNumber = phoneNumber.split(' ')[1:]
    # phoneNumber = ''.join(phoneNumber)

    return phoneNumber

Thumbs up if you like this script

user_103836500
Related