LoopDataMerge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Data sources are:\n",
    "\n",
    "### 1. Liam's SPSS coded data\n",
    "**File:** The Loop 2017 Final Interventions.xlsx\n",
    "\n",
    "Exported as Excel from SPSS, keeping the variable names.\n",
    "\n",
    "This file contains 1325 entries.\n",
    "\n",
    "\n",
    "27 have null festival or sample numbers so can't be used, leaving 1298\n",
    "\n",
    "\n",
    "One has sample number 12151, two have sample number 0 - these cannot be merged.\n",
    "\n",
    "\n",
    "This leaves 1295 - all of which can be merged\n",
    "\n",
    "### 2. Guy's cleaned up lab data\n",
    "**File:** Loop 2017 Lab fixed data.xlsm\n",
    "\n",
    "From: Dropbox/Testing/2017 results processing/Loop 2017 Lab fixed data.xlsm\n",
    "\n",
    "\n",
    "Data is in the ‘Raw Lab Data’ sheet\n",
    "\n",
    "\n",
    "This file contains 2544 entries\n",
    "\n",
    "\n",
    "1900 entries start with F\n",
    "\n",
    "\n",
    "621 entries begin with A (amnesty) so can't be merged\n",
    "\n",
    "\n",
    "23 Begin with W? so can't be merged\n",
    "\n",
    "\n",
    "Entry SGP2017 F0465 needs editing as 'Client gender' is FemaleaMalee \n",
    "\n",
    "### 3. Boomtown Intervention Questionnaire\n",
    "**File:** BTReport 2017 - Form responses 3.csv\n",
    "\n",
    "Exported from: https://docs.google.com/spreadsheets/d/15pdETY0HK-VbBcV-N0swt6ZrRBbeDnZR5RGDzfq95dg\n",
    "\n",
    "This file contains 194 entries\n",
    "\n",
    "### 4. 'Straggling' Boomtown Intervention Questionnaire\n",
    "**File:** Reports V2.6 Branch 2 - Form responses 2.csv\n",
    "\n",
    "https://docs.google.com/spreadsheets/d/1sZXFdiOaUX6n9HGq9s-t8T_zxNZhKyjxY83aY8mvUIo/edit#gid=1291806732\n",
    "\n",
    "This file contains 9 entries\n",
    "\n",
    "### Merging the data\n",
    "\n",
    "Merging the data on Festival and SampleNumber resulted in 1295 entries\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Module imports\n",
    "import datetime\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "def fix_sample_number(x):\n",
    "    \"\"\"Make sure all samples numbers are of form: FXXX (where X is a digit)\"\"\"\n",
    "    if isinstance(x, float) and np.isnan(x):\n",
    "        return x # leave NaN's alone    \n",
    "    try:\n",
    "        sn = int(x)\n",
    "        sn = 'F{:04d}'.format(int(x))\n",
    "    except ValueError:\n",
    "        # Assume string so make sure it's of the right format\n",
    "        sn = str(x).capitalize()\n",
    "    return sn\n",
    "\n",
    "def now():\n",
    "    return datetime.datetime.now().strftime(\"%d/%m/%y %H:%M:%S\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Read in from SPSS  1343\n",
      "07/05/18 18:23:08 Final SPSS  1298\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "# Liam's SPSS data exported as Excel\n",
    "#\n",
    "spssdata = '/opt/random/The Loop 2017 Final Interventions.xlsx'\n",
    "\n",
    "spss_df = pd.read_excel(spssdata)\n",
    "print(\"Read in from SPSS \",len(spss_df))\n",
    "\n",
    "# Change festival names\n",
    "spss_df['Festival'].replace(['BoomTown', 'KC', 'SGP'], ['BT2017', 'KC2017', 'SGP2017'], inplace=True)\n",
    "\n",
    "# Fix/update column labels\n",
    "d = {'spice_yesterday' : 'spice_legals_yesterday',\n",
    "     'Cannabis_ever' : 'cannabis_ever',\n",
    "     'Ethnictiy_other' : 'Ethnicity_other'\n",
    "    }\n",
    "spss_df.rename(columns=d, inplace=True)\n",
    "\n",
    "# Ensure all Sample numbers are consistent\n",
    "# 1. Delete any rows where SampleNumber or Festival is NA as we can't do anything with it\n",
    "# There are 45 entries that go, but none of them contain any valid data\n",
    "spss_df.dropna(subset=['SampleNumber', 'Festival'], inplace=True)\n",
    "\n",
    "# 2. Make all sample numbers a 4-digit code starting with F\n",
    "spss_df['SampleNumber'] = spss_df['SampleNumber'].apply(fix_sample_number)\n",
    "\n",
    "# Combine date and time columns into new single column\n",
    "spss_df['Date'] = pd.to_datetime(spss_df['Date']) # Convert Date to datetime object\n",
    "spss_df['Date & Time of intervention'] = spss_df.apply(lambda r : pd.datetime.combine(r['Date'], r['Time']), 1)\n",
    "\n",
    "# Remove Day, Date, Time and SurveyID columns\n",
    "spss_df.drop(['Day', 'Date', 'Time', 'SurveyID'], axis=1, inplace=True)\n",
    "\n",
    "# Fix dodgy sample number - Guy confirmed this with Liam\n",
    "spss_df.at[spss_df['SampleNumber'] == 'F12151', 'SampleNumber'] = 'F1215'\n",
    "\n",
    "# Sort on Festival then SampleNumber\n",
    "spss_df.sort_values(['Festival','SampleNumber'], ascending=True, inplace=True)\n",
    "\n",
    "print(now() + \" Final SPSS \",len(spss_df)) # shows we are left with 1298 datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#\n",
    "# Code to read the Boomtown straggling results from the Google Forms data\n",
    "#\n",
    "bt_straggling = 'Reports V2.6 Branch 2 - Form responses 2.csv'\n",
    "date_cols = ['Timestamp']\n",
    "bt_df2 = pd.read_csv(bt_straggling, engine=\"python\", parse_dates=date_cols)\n",
    "\n",
    "# 2 columns are missing so need to be added as Nan\n",
    "bt_df2['Which drugs have you used? [Non-prescribed opiods]'] = np.nan\n",
    "bt_df2['Are you planning to take any of these drugs later?'] = np.nan\n",
    "\n",
    "# Sample 'F0001' and 'F0535' appear to be errors - one taken by Mike Capper - another a 13 year old female\n",
    "bt_df2.drop(bt_df2[(bt_df2['Sample Number'] == '1') | (bt_df2['Sample Number'] == 'F0535')].index, inplace=True)\n",
    "\n",
    "# Delete the columns that are only in straggling results\n",
    "to_drop = ['Which risks are you aware of that exist when using this substance?', \n",
    "           'Please could you tell me exactly what you have had to drink today: [Wine]',\n",
    "           'The service user abandoned the intervention before completion.',\n",
    "           'Please could you tell me exactly what you have had to drink today: [Beer]',\n",
    "           'Please could you tell me exactly what you have had to drink today: [Spirits]',\n",
    "           'Please could you tell me exactly what you have had to drink today: [Alcopops]',\n",
    "           'Do you know any ways to reduce those risks?',\n",
    "           'Did you understand the disclaimer explaining the limitations that was read to you?',\n",
    "           'Which of the drugs are you planning to use later today?']\n",
    "bt_df2.drop(to_drop, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DONE COLUMNS at  07/05/18 18:23:09\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "# The Google Forms version of the Intervention Questionnaire\n",
    "#\n",
    "bt_interventions = '/opt/random/BTReport 2017 - Form responses 3.csv'\n",
    "date_cols = ['Timestamp']\n",
    "bt_df = pd.read_csv(bt_interventions, engine=\"python\", parse_dates=date_cols)\n",
    "\n",
    "# Add the 'straggling' results\n",
    "bt_df = pd.concat([bt_df, bt_df2], axis='rows', ignore_index=True)\n",
    "\n",
    "# Map Columns in Google Forms to SPSS\n",
    "d = {'Sample Number': 'SampleNumber',\n",
    " 'Timestamp' : 'Date & Time of intervention',\n",
    " 'Number of friends present with primary respondent': 'FriendsPresent',\n",
    " 'Gender of primary respondent': 'Gender',\n",
    " 'Ethnicity': 'Ethnicity',\n",
    " 'Age': 'Age',\n",
    " 'Have you had any alcohol to drink today?': 'ConsumedAlcohol',\n",
    " 'How much spirits have you had today?': 'Spirits',\n",
    " 'How much wine have you had today?': 'Wine',\n",
    " 'How many alcopops have you had today?': 'Alcopops',\n",
    " 'Are you currently taking any prescribed medication?': 'PrescribedDrugs',\n",
    " 'Are you currently taking any \"Over the Counter\" medication?': 'OverTheCounter',\n",
    " 'Do you have any concerns about how you are feeling at the moment?': 'ConcernsWithCurrentFeelings',\n",
    " 'You submitted a substance of concern for analysis, what do you believe it to be?': 'SubmittedSubstanceAs',\n",
    " 'Where did you obtain the sample?': 'Obtained',\n",
    " 'Very roughly, how often do you use this drug?': 'EverHadSubstance',\n",
    " 'When did you first use this batch?': 'WhereAndWhen',\n",
    " 'Have you or anyone you know ever had negative experiences taking this substance?': 'NegativeExperieces',\n",
    " 'How many times have you used this batch?': 'ConsumedFromBatchAlready',\n",
    " 'Do you have any concerns about using this sample from this batch or any other concerns about the result?': 'PriorConcerns',\n",
    " 'Have you ever accessed a treatment service for your alcohol or drug use?': 'AccessedSupportBefore',\n",
    " 'After our conversation today, would you like to have any further advice or support from a treatment service for your alcohol or drug use?': 'WantFurtherAdvice',\n",
    " # Need to check this one\n",
    " 'Have you ever taken any other drugs I didn\\'t mention?' : 'other_specify'\n",
    "}\n",
    "\n",
    "bt_df.rename(columns=d, inplace=True)\n",
    "\n",
    "# Delete all columns that are only in Google Forms\n",
    "to_drop = ['Volunteer Name', \n",
    "           'When was the last time you used this service?',\n",
    "           'What was your first sample number at this event? Did you take a photo or keep the ticket?',\n",
    "           'Which drugs have you used? [Non-prescribed opiods]',\n",
    "           'Have you had any other legal or illegal drugs today?',\n",
    "           'Are you planning to take any of these drugs later?']\n",
    "bt_df.drop(to_drop, axis=1, inplace=True)\n",
    "\n",
    "# Add Festival Column\n",
    "bt_df['Festival'] = 'BT2017'\n",
    "\n",
    "# Add As_expected Column - is all null in Liams\n",
    "bt_df['As_expected'] = np.nan\n",
    "\n",
    "# Fix SampleNumber\n",
    "bt_df['SampleNumber'] = bt_df['SampleNumber'].apply(fix_sample_number)\n",
    "\n",
    "# Fix the broken sample numbers - have already made sure they're not in the bt_interventions set \n",
    "# SNBAD = 'F00119'\n",
    "# SNNEW = 'F0119'\n",
    "# print(\"GF \", bt_df.loc[bt_df['SampleNumber'] == SNBAD, ['Gender', 'Age', 'SubmittedSubstanceAs', 'Date & Time of intervention']])\n",
    "# print(\"LAB \", lab_df.loc[(lab_df['SampleNumber'] == SNNEW) & (lab_df['Festival'] == 'BT2017'), ['Client gender', 'Client age', 'Bought as', 'Date & Time of return']]) \n",
    "# print(\"SPSS \", spss_df.loc[(spss_df['SampleNumber'] == SNNEW) & (spss_df['Festival'] == 'BT2017'), ['Gender', 'Date & Time of intervention', 'Age', 'SubmittedSubstanceAs']])\n",
    "\n",
    "# '000-04' - assume 'F0004' as in lab data and date/time of return/intervention are ~ 30 min\n",
    "bt_df.at[bt_df['SampleNumber'] == '000-04', 'SampleNumber'] = 'F0004'\n",
    "\n",
    "# '5f009' - 'F0059' as nothing in SPSS and date/time of return/intervention are ~ 5 min\n",
    "bt_df.at[bt_df['SampleNumber'] == '5f009', 'SampleNumber'] = 'F0059'\n",
    "\n",
    "# 'F00117' - 'F0117' as nothing in SPSS and date/time of return/intervention are ~ 30 min\n",
    "bt_df.at[bt_df['SampleNumber'] == 'F00117', 'SampleNumber'] = 'F0117'\n",
    "\n",
    "# 'F00119' - 'F0119' as nothing in SPSS and date/time of return/intervention are ~ 30 min and samples math\n",
    "bt_df.at[bt_df['SampleNumber'] == 'F00119', 'SampleNumber'] = 'F0119'\n",
    "\n",
    "# Sample 'F308470234987' is rubbish\n",
    "bt_df.drop(bt_df[bt_df['SampleNumber'] == 'F308470234987'].index, inplace=True)\n",
    "\n",
    "print(\"DONE COLUMNS at \", now())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DONE DRUGS at  07/05/18 18:23:10\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "# Attempt to disentangle the Form responses into a form they can be merged with Liam's data\n",
    "#\n",
    "PERIODS = ['ever', 'year', 'month', 'week', 'yesterday', 'today', 'tonight', 'today_tonight']\n",
    "\n",
    "def includes_frequency(cell, period):\n",
    "    \"Return boolean indicating if this cell contains frequencies >= period\"\n",
    "    if isinstance(cell, float) and np.isnan(cell):\n",
    "        return False # nan's is considered not having the value\n",
    "    \n",
    "    #periods = ['ever', 'year', 'month', 'week', 'yesterday', 'today', 'tonight']\n",
    "    form_responses = ['Had in my life', 'Had in last year', 'Had in last month', 'Had in last week', \n",
    "                      'Had yesterday', 'Had today', '(Probably) planning later']\n",
    "    \n",
    "    assert period in PERIODS, \"Invalid period: {0}\".format(period)\n",
    "    \n",
    "    values = [v.strip() for v in cell.split(',')]   \n",
    "    idx = PERIODS.index(period)\n",
    "    # check if any of the periods >= this have been checked\n",
    "    for i in range(idx, len(PERIODS) - 1):\n",
    "        if form_responses[i] in values:\n",
    "            return True\n",
    "    return False\n",
    "\n",
    "def get_value(column, period):\n",
    "    \"Return boolean Series indicating if this columns contains frequencies >= period\"\n",
    "    result = None\n",
    "    if period == 'today_tonight':\n",
    "        today = column.apply(includes_frequency, period='today')\n",
    "        tonight = column.apply(includes_frequency, period='tonight')\n",
    "        result = today | tonight\n",
    "    else:\n",
    "        result = column.apply(includes_frequency, period=period)\n",
    "    return result\n",
    "\n",
    "def add_columns(bt_df, label_map):\n",
    "    \"\"\"For all of the drugs in the label_map expand them out to match Liam's data and then delete the original column\n",
    "    \"\"\"\n",
    "    for gdrug in label_map.keys():\n",
    "        gcolumn_label = 'Which drugs have you used? [{}]'.format(gdrug)\n",
    "        gcolumn = bt_df[gcolumn_label]\n",
    "        sdrug = label_map[gdrug]\n",
    "        # Add columns for each period\n",
    "        for period in PERIODS:\n",
    "            column_name = '{}_{}'.format(sdrug, period)\n",
    "            bt_df[column_name] = get_value(gcolumn, period)\n",
    "\n",
    "        # Delete the original column\n",
    "        bt_df.drop([gcolumn_label], axis=1, inplace=True)\n",
    "\n",
    "def freq_summary(drugs, column_prefix):\n",
    "    \"\"\"Run boolean OR (any) for the give set of drugs and put result in column with \n",
    "    name {period}_{column_prefix}\"\"\"\n",
    "    for period in PERIODS:\n",
    "        labels = ['{}_{}'.format(drug, period) for drug in drugs]\n",
    "        clabel = '{}_{}'.format(column_prefix, period)\n",
    "        bt_df[clabel] = bt_df[labels].any(axis=1)\n",
    "     \n",
    "column_map_core = {'Cannabis' : 'cannabis',\n",
    "                   'Cocaine' : 'cocaine',\n",
    "                   'Ecstasy pills' : 'ecstasy',\n",
    "                   'Nitrous (NOS, laughing gas)' : 'nitrous_oxide',\n",
    "                   'MDMA crystal/powder' : 'mdma',\n",
    "                   'Ketamine' : 'ketamine',\n",
    "                   'Magic mushrooms' : 'mushrooms',\n",
    "                   'LSD' : 'lsd',\n",
    "                   'Mephedrone (M-Cat)' : 'Mephedrone',\n",
    "                   'Synthetic cannabinoids (\"Spice\")' : 'spice_legals',\n",
    "                   'A powder which I had no idea what it was' : 'unknown_powder',\n",
    "                  }\n",
    "\n",
    "# Add core drug columns\n",
    "add_columns(bt_df, column_map_core)\n",
    "\n",
    "# Extra columns that aren't present in Liam's data\n",
    "column_map_extra = {'2C-B' : '2cb',\n",
    "                    'Amphetamine (speed)' : 'speed',\n",
    "                    'Codeine' : 'coedine',\n",
    "                    'Valium or other benzodiazepines' : 'valium',\n",
    "                   }\n",
    "add_columns(bt_df, column_map_extra)\n",
    "\n",
    "\n",
    "# Now need to add calculated data for the other drug uses:\n",
    "# Legal (any_legal):    balloons, poppers, spice, other legal highs\n",
    "# core drugs (core):    cannabis, cocaine, ecstasy, mdma, ketamine, mephodrone, speed, heroin\n",
    "# polydrug ():    2 + illegal drugs\n",
    "# polysubstance:    2 + illegal drigs and usual alcohol frequency      \n",
    "\n",
    "all_drugs = list(column_map_core.values()) + list(column_map_extra.values())\n",
    "# ['cannabis', 'cocaine', 'ecstasy', 'nitrous_oxide', 'mdma', 'ketamine', 'mushrooms', 'lsd', 'Mephedrone', 'spice_legals', 'unknown_powder', '2cb', 'speed', 'coedine', 'valium']\n",
    "freq_summary(all_drugs, 'any')\n",
    "        \n",
    "legal_drugs = ['nitrous_oxide', 'spice_legals', 'coedine', 'valium']\n",
    "freq_summary(all_drugs, 'any_legal')\n",
    "\n",
    "core_drugs = ['cannabis', 'cocaine', 'ecstasy', 'mdma', 'ketamine', 'Mephedrone', 'speed']\n",
    "freq_summary(core_drugs, 'core')\n",
    "\n",
    "# Now poly drug use\n",
    "illegal_drugs = ['cannabis', 'cocaine', 'ecstasy', 'mdma', 'ketamine', 'mushrooms', 'lsd', 'Mephedrone', '2cb', 'speed']\n",
    "column_prefix = 'polydrug'\n",
    "for period in PERIODS:\n",
    "    labels = ['{}_{}'.format(drug, period) for drug in illegal_drugs]\n",
    "    clabel = '{}_{}'.format(column_prefix, period)\n",
    "    bt_df[clabel] = bt_df[labels].sum(axis=1) >= 2\n",
    "\n",
    "# Now poly substance use - NEED TO DECIDE ON ALCOHOL COLUMN\n",
    "# column_prefix = 'polysubstance'\n",
    "# for period in PERIODS:\n",
    "#     clabel = '{}_{}'.format(column_prefix, period)\n",
    "#     labels = ['polydrug_{}'.format(period), 'polydrug_{}'.format(period)]\n",
    "#     bt_df[clabel] = bt_df[labels].sum(axis=1) >= 2\n",
    "\n",
    "# Delete the drug columns that don't match Liam's data\n",
    "for drug in column_map_extra.values():\n",
    "    labels = [\"{}_{}\".format(drug, period) for period in PERIODS]\n",
    "    bt_df.drop(labels, axis=1, inplace=True)\n",
    "\n",
    "# Covert all booleans to Yes/No strings\n",
    "# 'polysubstance'\n",
    "prefixes = list(column_map_core.values()) + ['any', 'any_legal', 'core', 'polydrug' ]\n",
    "columns = []\n",
    "for prefix in prefixes:\n",
    "    for period in PERIODS:\n",
    "        columns.append(\"{}_{}\".format(prefix, period))\n",
    "for column in columns:\n",
    "    bt_df[column] = bt_df[column].map({True : 'Yes', False : 'No'})\n",
    "        \n",
    "#bt_df.to_csv('foo.csv')\n",
    "print(\"DONE DRUGS at \", now())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DONE DISPOSALS at  07/05/18 18:23:10\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "# Now disentangle the disposals columns\n",
    "#\n",
    "# Map of Google Forms responses to Liam's categories\n",
    "GMAP = {\n",
    "    'I will ask the Loop to safely dispose of the rest of the sample in my possession' : 'a',\n",
    "    'I will throw it away myself' : 'b',\n",
    "    'I will take a smaller amount of it' : 'c',\n",
    "    'I will take a larger amount of it' : 'd',\n",
    "    'I will take the same amount as usual' : 'e',\n",
    "    'I will take it over a longer time period' : 'f',\n",
    "    'I will be more careful about mixing it with other substances' : 'g',\n",
    "    'I will give it away instead of taking it myself' : 'h',\n",
    "    'I will sell it' : 'i',\n",
    "    'I will obtain more on site' : 'j',\n",
    "    'I will warn my friends and acquaintances' : 'k',\n",
    "    'I will warn others via social media and public websites' : 'l',\n",
    "    'I will tell my dealer' : 'm',\n",
    "    'I will return it to my dealer' : 'n',\n",
    "    'I will ask for a refund from my dealer' : 'o',\n",
    "    'I will go to another dealer' : 'p',\n",
    "    'I will keep it to take it elsewhere, after the festival' : 'q',\n",
    "    'I will do something else' : 'r'\n",
    "}\n",
    "CATEGORIES = list(sorted(GMAP.values()))\n",
    "\n",
    "def get_disposal(cell):\n",
    "    \"\"\"Return a list of booleans depending on which categories were seen for this cell.\"\"\"\n",
    "    result = [False] * len(CATEGORIES)\n",
    "    if isinstance(cell, float) and np.isnan(cell):\n",
    "        return result # nan's is considered not having the value\n",
    "    values = cell.split(',')\n",
    "    for v in values:\n",
    "        v = v.strip()\n",
    "        if v in GMAP.keys():\n",
    "            idx = CATEGORIES.index(GMAP[v])\n",
    "            result[idx] = True\n",
    "    return result\n",
    "\n",
    "label1 = 'After hearing today’s test results and harm reduction advice from The Loop, what do you plan to do with the sample?'\n",
    "label2 = 'What other actions will you do?'\n",
    "\n",
    "# Create two Series with the results of parsing the two columns\n",
    "series1 = bt_df[label1].apply(get_disposal).apply(pd.Series)\n",
    "series2 = bt_df[label2].apply(get_disposal).apply(pd.Series)\n",
    "\n",
    "# OR them to get the final column as a dataframe and name the columns accordingly\n",
    "df_tmp = series1 | series2\n",
    "\n",
    "# Rename columns to match\n",
    "df_tmp.columns = CATEGORIES\n",
    "\n",
    "# add to the bt_df\n",
    "bt_df = pd.concat([bt_df, df_tmp], axis=1)\n",
    "\n",
    "# CONVERT TRUE/FALSE TO Yes/No\n",
    "for column in CATEGORIES:\n",
    "    bt_df[column] = bt_df[column].map({True : 'Yes', False : 'No'})\n",
    "\n",
    "# Delete redundant columns\n",
    "bt_df.drop([label1, label2], axis=1, inplace=True)\n",
    "\n",
    "print(\"DONE DISPOSALS at \", now())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DONE FIX COLUMNS at  07/05/18 18:23:10\n"
     ]
    }
   ],
   "source": [
    "# Fix remaining non-matching columns\n",
    "\n",
    "# ConcernsWithCurrentFeelings' and any non yes/no answers to'WhatConcerns'\n",
    "label1 = 'ConcernsWithCurrentFeelings'\n",
    "label2 = 'WhatConcerns'\n",
    "bt_df[label1].fillna('No', inplace=True)\n",
    "mask = bt_df[label1] == 'No'\n",
    "bt_df[label2] = np.nan\n",
    "bt_df[label2].where(mask, bt_df[label1], inplace=True) # Copy values from label1 column over\n",
    "bt_df[label1].where(mask, 'Yes', inplace=True) # Set copied over values to 'Yes'\n",
    "\n",
    "label1 = 'PriorConcerns'\n",
    "label2 = 'Why'\n",
    "bt_df[label1].fillna('No', inplace=True)\n",
    "mask = bt_df[label1] == 'No'\n",
    "bt_df[label2] = np.nan\n",
    "bt_df[label2].where(mask, bt_df[label1], inplace=True) # Copy values from label1 column over\n",
    "bt_df[label1].where(mask, 'Yes', inplace=True) # Set copied over values to 'Yes'\n",
    "\n",
    "# Fix Ethnicity / Ethnicity_Other\n",
    "label1 = 'Ethnicity'\n",
    "label2 = 'Ethnicity_other'\n",
    "#Find where the values aren't the 5 core\n",
    "mask = bt_df[label1].apply(lambda x: x not in ['White', 'Black', 'Asian', 'Mixed Race', 'Other'])\n",
    "bt_df[label2] = np.nan\n",
    "bt_df[label2].where(~mask, bt_df[label1], inplace=True) # Copy values from label1 column over\n",
    "bt_df[label1].where(~mask, 'Other', inplace=True) # Set copied over values to 'Other'\n",
    "\n",
    "# Can't currently process data for other_ or polysubstance_ so just set to nan\n",
    "columns = []\n",
    "for prefix in ['other', 'polysubstance']:\n",
    "    for period in PERIODS:\n",
    "        columns.append(\"{}_{}\".format(prefix, period))\n",
    "# Add null columns\n",
    "bt_df = pd.concat([bt_df, pd.DataFrame(columns=columns)], axis='columns')\n",
    "\n",
    "# For now we just delete the beer and cider and set BeerCider to nan as we can't merge\n",
    "bt_df.drop(['How much cider have you had today?', 'How much beer have you had today?'], axis=1, inplace=True)\n",
    "bt_df['BeerCider'] = np.nan\n",
    "\n",
    "# Can't calculate UnitsConsumed\n",
    "bt_df['UnitsConsumed'] = np.nan\n",
    "\n",
    "# PriorConcerns': and any non yes/no answers to 'Why'\n",
    "print(\"DONE FIX COLUMNS at \", now())\n",
    "#bt_df.to_csv('foo.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make sure columns match between two dataframes\n",
    "btdfc = set(bt_df.columns.values)\n",
    "spssc = set(spss_df.columns.values)\n",
    "assert btdfc == spssc, \"Differing columns: %s\" % str(btdfc - spssc)\n",
    "\n",
    "# Rename bt_df columns to match spss_df\n",
    "bt_df = bt_df[spss_df.columns.values]\n",
    "\n",
    "# Join the two dataframes\n",
    "spss_df = pd.concat([spss_df, bt_df], axis='rows', ignore_index=True)\n",
    "\n",
    "# Sort\n",
    "spss_df.sort_values(['Festival','SampleNumber'], ascending=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Read in from LAB  2544\n",
      "07/05/18 18:23:21 Final LAB  2543\n"
     ]
    }
   ],
   "source": [
    "# Read in Guy's lab data\n",
    "labdata = '/opt/random/Loop 2017 Lab fixed data.xlsm'\n",
    "lab_df = pd.read_excel(labdata, sheet_name='Raw LabData')\n",
    "print(\"Read in from LAB \",len(lab_df))\n",
    "\n",
    "# Remame 'Event Name' and 'Sample Number' columns so they match\n",
    "lab_df.rename(columns={'Event  Name': 'Festival', 'Sample Number': 'SampleNumber'}, inplace=True)\n",
    "\n",
    "# Delete any rows where SampleNumber or Festival is NA as we can't do anything with it\n",
    "lab_df.dropna(subset=['SampleNumber', 'Festival'], inplace=True) # This just drops one case\n",
    "\n",
    "# Uppercase all sample numbers\n",
    "labels = ['SampleNumber']\n",
    "lab_df.loc[:, labels] = lab_df[labels].apply(lambda x: x.str.capitalize())\n",
    "\n",
    "# Change 'Matches Sold as?' to be yes/no\n",
    "column = 'Matches Sold as?'\n",
    "lab_df[column] = lab_df[column].map({1.0 : 'Yes', 0.0 : 'No'})\n",
    "\n",
    "# Delete redundant columns\n",
    "columns = ['SPSS UID']\n",
    "lab_df.drop(columns, axis=1, inplace=True)\n",
    "\n",
    "# Some sample numbers begin with W or F \n",
    "#print(len(lab_df[ ~ (lab_df['SampleNumber'].str.startswith('F') | lab_df['SampleNumber'].str.startswith('A')) ]))\n",
    "\n",
    "# BT2017-F0334 - Guy's email of the 4th May - spectrum matches 2C-B\n",
    "lab_df.at[lab_df['SampleNumber'] == 'F0334', 'Final Result'] = '2cb'\n",
    "print(now() + \" Final LAB \",len(lab_df)) # shows we are left with 2543 datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Festival BT2017: 873 entries 30 duplicates\n",
      "Festival KC2017: 67 entries 4 duplicates\n",
      "Festival SGP2017: 557 entries 36 duplicates\n",
      "70 duplicates\n",
      "70 entries were merged\n",
      "11\n",
      "26\n",
      "Deleted 37 entries based on duplication criteria\n"
     ]
    }
   ],
   "source": [
    "# This cell is for sorting out the duplicate entries.\n",
    "\n",
    "# This is clunky and can probably be done better - possibly with a MultiIndex?\n",
    "# Reindex twice to create an in index column that can be used to identify individual samples\n",
    "spss_df.reset_index(drop=True, inplace=True)\n",
    "spss_df.reset_index(drop=False, inplace=True)\n",
    "\n",
    "# Get all spss duplicated entries\n",
    "df = pd.DataFrame()\n",
    "for festival in ['BT2017', 'KC2017', 'SGP2017']:\n",
    "    sample_numbers = spss_df.loc[spss_df['Festival'] == festival, ['SampleNumber']]    \n",
    "    indexes = sample_numbers[sample_numbers.duplicated(keep=False)].index\n",
    "    print(\"Festival {}: {} entries {} duplicates\".format(festival, len(sample_numbers), len(indexes)))\n",
    "    df = pd.concat([df, spss_df.iloc[indexes,]], axis='rows')\n",
    "\n",
    "# Merge in the relevant lab data\n",
    "print(\"%d duplicates\" % len(df))\n",
    "dfa = pd.merge(df, lab_df, how='left', on=['Festival','SampleNumber'])\n",
    "print(\"%d entries were merged\" % len(dfa))\n",
    "\n",
    "# Get list of all columns\n",
    "columns = dfa.columns.values.tolist()\n",
    "\n",
    "# Remove the ones we want to look at together\n",
    "spss_cols = ['Age', 'Gender', 'Date & Time of intervention', 'SubmittedSubstanceAs']\n",
    "lab_cols = ['Client age', 'Client gender', 'Date & Time of return', 'Bought as', 'Client suspicion', 'Matches Sold as?', 'Final Result']\n",
    "for c in spss_cols + lab_cols + ['index', 'Festival', 'SampleNumber']:\n",
    "    columns.remove(c)\n",
    "# Put the ones we want together at the front\n",
    "columns =  ['index', 'Festival', 'SampleNumber'] + \\\n",
    "           ['Age', 'Client age',\n",
    "            'Gender', 'Client gender',\n",
    "            'Date & Time of intervention', 'Date & Time of return',\n",
    "            'SubmittedSubstanceAs', 'Bought as', 'Client suspicion', 'Matches Sold as?', 'Final Result'\n",
    "           ] + columns\n",
    "# Reorder the columns\n",
    "dfa = dfa[columns]\n",
    "dfa.sort_values(['Festival','SampleNumber'], ascending=True, inplace=True)\n",
    "dfa.to_csv('duplicates.csv')\n",
    "\n",
    "# Festival BT2017: 873 entries 30 duplicates\n",
    "# Festival KC2017: 67 entries 4 duplicates\n",
    "# Festival SGP2017: 557 entries 36 duplicates\n",
    "# The following entries appear to be genuine duplicates and can just be removed\n",
    "duplicated = [16, 278, 435, 679, 783, 820, 824, 881, 1016, 1349, 1365]\n",
    "# The below would need to be manually linked to lab data\n",
    "orphans = [3, 28, 87, 213, 249, 274, 353, 396, 896, 975, 980, 986, 990, \n",
    "           991, 1056, 1074, 1076, 1077, 1107, 1158, 1159, 1174, 1290, 1291, 1383, 1426]\n",
    "\n",
    "print(len(duplicated))\n",
    "print(len(orphans))\n",
    "l1 = len(spss_df)\n",
    "\n",
    "# Save orphans to see if we can find them later\n",
    "spss_orphan_df = spss_df[spss_df['index'].isin(orphans)].copy()\n",
    "\n",
    "# Change all orphan sample numbers to FXXX\n",
    "spss_orphan_df.loc[:,'SampleNumber'] = 'FXXX'\n",
    "\n",
    "# Drop the unwanted rows\n",
    "spss_df = spss_df[~spss_df['index'].isin(duplicated + orphans)]\n",
    "\n",
    "l2 = len(spss_df)\n",
    "print(\"Deleted {} entries based on duplication criteria\".format(l1 - l2))\n",
    "\n",
    "# Now delete the index column\n",
    "spss_df.drop(['index'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1456 entries were merged\n"
     ]
    }
   ],
   "source": [
    "# Merge the lab and spss dataframes where Festival and SampleNumber match\n",
    "df_final = pd.merge(spss_df, lab_df, how='inner', on=['Festival','SampleNumber'])\n",
    "print(\"%d entries were merged\" % len(df_final))\n",
    "\n",
    "# For checking which entries can't be merged - check for right_only\n",
    "#pd.merge(lab_df, spss_df, how='outer', indicator=True)\n",
    "\n",
    "# Append the orphan entries\n",
    "df_final = df_final.append(spss_orphan_df, ignore_index=True)\n",
    "\n",
    "# Rename columns\n",
    "d = { 'Bought as' : 'sold/acquired/advertised as' }\n",
    "df_final.rename(columns=d, inplace=True)\n",
    "\n",
    "# Sort first by Festival, then SampleNumber\n",
    "df_final.sort_values(['Festival', 'SampleNumber'], ascending=True, inplace=True)\n",
    "\n",
    "# Here we reorder columns that should be identical to:\n",
    "# 1. spot data errors\n",
    "# 2. remove duplicate columns once we're happy data is consistent\n",
    "prefix_cols = ['Festival', 'SampleNumber',\n",
    "             'Sample submission time', 'Date & Time of return', 'Date & Time of intervention', \n",
    "             'Client age', 'Age', 'Client gender', 'Gender',\n",
    "              'sold/acquired/advertised as', 'SubmittedSubstanceAs', 'Client suspicion', 'Final Result',\n",
    "              'As_expected','Matches Sold as?' ]\n",
    "\n",
    "# Get the list of columns excluding the ones in prefix_cols\n",
    "cols = [c for c in df_final.columns.tolist() if c not in prefix_cols]\n",
    "# Prepend prefix_cols to create the new list\n",
    "cols = prefix_cols + cols\n",
    "# Reorder columns\n",
    "df_final = df_final[cols]\n",
    "\n",
    "# capitalize all genders for consistency\n",
    "labels = ['Client gender', 'Gender']\n",
    "df_final.loc[:, labels] = df_final[labels].apply(lambda x: x.str.capitalize())\n",
    "\n",
    "# Set any MISSING to be nan\n",
    "df_final.loc[:, labels] = df_final.loc[:, labels].replace({'Missing':np.nan})\n",
    "\n",
    "# Fix case across all columns\n",
    "cprefix = ['cannabis', 'cocaine', 'ecstasy', 'mdma', 'ketamine', 'lsd', 'nitrous_oxide', \n",
    "         'mushrooms', 'Mephedrone', 'spice_legals','unknown_powder', 'other', 'any', 'any_legal', 'core', \n",
    "         'polydrug', 'polysubstance']\n",
    "columns = []\n",
    "for prefix in cprefix:\n",
    "    for period in PERIODS:\n",
    "        columns.append(\"{}_{}\".format(prefix, period))\n",
    "columns += list('abcdefghijklmnopqr')\n",
    "\n",
    "# Apply mapping\n",
    "df_final.loc[:, columns] = df_final[columns].apply(lambda x: x.str.capitalize())\n",
    "\n",
    "# Create an initial UID column at beginning\n",
    "uid = df_final['Festival'] + \"-\" + df_final['SampleNumber'] + '-' + df_final.index.to_series().astype(str)\n",
    "df_final.insert(loc=0, column='UID', value=uid)\n",
    "\n",
    "# for c in df_final.columns.values:\n",
    "#     print(\"COLUMN %s: %s\" %(c, df_final[c].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This cell is for canonicalising the drug names\n",
    "\n",
    "# # Need to check drug names match across all datasets\n",
    "# def unique(column):\n",
    "#     return sorted(set([a.strip().lower() for a in column.astype(str).unique()]))\n",
    "\n",
    "# bought_as = unique(lab_df['Bought as'])\n",
    "# client_suspicion = unique(lab_df['Client suspicion'])\n",
    "# final_result = unique(lab_df['Final Result'])\n",
    "# submitted_as = unique(spss_df['SubmittedSubstanceAs'])\n",
    "# other_specify = unique(spss_df['other_specify'])\n",
    "\n",
    "# print(\"Bought as:\", bought_as)\n",
    "# print(\"Client suspicion:\", client_suspicion)\n",
    "# print(\"Final Result:\", final_result)\n",
    "# print(\"SubmittedSubstanceAs:\", submitted_as)\n",
    "# print(\"other_specify:\", other_specify)\n",
    "\n",
    "# x = bought_as + client_suspicion + final_result + submitted_as + other_specify\n",
    "# x = sorted(set(x))\n",
    "# print(\"X: \",x)\n",
    "\n",
    "AMPHETAMINE = 'amphetamine'\n",
    "BENZODIAZEPINE = 'benzodiazepine'\n",
    "COCAINE = 'cocaine'\n",
    "FOUND = 'found'\n",
    "KETAMINE = 'ketamine'\n",
    "LSD = 'lsd'\n",
    "MEPHEDRONE = 'mephedrone'\n",
    "MDMA = 'mdma'\n",
    "NETHYLPENTYLONE = 'n-ethylpentylone'\n",
    "PSYCHEDELIC = 'psychedelic'\n",
    "TWOCB = '2cb'\n",
    "UNKNOWN = 'unknown'\n",
    "\n",
    "drugs_map = { \n",
    "    AMPHETAMINE : ['speed', 'Speed', 'base/speed', 'adderall'],\n",
    "    BENZODIAZEPINE : ['chinese valium', ],\n",
    "    COCAINE : ['coke', 'cut cocaine'],\n",
    "    FOUND : ['unknow found'],\n",
    "    KETAMINE : ['?ket', '/ketamie', 'maybe ketamine?', 'katamine', 'vanila ketamine', \n",
    "                'vetamine', 'not mdma. ketamine?', 'ketamoine'],\n",
    "    LSD : ['acid', 'liquid lsd'],\n",
    "    MEPHEDRONE : ['meow meow', 'mcat'],\n",
    "    MDMA : ['mdxx', 'mda/mdea/mdma', 'mdma,', 'mandy', 'probaby mdma', 'mdma?', 'mdma with caffeine',\n",
    "           '3/4 of pill green shooting star', 'ecstacy', 'ecstacy pill', 'ecstasy',\n",
    "            'ecstasy pill', 'esctacy pill sample', 'estacy pill', 'pill'],\n",
    "    #NETHYLPENTYLONE : ['n-ethylpentylone'],\n",
    "    PSYCHEDELIC : [ '4-aco dmt', '4-aco-dmt', '4aco', '4acodmt', '5meomipit', 'dmt_2cb', 'dmt', 'ayahuasca'],\n",
    "    TWOCB : ['2 cb', '2c-b'],\n",
    "    UNKNOWN : ['unknown pill', 'unsure', 'unsure - maybe dmt', 'unsure of content', \n",
    "               'no effect', 'no idea', 'data missing'],   \n",
    "    }\n",
    "\n",
    "# Here we overwrite the values - if necessary we could create separate columns\n",
    "# Create dict for replace function is form {column : {value_to_replace, replacement_value}}\n",
    "replace_d = {}\n",
    "drug_columns = ['sold/acquired/advertised as', 'Client suspicion', 'Final Result', 'SubmittedSubstanceAs', 'other_specify']\n",
    "\n",
    "# Firstly convert all columns to lower case and remove any spaces\n",
    "def clean(value):\n",
    "    if type(value) is str:\n",
    "        value = value.strip().lower()\n",
    "    return value\n",
    "\n",
    "for column in drug_columns:\n",
    "    df_final[column] = df_final[column].map(clean, na_action='ignore')\n",
    "\n",
    "for column in drug_columns:\n",
    "    replace_d[column] = {}\n",
    "    for drug, names in drugs_map.items():\n",
    "        for name in names:\n",
    "            replace_d[column][name] = drug\n",
    "\n",
    "# Replace values\n",
    "df_final.replace(replace_d, inplace=True)\n",
    "            \n",
    "# NO_ANALYSIS as is treated separtely as only applies to Final Result - also can't include with other dict\n",
    "# or the replacement values and keys overlap\n",
    "NO_ANALYSIS = 'analysis_inconclusive'\n",
    "no_analysis = ['compound not in library', 'inconclusive', 'insufficient quantity for testing', \n",
    "               'insufficient sample', 'insufficient sample', 'lost', 'no active component identified', \n",
    "               'no match', 'no match', 'none', 'nothing detected', 'result missing', 'unable to test', 'unknown']\n",
    "\n",
    "# Fix 'Final Result' for NO_ANALYSIS\n",
    "column = 'Final Result'\n",
    "replace_d = {column: {}}\n",
    "for name in no_analysis:\n",
    "    replace_d[column][name] = NO_ANALYSIS\n",
    "\n",
    "# Replace values\n",
    "df_final.replace(replace_d, inplace=True)\n",
    "\n",
    "# Additional grouping requested by Fiona\n",
    "column = 'sold/acquired/advertised as'\n",
    "replace_d = {column: {'found' : 'unknown',\n",
    "                      \"don't know\" : 'unknown',\n",
    "                      'not sure' : 'unknown',\n",
    "                     }}\n",
    "df_final.replace(replace_d, inplace=True)\n",
    "\n",
    "# Calculate where they do/don't match\n",
    "df_final['As_expected'] = (df_final['Final Result'] == df_final['sold/acquired/advertised as']).map({True : 'Yes', False : 'No'})\n",
    "\n",
    "# BT2017 F0641 was submitted as 'ketamine or mdma' so we need to manually set the As_expected result\n",
    "df_final.at[(df_final['SampleNumber'] == 'F0641') & (df_final['Festival'] == 'BT2017'), 'As_expected'] = 'Yes'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# # See which non-na ages don't match\n",
    "# # 540 entries have valid ages\n",
    "# print(len(dft))\n",
    "# df = dft[pd.notnull(dft['Client age']) & pd.notnull(dft['Age'])]\n",
    "# print(len(df))\n",
    "# df = df[df['Client age'] != df['Age']]\n",
    "# # 127 don't match\n",
    "# print(len(df))\n",
    "# # df.to_csv('foo.csv')\n",
    "\n",
    "# Cross tab 'Client gender' and 'Gender\n",
    "#print(dft['Client gender'].unique())\n",
    "#print(dft['Gender'].unique())\n",
    "\n",
    "# # Look where they don't match\n",
    "# df = dft[pd.notnull(dft['Client gender']) & pd.notnull(dft['Gender'])]\n",
    "# df = df[df['Client gender'] != df['Gender']]\n",
    "# # 127 don't match\n",
    "# print(len(df))\n",
    "# df.to_csv('foo.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "07/05/18 18:23:33 Wrote  merged.xlsx\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "# Finally, dump everything to excel\n",
    "#\n",
    "filename = 'merged.xlsx'\n",
    "writer = pd.ExcelWriter(filename)\n",
    "df_final.to_excel(writer, 'MergedData', index=False)\n",
    "writer.save()\n",
    "print(now() + \" Wrote \",filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Get a list of all the lab data samples that can't be merged - use merge so the method is same as before\n",
    "df_check = spss_df.merge(lab_df, how='outer', right_on=['Festival','SampleNumber'], left_on=['Festival','SampleNumber'], indicator=True)\n",
    "columns = df_check.loc[df_check['_merge'] == 'right_only', ['Festival', 'SampleNumber']]\n",
    "# Remove any that don't start with F\n",
    "columns = columns[columns['SampleNumber'].map(lambda x: x.startswith('F'))]\n",
    "\n",
    "# Create combined coloumns so we can select on them - sure there is better way to do this\n",
    "columns['foo'] =  columns['Festival'] + columns['SampleNumber']\n",
    "lab_df['foo'] = lab_df['Festival'] + lab_df['SampleNumber']\n",
    "\n",
    "lab_df1 = lab_df[lab_df['foo'].isin(columns['foo'])]\n",
    "\n",
    "#print(spss_orphans['Date & Time of intervention'])\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}