process_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "import re\n",
    "import datetime\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle as pkl\n",
    "\n",
    "from itertools import permutations\n",
    "from matplotlib import pyplot as plt\n",
    "from unidecode import unidecode\n",
    "from google_trans_new import google_translator  \n",
    "\n",
    "# Setup Google translator\n",
    "translator = google_translator()\n",
    "\n",
    "# Configuring matplotlib to output opaque images to avoid issues with Jupyter's dark mode\n",
    "plt.rcParams.update({\n",
    "    \"axes.facecolor\": \"white\",\n",
    "    \"figure.facecolor\": \"white\",\n",
    "})\n",
    "\n",
    "# Configuration for parsing time data\n",
    "selects = {\n",
    "    \"times\": [\"times\"],\n",
    "    \"counts\": [\"counts\"],\n",
    "    \"firsts\": [\"firsts\"],\n",
    "    \"loc_mean\": [\"loc_means_stds\", \"mean\"],\n",
    "    \"loc_std\": [\"loc_means_stds\", \"std\"],\n",
    "    \"len_mean\": [\"elem_means_stds\", \"mean\"],\n",
    "    \"len_std\": [\"elem_means_stds\", \"std\"]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def json_data(path, indices, verbose=False):\n",
    "    dat = []\n",
    "\n",
    "    # Extract data from json files\n",
    "    for dn in os.listdir(os.path.join(*path)):\n",
    "        fns = os.listdir(os.path.join(*path, dn))\n",
    "        \n",
    "        if verbose:\n",
    "            print(dn, \"\\t\", len(fns) // 4)\n",
    "\n",
    "        for fn in fns:\n",
    "            if fn.endswith(\"json\"):\n",
    "                with open(os.path.join(*path, dn, fn), \"r\") as f:\n",
    "                    dat.append(json.load(f))\n",
    "                    \n",
    "    dicts = []\n",
    "    keys = set()\n",
    "\n",
    "    # Reshaping json data\n",
    "    for v in dat:\n",
    "        reformed_dict = {}\n",
    "        for outerKey, innerDict in v.items():\n",
    "            if outerKey == \"name\":\n",
    "                reformed_dict[(0, outerKey)] = innerDict\n",
    "                continue\n",
    "            elif outerKey == \"feedback\":\n",
    "                outerKey = \"16\"\n",
    "\n",
    "            for innerKey, values in innerDict.items():\n",
    "                reformed_dict[(int(outerKey), innerKey)] = values\n",
    "        \n",
    "        # Store dict and gather all possible column names\n",
    "        dicts.append(reformed_dict)\n",
    "        keys |= set(reformed_dict.keys())                          \n",
    "        \n",
    "    df = pd.DataFrame(dicts, columns=keys)              # Create Dataframe from dicts\n",
    "    df = df.sort_index(axis=1, level=0)                 # Sort columns\n",
    "    df[(0, \"name\")] = df[(0, \"name\")].map(indices)      # Replace username with custom index values\n",
    "    df = df.set_index((0, \"name\"))                      # Set it as index\n",
    "    df = df.sort_index()                                # Sort index\n",
    "    df = df.applymap(lambda e : None if e == [] else e) # Remove empty arrays\n",
    "    df = df.dropna(axis=1, how='all', inplace=False)    # Drop useless columns\n",
    "    df.index.name = None                                # Remove index name\n",
    "        \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define paths\n",
    "data_path = [\"data\"]\n",
    "json_path = data_path + [\"raw\"]\n",
    "log_path = data_path + [\"parsed\"]\n",
    "\n",
    "# Define steps of sim interactions\n",
    "sim_steps = [2, 3, 14]\n",
    "\n",
    "# Read session data\n",
    "sessions = pd.read_csv(os.path.join(*data_path, \"schedule.csv\"))[[\"Session\", \"Day\", \"Study Level\", \"Field of Study\", \"Language\", \"Year\", \"Sim Version\"]]\n",
    "sessions[\"Session\"] = sessions[\"Session\"].map(lambda s : s.split(\" \")[-1]).astype(int)\n",
    "sessions = sessions.set_index(\"Session\")\n",
    "\n",
    "# Read DB data\n",
    "df = pd.read_csv(os.path.join(*data_path, \"users.csv\"))\n",
    "\n",
    "# Generate group-user mapping\n",
    "groups = {}\n",
    "\n",
    "for dn in os.listdir(os.path.join(*json_path)):\n",
    "    idx = int(dn.split(\" \")[1])\n",
    "    \n",
    "    for user in set([fn.split(\"-\")[0] for fn in os.listdir(os.path.join(*json_path, dn))]):\n",
    "        groups[user] = idx\n",
    "\n",
    "# Get session IDs\n",
    "df[\"session\"] = df[\"username\"].map(groups)\n",
    "\n",
    "# Drop useless data\n",
    "df = df.dropna().reset_index().drop([\"id\", \"index\", \"test\", \"attempt\", \"created_at\"], axis=1)\n",
    "\n",
    "temp_df = df.copy()\n",
    "\n",
    "# Filter out users that did not consent to data usage\n",
    "# NOTE : This should not change anything since the data is supposed to be pre-filtered but is there for safety reasons\n",
    "no_consent = df[df[\"consent\"] == 0]\n",
    "\n",
    "if len(no_consent):\n",
    "    df = df[df[\"consent\"] == 1]\n",
    "    df = df.reset_index(drop=True)\n",
    "    \n",
    "    print(\"The following users were filtered out for lack of consent:\")\n",
    "    print(no_consent[[\"username\", \"session\"]])\n",
    "\n",
    "# Map session info\n",
    "df[\"session\"] = df[\"session\"].astype(int)\n",
    "df[\"date\"] =    df[\"session\"].map(sessions[\"Day\"].to_dict())\n",
    "df[\"year\"] =    df[\"session\"].map(sessions[\"Year\"].to_dict())\n",
    "df[\"level\"] =   df[\"session\"].map(sessions[\"Study Level\"].to_dict())\n",
    "df[\"field\"] =   df[\"session\"].map(sessions[\"Field of Study\"].to_dict())\n",
    "df[\"version\"] = df[\"session\"].map(sessions[\"Sim Version\"].to_dict())\n",
    "df[\"lang\"] =    df[\"session\"].map(sessions[\"Language\"].to_dict())\n",
    "\n",
    "# Convert date strings to datetime objects\n",
    "df[\"date\"] = pd.to_datetime(df[\"date\"])\n",
    "\n",
    "# Convert to 2-level index\n",
    "df = pd.concat({'Base': df}, axis=1)\n",
    "\n",
    "# Join with JSON data, ignoring users with no JSON data\n",
    "indices = {v: k for (k, v) in df[(\"Base\", \"username\")].to_dict().items()}\n",
    "df = df.join(json_data(json_path, indices), how=\"right\")\n",
    "\n",
    "# Split versions\n",
    "df_v1 = df[df[(\"Base\", \"version\")] == 1]\n",
    "df_v23 = df[df[(\"Base\", \"version\")] > 1]\n",
    "\n",
    "# Free space for column shift\n",
    "df_v1 = df_v1.drop(range(9, 16), axis=1, level=0)\n",
    "\n",
    "# Shift old question logs to indices of the newer version. Correspondences are the following:\n",
    "# Version 1  :                 0 - 1 - 2 - 3 - 4 - 5 - 6 - 7 -   - 8 \n",
    "# Version 2+ : 0 - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 -10 -11 -12 -13 -14 -15 -16 -17\n",
    "for k0, k1 in df_v1.columns[::-1]:    \n",
    "    if type(k0) == int and k0 < 9:\n",
    "        k = k0 + 1 if k0 == 8 else k0\n",
    "        k += 4\n",
    "        df_v1[(k, k1)] = df_v1[(k0, k1)]\n",
    "        df_v1.drop((k0, k1), axis=1, inplace=True)\n",
    "        \n",
    "# Shift old progress variables to indices of the newer version. Correspondences are the following:\n",
    "# Version 1  : 0 - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 -10 -11 -   -12 -13 -   -   -14\n",
    "# Version 2+ : 0 - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 -10 -11 -12 -13 -14 -15 -16 -17\n",
    "prog_v1 = df_v1[\"Base\"][\"progress\"].copy()\n",
    "prog_v1[prog_v1 > 11] += 1\n",
    "prog_v1[prog_v1 > 14] += 2\n",
    "df_v1[(\"Base\", \"progress\")] = prog_v1\n",
    "\n",
    "# Merge back the two halves, this triggers a warning that can be ignored\n",
    "df = pd.concat([df_v1, df_v23], axis=0)\n",
    "\n",
    "# Rename \"Base\" to 0 for column sorting purposes, now that column 0 is gone\n",
    "for k in df[\"Base\"].columns:\n",
    "    df[(0, k)] = df[(\"Base\", k)]\n",
    "    df.drop((\"Base\", k), axis=1, inplace=True)\n",
    "\n",
    "# Delete empty strings and remove empty columns\n",
    "df = df.replace(\"\", np.nan)\n",
    "df = df.dropna(axis=1, how=\"all\")\n",
    "\n",
    "# Access info about the minor status of the participants\n",
    "with open(os.path.join(*data_path, \"prefills.json\"), \"r\") as f:\n",
    "    prefills = json.load(f)\n",
    "\n",
    "# Filter and order the info like the main index then add it to the dataframe\n",
    "prefills = pd.concat([pd.Series(data=k, index=v) for k, v in prefills.items()]) \\\n",
    "                .reindex(index=df[(0, \"username\")]) \\\n",
    "                .reset_index(drop=True)\n",
    "df[(0, \"minor\")] = prefills.notnull().astype('bool')\n",
    "\n",
    "# Create a validity column, it is true when the participant is a minor and the data has been acquired less than 31 days ago, meaning the data should not be used yet\n",
    "df[(0, \"invalid\")] = (df[0][\"date\"] > datetime.datetime.now() - datetime.timedelta(31)) & df[0][\"minor\"]\n",
    "\n",
    "# Reunite all ranking data of step 4\n",
    "ans_r = df[(4, \"ranks\")].apply(lambda l: list(map(int, l)) if type(l) == list else np.NaN)\n",
    "ans_c = df[(4, \"choices\")].apply(lambda l: [ord(elem.split(\" \")[1]) - 65 for elem in l] if type(l) == list else np.NaN)\n",
    "df[(4, \"ranks\")] = pd.DataFrame(ans_r.fillna(ans_c))\n",
    "df = df.drop((4, \"choices\"), axis=1)\n",
    "\n",
    "# Load answer correction data\n",
    "with open(os.path.join(*data_path, \"ans_map.json\"), \"r\") as f:\n",
    "    ans_map = json.load(f)\n",
    "    \n",
    "# Define correct answer margin\n",
    "margin = 0.05\n",
    "\n",
    "# Process answer data\n",
    "for c in [(k1, k2) for (k1, k2) in df.columns]:\n",
    "    idx1 = str(c[0])\n",
    "    idx2 = str(c[1])\n",
    "    \n",
    "    # Text cleanup\n",
    "    if idx2 == \"text\":\n",
    "        df[c] = df[c].apply(lambda e: e if type(e) == float else unidecode(e))                        # Remove accentuation\n",
    "        df[c] = df[c].str.replace(r'([0-9]+),([0-9]+)', r'\\1.\\2', regex=True)                         # Replace commas with dots in decimal numbers\n",
    "        df[c] = df[c].str.replace(\"\\n\", \" \", regex=False)                                             # Remove newlines\n",
    "        df[c] = df[c].str.replace(\"\\r\", \" \", regex=False)                                             # Remove carriage returns\n",
    "        df[c] = df[c].str.replace(\" +\", \" \", regex=True)                                              # Remove double spaces\n",
    "        df[c] = df[c].str.strip()                                                                     # Remove leading and trailing spaces\n",
    "    \n",
    "    # Search for answer columns\n",
    "    if ans_map.get(idx1) is not None:\n",
    "        q_type = ans_map[idx1][\"type\"]\n",
    "        \n",
    "        # Questions 5-8\n",
    "        if idx2 == \"text\" and q_type == \"num\":\n",
    "            # Set new columns\n",
    "            df[(c[0], \"ans\")] = np.nan\n",
    "            df[(c[0], \"rel\")] = np.nan\n",
    "\n",
    "            # Read answer data\n",
    "            init = ans_map[idx1][\"init\"]\n",
    "            corr = ans_map[idx1][\"correct\"]\n",
    "\n",
    "            # Process data\n",
    "            processed = df[c].replace(ans_map[idx1][\"map\"])\n",
    "            num_mask = processed.str.match(r'^-?\\d+(?:\\.\\d+)$').fillna(False)\n",
    "            temp = num_mask.index[num_mask]\n",
    "\n",
    "            # Generate quantitative and qualitative data columns\n",
    "            df.loc[temp, (c[0], \"ans\")] = processed[num_mask].apply(lambda n: float(n))\n",
    "            df.loc[temp, (c[0], \"rel\")] = df[(c[0], \"ans\")][num_mask].apply(lambda n: 0 if abs(n - corr) < margin\n",
    "                                                                                        else 1 if n > init\n",
    "                                                                                        else -1)\n",
    "        # Questions 9-10\n",
    "        elif idx2 == \"sliders\" and q_type == \"sliders\":\n",
    "            df[(c[0], \"score\")] = df[c[0]][idx2].apply(pd.Series) \\\n",
    "                                                .astype(float) \\\n",
    "                                                .multiply(ans_map[idx1][\"correct\"]) \\\n",
    "                                                .sum(axis=1)\n",
    "        # Questions 11 & 13\n",
    "        elif idx2 == \"text\" and \"text\" in q_type:\n",
    "            # Compile data from the answer mapping\n",
    "            temp = pd.json_normalize([{\"user\": k, \"text\": v[\"text\"], \"r\": v[\"res\"]} for k, v in ans_map[idx1][\"map\"].items()], sep=\"_\").set_index(\"user\")\n",
    "            \n",
    "            # Add index level and align index with existing dataset\n",
    "            temp = pd.concat({c[0]: temp}, axis=1) \\\n",
    "                     .reindex(index=df[(0, \"username\")]) \\\n",
    "                     .reset_index(drop=True)\n",
    "            \n",
    "            # Join to the existing dataset and drop duplicate text column\n",
    "            df = df.join(temp)\n",
    "            df = df.drop((c[0], \"text\"), axis=1)\n",
    "            \n",
    "            # Extra for question 11, compute the score\n",
    "            if q_type == \"text4\":\n",
    "                cols = [col for col in df.columns if \"r_\" in col[1]]\n",
    "                col = (c[0], \"r_formula\")\n",
    "                df[(c[0], \"score\")] = pd.concat([df[col], (df[[col for col in df.columns if col[0] == c[0] and \"r_\" in col[1]]].sum(axis=1) - df[col]) / 3], axis=1).max(axis=1)\n",
    "            \n",
    "        # Question 12\n",
    "        elif idx2 == \"choices\" and q_type == \"notes\":\n",
    "            # Define column labels\n",
    "            cols = [(c[0], \"n_\" + e) for e in [\"text\", \"formula\", \"table\", \"diagram\"]]\n",
    "            \n",
    "            # Turn arrays of answers into 4 columns while keeping NaNs for those who never answered the question\n",
    "            df[cols] = df[c[0]][idx2].apply(pd.Series) \\\n",
    "                                     .applymap(lambda e: e if e is None or type(e) == float \n",
    "                                                           else len(e) > 0)\n",
    "        # Question 15\n",
    "        elif idx2 == \"choices\" and q_type == \"select\":\n",
    "            # Turn column of lists into a DataFrame\n",
    "            temp = df[c[0]][idx2].apply(lambda e: np.nan if e == [\"\"] * 3 else e).apply(pd.Series)\n",
    "            \n",
    "            # Turn text answers into categories, \"I don't know\" answers are all at the end of the list and we do max(8, <value>) to map \"I don't know\" in all languages to the same value\n",
    "            ans_cols = [(c[0], \"a_\" + str(i)) for i in range(3)]\n",
    "            ans = temp.applymap(lambda e: np.nan if type(e) == float or len(e) == 0 \n",
    "                                                 else str(min(8, ans_map[idx1][\"choices\"].index(e))))\n",
    "            df[ans_cols] = ans\n",
    "            \n",
    "            # Count number of \"I don't know\" answers\n",
    "            df[(c[0], \"idk\")] = (ans == \"8\").sum(axis=1)\n",
    "\n",
    "            # Get answer correctness\n",
    "            corr_cols = [(c[0], \"c_\" + str(i)) for i in range(3)]\n",
    "            corr = temp.apply(lambda e: pd.Series([a == b for a, b in zip(e, ans_map[idx1][\"correct\"])]), axis=1)\n",
    "            df[corr_cols] = corr\n",
    "\n",
    "            # Get number of correct answers\n",
    "            df[(c[0], \"score\")] = corr.sum(axis=1)\n",
    "            \n",
    "            # Set whole section row to NaN if initial value was NaN\n",
    "            cols = [col for col in df.columns if col[0] == c[0] and col[1] != \"time\"]\n",
    "            df.loc[df[c[0]][\"a_0\"].isnull(), cols] = np.nan\n",
    "            \n",
    "# Shift DataFrame columns to make room for the sim steps\n",
    "shift_steps = [0] + sim_steps\n",
    "df.columns = df.columns.set_levels([x + shift_steps.index(([0] + [s for s in shift_steps if s <= x])[-1]) for x in df.columns.levels[0]], level=0)\n",
    "\n",
    "# Shift sim steps to fit in the gaps\n",
    "sim_steps = [a + b for a, b in zip(sim_steps, range(len(sim_steps)))]\n",
    "            \n",
    "# Add one column per sim interaction to insert sim start times in\n",
    "for step in sim_steps:\n",
    "    df[(step, \"time\")] = np.nan\n",
    "\n",
    "# Add sim start times to the DataFrame\n",
    "for fn in os.listdir(os.path.join(*log_path)):                                               # Cycle through each session data file\n",
    "    with open(os.path.join(*log_path, fn), \"rb\") as f:\n",
    "        while True:                                                                          # Run as long as there are Dataframes in the file\n",
    "            try:\n",
    "                un, dfs = pkl.load(f)                                                        # Extract one triplet of DataFrames\n",
    "                \n",
    "                try:\n",
    "                    for step, elem in zip(sim_steps, dfs.values()):                          # Execute for each sim step\n",
    "                        try:\n",
    "                            df.at[indices[un], (step, \"time\")] = elem[\"time\"][0] / 1000      # Write time in column\n",
    "                        except TypeError:                                                    # Exception: Sim was never started\n",
    "                            pass\n",
    "                except KeyError:                                                             # Exception: User is not in the DB\n",
    "                    pass\n",
    "            except EOFError:                                                                 # Exception: No more users to process for this session\n",
    "                break\n",
    "                \n",
    "# Sort columns\n",
    "df = df.sort_index(axis=1, level=0)\n",
    "\n",
    "# Extract time columns and compute total time\n",
    "time_cols = [col for col in df.columns if \"time\" in col[1]]\n",
    "df_time = df[time_cols]\n",
    "tot = df_time.max(axis=1) - df_time.min(axis=1)\n",
    "\n",
    "# Compute time differences\n",
    "df_time = df_time.diff(axis=1).shift(-1, axis=1).drop(df_time.columns[-1], axis=1)\n",
    "df_time.columns = df_time.columns.droplevel(1)\n",
    "df_time = pd.concat({\"time\": df_time}, axis=1)\n",
    "\n",
    "# Store total time, recorded time and lost time\n",
    "aggr = df_time.sum(axis=1)\n",
    "df[(\"time\", \"total\")] = tot\n",
    "df[(\"time\", \"aggregate\")] = aggr\n",
    "df[(\"time\", \"lost\")] = tot - aggr\n",
    "\n",
    "# Store sim-specific time aggregates, and set to NaN for version 1\n",
    "aggr_sim = df_time[\"time\"][sim_steps].sum(axis=1)\n",
    "aggr_sim[df[0][\"version\"] == 1] = np.nan\n",
    "df[(\"time\", \"sim_aggregate\")] = aggr_sim\n",
    "df[(\"time\", \"sim_percent\")] = aggr_sim / aggr\n",
    "\n",
    "# Join back on main DataFrame and drop timestamps\n",
    "df = df.drop(time_cols, axis=1)\n",
    "df = df.join(df_time)\n",
    "\n",
    "# Extract confidence columns and convert to float\n",
    "conf_cols = [col for col in df.columns if \"conf\" == col[1]]\n",
    "df_conf = df[conf_cols].applymap(float)\n",
    "\n",
    "# Change column names\n",
    "df_conf.columns = df_conf.columns.droplevel(1)\n",
    "df_conf = pd.concat({\"conf\": df_conf}, axis=1)\n",
    "\n",
    "# Compute average confidence, store back in main DataFrame and drop old confidence columns\n",
    "df_conf[(\"conf\", \"avg\")] = df_conf.mean(axis=1)\n",
    "df = df.drop(conf_cols, axis=1)\n",
    "df = df.join(df_conf)\n",
    "\n",
    "# Turn feedback values into numbers\n",
    "fdbk_cols = [col for col in df.columns if type(col[1]) == str and \"s_\" in col[1]]\n",
    "df[fdbk_cols] = df[fdbk_cols].applymap(float)\n",
    "\n",
    "# Extract time data\n",
    "elems = dict()\n",
    "\n",
    "with open(os.path.join(*data_path, \"time_data.pkl\"), \"rb\") as f:\n",
    "    try:\n",
    "        while True:\n",
    "            uid, idx, data = pkl.load(f)\n",
    "            \n",
    "            if elems.get(uid) is None:\n",
    "                elems[uid] = dict()\n",
    "                \n",
    "            elems[uid][idx] = data\n",
    "    except EOFError:\n",
    "        pass\n",
    "    \n",
    "# Assemble it into a DataFrame\n",
    "for sim_idx in range(3):\n",
    "    time_data = []\n",
    "    \n",
    "    for k, v in selects.items():\n",
    "        # Select depth\n",
    "        if len(v) == 2:\n",
    "            temp_df = pd.DataFrame({uid: e.get(sim_idx)[v[0]][v[1]] for uid, e in elems.items() if e.get(sim_idx) is not None})\n",
    "        else:\n",
    "            temp_df = pd.DataFrame({uid: e.get(sim_idx)[v[0]] for uid, e in elems.items() if e.get(sim_idx) is not None})\n",
    "\n",
    "        # Transpose and add index level\n",
    "        temp_df = temp_df.T\n",
    "        temp_df = pd.concat({k + \"_\" + str(sim_idx): temp_df}, axis=1)\n",
    "        time_data.append(temp_df)\n",
    "\n",
    "    # Concatenate DataFrames and match index with main dataframe, then join on it\n",
    "    time_df = pd.concat(time_data, axis=1) \\\n",
    "                .reindex(index=df[(0, \"username\")]) \\\n",
    "                .reset_index(drop=True)\n",
    "    \n",
    "    df = df.join(time_df)\n",
    "    \n",
    "df = df[df[0][\"invalid\"] == False]\n",
    "\n",
    "# Export clean data\n",
    "with open(os.path.join(*data_path, \"post_test.pkl\"), \"wb\") as f:\n",
    "    pkl.dump(df, f)\n",
    "    \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns.levels[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[0].columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[19]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OLD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = [\"data\", \"raw\"]\n",
    "dat = dict()\n",
    "for dn in sorted(os.listdir(os.path.join(*path)), key=lambda n : int(n.split(\" \")[-1]))[:]:\n",
    "    keys = None\n",
    "    fns = os.listdir(os.path.join(*path, dn))\n",
    "    print(dn, \"\\t\", len(fns) // 4)\n",
    "    \n",
    "    for fn in fns:\n",
    "        if fn.endswith(\"json\"):\n",
    "            username = fn.split(\"-\")\n",
    "            with open(os.path.join(*path, dn, fn), \"r\") as f:\n",
    "                n_keys = json.load(f)\n",
    "                \n",
    "                try:\n",
    "                    dat[n_keys[\"name\"]] = n_keys\n",
    "                except:\n",
    "                    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"time\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/users.csv\")\n",
    "df[\"Session\"] = df[\"username\"].map(groups)\n",
    "df = df.dropna().reset_index().drop([\"id\", \"index\", \"test\", \"attempt\", \"created_at\"], axis=1)\n",
    "df = df[df[\"consent\"] == 1]\n",
    "df[\"Session\"] = df[\"Session\"].astype(int)\n",
    "\n",
    "uu = df\n",
    "\n",
    "#df[\"year\"] = df.rename(index=sessions.set_index('Session')['year']).index\n",
    "df[\"year\"] = df[\"Session\"].map(sessions[\"Year\"].to_dict())\n",
    "#df[\"field\"] = df[\"Session\"].map(sessions[\"Field\"].to_dict())\n",
    "#df = df[df[\"Session\"] > 2]\n",
    "\n",
    "# TEMPORARY\n",
    "df = df[df[\"progress\"] > 15]\n",
    "\n",
    "df = df.reset_index(drop=True)\n",
    "\n",
    "df[\"json\"] = df[\"username\"].apply(lambda n: dat.get(n))\n",
    "\n",
    "keys = ['1','2','3','5','6','7','8','9','10','11','12','13','14','15']\n",
    "\n",
    "#time_abs = [test_data[k][\"time\"] - test_data[\"1\"][\"time\"] for k in keys]\n",
    "#time_rel = [a - b for a, b in zip(time_abs[1:], time_abs[:-1])]\n",
    "\n",
    "df[\"time_abs\"] = df[\"json\"].apply(lambda d : [d[k][\"time\"] - d[\"1\"][\"time\"] for k in keys])\n",
    "df[\"time_rel\"] = df[\"time_abs\"].apply(lambda d : [a - b for a, b in zip(d[1:], d[:-1])])\n",
    "\n",
    "df[\"avg_time\"] = df[\"time_abs\"].apply(lambda d : d[-1] / len(d))\n",
    "df[\"time\"] = df[\"time_abs\"].apply(lambda d : d[-1])\n",
    "\n",
    "df[\"conf\"] = df[\"json\"].apply(lambda d : [d[k].get(\"conf\") for k in keys])\n",
    "\n",
    "df[\"avg_conf\"] = df[\"conf\"].apply(lambda d : sum([int(e) for e in d if e]) / 6)\n",
    "\n",
    "df[[\"fun\", \"hard\"]] = pd.json_normalize(df[\"json\"].apply(lambda d: d.get(\"feedback\", {})))[[\"s_entertain\", \"s_difficult\"]]\n",
    "df[\"fun\"] = pd.to_numeric(df[\"fun\"])\n",
    "df[\"hard\"] = pd.to_numeric(df[\"hard\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def proc_json(elem):\n",
    "    ret = dict()\n",
    "    \n",
    "    for k in elem.keys():\n",
    "        if k != \"name\":\n",
    "            for k2 in elem[k].keys():\n",
    "                if elem[k][k2] != []:\n",
    "                    ret[k + \"_\" + k2] = elem[k][k2]\n",
    "\n",
    "    return ret\n",
    "                \n",
    "proc_json(df[\"json\"][40])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_json = pd.json_normalize(df[\"json\"].apply(lambda elem : proc_json(elem)))\n",
    "df_extended = pd.concat([df.drop(\"json\", axis=1), df_json], axis=1)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"time\"][range(1,20)]\n",
    "\n",
    "fig = plt.figure(figsize=(16, 9))\n",
    "d_min = 0 - 0.5\n",
    "d_max = 12 + 0.5\n",
    "\n",
    "times = df[\"time\"][range(1,20)]\n",
    "means = times.mean()\n",
    "\n",
    "for i in times.columns:\n",
    "    ax = plt.scatter([i,] * len(times[i]), times[i], s=2)\n",
    "    ax = plt.scatter([i,], [means[i],], s=50, c=\"black\", marker=\",\")\n",
    "\n",
    "plt.xlim(d_min, d_max)\n",
    "plt.grid(None)\n",
    "        \n",
    "plt.xlabel(\"Step in the experiment\")\n",
    "plt.ylabel(\"Time spent (seconds)\")\n",
    "fig.suptitle(\"Time spent per step in the experiment by year\", fontsize=20)\n",
    "fig.savefig(\"plots/time_per_step.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "times = pd.DataFrame(df[\"time_rel\"].to_list())\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "a_heights, a_bins = np.histogram(times[0], bins=20)\n",
    "width = (a_bins[1] - a_bins[0])/30\n",
    "\n",
    "for i in range(len(times.iloc[0])):\n",
    "    b_heights, b_bins = np.histogram(times[i], bins=20)\n",
    "    ax.bar(b_bins[:-1] + i * width, b_heights, width=width)\n",
    "    \n",
    "b_heights"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(figsize=(16, 9))\n",
    "d_min = 0 - 0.5\n",
    "d_max = 12 + 0.5\n",
    "\n",
    "times = pd.DataFrame(df[\"time_rel\"].to_list())\n",
    "means = times.mean()\n",
    "\n",
    "for i in range(len(means)):\n",
    "    ax = plt.scatter([i,] * len(times[i]), times[i], s=2)\n",
    "    ax = plt.scatter([i,], [means[i],], s=50, c=\"black\", marker=\",\")\n",
    "\n",
    "plt.xlim(d_min, d_max)\n",
    "plt.grid(None)\n",
    "        \n",
    "plt.xlabel(\"Step in the experiment\")\n",
    "plt.ylabel(\"Time spent (seconds)\")\n",
    "fig.suptitle(\"Time spent per step in the experiment by year\", fontsize=20)\n",
    "fig.savefig(\"plots/time_per_step.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(3, 1, figsize=(16, 9))\n",
    "d_min = 0 - 0.5\n",
    "d_max = 12 + 0.5\n",
    "\n",
    "for j, (a, year) in enumerate(zip(ax, sorted(df[\"year\"].unique()))):\n",
    "    times = pd.DataFrame(df[df[\"year\"] == year][\"time_rel\"].to_list())\n",
    "    means = times.mean()\n",
    "\n",
    "    for i in range(len(means)):\n",
    "        a.scatter([i,] * len(times[i]), times[i], s=2)\n",
    "        a.scatter([i,], [means[i],], s=50, c=\"black\", marker=\",\")\n",
    "\n",
    "    a.set_xlim(d_min, d_max)\n",
    "    a.set_title(year)\n",
    "    a.grid(\"y\")\n",
    "    \n",
    "    if j < 2:\n",
    "        a.set_xticks([])\n",
    "        \n",
    "ax[2].set_xlabel(\"Step in the experiment\")\n",
    "ax[1].set_ylabel(\"Time spent (seconds)\")\n",
    "fig.suptitle(\"Time spent per step in the experiment by year\", fontsize=20)\n",
    "fig.savefig(\"plots/time_per_step_by_year.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(2, 2, figsize=(16, 9))\n",
    "d_min = 0 - 0.5\n",
    "d_max = 12 + 0.5\n",
    "\n",
    "for j, (a, gender) in enumerate(zip(ax.flatten(), sorted(df[\"gender\"].unique()))):\n",
    "    times = pd.DataFrame(df[df[\"gender\"] == gender][\"time_rel\"].to_list())\n",
    "    means = times.mean()\n",
    "\n",
    "    for i in range(len(means)):\n",
    "        a.scatter([i,] * len(times[i]), times[i], s=2)\n",
    "        a.scatter([i,], [means[i],], s=50, c=\"black\", marker=\",\")\n",
    "\n",
    "    a.set_xlim(d_min, d_max)\n",
    "    a.set_title([\"Male\", \"Female\", \"Other\", \"Unspecified\"][j])\n",
    "    a.grid(\"y\")\n",
    "    \n",
    "    if j < 2:\n",
    "        a.set_xticks([])\n",
    "\n",
    "    if j % 2 == 0:\n",
    "        a.set_ylabel(\"Time spent (seconds)\")\n",
    "\n",
    "    if j > 1:\n",
    "        a.set_xlabel(\"Step in the experiment\")\n",
    "\n",
    "fig.suptitle(\"Time spent per step in the experiment by gender\", fontsize=20)\n",
    "fig.savefig(\"plots/time_per_step_by_gender.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "confs = pd.DataFrame(df[\"conf\"].to_list())[[3, 4, 5, 6, 9, 13]].fillna(0).astype(int)\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "a_heights, a_bins = np.histogram(confs[3], bins=10)\n",
    "width = (a_bins[1] - a_bins[0])/10\n",
    "\n",
    "for i, cat in enumerate([3, 4, 5, 6, 9, 13]):\n",
    "    b_heights, b_bins = np.histogram(confs[cat], bins=a_bins)\n",
    "    ax.bar(b_bins[:-1] + i * width + 2.5, b_heights, width=width, label=\"Question \" + str(min(10, cat - 1)))\n",
    "    \n",
    "ax.legend()\n",
    "ax.set_title(\"Confidence per question\", fontsize=20)\n",
    "ax.set_xlabel(\"Confidence (%)\")\n",
    "ax.set_ylabel(\"Number of people\")\n",
    "fig.savefig(\"plots/conf_per_q.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(2, 2, figsize=(16, 9))\n",
    "    \n",
    "d_min, d_max = 0, 100\n",
    "binwidth = (d_max - d_min) / 10\n",
    "bins = np.arange(d_min, d_max + binwidth, binwidth)\n",
    "\n",
    "for i, (a, gender) in enumerate(zip(ax.flatten(), sorted(df[\"gender\"].unique()))):\n",
    "    confs = pd.DataFrame(df[df[\"gender\"] == gender][\"conf\"].to_list())[[3, 4, 5, 6, 9, 13]].fillna(0).astype(int)\n",
    "    \n",
    "    a_heights, a_bins = np.histogram(confs[3], bins=10)\n",
    "    width = (a_bins[1] - a_bins[0])/10\n",
    "    \n",
    "    for j, cat in enumerate([3, 4, 5, 6, 9, 13]):\n",
    "        b_heights, b_bins = np.histogram(confs[cat], bins=a_bins)\n",
    "        a.bar(b_bins[:-1] + j * width + 2.5, b_heights, width=width, label=\"Question \" + str(min(10, cat - 1)))\n",
    "\n",
    "    a.set_xlim(0, 100)\n",
    "    a.set_title([\"Male\", \"Female\", \"Other\", \"Unspecified\"][i])\n",
    "\n",
    "    if i % 2 == 0:\n",
    "        a.set_ylabel(\"Number of people\")\n",
    "\n",
    "    if i > 1:\n",
    "        a.set_xlabel(\"Confidence (%)\")\n",
    "        \n",
    "ax[0][1].legend()\n",
    "fig.suptitle(\"Confidence per question by gender\", fontsize=20)\n",
    "fig.savefig(\"plots/conf_per_q_by_gender.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(3, 1, figsize=(16, 9))\n",
    "    \n",
    "d_min, d_max = 0, 100\n",
    "binwidth = (d_max - d_min) / 10\n",
    "bins = np.arange(d_min, d_max + binwidth, binwidth)\n",
    "\n",
    "for i, (a, year) in enumerate(zip(ax, sorted(df[\"year\"].unique()))):\n",
    "    confs = pd.DataFrame(df[df[\"year\"] == year][\"conf\"].to_list())[[3, 4, 5, 6, 9, 13]].fillna(0).astype(int)\n",
    "    \n",
    "    a_heights, a_bins = np.histogram(confs[3], bins=10)\n",
    "    width = (a_bins[1] - a_bins[0])/10\n",
    "    \n",
    "    for j, cat in enumerate([3, 4, 5, 6, 9, 13]):\n",
    "        b_heights, b_bins = np.histogram(confs[cat], bins=a_bins)\n",
    "        a.bar(b_bins[:-1] + j * width + 2.5, b_heights, width=width, label=\"Question \" + str(min(10, cat - 1)))\n",
    "\n",
    "    a.set_xlim(0, 100)\n",
    "    a.set_title(year)\n",
    "    \n",
    "    if i < 2:\n",
    "        a.set_xticks([])\n",
    "        \n",
    "ax[2].set_xlabel(\"Confidence (%)\")\n",
    "ax[1].set_ylabel(\"Number of people\")\n",
    "ax[0].legend()\n",
    "fig.suptitle(\"Confidence per question by year\", fontsize=20)\n",
    "fig.savefig(\"plots/conf_per_q_by_year.png\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1 - intro  \n",
    "2 - sim 1  \n",
    "3 - sim 2  \n",
    "4 - Q ranking  \n",
    "5 - Q text  \n",
    "6 - Q text  \n",
    "7 - Q text  \n",
    "8 - Q text  \n",
    "9 - Q sliders  \n",
    "10 - Q sliders  \n",
    "11 - Q text  \n",
    "12 - Q checkboxes  \n",
    "13 - Q text  \n",
    "14 - sim 3  \n",
    "15 - Q dropdown  \n",
    "16 - feedback  \n",
    "17 - end  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = df[\"time_abs\"]\n",
    "pd.DataFrame.from_dict(dict(zip(s.index, s.values))).transpose().mean().plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = df[\"time_rel\"]\n",
    "pd.DataFrame.from_dict(dict(zip(s.index, s.values))).transpose().mean().plot(kind=\"bar\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#UNUSED FOR NOW\n",
    "params = [\n",
    "    (\"Confidence\", \"avg_conf\", \"%\"),\n",
    "    (\"Enjoyment\", \"fun\", \"%\"),\n",
    "    (\"Difficulty\", \"hard\", \"%\"),\n",
    "    (\"Total time\", \"time\", \"seconds\")\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for name, ref, unit in params:\n",
    "    d_min, d_max = df[ref].min(), df[ref].max()\n",
    "    binwidth = (d_max - d_min) / 40\n",
    "    bins = np.arange(d_min, d_max + binwidth, binwidth)\n",
    "    \n",
    "    fig, ax = plt.subplots(3, 1, figsize=(12, 9))\n",
    "    df[ref].hist(by=df[\"year\"], ax=ax, bins=bins, width=binwidth / 1.5)\n",
    "\n",
    "    for i, a in enumerate(ax):\n",
    "        a.set_xlim(0, df[ref].max())\n",
    "        a.set_ylabel(\"Number of people\")\n",
    "        \n",
    "        if i < 2:\n",
    "            a.set_xticks([])\n",
    "\n",
    "    ax[2].set_xlabel(name + \" (\" + unit + \")\")\n",
    "    fig.suptitle(name + ', by year', fontsize=20)\n",
    "    fig.savefig(\"plots/\" + ref + \"_by_year.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for name, ref, unit in params:\n",
    "    d_min, d_max = df[ref].min(), df[ref].max()\n",
    "    binwidth = (d_max - d_min) / 20\n",
    "    bins = np.arange(d_min, d_max + binwidth, binwidth)\n",
    "    \n",
    "    fig, ax = plt.subplots(2, 2, figsize=(12, 9))\n",
    "    df[ref].hist(by=df[\"gender\"].apply(lambda d : [\"Male\", \"Female\", \"Other\", \"Unspecified\"][int(d)-1]), ax=ax, bins=bins, width=binwidth / 1.5)\n",
    "\n",
    "    for i, a in enumerate(ax.flatten()):\n",
    "        a.set_xlim(0, df[ref].max())\n",
    "        \n",
    "        if i % 2 == 0:\n",
    "            a.set_ylabel(\"Number of people\")\n",
    "\n",
    "        if i > 1:\n",
    "            a.set_xlabel(name + \" (\" + unit + \")\")\n",
    "\n",
    "    fig.suptitle(name + ', by gender', fontsize=20)\n",
    "    fig.savefig(\"plots/\" + ref + \"_by_gender.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bokeh import palettes\n",
    "from bokeh.io import output_file\n",
    "from bokeh.plotting import figure, save, reset_output\n",
    "from bokeh.models import ColumnDataSource, GroupFilter, CDSView, HoverTool"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = pd.DataFrame(df[\"json\"].apply(lambda d : d.get(\"4\")))\n",
    "len([y for y in x[\"json\"].to_list() if y is None])\n",
    "len(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uu[\"progress\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = pd.DataFrame(df[\"json\"].apply(lambda x : x.get(\"4\", None)).to_dict()).transpose()\n",
    "pd.Series(temp[temp[\"ranks\"].astype(str) == \"None\"].index).hist(bins=129)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "idx = 122\n",
    "print(df.iloc[idx][\"year\"])\n",
    "df.iloc[idx][\"json\"][\"feedback\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gy = pd.crosstab(df.gender,df.year)\n",
    "gy.index = [\"Male\", \"Female\", \"Other\", \"Unspecified\"]\n",
    "gy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.rcParams['axes.prop_cycle'].by_key()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "gender_labels = {\n",
    "    1 : \"Male\",\n",
    "    2 : \"Female\",\n",
    "    3 : \"Other\",\n",
    "    4 : \"Unspecified\"\n",
    "}\n",
    "\n",
    "years = [\"1st\", \"2nd\", \"3rd\"]\n",
    "cmap = plt.rcParams['axes.prop_cycle'].by_key()[\"color\"]\n",
    "\n",
    "group_width = 0.75\n",
    "bar_width = group_width / len(gender_labels)\n",
    "\n",
    "for i, c in enumerate(cmap[:4]):\n",
    "    heights = df[df[0][\"gender\"] == i + 1][0][\"year\"].value_counts()\n",
    "    bins = heights.index.map(years.index)\n",
    "    \n",
    "    ax.bar(bins + (i + 0.5) * bar_width - group_width / 2 + 1, heights, width=bar_width, label=gender_labels[i + 1])\n",
    "    \n",
    "    for x, y in zip(bins, heights):\n",
    "        ax.text(x + (i + 0.5) * bar_width - group_width / 2 + 1, y + 0.5, y, ha=\"center\", c=c, size=16)\n",
    "    \n",
    "plt.xticks(ticks=range(1, len(years) + 1), labels=years)\n",
    "ax.set_xlim(0.5, len(years) + 0.5)\n",
    "ax.set_xlabel(\"Year of Study\", size=16)\n",
    "ax.set_ylabel(\"Number of people\", size=16)\n",
    "ax.legend(prop={'size': 16})\n",
    "#fig.suptitle(\"Repartition of gender per study year\", fontsize=20)\n",
    "fig.savefig(\"plots/gender_by_year.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ans_r = df_extended[\"4_ranks\"].apply(lambda l : list(map(int, l)) if type(l) == list else np.NaN)\n",
    "#ans_c = df_extended[\"4_choices\"].apply(lambda l : [ord(elem.split(\" \")[1]) - 65 for elem in l] if type(l) == list else np.NaN)\n",
    "#ans = pd.DataFrame(ans_r.fillna(ans_c))\n",
    "\n",
    "ans = df[6][\"ranks\"]\n",
    "\n",
    "def make_autopct(values):\n",
    "    def my_autopct(pct):\n",
    "        total = sum(values)\n",
    "        val = int(round(pct * total / 100.0))\n",
    "        return '{p:.2f}%  \\n({v:d})'.format(p=pct,v=val)\n",
    "    return my_autopct\n",
    "\n",
    "fig, ax = plt.subplots(1, 3, figsize=(16, 6))\n",
    "\n",
    "for i, y in enumerate(years):\n",
    "    values = ans[df_extended[\"year\"] == y].isnull().value_counts()\n",
    "    \n",
    "    ax[i].pie(values,\n",
    "        autopct=make_autopct(values),\n",
    "        shadow=True,\n",
    "        startangle=90)\n",
    "\n",
    "    ax[i].set_title(y + \" year\")\n",
    "fig.legend([\"Collected\", \"Non collected\"], prop={'size': 16})\n",
    "#fig.suptitle(\"Success of the ranking collection\", fontsize=20)\n",
    "fig.savefig(\"plots/ranking_success.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k1, g1 in rank_choices.groupby(\"year\"):\n",
    "    print(k1, \"year\")\n",
    "    \n",
    "    for k2, g2 in g1.groupby([0, 1, 2, 3]):\n",
    "        print(\"\\t\", k2, len(g2), \"<-------\" if k2 == good_rank else \"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ordre largeurs 0 3 1 2 / 2 1 3 0\n",
    "ordre concentrations 2 1 0 3 / 3 0 1 2\n",
    "\n",
    "no order 8 permutations\n",
    "vert vert rouge rouge 4 permutations\n",
    "rouge rouge vert vert 2 0 1 3 / 0 2 3 1\n",
    "0 2 1 3\n",
    "bonne solution 2 0 3 1\n",
    "\n",
    "OTHER FACTORS  \n",
    "\n",
    "Concepts à comprendre :\n",
    "Couleur (ORDRES DE MAGNITUDE PLUS IMPORTANT)\n",
    "Largeur de bécher\n",
    "Concentration\n",
    "\n",
    "[2] = 4 / 3 * [0] >>>>>>>>>>>>>> [3] = 9 / 8 * [1]\n",
    " \n",
    " SEC = privilegier la concentration à la couleur, width not taken into account\n",
    "\n",
    "COULEUR\n",
    "- OK  \n",
    "    CONCENTRATION\n",
    "    - OK\n",
    "        (2, 0, 3, 1)  21\n",
    "    - INV\n",
    "        (0, 2, 1, 3)  13\n",
    "    - OTHER\n",
    "        (2, 0, 1, 3)  11  \n",
    "        (0, 2, 3, 1)  23\n",
    "    - SEC\n",
    "        (0, 3, 2, 1)   6\n",
    "- INV  \n",
    "    CONCENTRATION\n",
    "    - OK\n",
    "        (3, 1, 2, 0)  14\n",
    "    - INV\n",
    "        (1, 3, 0, 2)   1\n",
    "    - OTHER\n",
    "        (3, 1, 0, 2)   9  \n",
    "        (1, 3, 2, 0)   4\n",
    "    - SEC\n",
    "        (3, 0, 1, 2)  17\n",
    "- SEQ (looked at b * c only), similar to ok_inv et inv_ok\n",
    "    - OK\n",
    "        (3, 2, 1, 0)   5\n",
    "    - INV\n",
    "        (0, 1, 2, 3)   5\n",
    "- SHADE ONLY\n",
    "    - CONS\n",
    "        - OK\n",
    "            (2, 1, 0, 3)   2  \n",
    "        - INV\n",
    "            (1, 2, 3, 0)   1  \n",
    "            \n",
    "    - INCONS\n",
    "        - OK\n",
    "            (0, 3, 1, 2)   7  \n",
    "            (3, 0, 2, 1)   4  \n",
    "        - INV\n",
    "            (2, 1, 3, 0)   6  \n",
    "            (1, 2, 0, 3)   3  \n",
    "- OTHER  \n",
    "    (0, 1, 3, 2)       2  \n",
    "    (1, 0, 2, 3)       1  \n",
    "    (1, 0, 3, 2)       3  \n",
    "    (2, 3, 0, 1)       2  \n",
    "    (2, 3, 1, 0)       3  \n",
    "    (3, 2, 0, 1)       3  \n",
    "\n",
    "Tester des groupements en ignorant certains facteurs\n",
    "Il y a bcp de variables\n",
    "\n",
    "(2, 0, 1, 3) Mauvais calcul sur les verts\n",
    "(3, 1, 0, 2) Ordre couleur inversé, sombres avant\n",
    "(0, 2, 1, 3) Ordre couleur juste, sombres a l'exterieur au lieu de l'intérieur\n",
    "(3, 1, 2, 0) Erreur de mettre les verts devant les rouges, les clairs au milieu, exact inverse de la bonne reponse\n",
    "(3, 0, 1, 2) Erreur de favoriser l'opacité avant la couleur, mis les sombres d'abord puis les verts d'abord\n",
    "(0, 2, 3, 1) Erreur de mettre les sombres systematiquement avant, ordre couleurs juste\n",
    "(2, 0, 3, 1) Tout juste\n",
    "\n",
    "Comprendre que les rouges vont devant car beaucoup plus d'absorption\n",
    "Prendre en compte largeur et concentration\n",
    "\n",
    "Green laser (520 nm)\n",
    "\n",
    "2 5.0cm 400mM 2000 Light Red    13.4\n",
    "0 2.5cm 600mM 1500 Dark Red     5.4\n",
    "3 3.0cm 750mM 2250 Dark Green   \n",
    "1 4.0cm 500mM 2000 Light Green\n",
    "\n",
    "Check table by Jade\n",
    "\n",
    "Bon ranking vis a vis de si ils ont utilisé la transmittance ou pas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "reasonings = {\n",
    "    \"ok\" : {\n",
    "        \"ok\" : [(2, 0, 3, 1)],\n",
    "        \"inv\" : [(0, 2, 1, 3)],\n",
    "        \"sec\" : [(0, 3, 2, 1)],\n",
    "        \"other\" : [(2, 0, 1, 3), (0, 2, 3, 1)]\n",
    "    },\n",
    "    \"inv\" : {\n",
    "        \"ok\" : [(3, 1, 2, 0)],\n",
    "        \"inv\" : [(1, 3, 0, 2)],\n",
    "        \"sec\" : [(3, 0, 1, 2)],\n",
    "        \"other\" : [(3, 1, 0, 2), (1, 3, 2, 0)]\n",
    "    },\n",
    "    \"seq\" : {\n",
    "        \"ok\" : [(3, 2, 1, 0)],\n",
    "        \"inv\" : [(0, 1, 2, 3)]\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "outputs = dict()\n",
    "levels = [\"color\", \"concentration x width\"]\n",
    "covered = []\n",
    "final = dict()\n",
    "temp = {y: 0 for y in years}\n",
    "\n",
    "for k1, v1 in reasonings.items():\n",
    "    outputs[k1] = dict()\n",
    "    \n",
    "    for k2, v2 in reasonings[k1].items():\n",
    "        covered += v2\n",
    "        outputs[k1][k2] = temp.copy()\n",
    "        s = 0\n",
    "        \n",
    "        for v in v2:\n",
    "            rcs = rank_choices[(rank_choices[[0, 1, 2, 3]] == v).all(1)]\n",
    "            vcs = rcs.value_counts(\"year\")\n",
    "            for y in years:\n",
    "                outputs[k1][k2][y] += vcs.get(y, 0)\n",
    "            s += len(rcs)\n",
    "        outputs[k1][k2][\"all\"] = s\n",
    "        \n",
    "for k1, v1 in outputs.items():\n",
    "    for k2, v2 in outputs[k1].items():\n",
    "        final[k1 + \"_\" + k2] = v2\n",
    "            \n",
    "s = 0\n",
    "            \n",
    "others = [p for p in permutations(range(4)) if p not in covered]\n",
    "final[\"other\"] = temp.copy()\n",
    "        \n",
    "for v in others:\n",
    "    rcs = rank_choices[(rank_choices[[0, 1, 2, 3]] == v).all(1)]\n",
    "    vcs = rcs.value_counts(\"year\")\n",
    "    for y in years:\n",
    "        final[\"other\"][y] += vcs.get(y, 0)\n",
    "    s += len(rcs)\n",
    "final[\"other\"][\"all\"] = s\n",
    "\n",
    "final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sum([v[\"all\"] for k, v in final.items()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "pd.DataFrame(final).T.plot(kind=\"bar\", ax=ax)\n",
    "ax.set_xlabel(\"Reasoning\", size=16)\n",
    "ax.set_ylabel(\"Number of people\", size=16)\n",
    "ax.legend(prop={'size': 16})\n",
    "fig.suptitle(\"Repartition of reasonings per study year\", fontsize=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k1, g1 in rank_choices.groupby([0, 1, 2, 3]):\n",
    "    vcs = g1[\"year\"].value_counts()\n",
    "    print(k1, vcs.sum(), vcs.to_dict())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k1, g1 in rank_choices.groupby(\"year\"):\n",
    "    print(k1, \"year\")\n",
    "    \n",
    "    for k2, g2 in g1.groupby([0, 1, 2, 3]):\n",
    "        print(\"\\t\", k2, len(g2), \"<-------\" if k2 == good_rank else \"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NEED TO DO THE PLOT OF YEAR+GENDER BUT FOR ANSWER+YEAR INSTEAD\n",
    "gender => year\n",
    "year => answer\n",
    "\n",
    "# Prendre les gens qui ont pas fini\n",
    "\n",
    "Quantitatif\n",
    "Valeure exacte\n",
    "\n",
    "RQ\n",
    "\n",
    "\n",
    "Qualitatif\n",
    "compris que la mere est plus lourde mais a pas le nombre exact\n",
    "\n",
    "Qualitatif > Rough Quantitatif > Quantitatif\n",
    "\n",
    "3 niveaux de score : Qualitatif, rough quantitif, quantitatif\n",
    "\n",
    "Faire des hypotheses sur le ranking et voir si le post test confirme ces hypotheses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "valid_ans[\"4_ranks\"].explode()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rank_choices[[0, 1, 2, 3]].drop_duplicates().agg(lambda x : tuple(x), axis=1).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "\n",
    "gender_labels = {\n",
    "    1 : \"Male\",\n",
    "    2 : \"Female\",\n",
    "    3 : \"Other\",\n",
    "    4 : \"Unspecified\"\n",
    "}\n",
    "\n",
    "years = [\"1st\", \"2nd\", \"3rd\"]\n",
    "unique_combs = rank_choices[[0, 1, 2, 3]].drop_duplicates().agg(lambda x : tuple(x), axis=1).reset_index(drop=True)\n",
    "cmap = plt.rcParams['axes.prop_cycle'].by_key()[\"color\"]\n",
    "\n",
    "group_width = 0.75\n",
    "bar_width = group_width / len(gender_labels)\n",
    "\n",
    "for i, c in enumerate(cmap[:3]):\n",
    "    heights = rank_choices[rank_choices[\"year\"] == years[i]][[0, 1, 2, 3]].value_counts()\n",
    "    bins = pd.Int64Index([newmap[x] for x in list(heights.index)])\n",
    "    \n",
    "    ax.bar(bins + (i + 0.5) * bar_width - group_width / 2 + 1, heights, width=bar_width)#, label=gender_labels[i + 1])\n",
    "    \n",
    "    #for x, y in zip(bins, heights):\n",
    "    #    ax.text(x + (i + 0.5) * bar_width - group_width / 2 + 1, y + 0.5, y, ha=\"center\", c=c, size=16)\n",
    "    \n",
    "plt.xticks(ticks = range(len(newmap.keys())), labels=list(newmap.keys()), rotation=45)\n",
    "#ax.set_xlim(0.5, len(years) + 0.5)\n",
    "ax.set_xlabel(\"Year of Study\", size=16)\n",
    "ax.set_ylabel(\"Number of people\", size=16)\n",
    "ax.legend(prop={'size': 16})\n",
    "fig.suptitle(\"Repartition of gender per study year\", fontsize=20)\n",
    "fig.savefig(\"plots/gender_by_year.png\")"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "[newmap[x] for x in list(heights.index)]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "newmap = {v: k for k, v in dict(unique_combs).items()}"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "newmap.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[(df[\"time\"] < 0).sum(axis=1) > 0][[0, \"time\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18]].columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set default end time per session and check whether they stopped or couldnt finish"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "diffs = df_times.max(axis=1) - df_times.min(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "cmap = plt.rcParams['axes.prop_cycle'].by_key()[\"color\"]\n",
    "\n",
    "_, g_bins = np.histogram(diffs[df.index], bins=50)\n",
    "\n",
    "for i, y in enumerate(years):\n",
    "    sub = diffs[df[df[0][\"year\"] == y].index]\n",
    "    sizes, bins = np.histogram(sub, bins=g_bins)\n",
    "    \n",
    "    cnts = {x: 0 for x in bins}\n",
    "    \n",
    "    for s in sub:\n",
    "        low_bins = [b for b in bins[:-1] if b <= s]\n",
    "        last = low_bins[-1]\n",
    "        total = sizes[len(low_bins)-1]\n",
    "        odd = (len(low_bins) + total) % 2\n",
    "        \n",
    "        plt.plot(i + cnts[last] / 40 + odd/80 - total / 80, last, \"o\", c=cmap[i])\n",
    "        cnts[last] += 1\n",
    "        \n",
    "    #ax.bar(bins, sizes)\n",
    "    \n",
    "    #ax.scatter([i] * len(sub), sub)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Correct answers\n",
    "\n",
    "```\n",
    "4  - (2, 0, 3, 1)\n",
    "5  - 0.37 (1/2x container)\n",
    "6  - 1.59 (3x concentration)\n",
    "7  - 0.96 (1/2x container, 2x concentration)\n",
    "8  - 0.80 (4x container, 1/3x concentration)\n",
    "9  - \n",
    "    YES - 0.01 - Blue\n",
    "    OK  - 0.02 - Purple\n",
    "    OK  - 0.02 - Yellow\n",
    "    NO  - 0.20 - Red\n",
    "10 - \n",
    "    YES - 0.00 - Cobalt nitrate\n",
    "    OK  - 0.01 - Copper sulfate\n",
    "    OK  - 0.05 - Potassium permanganate\n",
    "    NO  - 0.48 - Potassium chromate\n",
    "11 - Relationship\n",
    "12 - Notes\n",
    "13 - Knowledge of Law\n",
    "15 - \n",
    "16 - Feedback\n",
    "17 - End (might have to discard)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k, v in ans_map[\"8\"].items():\n",
    "    if v == \"?\":\n",
    "        print(k)\n",
    "        print(translator.translate(k, lang_src=\"de\", lang_tgt='en'))\n",
    "        print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k, v in df[8][\"text\"].value_counts().to_dict().items():\n",
    "    print(\"\\\"\" + k + \"\\\": \\\"?\\\",\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CHECK WHY df[5][\"text\"] has less values "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "230 - df[0][\"progress\"].value_counts().sort_index().cumsum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove the username that said TEST everywhere"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "with open('ans_map.json', 'w') as f:\n",
    "    json.dump(ans_map, f, indent=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx, row in df.iterrows():\n",
    "    txt = row[11][\"text\"]\n",
    "    \n",
    "    if type(txt) == float:\n",
    "        trans = txt\n",
    "    else:\n",
    "        trans = translator.translate(txt, lang_src=\"de\", lang_tgt=\"en\")\n",
    "        \n",
    "    elem = {\n",
    "            \"de\": txt,\n",
    "            \"en\": trans\n",
    "        }\n",
    "        \n",
    "    ans_map[\"11\"][\"map\"][row[0][\"username\"]][\"text\"] = elem"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "E = molar extinction coefficient  \n",
    "C = substance concentration  \n",
    "D = layer thickness of the cuvette\n",
    "\n",
    "8ethqmkd  \n",
    "6tgyhcuh  \n",
    "w7asnymz  \n",
    "\n",
    "Noter -1 quand ils ont inversé la proportionnalité\n",
    "\n",
    "Calculer ratio utilisation transmittance/absorbance dans la sim  \n",
    "Graph qui map les stratégies et l'utilisation de la transmittance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "i = 0\n",
    "d = dict()\n",
    "\n",
    "for idx, row in df.iterrows():\n",
    "    \n",
    "    txt = row[13][\"text\"]\n",
    "    \n",
    "    if type(txt) == float:\n",
    "        trans = txt\n",
    "    else:\n",
    "        trans = translator.translate(txt, lang_src=\"de\", lang_tgt=\"en\")\n",
    "        \n",
    "    elem = {\n",
    "            \"text\": {\n",
    "                \"de\": txt,\n",
    "                \"en\": trans,\n",
    "            },\n",
    "            \"res\": None\n",
    "        }\n",
    "    \n",
    "    u = row[0][\"username\"]\n",
    "    \n",
    "    d[u] = elem\n",
    "        \n",
    "    #ans_map[\"13\"][\"map\"][row[0][\"username\"]][\"text\"] = elem"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Break time\n",
    "Break count\n",
    "Avg break time\n",
    "Active time\n",
    "Active count\n",
    "Avg active time\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NOTIFY IF THERE IS DATA FILES WITH NO MATCHING CODE\n",
    "\n",
    "Get latest users.csv and regenerate auxiliary dfs\n",
    "\n",
    "Also reorganize to have everything in one spot\n",
    "\n",
    "Generate plots in other DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in cat_15.value_counts().items():\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Starting points:\n",
    "```\n",
    "- 1 :    Info 1\n",
    "- 2 :    Sim 1\n",
    "- 3 :    Info 2\n",
    "- 4 :    Sim 2\n",
    "- 5 :    Question 1 (ranking, absorption)\n",
    "- 6 :    Question 2 (text, absorption)\n",
    "- 7 :    Question 3 (text, absorption)\n",
    "- 8 :    Question 4 (text, absorption)\n",
    "- 9 :    Question 5 (text, absorption)\n",
    "- 10 :   Question 6 (sliders, color)\n",
    "- 11 :   Question 7 (sliders, solution)\n",
    "- 12 :   Question 8 (text, proportionality)\n",
    "- 13 :   Question 9 (checkboxes & text, notes)\n",
    "- 14 :   Question 10 (text, previous knowledge)\n",
    "- 15 :   Info 3\n",
    "- 16 :   Sim 3\n",
    "- 17 :   Question 11 (dropdown, solution)\n",
    "- 18 :   Feedback\n",
    "- 19 :   Info End\n",
    "- 20 :   End\n",
    "```\n",
    "\n",
    "Time for step n = t_n - t_[n-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "step_labels = [\n",
    "    \"Info 1\",\n",
    "    \"Sim 1\",\n",
    "    \"Info 2\",\n",
    "    \"Sim 2\",\n",
    "    \"Question 1 (ranking, absorption)\",\n",
    "    \"Question 2 (text, absorption)\",\n",
    "    \"Question 3 (text, absorption)\",\n",
    "    \"Question 4 (text, absorption)\",\n",
    "    \"Question 5 (text, absorption)\",\n",
    "    \"Question 6 (sliders, color)\",\n",
    "    \"Question 7 (sliders, solution)\",\n",
    "    \"Question 8 (text, proportionality)\",\n",
    "    \"Question 9 (checkboxes & text, notes)\",\n",
    "    \"Question 10 (text, previous knowledge)\",\n",
    "    \"Info 3\",\n",
    "    \"Sim 3\",\n",
    "    \"Question 11 (dropdown, solution)\",\n",
    "    \"Feedback\",\n",
    "    \"Info End\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "end_cols = [col for col in df_time.columns if \"time\" == col[1]]\n",
    "df_ver = df_time[df[0][\"version\"] > 1]\n",
    "df_ver[end_cols].isna().idxmax(1).where(df_ver.isna().any(1)).value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "completed_count = df[0][\"progress\"].value_counts()[17]\n",
    "completed_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_count = len(df)\n",
    "total_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "completed_ratio = completed_count / total_count\n",
    "completed_ratio * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[0][\"year\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[0][\"field\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[0][\"level\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "levels = {\n",
    "    \"General (Low)\": 0,\n",
    "    \"Apprenticeship (Mid)\": 1,\n",
    "    \"Professional Maturity (High)\": 2\n",
    "}\n",
    "\n",
    "levels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[0][[\"year\", \"version\"]].value_counts().unstack().plot(kind=\"bar\", figsize=(16, 9))\n",
    "plt.xticks(rotation=0)\n",
    "plt.xlabel(\"Study Year\")\n",
    "plt.ylabel(\"Number of people\")\n",
    "plt.legend(title=\"Level\")\n",
    "plt.savefig(\"plots/lvl_per_year.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[15]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[12]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[(0, \"gender\"), (12, \"score\")]].value_counts().unstack().plot(kind=\"bar\", figsize=(16, 9), cmap='RdYlBu')\n",
    "plt.xticks(rotation=0)\n",
    "plt.xlabel(\"Study Year\")\n",
    "plt.ylabel(\"Number of people\")\n",
    "plt.legend(title=\"Previous knowledge of Beer's Law\", labels=[\"None\", \"Little\", \"Average\", \"Good\", \"Perfect\"])\n",
    "#plt.savefig(\"plots/beerknow_per_year.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[(0, \"year\"), (15, \"r\")]].value_counts().unstack().plot(kind=\"bar\", figsize=(16, 9), cmap='RdYlBu')\n",
    "plt.xticks(rotation=0)\n",
    "plt.xlabel(\"Study Year\")\n",
    "plt.ylabel(\"Number of people\")\n",
    "plt.legend(title=\"Previous knowledge of Beer's Law\", labels=[\"None\", \"Little\", \"Average\", \"Good\", \"Perfect\"])\n",
    "plt.savefig(\"plots/beerknow_per_year.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "df[15][\"r\"].value_counts().sort_index().plot(kind=\"bar\", figsize=(16, 9))\n",
    "plt.xticks(range(5), [\"Unknown\", \"\", \"Average\", \"\", \"Well known\"], rotation=0)\n",
    "plt.ylabel(\"Number of people\")\n",
    "plt.xlabel(\"Previous knowledge of Beer's Law\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "df[15][\"r\"].hist(widths=[0.5] * 5, bins=[-1, -0.5, 0, 0.5, 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[c for c in df.columns if c[1] == \"rel\"]].applymap(lambda x: (1 if x == 0 else 0) if x is not None else None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[c for c in df.columns if c[1] == \"score\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[0][[\"year\", \"level\"]].value_counts().unstack()[levels.keys()].T.plot(kind=\"bar\", figsize=(16, 9))\n",
    "plt.xticks(rotation=90)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gender_map = {\n",
    "    1: \"Male\",\n",
    "    2: \"Female\",\n",
    "    3: \"Other\",\n",
    "    4: \"Unspecified\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "year_level = df[0][[\"year\", \"level\", \"gender\"]].value_counts().unstack()\n",
    "year_level = year_level.set_index(year_level.index.to_flat_index().map(\" year\\n\".join).map(lambda x: x.split(\"(\")[0]))\n",
    "year_level.columns = year_level.columns.map(lambda a: gender_map[a])\n",
    "ax = year_level.plot(kind=\"bar\", figsize=(16, 9))\n",
    "plt.legend(title=\"Gender\")\n",
    "plt.xticks(rotation=40, ha=\"right\")\n",
    "plt.xlabel(\"Formation & Study Year\")\n",
    "plt.ylabel(\"Student count\")\n",
    "plt.title(\"Gender repartition per formation and study year\")\n",
    "plt.savefig(\"plots/gender_year_level.png\", bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[0][[\"year\", \"gender\"]].value_counts().unstack().plot(kind=\"bar\", figsize=(16, 9))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[0][[\"level\", \"gender\"]].value_counts().unstack().plot(kind=\"bar\", figsize=(16, 9))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[7][\"ans\"].hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def hex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(16, 9))\n",
    "cmap = plt.rcParams['axes.prop_cycle'].by_key()[\"color\"]\n",
    "\n",
    "_, g_bins = np.histogram(diffs[df.index], bins=50)\n",
    "\n",
    "for i, y in enumerate(years):\n",
    "    sub = diffs[df[df[0][\"year\"] == y].index]\n",
    "    sizes, bins = np.histogram(sub, bins=g_bins)\n",
    "    \n",
    "    cnts = {x: 0 for x in bins}\n",
    "    \n",
    "    for s in sub:\n",
    "        low_bins = [b for b in bins[:-1] if b <= s]\n",
    "        last = low_bins[-1]\n",
    "        total = sizes[len(low_bins)-1]\n",
    "        odd = (len(low_bins) + total) % 2\n",
    "        \n",
    "        plt.plot(i + cnts[last] / 40 + odd/80 - total / 80, last, \"o\", c=cmap[i])\n",
    "        cnts[last] += 1\n",
    "        \n",
    "    #ax.bar(bins, sizes)\n",
    "    \n",
    "    #ax.scatter([i] * len(sub), sub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1 fichier par utilisateur nommé avec le uid\n",
    "\n",
    "mapping ranking -> groupe pour 3 et 4 class case\n",
    "\n",
    "liste des actions"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pdm-env",
   "language": "python",
   "name": "pdm-env"
  },
  "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}