# -*- coding: utf-8 -*-
"""
Created on Sat Aug 28 17:57:25 2021

@author: Filip
"""

import pandas as pd

df=pd.read_csv('glassdoor_jobs.csv')

#salary column parsing 
df['hourly']=df['Salary Estimate'].apply(lambda x: 1 if 'per hour' in x.lower() else 0)
df['employer_provided']=df['Salary Estimate'].apply(lambda x: 1 if 'employer provided salary' in x.lower() else 0)

df=df[df['Salary Estimate']!='-1'] # we removed -1 valued rows in salary column

#salary parsing
salary=df['Salary Estimate'].apply(lambda x: x.split('(')[0]) # split text value in cell on '(' and keep just first part 
minus_Kd=salary.apply(lambda x: x.replace('K',' ').replace('$',''))

min_hr=minus_Kd.apply(lambda x: x.lower().replace('per hour','').replace('employer provided salary:',''))

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

#Company name text only
df['company_txt']=df.apply(lambda x: x['Company Name'] if x['Rating']<0 else x['Company Name'][:-3],axis=1)

#state field prettify
df['job_state']=df['Location'].apply(lambda x: x.split(',')[1])

#age of company 
df['same_state']=df.apply(lambda x: 1 if x['Location']==x['Headquarters'] else 0, axis=1)

df['age']=df.Founded.apply(lambda x: x if x<0 else 2021-x)

#parsing of job description (python, ect...)
df['python_yn']=df['Job Description'].apply(lambda x: 1 if 'python' in x.lower() else 0)
df['r_yn']=df['Job Description'].apply(lambda x: 1 if 'r studio' in x.lower() or 'r-studio' in x.lower() else 0)
df['spark_yn']=df['Job Description'].apply(lambda x: 1 if 'spark' in x.lower() else 0)
df['aws_yn']=df['Job Description'].apply(lambda x: 1 if 'aws' in x.lower() else 0)
df['excel_yn']=df['Job Description'].apply(lambda x: 1 if 'excel' in x.lower() else 0)

df=df.drop(['Unnamed: 0'],axis=1)

df.to_csv('salary_data_cleaned.csv', index=False)

