<jupyter_start><jupyter_text>Introduction 1.Business UnderstandingI'm intested in daba analyst job, and I want to know about job demand.There are three questions I want to learn about:1.Which direction do data analysts get paid more?2.What kinds of skills I need to master?3.Should I go to big-size company if taking into account wages?  2.Data UnderstandingI choose a dataset from  Kaggle This dataset contains more than 2000 job listing for data analyst positions. If you are looking for a data analyst job, you could obtain 'Job Title', 'Salary Estimate', 'Job Description', 'Rating', 'Company Name', 'Location', 'Headquarters', 'Size', 'Founded','Type of ownership', 'Industry', 'Sector', 'Revenue', 'Competitors','Easy Apply'information. You could filter what kind of job you could apply.Move these questions to this dataset:1.Which direction do data analysts get paid more?This question would use 'Salary Estimate' and 'Sector' columns.2.What kinds of skills I need to master?This question would use 'Job Description' column.3.Should I go to big-size company if taking into account wages?This question would use 'Size','Salary Estimate','Rating' and 'Revenue' columns.<jupyter_code># read in libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
from collections import Counter
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split
from sklearn.metrics import r2_score
import warnings 
warnings.filterwarnings("ignore")
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split
from sklearn.metrics import r2_score
# load dataset
df = pd.read_csv('./Dataset/DataAnalyst.csv',usecols=['Job Title','Salary Estimate','Job Description','Size','Rating','Sector','Revenue'])

df.head()
# 1)get columns and rows of dataset
df.shape<jupyter_output><empty_output><jupyter_text>This dataset has 2253 rows and 6 columns<jupyter_code># 2)check missing value in columns
df.isnull().sum()<jupyter_output><empty_output><jupyter_text>There is no missing values in df.<jupyter_code># 3)check abnormal values one by one
df['Job Title'].unique()<jupyter_output><empty_output><jupyter_text>There are no obvious abnormal values for 'Job Title' column<jupyter_code>df['Salary Estimate'].unique()<jupyter_output><empty_output><jupyter_text>'-1' value looks a little abnormal in 'Salary Estimate' column, it may be the missing value.<jupyter_code>df['Job Description'].unique()<jupyter_output><empty_output><jupyter_text>There are no obvious abnormal values for 'Job Description' column<jupyter_code>df['Rating'].unique()<jupyter_output><empty_output><jupyter_text>Generally speaking, rating is equal or greater than zero. So -1 may be missing value.<jupyter_code>df['Size'].unique()<jupyter_output><empty_output><jupyter_text>'-1' value looks like missing value in 'Size' column.<jupyter_code>df['Sector'].unique()<jupyter_output><empty_output><jupyter_text>'-1' value looks a little abnormal in 'Sector' column, it may be the missing value.<jupyter_code>df['Revenue'].unique()<jupyter_output><empty_output><jupyter_text>'-1' value looks a little abnormal in 'Revenue' column, it may be the missing value. So after check every column unique values, there are two kinds of abnormal values: -1 and '-1'. They may be missing values, so replace them to nan in the cleaning process.  3.Prepare DataAccording to data understanding, the columns might be used are 'Job Title','Salary Estimate','Job Description','Size','Rating','Sector' and 'Revenue'. Now wrangling and cleaning process is starting.<jupyter_code># replace -1 or '-1' into nan for every column
def replace_abnormal(df):
    """
    replace -1 or '-1' into nan for every column
    
    INPUT:
    df-the pandas dataframe need to be cleaned
    
    OUTPUT:
    None

    """
    for col in df.columns:
        if not df[df[col] == -1].empty:
            df[col] = df[col].replace(-1,np.nan)
        elif not df[df[col] == '-1'].empty:
            df[col] = df[col].replace('-1',np.nan)
    
# run this function
replace_abnormal(df)<jupyter_output><empty_output><jupyter_text>After running function 'replace_abnormal', there is no '-1' or -1 values. Becasue different columns may correspond to different processing methods, so I would check columns one by one in the next steps.<jupyter_code># df.columns[0]] 
df['Job Title'].value_counts(dropna=False) # some job title could be classified into one type
df['Job Title'] = df['Job Title'].str.lower() # converts fields to lowercase for easy categorization: such as data analyst and DATA ANALYST are the same 
df['Job Title'].value_counts()[:50]   # checkout the top 50 job title 
# Unify the duplicate type
df = df.replace('sr. data analyst','senior data analyst')
df = df.replace('sr data analyst','senior data analyst')
df = df.replace('data analyst junior','junior data analyst')  
df = df.replace('quality data analyst','data quality analyst')
df = df.replace('business data analyst','data business analyst')
df = df.replace('data reporting analyst','reporting data analyst')
df['Job Title'].value_counts()[:50]<jupyter_output><empty_output><jupyter_text>1.'Job Title' column has no missing value but many titles could be classfied, such as 'data analyst' and 'DATA ANALYST', 'sr. data analyst','senior data analyst'.So I convert fields to lowercase for easy categorization and then unify these kinds values.2.I choose the top 50 type titles and unify the duplicate type<jupyter_code># df.columns[1]
df['Salary Estimate'].value_counts(dropna=False)
df['Salary Estimate'].isnull().mean() * 100 # only exists 1 missing value, so I decide to drop this data
df[df['Salary Estimate'].isnull()]  # the row that contain missing value
df.drop(index=2149,inplace=True)<jupyter_output><empty_output><jupyter_text>For 'Salary Estimate' column, the missing proportion is too tiny and there is much missing values for this row so I drop this row .<jupyter_code># split salary estimate into lower and upper columns
s = df["Salary Estimate"].str.split(" ",n= 1,expand=True)
sr = s[0].str.split('-',expand=True,n=1)
df['lower_salary'] = sr[0].str.replace('$','')
df['lower_salary'] = df['lower_salary'].str.replace('K','000')
df['upper_salary'] = sr[1].str.replace('$','')
df['upper_salary'] = df['upper_salary'].str.replace('K','000')

# convert data type
df['upper_salary'] = df['upper_salary'].astype('int')
df['lower_salary'] = df['lower_salary'].astype('int')

# add new column 'average_salary'
df['average_salary'] = (df['upper_salary'] + df['lower_salary']) / 2

# delete column
df = df.drop(columns=['Salary Estimate', 'upper_salary', 'lower_salary'])<jupyter_output><empty_output><jupyter_text>Split 'Salary Estimate' column into 'upper_salary' column and 'lower_salary' and convert into int type. After doing these, add a 'average_salary' column and drop relate column.<jupyter_code># df.columns[2]
df['Job Description'].value_counts(dropna=False)
df['Job Description'].isnull().mean()   # no missing values<jupyter_output><empty_output><jupyter_text>'Job Description' column has neither abnormal values or missing values.<jupyter_code># df.columns[3]
df['Rating'].unique() 
print(df['Rating'].isnull().mean())  # missing proportion is 12%
df['Rating'] = df['Rating'].fillna(df['Rating'].mean()) # fill nan into the mean<jupyter_output>0.12078152753108348<jupyter_text>There is 12% missing values for 'Rating' column. Fill nan into the column mean.<jupyter_code># df.columns[4]
df['Size'].unique()
# fill nan into Unkon
df['Size'] = df['Size'].fillna('Unknown')
df['Size'].unique()<jupyter_output><empty_output><jupyter_text>For 'Size' column, fill nan into 'Unknown'.<jupyter_code># df.columns[5]
df['Sector'].unique()
print(df['Sector'].isnull().mean())
df['Sector'] = df['Sector'].fillna('Unknown')<jupyter_output>0.15674955595026643<jupyter_text>The mission proportion for 'Sector' column is 16%, fill nan into 'Unknown'.<jupyter_code># df.columns[6]
df['Revenue'].unique()
# replace billion to million
df['Revenue'] = df['Revenue'].fillna('Unknown / Non-Applicable')
df = df.replace('$1 to $2 billion (USD)','$1000 to $2000 million')
df = df.replace('$2 to $5 billion (USD)','$2000 to $5000 million')
df = df.replace('$5 to $10 billion (USD)','$5000 to $10000 million')
df = df.replace('$10+ billion (USD)','more than $10000 million')
df = df.replace('$500 million to $1 billion (USD)','$500 to $10000 million')
df['Revenue'] = df['Revenue'].str.split('(',expand=True)[0]
df['Revenue'].value_counts()<jupyter_output><empty_output><jupyter_text>For 'Revenue' column, I fill nan ito 'Unknown / Non-Applicable' and unify units into millions.<jupyter_code># Now, the missing values for all columns
print(df.isnull().mean().sort_values() * 100)<jupyter_output>Job Title          0.0
Job Description    0.0
Rating             0.0
Size               0.0
Sector             0.0
Revenue            0.0
average_salary     0.0
dtype: float64<jupyter_text>The basic cleaning process is done.Get ride of abnormal values.  4.Analysis process Q1.Which direction do data analysts get paid more?<jupyter_code># group the dataset by 'Sector'
group_job = df.groupby('Sector')

# create a new df to store grouped data
new_df = pd.DataFrame()
new_df['average_salary'] = group_job.average_salary.mean()
new_df = new_df.sort_values(by=['average_salary'],ascending=True)
display(new_df[:5]) # top 5 sector
# visilization
plt.figure(figsize=(20, 8), dpi=80)
new_df['average_salary'].plot.barh();
plt.title('Different sector with high salary',fontsize=28);
plt.xlabel('Estimate average salary',fontsize=20);
plt.ylabel('Sector',fontsize=20);<jupyter_output><empty_output><jupyter_text>Answer 1:From the above data, 'Information Technology', 'Accounting&Legal', 'Art, Entertainment & Recreation', 'Real Estate' and 'Biotech & Pharmaceuticals' are top 5 sectors that data analysts might earn relative higher salary in. If someone considers to be a data analyst, he/she could look for job opportunities in these sector. Q2.What kinds of skills I need to master?<jupyter_code># come up with common skills
possible_skills = ['python','sql','git','gitHub','coding','programming skills','problem-solving ability','logical reasoning skills','sas','stata', 'word','powerpoint',
         'excel','access','bi','tableau','communication skills','multitask','visualization','analytical skills','modeling','mindmapping','microsoft office'
         'organizational skills']
df['Job Description'] = df['Job Description'].str.lower()
def total_count(df,col, look_for):
    """
    INPUT:
    df - the pandas dataframe you want to search
    col - the column name you want to look through
    look_for - a list of strings you want to search for in each row of df[col]
    OUTPUT:
    new_df - a dataframe of each look_for with the count of how often it shows up
    """
    from collections import defaultdict
    new_df = defaultdict(int)
    for description in df[col]:
        for skill in look_for:
            if skill in description:
                new_df[skill] += 1
    new_df = pd.DataFrame(pd.Series(new_df)).reset_index()
    new_df.columns = ['skill', 'count']
    new_df.sort_values('count', ascending=False, inplace=True)
    return new_df


skills_df = total_count(df,'Job Description',possible_skills)
display(skills_df)
# merge rows
skills_df['count'][3] = skills_df['count'][3] + skills_df['count'][18]
skills_df['count'][14] = skills_df['count'][14] + skills_df['count'][13]
skills_df['skill'][14] = 'visualization(tableau+)'
skills_df['count'][16] = skills_df['count'][16] + skills_df['count'][15]
skills_df['skill'][16] = 'analytical skills(modeling+)'

# drop useless rows
skills_df.drop(index=[18,13,15],inplace=True)

# top 15 skills
skills_df.sort_values('count', ascending=False, inplace=True)
skills_df[:15]
x = list(skills_df[:15]['skill'])
y = skills_df[:15]['count']
plt.figure(figsize=(10,4))
plt.barh(range(len(x)), y, height=0.3, color="orange"); 
plt.yticks(range(len(x)), x);
plt.ylabel('skills',fontsize=15);
plt.xlabel('count',fontsize=15);
plt.title('Top 15 kinds skills required',fontsize=20);<jupyter_output><empty_output><jupyter_text>Answer 2:skills required in job descriptions: BI,SQL,Excel,Communication skills,Python/R,Visualization(Tableau,or may be other tools),Access,Analytical sills(Modeling,etc.),Statistical software,Office software,Coding.These are the most mentioned skills.  5.Data Modeling Q3.Should I go to big-size company if taking into account wages?Think about it in terms of  'Size','Salary','Rating' and 'Revenue' features<jupyter_code># create a dataframe to store group data
df_groupby = pd.DataFrame()

# group by 'Revenue' and 'Size' and calculate the mean for 'Rating' and 'average_salary'
df_groupby['Rating'] = df.groupby(['Revenue','Size'])['Rating'].mean()
df_groupby['average_salary'] = df.groupby(['Revenue','Size'])['average_salary'].mean()

df_groupby = df_groupby.reset_index()
df_groupby = df_groupby.set_index('Revenue')
df_groupby.columns = ['size','avarage_rating','average_salary']
df_groupby<jupyter_output><empty_output><jupyter_text>Create a new dataframe: df_groupby Firstly, I group by revenue and size for df and calculate the average_rating and average_salary for every group. Then assign these values to every group.Then convert index 'Size' to 'size' column.<jupyter_code># creat new binary columns for categorical variable 'Size'
df_groupby = pd.get_dummies(df_groupby)
df_groupby
# the correlation among variables
plt.figure(figsize=(20,8))
sns.heatmap(df_groupby.corr(), annot=True, fmt='.2f');<jupyter_output><empty_output><jupyter_text>The correlations are weak from the picture.<jupyter_code>df_groupby.columns
# LinearRegression
X = df_groupby[['avarage_rating','size_1 to 50 employees',
       'size_10000+ employees', 'size_1001 to 5000 employees',
       'size_201 to 500 employees', 'size_5001 to 10000 employees',
       'size_501 to 1000 employees', 'size_51 to 200 employees',
       'size_Unknown']]
y = df_groupby['average_salary']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = .30, random_state=42)

# instantiate
lm_model = LinearRegression(normalize=True) 
# fit training
lm_model.fit(X_train, y_train) 
# predict test data
y_hat = lm_model.predict(X_test)
# score model on test data
r2_score(y_test,y_hat)<jupyter_output><empty_output><jupyter_text>Answer 3:The answer is no. There is little linear correlation between salary and company size. Small companies are higher paid than big ones sometimes.  6.Evaluate the Results This r2_score 0.09 is higher than last modeling(r2_score: -0.002). Put more features might enhance the model.<jupyter_code># the last submission code about modeling:

# df = df.replace('1 to 50 employees',1)
# df = df.replace('51 to 200 employees',2)
# df = df.replace('201 to 500 employees',3)
# df = df.replace('501 to 1000 employees',4)
# df = df.replace('1001 to 5000 employees',5)
# df = df.replace('5001 to 10000 employees',6)
# df = df.replace('10000+ employees',7)
# df.dropna(inplace=True)
# X = df[['Size']]
# y = df['average_salary']
# X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = .30, random_state=42)
# lm_model = LinearRegression(normalize=True) 
# lm_model.fit(X_train, y_train) 
# y_hat = lm_model.predict(X_test)
# r2_score(y_test,y_hat)   -0.0020905270060089798<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13066337790471244, 'fraction_numerical': 0.025769180838634297, 'mean_word_length': 6.012744651797906, 'pattern_counts': {'":': 0, '<': 98, '<?xml version=': 0, '>': 98, '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': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '394968', 'license': 'FTL', 'n_tokens_mistral': 5241, 'n_tokens_neox': 4885, 'n_words': 1456, 'path': 'project_Data_analysis.ipynb', 'repo_name': 'shuini888/Data_analysis', 'size': '15406'}