<jupyter_start><jupyter_text>Preprocessing SNSF Public Data<jupyter_code>import os
import sqlite3
from sqlite3 import Error

import numpy as np
import pandas as pd

RAW_DATA_FOLDER = '../rawdata'
DATA_FOLDER = '../data/'
# file_names = os.listdir(RAW_DATA_FOLDER) # file_names[0], file_names[1]
project = pd.read_csv(os.path.join(RAW_DATA_FOLDER, 'P3_GrantExport.csv'), sep=';')
person = pd.read_csv(os.path.join(RAW_DATA_FOLDER, 'P3_PersonExport.csv'), sep=';')<jupyter_output><empty_output><jupyter_text>Assess<jupyter_code>project.sample(3)
project.info()
person.sample(3)
person.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 112476 entries, 0 to 112475
Data columns (total 13 columns):
 #   Column                             Non-Null Count   Dtype 
---  ------                             --------------   ----- 
 0   Last Name                          112476 non-null  object
 1   First Name                         112469 non-null  object
 2   Gender                             112476 non-null  object
 3   Institute Name                     54497 non-null   object
 4   Institute Place                    54393 non-null   object
 5   Person ID SNSF                     112476 non-null  int64 
 6   OCRID                              7328 non-null    object
 7   Projects as responsible Applicant  29056 non-null   object
 8   Projects as Applicant              18969 non-null   object
 9   Projects as Partner                5460 non-null    object
 10  Projects as Practice Partner       735 non-null     object
 11  Projects as Employee               82415 non-null   [...]<jupyter_text>Tidiness `project` (ie. `GrantExport`) table- `Funding Instrument`, `Funding Instrument Hierarchy` are confusing- `Discipline`, ... `Discipline Name Hierarchy` are confusing- Details about `Institute` out of scope- `University` contains both long and short names: details out of scope `person` (ie. `PersonExport`) table- [x]`Project as...` contain mixed variables and observations for grant and role- Details about `Institute` out of scope Quality- [x] spaces in column names `project` (ie. `GrantExport`) table- [x] `Project Number` and `Project Number String` are redundant- [x] `Project Number String` encodes division information?- [x] `Responsible Applicant` not an uid- [x] `Start Date` and `End Date` string, not date type- [x] `Aproved Amount` not numeric- [x] 2 observations without `start_date` or `end_date`- `Project Title English` often redundant or null- `Institution` free text? if yes, is it relevant? better named as department?- `Keywords` not consistent (see keyword extraction from abstracts) `person` (ie. `PersonExport`) table- typo in col name for `ORCID`- gender not categorical variable- make sure `person_id_snsf` is unique  Clean<jupyter_code># Fix column names
project.columns = project.columns.str.lower().str.replace(' ', '_')
person.columns = person.columns.str.lower().str.replace(' ', '_')

assert all(project.columns.str.contains(' ') == False), 'Space in project column names'
assert all(person.columns.str.contains(' ') == False), 'Space in person column names'
assert all(project.columns == project.columns.str.lower()), 'Uppercase in project column names'
assert all(person.columns == person.columns.str.lower()), 'Uppercase in person column names'
# https://github.com/zambujo/p3data/issues/4
role_types = np.array(person.columns[person.columns.str.contains('projects_as_')])
hr = (person
        .melt('person_id_snsf', 
              var_name='role', 
              value_name='project_number', 
              value_vars=role_types).dropna())
hr = (hr
        .assign(project_number=hr
        .project_number.str.split(';'))
        .explode('project_number')
        .reset_index(drop=True))
hr['role'] = hr.role.str[12:] # remove 'projects_as_'
hr['project_number'] = pd.to_numeric(hr['project_number'], errors='coerce')
hr=hr.dropna()
hr['project_number'] = hr['project_number'].astype(int)
hr.drop_duplicates(inplace=True)

# hr.role.value_counts()
assert hr.project_number.dtype.name == 'int64', 'project number column not an integer'
# hr table
hr.reset_index(inplace=True)
hr = hr.rename(columns = {'index':'id',
                          'project_number':'project_id', 
                          'person_id_snsf':'person_id'})
hr = hr[['id', 'project_id', 'person_id', 'role']]

assert all(hr.id.duplicated() == False), 'id contains duplicates'
# person table
person = person.rename(columns = {'person_id_snsf':'id'})
person = person[['id', 'gender', 'first_name', 'last_name']]
person = person.drop_duplicates()

assert all(person.id.duplicated() == False), 'id contains duplicates'
# https://github.com/zambujo/p3data/issues/1
# remove ending project_number >> remove ending separator(s)
project['string_code'] = (
    project.project_number_string.
        str.replace('([0-9]+)$', '').
        str.replace('[^a-zA-Z0-9]+$', ''))
project = project.drop('project_number_string', 1)
# string_code vs funding_instrument
# grants.groupby(["string_code", "funding_instrument"]).size().reset_index().rename(columns={0: "count"}).sort_values(by='count', ascending=False)
# https://github.com/zambujo/p3data/issues/6
project['approved_amount'] = pd.to_numeric(project['approved_amount'], errors='coerce')
project['start_date'] = pd.to_datetime(project.start_date)
project['end_date'] = pd.to_datetime(project.end_date)

assert project.approved_amount.dtype.name == 'float64', 'approved amount column not a float'
assert project.start_date.dtype.name == 'datetime64[ns]', 'start_date column not datetime type'
assert project.end_date.dtype.name == 'datetime64[ns]', 'end_date column not datetime type'
# https://github.com/zambujo/p3data/issues/13
project=project.dropna(subset=['start_date'])
project=project.dropna(subset=['end_date']) # to be on safe side

assert all(pd.isna(project.start_date) == False), 'NA values in project.start_date'
assert all(pd.isna(project.end_date) == False), 'NA values in project.end_date'
# project table
project[['domain','topic']] = project.discipline_name_hierarchy.str.split(';', expand=True)
project[['programme', 'programme_details', 'programme_specifics']] = project.funding_instrument_hierarchy.str.split(';', expand=True)

project = project.rename(columns = {'project_number':'id'})
project = project[['id', 'programme', 'funding_instrument', 'domain', 'topic', 'discipline_name', 'start_date', 'end_date', 'approved_amount']]

assert all(project.id.duplicated() == False), 'id contains duplicates'
# create a database connection and a cursor object to a SQLite database
conn = None
try:
    conn = sqlite3.connect('../data/p3.db')
    c = conn.cursor()
except Error as e:
    print(e)
sql_drop_project_table = 'DROP TABLE IF EXISTS project;'
sql_drop_person_table =  'DROP TABLE IF EXISTS person;'
sql_drop_hr_table =  'DROP TABLE IF EXISTS hr;'

sql_create_project_table = '''
CREATE TABLE IF NOT EXISTS project (
  id integer PRIMARY KEY,
  programme text,
  funding_instrument text,
  domain text,
  topic text,
  discipline_name text,
  start_date datetime,
  end_date datetime,
  approved_amount float
);'''

sql_create_person_table = '''
CREATE TABLE IF NOT EXISTS person (
  id integer PRIMARY KEY,
  gender text,
  first_name text,
  last_name text
);'''

sql_create_hr_table = '''
CREATE TABLE IF NOT EXISTS hr (
  id integer PRIMARY KEY,
  project_id integer,
  person_id integer,
  role text,
  FOREIGN KEY (project_id) REFERENCES project (id),
  FOREIGN KEY (person_id) REFERENCES person (id)
);'''

def manipulate_table(conn, sql_command):
    try:
        c = conn.cursor()
        c.execute(sql_command)
        # conn.commit()
    except Error as e:
        print(e)

if conn is not None:
    manipulate_table(conn, sql_drop_project_table)
    manipulate_table(conn, sql_drop_person_table)
    manipulate_table(conn, sql_drop_hr_table)
    manipulate_table(conn, sql_create_project_table)
    manipulate_table(conn, sql_create_person_table)
    manipulate_table(conn, sql_create_hr_table)
else:
    print("Problem with connection to database.")
project.to_sql('project', con=conn, if_exists='append', index=False)
person.to_sql('person', con=conn, if_exists='append', index=False)
hr.to_sql('hr', con=conn, if_exists='append', index=False)
if conn is not None:
    conn.close()
else:
    print("Problem with connection to database.")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1310615989515072, 'fraction_numerical': 0.015131657333492195, 'mean_word_length': 4.056626506024096, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 15, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '623912', 'license': 'MIT', 'n_tokens_mistral': 2764, 'n_tokens_neox': 2628, 'n_words': 680, 'path': 'notebooks/cleaning.ipynb', 'repo_name': 'zambujo/p3data', 'size': '8393'}