<jupyter_start><jupyter_text>Build Data for the Map Check and merge datasets from:    cordis    creative    esif    fts    erasmus    nhs    nweurope    life<jupyter_code>%matplotlib inline

from functools import reduce
import glob
import json
import os

import pandas as pd
import numpy as np

pd.set_option('display.max_columns', 50)<jupyter_output><empty_output><jupyter_text>Define Validity Checks<jupyter_code>ukpostcodes = pd.read_csv('../postcodes/input/ukpostcodes.csv.gz')
ukpostcodes.shape
def validate_postcodes(df):
    assert 'postcode' in df.columns
    assert (~df['postcode'].isin(ukpostcodes.postcode)).sum() == 0
    
def validate_date_range(df):
    assert 'start_date' in df.columns
    assert 'end_date' in df.columns
    assert df['start_date'].dtype == 'datetime64[ns]'
    assert df['end_date'].dtype == 'datetime64[ns]'
    assert (df['start_date'] > df['end_date']).sum() == 0<jupyter_output><empty_output><jupyter_text>Load Cleaned Data  CORDIS<jupyter_code>fp7_organizations = pd.read_pickle('../cordis/output/fp7_organizations.pkl.gz')
validate_postcodes(fp7_organizations)
fp7_organizations.head()
fp7_projects = pd.read_pickle('../cordis/output/fp7_projects.pkl.gz')
validate_date_range(fp7_projects)
fp7_projects.head()
fp7 = pd.merge(
    fp7_projects, fp7_organizations,
    left_on='rcn', right_on='project_rcn', validate='1:m'
)
fp7['my_eu_id'] = 'fp7_' + fp7.project_rcn.astype('str') + '_' + fp7.organization_id.astype('str')
fp7.head()
h2020_organizations = pd.read_pickle('../cordis/output/h2020_organizations.pkl.gz')
validate_postcodes(h2020_organizations)
h2020_organizations.head()
h2020_projects = pd.read_pickle('../cordis/output/h2020_projects.pkl.gz')
validate_date_range(h2020_projects)
h2020_projects.head()
h2020 = pd.merge(
    h2020_projects, h2020_organizations,
    left_on='rcn', right_on='project_rcn', validate='1:m'
)
h2020['my_eu_id'] = 'h2020_' + h2020.project_rcn.astype('str') + '_' + h2020.organization_id.astype('str')

# no briefs available for H2020
h2020['related_report_title'] = float('nan')
h2020['brief_title'] = float('nan')
h2020['teaser'] = float('nan')
h2020['article'] = float('nan')
h2020['image_path'] = float('nan')

h2020.head()
assert set(fp7.columns) == set(h2020.columns)
cordis = pd.concat([fp7, h2020[fp7.columns]])
cordis.shape
cordis['total_cost_gbp'] = (cordis.total_cost_eur * cordis.eur_gbp).round()
cordis['max_contribution_gbp'] = (cordis.max_contribution_eur * cordis.eur_gbp).round()
cordis['contribution_gbp'] = (cordis.contribution_eur * cordis.eur_gbp).round()
cordis.head()
cordis.describe()
(cordis.contribution_eur > cordis.total_cost_eur + 0.1).sum()
[
    cordis.start_date.isna().sum(),
    cordis.end_date.isna().sum()
]<jupyter_output><empty_output><jupyter_text>Creative Europe<jupyter_code>creative_organisations = pd.read_pickle('../creative/output/creative_europe_organisations.pkl.gz')
creative_organisations.shape
creative_projects = pd.read_pickle('../creative/output/creative_europe_projects.pkl.gz')
creative_projects.shape
creative = pd.merge(creative_projects, creative_organisations, on='project_number', validate='1:m')
creative.shape
validate_postcodes(creative)
validate_date_range(creative)
creative['max_contribution_gbp'] = (creative.max_contribution_eur * creative.eur_gbp).round()
creative['my_eu_id'] = \
    'creative_' + creative.project_number + '_' + \
    creative.partner_number.apply('{:.0f}'.format).\
    str.replace('nan', 'coordinator', regex=False)
assert creative.shape[0] == creative.my_eu_id.unique().shape[0]
creative.head()
creative.results_available.value_counts()
creative.results_url[0]
[creative.start_date.isna().sum(), creative.end_date.isna().sum()]<jupyter_output><empty_output><jupyter_text>ESIF (ESF/ERDF)<jupyter_code>esif_england = pd.read_pickle('../esif/output/esif_england_2014_2020.pkl.gz')
validate_postcodes(esif_england)
validate_date_range(esif_england)
esif_england.head()
esif_ni = pd.read_pickle('../esif/output/esif_ni_2014_2020.pkl.gz')
validate_postcodes(esif_ni)
validate_date_range(esif_ni)
esif_ni.head()
esif_scotland = pd.read_pickle('../esif/output/esif_scotland.pkl.gz')
validate_postcodes(esif_scotland)
validate_date_range(esif_scotland)
esif_scotland.head()
esif_wales = pd.read_pickle('../esif/output/esif_wales.pkl.gz')
validate_postcodes(esif_wales)
validate_date_range(esif_wales)
esif_wales.head()
assert set(esif_england.columns) == set(esif_ni.columns)
assert set(esif_england.columns) == set(esif_scotland.columns)
assert set(esif_england.columns) == set(esif_wales.columns)
esif_columns = esif_england.columns
esif = pd.concat([
    esif_england,
    esif_ni[esif_columns],
    esif_scotland[esif_columns],
    esif_wales[esif_columns]
])
esif.shape
[esif.start_date.isna().sum(), esif.end_date.isna().sum()]<jupyter_output><empty_output><jupyter_text>FTS<jupyter_code>fts_2016 = pd.read_pickle('../fts/output/fts_2016.pkl.gz')
validate_postcodes(fts_2016)
fts_2016['year'] = 2016
fts_2016.head()
fts_2017 = pd.read_pickle('../fts/output/fts_2017.pkl.gz')
validate_postcodes(fts_2017)
fts_2017['year'] = 2017
fts_2017.head()
fts = pd.concat([fts_2016, fts_2017])
fts['amount_gbp'] = (fts.amount * fts.eur_gbp).round()
fts['total_amount_gbp'] = (fts.total_amount_eur * fts.eur_gbp).round()
fts.shape<jupyter_output><empty_output><jupyter_text>Erasmus<jupyter_code>erasmus_organisations = pd.read_pickle('../erasmus/output/erasmus_mobility_organisations.pkl.gz')
erasmus_organisations.shape
erasmus_projects = pd.read_pickle('../erasmus/output/erasmus_mobility_projects.pkl.gz')
erasmus_projects.shape
erasmus = pd.merge(erasmus_projects, erasmus_organisations, on='project_identifier', validate='1:m')
erasmus.shape
validate_postcodes(erasmus)

erasmus['max_contribution_gbp'] = (erasmus.max_contribution_eur * erasmus.eur_gbp).round()
erasmus['my_eu_id'] = \
    'erasmus_' + erasmus.project_identifier + '_' + \
    erasmus.partner_number.apply('{:.0f}'.format).\
    str.replace('nan', 'coordinator', regex=False)
assert erasmus.shape[0] == erasmus.my_eu_id.unique().shape[0]
erasmus.head()<jupyter_output><empty_output><jupyter_text>NHS<jupyter_code>nhs_staff = pd.read_pickle('../nhs/output/staff.pkl.gz')
nhs_hospital_postcodes = pd.read_pickle('../nhs/output/hospital_postcodes.pkl.gz')
validate_postcodes(nhs_hospital_postcodes)
[nhs_staff.shape, nhs_hospital_postcodes.shape, nhs_hospital_postcodes.hospital_organisation.nunique()]
# dummy amount so we can put it in
nhs_hospital_postcodes['zero'] = 0
nhs_hospital_postcodes['my_eu_id'] = \
    'nhs_' + nhs_hospital_postcodes.hospital_organisation
nhs_hospital_postcodes.head()<jupyter_output><empty_output><jupyter_text>Interreg NW Europe<jupyter_code>nweurope_projects = pd.read_pickle('../nweurope/output/projects.pkl.gz')
nweurope_partnerships = pd.read_pickle('../nweurope/output/partnerships.pkl.gz')
nweurope = pd.merge(nweurope_projects, nweurope_partnerships)
validate_postcodes(nweurope)
nweurope['my_eu_id'] = \
    'nweurope_' + nweurope.project_id.astype('str') + '_' + nweurope.partner_number.astype('str')
nweurope['contribution_gbp'] = (nweurope.contribution_eur * nweurope.eur_gbp).round()
nweurope.head(2)<jupyter_output><empty_output><jupyter_text>LIFE<jupyter_code>life = pd.read_pickle('../life/output/life.pkl.gz')
validate_postcodes(life)
life['total_budget_gbp'] = (life.total_budget_eur * life.eur_gbp).round()
life['eu_contribution_gbp'] = (life.eu_contribution_eur * life.eur_gbp).round()
life = life[life.eu_contribution_gbp > 1000] # filter out a few token £1 contributions
life.shape<jupyter_output><empty_output><jupyter_text>Idea 1: All Points on Map, Data by DistrictThis should make the map look fairly similar to how it looks now, so it seems like a good starting point.<jupyter_code>ALL_PLACES = [
    (cordis, 'contribution_gbp', 'money'),
    (creative, 'max_contribution_gbp', 'money'),
    (esif, 'eu_investment', 'money'),
    (fts.drop('amount', axis=1), 'amount_gbp', 'money'),
    (erasmus, 'max_contribution_gbp', 'money'),
    (nhs_hospital_postcodes, 'zero', 'hospital'),
    (nweurope, 'contribution_gbp', 'money'),
    (life, 'eu_contribution_gbp', 'money')
]<jupyter_output><empty_output><jupyter_text>GeoJSON is very inefficient for representing a bunch of points, so let's use a relatively simple packed format.```min_longitude min_latitudeoutcode incode delta_longitude delta_latitude incode delta_longitude delta_latitude```We need [about 4 decimal places](https://gis.stackexchange.com/questions/8650/measuring-accuracy-of-latitude-and-longitude).<jupyter_code>def add_outward_and_inward_codes(df):
    df['outward_code'] = df.postcode.str.split(' ').str[0]
    df['inward_code'] = df.postcode.str.split(' ').str[1]
    return df

def add_packed_icon_mask(df):
    def pack_icon_mask(icons):
        # So far we just have one bit in our mask; we may have more.
        if 'hospital' in icons:
            return 1
        else:
            return 0
    MASK_BITS = 1
    assert df.amount.max() < 2**(32 - MASK_BITS)
    df['icon_mask'] = df.icons.apply(pack_icon_mask).astype('uint32')
    df['packed_amount'] = np.bitwise_or(
        np.left_shift(df.amount, MASK_BITS), df.icon_mask
    )
    return df

def pack_geocoded_postcodes(dfs):
    all_postcode_amounts = pd.concat([
        df.rename(columns={amount_column: 'amount'}).assign(icons=icon)\
        [['postcode', 'amount', 'icons']]
        for df, amount_column, icon in dfs
    ])
    postcode_amounts = all_postcode_amounts.groupby('postcode').\
        aggregate({'amount': sum, 'icons': lambda icons: set(icons)})
    postcode_amounts.reset_index(inplace=True)
    postcode_amounts.amount = postcode_amounts.amount.astype('uint32')
    add_outward_and_inward_codes(postcode_amounts)
    add_packed_icon_mask(postcode_amounts)
    
    geocoded_postcodes = pd.merge(postcode_amounts, ukpostcodes, validate='1:1')
    
    min_longitude = geocoded_postcodes.longitude.min()
    min_latitude = geocoded_postcodes.latitude.min()
    
    geocoded_postcodes['delta_longitude'] = geocoded_postcodes.longitude - min_longitude
    geocoded_postcodes['delta_latitude'] = geocoded_postcodes.latitude - min_latitude
    
    return {
        'min_longitude': min_longitude,
        'min_latitude': min_latitude,
        'geocoded_postcodes': geocoded_postcodes
    }

packed_postcodes = pack_geocoded_postcodes(ALL_PLACES)
[
    packed_postcodes['min_longitude'],
    packed_postcodes['min_latitude'],
    packed_postcodes['geocoded_postcodes'].shape[0]
]
packed_postcodes['geocoded_postcodes'].head()
def make_packed_postcode_json(packed_postcodes):
    packed_postcodes = packed_postcodes.copy()
   
    grouped_postcodes = packed_postcodes['geocoded_postcodes'].\
        sort_values('outward_code').groupby('outward_code')
     
    def make_code_tuples(row):
        coordinate = '{0:.4f}'
        return [
            row['inward_code'],
            float(coordinate.format(row['delta_longitude'])),
            float(coordinate.format(row['delta_latitude'])),
            row['packed_amount']
        ]
    
    postcodes = {}
    for outward_code, group in grouped_postcodes:
        postcodes[outward_code] = [
            x
            for code in group.sort_values('inward_code').apply(make_code_tuples, axis=1)
            for x in code
        ]

    min_coordinate = '{0:.6f}'
    return {
        'min_longitude': float(min_coordinate.format(packed_postcodes['min_longitude'])),
        'min_latitude': float(min_coordinate.format(packed_postcodes['min_latitude'])),
        'postcodes': postcodes
    }

with open('output/packed_postcodes.data.json', 'w') as file:
    json.dump(make_packed_postcode_json(packed_postcodes), file, sort_keys=True)<jupyter_output><empty_output><jupyter_text>Some Aggregates<jupyter_code>[place[0].shape[0] for place in ALL_PLACES]
sum(place[0].shape[0] for place in ALL_PLACES)<jupyter_output><empty_output><jupyter_text>Data by District CORDIS<jupyter_code>MAX_PROJECTS = 50

# Dump to JSON using pandas, because it puts in nulls instead of NaNs for
# missing values. Then load the JSON into dicts for 
def make_district_data_json(df, name):
    key = ['outwardCode', 'inwardCode']
    def to_json(group):
        group.drop(key, axis=1, inplace=True)
        return json.loads(group.to_json(orient='split', index=False))
    grouped = df.groupby(key).apply(to_json)
    
    grouped = grouped.reset_index()
    grouped.columns = key + [name]
    
    for _key, row in grouped.iterrows():
        count = len(row[name]['data'])
        if count > MAX_PROJECTS:
            row[name]['data'] = row[name]['data'][0:MAX_PROJECTS]
            row[name]['extra'] = count - MAX_PROJECTS
        
    return grouped

def make_cordis_district_data(cordis):
    cordis = add_outward_and_inward_codes(cordis.copy())

    cordis = cordis[[
        'outward_code',
        'inward_code',
        'start_date', 'end_date',
        'title',
        'name', # of organization
        'objective',
        'contribution_gbp',
        'total_cost_gbp',
        'num_countries',
        'num_organizations',
        'acronym',
        'project_url',
        'organization_url',
        'image_path',
        'my_eu_id'
    ]]
    
    cordis.rename({
        'outward_code': 'outwardCode',
        'inward_code': 'inwardCode',
        'start_date': 'startDate',
        'end_date': 'endDate',
        'title': 'projectTitle',
        'name': 'organisationName',
        'contribution_gbp': 'contribution',
        'total_cost_gbp': 'totalCost',
        'num_countries': 'numCountries',
        'num_organizations': 'numOrganisations',
        'project_url': 'projectUrl',
        'organization_url': 'organisationUrl',
        'image_path': 'imagePath',
        'my_eu_id': 'myEuId'
    }, axis=1, inplace=True)
    
    cordis.sort_values(
        ['outwardCode', 'inwardCode', 'contribution'],
        ascending=[True, True, False],
        inplace=True
    )
    
    return make_district_data_json(cordis, 'cordis')

cordis_district_data = make_cordis_district_data(cordis)
cordis_district_data.head()<jupyter_output><empty_output><jupyter_text>Creative Europe<jupyter_code>def make_creative_district_data(creative):
    creative = add_outward_and_inward_codes(creative.copy())
    
    coordinators = creative[creative.organisation_coordinator]
    coordinators = coordinators[['project_number', 'organisation_name']]
    creative = pd.merge(
        creative, coordinators,
        how='left', on='project_number', suffixes=('', '_coordinator'))

    creative = creative[[
        'outward_code',
        'inward_code',
        'start_date', 'end_date',
        'project',
        'organisation_name',
        'max_contribution_gbp',
        'num_countries',
        'num_organisations',
        'summary',
        'organisation_website',
        'organisation_name_coordinator',
        'my_eu_id'
    ]]
    
    creative.rename({
        'outward_code': 'outwardCode',
        'inward_code': 'inwardCode',
        'start_date': 'startDate',
        'end_date': 'endDate',
        'organisation_name': 'organisationName',
        'max_contribution_gbp': 'maxContribution',
        'num_countries': 'numCountries',
        'num_organisations': 'numOrganisations',
        'organisation_website': 'organisationWebsite',
        'organisation_name_coordinator': 'coordinatorName',
        'my_eu_id': 'myEuId'
    }, axis=1, inplace=True)
    
    creative.sort_values(
        ['outwardCode', 'inwardCode', 'maxContribution'],
        ascending=[True, True, False],
        inplace=True
    )
    
    return make_district_data_json(creative, 'creative')

creative_district_data = make_creative_district_data(creative)
creative_district_data.head()<jupyter_output><empty_output><jupyter_text>ESIF<jupyter_code>def make_esif_district_data(esif):
    esif = add_outward_and_inward_codes(esif.copy())
    esif = esif[[
        'outward_code',
        'inward_code',
        'start_date', 'end_date',
        'project',
        'beneficiary',
        'summary',
        'funds',
        'eu_investment',
        'project_cost',
        'my_eu_id'
    ]]
    
    esif.rename({
        'outward_code': 'outwardCode',
        'inward_code': 'inwardCode',
        'start_date': 'startDate',
        'end_date': 'endDate',
        'project': 'projectTitle',
        'beneficiary': 'organisationName',
        'eu_investment': 'euInvestment',
        'project_cost': 'projectCost',
        'my_eu_id': 'myEuId'
    }, axis=1, inplace=True)
    
    esif.sort_values(
        ['outwardCode', 'inwardCode', 'euInvestment'],
        ascending=[True, True, False],
        inplace=True
    )
    
    return make_district_data_json(esif, 'esif')

esif_district_data = make_esif_district_data(esif)
esif_district_data.head()<jupyter_output><empty_output><jupyter_text>FTS<jupyter_code>fts.columns
def make_fts_district_data(fts):
    fts = add_outward_and_inward_codes(fts.copy())
    fts = fts[[
        'outward_code',
        'inward_code',
        'year',
        'beneficiary',
        'amount_gbp',
        'budget_line_name_and_number',
        'my_eu_id'
    ]]
    
    fts.rename({
        'outward_code': 'outwardCode',
        'inward_code': 'inwardCode',
        'amount_gbp': 'amount',
        'budget_line_name_and_number': 'budgetLineNameAndNumber',
        'my_eu_id': 'myEuId'
    }, axis=1, inplace=True)
    
    fts.sort_values(
        ['outwardCode', 'inwardCode', 'amount'],
        ascending=[True, True, False],
        inplace=True
    )
    
    return make_district_data_json(fts, 'fts')

fts_district_data = make_fts_district_data(fts)
fts_district_data.head()<jupyter_output><empty_output><jupyter_text>Erasmus<jupyter_code>def make_erasmus_district_data(erasmus):
    erasmus = add_outward_and_inward_codes(erasmus.copy())

    coordinators = erasmus[erasmus.organisation_coordinator]
    coordinators = coordinators[['project_identifier', 'organisation_name']]
    erasmus = pd.merge(
        erasmus, coordinators,
        how='left', on='project_identifier', suffixes=('', '_coordinator'))

    erasmus = erasmus[[
        'outward_code',
        'inward_code',
        'call_year',
        'project',
        'organisation_name',
        'max_contribution_gbp',
        'num_countries',
        'num_organisations',
        'summary',
        'organisation_website',
        'organisation_name_coordinator',
        'my_eu_id'
    ]]
    
    erasmus.rename({
        'outward_code': 'outwardCode',
        'inward_code': 'inwardCode',
        'call_year': 'callYear',
        'organisation_name': 'organisationName',
        'max_contribution_gbp': 'maxContribution',
        'num_countries': 'numCountries',
        'num_organisations': 'numOrganisations',
        'organisation_website': 'organisationWebsite',
        'organisation_name_coordinator': 'coordinatorName',
        'my_eu_id': 'myEuId'
    }, axis=1, inplace=True)
    
    erasmus.sort_values(
        ['outwardCode', 'inwardCode', 'maxContribution'],
        ascending=[True, True, False],
        inplace=True
    )
    
    return make_district_data_json(erasmus, 'erasmus')

erasmus_district_data = make_erasmus_district_data(erasmus)
erasmus_district_data.head()<jupyter_output><empty_output><jupyter_text>NHSJust store the organisation key for now. The data doesn't really fit with the district model.<jupyter_code>def make_nhs_district_data(nhs_hospital_postcodes, nhs_staff):
    nhs = add_outward_and_inward_codes(pd.merge(
        nhs_hospital_postcodes, nhs_staff, on='organisation', validate='m:1'
    ))
    
    nhs.sort_values(
        ['outward_code', 'inward_code', 'eu_nurses'],
        ascending=[True, True, False],
        inplace=True
    )

    nhs = nhs[[
        'outward_code',
        'inward_code',
        'organisation',
        'hospital_name',
        'my_eu_id'
    ]]
    
    nhs.rename({
        'outward_code': 'outwardCode',
        'inward_code': 'inwardCode',
        'hospital_name': 'hospitalName',
        'my_eu_id': 'myEuId'
    }, axis=1, inplace=True)
    
    return make_district_data_json(nhs, 'nhs')

nhs_district_data = make_nhs_district_data(nhs_hospital_postcodes, nhs_staff)
nhs_district_data.head()<jupyter_output><empty_output><jupyter_text>Interreg NW Europe<jupyter_code>nweurope.columns
nweurope.head(2)
def make_nweurope_district_data(nweurope):
    nweurope = add_outward_and_inward_codes(nweurope.copy())
    nweurope = nweurope[[
        'outward_code',
        'inward_code',
        'beneficiary',
        'project',
        'project_summary',
        'start_date',
        'end_date',
        'contribution_gbp',
        'my_eu_id'
    ]]
    
    nweurope.rename({
        'outward_code': 'outwardCode',
        'inward_code': 'inwardCode',
        'project_summary': 'projectSummary',
        'start_date': 'startDate',
        'end_date': 'endDate',
        'contribution_gbp': 'contribution',
        'my_eu_id': 'myEuId'
    }, axis=1, inplace=True)
    
    nweurope.sort_values(
        ['outwardCode', 'inwardCode', 'contribution'],
        ascending=[True, True, False],
        inplace=True
    )
    
    return make_district_data_json(nweurope, 'nweurope')

nweurope_district_data = make_nweurope_district_data(nweurope)
nweurope_district_data.head()<jupyter_output><empty_output><jupyter_text>LIFE<jupyter_code>life.columns
def make_life_district_data(life):
    life = add_outward_and_inward_codes(life.copy())
    life = life[[
        'outward_code',
        'inward_code',
        'year',
        'project_title',
        'coordinator',
        'total_budget_gbp',
        'eu_contribution_gbp',
        'background',
        'project_url',
        'website',
        'my_eu_id'
    ]]
    
    life.rename({
        'outward_code': 'outwardCode',
        'inward_code': 'inwardCode',
        'total_budget_gbp': 'amount',
        'eu_contribution_gbp': 'euContribution',
        'my_eu_id': 'myEuId'
    }, axis=1, inplace=True)
    
    life.sort_values(
        ['outwardCode', 'inwardCode', 'amount'],
        ascending=[True, True, False],
        inplace=True
    )
    
    return make_district_data_json(life, 'life')

life_district_data = make_life_district_data(life)
life_district_data.head()<jupyter_output><empty_output><jupyter_text>Assemble full dataset in dataframe Looking at each programme in turn, create a full_data_ dataframe with the key attributes:postcode, projectTitle, euContribution, amount, website, myEuId Check union_cofinancing same as euContribution  Cordis<jupyter_code>cordis.head(2)
full_data_cordis = cordis[['postcode','title','contribution_gbp','total_cost_gbp','project_url','my_eu_id']].copy()
full_data_cordis.rename({
    'title': 'projectTitle',
    'contribution_gbp': 'euContribution',
    'total_cost_gbp': 'amount',
    'project_url': 'website',
    'my_eu_id': 'myEuId'
}, axis=1, inplace=True)
full_data_cordis.head(2)<jupyter_output><empty_output><jupyter_text>Creative Europe<jupyter_code>creative.head(2)
full_data_creative = creative[['postcode','max_contribution_gbp','organisation_website','my_eu_id']].copy()
full_data_creative.rename({
    'project': 'projectTitle',
    'max_contribution_gbp': 'euContribution',
    'organisation_website': 'website',
    'my_eu_id': 'myEuId'
}, axis=1, inplace=True)
full_data_creative.head(2)<jupyter_output><empty_output><jupyter_text>ESIF<jupyter_code>esif.head(2)
full_data_esif = esif[['postcode','project','eu_investment','project_cost','my_eu_id']].copy()
full_data_esif.rename({
    'project': 'projectTitle',
    'eu_investment': 'euContribution',
    'project_cost': 'amount',
    'my_eu_id': 'myEuId'
}, axis=1, inplace=True)
full_data_esif.head(2)<jupyter_output><empty_output><jupyter_text>FTS<jupyter_code>fts.head(2)
full_data_fts = fts[['postcode','budget_line_name_and_number','total_amount_gbp','amount_gbp','my_eu_id']].copy()
full_data_fts.rename({
    'budget_line_name_and_number': 'projectTitle',
    'total_amount_gbp': 'amount',
    'amount_gbp': 'euContribution',
    'my_eu_id': 'myEuId'
}, axis=1, inplace=True)
full_data_fts.head(2)<jupyter_output><empty_output><jupyter_text>Erasmus<jupyter_code>full_data_cordis.head(2)
erasmus.head(2)
full_data_erasmus = erasmus[['postcode','project','max_contribution_gbp','organisation_website','my_eu_id']].copy()
full_data_erasmus.rename({
    'project': 'projectTitle',
    'max_contribution_gbp': 'euContribution',
    'organisation_website' : 'website',
    'my_eu_id': 'myEuId'
}, axis=1, inplace=True)
full_data_erasmus.head(2)<jupyter_output><empty_output><jupyter_text>NWEurope<jupyter_code>nweurope.head(2)
full_data_nweurope = nweurope[['postcode','project','contribution_gbp','my_eu_id']].copy()
full_data_nweurope.rename({
    'project': 'projectTitle',
    'contribution_gbp': 'euContribution',
    'my_eu_id': 'myEuId'
}, axis=1, inplace=True)
full_data_nweurope.head(2)<jupyter_output><empty_output><jupyter_text>LIFE<jupyter_code>life.head(2)
full_data_life = life[['postcode','project_title','total_budget_gbp','eu_contribution_gbp','project_url','my_eu_id']].copy()
full_data_life.rename({
        'project_title': 'projectTitle',
        'total_budget_gbp': 'amount',
        'eu_contribution_gbp': 'euContribution',
        'project_url': 'website',
        'my_eu_id': 'myEuId'
}, axis=1, inplace=True)
full_data_life.head(2)
full_data = pd.concat([full_data_cordis,
                       full_data_creative,
                       full_data_esif,
                       full_data_fts,
                       full_data_erasmus,
                       full_data_nweurope,
                       full_data_life], sort=False)
full_data.shape<jupyter_output><empty_output><jupyter_text>Location on myeu.uk website<jupyter_code>full_data['myeuURL'] = full_data.apply(
    lambda row: 'https://www.myeu.uk/postcode/' + "/".join( row['postcode'].split() ), axis=1)
full_data.head(2)
full_data.to_csv('../constituencyprojects/output/full_data.csv', index=False)<jupyter_output><empty_output><jupyter_text>Save Data```districtData.postcodes['A11'].cordis.datadistrictData.postcodes['A11'].cordis.extra```<jupyter_code>ALL_DISTRICT_DATA = reduce(
    lambda left, right: pd.merge(left, right, on=('outwardCode', 'inwardCode'), how='outer'), [
    cordis_district_data,
    creative_district_data,
    esif_district_data,
    fts_district_data,
    erasmus_district_data,
    nhs_district_data,
    nweurope_district_data,
    life_district_data    
])
ALL_DATASET_NAMES = list(set(ALL_DISTRICT_DATA.columns) - set(['outwardCode', 'inwardCode']))
    
def save_columns():
    def find_columns(data):
        return data[~data.isna()].iloc[0]['columns']
  
    columns = {
        dataset_name: find_columns(ALL_DISTRICT_DATA[dataset_name])
        for dataset_name in ALL_DATASET_NAMES
    }
    
    with open(os.path.join('output', 'district_columns.json'), 'w') as file:
        json.dump(columns, file, sort_keys=True)
save_columns()
life.head()
ALL_DATASET_NAMES
ALL_DISTRICT_DATA[(~ALL_DISTRICT_DATA[ALL_DATASET_NAMES].isna()).sum(axis=1) > 4][['outwardCode', 'inwardCode']].head()
def merge_district_data(all_data):
    def find_dataset_data(row):
        dataset_data = row.drop(['outwardCode', 'inwardCode']).dropna().to_dict()
        return {
            dataset: { k: v for k, v in json.items() if k != 'columns' }
            for dataset, json in dataset_data.items()
        }
    
    def make_merged_district_data(outward_code, group):
        return {
            'outwardCode': outward_code,
            'postcodes': {
                row.inwardCode: find_dataset_data(row)
                for _index, row in group.iterrows()
            }
        }
    
    return {
        outward_code: make_merged_district_data(outward_code, group)
        for outward_code, group in all_data.groupby('outwardCode')
    }

district_data = merge_district_data(ALL_DISTRICT_DATA)
district_data['CA4']
OUTPUT_DISTRICT_PATH = 'output/district'

def list_district_data(path):
    return glob.glob(os.path.join(path, '*.data.json'))

def clear_district_data(path):
    for f in list_district_data(path):
        os.remove(f)

def write_district_data(district_data, path):
    os.makedirs(path, exist_ok=True)
    clear_district_data(path)
    for outward_code, data in district_data.items():
        output_pathname = os.path.join(path, outward_code + '.data.json')
        with open(output_pathname, 'w') as file:
            json.dump(data, file, sort_keys=True)
write_district_data(district_data, OUTPUT_DISTRICT_PATH)
def find_district_data_stats():
    files = list_district_data(OUTPUT_DISTRICT_PATH)
    return pd.DataFrame({
        'file': [file for file in files],
        'byte_size': [os.stat(file).st_size for file in files]
    })
district_data_stats = find_district_data_stats()
district_data_stats.describe()
district_data_stats.byte_size.sum() / 1024 / 1024
district_data_stats[district_data_stats.byte_size > 1024*1024]
district_data_stats.describe().hist()<jupyter_output><empty_output><jupyter_text>Data IndexGenerate a JS file that webpack can use to make paths for all of the data files.<jupyter_code>def write_district_data_js():
    data_files = list_district_data(OUTPUT_DISTRICT_PATH)
    
    def make_require(data_file):
        basename = os.path.basename(data_file)
        pathname = os.path.join('.', 'district', basename)
        outward_code = basename.split('.')[0]
        return "  {}: require('{}')".format(outward_code, pathname)

    with open('output/district.js', 'w') as file:
        file.write('// NB: This file is generated automatically. Do not edit.\n')
        file.write('export default {\n')
        requires = [
            make_require(data_file)
            for data_file in sorted(data_files)
        ]
        file.write(',\n'.join(requires))
        file.write('\n}\n')
write_district_data_js()<jupyter_output><empty_output><jupyter_text>NHS Data<jupyter_code>[nhs_staff.shape[0], nhs_hospital_postcodes.shape[0]]
nhs_hospital_postcodes.groupby('hospital_organisation').organisation.count().max()
def write_nhs_staff_data():
    with open('output/nhs_staff.json', 'w') as file:
        file.write(
            nhs_staff.sort_values('organisation', ascending=True).\
            to_json(orient='split', index=False))
write_nhs_staff_data()<jupyter_output><empty_output><jupyter_text>Put all project stories into one dataframe Looking at each programme in turn, create an project_stories dataframe with the key attributes.  Cordis<jupyter_code>cordis.head(2)
project_stories_cordis = cordis[[
    'my_eu_id',
    'postcode',
    'title',
    'name',
    'contribution_gbp',
    'max_contribution_gbp',
    'total_cost_gbp',
    'start_date',
    'end_date',
    'project_url',
    'organization_url'
]].copy().rename({
    'title': 'project_title',
    'name': 'organisation_name',
    'contribution_gbp': 'eu_contribution_gbp',
    'max_contribution_gbp': 'project_eu_contribution_gbp',
    'total_cost_gbp': 'project_total_cost_gbp',
    'organization_url': 'organisation_url'
}, axis=1)
project_stories_cordis.head(2)<jupyter_output><empty_output><jupyter_text>Creative Europe<jupyter_code>creative.head(2)
project_stories_creative = creative.copy()
project_stories_creative['eu_contribution_gbp'] = np.nan # not available
project_stories_creative['project_total_cost_gbp'] = np.nan # not available
project_stories_creative = project_stories_creative[[
    'my_eu_id',
    'postcode',
    'project',
    'organisation_name',
    'eu_contribution_gbp',
    'max_contribution_gbp',
    'project_total_cost_gbp',
    'start_date',
    'end_date',
    'project_url',
    'organisation_website',
]].rename({
    'project': 'project_title',
    'max_contribution_gbp': 'project_eu_contribution_gbp',
    'organisation_website': 'organisation_url'
}, axis=1)
assert np.all(project_stories_cordis.columns == project_stories_creative.columns)
project_stories_creative.head(2)<jupyter_output><empty_output><jupyter_text>ESIF<jupyter_code>esif.head(2)
project_stories_esif = esif.copy()
project_stories_esif['project_eu_contribution_gbp'] = esif.eu_investment # story and project are the same
project_stories_esif['project_url'] = np.nan # not available
project_stories_esif['organisation_url'] = np.nan # not available
project_stories_esif = project_stories_esif[[
    'my_eu_id',
    'postcode',
    'project',
    'beneficiary',
    'eu_investment',
    'project_eu_contribution_gbp', 
    'project_cost',
    'start_date',
    'end_date',
    'project_url',
    'organisation_url'
]].copy().rename({
    'project': 'project_title',
    'beneficiary': 'organisation_name',
    'eu_investment': 'eu_contribution_gbp',
    'project_cost': 'project_total_cost_gbp'
}, axis=1)
assert np.all(project_stories_cordis.columns == project_stories_esif.columns)
project_stories_esif.head(2)<jupyter_output><empty_output><jupyter_text>FTSI am not sure whether to include these, so for now I have excluded them from the build later.<jupyter_code>fts.head(2)
project_stories_fts = fts.copy()
project_stories_fts['project_eu_contribution_gbp'] = project_stories_fts.amount_gbp # story and project are the same
project_stories_fts['start_date'] = pd.to_datetime({'year': fts.year, 'month': 1, 'day': 1})
project_stories_fts['end_date'] = np.nan # not available
project_stories_fts['project_url'] = np.nan # not available
project_stories_fts['organisation_url'] = np.nan # not available
project_stories_fts = project_stories_fts[[
    'my_eu_id',
    'postcode',
    'budget_line_name_and_number',
    'beneficiary',
    'amount_gbp',
    'project_eu_contribution_gbp',
    'total_amount_gbp',
    'start_date',
    'end_date',
    'project_url',
    'organisation_url'
]].rename({
    'budget_line_name_and_number': 'project_title',
    'beneficiary': 'organisation_name',
    'amount_gbp': 'eu_contribution_gbp',
    'total_amount_gbp': 'project_total_cost_gbp'
}, axis=1)
assert np.all(project_stories_cordis.columns == project_stories_fts.columns)
project_stories_fts.head(2)<jupyter_output><empty_output><jupyter_text>Erasmus<jupyter_code>erasmus.head(2)
project_stories_erasmus = erasmus.copy()
project_stories_erasmus['eu_contribution_gbp'] = np.nan # not available
project_stories_erasmus['project_total_cost_gbp'] = np.nan # not available
project_stories_erasmus['start_date'] = pd.to_datetime({'year': erasmus.call_year, 'month': 1, 'day': 1})
project_stories_erasmus['end_date'] = np.nan # not available
project_stories_erasmus = project_stories_erasmus[[
    'my_eu_id',
    'postcode',
    'project',
    'organisation_name',
    'eu_contribution_gbp',
    'max_contribution_gbp',
    'project_total_cost_gbp',
    'start_date',
    'end_date',
    'project_url',
    'organisation_website',
]].rename({
    'project': 'project_title',
    'max_contribution_gbp': 'project_eu_contribution_gbp',
    'organisation_website': 'organisation_url'
}, axis=1)
assert np.all(project_stories_cordis.columns == project_stories_erasmus.columns)
project_stories_erasmus.head(2)<jupyter_output><empty_output><jupyter_text>NWEurope<jupyter_code>nweurope.head(2)
project_stories_nweurope = nweurope.copy()
project_stories_nweurope['project_eu_contribution_gbp'] = \
    nweurope.project_contribution_eur * nweurope.eur_gbp
project_stories_nweurope['project_total_cost_gbp'] = \
    nweurope.project_total_cost_eur * nweurope.eur_gbp
project_stories_nweurope['project_url'] = 'http://www.nweurope.eu' + nweurope.page_path
project_stories_nweurope['organisation_url'] = np.nan # not available
project_stories_nweurope = project_stories_nweurope[[
    'my_eu_id',
    'postcode',
    'project',
    'beneficiary',
    'contribution_gbp',
    'project_eu_contribution_gbp',
    'project_total_cost_gbp',
    'start_date',
    'end_date',
    'project_url',
    'organisation_url'
]].rename({
    'project': 'project_title',
    'beneficiary': 'organisation_name',
    'contribution_gbp': 'eu_contribution_gbp'
}, axis=1)
assert np.all(project_stories_cordis.columns == project_stories_nweurope.columns)
project_stories_nweurope.head(2)<jupyter_output><empty_output><jupyter_text>LIFE<jupyter_code>life.head(2)
project_stories_life = life.copy()
project_stories_life['organisation_name'] = life.coordinator + ' (' + life.area_name + ')'
project_stories_life['project_eu_contribution_gbp'] = life.eu_contribution_gbp # story and project are the same
project_stories_life['start_date'] = pd.to_datetime({'year': life.year, 'month': 1, 'day': 1})
project_stories_life['end_date'] = np.nan # not available
project_stories_life = project_stories_life[[
    'my_eu_id',
    'postcode',
    'project_title',
    'organisation_name',
    'eu_contribution_gbp',
    'project_eu_contribution_gbp',
    'total_budget_gbp',
    'start_date',
    'end_date',
    'project_url',
    'website'
]].rename({
    'total_budget_gbp': 'project_total_cost_gbp',
    'website': 'organisation_url',
}, axis=1)
assert np.all(project_stories_cordis.columns == project_stories_life.columns)
project_stories_life.head(2)<jupyter_output><empty_output><jupyter_text>Save All Project Stories<jupyter_code>project_stories = pd.concat([
    project_stories_cordis,
    project_stories_creative,
    project_stories_esif,
    project_stories_erasmus,
    project_stories_nweurope,
    project_stories_life
], sort=False)
project_stories.shape<jupyter_output><empty_output><jupyter_text>Add location on myeu.uk website:<jupyter_code>project_stories['my_eu_url'] = project_stories.apply(
    lambda row: 'https://www.myeu.uk/postcode/' + "/".join( row['postcode'].split() ), axis=1)
project_stories.sort_values(['postcode', 'my_eu_id'], inplace=True)
project_stories.head(2)
project_stories.to_pickle('output/project_stories.pkl.gz')<jupyter_output><empty_output><jupyter_text>Save All NHS Stories<jupyter_code>nhs_stories = pd.merge(
    nhs_hospital_postcodes, nhs_staff, on='organisation', validate='m:1'
)
nhs_stories = nhs_stories[[
    'my_eu_id',
    'postcode',
    'organisation',
    'organisation_name',
    'hospital_organisation',
    'hospital_name',
    'eu_doctors',
    'eu_nurses',
    'eu_other',
    'non_eu_doctors',
    'non_eu_nurses',
    'non_eu_other',
    'unknown_doctors',
    'unknown_nurses',
    'unknown_other'
]]
nhs_stories.sort_values('postcode', inplace=True)
nhs_stories['my_eu_url'] = nhs_stories.apply(
    lambda row: 'https://www.myeu.uk/postcode/' + "/".join( row['postcode'].split() ), axis=1)
nhs_stories.head()
nhs_stories.to_pickle('output/nhs_stories.pkl.gz')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13303775150383737, 'fraction_numerical': 0.008867454884878655, 'mean_word_length': 4.651963657678781, 'pattern_counts': {'":': 0, '<': 170, '<?xml version=': 0, '>': 175, 'https://': 4, 'lorem ipsum': 0, 'www.': 4, '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': '42', 'dir': 'jupyter-structured-clean-dedup', 'id': '40966', 'license': 'Apache-2.0', 'n_tokens_mistral': 14400, 'n_tokens_neox': 13830, 'n_words': 1888, 'path': 'data/map/build_map.ipynb', 'repo_name': 'meloncholy/my_eu', 'size': '38568'}