<jupyter_start><jupyter_text>![Callysto.ca Banner](https://github.com/callysto/curriculum-notebooks/blob/master/callysto-notebook-banner-top.jpg?raw=true)<jupyter_code>%%html

<script>
  function code_toggle() {
    if (code_shown){
      $('div.input').hide('500');
      $('#toggleButton').val('Show Code')
    } else {
      $('div.input').show('500');
      $('#toggleButton').val('Hide Code')
    }
    code_shown = !code_shown
  }
  
  $( document ).ready(function(){
    code_shown=false;
    $('div.input').hide()
  });
</script>
<form action="javascript:code_toggle()"><input type="submit" id="toggleButton" value="Show Code"></form>
import pandas as pd
import numpy as np
from numpy import nan as Nan
from plotly.offline import init_notebook_mode, iplot
import plotly.graph_objs as go
import re
import ipywidgets
from IPython.display import display
init_notebook_mode(connected=True)
%%javascript
IPython.OutputArea.prototype._should_scroll = function(lines) {
    return false;
}<jupyter_output><empty_output><jupyter_text>Analysing Diploma Exam Data - results by school  Provincial diploma exam results are located here: https://education.alberta.ca/diploma-exam-administration/diploma-results/?searchMode=3 .We will download and examin provincial results by shool xlsx spreadsheet: diploma-multiyear-sch-list-annual.xlsx<jupyter_code>school_results = pd.read_excel('https://education.alberta.ca/media/3680580/diploma-multiyear-sch-list-annual.xlsx')
school_results.head()<jupyter_output><empty_output><jupyter_text>This data set needs to be reshaped - to split year and all the metrics in separate columns. Let's reshape it and remove rows  having  "n/a" in following columns: 'Sch Students Writing','Sch School Mark % Acc','Sch School Mark % Exc','Sch Exam Mark % Exc','Sch Exam Mark % Acc'.Examine the result:<jupyter_code>school_results_reshaped  = school_results.copy()

def splitter(string):
    r = re.compile(r'\d{4}|\S.*$')
    return r.findall(string)

cols = list(school_results_reshaped)[0:6]
years = ['2013', '2014', '2015', '2016', '2017']

for year in years:
    for names in cols:
        school_results_reshaped[str(year +" "+names)] = school_results_reshaped[names]

school_results_reshaped.columns = pd.MultiIndex.from_tuples([tuple(splitter(c)) for c in school_results_reshaped.columns])
school_results_reshaped = school_results_reshaped.stack(0).reset_index(1)

school_results_reshaped.rename(columns={'level_1': "Year"}, inplace=True)
school_results_reshaped = school_results_reshaped.reset_index(drop=True)
school_results_reshaped[['Sch Students Writing','Sch School Mark % Acc','Sch School Mark % Exc','Sch Exam Mark % Exc','Sch Exam Mark % Acc']]=school_results_reshaped[['Sch Students Writing','Sch School Mark % Acc','Sch School Mark % Exc','Sch Exam Mark % Exc','Sch Exam Mark % Acc']].apply(pd.to_numeric, errors='coerce')
school_results_reshaped=school_results_reshaped.dropna(subset=['Sch Students Writing','Sch School Mark % Acc','Sch Exam Mark % Exc','Sch School Mark % Exc','Sch Exam Mark % Acc'])
school_results_reshaped=school_results_reshaped[:-5]
school_results_reshaped.head()<jupyter_output><empty_output><jupyter_text>Let's create interactive plot - to check statistics by school for all available years.We well use two dropdown menus - to choose school(schols are sorted alphabetically) and Diploma Course. After school is chosen - the second dropdown menu is updtated with the list of courses available for this school. We use plot.ly ( 'bar') for this visualization, you can click on any legend item and bars for this metrics will be hidden.<jupyter_code>import ipywidgets as widgets

def view_schools(subject):

    result = school_results_reshaped[school_results_reshaped['School Name'] == schoolW.value]
    result = result[result['Diploma Course'] == subject]

    trace1 = go.Bar( x= result['Year'], y=result['Sch Students Writing'],
                name='Number of students taken the course', marker=dict(color='#59606D'))

    trace2 = go.Bar(x=result['Year'], y=result['Sch Students Writing']/100*result['Sch Exam Mark % Acc'],
                name='Number of students achieved acceptable standard', marker=dict(color='#ffcdd2'))

    trace3 = go.Bar(x=result['Year'], y=result['Sch Students Writing']/100*result['Sch Exam Mark % Exc'],
        name='Number of students achieved standard of excellence',marker=dict(color='#A2D5F2'))


    data = [trace1, trace2, trace3]
    layout = go.Layout(title=subject + "/" + schoolW.value,
                xaxis=dict(title='Year'),
                yaxis=dict(title='Number of students'))
    fig = go.Figure(data=data, layout=layout)

    iplot(fig)


def select_school(school):
    subjectW.options = school_results_reshaped[school_results_reshaped['School Name'] == school]['Diploma Course'].unique()
   

schoolW = widgets.Select(options=np.sort(school_results_reshaped['School Name'].unique()))
subjectW = widgets.Select(options=school_results_reshaped[school_results_reshaped['School Name'] == schoolW]['Diploma Course'].unique())

i = widgets.interactive(select_school, school=schoolW)
j = widgets.interactive(view_schools, subject=subjectW)

display(i)
display(j)<jupyter_output><empty_output><jupyter_text>Comparing multiple schools.To compare schools to provincial average - lets download diploma-multiyear-province-annual.xlsx dataset with provincial results.<jupyter_code>provincial_results = pd.read_excel('https://education.alberta.ca/media/3680581/diploma-multiyear-province-annual.xlsx')
provincial_results.head()<jupyter_output><empty_output><jupyter_text>This dataset needs to be reshaped as well to separate year and metrics to different columns.Additionally  4 columns need to be  renamed to match statistics by school (from {'Prov Exam Mark % Exc', 'Prov School Mark % Exc','Prov School Mark % Acc''Prov Exam Mark % Exc','Prov Exam Mark % Acc'} to {'Sch School Mark % Exc','Sch School Mark % Acc', 'Sch Exam Mark % Exc', 'Sch Exam Mark % Acc'}).Reshaping, renaming columns and checking the result:<jupyter_code>provincial_results = pd.read_excel('https://education.alberta.ca/media/3680581/diploma-multiyear-province-annual.xlsx')  
years = []
stats = []
for value in provincial_results.columns.values[1:-1]:
    year = value[0:4]
    stat = value[5:]
    int(year)
    if year not in years:
        years.append(year)
    if stat not in stats:
        stats.append(stat)
provincial_results_reshaped = pd.DataFrame(columns=(['Diploma Course','Year'] + stats))
for ind,row in provincial_results.drop(provincial_results.index[len(provincial_results)-1]).iterrows():
    new_row = pd.DataFrame(columns=(['Diploma Course','Year'] + stats))
    new_row.loc[0] = [Nan,Nan,Nan,Nan,Nan,Nan,Nan,Nan,Nan,Nan,Nan,Nan,Nan]
    new_row.loc[0]['Diploma Course'] = row['Diploma Course']                                       
    for year in years:
        new_row.loc[0]['Year'] = year
        for stat in stats:
            new_row.loc[0][stat] = row[year+" "+stat]
        provincial_results_reshaped=provincial_results_reshaped.append(new_row)                               
provincial_results_reshaped = provincial_results_reshaped.reset_index(drop=True)

provincial_results_reshaped=provincial_results_reshaped.rename(columns = {'Prov School Mark % Exc':'Sch School Mark % Exc'})
provincial_results_reshaped=provincial_results_reshaped.rename(columns = {'Prov School Mark % Acc':'Sch School Mark % Acc'})
provincial_results_reshaped=provincial_results_reshaped.rename(columns = {'Prov Exam Mark % Exc':'Sch Exam Mark % Exc'})
provincial_results_reshaped=provincial_results_reshaped.rename(columns = {'Prov Exam Mark % Acc':'Sch Exam Mark % Acc'})
provincial_results_reshaped.head()<jupyter_output><empty_output><jupyter_text>Compaing 5 schools between each other and with provincial average.For this interactive plot  -  we create 7 dropdown menus. 5 top menus are for schools selection. The result for every school will be displayed as circles with unique colour.Provincial average results are added as red diamonds to the plot.2 bottom dropdown menus are created to choose Diploma Course from the list and metric. Available metrics are: 'Sch Exam Mark % Exc','Sch Exam Mark % Acc','Sch School Mark % Acc','Sch School Mark % Exc'. The resulting data is percentage of the students(y axix) by year (x axix).We use plot.ly ('scatter') to diplay the data, if you click on any school in the legend - the plot will for this school will be hidden.<jupyter_code>def view_schools(school1,school2,school3,school4, school5, subject, metric):

    result1 = school_results_reshaped[school_results_reshaped['School Name'] == school1]
    result1 = result1[result1['Diploma Course'] == subject]
    
    result2 = school_results_reshaped[school_results_reshaped['School Name'] == school2]
    result2 = result2[result2['Diploma Course'] == subject]
    
    result3 = school_results_reshaped[school_results_reshaped['School Name'] == school3]
    result3 = result3[result3['Diploma Course'] == subject]
    
    result4 = school_results_reshaped[school_results_reshaped['School Name'] == school4]
    result4 = result4[result4['Diploma Course'] == subject]
    
    result5 = school_results_reshaped[school_results_reshaped['School Name'] == school5]
    result5 = result5[result5['Diploma Course'] == subject]
    
    provincial_results = provincial_results_reshaped[provincial_results_reshaped['Diploma Course'] == subject]
    
    trace1 = go.Scatter(
    x = result1['Year'],
    y = result1[metric],
    mode='markers',
    opacity=0.7,
    marker={
        'size': 15,
        'line': {'width': 0.5, 'color': 'white'}
            },
    name = school1)
        
    trace2 = go.Scatter(
    x = result2['Year'],
    y = result2[metric],
    mode = 'markers',
    opacity=0.7,
    marker={
        'size': 15,
        'line': {'width': 0.5, 'color': 'white'}
            },
    name = school2)    
        
    trace3 = go.Scatter(
    x =  result3['Year'],
    y = result3[metric],
    mode = 'markers',
    opacity=0.7,
    marker={
        'size': 15,
        'line': {'width': 0.5, 'color': 'white'}
            },
    name = school3)
        
    trace4 = go.Scatter(
    x = result4['Year'],
    y = result4[metric],
    mode = 'markers',
    opacity=0.7,
    marker={
        'size': 15,
        'line': {'width': 0.5, 'color': 'white'}
            },
    name = school4)

        
    trace5 = go.Scatter(
    x = result5['Year'],
    y = result5[metric],
    mode = 'markers',
    opacity=0.7,
    marker={
        'size': 15,
        'line': {'width': 0.5, 'color': 'white'}
            },
    name = school5)
    
    trace6 = go.Scatter(
        x=provincial_results['Year'],
        y=provincial_results[metric],
        mode='markers',
        opacity=0.7,
        marker={
            'size': 15,
            'symbol':'diamond',
            'color': 'red',
            'line': {'width': 0.5, 'color': 'white'}
            },
        name='Provincial Average'
        )
        
    data = [trace1, trace2, trace3, trace4, trace5, trace6]
    layout = go.Layout(
                title=subject + " - percentage of students achived " + str(metric),
                xaxis=dict(title='Year', type='category'),
                yaxis=dict(title='% of students'))
    fig = go.Figure(data=data, layout=layout)

    iplot(fig)


widgets.interact(view_schools, school1=np.sort(school_results_reshaped['School Name'].unique()),
         school2=np.sort(school_results_reshaped['School Name'].unique()),
         school3=np.sort(school_results_reshaped['School Name'].unique()),
         school4=np.sort(school_results_reshaped['School Name'].unique()),
         school5=np.sort(school_results_reshaped['School Name'].unique()),
         subject=np.sort(school_results_reshaped['Diploma Course'].unique()),
         metric=['Sch Exam Mark % Exc','Sch Exam Mark % Acc','Sch School Mark % Acc','Sch School Mark % Exc']);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13050748265195217, 'fraction_numerical': 0.01580135440180587, 'mean_word_length': 4.173875432525952, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 34, 'https://': 5, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '550647', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 3866, 'n_tokens_neox': 3694, 'n_words': 884, 'path': 'notebooks/Digital_Citizenship/Exam_data_results_by_school.ipynb', 'repo_name': 'callysto/callysto-sample-notebooks', 'size': '11961'}