sql-vs-rd-pattern-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b10ec5b6-efb7-4010-bede-f4f5fe9b1ce1",
   "metadata": {},
   "source": [
    "# Final analysis — SQL vs. Visual Diagrams for Matching Relational Query Patterns — June 2023 Study\n",
    "\n",
    "We are preregistering this study based on the [OSF Google Docs template](https://docs.google.com/document/d/1DaNmJEtBy04bq1l5OxS4JAscdZEkUGATURWwnBKLYxk/edit), which is one of [several preregistration templates](https://osf.io/zab38/wiki/home/?view) that [OSF](https://osf.io/) provides. Our experimental setup is inspired by [Leventidis et al. (2020)](https://doi.org/10.1145/3318464.3389767).\n",
    "\n",
    "$\\color{red}{\\text{\\textbf{See our updates post-registration}}}$ below in the [Other](#Other) section."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3b92256-751a-4d54-9df8-8d0da7a961c2",
   "metadata": {},
   "source": [
    "## Study Information\n",
    "\n",
    "* **Title:**\n",
    "    The effect of SQL vs. Visual Diagrams on time and correctness matching relational query patterns\n",
    "\n",
    "* **Authors:** \n",
    "    *Anonymous for peer review. The online form on [osf.io](https://osf.io/) will list authors upon publication or embargo expiration.*\n",
    "\n",
    "* **Description:** \n",
    "    Pilot testing has indicated that visual diagrams (RD) improve participant speed at correctly identifying relational query patterns, contrasting with formatted SQL.\n",
    "    We will measure participant time and the proportion of correct answers for two conditions (RD and SQL) and 4 relational query patterns across 32 questions.\n",
    "\n",
    "* **Hypotheses:** We are testing for a total of `3` hypotheses: \n",
    "  * ***Time:***\n",
    "    * Let $\\theta_X$ denote the median time per question in seconds for a given condition $X$ per participant. We hypothesize that (1) $\\theta_{RD}$ / $\\theta_{SQL} < 1$, thus participants are relatively faster using `RD` compared to `SQL`.\n",
    "    * $\\color{red}{\\text{Corrected to match code \\& original intent:}}$ Let $\\theta_{X1}$ and $\\theta_{X2}$ denote the median time per question in seconds for a given condition $X$ per participant in the first $X1$ or second half $X2$. We hypothesize that (2) both $\\theta_{RD2} / \\theta_{RD1}$ and $\\theta_{SQL2} / \\theta_{SQL1} < 1$, thus participants are relatively faster in the 2nd half than the 1st.\n",
    "  * ***Correctness:*** Let $\\delta_X$ denote the mean proportion of correct responses for a given condition $X$. We hypothesize that (3) $\\delta_{RD}$ $\\simeq$ $\\delta_{SQL}$, i.e., participants make a comparable number of correct responses using `RD` or `SQL`.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "525cf552-1d10-4073-a2d9-2388f94aea72",
   "metadata": {},
   "source": [
    "## Design Plan\n",
    "\n",
    "* **Study type:**\n",
    "    Experiment.\n",
    "\n",
    "* **Blinding:**\n",
    "    No blinding is involved in this study.\n",
    "\n",
    "* **Study design:** \n",
    "    We have a within-subjects design, i.e., each participant sees questions in both of our modes: `RD` and `SQL`.\n",
    "    Each participant will see a total of `32` questions: `2` modes $\\times$ `4` patterns $\\times$ `2` instances in each half $\\times$ `2` halves.\n",
    "    I.e., they will see each pattern-mode combination `2` times per half.\n",
    "    For each question, the participant will be given a SQL query presented using one of the modes. They must choose the most appropriate of `4` natural-language interpretations of the query, each corresponding to one of our `4` patterns.\n",
    "    Their sequence number determines the mode presented to each participant for a given question — described under the randomization bullet.\n",
    "    The stimuli for each mode is:\n",
    "\n",
    "  * `SQL` — A conventional SQL representation with appropriate indentation.\n",
    "      The SQL text is indented and SQL keywords are color-coded appropriately.\n",
    "  * `RD` — A visual diagram we created of the query.\n",
    "\n",
    "    See the included `supplement/tutorial.pdf` file for a sample of the stimuli and how to read them, one page extracted here:\n",
    "\n",
    "![Example of the four patterns and two modes.](supplement/patterns.png)\n",
    "\n",
    "* **Randomization:** \n",
    "    To reduce ordering effects caused by which mode is presented first, we assign participants as they arrive to alternately start with `SQL` (sequence number `0`) or `RD` (sequence number `1`).\n",
    "    We then alternate the modes the presenter sees with each question.\n",
    "    I.e., `[SQL, RD, SQL, RD...]`.\n",
    "    We randomize the order that patterns are presented in each half separately, ensuring that each combination appears the same number of times and that both halves have the same number of each pattern-mode combination."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbfb9ee8-3d98-441d-8594-fc6118b5f54c",
   "metadata": {},
   "source": [
    "## Sampling Plan\n",
    "\n",
    "* **Existing data:**\n",
    "    Registration before creating data: As of the date of submission of this research plan for preregistration, the data have not yet been collected, created, or realized.\n",
    "    Only pilot data has been collected.\n",
    "\n",
    "* **Explanation of existing data:**\n",
    "    N/A.\n",
    "  \n",
    "\n",
    "* **Data collection procedures:** \n",
    "  * ***Population:***\n",
    "    Participants will be recruited through [Amazon Mechanical Turk (AMT)](https://www.mturk.com/), a popular crowdsourcing website used for a variety of tasks, including academic studies.\n",
    "  * ***Recruitment efforts:*** A Human Intelligence Task (HIT) will be posted on AMT. We may repeatedly re-post the HIT as recruitment slows over time.\n",
    "  * ***Inclusion criteria:*** Participants could accept the AMT HIT if they are all of the following:\n",
    "    1. Residing in the USA and, thus, we expect English proficiency.\n",
    "    2. Adults (over 18 years old).\n",
    "    3. Experienced SQL users, self-determined with the prompt: \"Workers should be familiar with SQL at the level of an advanced undergraduate database class, in particular with nested SQL queries.\"\n",
    "    4. Have submitted over `500` approved HITs on AMT.\n",
    "    5. Have more than `97%` approved HIT assignments on AMT.\n",
    "  * ***Exclusion criteria:*** None. Pilot study participants were collected from our institution so did not need to be excluded on AMT.\n",
    "  * ***Timeline:*** Data will be collected from when we start until our **Stopping rule** below is met.\n",
    "  * ***Payment:***\n",
    "    * *AMT Rejection criteria:* A HIT will be accepted and the participant paid only if they correctly answered $\\ge$ `16/32` questions within `50` minutes. Otherwise, the HIT will be rejected.\n",
    "    * *Base pay:* `$6.00` USD.\n",
    "    * *Correctness bonus:* For every correctly answered question after the `16th` the participant receive a bonus payment of `$0.20` USD for a total pay of `$9.20` USD.\n",
    "    * *Time bonus:* Based on total test completion time, the participant will receive a percentage bonus on total pay (including the correctness bonus). Completion within `11` minutes awards a `5%` bonus for a maximum pay of `$9.66`. Each minute faster gets you an additional `5%` bonus up to `40%` for completing within `4` minutes, with a maximum pay of `$12.88`.  \n",
    "\n",
    "* **Sample size:** \n",
    "    Our target sample size is `50` participants.\n",
    "\n",
    "* **Sample size rationale:**\n",
    "    As all 13 pilot participants were faster with `RD` than `SQL`, we did not use a power analysis to choose the sample size. Instead, `50` was chosen as a meaningfully large and round number that is still a multiple of 2 to ensure that we have an equal number of participants among the sequences (see **Randomization**, above)\n",
    "\n",
    "* **Stopping rule:**\n",
    "    We will terminate data collection once our number of complete HITs has reached our maximum target sample size.\n",
    "    Given the strict ***Inclusion criteria*** in our study, it is possible that we won't be able to hit our target sample size.\n",
    "    In that case, we shall restrict our analysis to the data we can collect before paper submission.\n",
    "    We will continue collecting data until we reach the maximum target sample size or the camera-ready paper submission deadline."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbd00368-c86e-4442-9dd3-7a7aa6646c6b",
   "metadata": {},
   "source": [
    "## Variables\n",
    "\n",
    "* **Manipulated Variables:**\n",
    "\n",
    "  * *Mode:* `[SQL, RD]`. See [Study Information](#Study-Information).\n",
    "\n",
    "\n",
    "* **Measured Variables:** \n",
    "\n",
    "  * For each participant and each question they answer, we record:\n",
    "    * ***Time (quantitative):*** The time they take to answer the question.\n",
    "    * ***Pattern (categorical/integer):*** The pattern they were provided in one of the modes for that question.\n",
    "    * ***Choice (integer):*** Their selected pattern from the `4`-option multiple choice question.\n",
    "    * ***Correct (boolean/integer):*** Whether their answer for the `4`-option multiple choice question was correct.\n",
    "\n",
    "  * For each participant, we also record:  \n",
    "    * ***Sequence (categorical):*** The sequence the participant was randomly assigned to (see [Design Plan](#Design-Plan)).\n",
    "    * ***Free-text feedback (string):***  The participant's optional answers to a feedback prompt.\n",
    "\n",
    "\n",
    "* **Indices:** From our collected study data, we will calculate:\n",
    "  * ***Indices for time***\n",
    "     \n",
    "    * **`Median time per mode per participant`:**\n",
    "    This is calculated by taking the median of the `16` ***Time*** records for each mode for each participant.\n",
    "    Using that information, we can calculate the median time across all participants.\n",
    "\n",
    "    * **`Ratio of median time of RD relative to SQL per participant:`**\n",
    "    Using the `Median time per mode per participant`, we will calculate `RD/SQL`.\n",
    "\n",
    "    * **`Quartiles and CIs of median time per mode across all participants:`**\n",
    "    Using the `Median time per mode per participant`, we will calculate the 1st, 2nd (median), 3rd quartiles.\n",
    "\n",
    "    * **`Quartiles and CIs of median time of RD relative to SQL across all participants:`**\n",
    "    Using the `Ratio of median time of RD relative to SQL per participant`, we will calculate the 1st, 2nd (median), 3rd quartiles as well as 95% Confidence Intervals using Bias Corrected and Accelerated (BCa) bootstrapping ([Efron (1987)](https://doi.org/10.2307%2F2289144)).\n",
    "\n",
    "    * **`Per-half indices:`**\n",
    "    We will compute both the `Quartiles and CIs of median time...` indices for each half.\n",
    "        \n",
    "  * ***Indices for correctness***\n",
    "\n",
    "    * **`Proportion of correct responses per mode per participant`:**\n",
    "    For each participant, calculate the proportion of correct responses per mode as: `correct responses / total questions per mode`.\n",
    "    \n",
    "    * **`Mean and CIs of proportion of correct responses per mode across all participants`:**\n",
    "    The mean proportion of correct responses across all participants is calculated by taking the arithmetic mean of all the `Proportion of correct responses per mode per participant` values for a given mode across all participants.\n",
    "    We will calculate 95% Confidence Intervals using Bias Corrected and Accelerated (BCa) bootstrapping."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e9d2853-0e21-469a-96a4-316c25dd9e42",
   "metadata": {},
   "source": [
    "## Analysis Plan\n",
    "\n",
    "* **Statistical Models:**\n",
    "\n",
    "  * ***Distribution testing:***\n",
    "    We will examine the distributions of our data visually, including for each mode, to ensure there are no problematic distributions.\n",
    "  * ***Hypothesis Testing:***\n",
    "    We will visually examine the 95% BCa bootstrapped confidence intervals for each mean and median.\n",
    "\n",
    "* **Transformations:** N/A.\n",
    "\n",
    "* **Inference criteria:**\n",
    "    We will interpret the results using Interval Estimation rather than dichotomous $p$-value cutoffs (e.g., we will not use *p*<.05 to determine statistical significance).\n",
    "    See [Dragicevik (2016)](http://dx.doi.org/10.1007/978-3-319-26633-6_13) and [Besançon & Dragicevic (2019)](http://dx.doi.org/10.1145/3290607.3310432) for a discussion of using estimation for these types of analyses.\n",
    "    \n",
    "* **Data exclusion:**\n",
    "    To perform a concrete analysis of our data, we would like to minimize the set of outlier points as they will negatively affect the quality of our statistical analysis and introduce unwanted/non-existent bias.\n",
    "    After collecting our data, we will examine the time distribution of each worker. In particular, we will examine how long each worker took to answer the question on a per-question basis. We expect two types of outlier points in our experiments. \n",
    "  1. **Speeders:** Workers that answered a question much faster than the vast majority of participants (usually in the order of a few seconds, i.e., workers tried to rush answering each question without thinking).\n",
    "      The range could vary, but a rough indication of a speeder would be if their time per question is 2-3 standard deviations lower than the mean time per question.\n",
    "  2. **Unusually delayed answers:** This refers to workers who took unusually long to answer a question.\n",
    "      This is most likely attributed to some distraction that made the worker not focus on our question while the timer was running (i.e., a phone call, text message, bathroom break, etc.).\n",
    "      As an online test, we can't know exactly what was the cause of it, but usually, we can identify such data points by noticing their much larger value in time.\n",
    "      Since we capture a time distribution, it is expected to be left-skewed, and thus a rough measure of an *unusually delayed answer* would be about $\\geq 3$ times the mean time per question.\n",
    "  3. **Cheaters:** Previous studies have indicated that workers can leak answers to enable other workers to answer all the questions correctly and quickly. We have used technical measures to limit this possibility and give each user different sets of stimuli. However, if we identify cheaters through log analysis, we will exclude them.\n",
    "  4. **Median:** To minimize the effect of outliers for all the above reasons and to provide a statistically valid unbiased estimator for the ratio of times, we use the median instead of the mean aggregation for time analysis.\n",
    "\n",
    "\n",
    "* **Missing data:**\n",
    "    We will unlikely have missing data because for a participant to submit their results, they must answer all the questions.\n",
    "    However, if we have missing data points from an individual, we will remove the individual completely from our analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebff1220-4844-4180-ab77-1b7c128a6f88",
   "metadata": {},
   "source": [
    "## Other\n",
    "\n",
    "* **Discrepancies between preregistration prose and analysis code:**\n",
    "    The intent of our study design is explained in this section.\n",
    "    In case of any discrepancy between the analysis code below and this section, what is written in this section takes precedence for the preregistration.\n",
    "\n",
    "* $\\color{red}{\\text{\\textbf{Updates post-registration:}}}$\n",
    "\n",
    "    1. Our second hypothesis was incorrectly stated above, but our intent was clear from the below text and code. We have updated it. This result should be evaluated accordingly as it is a deviation from our prergistration.\n",
    "    2. Minor error in correctness score calculation fixed.\n",
    "    3. Anonymization of MTurk worker IDs is removed from this code and now done outside this worksheet to avoid accidental release of worker IDs.\n",
    "    4. Time spent on tutorials was erroneously collected, and should not be used. We remove this column in the anonymization code.\n",
    "    5. In total, 177 participants began the study, but many quit before finishing the tutorial or after a few questions. Only 120 participants submitted the HIT. Of those, only 58 reached the 50% correctness threshold for HIT acceptance. We only select the first 50 of those 58 that were submitted to be in accordance with our preregistration.\n",
    "    6. Added visual emphasis for ratio = 1 in figure 1b.\n",
    "    7. Added user feedback printing at the end of the worksheet.\n",
    "    8. Added \"variants\" for further exploratory analysis based on elevated correctness thresholds.\n",
    "    9. Added per-pattern exploratory analysis.\n",
    "    10. Simplified the code for figure 3.\n",
    "    11. Added additional figure showing difference for question 4.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a58c448a",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "# Scripts to analyze the study data\n",
    "\n",
    "(Q1) TIMING PER PARTICIPANT (SQL vs RD)\n",
    "1. Per participant, calculate the median time in seconds spent on SQL and RD (32/2=16 per mode and participant, irrespective of correctness)\n",
    "2. Q1a: show violin plot figure with median times per user compared via gray lines\n",
    "3. Calculate their ratio per user (also gives fraction of users who are faster with one or the other)\n",
    "4. Calculate the median of those fractions and the 95% CI\n",
    "5. Q1b: show violin plot figure with fractions, and also 95% CI\n",
    "\n",
    "(Q2) TIMING PER PARTICIPANT (SQL vs RD / 1st vs. 2nd half)\n",
    "1. Per participant, calculate the median time over all questions answered in 1st half in RD (32/2/2=8) and SQL, and in 2nd half.\n",
    "2. Q2: show repeated measure violin plot figure, showing improvements over time, of 2 halfs\n",
    "3. Calculate the relative ratio for timing 2nd/1st for RD, and SQL including 95% CI\n",
    "\n",
    "(Q3) TIMING PATTERNS ACROSS PARTICIPANTS\n",
    "1. calculate the median time per pattern (4) across the two modes (2). Thus 8 values.\n",
    "2. show repeated measure violin plot figure\n",
    "\n",
    "(Q4) CORRECTNESS (SQL vs RD)\n",
    "1. take mean correct over all questions and all users answered in SQL (32/2*13), or in RD (2 values)\n",
    "2. calculate 95% CI for each, and sampled p-value (perhaps with difference?)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11486b0f",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "## Load packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90163bd4-f66f-4d4d-a525-a8420532f03e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "sns.set(style=\"whitegrid\",font_scale=2)\n",
    "import matplotlib.pyplot as plt\n",
    "from scipy.stats import bootstrap as scipybootstrap\n",
    "from IPython.display import display\n",
    "import matplotlib.ticker as mtick           # allows change to percentage\n",
    "\n",
    "# Tell matplotlib to export svg text as text not paths\n",
    "plt.rcParams['svg.fonttype'] = 'none'\n",
    "plt.rcParams['axes.axisbelow'] = True   # draw axes and grids behind everything else\n",
    "\n",
    "# Set Jupyter and Pandas to show 3 decimal places, does not work for lists of numbers\n",
    "%precision 3\n",
    "pd.options.display.float_format = '{:,.3f}'.format\n",
    "np.set_printoptions(precision=3)\n",
    "# np.set_printoptions(formatter={'float': lambda x: \"{0:0.3f}\".format(x)})            # TODO: does not work for lists\n",
    "def print(*args):\n",
    "    __builtins__.print(*(\"%.3f\" % a if isinstance(a, float) else a\n",
    "                         for a in args))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60109a40-4d08-4bd6-90ed-a6788cfe728a",
   "metadata": {},
   "source": [
    "## Global Variables Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d1d96cd-4678-4d54-a8eb-d84cf3e14247",
   "metadata": {},
   "outputs": [],
   "source": [
    "# A set of constant global variables used throughout the notebook\n",
    "num_questions = 32\n",
    "modes = ['SQL', 'RD']\n",
    "mode_to_name = {0: 'SQL', 1: 'RD'}\n",
    "\n",
    "# anonymizeddata = 'data/users-table-pilot.csv'                      # pilot\n",
    "anonymizeddata = 'data/users-table-anonymized.csv'\n",
    "transformeddata = 'data/transformed_data.csv'   # file with appropriately transformed data ready for analysis\n",
    "\n",
    "BOOTSTRAPCONFIDENCE = 0.95      # confidence level used for bootstrap\n",
    "BOOTSTRAPMETHOD = 'BCa'         # method used for bootstrap, appears to be better than the textbook version for mean (but not for median), also available as 'percentage'\n",
    "BOOTSTRAPSAMPLES = 10000        # number of resamples\n",
    "VARIANT = 1                     # variant 1: all participants, variant 2: only for correctness = 1.0, variant 3: only for correctness = 0.9, variant 4: only for correctness >= 0.66"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "220858af",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "## Define subfolder where figures are stored\n",
    "\n",
    "By default, figures will not be saved. If you want to save figures, set savefig to `True`.\n",
    "Learned from: https://github.com/jorvlan/open-visualizations/blob/master/Python/tutorial_2/repeated_measures_python_2.ipynb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80874ad0",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "savefig = True\n",
    "if savefig:\n",
    "    import os\n",
    "    from os.path import isdir\n",
    "    cwd = os.getcwd()   # Get current working directory, but you can specify your own directory of course.\n",
    "    if  os.path.exists(cwd + \"/figs\"):\n",
    "        print(\"Directory already exists\")\n",
    "        fig_dir = cwd + \"/figs\"     # Assign the existing directory to a variable\n",
    "    elif not os.path.exists(cwd + \"/figs\"):\n",
    "        print(\"Directory does not exist and will be created ......\")\n",
    "        os.makedirs(cwd + \"/figs\")\n",
    "        if isdir(cwd + \"/figs\"):\n",
    "            print('Directory was created succesfully')\n",
    "        fig_dir = cwd + \"/figs\"     # Assign the created directory to a variable\n",
    "    else:\n",
    "        print(\"Something went wrong\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce8b86e6-d818-4a15-a5f4-9ca691e84fba",
   "metadata": {},
   "source": [
    "## Loading full data, transforming it, and saving the transformed version\n",
    "\n",
    "Loading the full data, transforming it to make available for later analysis, and saving it\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "385495ea-f2c9-4273-9cd9-4e2378417641",
   "metadata": {},
   "outputs": [],
   "source": [
    "# --- Load anonymized full study data\n",
    "df = pd.read_csv(anonymizeddata)\n",
    "\n",
    "# --- Filter on 'current_section=RESULTS'\n",
    "dfresults = df.loc[(df.current_section == \"RESULTS\")].copy()              # (7/6/2023: added filter to only focus on RESULTS)\n",
    "\n",
    "# --- Turn string to array\n",
    "from ast import literal_eval            # to turn string to array\n",
    "dfresults['pattern_order']= dfresults['pattern_order'].apply(literal_eval)\n",
    "\n",
    "# display(dfresults)\n",
    "# The \"current page\" is the section of the study the workers are doing to save their state & prevent them cheating\n",
    "\n",
    "# --- The following code block transforms the data frame to have one question per row. That simplifies the later analysis.\n",
    "# reshape dfresults (melt, pivot) to bring multiple question times (e.g. 'q7_time') per row into separate rows\n",
    "# https://towardsdatascience.com/wide-to-long-data-how-and-when-to-use-pandas-melt-stack-and-wide-to-long-7c1e0f462a98\n",
    "df2 = dfresults.melt(id_vars=['worker_id', 'sequence_num', 'pattern_order',\n",
    "                       'q1', 'q2','q3', 'q4','q5', 'q6','q7', 'q8', 'q9', 'q10',\n",
    "                       'q11', 'q12','q13', 'q14','q15', 'q16','q17', 'q18', 'q19', 'q20',\n",
    "                       'q21', 'q22','q23', 'q24','q25', 'q26','q27', 'q28', 'q29', 'q30',\n",
    "                       'q31', 'q32'], value_vars=['q1_time', 'q2_time', 'q3_time', 'q4_time','q5_time', 'q6_time', 'q7_time', 'q8_time', 'q9_time', 'q10_time',\n",
    "                                                  'q11_time', 'q12_time', 'q13_time', 'q14_time', 'q15_time', 'q16_time', 'q17_time', 'q18_time', 'q19_time', 'q20_time',\n",
    "                                                  'q21_time', 'q22_time', 'q23_time', 'q24_time', 'q25_time', 'q26_time', 'q27_time', 'q28_time', 'q29_time', 'q30_time',\n",
    "                                                  'q31_time', 'q32_time'], var_name='question', value_name='time')\n",
    "\n",
    "# replace time in msec with sec in column 'time'\n",
    "df2['time'] = df2['time'] / 1000\n",
    "\n",
    "# replace question string 'q7_time' with number '7' in column 'question'\n",
    "from re import search as re_search               # regular expression\n",
    "new_column = []\n",
    "for values in df2['question']:\n",
    "    new_column.append(int(re_search(r'\\d+', values).group()))\n",
    "df2['question'] = new_column\n",
    "\n",
    "# choose the right pattern from the list 'pattern_order' and add as column 'pattern'\n",
    "new_column = []\n",
    "for (pattern_order_list, ind) in zip(df2['pattern_order'], df2['question']):\n",
    "    new_column.append(pattern_order_list[ind-1])\n",
    "df2['pattern'] = new_column\n",
    "\n",
    "# determine the 'mode' (SQL or RD) from 'sequence_num' and 'question'\n",
    "#   sequence_num = 0 means that the first question is shown in SQL, 1 means we start instead with RD. Then alternate between the two.\n",
    "#   Thus (sequence_num + question_num) % 2 == 1 means SQL\n",
    "#   Thus (sequence_num + question_num) % 2 == 0 means RD\n",
    "new_column = []\n",
    "for (sequence, question) in zip(df2['sequence_num'], df2['question']):\n",
    "    mode = 'SQL' if (sequence + question) % 2 == 1 else 'RD'\n",
    "    new_column.append(mode)\n",
    "df2['mode'] = new_column\n",
    "\n",
    "# determine the 'choice' (among the 4 patterns) made by the user for this question. Requires all the 32 question choices (e.g. 'q7') and index of the question at hand ('question')\n",
    "questionarray = df2[['q1', 'q2','q3', 'q4','q5', 'q6','q7', 'q8', 'q9', 'q10',\n",
    "                     'q11', 'q12','q13', 'q14','q15', 'q16','q17', 'q18', 'q19', 'q20',\n",
    "                     'q21', 'q22','q23', 'q24','q25', 'q26','q27', 'q28', 'q29', 'q30',\n",
    "                     'q31', 'q32']].to_numpy()\n",
    "questionindex = df2[[\"question\"]].to_numpy()\n",
    "\n",
    "new_array = np.take_along_axis(questionarray,questionindex-1,1)     # take the 'questionindex'-th entry from each row of the questionarray (notice 1-index vs 0-indexin)\n",
    "df2['choice'] = new_array\n",
    "\n",
    "# determine whether the choice was correct by comparing the ground truth ('pattern') against the choice made ('choice'). Saved as 0/1 value in new column 'correct'\n",
    "new_column = []\n",
    "for (pattern, choice) in zip(df2['pattern'], df2['choice']):\n",
    "    correct = 1 if pattern == choice else 0\n",
    "    new_column.append(correct)\n",
    "df2['correct'] = new_column\n",
    "\n",
    "# sort by worker and question number, and reset the inde\n",
    "df2.sort_values(by=['worker_id', 'question'], inplace=True)\n",
    "df2.reset_index(drop=True, inplace=True)\n",
    "# display(df2)\n",
    "\n",
    "# select only the relevant subset of columns\n",
    "df_transformed_data = df2[['worker_id', 'question', 'time', 'pattern', 'mode', 'choice', 'correct']]\n",
    "# display(df3)\n",
    "\n",
    "# pd.write_csv(filename)\n",
    "df_transformed_data.to_csv(transformeddata,\n",
    "                           index=False,\n",
    "                           )\n",
    "display(dfresults)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaeee9a1",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Loading transformed data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e29e9e0e",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "df_transformed_data = pd.read_csv(transformeddata)\n",
    "display(df_transformed_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4330c84c",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "## Filter users down to first 50 (VARIANT filters), and total time users took (in minutes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56ad87ce",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# New dataframe with worker id and when they started the HITS (allowing to sort by starting time)\n",
    "dfendtime = dfresults[[\"worker_id\", \"start_datetime\", \"sequence_num\"]]\n",
    "dfendtime.set_index(\"worker_id\", inplace=True)\n",
    "\n",
    "# New dataframe with worker ids and fraction correct (allowing to filter out those who did not pass the 0.5 correctness criterion)\n",
    "dftemp = df_transformed_data.groupby(['worker_id']).agg(\n",
    "    time=('time', np.sum),\n",
    "    correct=('correct', np.mean))\n",
    "dftemp['time'] = dftemp['time'] / 60\n",
    "dftemp.sort_values(by=['correct'], ascending=False, inplace=True)\n",
    "# display(dftemp)\n",
    "\n",
    "# Joining the dataframes\n",
    "dftemp = dftemp.join(dfendtime)\n",
    "\n",
    "# Filtering the dataframes for those who passed the 0.5 correctness criterion\n",
    "dftemp = dftemp.loc[dftemp.correct >= 0.5]\n",
    "\n",
    "# Keep only first 50 participants (creates imbalance: 26/24 between sequence numbers)\n",
    "# dftemp = dftemp.sort_values(by=\"start_datetime\", ascending=True)\n",
    "# dftemp = dftemp.head(50)                            # only keep the first 50 participants\n",
    "\n",
    "# Keep only first 50 balanced participants, thus first 25 from sequence 0, and first 25 from sequence 1\n",
    "dftemp0 = dftemp.loc[(dftemp.sequence_num == 0.0)].copy()\n",
    "dftemp0 = dftemp0.sort_values(by=\"start_datetime\", ascending=True)\n",
    "dftemp0 = dftemp0.head(25)                            # only keep the first 25 participants\n",
    "dftemp1 = dftemp.loc[(dftemp.sequence_num == 1.0)].copy()\n",
    "dftemp1 = dftemp1.sort_values(by=\"start_datetime\", ascending=True)\n",
    "dftemp1 = dftemp1.head(25)                            # only keep the first 25 participants\n",
    "dftemp = pd.concat([dftemp0, dftemp1])\n",
    "\n",
    "if VARIANT == 2:\n",
    "    dftemp = dftemp.loc[dftemp.correct == 1.0]                                  # 12/50\n",
    "if VARIANT == 3:\n",
    "    dftemp = dftemp.loc[dftemp.correct >= 0.9]                                  # up to 3 mistakes, 27/50\n",
    "if VARIANT == 4:\n",
    "    dftemp = dftemp.loc[dftemp.correct >= 0.66]                                 # up to 12 mistakes, thus 2/3 correct, 34/50\n",
    "print('dftemp:')\n",
    "display(dftemp)\n",
    "\n",
    "print('Number of participants who started with RD first:', np.sum(dftemp.sequence_num))\n",
    "\n",
    "df_filtered_data = df_transformed_data[df_transformed_data.worker_id.isin(dftemp.index)]     # only retain those that pass the 0.5 correctness criterium\n",
    "print('df_filtered_data:')\n",
    "display(df_filtered_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "524ecbe1",
   "metadata": {},
   "source": [
    "# Question 1. Figure 1a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73a42469",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# create two columns mode and median, with 2 rows per worker (used for Fig 1a violines)\n",
    "dfq1a = df_filtered_data.groupby(['worker_id', 'mode']).time.agg(['median'])\n",
    "dfq1a.reset_index(inplace=True)\n",
    "# print('dfq1a:')\n",
    "# display(dfq1a)\n",
    "\n",
    "# pivot to have one row per worker (used for Fig 1a individual points)\n",
    "dfq1b = pd.pivot_table(dfq1a, values=['median'], index=['worker_id'], columns=['mode'])\n",
    "dfq1b=dfq1b.droplevel(0, axis=1)\n",
    "print('dfq1b:')\n",
    "display(dfq1b)\n",
    "\n",
    "modes = ['RD', 'SQL']\n",
    "median_time = {}\n",
    "ci = {}\n",
    "ci_delta = {}\n",
    "for mode in modes:\n",
    "    median_time[mode] = np.median(dfq1b[mode])\n",
    "    ci[mode] = scipybootstrap((dfq1b[mode],), statistic=np.median, n_resamples=BOOTSTRAPSAMPLES, confidence_level=BOOTSTRAPCONFIDENCE, method='percentile', axis=0).confidence_interval        #convert array to sequence\n",
    "    ci_delta[mode] = [median_time[mode] - ci[mode].low, ci[mode].high - median_time[mode]]\n",
    "\n",
    "    print(f'Median time {mode}: {median_time[mode]:.2f}, 95% CI [{ci[mode].low:.2f}, {ci[mode].high:.2f}]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c394044",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Define pre-settings\n",
    "figwidth = 10\n",
    "figheight = 6\n",
    "xlab_size = 20\n",
    "ylab_size = 20\n",
    "figfont_size = 24\n",
    "\n",
    "# Define consistent color maps\n",
    "my_cmap_sns_light = [(0.9921568627450981, 0.8156862745098039, 0.6352941176470588), (0.7764705882352941, 0.8588235294117647, 0.9372549019607843)]        # light blue, light orange\n",
    "my_cmap_sns_dark = [(0.9019607843137255, 0.3333333333333333, 0.050980392156862744), (0.19215686274509805, 0.5098039215686274, 0.7411764705882353)]      # dark blue, dark orange\n",
    "my_cmap_dark = sns.color_palette(my_cmap_sns_dark, as_cmap=True)\n",
    "my_cmap_light = sns.color_palette(my_cmap_sns_light, as_cmap=True)\n",
    "\n",
    "\n",
    "# Create empty figure and plot the individual datapoints\n",
    "fig, ax = plt.subplots(figsize=(figwidth,figheight))\n",
    "\n",
    "\n",
    "# 1. Violinplots\n",
    "axsns = sns.violinplot(x='median', y='mode', data=dfq1a,\n",
    "                       hue=True, hue_order=[False, True], split=True,   # half violinplots https://stackoverflow.com/questions/53872439/half-not-split-violin-plots-in-seaborn\n",
    "                       inner='quartile',\n",
    "                       cut=0,               # 0 means ending sharp at end points\n",
    "                       width=.7,\n",
    "                       orient = 'h',\n",
    "                       zorder=20,)\n",
    "\n",
    "# change the medium default line to full (https://stackoverflow.com/questions/60638344/quartiles-line-properties-in-seaborn-violinplot)\n",
    "for l in axsns.lines[1::3]:\n",
    "    l.set_linestyle('-')\n",
    "    l.set_linewidth(1.2)\n",
    "    l.set_color('black')\n",
    "    l.set_alpha(0.8)\n",
    "\n",
    "# Apply colorscheme to violinplots https://stackoverflow.com/questions/70442958/seaborn-how-to-apply-custom-color-to-each-seaborn-violinplot\n",
    "from matplotlib.collections import PolyCollection\n",
    "for ind, violin in enumerate(axsns.findobj(PolyCollection)):\n",
    "    violin.set_facecolor(my_cmap_light[ind])\n",
    "\n",
    "\n",
    "# 2. Plot individual points\n",
    "y_tilt = -0.25                                      # Set some delta for the points below the violinplot\n",
    "y_base = np.zeros(dfq1b.values.shape[0]) + y_tilt   # base vector to which to broadcast y-tilt values\n",
    "\n",
    "for i, col in enumerate(modes):\n",
    "    ax.plot(dfq1b[col],\n",
    "            y_base + i,\n",
    "            # 'o',          # circles\n",
    "            '^',            # triangles_up\n",
    "            alpha=1,\n",
    "            zorder=20,      # higher means more visible\n",
    "            markersize=11,\n",
    "            markeredgewidth=0,\n",
    "            # markerfacecolor='none',\n",
    "            markerfacecolor=my_cmap_dark[i],\n",
    "            markeredgecolor=my_cmap_dark[i],)\n",
    "    ax.plot(dfq1b[col],\n",
    "            y_base + i,\n",
    "            # 'o',          # circles\n",
    "            '^',            # triangles_up\n",
    "            markersize=11,\n",
    "            markerfacecolor='white',\n",
    "            markeredgewidth=1,\n",
    "            color ='white',\n",
    "            linewidth = None,\n",
    "            zorder=1,)\n",
    "\n",
    "\n",
    "# 3. Plot gray lines connecting modes\n",
    "for i, idx in enumerate(dfq1b.index):\n",
    "    ax.plot(dfq1b.loc[idx, modes],\n",
    "            [y_tilt, y_tilt+1],\n",
    "            color ='gray', linewidth = 2, linestyle ='-', alpha = .2,\n",
    "            zorder=0)\n",
    "\n",
    "\n",
    "# 4. Plot red line connecting medians\n",
    "ax.plot(np.median(dfq1b, axis=0), [0, 1], color ='red', linewidth = 2, linestyle ='-', alpha = .4)\n",
    "\n",
    "\n",
    "# 5. CI Errorbars\n",
    "for i, mode in enumerate(modes):\n",
    "    plt.errorbar(median_time[mode], i, xerr=np.array([[ci_delta[mode][0], ci_delta[mode][1]]]).T,\n",
    "                 fmt='o', markersize=10,\n",
    "                 # lw = 3,          # if end line for CI\n",
    "                 lw = 5,            # if no ned line for CI\n",
    "                 alpha=1,\n",
    "                 zorder=100,        # higher means more visible\n",
    "                 capsize = 0,      # 10\n",
    "                 # capthick = 4,    # end line for CI\n",
    "                 capthick = 0,      # no end line for CI\n",
    "                 # color = 'black',\n",
    "                 color = my_cmap_dark[i],\n",
    "                 )   # my_cmap[1])\n",
    "    ax.text(median_time[mode],\n",
    "            # i+0.36,\n",
    "            i-0.16,\n",
    "            f'{median_time[mode]:.1f}', horizontalalignment='center',\n",
    "            # color='black',\n",
    "            color= my_cmap_dark[i],\n",
    "            fontsize=figfont_size)\n",
    "    # ax.text(ci[mode].low, i+0.1, f'{ci[mode].low:.1f}', horizontalalignment='center', color='black', fontsize=20)\n",
    "    # ax.text(ci[mode].high, i+0.1, f'{ci[mode].high:.1f}', horizontalalignment='center', color='black', fontsize=20)\n",
    "\n",
    "\n",
    "#Additional settings\n",
    "ax.set_xticks(range(0, 100, 5))\n",
    "ax.set_yticks(range(len(dfq1b.columns)))\n",
    "ax.set_yticklabels(modes, size= ylab_size)\n",
    "ax.set_xlim(0, 40.1)\n",
    "ax.set_ylim(-0.5, 1.5)\n",
    "ax.set_xlabel('Median time per worker (sec)', size = xlab_size)\n",
    "ax.set_ylabel(None)\n",
    "ax.set_yticklabels(['RD', 'SQL', ])\n",
    "# ax.set_title('Median times per worker', size = title_size)\n",
    "sns.despine()\n",
    "ax.legend_.remove()\n",
    "\n",
    "plt.grid(axis = 'x', linewidth = 0.5, color = 'lightgray')\n",
    "\n",
    "if savefig:\n",
    "    plt.savefig(fig_dir + f'/q1_figure1_variant{VARIANT}.pdf', bbox_inches='tight')\n",
    "    plt.savefig(fig_dir + f'/q1_figure1_variant{VARIANT}.svg', bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbc86c76",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "# Question 1. Figure 1b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb8b617c",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dfq1c = df_filtered_data.groupby(['worker_id', 'mode']).time.agg(['median'])                               # for each worker, calculate median for both modes\n",
    "dfq1c = pd.pivot_table(dfq1c, values=['median'], index=['worker_id'], columns=['mode'])       # pivot to have one row per worker\n",
    "dfq1c['ratio median'] = dfq1c['median','RD'] / dfq1c['median','SQL']                           # add the ratio between medians of the two modes\n",
    "\n",
    "print('dfq1c:')\n",
    "display(dfq1c)\n",
    "\n",
    "sample = np.array(dfq1c['ratio median'])                                                     # extract the sample and then create the boostrapped medians\n",
    "data_ratio = dfq1c['ratio median']\n",
    "\n",
    "median_ratio = np.median(data_ratio)\n",
    "ci_ratio = scipybootstrap((data_ratio,), statistic=np.median, n_resamples=BOOTSTRAPSAMPLES, confidence_level=BOOTSTRAPCONFIDENCE, method=BOOTSTRAPMETHOD, axis=0).confidence_interval        #convert array to sequence\n",
    "ci_ratio_delta = [median_ratio - ci_ratio.low, ci_ratio.high - median_ratio]\n",
    "\n",
    "print(f'Median ratio: {median_ratio:.3f}, 95% CI [{ci_ratio.low:.3f}, {ci_ratio.high:.3f}]')\n",
    "print(f'Number (fraction) of users faster with RD: {np.sum(data_ratio<1.0)} ({np.sum(data_ratio<1.0)/np.sum(data_ratio>0.0):.3f})')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3c5ea47",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Define figure settings\n",
    "figwidth = 10\n",
    "figheight = 3\n",
    "xlab_size = 20\n",
    "ylab_size = 20\n",
    "figfont_size = 24\n",
    "\n",
    "# Define consistent color maps\n",
    "my_cmap_sns_dark = [(0.8392156862745098, 0.15294117647058825, 0.1568627450980392)]\n",
    "my_cmap_sns_light = [(0.984313725490196, 0.6039215686274509, 0.6)]\n",
    "my_cmap_dark = sns.color_palette(my_cmap_sns_dark, as_cmap=True)\n",
    "my_cmap_light = sns.color_palette(my_cmap_sns_light, as_cmap=True)\n",
    "\n",
    "# Create data frame for split violinplot\n",
    "dfvp = pd.DataFrame()\n",
    "dfvp[\"values\"] = sample\n",
    "dfvp[\"all\"] = \"\"                                        # attribute that is shared by all entries\n",
    "# print(dfvp)\n",
    "\n",
    "# Create empty figure and plot the individual datapoints\n",
    "fig, ax = plt.subplots(figsize=(figwidth,figheight))\n",
    "\n",
    "\n",
    "# 1. Violinplot\n",
    "axsns = sns.violinplot(x='values', y='all',    # y='all' just need to group both types together\n",
    "                       data=dfvp,\n",
    "                       hue = True, hue_order = [False, True],\n",
    "                       split = True, inner = 'quartile',\n",
    "                       cut=0,                  # 0 means ending sharp at end points\n",
    "                       width=.6, scale = 'width',\n",
    "                       # dodge = False,        # When using ``hue`` nesting, setting this to ``True`` will separate the strips for different hue levels along the categorical axis.\n",
    "                       orient = 'h',\n",
    "                       color=my_cmap_light[0],)\n",
    "\n",
    "# change the medium default linke to full\n",
    "for l in axsns.lines[1::3]:\n",
    "    l.set_linestyle('-')\n",
    "    l.set_linewidth(1.5)\n",
    "    l.set_color('black')\n",
    "    l.set_alpha(0.8)\n",
    "\n",
    "\n",
    "# 2. Plot individual points\n",
    "y_tilt = -0.13                                   # Set some delta for the points below the violinplot\n",
    "y_base = np.zeros(len(data_ratio)) + y_tilt     # base vector to which to broadcast y-tilt values\n",
    "\n",
    "ax.plot(data_ratio, y_base,\n",
    "        # 'o',\n",
    "        '^',\n",
    "        alpha=1,\n",
    "        zorder=20,      # higher means more visible\n",
    "        markersize=11,\n",
    "        markeredgewidth=0,\n",
    "        # markerfacecolor='none',\n",
    "        markerfacecolor=my_cmap_dark[0],\n",
    "        markeredgecolor=my_cmap_dark[0],\n",
    "        )\n",
    "\n",
    "\n",
    "# 3. CI Errorbars & show numbers\n",
    "axeb = plt.errorbar(median_ratio, 0, xerr=np.array([[ci_ratio_delta[0], ci_ratio_delta[1]]]).T,\n",
    "                    fmt='o',\n",
    "                    markersize=10, alpha=1,\n",
    "                    # lw = 3,\n",
    "                    lw = 5,\n",
    "                    zorder=100,      # higher means more visible\n",
    "                    capsize = 0,     # 10\n",
    "                    # capthick = 4,\n",
    "                    capthick = 0,\n",
    "                    # color = 'black',\n",
    "                    color = my_cmap_dark[0],\n",
    "                    )\n",
    "\n",
    "med = np.median(sample)\n",
    "# ax.text(med, 0.32, f'{100*med:.1f}%', horizontalalignment='center', color='black', fontsize=20)\n",
    "# ax.text(med, 0.32, f'{med:.2f}', horizontalalignment='center', color='black', fontsize=20)\n",
    "ax.text(med, -0.1, f'{med:.2f}', horizontalalignment='center',\n",
    "        # color='black',\n",
    "        color = my_cmap_dark[0],\n",
    "        fontsize=figfont_size)\n",
    "# ax.text(ci_ratio.low, 0.04, f'{100*ci_ratio.low:.1f}%', horizontalalignment='center', color='black', fontsize=20)\n",
    "# ax.text(ci_ratio.high, 0.04, f'{100*ci_ratio.high:.1f}%', horizontalalignment='center', color='black', fontsize=20)\n",
    "\n",
    "\n",
    "# 4. vertical bar for x-axis = 1\n",
    "plt.plot([1, 1], [-10, 10], color = 'black', zorder = 0, linewidth = 2)\n",
    "\n",
    "\n",
    "# Additional settings\n",
    "# ax.set_ylim(-0.2, 0.4)\n",
    "ax.set_xticks(np.linspace(0, 2, num=21))\n",
    "ax.set_ylim(-0.25, 0.35)\n",
    "ax.set_ylabel(None)         # remove the 'all'\n",
    "ax.set_xlim(0.2, 1.25)\n",
    "if VARIANT == 1:\n",
    "    ax.set_xlim(0.2, 1.301)\n",
    "if VARIANT == 3:\n",
    "    ax.set_xlim(0.499, 1.205)\n",
    "ax.set_xlabel('Ratio of median time per worker (RD / SQL)', size = xlab_size)\n",
    "sns.despine(left=True)               # remove bounding box\n",
    "plt.grid(axis = 'x', linewidth = 0.5, color = 'lightgray')\n",
    "ax.legend_.remove()\n",
    "\n",
    "if savefig:\n",
    "    plt.savefig(fig_dir + f'/q1_figure2_variant{VARIANT}.pdf', bbox_inches='tight')\n",
    "    plt.savefig(fig_dir + f'/q1_figure2_variant{VARIANT}.svg', bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c17c56d9",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "# Question 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d81ad1bc",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Create df6, df7\n",
    "df0 = df_filtered_data[['worker_id', 'question', 'time', 'mode']].copy()\n",
    "df0['H1'] = np.where(df0['question'].between(1, 16, inclusive='both'), 'H1', '')\n",
    "df0['H2'] = np.where(df0['question'].between(17, 32, inclusive='both'), 'H2', '')\n",
    "# display(df0)\n",
    "\n",
    "df1 = df0[['worker_id','question','time','mode','H1']].rename(columns={'H1': 'section'}) # Two sections: 1st half (H1) and 2nd half (H2)\n",
    "df2 = df0[['worker_id','question','time','mode','H2']].rename(columns={'H2': 'section'})\n",
    "df4 = pd.concat([df1, df2])\n",
    "df4 = df4.loc[df4['section'] != '']\n",
    "df4 = df4.reset_index(inplace=False, drop=True)\n",
    "# display(df4)\n",
    "\n",
    "df5 = df4.groupby(['worker_id', 'mode', 'section']).time.agg(['median'])                # for each worker, calculate median for both modes and section\n",
    "df5.reset_index(inplace=True)\n",
    "# display(df5)\n",
    "\n",
    "# pivot to have one row per worker\n",
    "df6 = pd.pivot_table(df5, values=['median'], index=['worker_id'], columns=['mode', 'section'])\n",
    "df6=df6.droplevel(0, axis=1)\n",
    "\n",
    "# relative improvements per user per mode\n",
    "df6['RD', 'ratio'] = df6['RD', 'H2'] / df6['RD', 'H1']\n",
    "df6['SQL', 'ratio'] = df6['SQL', 'H2'] / df6['SQL', 'H1']\n",
    "# relative improvements of RD over SQL per user half\n",
    "df6['H1', 'ratio'] = df6['RD', 'H1'] / df6['SQL', 'H1']\n",
    "df6['H2', 'ratio'] = df6['RD', 'H2'] / df6['SQL', 'H2']\n",
    "print('df6:')\n",
    "display(df6)\n",
    "\n",
    "# Median of median task time for each mode and section\n",
    "modes = ['RD', 'SQL']\n",
    "sections = ['H1', 'H2', 'ratio']\n",
    "median_time = {}\n",
    "ci = {}\n",
    "ci_delta = {}\n",
    "for mode in modes:\n",
    "    for section in sections:\n",
    "        column = (mode, section)\n",
    "        median_time[column] = np.median(df6[column])\n",
    "        ci[column] = scipybootstrap((df6[column],), statistic=np.median,\n",
    "                                    n_resamples=BOOTSTRAPSAMPLES,\n",
    "                                    confidence_level=BOOTSTRAPCONFIDENCE,\n",
    "                                    method='percentile',\n",
    "                                    axis=0).confidence_interval        #convert array to sequence\n",
    "        ci_delta[column] = [median_time[column] - ci[column].low, ci[column].high - median_time[column]]\n",
    "        print(f'{mode}, {section}: {median_time[column]:.3f}, 95% CI [{ci[column].low:.3f}, {ci[column].high:.3f}]')\n",
    "\n",
    "for half in ['H1', 'H2']:\n",
    "    column = half\n",
    "    median_time[column] = np.median(df6[column])\n",
    "    ci[column] = scipybootstrap((df6[column],), statistic=np.median,\n",
    "                                n_resamples=BOOTSTRAPSAMPLES,\n",
    "                                confidence_level=BOOTSTRAPCONFIDENCE,\n",
    "                                method='percentile',\n",
    "                                axis=0).confidence_interval        #convert array to sequence\n",
    "    ci_delta[column] = [median_time[column] - ci[column].low, ci[column].high - median_time[column]]\n",
    "    print(f'{column}: {median_time[column]:.3f}, 95% CI [{ci[column].low[0]:.3f}, {ci[column].high[0]:.3f}]')\n",
    "\n",
    "# uses df5 to make df7 (used for later plot)\n",
    "modes = ['SQL', 'RD']\n",
    "sections = ['H1', 'H2']\n",
    "df7 = df5.loc[df5['section'].isin(sections)]\n",
    "# display(df7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc21c930",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Plot (uses df6, df7)\n",
    "\n",
    "# Define pre-settings\n",
    "figwidth = 10\n",
    "figheight = 6\n",
    "if VARIANT == 1:\n",
    "    figheight = 8\n",
    "xlab_size = 20\n",
    "ylab_size = 20\n",
    "figfont_size = 24\n",
    "\n",
    "# Define consistent color maps\n",
    "my_cmap_sns_light = [(0.7764705882352941, 0.8588235294117647, 0.9372549019607843), (0.9921568627450981, 0.8156862745098039, 0.6352941176470588)]        # light orange, light blue\n",
    "my_cmap_sns_dark = [(0.19215686274509805, 0.5098039215686274, 0.7411764705882353), (0.9019607843137255, 0.3333333333333333, 0.050980392156862744)]      # dark orange, dark blue\n",
    "my_cmap_dark = sns.color_palette(my_cmap_sns_dark, as_cmap=True)\n",
    "my_cmap_light = sns.color_palette(my_cmap_sns_light, as_cmap=True)\n",
    "\n",
    "\n",
    "# Create empty figure and plot the individual datapoints\n",
    "fig, ax = plt.subplots(figsize=(figwidth,figheight))\n",
    "\n",
    "\n",
    "# 1. Violinplots\n",
    "axsns = sns.violinplot(x='median', y='section', data=df7,\n",
    "                       hue='mode',\n",
    "                       hue_order=['SQL', 'RD'],\n",
    "                       split=True,   # half violinplots https://stackoverflow.com/questions/53872439/half-not-split-violin-plots-in-seaborn\n",
    "                       inner='quartile',\n",
    "                       cut=0,               # 0 means ending sharp at end points\n",
    "                       width=.4,\n",
    "                       orient = 'h',\n",
    "                       zorder=20,\n",
    "                       palette = my_cmap_light,)\n",
    "\n",
    "# change the medium default line to full (https://stackoverflow.com/questions/60638344/quartiles-line-properties-in-seaborn-violinplot)\n",
    "for l in axsns.lines[1::3]:\n",
    "    l.set_linestyle('-')\n",
    "    l.set_linewidth(1.2)\n",
    "    l.set_color('black')\n",
    "    # l.set_alpha(0.8)\n",
    "\n",
    "\n",
    "# 2. Plot individual points\n",
    "y_base = np.zeros(df6.values.shape[0])  # base vector to which to broadcast y-tilt values\n",
    "y_tilt_mode = [0.3, 0.38]\n",
    "y_tilt_section = [0, 1]\n",
    "for i, mode in enumerate(modes):\n",
    "    for j, section in enumerate(sections):\n",
    "        column = (mode, section)\n",
    "        ax.plot(df6[column],\n",
    "                y_base + y_tilt_mode[i] + y_tilt_section[j],\n",
    "                # 'o',\n",
    "                # '|',\n",
    "                '^',\n",
    "                alpha=1,\n",
    "                zorder=20,      # higher means more visible\n",
    "                markersize=11,\n",
    "                markeredgewidth=0,\n",
    "                # markerfacecolor='none',\n",
    "                markerfacecolor=my_cmap_sns_dark[i],\n",
    "                markeredgecolor=my_cmap_sns_dark[i],)\n",
    "        ax.plot(df6[column],          # white background\n",
    "                y_base + y_tilt_mode[i] + y_tilt_section[j],\n",
    "                # 'o',\n",
    "                # '|',\n",
    "                '^',\n",
    "                markersize=11,\n",
    "                markeredgewidth=1,\n",
    "                markerfacecolor='white',\n",
    "                color ='white',\n",
    "                linewidth = None,\n",
    "                zorder=1,)\n",
    "\n",
    "\n",
    "# # 3. Plot lines connecting points\n",
    "# for idx in df6.index:\n",
    "#     for i, mode in enumerate(modes):\n",
    "#         for j in range(len(sections)-1):\n",
    "#             start = (mode, sections[j])\n",
    "#             end = (mode, sections[j+1])\n",
    "#             ax.plot(df6.loc[idx, [start, end]],\n",
    "#                     [y_tilt_mode[i] + y_tilt_section[j], y_tilt_mode[i] + y_tilt_section[j+1]],\n",
    "#                     color=my_cmap_sns_dark[i], linewidth=2, linestyle='-', alpha=.2, zorder=0)\n",
    "\n",
    "\n",
    "# 4. CI Errorbars & numbers\n",
    "y_tilt_mode = [0.5, 0.55]\n",
    "# y_tilt_section_bar = [0.23, 0.8]\n",
    "# y_tilt_section_number = [0.19, 0.89]\n",
    "for i, mode in enumerate(modes):\n",
    "    for j, section in enumerate(sections):\n",
    "        column = (mode, section)\n",
    "        plt.errorbar(median_time[column], y_tilt_mode[i]+y_tilt_section[j],\n",
    "                     xerr=np.array([[ci_delta[column][0] ,ci_delta[column][1]]]).T,\n",
    "                     fmt='o', markersize=10,\n",
    "                     lw = 3, alpha=1,\n",
    "                     zorder=100,        # higher means more visible\n",
    "                     #             capsize = 10, capthick = 4,\n",
    "                     capsize = 0,\n",
    "                     color = my_cmap_sns_dark[i]    # 'black'\n",
    "                     )\n",
    "        ax.text(median_time[column], y_tilt_mode[i]+y_tilt_section[j] + 0.18, f'{median_time[column]:.1f}',\n",
    "                horizontalalignment='center', color = my_cmap_sns_dark[i],\n",
    "                fontsize=figfont_size)\n",
    "\n",
    "\n",
    "# 5. Plot red line connecting medians\n",
    "for i, mode in enumerate(modes):\n",
    "    ax.plot([median_time[(mode, 'H1')], median_time[(mode, 'H2')]],\n",
    "            [y_tilt_mode[i]+y_tilt_section[0], y_tilt_mode[i]+y_tilt_section[1]],\n",
    "            color=my_cmap_sns_dark[i], linewidth = 3, linestyle ='-',\n",
    "            alpha=.3,\n",
    "            zorder=0)\n",
    "\n",
    "\n",
    "# #Additional settings\n",
    "ax.set_xticks(range(0, 100, 5))\n",
    "ax.set_xlabel('Median time per worker and halves (sec)', size = xlab_size)\n",
    "ax.set_ylabel(None)\n",
    "ax.set_xlim(0, 40.1)\n",
    "ax.set_ylim(1.82, -0.25)\n",
    "leg = plt. legend(loc='lower right',\n",
    "                  borderaxespad= 0.2,\n",
    "                  frameon = True,\n",
    "                  labelspacing = 0.1)\n",
    "leg.get_frame().set_alpha(1)\n",
    "leg.get_frame().set_linewidth(0.0)\n",
    "for text, text2 in zip(leg.get_texts(), ['SQL', 'RD']):\n",
    "    text.set_text(text2)\n",
    "\n",
    "plt.grid(axis = 'x', linewidth = 0.5, color = 'lightgray')\n",
    "sns.despine()               # remove bounding box\n",
    "\n",
    "if savefig:\n",
    "    plt.savefig(fig_dir + f'/q2_figure_variant{VARIANT}.pdf', bbox_inches='tight')\n",
    "    plt.savefig(fig_dir + f'/q2_figure_variant{VARIANT}.svg', bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cd2ab3b",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "# Question 3: four patterns\n",
    "\n",
    "(1) calculate the median time per pattern (4) across the two modes (2). Thus 8 values.\n",
    "(2) show repeated measure violin plot figure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da5ecbe0",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Create df8, df9\n",
    "df0 = df_filtered_data[['worker_id', 'pattern', 'time', 'mode']]\n",
    "# print(df)\n",
    "\n",
    "df8 = df0.groupby(['worker_id', 'mode', 'pattern']).time.agg(['median'])         # for each worker, calculate median for both modes\n",
    "df8.reset_index(inplace=True)\n",
    "# print('df8:')\n",
    "# display(df8)\n",
    "\n",
    "# Pivot to have one row per worker\n",
    "df9 = pd.pivot_table(df8, values=['median'], index=['worker_id'], columns=['mode', 'pattern'])\n",
    "df9=df9.droplevel(0, axis=1)\n",
    "print('df9:')\n",
    "display(df9)\n",
    "\n",
    "# Median of median task time for each mode and section (for error plots)\n",
    "modes = ['RD', 'SQL']\n",
    "patterns = [1, 2, 3, 4]\n",
    "median_time = {}\n",
    "ci = {}\n",
    "ci_delta = {}\n",
    "for mode in modes:\n",
    "    for pattern in patterns:\n",
    "        column = (mode, pattern)\n",
    "        median_time[column] = np.median(df9[column])\n",
    "        ci[column] = scipybootstrap((df9[column],), statistic=np.median,\n",
    "                                    n_resamples=BOOTSTRAPSAMPLES,\n",
    "                                    confidence_level=BOOTSTRAPCONFIDENCE,\n",
    "                                    method='percentile',\n",
    "                                    axis=0).confidence_interval        #convert array to sequence\n",
    "        ci_delta[column] = [median_time[column] - ci[column].low, ci[column].high - median_time[column]]\n",
    "        print(f'{mode}, {pattern}: {median_time[column]:.3f}, 95% CI [{ci[column].low:.3f}, {ci[column].high:.3f}]')\n",
    "\n",
    "# Median ratio RD/SQL per pattern\n",
    "for pattern in patterns:\n",
    "    column = ('ratio', pattern)\n",
    "    df9['ratio', pattern] = df9['RD', pattern] / df9['SQL', pattern]\n",
    "    median_time[column] = np.median(df9[column])\n",
    "    ci[column] = scipybootstrap((df9[column],), statistic=np.median,\n",
    "                                n_resamples=BOOTSTRAPSAMPLES,\n",
    "                                confidence_level=BOOTSTRAPCONFIDENCE,\n",
    "                                method='percentile',\n",
    "                                axis=0).confidence_interval        #convert array to sequence\n",
    "    ci_delta[column] = [median_time[column] - ci[column].low, ci[column].high - median_time[column]]\n",
    "    print(f'ratio, {pattern}: {median_time[column]:.3f}, 95% CI [{ci[column].low:.3f}, {ci[column].high:.3f}]')\n",
    "\n",
    "\n",
    "# print(median_time)\n",
    "# print(ci)\n",
    "# print(ci_delta)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29006886",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# needs df8 for violin, df9 for points, dictionaries (median_time, ci, ci_delta) for error plots, df6 for individual points\n",
    "\n",
    "modes = ['SQL', 'RD']\n",
    "patterns = [1, 2, 3, 4]\n",
    "y_tilt_section = [0, 1, 2, 3]\n",
    "\n",
    "# Define pre-settings\n",
    "figwidth = 10\n",
    "figheight = 9\n",
    "xlab_size = 20\n",
    "ylab_size = 20\n",
    "figfont_size = 20\n",
    "\n",
    "# Define consistent color maps\n",
    "my_cmap_sns_light = [(0.7764705882352941, 0.8588235294117647, 0.9372549019607843), (0.9921568627450981, 0.8156862745098039, 0.6352941176470588)]        # light orange, light blue\n",
    "my_cmap_sns_dark = [(0.19215686274509805, 0.5098039215686274, 0.7411764705882353), (0.9019607843137255, 0.3333333333333333, 0.050980392156862744)]      # dark orange, dark blue\n",
    "my_cmap_dark = sns.color_palette(my_cmap_sns_dark, as_cmap=True)\n",
    "my_cmap_light = sns.color_palette(my_cmap_sns_light, as_cmap=True)\n",
    "\n",
    "# Create empty figure and plot the individual datapoints\n",
    "fig, ax = plt.subplots(figsize=(figwidth,figheight))\n",
    "\n",
    "\n",
    "# 1. Violinplots\n",
    "axsns = sns.violinplot(x='median', y='pattern', data=df8,\n",
    "                       hue='mode',\n",
    "                       hue_order=['SQL', 'RD'],\n",
    "                       split=True,   # half violinplots https://stackoverflow.com/questions/53872439/half-not-split-violin-plots-in-seaborn\n",
    "                       inner='quartile',\n",
    "                       cut=0,               # 0 means ending sharp at end points\n",
    "                       width=.4,\n",
    "                       orient = 'h',\n",
    "                       zorder=20,\n",
    "                       palette = my_cmap_light,)\n",
    "\n",
    "# change the medium default line to full (https://stackoverflow.com/questions/60638344/quartiles-line-properties-in-seaborn-violinplot)\n",
    "for l in axsns.lines[1::3]:\n",
    "    l.set_linestyle('-')\n",
    "    l.set_linewidth(1.2)\n",
    "    l.set_color('black')\n",
    "    l.set_alpha(0.8)\n",
    "\n",
    "\n",
    "# 2. Plot individual points\n",
    "y_base = np.zeros(df6.values.shape[0])  # base vector to which to broadcast y-tilt values\n",
    "y_tilt_mode = [0.3, 0.39]\n",
    "for i, mode in enumerate(modes):\n",
    "    for j, pattern in enumerate(patterns):\n",
    "        column = (mode, pattern)\n",
    "        ax.plot(df9[column],\n",
    "                y_base + y_tilt_mode[i] + y_tilt_section[j],\n",
    "                '^',\n",
    "                alpha=1,\n",
    "                zorder=20,      # higher means more visible\n",
    "                markersize=10,\n",
    "                markeredgewidth=0,\n",
    "                # markerfacecolor='none',\n",
    "                markerfacecolor=my_cmap_sns_dark[i],\n",
    "                markeredgecolor=my_cmap_sns_dark[i],)\n",
    "        ax.plot(df9[column],    # white background behind the markers, but in front of the connecting lines\n",
    "                y_base + y_tilt_mode[i] + y_tilt_section[j],\n",
    "                '^',\n",
    "                markersize=10,\n",
    "                markeredgewidth=1,\n",
    "                markerfacecolor='white',\n",
    "                color ='white',\n",
    "                linewidth = None,\n",
    "                zorder=1,)\n",
    "\n",
    "\n",
    "# # 3. Plot lines connecting individual points\n",
    "# for idx in df9.index:\n",
    "#     for i, mode in enumerate(modes):\n",
    "#         for j in range(len(patterns)-1):\n",
    "#             start = (mode, patterns[j])\n",
    "#             end = (mode, patterns[j+1])\n",
    "#             ax.plot(df9.loc[idx, [start, end]],\n",
    "#                     [y_tilt_mode[i] + y_tilt_section[j], y_tilt_mode[i] + y_tilt_section[j+1]],\n",
    "#                     color =my_cmap_sns_dark[i], linewidth = 2, linestyle ='-', alpha = .2, zorder=0)\n",
    "\n",
    "\n",
    "# 4. CI Errorbars & numbers\n",
    "y_tilt_mode = [0.5, 0.57]\n",
    "for i, mode in enumerate(modes):\n",
    "    for j, section in enumerate(patterns):\n",
    "        column = (mode, section)\n",
    "        plt.errorbar(median_time[column],\n",
    "                     y_tilt_mode[i]+y_tilt_section[j],\n",
    "                     xerr=np.array([[ci_delta[column][0] ,ci_delta[column][1]]]).T,\n",
    "                     fmt='o', markersize=10,\n",
    "                     capsize = 0,\n",
    "                     lw = 3, alpha=1,\n",
    "                     zorder=100,        # higher means more visible\n",
    "                     color = my_cmap_sns_dark[i])    # 'black'\n",
    "        ax.text(median_time[column], y_tilt_mode[i]+y_tilt_section[j] + 0.22, f'{median_time[column]:.1f}',\n",
    "                horizontalalignment='center', color = my_cmap_sns_dark[i],\n",
    "                fontsize=figfont_size)\n",
    "\n",
    "\n",
    "# 5. Plot red line connecting medians\n",
    "for i, mode in enumerate(modes):\n",
    "    for j in range(len(patterns)-1):\n",
    "        start = (mode, patterns[j])\n",
    "        end = (mode, patterns[j+1])\n",
    "        ax.plot([median_time[start], median_time[end], ],\n",
    "                [y_tilt_mode[i] + y_tilt_section[j], y_tilt_mode[i] + y_tilt_section[j+1]],\n",
    "                color =my_cmap_sns_dark[i], linewidth = 3, linestyle ='-', alpha = .3, zorder=0)\n",
    "\n",
    "\n",
    "\n",
    "# # #Additional settings\n",
    "ax.set_xticks(range(0, 100, 5))\n",
    "ax.set_xlabel('Median time per worker (sec)', size = xlab_size)\n",
    "ax.set_ylabel(None)\n",
    "ax.set_yticklabels(['P1', 'P2', 'P3', 'P4'])\n",
    "ax.set_xlim(0, 50.05)\n",
    "ax.set_ylim(3.9, -0.25)\n",
    "leg = plt.legend(loc='lower right',\n",
    "                 borderaxespad= 0.2,\n",
    "                 frameon = True,\n",
    "                 labelspacing = 0.1)\n",
    "leg.get_frame().set_alpha(1)\n",
    "leg.get_frame().set_linewidth(0.0)\n",
    "\n",
    "for text, text2 in zip(leg.get_texts(), ['SQL', 'RD']):\n",
    "    text.set_text(text2)\n",
    "\n",
    "plt.grid(axis = 'x', linewidth = 0.5, color = 'lightgray')\n",
    "sns.despine()               # remove bounding box\n",
    "\n",
    "if savefig:\n",
    "    plt.savefig(fig_dir + f'/q3_figure_variant{VARIANT}.pdf', bbox_inches='tight')\n",
    "    plt.savefig(fig_dir + f'/q3_figure_variant{VARIANT}.svg', bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "526669f1",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "# Question 4: Correctness\n",
    "\n",
    "(1) take mean correct over all questions and all users answered in SQL or RD (32/2*13) and the difference in correctness score\n",
    "(2) calculate 95% CI for each"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b281fc0",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# dfq4a: Create two columns mode and mean, with 2 rows per worker\n",
    "dfq4a = df_filtered_data.groupby(['worker_id', 'mode']).correct.agg(['mean'])\n",
    "dfq4a.reset_index(inplace=True)\n",
    "# display(dfq4a)\n",
    "\n",
    "# dfq4b: Pivot to have one row per worker\n",
    "dfq4b = pd.pivot_table(dfq4a, values=['mean'], index=['worker_id'], columns=['mode'])\n",
    "dfq4b=dfq4b.droplevel(0, axis=1)\n",
    "dfq4b['diff'] = dfq4b['RD'] - dfq4b['SQL']\n",
    "print(\"dfq4b:\\n\")\n",
    "display(dfq4b)\n",
    "\n",
    "# Calculate fraction of those better in either mode\n",
    "num_SQLbetter = np.where(dfq4b['SQL'] > dfq4b['RD'], 1, 0).sum()\n",
    "num_RDbetter = np.where(dfq4b['SQL'] < dfq4b['RD'], 1, 0).sum()\n",
    "num_workers =len(dfq4b)\n",
    "print(f'{num_SQLbetter}/{num_workers} ({num_SQLbetter/num_workers:.3f}) better with SQL.')\n",
    "print(f'{num_RDbetter}/{num_workers} ({num_RDbetter/num_workers:.3f}) better with RD.')\n",
    "print(f'{num_workers-num_RDbetter-num_SQLbetter}/{num_workers} ({(num_workers-num_RDbetter-num_SQLbetter)/num_workers:.3f}) equally good.')  # (7/6/2023): fixed: was incorrectly deducing RD twice, instead of num-RD-SQL\n",
    "\n",
    "# Mean of mean correctness for each mode Plus 95% CI\n",
    "modes_diff = ['RD', 'SQL', 'diff']\n",
    "mean_correct = {}\n",
    "ci = {}\n",
    "ci_delta = {}\n",
    "for mode in modes_diff:\n",
    "    mean_correct[mode] = np.mean(dfq4b[mode])\n",
    "    ci[mode] = scipybootstrap((dfq4b[mode],), statistic=np.mean, n_resamples=BOOTSTRAPSAMPLES, confidence_level=BOOTSTRAPCONFIDENCE, method='percentile', axis=0).confidence_interval        #convert array to sequence\n",
    "    ci_delta[mode] = [mean_correct[mode] - ci[mode].low, ci[mode].high - mean_correct[mode]]\n",
    "\n",
    "print(f\"mean RD correct = {mean_correct['RD']:.3f}, 95% CI [{ci['RD'].low:.3f}, {ci['RD'].high:.3f}]\")\n",
    "print(f\"mean SQL correct = {mean_correct['SQL']:.3f}, 95% CI [{ci['SQL'].low:.3f}, {ci['SQL'].high:.3f}]\")\n",
    "print(f\"mean difference in correctness = {mean_correct['diff']:.3f}, 95% CI [{ci['diff'].low:.3f}, {ci['diff'].high:.3f}]\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2506325",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Removing 'diff' from variables\n",
    "dfq4c=dfq4b.copy()\n",
    "dfq4c.drop('diff', inplace=True, axis=1)\n",
    "modes = ['RD', 'SQL']\n",
    "\n",
    "# Define pre-settings\n",
    "figwidth = 9.7\n",
    "figheight = 6\n",
    "xlab_size = 20\n",
    "ylab_size = 20\n",
    "figfont_size = 24\n",
    "\n",
    "# Define consistent color maps\n",
    "my_cmap_sns_light = [(0.9921568627450981, 0.8156862745098039, 0.6352941176470588), (0.7764705882352941, 0.8588235294117647, 0.9372549019607843)]        # light blue, light orange\n",
    "my_cmap_sns_dark = [(0.9019607843137255, 0.3333333333333333, 0.050980392156862744), (0.19215686274509805, 0.5098039215686274, 0.7411764705882353)]      # dark blue, dark orange\n",
    "my_cmap_dark = sns.color_palette(my_cmap_sns_dark, as_cmap=True)\n",
    "my_cmap_light = sns.color_palette(my_cmap_sns_light, as_cmap=True)\n",
    "\n",
    "# Create empty figure and plot the individual datapoints\n",
    "fig, ax = plt.subplots(figsize=(figwidth,figheight))\n",
    "\n",
    "# 1. Violinplots\n",
    "axsns = sns.violinplot(x='mean', y='mode', data=dfq4a,\n",
    "                       hue=True, hue_order=[False, True], split=True,   # half violinplots https://stackoverflow.com/questions/53872439/half-not-split-violin-plots-in-seaborn\n",
    "                       inner='quartile',\n",
    "                       cut=0,               # 0 means ending sharp at end points\n",
    "                       width=.6,\n",
    "                       orient = 'h',\n",
    "                       zorder=20,)\n",
    "\n",
    "# change the medium default line to full (https://stackoverflow.com/questions/60638344/quartiles-line-properties-in-seaborn-violinplot)\n",
    "for l in axsns.lines[1::3]:\n",
    "    l.set_linestyle('-')\n",
    "    l.set_linewidth(1.2)\n",
    "    l.set_color('black')\n",
    "    l.set_alpha(0.8)\n",
    "for i in [0, 2, 3, 5]:         # remove the 25% and 75% quartiles\n",
    "    l = axsns.lines[i]\n",
    "    l.set_linestyle('-')\n",
    "    l.set_linewidth(0)\n",
    "    l.set_color('red')\n",
    "    l.set_alpha(0.8)\n",
    "\n",
    "# Apply colorscheme to violinplots https://stackoverflow.com/questions/70442958/seaborn-how-to-apply-custom-color-to-each-seaborn-violinplot\n",
    "from matplotlib.collections import PolyCollection\n",
    "for ind, violin in enumerate(axsns.findobj(PolyCollection)):\n",
    "    violin.set_facecolor(my_cmap_light[ind])\n",
    "# plt.setp(ax.collections, alpha=.999)  # semi-transparent (https://stackoverflow.com/questions/62597959/seaborn-violinplot-transparency)\n",
    "\n",
    "\n",
    "# 2. Plot individual points [new dot plot]\n",
    "y_tilt = -0.5       # how far below each violinplot\n",
    "\n",
    "def dotplot(input_x, y0, delta, **args):\n",
    "    unique_values, counts = np.unique(input_x, return_counts=True)  # Count how many times does each value occur\n",
    "\n",
    "    # Convert 1D input into 2D array\n",
    "    scatter_x = [] # x values\n",
    "    scatter_y = [] # corresponding y values\n",
    "    for idx, value in enumerate(unique_values):\n",
    "        for counter in range(0, counts[idx]):\n",
    "            scatter_x.append(value)\n",
    "            scatter_y.append(y0+counter*delta)\n",
    "    plt.scatter(scatter_x, scatter_y, **args)\n",
    "\n",
    "for i, col in enumerate(dfq4c):\n",
    "    dotplot(input_x=dfq4c[col], y0=y_tilt + i, delta=0.03,      # y-axis tilt change with each column\n",
    "            marker='^',\n",
    "            alpha=1,\n",
    "            zorder=20,      # higher means more visible\n",
    "            color=my_cmap_dark[i],\n",
    "            s=150,\n",
    "            linewidth=0,)\n",
    "\n",
    "\n",
    "# 4. Plot red line connecting means\n",
    "ax.plot(np.mean(dfq4c, axis=0), [0, 1], color ='red', linewidth = 2, linestyle ='-',\n",
    "        alpha = .3,\n",
    "        zorder=4,\n",
    "        )\n",
    "\n",
    "# 5. CI Errorbars\n",
    "for i, mode in enumerate(modes):\n",
    "    plt.errorbar(mean_correct[mode], i, xerr=np.array([[ci_delta[mode][0], ci_delta[mode][1]]]).T,\n",
    "                 fmt='o', markersize=10,\n",
    "                 lw = 5, alpha=1,\n",
    "                 zorder=100,        # higher means more visible\n",
    "                 capsize = 0,\n",
    "                 # capthick = 4,\n",
    "                 capthick = 0,\n",
    "                 # color = 'black',\n",
    "                 color=my_cmap_dark[i],\n",
    "                 )   # my_cmap[1])\n",
    "\n",
    "    # ax.text(median_time[column], y_tilt_mode[i]+y_tilt_section[j] + 0.18, f'{median_time[column]:.1f}',\n",
    "    #         horizontalalignment='center', color = my_cmap_sns_dark[i], fontsize=20)\n",
    "    ax.text(mean_correct[mode],\n",
    "            i-0.2,\n",
    "            # f'{mean_correct[mode]:.2f}', horizontalalignment='center',\n",
    "            f'{100*mean_correct[mode]:.0f}%', horizontalalignment='center',\n",
    "            # color='black',\n",
    "            color=my_cmap_dark[i],\n",
    "            fontsize=figfont_size)\n",
    "    # ax.text(ci[mode].low, i+0.1, f'{ci[mode].low:.2f}', horizontalalignment='center', color='black', fontsize=20)\n",
    "    # ax.text(ci[mode].high, i+0.1, f'{ci[mode].high:.2f}', horizontalalignment='center', color='black', fontsize=20)\n",
    "\n",
    "\n",
    "#Additional settings\n",
    "# ax.set_yticklabels(modes, size= ylab_size)\n",
    "# ax.set_xlim(0.7499, 1.0003)\n",
    "# ax.set_xlim(0.74, 1.01)\n",
    "ax.set_xlim(0.1, 1.01)\n",
    "if VARIANT == 1:\n",
    "    ax.set_xticks(np.linspace(0.1, 1, num=10))\n",
    "if VARIANT == 3:\n",
    "    ax.set_xlim(0.8, 1.01)\n",
    "if VARIANT == 4:\n",
    "    ax.set_xlim(0.4, 1.01)\n",
    "ax.set_ylim(-0.6, 1.5)\n",
    "ax.set_xlabel('Mean accuracy per worker', size = xlab_size)\n",
    "ax.set_ylabel(None)\n",
    "ax.set_yticklabels(['RD', 'SQL'])\n",
    "ax.legend_.remove()\n",
    "plt.grid(axis = 'x', linewidth = 0.5, color = 'lightgray')\n",
    "sns.despine()               # remove bounding box\n",
    "\n",
    "# import matplotlib.ticker as mtick\n",
    "ax.xaxis.set_major_formatter(mtick.PercentFormatter(1.0))           # show in percentage\n",
    "\n",
    "if savefig:\n",
    "    plt.savefig(fig_dir + f'/q4_figure_variant{VARIANT}.pdf', bbox_inches='tight')\n",
    "    plt.savefig(fig_dir + f'/q4_figure_variant{VARIANT}.svg', bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e212f759",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "# Question 4. Figure 4b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc15e9e6",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Define figure settings\n",
    "figwidth = 10\n",
    "figheight = 3\n",
    "xlab_size = 20\n",
    "ylab_size = 20\n",
    "figfont_size = 24\n",
    "\n",
    "# Define consistent color maps\n",
    "my_cmap_sns_dark = [(0.8392156862745098, 0.15294117647058825, 0.1568627450980392)]\n",
    "my_cmap_sns_light = [(0.984313725490196, 0.6039215686274509, 0.6)]\n",
    "my_cmap_dark = sns.color_palette(my_cmap_sns_dark, as_cmap=True)\n",
    "my_cmap_light = sns.color_palette(my_cmap_sns_light, as_cmap=True)\n",
    "\n",
    "# Create data frame for split violinplot\n",
    "sample = np.array(dfq4b['diff'])                                                     # extract the sample and then create the boostrapped medians\n",
    "data_difference = dfq4b['diff']\n",
    "dfvp = pd.DataFrame()\n",
    "dfvp[\"values\"] = sample\n",
    "dfvp[\"all\"] = \"\"                                        # attribute that is shared by all entries\n",
    "\n",
    "# Create empty figure and plot the individual datapoints\n",
    "fig, ax = plt.subplots(figsize=(figwidth,figheight))\n",
    "\n",
    "\n",
    "# 1. Violinplot\n",
    "axsns = sns.violinplot(x='values', y='all',    # y='all' just need to group both types together\n",
    "                       data=dfvp,\n",
    "                       hue = True, hue_order = [False, True],\n",
    "                       split = True, inner = 'quartile',\n",
    "                       cut=0,                  # 0 means ending sharp at end points\n",
    "                       width=.6, scale = 'width',\n",
    "                       # dodge = False,        # When using ``hue`` nesting, setting this to ``True`` will separate the strips for different hue levels along the categorical axis.\n",
    "                       orient = 'h',\n",
    "                       color=my_cmap_light[0],\n",
    "                       zorder = 3)\n",
    "\n",
    "# change the medium default linke to full\n",
    "for l in axsns.lines[1::3]:\n",
    "    l.set_linestyle('-')\n",
    "    l.set_linewidth(1.5)\n",
    "    l.set_color('black')\n",
    "    l.set_alpha(0.8)\n",
    "\n",
    "\n",
    "# 2. Plot individual points [new dot plot]\n",
    "y_tilt = -0.3                                      # Set some delta for the points below the violinplot\n",
    "\n",
    "def dotplot(input_x, y0, delta, **args):\n",
    "    unique_values, counts = np.unique(input_x, return_counts=True)  # Count how many times does each value occur\n",
    "\n",
    "    # Convert 1D input into 2D array\n",
    "    scatter_x = [] # x values\n",
    "    scatter_y = [] # corresponding y values\n",
    "    for idx, value in enumerate(unique_values):\n",
    "        for counter in range(0, counts[idx]):\n",
    "            scatter_x.append(value)\n",
    "            scatter_y.append(y0+counter*delta)\n",
    "    plt.scatter(scatter_x, scatter_y, **args)\n",
    "\n",
    "dotplot(input_x=data_difference, y0=y_tilt, delta=0.02,\n",
    "        marker='^',\n",
    "        alpha=1,\n",
    "        zorder=20,      # higher means more visible\n",
    "        color=my_cmap_dark[0],\n",
    "        s=150,\n",
    "        linewidth=0,)\n",
    "\n",
    "\n",
    "# 3. CI Errorbars & show numbers\n",
    "# axeb = plt.errorbar(median_ratio, 0, xerr=np.array([[ci_ratio_delta[0], ci_ratio_delta[1]]]).T,\n",
    "axeb = plt.errorbar(mean_correct['diff'], 0, xerr=np.array([[ci_delta['diff'][0], ci_delta['diff'][1]]]).T,\n",
    "                    fmt='o',\n",
    "                    markersize=10, alpha=1,\n",
    "                    # lw = 3,\n",
    "                    lw = 5,\n",
    "                    zorder=100,      # higher means more visible\n",
    "                    capsize = 0,     # 10\n",
    "                    # capthick = 4,\n",
    "                    capthick = 0,\n",
    "                    # color = 'black',\n",
    "                    color = my_cmap_dark[0],\n",
    "                    )\n",
    "\n",
    "\n",
    "\n",
    "# 4. vertical bar for x-axis = 1\n",
    "plt.plot([0, 0], [-10, 10], color = 'black', zorder = 0, linewidth=2)\n",
    "\n",
    "\n",
    "meandiff = np.mean(sample)           # rename\n",
    "# ax.text(meandiff, -0.12, f'{meandiff:.2f}', horizontalalignment='center',\n",
    "ax.text(meandiff, -0.12, f'{100*meandiff:.0f}%', horizontalalignment='center',\n",
    "        # color='black',\n",
    "        color = my_cmap_dark[0],\n",
    "        fontsize=figfont_size)\n",
    "# ax.text(ci_ratio.low, 0.04, f'{100*ci_ratio.low:.1f}%', horizontalalignment='center', color='black', fontsize=20)\n",
    "# ax.text(ci_ratio.high, 0.04, f'{100*ci_ratio.high:.1f}%', horizontalalignment='center', color='black', fontsize=20)\n",
    "\n",
    "\n",
    "\n",
    "# Additional settings\n",
    "ax.set_ylim(-0.4, 0.35)\n",
    "ax.set_ylabel(None)         # remove the 'all'\n",
    "\n",
    "ax.set_xticks(np.linspace(-1, 1, num=11))\n",
    "# ax.set_xticks(np.linspace(-1, 1, num=21), minor=True)\n",
    "ax.set_xlim(-0.201, 0.901)\n",
    "\n",
    "if VARIANT == 3:\n",
    "    ax.set_xticks(np.linspace(-0.2, 0.2, num=9))\n",
    "    ax.set_xlim(-0.151, 0.201)\n",
    "if VARIANT == 4:\n",
    "    ax.set_xlim(-0.201, 0.601)\n",
    "ax.set_xlabel('Difference in accuracy per worker (RD - SQL)', size = xlab_size)\n",
    "\n",
    "sns.despine(trim=False, left=True)               # remove bounding box\n",
    "plt.grid(axis = 'x', linewidth = 0.5, color = 'lightgray', which='both')\n",
    "ax.legend_.remove()\n",
    "ax.xaxis.set_major_formatter(mtick.PercentFormatter(1.0, decimals=0), )           # show in percentage\n",
    "\n",
    "if savefig:\n",
    "    plt.savefig(fig_dir + f'/q4_figure2_variant{VARIANT}.pdf', bbox_inches='tight')\n",
    "    plt.savefig(fig_dir + f'/q4_figure2_variant{VARIANT}.svg', bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5489ff85",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "# Print user feedback\n",
    "\n",
    "Prints all comments received from participants who passed the requirements (0.5 correctness)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9864109",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feedback = dfresults.loc[dfresults.worker_id.isin(dftemp.index),'feedback']\n",
    "for i, text in enumerate(feedback):\n",
    "    print(f'{i}: \"{text}\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6e5b507",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc467f89",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# end"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}