<jupyter_start><jupyter_text>Analysis & Scorecard- Analysis in each area     - [Dataset Overview](overview)    - [Dataset Sources](sources)    - [File Types](filetypes)    - [File Sizes](sizes)    - [Dataset Tags](tags)    - [Dataset Licensing](licensing)    - [URL Statuses](urls)- [Summary Matrix](summary)- [Scorecard](scorecard)<jupyter_code>print('Generated: ', dt.datetime.now().strftime('%d-%b-%Y %H:%M'))<jupyter_output>Generated:  29-Nov-2021 20:45<jupyter_text>Dataset OverviewAn overview of the OD files available and which councils provide them<jupyter_code>print('all data files: ', len(data))
print('distinct datasets: ', len(data_distinct))
### Unique datasets by council
stats_a = data_distinct.groupby(['Owner']).size().reset_index().rename(columns={0:'Count'})
stats_a = stats_a.sort_values(by=['Count'], ascending=False).reset_index(drop=True)

### Chart
columnchart('Unique datasets by Council', stats_a['Owner'], stats_a['Count'], colour='#1f78b4')

### Unique datasets by council
stats_a1 = data.groupby(['Owner']).size().reset_index().rename(columns={0:'Count'})
stats_a1 = stats_a1.sort_values(by=['Count'], ascending=False).reset_index(drop=True)

### Chart
columnchart('Total files by Council', stats_a1['Owner'], stats_a1['Count'], colour='#1f78b4')<jupyter_output><empty_output><jupyter_text>Dataset SourcesLooking at how these OD files are accessed via API or by manual extraction<jupyter_code>### Unique datasets by Source
stats_b1 = data_distinct.groupby(['Source']).size().reset_index().rename(columns={0:'Count'})
stats_b1 = stats_b1.sort_values(by=['Count'], ascending=False).reset_index(drop=True)

### Chart
plt.figure(figsize=(5,3))
plt.bar(x=stats_b1['Source'], height=stats_b1['Count'])
plt.ylabel(''), plt.xlabel(''), plt.yticks([])
addlabels(stats_b1['Source'], stats_b1['Count'],0)
plt.box(False)
plt.title('Unique datasets by Source')
plt.savefig('image_Unique datasets by Source.png')
plt.show()

### Unique datasets by Council and Source
stats_b = data_distinct.groupby(['Owner','Source']).size().reset_index().rename(columns={0:'Count'})
stats_b = stats_b.sort_values(by=['Count'], ascending=False)

### Unique datasets by Council and Source, as matrix
stats_b_pivot = stats_b.pivot(index='Owner',columns='Source', values='Count').reset_index()
stats_b_pivot.loc['Source_Total']= stats_b_pivot.sum(numeric_only=True, axis=0)
stats_b_pivot.loc[:,'Council_Total'] = stats_b_pivot.sum(numeric_only=True, axis=1)
stats_b_pivot = stats_b_pivot.fillna('')
stats_b_pivot<jupyter_output><empty_output><jupyter_text>FiletypesThere are 27 different filetypes labelled in our dataset but the distribution is very uneven. The clear popular format is the open CSV format, but we also see some proprietary spreadsheet formats such as XLSX and the now depreciated XLS format. Bear in mind that several of these files will be duplicated, but in different formats. For example, KMZ is the compressed format of KML. ZIP files may contain several different formats together.<jupyter_code>### Table showing counts by filetypes
pd.DataFrame(\
        data.groupby(['FileType']).size())\
    .rename(columns={0:'Count'})\
    .sort_values(by='Count', ascending=False)\
    .reset_index()<jupyter_output><empty_output><jupyter_text>Inconsistencies in filetype metadataThere are filetypes labelled "Table", "Image", and "Document Link" which are descriptions rather than names of file formats. All of these come from 1 council, and is suggestive of a lack of standardised labelling.Only 13 of the 27 formats (~48%) are provided by more than 1 council, which again suggests inconsistencies, but this time it could be that the actual filetypes provided are unusual, or it could be labelling errors as before. Note that these labels have already been treated for inconsistencies in string formating (casing). Again, these should ideally be standardised in the publishing process.<jupyter_code>### Table showing number of councils providing each filetypes
data.groupby(['FileType'])['Owner'].nunique()\
    .reset_index()\
    .sort_values(by='Owner', ascending=False)\
    .rename(columns={'Owner':'Count of Councils Providing'})\
    .reset_index(drop=True)
### Count of data files by Council and FileType, ordered by Council name
stats_c = data.groupby(['Owner','FileType']).size().reset_index().rename(columns={0:'Count'})
stats_c = stats_c.sort_values(by=['Owner'], ascending=True).reset_index(drop=True)

### Chart
plt.figure(figsize=(15,5))
sns.barplot(x='Owner', y='Count', data=stats_c, hue='FileType')
plt.ylabel(''), plt.xlabel('')
plt.legend(loc='center left', bbox_to_anchor=(1, 0.5))
plt.title('Data files by Council and FileType')
plt.savefig('image_Data files by Council and FileType.png')
plt.show()

### Count of data files by Council and FileType, as matrix (rows=filetype, columns=council), ordered by FileType count

stats_c_pivot = stats_c.pivot(index='FileType',columns='Owner', values='Count')
stats_c_pivot.loc[:,'FileType_Total'] = stats_c_pivot.sum(numeric_only=True, axis=1)
stats_c_pivot = stats_c_pivot.sort_values(by='FileType_Total', ascending=False)
stats_c_pivot.loc['Council_Total']= stats_c_pivot.sum(numeric_only=True, axis=0)
stats_c_pivot = stats_c_pivot.fillna('')
stats_c_pivot<jupyter_output><empty_output><jupyter_text>Average FileTypes for each datasetThis gives us an idea of the variety of file types offered per dataset. A higher average would suggest several filetypes are offered for the same data (e.g. a .csv and a .json), although with caveats. Bear in mind that - If there is a ZIP of multiple .csv files this counts as 2 file types. Same with KMZ and KML.- geojson or shapefiles tend to come as a package, so councils providing a high proportion of geospatial data will naturally serve a high average number of filetypes per dataset. So while this measure is useful, just be aware it may just be echoing the nature of the data provided rather than a true offering of variety.<jupyter_code>### Average count of filetypes provided per dataset, by council
stats_e = data.groupby(['Owner','Title'])['FileType'].nunique().reset_index()
stats_e = stats_e.groupby(['Owner'])['FileType'].mean().reset_index()
stats_e = stats_e.sort_values(by=['FileType'], ascending=False).reset_index(drop=True)
stats_e_mean = round(stats_e['FileType'].mean(),2)

### Chart

plt.figure(figsize=(15,3))
plt.bar(x=stats_e['Owner'], height=stats_e['FileType'])
plt.ylabel(''), plt.xlabel('')
addlabels(stats_e['Owner'], stats_e['FileType'],2)
wrapaxis(stats_e['Owner'],13)
plt.box(False)
plt.axhline(y=stats_e_mean, linestyle=':')
plt.text(x=len(stats_e)-1, y=stats_e_mean, s='Average= '+ str(stats_e_mean), verticalalignment='bottom')
plt.title('Average FileTypes provided for each dataset by Council')
plt.savefig('image_Average FileTypes provided for each dataset by Council.png')
plt.show()<jupyter_output><empty_output><jupyter_text>Bottom line There is a wide range of filetypes available, but this range is likely due to inconsistencies in labelling rather than a true variety of filetype offerings. The most popular filetype by far is CSV, which is offered by the most councils and also makes the largest share of all offerings.  File sizeFile size isn't the best proxy for the volume of data provided, we will want to look at the number of instances or number of values per file or dataset for this. Filetypes will also have influence on total filesize: binary files such as .xlsx will be larger than their .csv equivalents, as will PDFs and images.But file size is still interesting to look at for accessibility reasons. Mammoth files will struggle to download for users or organisations on limited bandwidth internet connections, they also take a chunk of councils' storage resources.<jupyter_code>### Convert file size units to bytes
def filesize_convert(unit, val):
    byte_conversions = {
            'bytes': 1,
            'B': 1,
            'KB': 1000,
            'MB': 1000000,
            'GB': 1000000000
        }
    if (unit in list(byte_conversions.keys())) & (val > 0):
        conv_bytes = val * byte_conversions[unit]
    else: 
        conv_bytes = None
        
    return conv_bytes

### apply conversion function
data['FileSizeBytes'] = data.apply(lambda x: filesize_convert(x['FileSizeUnit'], x['FileSize']), axis=1)
data['FileSizeBins'] = pd.cut(x=data['FileSizeBytes'], bins=[1,1000,10000,100000,1000000,10000000, 100000000, 1000000000, 10000000000], labels=['<1KB','1-10KB','10-100KB','0.1-1MB','1-10MB','10-100MB','0.1-1GB','1-10GB'])

### Quality check: instances with no filesize info
filesize_issues = len(data[data['FileSizeBytes'].isnull()])
if filesize_issues > 0:
    print('There is no filesize info for ',filesize_issues,' files.')
else: None
### Count of files by filesize bins
stats_g = data.groupby(['FileSizeBins']).size().reset_index()

### Chart
columnchart('Number of files by FileSize', stats_g['FileSizeBins'], stats_g[0], colour='#1f78b4')<jupyter_output><empty_output><jupyter_text>Total size by councilNaturally, in looking at total size of all files by council, councils which provide the most datafiles would tend to higher total sizes. Yet Renfrewshire is an exception here, they are middle of the pack for number of files provided but their total file size far exceeds everyone else. This might be because the filetypes they are using are storage heavy (7 of their 8 filetypes are unique to them and we have no filesize data for other councils using the 1 shared filetype) or they could benefit from file compression in their publishing process.<jupyter_code>### Filesize totals by council
stats_g1 = data.groupby(['Owner'])['FileSizeBytes'].sum().reset_index()
stats_g1['FileSizeMB'] = stats_g1['FileSizeBytes']/1000000
stats_g1 = stats_g1.sort_values(by=['FileSizeMB'], ascending=False).reset_index(drop=True)

### Chart
columnchart('Total size of all files (MB) by Council', stats_g1['Owner'], stats_g1['FileSizeMB'], colour='#1f78b4')<jupyter_output><empty_output><jupyter_text>Total file size (MB) by Council and FileType<jupyter_code>### Total FileSize by FileType
stats_g2 = data.groupby(['Owner','FileType'])['FileSizeBytes'].sum().reset_index()
stats_g2['FileSizeMB'] = stats_g2['FileSizeBytes']/1000000
stats_g2 = stats_g2.sort_values(by=['FileSizeMB'], ascending=False).reset_index(drop=True)
stats_g2

### Total FileSize by Council and FileType, as matrix (rows=filetype, columns=council), ordered by FileType total FileSize
stats_g2_pivot = stats_g2.pivot(index='FileType',columns='Owner', values='FileSizeMB')
stats_g2_pivot.loc[:,'FileType_Total'] = stats_g2_pivot.sum(numeric_only=True, axis=1)
stats_g2_pivot = stats_g2_pivot.sort_values(by='FileType_Total', ascending=False)
stats_g2_pivot.loc['Council_Total']= stats_g2_pivot.sum(numeric_only=True, axis=0)
stats_g2_pivot = stats_g2_pivot.round(2)
stats_g2_pivot = stats_g2_pivot.fillna('')
stats_g2_pivot<jupyter_output><empty_output><jupyter_text>Dataset Tags<jupyter_code>### New dataset: Tags used in the datasets
data_tags = data[['Owner', 'PageURL','OriginalTags','ManualTags']]
data_tags = data_tags.drop_duplicates()
### combine the Original and Manual tags together then split them out
data_tags['AllTags'] = data_tags.apply(lambda x: str(x['OriginalTags'])+ ';' + str(x['ManualTags']), axis=1)
data_tags['AllTags'] = data_tags['AllTags'].str.replace(',',';')
data_tags = data_tags.join(data_tags['AllTags'].str.split(';', expand = True))
### unpivot the table from wide to long format
data_tags = data_tags.drop(columns=['OriginalTags','ManualTags','AllTags'])
data_tags = data_tags.melt(id_vars=['Owner','PageURL'])
### Clean up: remove nones
data_tags = data_tags.drop(data_tags[data_tags['value']=='nan'].index)
data_tags = data_tags.dropna()
### Clean up: remove redundant column and rename the other
data_tags = data_tags.drop(columns=['variable'])
data_tags = data_tags.rename(columns={'value':'Tag'})
### Clean up: set all tags to lowercase and trim
data_tags['Tag'] = data_tags['Tag'].str.lower()
data_tags['Tag'] = data_tags['Tag'].str.strip()
### The 10 most popular tags
stats_d = pd.DataFrame(data_tags['Tag'].value_counts()).head(10)

### Chart
columnchart('The 10 most popular tags in datasets (by volume)', stats_d.index, stats_d['Tag'], colour='#1f78b4')

### The 10 most shared tags across councils
stats_d1 = pd.DataFrame(data_tags.groupby(['Tag'])['Owner'].nunique()).sort_values(by=['Owner'],ascending=False).head(10)

### Chart
columnchart('The 10 most shared tags across councils (by number of councils using that tag)', stats_d1.index, stats_d1['Owner'], colour='#1f78b4')<jupyter_output><empty_output><jupyter_text>Tags by councilA high number of unique tags could suggest that a council is providing a wider range of datasets over different interest areas. Or it could also be indicative of a tag-happy staffer who is great at tagging (this is good). So conversely, a low number of unique tags by councils should be of concern ---> why are you not tagging?<jupyter_code>### Number of unique tags by council
stats_d2 = pd.DataFrame(data_tags.groupby(['Owner'])['Tag'].nunique()).sort_values(by=['Tag'],ascending=False)

### Chart
columnchart('Unique tags by council', stats_d2.index, stats_d2['Tag'], colour='#1f78b4')<jupyter_output><empty_output><jupyter_text>A note for the futureIt would be very interesting to have a master list of potential tags (that is, areas of interest where OD should be provided for) to then compare how each council is providing data in each of these interest areasFor example, it's clear above that many councils are providing datasets to do with Education, but what about transport and waste management etc?  Inconsistencies in dataset tagsAs with free-text entry fields, we would expect to see issues in quality here. We have inconsistencies in format (casing), different tag separators, and typos. But we also have inconsistencies in style of tagging.For example, see the extract below for the 5 different ways electoral data has been tagged, only 1 is an obvious error, the other 4 are legitimate tags but in different styles. This if anything highlights a need for a shared standardisation in the open data publishing process.<jupyter_code>### Matrix of tags used by council
stats_d3 = pd.DataFrame(\
                    data_tags.groupby(['Tag','Owner']).size())\
                .reset_index()\
                .pivot(index='Tag',columns='Owner',values=0)
stats_d3.loc[:,'Tag_Total'] = stats_d3.sum(numeric_only=True, axis=1)
stats_d3.loc['Council_Total',:] = stats_d3.sum(numeric_only=True, axis=0)

### Export to 'output_TagsByCouncil.csv'
stats_d3.to_csv('output_TagsByCouncil.csv')

### Show a snippet for "elec"
elec_tag_list = [t for t in stats_d3.index.values if (t[0:4]=='elec') & (t.find('electric'))]
stats_d3.loc[elec_tag_list,:].fillna('')<jupyter_output><empty_output><jupyter_text>Dataset Licensing<jupyter_code>stats_h = pd.DataFrame(data.groupby(['License'], dropna=False).size().sort_values(ascending=True)).reset_index().rename(columns={0:'Count'})
stats_h = stats_h.fillna('None')

plt.figure(figsize=(5,4))
plt.barh(y=stats_h['License'], width=stats_h['Count'])
plt.ylabel(''), plt.xlabel(''), plt.xticks([])
plt.box(False)
for i in range(len(stats_h['License'])):
    y=stats_h['Count']
    plt.text(y[i], i, round(y[i],0), va = 'center')
plt.title('Number of data files by License')
plt.savefig('image_Number of data files by License.png')
plt.show()<jupyter_output><empty_output><jupyter_text>InconsistenciesWe have multiple ways of writing the same thing across councils. See:- OGL3- Open Government Licence 3.0 (United Kingdom)- UK Open Government Licence (OGL)- uk-ogl are all the same thing.Note that all arcgis API licenses have returned empty - this is likely due to an error in the API call rather than in the datasets themselves.<jupyter_code>pd.DataFrame(data.groupby(['Source','License'], dropna=False).size()).reset_index().rename(columns={0:'Count'}).fillna('None')<jupyter_output><empty_output><jupyter_text>Check URL statusesChecks to see if our urls are active or if the file for some reason has been removed.<jupyter_code>### shows progress indicator for slow processes.
def progress_indicator(n,jump,**kwargs):
### n is the current instance, jump is the instance increment to highlight, as is jump2 (e.g. show every 10 and 100). jump2 is optional.
    jump2 = kwargs.get('jump2', None)

    ### if the remainder of n/jump2 = 0 (i.e. a whole number)
    if (n>0) & (n%jump2 == 0):
        ### print the quotient + string marker
        print(str(n//jump2)+'00', end='', flush=True)
    ### otherwise print a * for every jump
    elif n%jump == 0:
        print('*', end='', flush=True)
    return

### get web status for assetURL
def check_status(url):
    try:
        response = requests.get(url)
        status = (response.status_code == 200)
    except:
        status = 'Error'
    return status
### This is a very very very slow process. It can take >10 minutes to check 800 urls. 
### I recommend skipping this if you're doing a light update.
### But if you want to get updated statuses (and wait for a while), type "IMSURE" when prompted.
### There is a progress indicator and confirmation at end.

are_you_sure = input("Update URL statuses: This is a very slow process, be prepared to wait over 10mins. To go ahead type IMSURE")

if are_you_sure == 'IMSURE':
    for row in data.index:
        data.loc[row,'AssetStatus'] = check_status(data.loc[row,'AssetURL'])
        progress_indicator(row,10,jump2=100)
    
    print('\nok done. Last updated',dt.datetime.now().strftime(format='%d-%m-%Y %Hh:%Mm'))
    print(data['AssetStatus'].value_counts())

else:
    print('url status check skipped.')<jupyter_output>**********100*********200*********300*********400*********500*********600*********700*********800*********900*****
ok done. Last updated 29-11-2021 21h:36m
True     707
Error    196
False     50
Name: AssetStatus, dtype: int64<jupyter_text>URL statuses by CouncilThis shows if the URL of OD assets are active (True) or not (False).If it is True, the data is accessible, if it is False there is a hosting issue.(Error) indicates there is no URL to check or it is missing from our data.A full listing of the URL status is available in 'output_AllAnalytics.csv'<jupyter_code>pd.DataFrame(data.groupby(['Owner','AssetStatus']).size())\
        .reset_index()\
        .pivot(index='Owner',columns='AssetStatus',values=0)\
        .fillna('')\
        .rename(columns={False:'Inactive',True:'Active','Error':'Missing'})\
        [['Active','Inactive','Missing']]<jupyter_output><empty_output><jupyter_text>Summary MatrixAll the characteristics, in one place.<jupyter_code>data.to_csv('output_AllAnalytics.csv')
summary_matrix = pd.DataFrame(data.groupby(['Owner'])['AssetURL'].size()).reset_index()
summary_matrix = summary_matrix.merge(data[data['AssetStatus']==True].groupby(['Owner'])['AssetStatus'].size().reset_index(), how='left', on='Owner')
summary_matrix['% Active URLs'] = summary_matrix['AssetStatus']/summary_matrix['AssetURL']
summary_matrix['% Active URLs'] = round(summary_matrix['% Active URLs']*100,1)
summary_matrix = summary_matrix.merge(stats_a, how='left', on='Owner')
summary_matrix = summary_matrix.merge(data.groupby(['Owner'])['Source'].nunique().reset_index(), how='left', on='Owner')
summary_matrix = summary_matrix.merge(data.dropna(subset=['DateUpdated']).groupby(['Owner'])['DateUpdated'].max().reset_index(), how='left', on='Owner')
summary_matrix = summary_matrix.merge(data.groupby(['Owner'])['License'].nunique().reset_index(), how='left', on='Owner')
summary_matrix = summary_matrix.merge(data_tags.groupby(['Owner'])['Tag'].nunique().reset_index(), how='left', on='Owner')
summary_matrix = summary_matrix.merge(data.groupby(['Owner'])['FileType'].nunique().reset_index(), how='left', on='Owner')
summary_matrix = summary_matrix.merge(stats_e, how='left', on='Owner')
summary_matrix = summary_matrix.merge(stats_g1[['Owner','FileSizeMB']], how='left', on='Owner')
summary_matrix['FileType_y'] = round(summary_matrix['FileType_y'],2)
summary_matrix['FileSizeMB'] = round(summary_matrix['FileSizeMB'],2)
column_rename = {'Owner':'Council',
                 'AssetURL':'#Files',
                 'AssetStatus':'Active URLs',
                 'Count':'#Datasets',
                 'Source':'#Data Sources',
                 'DateUpdated':'Last Updated',
                 'License':'#Licenses',
                 'Tag':'#Tags',
                 'FileType_x':'#FileTypes',
                 'FileType_y':'Avg FileTypes per Dataset',
                 'FileSizeMB':'Total Size (MB)'}
summary_matrix = summary_matrix.rename(columns=column_rename)
summary_matrix.to_csv('output_SummaryMatrix.csv', index=False)
summary_matrix.fillna('')<jupyter_output><empty_output><jupyter_text>ScorecardEverything we’ve looked at so far is descriptive and explanatory. It helps us figure out what’s going on but how do we make this useful and actionable?One way is to turn it into a competition to score and rank each council to see how they're all stacking up. It's not perfect, but it's one way of consistently and transparently measuring everyone. This isn't a measure of if the council is rated "good" or not, this is just an ordering from best offerings to worst, where the worst offering is having no open data provision at all. This could be a way to identify who the leader councils are for other councils to emulate, but that doesn't exclude the need for leading councils to improve their offering either.The scorecare can be a great tool to potentially facilitate a lot of conversation. If a council asks "so how do we get a better score?", well, where are you performing worst at the moment? What's the easiest thing to fix right now?- if you post a new dataset next week your score could improve significantly.- Could you relabel some of your existing datasets, that could be a way to improve your score?  MethodologyThe idea behind the scorecard is to encourage and reward good behaviour: having a lot of datasets, a high variety of filetypes, a high variety of datasets, and timeliness (!) etc...There are 10 areas of assessment, each with a potential score of 0-1 and come from either of 2 scoring mechanisms:- an objective binary measure: Did they meet this critera Yes(1)/No(0)- a relative measure: Who is best in this area (1), and worst (0), everyone else gets between 0-1 based on rank. | Measure | Scoring  | Goal  ||:--|:--|:--|| Has OD Site  | objective  |  Is providing some OD || Datasets |  relative | More datasets are better  || % Active URLs  |  relative |  More active links are better || Last 365 days |  objective | Has updated data in the last year || Last 180 days |  objective | Has updated data in the last 6 months || Last 90 days |  objective | Has updated data in the last 3 months || Last 30 days |  objective | Has updated data in the last month || Avg FileTypes per Dataset  |  relative | Higher avg filetypes suggests better variety and accomodations || Total Size (MB)  |  relative | Higher size might suggest more data available || Tags  |  relative | More tags might suggest broader range of categories offered |The highest possible perfect score is 10.<jupyter_code># Calculating Last updated

def days_updated(df):
    last_update = df[['Council','Last Updated']].copy()
    last_update['Days_Passed'] = last_update['Last Updated'].apply(lambda x: (dt.date(2021,6,27) - x).days if isinstance(x, dt.date) else None)
    #last_update['Days_Passed'] = last_update['Last Updated'].apply(lambda x: (dt.datetime.today().date() - x).days if isinstance(x, dt.date) else None)
    last_update['Last 365 days'] = last_update['Days_Passed'] < 365
    last_update['Last 180 days'] = last_update['Days_Passed'] < 180
    last_update['Last 90 days'] = last_update['Days_Passed'] < 90
    last_update['Last 30 days'] = last_update['Days_Passed'] < 30
    last_update = last_update.drop(columns=['Last Updated','Days_Passed'])

    return last_update
### Putting the scorecard together

scorecard = data_localauthorities.copy()
### Has OD site
scorecard['Has OD Site'] = scorecard['Council'].isin(set(summary_matrix['Council']))
### Unique datasets
scorecard = scorecard.merge(summary_matrix[['Council','#Datasets']], how='left', on='Council')
scorecard['#Datasets'] = round(scorecard['#Datasets'].rank()/len(summary_matrix),2)
### % Active URLS
scorecard = scorecard.merge(summary_matrix[['Council','% Active URLs']], how='left', on='Council')
scorecard['% Active URLs'] = round(scorecard['% Active URLs'].rank()/len(summary_matrix),2)
### Last updates in 365, 180, 90, 30 days
scorecard = scorecard.merge(days_updated(summary_matrix), how='left', on='Council')
### Filetypes per dataset
scorecard = scorecard.merge(summary_matrix[['Council','Avg FileTypes per Dataset']], how='left', on='Council')
scorecard['Avg FileTypes per Dataset'] = round(scorecard['Avg FileTypes per Dataset'].rank()/len(summary_matrix),2)
### Total filesize
scorecard = scorecard.merge(summary_matrix[['Council','Total Size (MB)']], how='left', on='Council')
scorecard['Total Size (MB)'] = round(scorecard['Total Size (MB)'].rank()/len(summary_matrix),2)
### Unique tags used
scorecard = scorecard.merge(summary_matrix[['Council','#Tags']], how='left', on='Council')
scorecard['#Tags'] = round(scorecard['#Tags'].rank()/len(summary_matrix),2)

### Cleanup
scorecard = scorecard.fillna(0)
scorecard = scorecard.replace([True,False], [1,0])
scorecard.loc[:,'Council_Score'] = scorecard.sum(numeric_only=True, axis=1)
scorecard = scorecard.sort_values(by='Council_Score', ascending=False).set_index('Council')

scorecard.to_csv('output_Scorecard.csv')
scorecard<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13590042784908596, 'fraction_numerical': 0.017619603267211202, 'mean_word_length': 5.885645420460632, 'pattern_counts': {'":': 0, '<': 76, '<?xml version=': 0, '>': 76, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 5, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '357025', 'license': 'MIT', 'n_tokens_mistral': 8218, 'n_tokens_neox': 7716, 'n_words': 2388, 'path': 'analytics/analytics.ipynb', 'repo_name': 'ash-mcc/the_od_bods', 'size': '25710'}