<jupyter_start><jupyter_text>Things to do* salary parsing* company name text only* state field* age of company* parsing of job description (python, etc.)<jupyter_code>df.shape
# Number of missing values in the dataframe
df.isnull().sum()
df.dtypes<jupyter_output><empty_output><jupyter_text>Salary Parsing<jupyter_code>(df['Salary Estimate'] == "-1").sum()
# remove the rows containing "-1"
df = df[df['Salary Estimate'] != "-1"]
df.shape
# use [regex or lamda functions] to remove symbols and text in Salary Estimate

# lamda is like a normal function is one line of code
salary = df['Salary Estimate'].apply(lambda x: x.split('(')[0]) 
# split till '(', and then take the 1st element of the list
salary
# remove the K's and $ sign
minus_kd = salary.apply(lambda x: x.replace('K','').replace('$',''))
minus_kd
# some salary estimates are *perhour and *employer provided. Lets make them into new columns

df['hourly'] = df['Salary Estimate'].apply(lambda x: 1 if 'per hour' in x.lower() else 0) # ternary statement - if else in one line
df['employer provided'] = df['Salary Estimate'].apply(lambda x: 1 if 'employer provided salary' in x.lower() else 0)
df.head()
# Remove 'perhour' and 'employer provided' from Salary Estimates since we have columns for them now
min_hr = minus_kd.apply(lambda x: x.lower().replace('per hour','').replace('employer provided salary:',''))

if 'per hour' in min_hr and 'employer provided salary:' in min_hr:
    print(True)
else:
    print(False)
# Split salary -> minSalary-maxSalary into separate columns
# split --> [0]-[1]
df['min_salary'] = min_hr.apply(lambda x: int(x.split('-')[0]))
df['max_salary'] = min_hr.apply(lambda x: int(x.split('-')[1]))
df['avg_salary'] = (df.min_salary + df.max_salary)/2
df.head()<jupyter_output><empty_output><jupyter_text>Company name text only<jupyter_code># get company name based on ratings as well (since some ratings have -1),  remove ratings from company name - 3 char long
# axis=1 --> each columns as a bulk

# [:-4] --> take all data other than last 4, which are \n + ratings
df['company_txt'] = df.apply(lambda x : x['Company Name'] if x['Rating']<0 else x['Company Name'][:-4], axis=1 )<jupyter_output><empty_output><jupyter_text>State Field<jupyter_code># Take the shortform of the states
df['job_state'] = df['Location'].apply(lambda x: x.split(',')[1])

# find how many jobs are there in each state --> using value_counts()
df.job_state.value_counts()
# Find out if the actual job is at the company's headquarter location

df['same_state'] = df.apply(lambda x : 1 if x.Location == x.Headquarters else 0, axis=1)<jupyter_output><empty_output><jupyter_text>Age of company<jupyter_code># subtract age of company from current year

df['age'] = df.Founded.apply(lambda x : x if x < 1 else 2021  - x)
df.head()<jupyter_output><empty_output><jupyter_text>Parsing of job description (python, excel, etc.)<jupyter_code># find how many jobs require python
df['python_yn'] = df['Job Description'].apply(lambda x: 1 if 'python' in x.lower() else 0)
df.python_yn.value_counts()
# r studio
df['r_yn'] = df['Job Description'].apply(lambda x: 1 if 'r studio' in x.lower() else 0)
df.r_yn.value_counts()
# spark
df['spark'] = df['Job Description'].apply(lambda x: 1 if 'r studio' in x.lower() else 0)
df.python_yn.value_counts()
# aws
df['aws'] = df['Job Description'].apply(lambda x: 1 if 'aws' in x.lower() else 0)
df.aws.value_counts()
# excel
df['excel'] = df['Job Description'].apply(lambda x: 1 if 'excel' in x.lower() else 0)
df.excel.value_counts()
df.columns
df_final = df.drop(['Unnamed: 0'], axis=1)
df_final.head()<jupyter_output><empty_output><jupyter_text>Other things to do* Job title and seniority * Fix state Los Angeles* Job description length* Competitor count* hourly wage to annual  Job title and seniority<jupyter_code>def title_simplifier(title):
    if 'data scientist' in title.lower():
        return 'data scientist'
    elif 'data engineer' in title.lower():
        return 'data engineer'
    elif 'analyst' in title.lower():
        return 'analyst'
    elif 'machine learning' in title.lower():
        return 'mle'
    elif 'manager' in title.lower():
        return 'manager'
    elif 'director' in title.lower():
        return 'director'
    else:
        return 'na'
    
def seniority(title):
    if 'sr' in title.lower() or 'senior' in title.lower() or 'sr.' in title.lower() or 'lead' in title.lower() or 'principal' in title.lower():
            return 'senior'
    elif 'jr' in title.lower() or 'jr.' in title.lower():
        return 'jr'
    else:
        return 'na'
df['job_simp'] = df['Job Title'].apply(title_simplifier) # apply a custom function to values of a column|
df['job_simp'].value_counts()
df['seniority'] = df['Job Title'].apply(seniority)
df['seniority'].value_counts()<jupyter_output><empty_output><jupyter_text>Fix state Los Angeles<jupyter_code>df['job_state']= df.job_state.apply(lambda x: x.strip() if x.strip().lower() != 'los angeles' else 'CA')
df.job_state.value_counts()<jupyter_output><empty_output><jupyter_text>Job description length<jupyter_code>df['desc_len'] = df['Job Description'].apply(lambda x: len(x)) # loop through each row in column, and find len of each row
df['desc_len']<jupyter_output><empty_output><jupyter_text>Competitor count<jupyter_code># len of array after split
df['num_comp'] = df['Competitors'].apply(lambda x : len(x.split(',')) if x!=-1 else 0)
df['num_comp']<jupyter_output><empty_output><jupyter_text>hourly wage to annual<jupyter_code># some jobs pay hourly, conver them to annual

df[['hourly','min_salary','max_salary']] # group 3 columns and display as a dataframe
df['min_salary'] = df.apply(lambda x : x.min_salary*2 if x.hourly==1 else x.min_salary,axis=1)
df['max_salary'] = df.apply(lambda x : x.max_salary*2 if x.hourly==1 else x.max_salary,axis=1)
df[df.hourly == 1][['hourly','min_salary','max_salary']]
df = df.drop(['Unnamed: 0'], axis = 1)
# convert dataframe to a webframe
df.to_html("data_cleaned.html")
df.to_csv("salary_data_cleaned.csv", index=False)
# index=False --> avoid getting unwanited index column<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14943655071043607, 'fraction_numerical': 0.008982524906091785, 'mean_word_length': 5.198380566801619, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 0, '>': 51, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '44471', 'license': 'MIT', 'n_tokens_mistral': 2164, 'n_tokens_neox': 2104, 'n_words': 628, 'path': 'data_cleaning.ipynb', 'repo_name': 'Jawwad-Fida/Salary-Estimator', 'size': '6123'}