limesurvey_suave/assets/CSV to VV.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8bed1ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import panel as pn\n",
    "from datetime import datetime\n",
    "\n",
    "pn.extension('tabulator')\n",
    "pn.extension()\n",
    "\n",
    "# specific imports\n",
    "import requests\n",
    "import re\n",
    "from IPython.display import Markdown, display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d6bd846",
   "metadata": {},
   "outputs": [],
   "source": [
    "def printmd(string):\n",
    "    display(Markdown(string))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f11a8df",
   "metadata": {},
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>In the next cell, please select and locate your SuAVE CSV file.</span></b>\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbff6994",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "s_files = pn.widgets.FileSelector('~')\n",
    "\n",
    "s_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "514c373c",
   "metadata": {},
   "outputs": [],
   "source": [
    "suave = pd.read_csv(s_files.value[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36ba4626",
   "metadata": {},
   "outputs": [],
   "source": [
    "pn.widgets.Tabulator(suave)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bc34518",
   "metadata": {},
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>In the next cell, please select and locate the VV file you downloaded from LimeSurvey.</span></b>\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b1f25bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "v_files = pn.widgets.FileSelector('~')\n",
    "\n",
    "v_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c113114",
   "metadata": {},
   "outputs": [],
   "source": [
    "vv = pd.read_csv(v_files.value[0],sep='\\t')\n",
    "if 'Unnamed' in vv.columns[-1]:\n",
    "    vv = vv.drop(vv.columns[-1], axis=1)\n",
    "old_header = vv.columns\n",
    "new_header = vv.iloc[0] #grab the first row for the header\n",
    "vv = vv[1:] #take the data less the header row\n",
    "vv.columns = new_header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3263b550",
   "metadata": {},
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>In the next cell, please select and locate the particpants file you downloaded from LimeSurvey.</span></b>\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8732a88a",
   "metadata": {},
   "outputs": [],
   "source": [
    "p_files = pn.widgets.FileSelector('~')\n",
    "\n",
    "p_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77afbd46",
   "metadata": {},
   "outputs": [],
   "source": [
    "part = pd.read_csv(p_files.value[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18877cc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>Please input your survey id.</span></b>\")\n",
    "survey_id = input()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73d1f463",
   "metadata": {},
   "outputs": [],
   "source": [
    "vv['token'] = part['token']\n",
    "vv['id']=range(1,len(part)+1)\n",
    "vv['lastpage']=1\n",
    "vv['startlanguage'] = 'en'\n",
    "vv['refurl']='https://limesurvey.sdsc.edu/limesurvey/index.php/admin/tokens/sa/browse/surveyid/'+survey_id\n",
    "vv['submitdate'] = datetime.now().strftime(\"%m-%d-%y %H:%M:%S\")\n",
    "vv['startdate'] = datetime.now().strftime(\"%m-%d-%y %H:%M:%S\")\n",
    "vv['datestamp'] = datetime.now().strftime(\"%m-%d-%y %H:%M:%S\")\n",
    "vv['ipaddr'] = '123.123.123.123'\n",
    "vv['seed'] = 123456789"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "488d79ff",
   "metadata": {},
   "source": [
    "# Short Text Questions & Multi Short Text Questions (rerun this section for each short text question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba08f94f",
   "metadata": {},
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>For a short text question (works on a multi short text question too), please select the exact column name/s (from the vv file, the table above this cell) corresponding to each subquestion.</span></b>\")\n",
    "vmulti_text = pn.widgets.CrossSelector(name='VV Short Question', options=list(vv.columns))\n",
    "\n",
    "vmulti_text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "279c7591",
   "metadata": {},
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>For a short text question (works on a multi short text question too), please select the exact column name/s (from the SuAVE file) corresponding to each subquestion. Make sure the order corresponds to the order above.</span></b>\")\n",
    "smulti_text = pn.widgets.CrossSelector(name='SuAVE Short Question', options=list(suave.columns), definition_order=False)\n",
    "\n",
    "smulti_text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86dcf195",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,len(vmulti_text.value)):\n",
    "    vv[vmulti_text.value[i]] = suave[smulti_text.value[i]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efb7cb05",
   "metadata": {},
   "source": [
    "# List Questions (rerun this section for each list question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2798297c",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>For a list question, please select the exact column name (from the VV file) corresponding to the list question.</span></b>\")\n",
    "listq = pn.widgets.CrossSelector(name='List Question', options=list(vv.columns), definition_order=False)\n",
    "\n",
    "listq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92d1c240",
   "metadata": {},
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>For a list question, please select the exact column name (from the SuAVE file) corresponding to the list question.</span></b>\")\n",
    "lista = pn.widgets.CrossSelector(name='List Question', options=list(suave.columns), definition_order=False)\n",
    "\n",
    "lista"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c43364c",
   "metadata": {},
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>Are these all the answer options in this question: {}?</span></b>\".format(suave[lista.value[0]].unique()))\n",
    "select = pn.widgets.Select(name='', options=[True, False])\n",
    "\n",
    "select"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5863f5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "if select.value==False:\n",
    "    printmd(\"<b><span style='color:red; font-size: 150%;'>Please input all answer options separated by a comma in the list question.</span></b>\")\n",
    "    options = input()\n",
    "    options = [x.strip() for x in options.split(',')]\n",
    "elif select.value==True:\n",
    "    options = suave[lista.value[0]].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8929795",
   "metadata": {},
   "outputs": [],
   "source": [
    "options.sort()\n",
    "temp = []\n",
    "options_dict = {}\n",
    "for i in range(1,len(options)+1):\n",
    "    options_dict[options[i-1]] = 'A'+str(i)\n",
    "for i in suave[lista.value[0]].values:\n",
    "    temp.append(options_dict[i])\n",
    "vv[listq.value[0]] = temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53e3a73d",
   "metadata": {},
   "outputs": [],
   "source": [
    "pn.widgets.Tabulator(vv)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaf5a196",
   "metadata": {},
   "source": [
    "# Multiple Choice Questions (rerun this section for each multiple choice question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62f129e9",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>For a multiple choice question, please select the exact column names (from the VV file) corresponding to the multiple choice question.</span></b>\")\n",
    "multipleq = pn.widgets.CrossSelector(name='Multiple Choice Question', options=list(vv.columns), definition_order=False)\n",
    "\n",
    "multipleq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36a8d080",
   "metadata": {},
   "outputs": [],
   "source": [
    "multiple_q = multipleq.value\n",
    "multiple_q.sort(key=len)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55e7ada4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>For a multiple choice question, please select the exact column name (from the SuAVE file) corresponding to the list question.</span></b>\")\n",
    "multiple_s = pn.widgets.CrossSelector(name='List Question', options=list(suave.columns), definition_order=False)\n",
    "\n",
    "multiple_s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "958de8d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "multiple_a = list(set(sum([i.split('|') for i in suave[multiple_s.value[0]].unique()],[])))\n",
    "multiple_a.sort()\n",
    "printmd(\"<b><span style='color:red; font-size: 150%;'>Are these all the subquestions in this question: {}?</span></b>\".format(multiple_a))\n",
    "selectm = pn.widgets.Select(name='', options=[True, False])\n",
    "selectm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76bad43b",
   "metadata": {},
   "outputs": [],
   "source": [
    "if selectm.value==False:\n",
    "    printmd(\"<b><span style='color:red; font-size: 150%;'>Please input all subquestions separated by a comma in this multiple choice question.</span></b>\")\n",
    "    multiple_a = input()\n",
    "    multiple_a = [x.strip() for x in multiple_a.split(',')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b76158c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "multiple_dict = {}\n",
    "for i in range(0,len(multiple_a)):\n",
    "    multiple_dict[multiple_a[i]] = 'Keywords_A'+str(i+1)\n",
    "for i in range(0,len(multiple_a)):\n",
    "    keywords = suave.iloc[[i]][multiple_s.value[0]].values[0].split('|')\n",
    "    for j in keywords:\n",
    "        vv.loc[vv.index[i],multiple_dict[j]] = 'Y'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fbd2186",
   "metadata": {},
   "source": [
    "# Image Question "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f1211eb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "printmd(\"<b><span style='color:red; font-size: 150%;'>For the photo question, please select the exact column name (from the VV file) corresponding to the photo question. The column name should be the name of the question only and not the one with filecount.</span></b>\")\n",
    "photoq = pn.widgets.CrossSelector(name='Photo Question', options=list(vv.columns), definition_order=False)\n",
    "photoq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77d36f63",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "vv[photoq.value[0]+'_filecount'] = 1\n",
    "temp = '[{lbrace} \"title\":\"\",\"comment\":\"\",\"size\":\"817.6318359\",\"name\":\"\",\"filename\":\"\",\"ext\":\"png\" }]'.split(\":\")\n",
    "for j in range(0,len(vv)):\n",
    "    temp = '[{lbrace} \"title\":\"\",\"comment\":\"\",\"size\":\"817.6318359\",\"name\":\"\",\"filename\":\"\",\"ext\":\"png\" }]'.split(\":\")\n",
    "    img = ''\n",
    "    for i in range(0,len(temp)):\n",
    "        if i == 3:\n",
    "            img = img+temp[i]+\":\"+\"\\\"\"+suave['#img'][j]+\"\\\"\"\n",
    "        elif i == 4:\n",
    "            img = img+temp[i][2:]+\":\"+\"\\\"\"+suave['#img'][j]+\"\\\"\"\n",
    "        elif i == 5:\n",
    "            img = img+temp[i][2:]+\":\"\n",
    "        elif i != 6:\n",
    "            img = img+temp[i]+\":\"\n",
    "        elif i == 6:\n",
    "            img = img + temp[i]\n",
    "    vv.loc[vv.index[j],photoq.value[0]] = img"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ab03d13",
   "metadata": {},
   "source": [
    "# Review your VV file by running the cell below to check that your columns are correctly populated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ede4ba59",
   "metadata": {},
   "outputs": [],
   "source": [
    "pn.widgets.Tabulator(vv)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10d4b0b0",
   "metadata": {},
   "source": [
    "# When you finish populating all your columns, run the cell below to get the updated VV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "542e36dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "vv.loc[-1] = vv.columns.values\n",
    "vv.sort_index(inplace=True)\n",
    "vv.reset_index(drop=True, inplace=True)\n",
    "\n",
    "col_dict = {}\n",
    "for i in range(0,len(old_header)):\n",
    "    col_dict[new_header[i]] = old_header[i]\n",
    "vv.rename(columns=col_dict, inplace=True)\n",
    "vv.to_csv('updated'+survey_id+\".csv\",index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}