<jupyter_start><jupyter_text>Exploring the Where it Pays to Attend College Dataset**<NAME>** - **19 November 2018** * [Introduction](1)* [Import Modules](2)* [Exploring the First Data](3)    * [Change Columns Name](4)    * [Convert Data Type](5)    * [Visualization](6)        * [Undergraduate Major Salary](7)        * [Start Career to Middle Career](8)*  [Import Second Data](9)    * [Visualize the Empty Data](10)    * [Formulas for null values](11)    * [Change Data Type](12)    * [Fill null values with Formula](13)    * [Visualization](14)        * [Compare Schools](15)        * [School Types](16)        * [Salary Statistics](17)*  [Import Third Data](18)    * [Change Columns Name](19)    * [Convert Data Type](20)    * [Fill null values with Formula](21)    * [Visualization](22)        * [Region & School Visualization](23)        * [Average starting salary for the states](24)        * [Compare the pay for the schools](25)* [Conclusion](26) INTRODUCTION [Return Contents](0)    In this notebook, let us try and explore the data given for About college.What do we do in this notebook?* we will fill the null data with a formula.* we're going to plotly visualize the data.and **more**.  **Import Modules** [Return Contents](0)Let us first import the necessary modules.<jupyter_code>import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)
import matplotlib.pyplot as plt

# plotly
import plotly.plotly as py
from plotly.offline import init_notebook_mode, iplot
init_notebook_mode(connected=True)
# import graph objects as "go"
import plotly.graph_objs as go

# import missingno library
import missingno as msno

import os
#print(os.listdir("../input"))<jupyter_output><empty_output><jupyter_text>let's list the files in the input folder.<jupyter_code>from subprocess import check_output
print(check_output(["ls", "../input"]).decode("utf8"))<jupyter_output>degrees-that-pay-back.csv
salaries-by-college-type.csv
salaries-by-region.csv<jupyter_text>**Exploring the First Data** [Return Contents](0)firstly, let's explore the degrees that pay back.<jupyter_code>degrees_df = pd.read_csv('../input/degrees-that-pay-back.csv')
degrees_df.shape
degrees_df.head()<jupyter_output><empty_output><jupyter_text>**Change Columns Name** [Return Contents](0)I need change to coloumns name because columns name are very complex.<jupyter_code>degrees_df.columns = ['major','starting','midcareer','starttomid','mid_p10','mid_p25','mid_p75','mid_p90']<jupyter_output><empty_output><jupyter_text>let's see the columns datatype.<jupyter_code>degrees_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 50 entries, 0 to 49
Data columns (total 8 columns):
major         50 non-null object
starting      50 non-null object
midcareer     50 non-null object
starttomid    50 non-null float64
mid_p10       50 non-null object
mid_p25       50 non-null object
mid_p75       50 non-null object
mid_p90       50 non-null object
dtypes: float64(1), object(7)
memory usage: 3.2+ KB<jupyter_text>Convert Data Type [Return Contents](0)some columns need change to data type. For example, starting column is data type object but I'm going to convert data type. You must also convert other columns.<jupyter_code>someColumns = ['starting', 'midcareer', 'mid_p10', 'mid_p25', 'mid_p75', 'mid_p90']

for column in someColumns:
    degrees_df[column] = degrees_df[column].str.replace("$","")
    degrees_df[column] = degrees_df[column].str.replace(",","")
    degrees_df[column] = pd.to_numeric(degrees_df[column])
degrees_df.info()
degrees_df.head()<jupyter_output><empty_output><jupyter_text>Now, I'm going to sort the major by the starting salaries.<jupyter_code>sorted = degrees_df.sort_values('starting', ascending=False)
sorted.head()<jupyter_output><empty_output><jupyter_text>**Visualization** [Return Contents](0) Undergraduate Major Salaries Now, I converted to data type and name for columns then I am visualizing the data. First, I'm going to show you the salaries for each Undergraduate Major.<jupyter_code># Bar Chart
trace1 = go.Bar(
    x=sorted.major,
    y=sorted.starting,
    name='Starting Median Salary'
)
trace2 = go.Bar(
    x=sorted.major,
    y=sorted.midcareer,
    name='Mid-Career Median Salary'
)
trace3 = go.Bar(
    x=sorted.major,
    y=sorted.mid_p10,
    name='Mid-Career 10th Percentile Salary'
)
trace4 = go.Bar(
    x=sorted.major,
    y=sorted.mid_p25,
    name='Mid-Career 25th Percentile Salary'
)
trace5 = go.Bar(
    x=sorted.major,
    y=sorted.mid_p75,
    name='Mid-Career 75th Percentile Salary'
)
trace6 = go.Bar(
    x=sorted.major,
    y=sorted.mid_p90,
    name='Mid-Career 90th Percentile Salary'
)

data = [trace1,trace2,trace3,trace4,trace5,trace6]
layout = go.Layout(
    barmode='stack'
)

fig = go.Figure(data=data, layout=layout)
iplot(fig)<jupyter_output><empty_output><jupyter_text>Start Career to Middle Career [Return Contents](0)For Undergradute Major, I will list the salary change from start career to middle career.<jupyter_code>sorted_starttomid = degrees_df.sort_values('starttomid', ascending=False)
sorted_starttomid.head()<jupyter_output><empty_output><jupyter_text>Now I'm going to visualize this sequence.<jupyter_code># Line Chart
trace = go.Scatter(
    x = sorted_starttomid.major,
    y = sorted_starttomid.starttomid
)
layout = dict(title = 'Percent change from Starting to Mid-Career Salary',
              xaxis= dict(title= '',ticklen= 5,zeroline= False)
             )
data = [trace]
fig = go.Figure(data=data, layout=layout)
iplot(fig)<jupyter_output><empty_output><jupyter_text>**Import Second Data** [Return Contents](0)let's explore our second set of data.<jupyter_code>salariesByCollege_df = pd.read_csv('../input/salaries-by-college-type.csv')
salariesByCollege_df.shape
salariesByCollege_df.head()<jupyter_output><empty_output><jupyter_text>**Visualize the Empty Data** [Return Contents](0)Let's visualize the empty data.<jupyter_code>msno.matrix(salariesByCollege_df)
plt.show()

# Other visualization type
#msno.bar(salariesByCollege_df)
#plt.show()
salariesByCollege_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 269 entries, 0 to 268
Data columns (total 8 columns):
School Name                          269 non-null object
School Type                          269 non-null object
Starting Median Salary               269 non-null object
Mid-Career Median Salary             269 non-null object
Mid-Career 10th Percentile Salary    231 non-null object
Mid-Career 25th Percentile Salary    269 non-null object
Mid-Career 75th Percentile Salary    269 non-null object
Mid-Career 90th Percentile Salary    231 non-null object
dtypes: object(8)
memory usage: 16.9+ KB<jupyter_text>**Formulas for null values** [Return Contents](0)Totaly 38 data is null. Null data is only available in Mid-Career 10th and in Mid-Career 90th. Now, I will make a formula for null data;**For Mid-Career 10th Formula = (midcareer + mid_p25) / 3 = mid_p10****For Mid-Career 90th Formula = (mid_p75 - mid_p25) + mid_p75**These formulas give more or less results.firstly I am changing some column names.<jupyter_code>salariesByCollege_df.columns = ['schoolname', 'schooltype', 'starting', 'midcareer', 'mid_p10', 'mid_p25', 'mid_p75', 'mid_p90']
salariesByCollege_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 269 entries, 0 to 268
Data columns (total 8 columns):
schoolname    269 non-null object
schooltype    269 non-null object
starting      269 non-null object
midcareer     269 non-null object
mid_p10       231 non-null object
mid_p25       269 non-null object
mid_p75       269 non-null object
mid_p90       231 non-null object
dtypes: object(8)
memory usage: 16.9+ KB<jupyter_text>Change Data Type [Return Contents](0)let's change the data type.<jupyter_code>columns = ['starting', 'midcareer', 'mid_p10', 'mid_p25', 'mid_p75', 'mid_p90']

for column in columns:
    salariesByCollege_df[column] = salariesByCollege_df[column].str.replace("$","")
    salariesByCollege_df[column] = salariesByCollege_df[column].str.replace(",","")
    salariesByCollege_df[column] = pd.to_numeric(salariesByCollege_df[column])
# there are two from some universities.
salariesByCollege_df.schoolname.value_counts().head(25)<jupyter_output><empty_output><jupyter_text>let's see the data info.<jupyter_code>salariesByCollege_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 269 entries, 0 to 268
Data columns (total 8 columns):
schoolname    269 non-null object
schooltype    269 non-null object
starting      269 non-null float64
midcareer     269 non-null float64
mid_p10       231 non-null float64
mid_p25       269 non-null float64
mid_p75       269 non-null float64
mid_p90       231 non-null float64
dtypes: float64(6), object(2)
memory usage: 16.9+ KB<jupyter_text>**Fill null values with Formula** [Return Contents](0)let's fix some null columns.<jupyter_code># this code checks to see if the cell is empty.
if salariesByCollege_df.iloc[[1],[7]].isnull().any().any():
    print('This column is null')
columns = ['mid_p10','mid_p90']

for i in range(269):
        for column in columns:
            if column == 'mid_p10':
                if salariesByCollege_df.iloc[[i],[4]].isnull().any().any():
                    salariesByCollege_df.iloc[[i],[4]] = (salariesByCollege_df.iloc[[i],[3]].values + salariesByCollege_df.iloc[[i],[5]].values) / 3
            if column == 'mid_p90':
                if salariesByCollege_df.iloc[[i],[7]].isnull().any().any():
                    salariesByCollege_df.iloc[[i],[7]] = (salariesByCollege_df.iloc[[i],[6]].values - salariesByCollege_df.iloc[[i],[5]].values) + salariesByCollege_df.iloc[[i],[6]].values<jupyter_output><empty_output><jupyter_text>let's control it.<jupyter_code>salariesByCollege_df.head()
# Empty data, not empty.
# it worked.<jupyter_output><empty_output><jupyter_text>Visualization [Return Contents](0)     Compare Schools Now, let's compare the pay for the schools.<jupyter_code># Line Charts
# prepare data frame
df = salariesByCollege_df.sort_values('starting', ascending=False)
df = df.iloc[:55,:]
new_df = df.reset_index()

# Creating trace1
trace1 = go.Scatter(
                    x = new_df.index.values,
                    y = df.starting,
                    mode = "lines",
                    name = "Starting Median Salary",
                    marker = dict(color = 'rgba(324, 97, 98, 0.8)'),
                    text= df.schoolname)
# Creating trace2
trace2 = go.Scatter(
                    x = new_df.index.values,
                    y = df.midcareer,
                    mode = "lines+markers",
                    name = "Mid-Career Median Salary",
                    marker = dict(color = 'rgba(61, 100, 94, 0.8)'),
                    text= df.schoolname)
data = [trace1, trace2]
layout = dict(title = 'Starting Median Salary and Mid-Career Median Salary',
              xaxis= dict(title= 'Universities Rank',ticklen= 5,zeroline= False)
             )
fig = dict(data = data, layout = layout)
iplot(fig)<jupyter_output><empty_output><jupyter_text>School Types [Return Contents](0)Let's visualize how many school types there are.<jupyter_code># Basic Bar Chart
data = [go.Bar(
            x=salariesByCollege_df.schooltype.value_counts().index,
            y=salariesByCollege_df.schooltype.value_counts().values
    )]
layout = {"title": "How many school types are there?", 
          "xaxis": {"title": "School Type", }, 
          "yaxis": {"title": "Value"}}
fig = go.Figure(data=data, layout=layout)
iplot(fig)<jupyter_output><empty_output><jupyter_text>Salary Statistics [Return Contents](0)<jupyter_code># Colored Box Plot
# prepare data frame
df1 = salariesByCollege_df.sort_values('starting', ascending=False)

trace0 = go.Box(
    y=df1.starting,
    name = 'Starting Career Median Salary',
    marker = dict(
        color = 'rgb(214, 12, 140)',
    )
)
trace1 = go.Box(
    y=df1.midcareer,
    name = 'Mid-Career Median Salary',
    marker = dict(
        color = 'rgb(0, 128, 128)',
    )
)
trace2 = go.Box(
    y=df1.mid_p10,
    name = 'Mid-Career 10th Percentile Salary',
    marker = dict(
        color = 'rgb(12, 15, 75)',
    )
)
trace3 = go.Box(
    y=df1.mid_p25,
    name = 'Mid-Career 25th Percentile Salary',
    marker = dict(
        color = 'rgb(12, 100, 190)',
    )
)
trace4 = go.Box(
    y=df1.mid_p75,
    name = 'Mid-Career 75th Percentile Salary',
    marker = dict(
        color = 'rgb(12, 128, 128)',
    )
)
trace5 = go.Box(
    y=df1.mid_p90,
    name = 'Mid-Career 90th Percentile Salary',
    marker = dict(
        color = 'rgb(12, 12, 140)',
    )
)
data = [trace0, trace1,trace2,trace3,trace4,trace5]
iplot(data)<jupyter_output><empty_output><jupyter_text>**Import Third Data** [Return Contents](0)let's import last data set.<jupyter_code>salary_region_df = pd.read_csv('../input/salaries-by-region.csv')
salary_region_df.shape
salary_region_df.head()<jupyter_output><empty_output><jupyter_text>**Change Columns Name** [Return Contents](0)There are empty data as above. But first we have to change the name and data type. Let's the start.<jupyter_code>salary_region_df.columns = ['schoolname','region','starting','midcareer','mid_p10','mid_p25','mid_p75','mid_p90']
salary_region_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 320 entries, 0 to 319
Data columns (total 8 columns):
schoolname    320 non-null object
region        320 non-null object
starting      320 non-null object
midcareer     320 non-null object
mid_p10       273 non-null object
mid_p25       320 non-null object
mid_p75       320 non-null object
mid_p90       273 non-null object
dtypes: object(8)
memory usage: 20.1+ KB<jupyter_text>**Convert Data Type** [Return Contents](0)let's the change data type.<jupyter_code>regionColumns = ['starting', 'midcareer', 'mid_p10', 'mid_p25', 'mid_p75', 'mid_p90']

for column in regionColumns:
    salary_region_df[column] = salary_region_df[column].str.replace("$","")
    salary_region_df[column] = salary_region_df[column].str.replace(",","")
    salary_region_df[column] = pd.to_numeric(salary_region_df[column])
salary_region_df.info()
salary_region_df.head()<jupyter_output><empty_output><jupyter_text>**Fill null values with Formula** [Return Contents](0)I will use the same formula we used above here.<jupyter_code>columns = ['mid_p10','mid_p90']

for i in range(269):
        for column in columns:
            if column == 'mid_p10':
                if salary_region_df.iloc[[i],[4]].isnull().any().any():
                    salary_region_df.iloc[[i],[4]] = (salary_region_df.iloc[[i],[3]].values + salary_region_df.iloc[[i],[5]].values) / 3
            if column == 'mid_p90':
                if salary_region_df.iloc[[i],[7]].isnull().any().any():
                    salary_region_df.iloc[[i],[7]] = (salary_region_df.iloc[[i],[6]].values - salary_region_df.iloc[[i],[5]].values) + salary_region_df.iloc[[i],[6]].values
salary_region_df.head()<jupyter_output><empty_output><jupyter_text>Visualization [Return Contents](0)     Region & School Visualization Let's the visualization.<jupyter_code># Bubble Charts
trace0 = go.Scatter(
    x=salary_region_df.region.value_counts().index,
    y=salary_region_df.region.value_counts().values,
    mode='markers',
    marker=dict(
        color=['rgb(93, 100, 210)', 'rgb(255, 144, 14)',
               'rgb(44, 160, 101)', 'rgb(255, 65, 54)'],
        opacity=[1, 0.8, 0.6, 0.4],
        size=[100, 80, 60, 40,20],
    )
)
layout = {"title": "How many region are there and how many schools are there?", 
          "xaxis": {"title": "Region", }, 
          "yaxis": {"title": "Number of Schools"}}
data = [trace0]
fig = go.Figure(data=data, layout=layout)
iplot(fig)<jupyter_output><empty_output><jupyter_text>**Average starting salary for the states** [Return Contents](0)let's find the average starting salary for the states.<jupyter_code>california_starting = 0
western_starting = 0
midwestern_starting = 0
southern_starting = 0
northeastern_staring = 0

for i in range(269):
    if salary_region_df.iloc[[i],[1]].values == 'Northeastern':
        northeastern_staring += salary_region_df.iloc[[i],[2]].values
    if salary_region_df.iloc[[i],[1]].values == 'Southern':
        southern_starting += salary_region_df.iloc[[i],[2]].values
    if salary_region_df.iloc[[i],[1]].values == 'Midwestern':
        midwestern_starting += salary_region_df.iloc[[i],[2]].values
    if salary_region_df.iloc[[i],[1]].values == 'Western':
        western_starting += salary_region_df.iloc[[i],[2]].values
    if salary_region_df.iloc[[i],[1]].values == 'California':
        california_starting += salary_region_df.iloc[[i],[2]].values
        
northeastern_staring = int(northeastern_staring/100)
southern_starting = int(southern_starting/79)
midwestern_starting = int(midwestern_starting/71)
western_starting = int(western_starting/42)
california_starting = int(california_starting/28)


data = {'region' : ['Northeastern', 'Southern', 'Midwestern', 'Western','California'],
        'startingmed': [northeastern_staring,southern_starting,midwestern_starting,western_starting,california_starting]}
datas = pd.DataFrame(data, columns=['region','startingmed'])
datas<jupyter_output><empty_output><jupyter_text>Let's visualize this datas.<jupyter_code># Customizing Individual Bar Colors
trace0 = go.Bar(
    x=datas.region.values,
    y=datas.startingmed.values,
    marker=dict(
        color=['rgba(24,158,242,1)', 'rgba(44,167,244,1)',
               'rgba(134,185,216,1)', 'rgba(50,104,137,1)',
               'rgba(47,72,88,1)']),
)

data = [trace0]
layout = go.Layout(
    title='According to the state, average starting salary',
    xaxis= {"title": "Region", }, 
    yaxis= {"title": "Average Starting Salary"}
)

fig = go.Figure(data=data, layout=layout)
iplot(fig)<jupyter_output><empty_output><jupyter_text>**Compare the pay for the schools** [Return Contents](0)Now, let's compare the pay for the schools.<jupyter_code># Line Charts
# prepare data frame
df = salary_region_df.sort_values('starting', ascending=False)
df = df.iloc[:55,:]
new_df = df.reset_index()

# Creating trace1
trace1 = go.Scatter(
                    x = new_df.index.values,
                    y = df.starting,
                    mode = "lines",
                    name = "Starting Median Salary",
                    marker = dict(color = 'rgba(16, 112, 2, 0.8)'),
                    text= df.schoolname)
# Creating trace2
trace2 = go.Scatter(
                    x = new_df.index.values,
                    y = df.midcareer,
                    mode = "lines+markers",
                    name = "Mid-Career Median Salary",
                    marker = dict(color = 'rgba(80, 26, 80, 0.8)'),
                    text= df.schoolname)
data = [trace1, trace2]
layout = dict(title = 'Starting Median Salary and Mid-Career Median Salary',
              xaxis= dict(title= 'Universities Rank',ticklen= 5,zeroline= False)
             )
fig = dict(data = data, layout = layout)
iplot(fig)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13142887040234397, 'fraction_numerical': 0.03672892795479517, 'mean_word_length': 3.9866944951734933, 'pattern_counts': {'":': 12, '<': 113, '<?xml version=': 0, '>': 113, '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': '28', 'dir': 'jupyter-structured-clean-dedup', 'id': '512937', 'license': 'Apache-2.0', 'n_tokens_mistral': 7068, 'n_tokens_neox': 6304, 'n_words': 1513, 'path': 'EDA of Salaries by college, region, and major.ipynb', 'repo_name': 'mrabdullahsahin/data-analysis-data-visualization', 'size': '19121'}