python dataframe income column cleanup

I would like to reiterate if this are only possible combinations of the data, then i have done and provided the below code.

Even if there is any small change you will need to edit to cater to new change. Let me explain what i have done, for all the strings that you want to replace with "" i have created a array Normrep. So, if you some more strings to be removed you can add in the elements. Also, for 'K','p/h','per hour' they need to specifically handled and the conversion needs to be done. So, if the string in your data may change then you need to handle that over here.

import pandas as pd
import numpy as np

Normrep = ['N/A', 'per Annum', 'per annum', '/year', '/Year']


def clean_income(value):
    if isinstance(value,float):
        return value
    else:
        isHourConversionNeeded = False;
        
        for i in Normrep:
            value = value.replace(i, "")

        if len(value) == 0 or value.isspace() or value == '-':  # - cannot be clubbed to array as used else where in data
            return np.nan
        elif value == '0':
            return np.nan

        # now there should not be any extra letters with K hence can be done below step
        if value.endswith('K'):
            value = value.replace('K', '000')
        elif value.endswith('p/h') or value.endswith('per hour'):
            isHourConversionNeeded = True
            value = value.replace('p/h',"")
            value = value.replace('per hour',"")

        # for to and -
        vals = value.split(' to ')
        if len(vals) != 2:
            vals = value.split(' To ')
            if len(vals) != 2:
                vals = value.split(' - ')

        if len(vals) == 2:
            return (float(vals[0]) + float(vals[1])) / 2

        try:
            a = float(value)
            if isHourConversionNeeded:
                a = a * 20 * 52
            return a
        except:
            return np.nan  # Either not proper data or need to still handle some fromat of inputs.


testData = ['26000', '45K', '-', '0', 'N/A', '2000', '30000 - 45000', '21000 per Annum', '', '21000 to 30000',
            '21000 To 50000', '43000/year', 35000.0,'12.40 p/h','12.40 per hour']
df = pd.DataFrame(testData)
print(df)

df[0] = df[0].apply(lambda x: clean_income(x))

print(df)

Here's how I will do it without all the looping.

c = ['ID','Income']
d = [
[1, 26000],  
[2, '45K'],
[3, '-'],
[4, 0],  
[5, 'N/A'],     
[6, 2000],         
[7, '30000 - 45000'],
[8, '21000 per Annum'],
[9, '50000 per annum'],
[10, '21000 to 30000'],
[11, ''],
[12, '21000 To 50000'],
[13, '43000/year'],
[14, ''],
[15, '80000/Year'],
[16, '12.40 p/h'],
[17, '12.40 per hour'],
[18, 45000.00]]

import pandas as pd
import numpy as np
df = pd.DataFrame(d,columns=c)

df['Income1'] = df['Income'].astype(str).str.lower()

df['Income1'].replace({'n/a' : '0', '':'0', '-':'0', 0:'0'}, regex=False, inplace=True)

df['Income1'].replace({'k$': '000','to': '+', '-': '+', ' per annum': '', 'p/h' : 'per hour', '/year': ''}, regex=True, inplace=True)

df['Income1'].replace(' per hour', ' * 12 * 52', regex=True, inplace=True)

df.loc[df.astype(str).Income1.str.contains('\+'),'Income1'] = '(' + df['Income1'].astype(str) + ') / 2'

df['Income1'] = df['Income1'].apply(lambda x: eval(x) if (pd.notnull(x)) else x)

df['Income1'] = (df['Income1'].fillna(0)
                 .astype(int)
                 .astype(object)
                 .where(df['Income1'].notnull()))

print (df)

The output of this will be:

    ID           Income Income1
0    1            26000   26000
1    2              45K   45000
2    3                -     NaN
3    4                0     NaN
4    5              N/A     NaN
5    6             2000    2000
6    7    30000 - 45000   37500
7    8  21000 per Annum   21000
8    9  50000 per annum   50000
9   10   21000 to 30000   25500
10  11                      NaN
11  12   21000 To 50000   35500
12  13       43000/year   43000
13  14                      NaN
14  15       80000/Year   80000
15  16        12.40 p/h    7737
16  17   12.40 per hour    7737
17  18            45000   45000