<jupyter_start><jupyter_text>4. Trend Analysis of Programming Languages (2013-2019)This separate notebook is for trend analysis and wrangling techniques neccessary to parse responses stored in a variety of formats.Similarly to the previous notebook, we will import our packages and get the dataset and schema file names.<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import glob
from IPython import display
import seaborn as sns
from collections import defaultdict, Counter
from sklearn.preprocessing import MinMaxScaler

%matplotlib inline
datasets = glob.glob("datasets/*_public.csv")
datasets
schemas = glob.glob("datasets/*_schema.csv")
schemas<jupyter_output><empty_output><jupyter_text>We will be storing the results yearly in a dictionary with the keys being year.<jupyter_code>results = {}
results<jupyter_output><empty_output><jupyter_text>2013 DatasetEarlier datasets do not have traditional headers, instead the first row of these datasets are the questions and the second row are the possible responses of the question, such as C, C, PHP etc.Therefore, instead of parsing multilevel indexes, it would be simpler to get the columns by ``idx`` - the function defined below allows this.<jupyter_code>def get_index(column, column_value, schema):
    '''
    INPUT - column - string - the name of the column we want to search
            column_value - string - the term we would like to search for
    OUTPUT - 
            idx - index - the integer index of the column containing the specified value
    '''
    idx = schema.index[schema[column].astype(str).str.contains(str(column_value))]
    return idx<jupyter_output><empty_output><jupyter_text>We can create somewhat of a schema by parsing the first two rows of the 2013 dataset to reference as a schema and fetch data by index. The columns in the schema will be defined as ``Question`` and ``Option``.<jupyter_code>def make_schema_load_dataset(data_path):
    '''
    INPUT - datapath - string - the location of the file we want to load
    OUTPUT - 
            schema, dataset - tuple(DataFrame) - two pandas dataframes, one of the schema and another, the data.
    '''
    schema = pd.read_csv(data_path, index_col=None, header=None, nrows=2, encoding="utf-8", low_memory=False).T
    schema.columns = ['Question', 'Option']
    dataset = pd.read_csv(data_path, index_col=None, header=1, encoding="utf-8", low_memory=False)
    return schema, dataset
df_2013_schema, df_2013_data = make_schema_load_dataset(datasets[0])
df_2013_schema.head(3)<jupyter_output><empty_output><jupyter_text>We obtain the index by passing the value from the dataset to find, which is string matched to our values in the schema.<jupyter_code>index = get_index('Question', 'Which of the following languages or technologies have you used significantly in the past year?', df_2013_schema)<jupyter_output><empty_output><jupyter_text>We can then slice the dataframe into a subset containing only the desired language information.<jupyter_code>df_2013_schema.iloc[index[0]:index[0]+14]
df_2013_languages = df_2013_data.iloc[:, index[0]:index[0]+14]
df_2013_languages.head(3)
df_2013_languages.shape<jupyter_output><empty_output><jupyter_text>We can retrieved a dictionary of counts and calculate the percentages using the ``percentage_dict`` function defined below, which can also remove users who didn't report any languages. The results are stored in the results dictionary, with the key ``2013``.<jupyter_code>language_dict_2013 = df_2013_languages.describe().loc['count'].to_dict()
language_dict_2013
def percentage_dict(languages, dataframe, rows=None):
    '''
    INPUT - languages - dict - the dictionary of language counts
            dataframe - DataFrame - the dataframe we would like to process
            rows - boolean - the rows we want to remove nulls from
    OUTPUT - 
            idx - index - the integer index of the column containing the specified value
    '''
    print("Rows Before: {}".format(dataframe.shape[0]))
    if rows is not None:
        dataframe = dataframe[rows].dropna(how='all', axis=0)
    else:
        dataframe = dataframe.dropna(how='all', axis=0)
        
    n = dataframe.shape[0]
    print("Rows After: {}".format(n))
    return dict((k, v/n) for k, v in languages.items())
results['2013'] = percentage_dict(language_dict_2013, df_2013_languages)
results<jupyter_output><empty_output><jupyter_text>2014 DatasetThis dataset is in the same format was the previous and therefore the same wrangling process can be applied.<jupyter_code>df_2014_schema, df_2014_data = make_schema_load_dataset(datasets[1])
df_2014_schema.head(3)
index = get_index('Question', 'Which of the following languages or technologies have you used significantly in the past year?', df_2014_schema) 
df_2014_schema.iloc[index[0]:index[0]+12]
df_2014_languages = df_2014_data.iloc[:, index[0]:index[0]+12]
df_2014_languages.head(3)
df_2014_languages.describe().loc['count'].to_dict()
results['2014'] = percentage_dict(df_2014_languages.describe().loc['count'].to_dict(), df_2014_languages)<jupyter_output>Rows Before: 7643
Rows After: 6537<jupyter_text>2015 DatasetThis dataset is stored in a different format, and the function we created to return the indexes had to be changed to a more general approach. Using partial string matching, we can get the language columns and in fact is a nicer approach as we don't need to find where to end the slice as the indexes of all language columns are returned.<jupyter_code>df_2015_schema, df_2015_data = make_schema_load_dataset(datasets[2])
indexes = get_index('Option', 'Current Lang & Tech', df_2015_schema) 
indexes
df_2015_languages = df_2015_data.iloc[:, indexes]
df_2015_languages.head(3)<jupyter_output><empty_output><jupyter_text>Each language has the prefix ``'Current Lang & Tech: ``, for example, ``'Current Lang & Tech: Android'``. Therefore, to extract the language name, we can split the strings by ``: `` and take the second component.<jupyter_code>def strip_keys(dictionary):
    '''
    INPUT - dictionary - dict - the dictionary of language counts
    OUTPUT - 
            dict - the dictionary with stripped keys, containing only the language names
    '''
    return dict((k.split(': ')[1], v) for k, v in dictionary.items())
df_2015_languages.describe().loc['count'].to_dict()
df_2015_languages_stripped = strip_keys(df_2015_languages.describe().loc['count'].to_dict())
results['2015'] = percentage_dict(df_2015_languages_stripped, df_2015_languages)<jupyter_output>Rows Before: 26086
Rows After: 22107<jupyter_text>2016 Dataset This dataset, again, has a new format. The language results are stored in one column and not across multiple columns, delimitered by a semi-colon ``;``.  Therefore I have created a new function called ``explode_results`` which splits and counts responses and then returns the count dictionary.<jupyter_code>datasets[3]
df_2016_data = pd.read_csv(datasets[3], index_col=None, header=0, encoding="utf-8", low_memory=False)
df_2016_data.head()
def explode_results(data, column):
    '''
    INPUT - data - DataFrame - the dataframe we would like to process
            column - string - the column we would like to search for
    OUTPUT - 
            dict - the resulting dictionary where languages are keys and the values are their respective counts
    '''
    results = defaultdict(int)
    for index, row in data.iterrows():
        #print(row[column])
        if row[column] is not np.nan:
            for val in row[column].split(';'):
                results[val.strip()] += 1
    return dict(results)
df_2016_languages = explode_results(df_2016_data, 'tech_do')
df_2016_languages<jupyter_output><empty_output><jupyter_text>This dictionary can be passed to the ``percentage_dict`` function.<jupyter_code>results['2016'] = percentage_dict(df_2016_languages, df_2016_data, 'tech_do')<jupyter_output>Rows Before: 56030
Rows After: 49025<jupyter_text>2017 DatasetThe following two datasets are in a new, consistent format with an accompanying schema. Similarly to 2016, they are stored in a single column and semi-colon delimitered. Therefore, our ``explode_results`` function still works appropriately.<jupyter_code>df_2017_data = pd.read_csv(datasets[4], index_col=None, header=0, encoding="utf-8", low_memory=False)
df_2017_data['HaveWorkedLanguage'].head()
df_2017_languages = explode_results(df_2017_data, 'HaveWorkedLanguage')
df_2017_languages
results['2017'] = percentage_dict(df_2017_languages, df_2017_data, 'HaveWorkedLanguage')<jupyter_output>Rows Before: 51392
Rows After: 36625<jupyter_text>2018 DatasetAlthough stored similarly, there is a slight change in the way data is stored (colon only) which meant a change was neccessary in the ``explode_results`` function. Instead of splitting on ``; `` (colon whitespace), I removed the whitespace and stripped the strings in the resulting array.<jupyter_code>df_2018_data = pd.read_csv(datasets[5], index_col=None, header=0, encoding="utf-8", low_memory=False)
df_2018_data['LanguageWorkedWith'].head()
df_2018_languages = explode_results(df_2018_data, 'LanguageWorkedWith')
df_2018_languages
results["2018"] = percentage_dict(df_2018_languages, df_2018_data, 'LanguageWorkedWith')<jupyter_output>Rows Before: 98855
Rows After: 78334<jupyter_text>Find Top X in Dictionary for Latest YearAs we are interested in the most popular languages, we will focus on the 2018 results to determine the top languages.<jupyter_code>results.keys()
results["2018"]<jupyter_output><empty_output><jupyter_text>We can use the ``Counter`` package to find the most common values in a dictionary, returning the sorted top x specified languages.<jupyter_code>count = Counter(results['2018'])
count.most_common(10)<jupyter_output><empty_output><jupyter_text>We have defined a function which will help with retrieving the trends of only the languages we are interested in.<jupyter_code>def get_all_values(top, x, res):
    '''
    INPUT - top - Counter - a counter object containing the top languages
            x - int - the top number of languages we want returned
            res - List - list of yearly result dictionaries
    OUTPUT - 
            yearly_results - list - a new list containing only the languages of interest for all years
    '''
    yearly_results = defaultdict(list)
    
    for year in res.keys():
        for k, v in top.most_common(x):
            try:
                yearly_results[k].append(res[year][k])
            except:
                yearly_results[k].append(np.nan)
            
    return yearly_results
get_all_values(count, 10, results)<jupyter_output><empty_output><jupyter_text>It is surprising that 2018 is the first year in which ``HTML``, ``CSS`` and ``Bash/Shell`` are options. These are undoubtedly popular languages, but we will remove them to show the trends of other languages which are either under represented or present in all years.<jupyter_code>del count['HTML']
del count['CSS']
del count['Bash/Shell']
dict(get_all_values(count, 10, results))<jupyter_output><empty_output><jupyter_text>With those languages removed, we can show the trends of C and Ruby, with TypeScript, although a newer entry, still has > 1 data point and has an interesting trend.We can create a DataFrame from the resulting dictionary returned by ``yearly_results``, with the indexes being the keys (years).<jupyter_code>results_df = pd.DataFrame(dict(get_all_values(count, 10, results)), index=results.keys())
results_df<jupyter_output><empty_output><jupyter_text>VisualisationWe can visualise the trends with the values in ``results_df``. A great way to demonstrate trends is by using a line graph. The function below helps us plot these trends.<jupyter_code>def plot_line(dataset, title=""):
    '''
    INPUT - dataset - pandas dataframe with the dataset of the developers survey
            x - string - the name of the column you would like to group by
            y - string - the name of the column you would like to visualise
    OUTPUT - 
            A box plot of the user specified data
    '''
    plt.style.use('seaborn-whitegrid')
    fig = plt.figure(figsize=(8, 12))
    ax = plt.axes()
    
    # Create box plot with Seaborn's default settings
    for col in dataset.columns:
        plt.plot(dataset[col], linestyle='-', marker='o')
            
    box = ax.get_position()
    ax.set_position([box.x0, box.y0, box.width * 0.8, box.height])
    ax.legend(loc='center left', bbox_to_anchor=(1, 0.5))
    ax.set(xlabel='Year', ylabel='Percentage',
       title=title)
plot_line(results_df, 'Top 10 Most Popular Languages')<jupyter_output><empty_output><jupyter_text>Adding the projection of the languages next yearIn the 2018 data, we have a column which states the languages in which a user would want to be using next year. Therefore, we can include these in our line graph.<jupyter_code>df_2018_languages_before = df_2018_languages
df_2018_data[['LanguageWorkedWith', 'LanguageDesireNextYear']].head()<jupyter_output><empty_output><jupyter_text>In earlier iterations we ignored columns who did want a language in the next year, therefore containing ``np.nan`` values. The first iteration was to process the ``LanguageDesireNextYear`` column and to increment the values in the ``results['2018']`` dictionary, if a user reports a language they are not using this year. But this inflated the values disproportionately with what the question was expecting. The question states the following:     "Which of the following programming, scripting, and markup languages have you done extensive development work in over the past year, and which do you want to work in over the next year?  (If you both worked with the language and want to continue to do so, please check both boxes in that row.)". Therefore, if a person is using a language next year that wasn't used this year, we shouldn't just increment the language count. The best way to use the projections would be to apply the same splitting and counting function on the ``LanguageDesireNextYear`` column. If this column is nan, then include their values from ``LanguageWorkedWith``, as it's unlikely they would want to quit development all together.**The function below was the first implementation of incrementing counts. I have left this code and the resulting visualisation to show the working. I have included the newer implementation below.**<jupyter_code># Deprecated Function as the results were not a representation of the question. 
def project_language_want(data, col_worked, col_want, language_counts):
    
    for index, row in data.iterrows():
        if row[col_want] is not np.nan:
            #print("Worked {} and Want {}".format(row[col_worked], row[col_want]))
            if row[col_worked] is np.nan:
                language_worked = []
            else:
                language_worked = row[col_worked].split(';')
            language_want = row[col_want].split(';')
            for val in language_want:
                if val not in language_worked:
                    #print("{} is a new language".format(val))
                    language_counts[val.strip()] += 1
        
    return language_counts

df_2018_languages_after = project_language_want(df_2018_data, 'LanguageWorkedWith', 'LanguageDesireNextYear', df_2018_languages_before)
    
df_2018_languages_after
results['2019'] = percentage_dict(df_2018_languages_after, df_2018_data, ['LanguageWorkedWith', 'LanguageDesireNextYear'])
results['2019']
results_df_projection = pd.DataFrame(dict(get_all_values(count, 10, results)), index=results.keys())
results_df_projection
plot_line(results_df_projection, 'Top 10 Most Popular Languages Projection')<jupyter_output><empty_output><jupyter_text>The correct implementation in relation to what the question was expectingRow 14 in the DataFrame below shows where a user has reported no languages they desire next year. I will assume these users will be using the languages they have reported in ``LanguagesWorkedWith``. Therefore ``Java;JavaScript;PHP;VB.NET;HTML;CSS`` will be copied from ``LanguageWorkedWith`` into ``LanguageDesireNextYear`` where the value is currently ``np.nan``.<jupyter_code>df_2018_data_filled = df_2018_data
df_2018_data_filled[['LanguageWorkedWith', 'LanguageDesireNextYear']].head(15)
df_2018_data_filled['LanguageDesireNextYear'] = df_2018_data_filled['LanguageDesireNextYear'].fillna(df_2018_data_filled['LanguageWorkedWith'])
df_2018_data_filled[['LanguageWorkedWith', 'LanguageDesireNextYear']].head(15)<jupyter_output><empty_output><jupyter_text>The effect of our wrangling is shown in line 14 in the DataFrame above. The result of filling na with another series is a powerful function in pandas and produced the result we desired.<jupyter_code>df_2019_languages_projection = explode_results(df_2018_data_filled, 'LanguageDesireNextYear')
df_2019_languages_projection
results['2019'] = percentage_dict(df_2019_languages_projection, df_2018_data_filled, 'LanguageDesireNextYear')
results_df_projection = pd.DataFrame(dict(get_all_values(count, 10, results)), index=results.keys())
results_df_projection
plot_line(results_df_projection, 'Top 10 Most Popular Languages Projection')<jupyter_output><empty_output><jupyter_text>The projects according to what the question requested, shows a noticable dip and seems abnormal, even with filled columns. This may be due to the fact people have misunderstood the question or the question being poorly worded.   5. Developer Role Salary DistributionsThe last question we are concerned with is relating to the most popular roles in 2018 and how much are the salaries for each role. We can begin by using the 2018 dataset and removing any nulls in ``DevType`` or ``ConvertedSalary`` columns and subsetting the data with just these two columns.<jupyter_code>dev_type_2018 = df_2018_data
dev_type_2018.shape
def remove_rows(dataset, cols, vals=[], values=False, nulls=True):
    '''
    INPUT - dataset - pandas dataframe with the dataset of the developers survey
            col - string - the name of the column you would like to select
            vals - list of strings - the names of the values you would like to remove
            values - boolean - true to remove values.
            nulls - boolean - remove nulls for a specified column
    OUTPUT - 
            A subset of the original data with the specified values or nulls removed
    ''' 
    if values:
        if len(vals) >= 1:
            for val in vals:
                dataset = dataset[dataset[col] != val]
    
    if nulls:
        dataset = dataset.dropna(how='any', subset = cols, axis=0)
        
    return dataset[cols]
dev_type_2018_dropped = remove_rows(dev_type_2018, ['DevType', 'ConvertedSalary'], nulls=True)
dev_type_2018_dropped.shape
dev_type_2018_dropped.head()<jupyter_output><empty_output><jupyter_text>The ``DevType`` column contains multiple selections delimitered again by a semi-colon. We can use our function from earlier to explode the strings and strip them.One value will be problematic, ``C-suite executive ()``. We need to rename this key as the string pattern match will not work in a later step due to the parenthesis.<jupyter_code>popular_roles = explode_results(dev_type_2018_dropped, 'DevType')
popular_roles['C-suite executive'] = popular_roles.pop('C-suite executive (CEO, CTO, etc.)')
popular_roles<jupyter_output><empty_output><jupyter_text>Simiarly, we can use our percentage function to calculate user base share.<jupyter_code>role_share = percentage_dict(popular_roles, dev_type_2018_dropped, 'DevType')
developer_types = list(popular_roles.keys())
developer_types<jupyter_output><empty_output><jupyter_text>To calculate the salary of each group, I firstly iterated every row and increased a count value for each language and returned a dictionary. But if I wanted to plot distributions, which would be more informative to the user, I needed to use an alternate method as opposed to just computing the mean.Therefore, I opted to subset the 2018 data by rows that contained certain roles. For example, a dataframe for all people who responded ``Database administrator``, then another dataframe for all people who responded ``DevOps specialist``, which may have overlaps with other dataframes, depending on how many roles a person reported. Each of these subsets are tagged in a new column, which can be seen below.<jupyter_code>def salary_calculation_function_merge_frames(dataframe, column, roles, role_count, salary_column):
    '''
    INPUT - dataframe - pandas dataframe with the dataset of the developers survey
            column - string - the name of the column you would like to select
            roles - list of strings - the dictionary of role names
            role_count - dict - a dictionary of role counts used for asserting subsets are correct
            salary_column - string - the name of the salary column to include in the subsets
    OUTPUT - 
            A dataframe comprising of all concatenated subsets of the original data.
    ''' 
    combined_dataframe = pd.DataFrame()
    
    for role in roles:
        role_dataframe = dataframe[dataframe[column].str.contains(role)]
        # Check the number of rows is consistent with the reported count.
        assert role_dataframe.shape[0] == role_count[role]
        
        temp_dataframe = dataframe[dataframe[column].str.contains(role)]
        temp_dataframe['Role'] = role
          
        frames = [combined_dataframe, temp_dataframe]

        combined_dataframe = pd.concat(frames)
    
    return combined_dataframe

role_dataframe = salary_calculation_function_merge_frames(dev_type_2018_dropped, 'DevType', developer_types, popular_roles, 'ConvertedSalary')
role_dataframe.head()
role_dataframe['Role'].unique()<jupyter_output><empty_output><jupyter_text>We can check the new dataframe contains all roles and is of the correct size by comparing the number of rows with the counted values in the ``popular_roles`` dictionary.<jupyter_code>assert role_dataframe.shape[0] == sum(popular_roles.values())
print("Assertion Passed")<jupyter_output>Assertion Passed<jupyter_text>Now when we have much more data than the mean, and can plot this data in a similar fashion to earlier questions.<jupyter_code>role_dataframe.groupby('Role').describe()<jupyter_output><empty_output><jupyter_text>I have used the same functions from the previous notebook to draw our plots.<jupyter_code>def plot_box(dataset, x, y, show_outliers=True):
    '''
    INPUT - dataset - pandas dataframe with the dataset of the developers survey
            x - string - the name of the column you would like to group by
            y - string - the name of the column you would like to visualise
    OUTPUT - 
            A box plot of the user specified data
    '''
    # Set figure size and style to enhance readability 
    sns.set(rc={'figure.figsize':(11.7,8.27)})
    sns.set(font_scale=1.5)
    sns.set_style("darkgrid")
    # Create box plot with Seaborn's default settings
    _ = sns.boxplot(x=x, y=y, data=dataset, showfliers=show_outliers)
    # Label the axes
    _ = plt.xlabel(x)
    _ = plt.ylabel("")
    # Show the plot
    plt.show()
plot_box(role_dataframe, 'ConvertedSalary', 'Role',  True)<jupyter_output><empty_output><jupyter_text>Again the outliers (> 1.5 * IQR) squash our data and I have therefore opted to hide them.<jupyter_code>plot_box(role_dataframe, 'ConvertedSalary', 'Role',  False)<jupyter_output><empty_output><jupyter_text>Using the same information, we can plot a bar chart to show job role popularity in 2018.<jupyter_code>def plot_bar(dataset, x, y, show_outliers=True):
    '''
    INPUT - dataset - pandas dataframe with the dataset of the developers survey
            x - string - the name of the column you would like to group by
            y - string - the name of the column you would like to visualise
    OUTPUT - 
            A box plot of the user specified data
    '''
    # Create box plot with Seaborn's default settings
    ax = dataset.plot.barh(x=x, y=y, legend=None)
    ax.set_xlabel(y)
    ax.set_ylabel("")
role_dataframe.groupby('Role').describe()<jupyter_output><empty_output><jupyter_text>We are dealing with a dataframe and not a dictionary and have therefore opted to create a new ``Percent`` column using mathematical operators on a series.<jupyter_code>roles_popularity_dataframe = pd.DataFrame(role_dataframe.groupby('Role').describe()['ConvertedSalary', 'count']).reset_index()
roles_popularity_dataframe['Percent'] = roles_popularity_dataframe['ConvertedSalary', 'count']/dev_type_2018_dropped.shape[0]
roles_popularity_dataframe = roles_popularity_dataframe.sort_values(by=['Percent'])
roles_popularity_dataframe.head()<jupyter_output><empty_output><jupyter_text>Using the plotting function from earlier, we can show the most popular languages as a percentage of the userbase.<jupyter_code>plot_bar(roles_popularity_dataframe, 'Role', 'Percent')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08363067794571623, 'fraction_numerical': 0.02497694744016357, 'mean_word_length': 4.718477762494269, 'pattern_counts': {'":': 0, '<': 135, '<?xml version=': 0, '>': 138, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '35', 'dir': 'jupyter-structured-clean-dedup', 'id': '24416', 'license': 'FTL', 'n_tokens_mistral': 7623, 'n_tokens_neox': 7004, 'n_words': 2517, 'path': 'Trend Analysis of Programming Languages and Job Roles.ipynb', 'repo_name': 'ddh4/stackoverflow-analysis', 'size': '24943'}