<jupyter_start><jupyter_text>CORDIS FP7<jupyter_code>import json
import re
import urllib

from titlecase import titlecase
import pandas as pd

pd.set_option('display.max_columns', 50)<jupyter_output><empty_output><jupyter_text>Read in Data<jupyter_code>all_projects = pd.read_excel('input/fp7/cordis-fp7projects.xlsx')
all_projects.shape
all_organizations = pd.read_excel('input/fp7/cordis-fp7organizations.xlsx')
all_organizations.shape
all_briefs = pd.read_excel('input/fp7/cordis-fp7briefs.xlsx')
all_briefs.shape<jupyter_output><empty_output><jupyter_text>Count Organisations and CountriesIt is useful to know the total number of organisations and the number of countries involved, to deal with cases where the contribution of each organisation is unknown.<jupyter_code>all_organizations[['projectRcn', 'id', 'country']].count()
[
    all_organizations.country.isna().sum(),
    (all_organizations.country[~all_organizations.country.isna()] != 
         all_organizations.country[~all_organizations.country.isna()].str.strip()).sum(),
    (all_organizations.country[~all_organizations.country.isna()] != 
         all_organizations.country[~all_organizations.country.isna()].str.upper()).sum(),
]
project_num_organizations = all_organizations.groupby('projectRcn').\
    id.nunique().reset_index().rename(columns={'id': 'num_organizations'})
project_num_organizations.shape
project_num_countries = all_organizations.groupby('projectRcn').\
    country.nunique().reset_index().rename(columns={'country': 'num_countries'})
project_num_countries.shape
project_num_organizations_and_countries = pd.merge(
    project_num_countries, project_num_organizations,
    on='projectRcn', validate='1:1'
)
project_num_organizations_and_countries.shape
project_num_organizations_and_countries.head()<jupyter_output><empty_output><jupyter_text>Restrict to UKWe are only interested in projects and organizations where the coordinator or at least one participant institution is in the UK.<jupyter_code>uk_organizations = all_organizations[all_organizations.country == 'UK']
uk_organizations.shape
uk_organizations.head()
uk_projects = all_projects[all_projects.id.isin(uk_organizations.projectID)]
uk_projects.shape
uk_projects.head()
uk_briefs = all_briefs[all_briefs.projectRcn.isin(uk_projects.rcn)]
uk_briefs.shape
uk_briefs.head()<jupyter_output><empty_output><jupyter_text>Examples Coordinator outside UKThe UK has two participant institutions. It appears that `projects.ecMaxContribution` is the sum of all `organizations.ecContribution`s for all coordinator and participant institutions.<jupyter_code>uk_projects[uk_projects.rcn == 101244]
uk_organizations[uk_organizations.projectRcn == 101244]
all_organizations[all_organizations.projectRcn == 101244].ecContribution.max()
all_organizations[all_organizations.projectRcn == 101244].ecContribution.sum()
all_briefs[all_briefs.projectRcn == 101244]<jupyter_output><empty_output><jupyter_text>Coordinator in UKThis one is also interesting in that it seems to have a lot of duplicate records that don't have titles, for some reason. We will need to filter those out.<jupyter_code>uk_projects[uk_projects.rcn == 99464]
uk_organizations[uk_organizations.projectRcn == 99464]
uk_organizations[uk_organizations.projectRcn == 99464].ecContribution.unique().sum()
all_briefs[all_briefs.projectRcn == 99464]<jupyter_output><empty_output><jupyter_text>Duplicate ProjectsIt looks like it's safe to just drop projects without titles; those seem to be the only duplicates.<jupyter_code>[uk_projects.rcn.nunique(), uk_projects.id.nunique(), uk_projects.shape]
uk_projects[uk_projects.duplicated('rcn', keep=False)]
uk_projects[pd.isnull(uk_projects.title)]
clean_projects = uk_projects[~pd.isnull(uk_projects.title)].copy()
# Could include coordinator and participants... would need some extra cleaning.
clean_projects.drop([
    'id', 'programme', 'topics', 'frameworkProgramme', 'call',
    'fundingScheme', 'coordinator', 'participants', 'subjects'
], axis=1, inplace=True)
clean_projects.rename(columns={
    'startDate': 'start_date',
    'endDate': 'end_date',
    'projectUrl': 'project_url',
    'totalCost': 'total_cost_eur',
    'ecMaxContribution': 'max_contribution_eur',
    'coordinatorCountry': 'coordinator_country',
    'participantCountries': 'participant_countries'
}, inplace=True)
clean_projects.shape
clean_projects.describe()
clean_projects.head()<jupyter_output><empty_output><jupyter_text>Check Project Columns<jupyter_code>clean_projects.count()<jupyter_output><empty_output><jupyter_text>AcronymJust missing one.<jupyter_code>clean_projects[clean_projects.acronym.isna()]<jupyter_output><empty_output><jupyter_text>StatusSome projects are listed as cancelled. It's not clear what this means exactly. Spot checks reveal that some of them apparently received at least partial funding and delivered some results, so it does not seem appropriate to remove them altogether.- https://cordis.europa.eu/result/rcn/237795_en.html (TORTELLEX)- https://cordis.europa.eu/result/rcn/196663_en.html (YSCHILLER)- https://cordis.europa.eu/project/rcn/188111_en.html (MICARTREGEN) - no results<jupyter_code>clean_projects.status.value_counts()
clean_projects[clean_projects.status == 'CAN'].head()<jupyter_output><empty_output><jupyter_text>Title<jupyter_code>(clean_projects.title.str.strip() != clean_projects.title).sum()<jupyter_output><empty_output><jupyter_text>Start and End DatesSome are missing. Discard for now. There is some overlap with the cancelled projects, but it is not exact.<jupyter_code>(clean_projects.start_date.isna() | clean_projects.end_date.isna()).sum()
((clean_projects.status == 'CAN') & (clean_projects.start_date.isna() | clean_projects.end_date.isna())).sum()
((clean_projects.status != 'CAN') & (clean_projects.start_date.isna() | clean_projects.end_date.isna())).sum()
clean_projects = clean_projects[
    ~clean_projects.start_date.isna() | ~clean_projects.end_date.isna()
]
clean_projects.shape
(clean_projects.start_date > clean_projects.end_date).sum()<jupyter_output><empty_output><jupyter_text>Project URLLooks pretty clean.<jupyter_code>(~clean_projects.project_url.isna()).sum()
def is_valid_url(url):
    result = urllib.parse.urlparse(str(url))
    return bool((result.scheme == 'http' or result.scheme == 'https') and result.netloc)

project_url_bad = ~clean_projects.project_url.isna() & ~clean_projects.project_url.apply(is_valid_url)
project_url_bad.sum()
clean_projects[project_url_bad]
clean_projects.loc[project_url_bad, 'project_url'] = 'http://' + clean_projects.loc[project_url_bad, 'project_url']
(~clean_projects.project_url.isna() & ~clean_projects.project_url.apply(is_valid_url)).sum()<jupyter_output><empty_output><jupyter_text>Objective<jupyter_code>(clean_projects.objective.str.strip() != clean_projects.objective).sum()
clean_projects.objective = clean_projects.objective.str.strip()<jupyter_output><empty_output><jupyter_text>Total Cost and EC Max Contribution<jupyter_code>clean_projects.total_cost_eur.describe()
clean_projects.max_contribution_eur.describe()
(clean_projects.max_contribution_eur > clean_projects.total_cost_eur).sum()<jupyter_output><empty_output><jupyter_text>Clean Up OrganizationsI notice several issues:- Some are missing IDs (but do have postcodes)- Some are missing postcodes- Some postcodes are clearly typo'd (digit substitutions, etc);- Some postcodes have been terminated (searched for them with google)There are only 2993 unique organization IDs, so this is probably the result of a join.For now, drop all organizations that don't have both an ID and a valid postcode. (It does look possible to match names to find IDs, and many without postcodes still have addresses, which we could geocode.)Would be interesting to try this: https://codereview.stackexchange.com/questions/117801/uk-postcode-validation-and-format-correction-tool<jupyter_code>[
    uk_organizations.shape,
    uk_organizations.id.notna().sum(),
    uk_organizations.id.isna().sum(),
    uk_organizations.id[uk_organizations.id.notna()].nunique(),
    uk_organizations.postCode.isna().sum(),
    uk_organizations.postCode[uk_organizations.postCode.notna()].nunique()
]
organizations = uk_organizations[uk_organizations.id.notna() & uk_organizations.postCode.notna()].copy()
organizations.id = organizations.id.astype('int64')
organizations.postCode = organizations.postCode.astype('str')
[
    organizations.shape,
    organizations.id.nunique(),
    organizations.postCode.nunique()
]
ukpostcodes = pd.read_csv('../postcodes/input/ukpostcodes.csv.gz')
ukpostcodes.shape
organizations.postCode.isin(ukpostcodes.postcode).sum()
organizations['cleanPostcode'] = organizations.postCode.\
    str.upper().\
    str.strip().\
    str.replace(r'[^A-Z0-9]', '').\
    str.replace(r'^(\S+)([0-9][A-Z]{2})$', r'\1 \2')
organizations.cleanPostcode.isin(ukpostcodes.postcode).sum()
organizations.cleanPostcode[~organizations.cleanPostcode.isin(ukpostcodes.postcode)].unique()
organizations = organizations[organizations.cleanPostcode.isin(ukpostcodes.postcode)]
organizations.shape
clean_projects = clean_projects[clean_projects.rcn.isin(organizations.projectRcn)]
clean_projects.shape<jupyter_output><empty_output><jupyter_text>Clean Up Duplicate OrganizationsI think there is also a join on the contacts, because we get multiple rows for some project-organization pairs. The main thing is that we want the `ecContribution` to be consistent. Otherwise, any row will do.<jupyter_code>organizations.sort_values(['projectRcn', 'id']).\
    groupby(['projectRcn', 'id']).\
    filter(lambda x: x.shape[0] > 1)
organizations.groupby(['projectRcn', 'id']).\
    filter(lambda x: x.ecContribution.nunique() > 1).shape
clean_organizations = organizations.groupby(['projectRcn', 'id']).first()
clean_organizations.reset_index(inplace=True)
clean_organizations.drop([
    'projectID', 'projectAcronym', 'shortName', 'activityType', 'endOfParticipation',
    'country', 'street', 'city', 'postCode',
    'contactType', 'contactTitle', 'contactFirstNames', 'contactLastNames',
    'contactFunction', 'contactTelephoneNumber', 'contactFaxNumber', 'contactEmail'
], axis=1, inplace=True)
clean_organizations.rename({
    'projectRcn': 'project_rcn',
    'id': 'organization_id',
    'ecContribution': 'contribution_eur',
    'organizationUrl': 'organization_url',
    'cleanPostcode': 'postcode'
}, axis=1, inplace=True)
clean_organizations.name = clean_organizations.name.apply(titlecase)
clean_organizations.shape
clean_organizations.head()<jupyter_output><empty_output><jupyter_text>Check Organisations<jupyter_code>clean_organizations.count()<jupyter_output><empty_output><jupyter_text>Role<jupyter_code>clean_organizations.role.value_counts()<jupyter_output><empty_output><jupyter_text>Name<jupyter_code>(clean_organizations.name.str.strip() != clean_organizations.name).sum()<jupyter_output><empty_output><jupyter_text>Contribution EURMissing for some organisations.<jupyter_code>clean_organizations.contribution_eur.describe()
clean_organizations.contribution_eur.isna().sum()<jupyter_output><empty_output><jupyter_text>Organisation URLMostly clean. Found a couple with a `;` delimiting two URLs, neither of which resolved, so we can get rid of those.<jupyter_code>(~clean_organizations.organization_url.isna()).sum()
organization_url_bad = ~clean_organizations.organization_url.isna() & \
    ~clean_organizations.organization_url.apply(is_valid_url)
organization_url_bad.sum()
clean_organizations.loc[organization_url_bad, 'organization_url'] = \
    'http://' + clean_organizations.loc[organization_url_bad, 'organization_url']
organization_url_bad = ~clean_organizations.organization_url.isna() & \
    ~clean_organizations.organization_url.apply(is_valid_url)
organization_url_bad.sum()
clean_organizations[
    ~clean_organizations.organization_url.isna() & \
    clean_organizations.organization_url.str.match('http.*http')].organization_url.unique()
clean_organizations.loc[
    ~clean_organizations.organization_url.isna() & \
    clean_organizations.organization_url.str.match('http.*http'), 'organization_url'] = float('nan')<jupyter_output><empty_output><jupyter_text>BriefsMight as well merge these into the projects where we have them. We have a few duplicates to take care of.<jupyter_code>clean_briefs = uk_briefs[
    uk_briefs.projectRcn.isin(clean_projects.rcn) &\
    (uk_briefs.title.notna() | uk_briefs.teaser.notna() | uk_briefs.article.notna())
].copy()
clean_briefs.shape
clean_briefs[clean_briefs.projectRcn.duplicated(keep=False)]
clean_briefs = clean_briefs.sort_values('lastUpdateDate')
clean_briefs = clean_briefs[~clean_briefs.projectRcn.duplicated(keep='last')]
clean_briefs.shape
clean_briefs.drop([
    'rcn', 'language', 'lastUpdateDate', 'country', 'projectAcronym',
    'programme', 'topics', 'relatedReportRcn'
], axis=1, inplace=True)
clean_briefs.rename({
    'projectRcn': 'rcn',
    'title': 'brief_title',
    'relatedReportTitle': 'related_report_title',
    'imageUri': 'image_path'
}, axis=1, inplace=True)
clean_briefs.head()
clean_projects_with_briefs = pd.merge(
    clean_projects, clean_briefs, on='rcn', how='left', validate='1:1'
)
clean_projects_with_briefs.head()<jupyter_output><empty_output><jupyter_text>Checks<jupyter_code>clean_organizations[clean_organizations.project_rcn == 101244]
clean_projects_with_briefs[clean_projects_with_briefs.rcn == 101244]
clean_organizations[clean_organizations.project_rcn == 99464]
clean_projects_with_briefs[clean_projects_with_briefs.rcn == 99464]
project_organizations = pd.merge(
    clean_projects_with_briefs, clean_organizations,
    left_on='rcn', right_on='project_rcn', validate='1:m')
project_organizations.drop(['project_rcn'], axis=1, inplace=True)
project_organizations.shape
project_organizations.head()
uk_contributions = project_organizations.groupby('rcn').aggregate({'contribution_eur': sum})
uk_contributions.reset_index(inplace=True)
uk_contributions.head()
project_uk_contributions = pd.merge(
    clean_projects_with_briefs,
    uk_contributions,
    on='rcn', validate='1:1')
project_uk_contributions.head()
project_uk_contributions[project_uk_contributions.contribution_eur > project_uk_contributions.max_contribution_eur + 0.1].shape
project_organization_uk_contributions = pd.merge(
    project_uk_contributions, clean_organizations,
    left_on='rcn', right_on='project_rcn', validate='1:m'
)
project_organization_uk_contributions = pd.merge(
    project_organization_uk_contributions, ukpostcodes, on='postcode', validate='m:1'
)
project_organization_uk_contributions.shape
project_organization_uk_contributions.head()
(project_uk_contributions.contribution_eur < 1000).value_counts()<jupyter_output><empty_output><jupyter_text>Add Numbers of Organisations and CountriesAdd these back on and do a sanity check against the `participant_countries` field. They mostly match up, except for a few relatively small discrepancies.<jupyter_code>clean_projects_with_briefs.shape
clean_projects_with_briefs = pd.merge(
    clean_projects_with_briefs, project_num_organizations_and_countries,
    left_on='rcn', right_on='projectRcn', validate='1:1')
clean_projects_with_briefs.drop('projectRcn', axis=1, inplace=True)
clean_projects_with_briefs.shape
clean_projects_with_briefs.head()
[
    clean_projects_with_briefs.num_countries.isna().sum(),
    clean_projects_with_briefs.coordinator_country.isna().sum(),
    clean_projects_with_briefs.participant_countries.isna().sum()
]
def check_num_countries():
    ccs = clean_projects_with_briefs.coordinator_country
    pcs = clean_projects_with_briefs.participant_countries
    ncs = clean_projects_with_briefs.num_countries
    pcs_isna = pcs.isna()
    
    coordinator_mismatch = clean_projects_with_briefs[pcs_isna][ncs[pcs_isna] != 1].copy()
    coordinator_mismatch['check'] = 1
    
    cs = ccs[~pcs_isna] + ';' + pcs[~pcs_isna]
    check_ncs = cs.apply(lambda x: len(set(x.split(';'))))
    
    participant_mismatch = clean_projects_with_briefs[~pcs_isna][ncs[~pcs_isna] != check_ncs].copy()
    participant_mismatch['check'] = check_ncs
    
    return pd.concat([coordinator_mismatch, participant_mismatch])\
        [['rcn', 'coordinator_country', 'participant_countries', 'num_countries', 'check', 'num_organizations']]
check_num_countries()
all_organizations.country[all_organizations.projectRcn == 100467].unique()
all_organizations.country[all_organizations.projectRcn == 203681].unique()
all_organizations.country[all_organizations.projectRcn == 90982].unique()<jupyter_output><empty_output><jupyter_text>I suspect a problem with handling of `NA`; that is a valid code (Namibia), but maybe in some cases it is being used for Not Available.  Convert to GBP<jupyter_code>eur_gbp = pd.read_pickle('../exchange_rates/output/exchange_rates.pkl.gz')
eur_gbp.tail()
def find_average_eur_gbp_rate(row):
    # create timeseries from start to end
    days = pd.date_range(row.start_date, row.end_date, closed='left')
    daily = pd.DataFrame({
        'month_start': days,
        'weight': 1.0 / days.shape[0]
    })
    monthly = daily.resample('MS', on='month_start').sum()
    monthly = pd.merge(monthly, eur_gbp, on='month_start', validate='1:1')
    return (monthly.weight * monthly.rate).sum()

clean_projects_with_briefs['eur_gbp'] = \
    clean_projects_with_briefs.apply(
        find_average_eur_gbp_rate, axis=1, result_type='reduce')
clean_projects_with_briefs.head()<jupyter_output><empty_output><jupyter_text>Save Data<jupyter_code>clean_projects_with_briefs.to_pickle('output/fp7_projects.pkl.gz')
clean_organizations.to_pickle('output/fp7_organizations.pkl.gz')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12127045235803657, 'fraction_numerical': 0.009737870123987998, 'mean_word_length': 7.675834970530452, 'pattern_counts': {'":': 0, '<': 110, '<?xml version=': 0, '>': 114, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '11266', 'license': 'Apache-2.0', 'n_tokens_mistral': 6030, 'n_tokens_neox': 5812, 'n_words': 919, 'path': 'data/cordis/fp7_clean.ipynb', 'repo_name': 'meloncholy/my_eu', 'size': '17663'}