PullingNettskjema.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Collecting responses via Nettskjema API\n",
    "The following notebook details how to use the Nettskjema API to retrieve responses collected via these webforms. This example combines informatiom from the exiting documentation on this API's structure (https://utv.uio.no/docs/nettskjema/api/), code samples of the python library request, documentation of the r library nettskjemar, and some trial and error.\n",
    "\n",
    "In order to access the Nettskjema API, you need:\n",
    "\n",
    "    1) a token (string key) with editing rights to the form in questions\n",
    "    2) a connection from a suitable IP address (such as on the UIO network)\n",
    "    3) suitable commands for retrieving the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting access to the API\n",
    "In order to access a form through the API, you need to generate an api account for your uio account, generate a unique token with suitable roles for that api account, and grant that api account editing privileges for the form(s) you want to access via the API. This is the link to set up and edit your api account within the nettskjema webinterface: https://nettskjema.no/user/api/index.html\n",
    "\n",
    "First creat an api account with a simple name and description, then click on that generated account and generate a token with suitable rolls and IP restrictions. Tokens are strings that act as keys so the system knows who is logging in to the API and that they have permission to do specific things. If you are only downloading responses via API (instead of setting up and editing forms), your token needs only the roles []READ_SUBMISSIONS and []READ_FORMS. If you leave the default IP address range, you can access the API from computers on the UiO network. This include machines logged into remotely (like through VMware Horizon).\n",
    "\n",
    "When you generate/save the token, the next screen shows you the token string. **COPY AND PASTE THIS INTO A FILE RIGHT AWAY as you will never be able to retrieve it again.** (Though it is easy to just generate another token if needed.) This notebook reads a local directory file called 'nettskjema_token.txt' to get the token string.  \n",
    "\n",
    "Once you have the token with suitable roles, click over to your form. Under the rights tab (Rettigheter) should be a list of nettskjema users who have editing rights on this form. So long as you are in one of those accounts, you can add your api account as: \"*yourapiaccountname*@api\". Note: Once you save, it will show up as \"*yourapiaccountname* @ api\" with spaces arounf the @, but there should not be spaces in the username when you are pasting it in to grant acess. You can check if the rights have been granted properly by going back to your api account details (under https://nettskjema.no/user/api/index.html) and making sure the form is listed in the Skjemaer table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once the rights have been granted, we can use the token to access the API programatically. The API documentation gives examples of curl commands that can be run from the command like or terminal (https://utv.uio.no/docs/nettskjema/api/). Below are examples of performing the same tasks with python's request library. Also available is an r library developped by UiO research group LCBC to pull response data directly into r data formats, *nettskjemar*: https://lcbc-uio.github.io/nettskjemar/index.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Accessing the API via Request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import zipfile\n",
    "import os\n",
    "import base64\n",
    "import time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Navigate to the directory storing your *nettskjema_token.txt* file and read the API token stored there."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['PullingMusicLab.ipynb',\n",
       " '.DS_Store',\n",
       " 'PullingNettskjema.ipynb',\n",
       " 'CompressedData',\n",
       " 'README.md',\n",
       " '.gitignore',\n",
       " 'copen_netts.tsv',\n",
       " 'Test_API',\n",
       " '.ipynb_checkpoints',\n",
       " 'Copendata',\n",
       " '.git',\n",
       " 'nettskjema_token.txt']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# os.chdir('M:\\\\finnu\\\\kant\\\\div-ritmo-u1')\n",
    "# os.chdir('M:\\\\research')\n",
    "os.listdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "f=open('nettskjema_token.txt','r')\n",
    "TOKEN = f.read()\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set up a request session with the token information saved into the authentication settings. This allows us to skip spelling out the token string in this notebook with every API request sent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "session = requests.Session()\n",
    "session.headers.update({'Authorization': 'Bearer ' + TOKEN})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Test the token by sending a request to see its expiration date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b'{\"expireDate\":\"2022-10-18T17:38:29.000+0200\"}'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirm the token is working on this IP with the check on expiry date\n",
    "request_url = \"https://nettskjema.no/api/v2/users/admin/tokens/expire-date\"\n",
    "response = session.get(request_url)\n",
    "response.content"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " If the token is broken or wrong or too old, you will get an error message like: \n",
    "\n",
    "`b'{\"statusCode\":400,\"message\":\"Not token authenticated\",\"errors\":null,\"nestedErrors\":null}'`\n",
    "\n",
    "If the token is recognised, the output will be just the expiry date:\n",
    "\n",
    "`b'{\"expireDate\":\"2022-10-18T17:38:29.000+0200\"}'`\n",
    "\n",
    "Note: the 'b' before the response string indicates that the API reponse is transmited in bytes. The formate is important to handle when trying to store the collected API response data. The standard function .decode() converts the byte string into something python interpretable. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calling for Form metadata\n",
    "\n",
    "If the token is working, next request information about the form you want to get data from. For this you need the formID number, a unique integer assigned by Nettskjema when the form was created. This is at the end of the form URL (ex: 225781 in https://nettskjema.no/a/225781). The page describing which forms your API account has access to also includes these ID numbers (https://nettskjema.no/user/api/index.html#/user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The basic request to retreive metadata gives the description of who has access and editing rights, some history of the form, and the content of the form. The information returned to requests about forms are json files which can easily be converted into python dictionaries. \n",
    "\n",
    "It is possible to delete and edit forms through the API, but this isn't described here. The request url formates for these functions should be deducable from the curl commands listed in the API instructions at https://utv.uio.no/docs/nettskjema/api/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'formId': 225781,\n",
       " 'languageCode': 'en',\n",
       " 'title': '0 Physically Present Participant Number',\n",
       " 'deliveryDestination': 'DATABASE',\n",
       " 'formType': 'DEFAULT',\n",
       " 'theme': 'DEFAULT',\n",
       " 'createdBy': {'personId': 616293,\n",
       "  'username': 'danasw@uio.no',\n",
       "  'fullName': 'Dana Swarbrick',\n",
       "  'name': 'Dana Swarbrick',\n",
       "  'type': 'LOCAL'},\n",
       " 'modifiedBy': {'personId': 1676908,\n",
       "  'username': 'finnu@uio.no',\n",
       "  'fullName': 'Finn Upham',\n",
       "  'name': 'Finn Upham',\n",
       "  'type': 'LOCAL'},\n",
       " 'createdDate': '2021-10-21T09:39:49.000+0200',\n",
       " 'modifiedDate': '2021-10-28T16:29:27.000+0200',\n",
       " 'openFrom': '2021-10-27T09:44:45.000+0200',\n",
       " 'respondentGroup': 'ALL',\n",
       " 'editorsContactEmail': 'danasw@uio.no',\n",
       " 'editorsSubmissionEmailType': 'NONE',\n",
       " 'editors': [{'personId': 1914058,\n",
       "   'username': 'finnu1@api',\n",
       "   'fullName': 'finn ritmo access',\n",
       "   'name': 'finn ritmo access',\n",
       "   'type': 'API'},\n",
       "  {'personId': 1676908,\n",
       "   'username': 'finnu@uio.no',\n",
       "   'fullName': 'Finn Upham',\n",
       "   'name': 'Finn Upham',\n",
       "   'type': 'LOCAL'},\n",
       "  {'personId': 616293,\n",
       "   'username': 'danasw@uio.no',\n",
       "   'fullName': 'Dana Swarbrick',\n",
       "   'name': 'Dana Swarbrick',\n",
       "   'type': 'LOCAL'}],\n",
       " 'collectsPersonalData': False,\n",
       " 'maxSubmissionsPerson': 1,\n",
       " 'retainRespondentAccessAfterDelivery': False,\n",
       " 'clientPostponable': False,\n",
       " 'scoreResultDisplayType': 'NONE',\n",
       " 'sensitivePersonalDataCollected': False,\n",
       " 'codebookActivated': False,\n",
       " 'shouldHideProgressBar': False,\n",
       " 'shouldPreventDataManipulation': False,\n",
       " 'shouldGetRespondentInfoFromPerson': False,\n",
       " 'elements': [{'elementId': 3485744,\n",
       "   'elementType': 'QUESTION',\n",
       "   'sequence': 1,\n",
       "   'questions': [{'questionId': 3741786,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'QUESTION',\n",
       "     'mandatory': False,\n",
       "     'text': 'userID',\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3485745,\n",
       "   'elementType': 'QUESTION',\n",
       "   'sequence': 2,\n",
       "   'questions': [{'questionId': 3741787,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'QUESTION',\n",
       "     'mandatory': False,\n",
       "     'text': 'What is the participants&#39; ID number?',\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]}],\n",
       " 'newDesignEnabledSetting': 'ALWAYS',\n",
       " 'open': True,\n",
       " 'possibleToHaveInvitations': False,\n",
       " 'sendingReceiptToRespondent': False}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# example metadata API request with simple form of one question.\n",
    "\n",
    "# equivalent curl command\n",
    "#  $ curl 'https://nettskjema.no/api/v2/forms/225781' -i -X GET -H 'Authorization: Bearer TOKEN'\n",
    "\n",
    "formID = 225781\n",
    "request_url = 'https://nettskjema.no/api/v2/forms/' + str(formID)\n",
    "response = session.get(request_url) # using the request session call which includes the saved API token\n",
    "form_metadata = json.loads(response.content.decode()) # inteprete recieved string into a python native datatype\n",
    "form_metadata # show the information output"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you do not have access rights to a form, or if you are trying to access the API from an IP address that isn't in the range specified by your token, you get API errors instead, like:\n",
    "\n",
    " `{'statusCode': 403,\n",
    " 'message': 'No access to form with id 225782.',\n",
    " 'errors': None,\n",
    " 'nestedErrors': None}`\n",
    " \n",
    " `{'statusCode': 404,\n",
    " 'message': 'Could not find form with id 22578.',\n",
    " 'errors': None,\n",
    " 'nestedErrors': None}`\n",
    " \n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'statusCode': 403,\n",
       " 'message': 'No access to form with id 225782.',\n",
       " 'errors': None,\n",
       " 'nestedErrors': None}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "formID = 225782\n",
    "request_url = 'https://nettskjema.no/api/v2/forms/' + str(formID)\n",
    "response = session.get(request_url) # using the request session call which includes the saved API token\n",
    "form_metadata = json.loads(response.content.decode()) # inteprete recieved string into a python native datatype\n",
    "form_metadata # show the information output"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The addition of '/submissions' to the request URL calls instead for metadata on the responses, called \"submissions\" by the API. The returned json file is here converted into a list of dictionaries with standard information about each submission (submission ID, created and modified dates, etc.) as well as all the form responses. \n",
    "\n",
    "Responses are returned in reverse chronological order: the last response is the first submission in the list. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'submissionId': 16674015,\n",
       "  'createdDate': '2021-10-27T00:00:00.000+0200',\n",
       "  'modifiedDate': '2021-10-27T00:00:00.000+0200',\n",
       "  'delivered': True,\n",
       "  'answerTime': 0,\n",
       "  'answers': [{'answerId': 102748640,\n",
       "    'questionId': 3741786,\n",
       "    'textAnswer': '40bf6287-e5a4-8a30-8863-cc71c9ef7225'},\n",
       "   {'answerId': 102748641, 'questionId': 3741787, 'textAnswer': 'Finn'}]},\n",
       " {'submissionId': 16653694,\n",
       "  'createdDate': '2021-10-26T00:00:00.000+0200',\n",
       "  'modifiedDate': '2021-10-26T00:00:00.000+0200',\n",
       "  'delivered': True,\n",
       "  'answerTime': 0,\n",
       "  'answers': [{'answerId': 102664391,\n",
       "    'questionId': 3741786,\n",
       "    'textAnswer': '08ff9d8a-6bc2-897f-24d3-91eb10717741'}]}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "formID = 225781\n",
    "request_url = 'https://nettskjema.no/api/v2/forms/' + str(formID) + '/submissions' \n",
    "response = session.get(request_url) # using the request session call which includes the saved API token\n",
    "sub_metadata = json.loads(response.content.decode()) # inteprete recieved string into a python native datatype\n",
    "sub_metadata[:2] # examples of the last two responses recieved"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to request subsets of responses, specifically all responses after either a specific submission date or submission ID. Submission IDs increase monotonically, assigned uniquely across all of Nettskjema.no. A conveninent trick when downloading responses from an active survey is to call for only those submissions recieved since the last time the data was downloaded. To call only subsets, the request url gets extended with \"&fromDate=\" or \"&fromSubmissionID=\" with the appropriately formated threshold. \n",
    "\n",
    "It is also possible to download only the submission ID feild, instead of the full submission details with the addition of \"?fields=submissionId\". At this time, no other feilds can isolated this way."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submissions since date 2021-10-25T08%3A27%3A22.000%2B0100: 21559\n",
      "[{'submissionId': 16794081}, {'submissionId': 16789319}]\n"
     ]
    }
   ],
   "source": [
    "# how to call submissions from after a certain date\n",
    "\n",
    "# curl command template\n",
    "# $ curl 'https://nettskjema.no/api/v2/forms/8432376/submissions?fields=submissionId&fromDate=2021-01-11T13%3A43%3A17.486%2B0100' -i -X GET -H 'Authorization: Bearer TOKEN'\n",
    "\n",
    "formID = 141510\n",
    "# 2021-10-25T08:27:22+01:00 # remembrer URL encoding? : is %3A , + is %2B, https://www.w3schools.com/tags/ref_urlencode.ASP\n",
    "date = '2021-10-25T08%3A27%3A22.000%2B0100'\n",
    "\n",
    "request_url = 'https://nettskjema.no/api/v2/forms/' + str(formID) + '/submissions?fields=submissionId&fromDate=' + date\n",
    "\n",
    "response = session.get(request_url)\n",
    "subIDs = json.loads(response.content.decode())\n",
    "\n",
    "print('Submissions since date ' + date + ': ' + str(len(subIDs)))\n",
    "if len(subIDs)<5:\n",
    "    print(subIDs)\n",
    "else:\n",
    "    print(subIDs[:2])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: Forms that do not collect personal information on Nettskjema do not retain dates in a format that can be used for this kind of range restriction. You will get this error when trying to call a subset of responses by date: \n",
    "\n",
    "`{'statusCode': 409, 'message': 'Since the form does not collect personal data, the submissions will not have dates to compare with the fromDate parameter', 'errors': None, 'nestedErrors': None}`\n",
    "\n",
    "In this case, it is necessary to find a suitable submissionID that corresponds to the same temporal threshold. If you are monitoring an active survey, use the ID of the first `[0]` submissionID from your last API call. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submissions since subID 16653000: 3\n",
      "[{'submissionId': 16674015}, {'submissionId': 16653694}, {'submissionId': 16653300}]\n"
     ]
    }
   ],
   "source": [
    "# how to call submissions from after a certain ID, restricting metadata to submission ID\n",
    "# curl command template\"\n",
    "# $ curl 'https://nettskjema.no/api/v2/forms/225781/submissions?fields=submissionId&fromSubmissionId=16653694' -i -X GET \\\n",
    "#   -H 'Authorization: Bearer TOKEN'\n",
    "\n",
    "formID = 225781\n",
    "submissionID = 16653000\n",
    "request_url = 'https://nettskjema.no/api/v2/forms/' + str(formID) + '/submissions?fields=submissionId&fromSubmissionId=' + str(submissionID)\n",
    "response = session.get(request_url)\n",
    "subIDs = json.loads(response.content.decode())\n",
    "print('Submissions since subID ' + str(submissionID) + ': ' + str(len(subIDs)))\n",
    "if len(subIDs)<5: # print error message or top responses \n",
    "    print(subIDs)\n",
    "else:\n",
    "    print(subIDs[0])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Gathering Musiclab phone sensor data\n",
    "The above commands cover access to forms that collect information strickly through the webform interface. Apps like Musiclab also gather information in different shapes that are stored by nettskjema as attachments to submissions (responses). These are a bit trickier to retrieve, but still accessible through the API. \n",
    "\n",
    "Note: the following cells will not run without permissions for the MusicLab form on Nettskjema, but the shape should be the same for any forms that collects attachments with submissions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submissions since subID 16561653: 23523\n",
      "{'submissionId': 16794081, 'createdDate': '2021-10-31T20:11:18.000+0100', 'modifiedDate': '2021-10-31T20:11:18.000+0100', 'delivered': True, 'answerTime': 0, 'answers': [{'answerId': 103297368, 'questionId': 1996787, 'textAnswer': 'fb5a1b4d-e5c4-638c-1a08-932a34fd07b4'}, {'answerId': 103297367, 'questionId': 1996788, 'textAnswer': 'data.zip', 'attachments': [{'answerAttachmentId': 302899, 'fileName': 'data.zip', 'mediaType': 'application/zip'}]}]}\n"
     ]
    }
   ],
   "source": [
    "# get metadata on submissions for the music lab app after a certain submission ID\n",
    "\n",
    "formID = 141510\n",
    "submissionID = 16561653\n",
    "request_url = 'https://nettskjema.no/api/v2/forms/' + str(formID) + '/submissions?fromSubmissionId=' + str(submissionID)\n",
    "response = session.get(request_url)\n",
    "subIDs = json.loads(response.content.decode())\n",
    "print('Submissions since subID ' + str(submissionID) + ': ' + str(len(subIDs)))\n",
    "if len(subIDs)<5: # print error message or top responses \n",
    "    print(subIDs)\n",
    "else:\n",
    "    print(subIDs[0])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to retrieve the sensor data stored in the submission attachment, we have to call for each file individually and save it appropriately. Here are the essential details from one submission out of the metadata called above. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "submissionID : 16794081\n",
      "Submitting instillation: fb5a1b4d-e5c4-638c-1a08-932a34fd07b4\n",
      "[{'answerAttachmentId': 302899, 'fileName': 'data.zip', 'mediaType': 'application/zip'}]\n"
     ]
    }
   ],
   "source": [
    "subn = 0\n",
    "print('submissionID : ' + str(subIDs[subn]['submissionId']))\n",
    "for ans in subIDs[subn]['answers']:\n",
    "    if 'textAnswer' in ans:\n",
    "        if len(ans['textAnswer'])>12:\n",
    "            print('Submitting instillation: ' + ans['textAnswer'])\n",
    "    if 'attachments' in ans:\n",
    "        print(ans['attachments'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "so to call the attachment for that submission we use the request:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fileName: data.zip\n",
      "fileSize: 51850\n",
      "mediaType: application/zip\n",
      "content: UEsDBAoAAAAAAGiZX1Nnsy6jgcgAAIHIAAA1AAAAZmI1YTFiNGQtZTVjNC02MzhjLTFhMDgtOTMyYTM0ZmQwN2I0LmRldmljZU1vdGlvbi5jc3Z0aW1lc3RhbXAsdGltZSx4LHkseixhbHBoYSxiZXRhLGdhbW1hDQoxNjM1NzA3NDcwMTMwLDM4MjQxLjg5OTk5OTk5MzYyLDAuOTI3OTk5OTczMjk3MTE5MSw1LjcwNjk5OTc3ODc0NzU1OSw3Ljg1MzAwMDE2NDAzMTk4Miw4NC4xNjA1MTE3ODMwMDgyNiwtMjIuMDc2NTgwMTIzOTk3NTI3LDEwLjA4OTU1ODAwMzI2Mzc3NQ0KMTYzNTcwNzQ3MDE0NCwzODI1Ni45MDAwMDAwMDc1OSwwLjg5OTk5OTk3NjE1ODE0MjEsNS42NTk5OTk4NDc0MTIxMDksNy42OTk5OTk4MDkyNjUxMzcsODQuMTYwNTExNzgzMDA4MjYsLTIy...\n"
     ]
    }
   ],
   "source": [
    "subn = 0 # just calling one as an example\n",
    "\n",
    "subID = str(subIDs[subn]['submissionId'])\n",
    "for ans in subIDs[subn]['answers']:\n",
    "    if 'attachments' in ans:\n",
    "        attID = str(ans['attachments'][0]['answerAttachmentId'])\n",
    "request_url = 'https://nettskjema.no/api/v2/submissions/' + subID + '/attachments/' + attID\n",
    "response = session.get(request_url)\n",
    "\n",
    "att_dets = json.loads(response.content.decode())\n",
    "print('fileName: ' + att_dets['fileName'])\n",
    "print('fileSize: ' + str(att_dets['fileSize']))\n",
    "print('mediaType: ' + att_dets['mediaType'])\n",
    "print('content: ' + att_dets['content'][:500] + '...')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The nettskjema API returns attachments as 64encoded zipfiles in byte strings. To make these readable, we need to decode then save the string as a zip file and then unzip them. Thankfully there are python libraries for this.  \n",
    "\n",
    "First be sure you are in a suitable local folder, then unpack the attachement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#os.mkdir('./Test_API')\n",
    "os.chdir('Test_API')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['.DS_Store', '16759222', 'data.zip', '16767882', '16794081', '16758800', '16767010', '16765118', '16766563', '16767008', '16765068', '16767014', '16767875']\n",
      "['fb5a1b4d-e5c4-638c-1a08-932a34fd07b4.geoLocation.csv', 'fb5a1b4d-e5c4-638c-1a08-932a34fd07b4.deviceMotion.csv']\n"
     ]
    }
   ],
   "source": [
    "subn = 0 # just calling one as an example\n",
    "\n",
    "subID = str(subIDs[subn]['submissionId'])\n",
    "for ans in subIDs[subn]['answers']:\n",
    "    if 'attachments' in ans:\n",
    "        attID = str(ans['attachments'][0]['answerAttachmentId'])\n",
    "request_url = 'https://nettskjema.no/api/v2/submissions/' + subID + '/attachments/' + attID\n",
    "response = session.get(request_url)\n",
    "\n",
    "att_dets = json.loads(response.content.decode())\n",
    "\n",
    "# write the decoded attachment into a zip file\n",
    "f=open('data.zip', 'wb')\n",
    "f.write(base64.b64decode(att_dets['content']))\n",
    "f.close()\n",
    "\n",
    "# and then unzip that file, leaving a uniquely titled csv, I hope\n",
    "with zipfile.ZipFile('data.zip', 'r') as zip_ref:\n",
    "    if not os.path.exists(str(subID)):\n",
    "        os.mkdir(str(subID))\n",
    "        zip_ref.extractall('./'+str(subID)) # Not unique filenames so use the unique submission IDs \n",
    "\n",
    "print(os.listdir())\n",
    "os.chdir('./'+str(subID))\n",
    "print(os.listdir())\n",
    "os.chdir('..')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we have a minute recording from a device with the unique installation ID 'cfcd73d7-4a...' in a format that is easy to read. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The files within the zip are named for the device and information type, but do not include the submission number. This means if they are opened into a folder that already contains a previous recording from the same device, the previously saved recording will be overwritten and lost. To avoid this, the files are unzipped within a folder names for that unique submission. \n",
    "\n",
    "Now to collect many at once: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time to collect 25 attachments: 2.028269052505493\n"
     ]
    }
   ],
   "source": [
    "# pull in attachment files and unpack them\n",
    "\n",
    "checkedSubs = 25\n",
    "\n",
    "newSubs = 0 # count the submission sampled\n",
    "tic = time.time()\n",
    "for submis in subIDs[:checkedSubs]: # just getting 10 as a test\n",
    "    # first find out the attachment file ID for this submission\n",
    "    subID = str(submis['submissionId'])\n",
    "    # if there is an IDed attachment for this submission, get the file\n",
    "    for subm in submis['answers']:\n",
    "        if len(subm)>3: # cheat to pick out only submissions with attachments. might fail.\n",
    "            attID = str(subm['attachments'][0]['answerAttachmentId'])\n",
    "            request_url = 'https://nettskjema.no/api/v2/submissions/' + subID + '/attachments/' + attID\n",
    "            response = session.get(request_url)\n",
    "            newSubs += 1\n",
    "            att_dets = json.loads(response.content.decode())\n",
    "            # write the decoded attachment into a zip file\n",
    "            f=open('data.zip', 'wb')\n",
    "            f.write(base64.b64decode(att_dets['content']))\n",
    "            f.close()\n",
    "            # and then unzip that file, leaving a uniquely titled csv, I hope\n",
    "            with zipfile.ZipFile('data.zip', 'r') as zip_ref:\n",
    "                if not os.path.exists(str(subID)):\n",
    "                    os.mkdir(str(subID))\n",
    "                    zip_ref.extractall('./'+str(subID)) # if not unique can use the unique submission IDs \n",
    "\n",
    "print('time to collect ' + str(newSubs) + ' attachments: ' + str(time.time() - tic))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The files can then be crawled for with suitable information about which submissions related to which instillations, i.e., what can be sewn together in order. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## compressing submission files\n",
    "For some forms of storage and retrieval of unzipped data, this folder-per-submission arrangement is really awkward. The following shows two reorganisation schemes. The first moves the files from a long list of folders to a single folder while adding the submission number to filenames to preserve uniqueness. The second organises the files into unique installation folders."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "import shutil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir('..')\n",
    "os.mkdir('CompressedData')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['16786391',\n",
       " '16780206',\n",
       " '16786273',\n",
       " '.DS_Store',\n",
       " '16777969',\n",
       " '16786008',\n",
       " 'data.zip',\n",
       " '16767882',\n",
       " '16794081',\n",
       " '16767010',\n",
       " '16786383',\n",
       " '16767008',\n",
       " '16781376',\n",
       " '16789317',\n",
       " '16789319',\n",
       " '16777633',\n",
       " '16783995',\n",
       " '16777971',\n",
       " '16785268',\n",
       " '16785269',\n",
       " '16778331',\n",
       " '16787114',\n",
       " '16786004',\n",
       " '16775525',\n",
       " '16767014',\n",
       " '16767875',\n",
       " '16777820']"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "folders = os.listdir('Test_API')\n",
    "folders "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['16767010.cc61a83b-5b8a-6939-6294-5466a6a9d573.deviceMotion.csv',\n",
       " '16789319.1f3c39de-1f3f-757d-1c8b-b2c89439d0b3.deviceMotion.csv',\n",
       " '16777971.782ab0f9-d8db-34c4-8420-3700e1bbf564.deviceMotion.csv',\n",
       " '16786383.fa598a6c-ad16-7169-3686-0242cce08021.deviceMotion.csv',\n",
       " '16786273.34a001ef-4d44-38c4-f35c-4104070edbf6.geoLocation.csv',\n",
       " '16777633.8f9cd059-3495-5fae-96fb-3e9c043bff11.deviceMotion.csv',\n",
       " '16786008.a62945b6-3ac0-e1d4-63f5-cec7c57aa561.deviceMotion.csv',\n",
       " '16781376.6d324af2-a252-ecab-2268-ca4aa2e26b99.deviceMotion.csv',\n",
       " '16786004.a62945b6-3ac0-e1d4-63f5-cec7c57aa561.deviceMotion.csv',\n",
       " '16783995.5c30134c-c0b1-f3d8-c9d8-9ac523608db3.deviceMotion.csv',\n",
       " '16786391.fa598a6c-ad16-7169-3686-0242cce08021.deviceMotion.csv',\n",
       " '16767010.cc61a83b-5b8a-6939-6294-5466a6a9d573.geoLocation.csv',\n",
       " '16785269.0a68f772-30e3-2436-cc64-1444a0805a86.deviceMotion.csv',\n",
       " '16777969.782ab0f9-d8db-34c4-8420-3700e1bbf564.deviceMotion.csv',\n",
       " '16767008.cc61a83b-5b8a-6939-6294-5466a6a9d573.deviceMotion.csv',\n",
       " '16787114.b01f3d3e-d53f-d92e-b60a-ed81be656974.geoLocation.csv',\n",
       " '16767014.cc61a83b-5b8a-6939-6294-5466a6a9d573.geoLocation.csv',\n",
       " '16767014.cc61a83b-5b8a-6939-6294-5466a6a9d573.deviceMotion.csv',\n",
       " '16789317.1f3c39de-1f3f-757d-1c8b-b2c89439d0b3.deviceMotion.csv',\n",
       " '16777633.8f9cd059-3495-5fae-96fb-3e9c043bff11.geoLocation.csv',\n",
       " '16786383.fa598a6c-ad16-7169-3686-0242cce08021.geoLocation.csv',\n",
       " '16787114.b01f3d3e-d53f-d92e-b60a-ed81be656974.deviceMotion.csv',\n",
       " '16767882.cfcd73d7-4af9-08e2-aa6a-e73e87bbfbf3.deviceMotion.csv',\n",
       " '16785268.0a68f772-30e3-2436-cc64-1444a0805a86.deviceMotion.csv',\n",
       " '16778331.2c213490-5bad-6fe9-3c7e-db7fbcf99877.deviceMotion.csv',\n",
       " '16786004.a62945b6-3ac0-e1d4-63f5-cec7c57aa561.geoLocation.csv',\n",
       " '16794081.fb5a1b4d-e5c4-638c-1a08-932a34fd07b4.geoLocation.csv',\n",
       " '16786391.fa598a6c-ad16-7169-3686-0242cce08021.geoLocation.csv',\n",
       " '16777820.97d16b6e-81fa-3a8b-e467-45b7d431861a.deviceMotion.csv',\n",
       " '16767875.cfcd73d7-4af9-08e2-aa6a-e73e87bbfbf3.deviceMotion.csv',\n",
       " '16775525.79c31318-394a-d873-0463-7db0dc4d7d2b.deviceMotion.csv',\n",
       " '16780206.a6e6bc47-8c07-4c7b-8e6e-5076393a0444.deviceMotion.csv',\n",
       " '16767008.cc61a83b-5b8a-6939-6294-5466a6a9d573.geoLocation.csv',\n",
       " '16778331.2c213490-5bad-6fe9-3c7e-db7fbcf99877.geoLocation.csv',\n",
       " '16786008.a62945b6-3ac0-e1d4-63f5-cec7c57aa561.geoLocation.csv',\n",
       " '16775525.79c31318-394a-d873-0463-7db0dc4d7d2b.geoLocation.csv',\n",
       " '16786273.34a001ef-4d44-38c4-f35c-4104070edbf6.deviceMotion.csv',\n",
       " '16789319.1f3c39de-1f3f-757d-1c8b-b2c89439d0b3.geoLocation.csv',\n",
       " '16783995.5c30134c-c0b1-f3d8-c9d8-9ac523608db3.geoLocation.csv',\n",
       " '16794081.fb5a1b4d-e5c4-638c-1a08-932a34fd07b4.deviceMotion.csv']"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to put all the files inside the CompressedData folder for fastest sftp transfer\n",
    "for subid in folders:\n",
    "    if subid.startswith('1'):\n",
    "        filenames = os.listdir('./Test_API/'+str(subid))\n",
    "        #print(filenames)\n",
    "        for fn in filenames:\n",
    "            if fn.endswith('.csv'):\n",
    "                sourcefile = './Test_API/'+str(subid) + '/' + fn\n",
    "                targetfile = './CompressedData/'+str(subid) + '.' + fn\n",
    "                #print(targetfile)\n",
    "                shutil.copy2(sourcefile,targetfile)\n",
    "                \n",
    "os.listdir('./CompressedData/')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To organise the files into folders per installation, we monitor and generate new folders as needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.mkdir('InstOrdData') \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['a62945b6-3ac0-e1d4-63f5-cec7c57aa561',\n",
       " '2c213490-5bad-6fe9-3c7e-db7fbcf99877',\n",
       " '0a68f772-30e3-2436-cc64-1444a0805a86',\n",
       " 'fa598a6c-ad16-7169-3686-0242cce08021',\n",
       " 'a6e6bc47-8c07-4c7b-8e6e-5076393a0444',\n",
       " '782ab0f9-d8db-34c4-8420-3700e1bbf564',\n",
       " '8f9cd059-3495-5fae-96fb-3e9c043bff11',\n",
       " '79c31318-394a-d873-0463-7db0dc4d7d2b',\n",
       " '5c30134c-c0b1-f3d8-c9d8-9ac523608db3',\n",
       " '6d324af2-a252-ecab-2268-ca4aa2e26b99',\n",
       " '1f3c39de-1f3f-757d-1c8b-b2c89439d0b3',\n",
       " '34a001ef-4d44-38c4-f35c-4104070edbf6',\n",
       " 'fb5a1b4d-e5c4-638c-1a08-932a34fd07b4',\n",
       " '97d16b6e-81fa-3a8b-e467-45b7d431861a',\n",
       " 'cfcd73d7-4af9-08e2-aa6a-e73e87bbfbf3',\n",
       " 'b01f3d3e-d53f-d92e-b60a-ed81be656974',\n",
       " 'cc61a83b-5b8a-6939-6294-5466a6a9d573']"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to put all the files inside the CompressedData folder for fastest sftp transfer\n",
    "foldlist = os.listdir('./InstOrdData/')\n",
    "\n",
    "for subid in folders:\n",
    "    if subid.startswith('1'):\n",
    "        filenames = os.listdir('./Test_API/'+str(subid))\n",
    "        #print(filenames)\n",
    "        for fn in filenames:\n",
    "            if fn.endswith('.csv'):\n",
    "                # extract the installation ID \n",
    "                subdets = fn.split('.')\n",
    "                instid = subdets[0]\n",
    "                # if the device doesn't have a folder, generate one\n",
    "                if instid not in foldlist:\n",
    "                    os.mkdir('./InstOrdData/' + instid)\n",
    "                    foldlist = os.listdir('./InstOrdData/')\n",
    "                    \n",
    "                sourcefile = './Test_API/' + str(subid) + '/' + fn\n",
    "                targetfile = './InstOrdData/' + instid + '/'+str(subid) + '.' + fn\n",
    "                #print(targetfile)\n",
    "                shutil.copy2(sourcefile,targetfile)\n",
    "                \n",
    "os.listdir('./InstOrdData/')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, if we need to use sftp to move the data, we don't need to crawl through thousands of folders to find it. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Check out other forms\n",
    "Try to convert other forms response data into convenient formats for analysis. The json files are not super convenient for python analysis strategies. This section is a Work in Progress."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to begin, the list of forms associated with the musiclab app for the copenhagen experiment. \n",
    "formes = [141510,225781,225357,225336,225387,225707,225692,225713,225713,225702,225709,225693,225714,225695,225711]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'formId': 225702,\n",
       " 'languageCode': 'en',\n",
       " 'title': '4 After Bach (English) MusicLab Copenhagen',\n",
       " 'deliveryDestination': 'DATABASE',\n",
       " 'formType': 'DEFAULT',\n",
       " 'theme': 'DEFAULT',\n",
       " 'createdBy': {'personId': 616293,\n",
       "  'username': 'danasw@uio.no',\n",
       "  'fullName': 'Dana Swarbrick',\n",
       "  'name': 'Dana Swarbrick',\n",
       "  'type': 'LOCAL'},\n",
       " 'modifiedBy': {'personId': 1676908,\n",
       "  'username': 'finnu@uio.no',\n",
       "  'fullName': 'Finn Upham',\n",
       "  'name': 'Finn Upham',\n",
       "  'type': 'LOCAL'},\n",
       " 'createdDate': '2021-10-20T18:31:06.000+0200',\n",
       " 'modifiedDate': '2021-10-29T09:35:51.000+0200',\n",
       " 'openFrom': '2021-10-20T18:41:57.000+0200',\n",
       " 'respondentGroup': 'ALL',\n",
       " 'editorsContactEmail': 'danasw@uio.no',\n",
       " 'editorsSubmissionEmailType': 'NONE',\n",
       " 'editors': [{'personId': 573969,\n",
       "   'username': 'kaylab@uio.no',\n",
       "   'fullName': 'Kayla Burnim',\n",
       "   'name': 'Kayla Burnim',\n",
       "   'type': 'LOCAL'},\n",
       "  {'personId': 1914058,\n",
       "   'username': 'finnu1@api',\n",
       "   'fullName': 'finn ritmo access',\n",
       "   'name': 'finn ritmo access',\n",
       "   'type': 'API'},\n",
       "  {'personId': 1676908,\n",
       "   'username': 'finnu@uio.no',\n",
       "   'fullName': 'Finn Upham',\n",
       "   'name': 'Finn Upham',\n",
       "   'type': 'LOCAL'},\n",
       "  {'personId': 616293,\n",
       "   'username': 'danasw@uio.no',\n",
       "   'fullName': 'Dana Swarbrick',\n",
       "   'name': 'Dana Swarbrick',\n",
       "   'type': 'LOCAL'}],\n",
       " 'collectsPersonalData': False,\n",
       " 'maxSubmissionsPerson': 1,\n",
       " 'retainRespondentAccessAfterDelivery': False,\n",
       " 'clientPostponable': False,\n",
       " 'scoreResultDisplayType': 'NONE',\n",
       " 'sensitivePersonalDataCollected': False,\n",
       " 'codebookActivated': False,\n",
       " 'shouldHideProgressBar': False,\n",
       " 'shouldPreventDataManipulation': False,\n",
       " 'shouldGetRespondentInfoFromPerson': False,\n",
       " 'elements': [{'elementId': 3484591,\n",
       "   'elementType': 'QUESTION',\n",
       "   'sequence': 1,\n",
       "   'questions': [{'questionId': 3740387,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'QUESTION',\n",
       "     'mandatory': False,\n",
       "     'text': 'userID',\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484592,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 2,\n",
       "   'questions': [{'questionId': 3740388,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Did you perceive how the visualisation related to the music?',\n",
       "     'answerOptions': [{'answerOptionId': 8636637,\n",
       "       'sequence': 1,\n",
       "       'text': '1 (Not at all)',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636638,\n",
       "       'sequence': 2,\n",
       "       'text': '2',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636639,\n",
       "       'sequence': 3,\n",
       "       'text': '3',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636640,\n",
       "       'sequence': 4,\n",
       "       'text': '4',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636641,\n",
       "       'sequence': 5,\n",
       "       'text': '5 (Totally)',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484593,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 3,\n",
       "   'questions': [{'questionId': 3740389,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Did the coloured lines and discs help you follow the different instruments?',\n",
       "     'answerOptions': [{'answerOptionId': 8636642,\n",
       "       'sequence': 1,\n",
       "       'text': '1 (Not at all)',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636643,\n",
       "       'sequence': 2,\n",
       "       'text': '2',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636644,\n",
       "       'sequence': 3,\n",
       "       'text': '3',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636645,\n",
       "       'sequence': 4,\n",
       "       'text': '4',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636646,\n",
       "       'sequence': 5,\n",
       "       'text': '5 (Totally)',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484594,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 4,\n",
       "   'questions': [{'questionId': 3740390,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Did the visualization influence your understanding of the structural elements of the music?',\n",
       "     'answerOptions': [{'answerOptionId': 8636647,\n",
       "       'sequence': 1,\n",
       "       'text': '1 (Not at all)',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636648,\n",
       "       'sequence': 2,\n",
       "       'text': '2',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636649,\n",
       "       'sequence': 3,\n",
       "       'text': '3',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636650,\n",
       "       'sequence': 4,\n",
       "       'text': '4',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636651,\n",
       "       'sequence': 5,\n",
       "       'text': '5 (Totally)',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484595,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 5,\n",
       "   'questions': [{'questionId': 3740391,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Did the visualization augment your appreciation of the repetition of melodic themes?',\n",
       "     'answerOptions': [{'answerOptionId': 8636652,\n",
       "       'sequence': 1,\n",
       "       'text': '1 (Not at all)',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636653,\n",
       "       'sequence': 2,\n",
       "       'text': '2',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636654,\n",
       "       'sequence': 3,\n",
       "       'text': '3',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636655,\n",
       "       'sequence': 4,\n",
       "       'text': '4',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636656,\n",
       "       'sequence': 5,\n",
       "       'text': '5 (Totally)',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484596,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 6,\n",
       "   'questions': [{'questionId': 3740392,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Compared to seeing a performance without a visualization, did this visualization disturb and degrade your appreciation of the piece?',\n",
       "     'answerOptions': [{'answerOptionId': 8636657,\n",
       "       'sequence': 1,\n",
       "       'text': '1 (Not at all)',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636658,\n",
       "       'sequence': 2,\n",
       "       'text': '2',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636659,\n",
       "       'sequence': 3,\n",
       "       'text': '3',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636660,\n",
       "       'sequence': 4,\n",
       "       'text': '4',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636661,\n",
       "       'sequence': 5,\n",
       "       'text': '5 (Totally)',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484597,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 7,\n",
       "   'questions': [{'questionId': 3740393,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Did the visualization enhance your experience of the music?',\n",
       "     'answerOptions': [{'answerOptionId': 8636662,\n",
       "       'sequence': 1,\n",
       "       'text': '1 (Not at all)',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636663,\n",
       "       'sequence': 2,\n",
       "       'text': '2',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636664,\n",
       "       'sequence': 3,\n",
       "       'text': '3',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636665,\n",
       "       'sequence': 4,\n",
       "       'text': '4',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636666,\n",
       "       'sequence': 5,\n",
       "       'text': '5 (Totally)',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484598,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 8,\n",
       "   'questions': [{'questionId': 3740394,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Did the visualization help you be more emotionally moved by the music?',\n",
       "     'answerOptions': [{'answerOptionId': 8636667,\n",
       "       'sequence': 1,\n",
       "       'text': '1 (Not at all)',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636668,\n",
       "       'sequence': 2,\n",
       "       'text': '2',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636669,\n",
       "       'sequence': 3,\n",
       "       'text': '3',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636670,\n",
       "       'sequence': 4,\n",
       "       'text': '4',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636671,\n",
       "       'sequence': 5,\n",
       "       'text': '5 (Totally)',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484599,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 9,\n",
       "   'questions': [{'questionId': 3740395,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Were you familiar with the music that you heard?',\n",
       "     'answerOptions': [{'answerOptionId': 8636672,\n",
       "       'sequence': 1,\n",
       "       'text': '1 (I have never heard it before)',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636673,\n",
       "       'sequence': 2,\n",
       "       'text': '2',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636674,\n",
       "       'sequence': 3,\n",
       "       'text': '3',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636675,\n",
       "       'sequence': 4,\n",
       "       'text': '4',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636676,\n",
       "       'sequence': 5,\n",
       "       'text': '5',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636677,\n",
       "       'sequence': 6,\n",
       "       'text': '6',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636678,\n",
       "       'sequence': 7,\n",
       "       'text': '7 (I am very familiar with the music)',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484600,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 10,\n",
       "   'questions': [{'questionId': 3740396,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Are you familiar with this style of music (Bach fugues)?',\n",
       "     'answerOptions': [{'answerOptionId': 8636679,\n",
       "       'sequence': 1,\n",
       "       'text': '1 (I have never heard it before)',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636680,\n",
       "       'sequence': 2,\n",
       "       'text': '2',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636681,\n",
       "       'sequence': 3,\n",
       "       'text': '3',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636682,\n",
       "       'sequence': 4,\n",
       "       'text': '4',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636683,\n",
       "       'sequence': 5,\n",
       "       'text': '5',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636684,\n",
       "       'sequence': 6,\n",
       "       'text': '6',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636685,\n",
       "       'sequence': 7,\n",
       "       'text': '7 (I am very familiar with this type of music)',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484601,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 11,\n",
       "   'questions': [{'questionId': 3740397,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Were you aware of your physical body during this piece?',\n",
       "     'answerOptions': [{'answerOptionId': 8636686,\n",
       "       'sequence': 1,\n",
       "       'text': 'Not at all',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636687,\n",
       "       'sequence': 2,\n",
       "       'text': 'Rarely',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636688,\n",
       "       'sequence': 3,\n",
       "       'text': 'Intermittently',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636689,\n",
       "       'sequence': 4,\n",
       "       'text': 'Continuously',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484602,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 12,\n",
       "   'questions': [{'questionId': 3740398,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Were you aware of your own physical movement during this piece? If so, how much did you move relative to your own usual behaviour at this kind of concert?',\n",
       "     'answerOptions': [{'answerOptionId': 8636690,\n",
       "       'sequence': 1,\n",
       "       'text': 'Not aware of movement',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636691,\n",
       "       'sequence': 2,\n",
       "       'text': 'Yes and I moved less than usual',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636692,\n",
       "       'sequence': 3,\n",
       "       'text': 'Yes and I moved a normal amount',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636693,\n",
       "       'sequence': 4,\n",
       "       'text': 'Yes and I moved more than usual',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]},\n",
       "  {'elementId': 3484603,\n",
       "   'elementType': 'RADIO',\n",
       "   'sequence': 13,\n",
       "   'questions': [{'questionId': 3740399,\n",
       "     'sequence': 1,\n",
       "     'elementType': 'RADIO',\n",
       "     'mandatory': False,\n",
       "     'text': 'Were you aware of others in the audience moving during the piece? If so, how much were they moving relative to the usual behaviour you observe at this kind of concert?',\n",
       "     'answerOptions': [{'answerOptionId': 8636694,\n",
       "       'sequence': 1,\n",
       "       'text': 'Not aware of others’ movement',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636695,\n",
       "       'sequence': 2,\n",
       "       'text': 'Yes and they moved less than usual',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636696,\n",
       "       'sequence': 3,\n",
       "       'text': 'Yes and they moved a normal amount',\n",
       "       'preselected': False,\n",
       "       'correct': False},\n",
       "      {'answerOptionId': 8636697,\n",
       "       'sequence': 4,\n",
       "       'text': 'Yes and they moved more than usual',\n",
       "       'preselected': False,\n",
       "       'correct': False}],\n",
       "     'sendAdditionalRecipientEmail': False,\n",
       "     'horizontal': False,\n",
       "     'rangeMarksShown': False}]}],\n",
       " 'newDesignEnabledSetting': 'ALWAYS',\n",
       " 'open': True,\n",
       " 'possibleToHaveInvitations': False,\n",
       " 'sendingReceiptToRespondent': False}"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examples of the data shape as recieved by the API\n",
    "\n",
    "formID = formes[9]\n",
    "request_url = 'https://nettskjema.no/api/v2/forms/' + str(formID)\n",
    "response = session.get(request_url) # using the request session call which includes the saved API token\n",
    "form_metadata = json.loads(response.content.decode()) # inteprete recieved string into a python native datatype\n",
    "form_metadata # show the information output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3484591 QUESTION\n",
      "\t Qid: 3740387\n",
      "\t Q: userID\n",
      "3484592 RADIO\n",
      "\t Qid: 3740388\n",
      "\t Q: Did you perceive how the visualisation related to the music?\n",
      "\t \t1 (Not at all)\n",
      "\t \t2\n",
      "\t \t3\n",
      "\t \t4\n",
      "\t \t5 (Totally)\n",
      "3484593 RADIO\n",
      "\t Qid: 3740389\n",
      "\t Q: Did the coloured lines and discs help you follow the different instruments?\n",
      "\t \t1 (Not at all)\n",
      "\t \t2\n",
      "\t \t3\n",
      "\t \t4\n",
      "\t \t5 (Totally)\n",
      "3484594 RADIO\n",
      "\t Qid: 3740390\n",
      "\t Q: Did the visualization influence your understanding of the structural elements of the music?\n",
      "\t \t1 (Not at all)\n",
      "\t \t2\n",
      "\t \t3\n",
      "\t \t4\n",
      "\t \t5 (Totally)\n",
      "3484595 RADIO\n",
      "\t Qid: 3740391\n",
      "\t Q: Did the visualization augment your appreciation of the repetition of melodic themes?\n",
      "\t \t1 (Not at all)\n",
      "\t \t2\n",
      "\t \t3\n",
      "\t \t4\n",
      "\t \t5 (Totally)\n",
      "3484596 RADIO\n",
      "\t Qid: 3740392\n",
      "\t Q: Compared to seeing a performance without a visualization, did this visualization disturb and degrade your appreciation of the piece?\n",
      "\t \t1 (Not at all)\n",
      "\t \t2\n",
      "\t \t3\n",
      "\t \t4\n",
      "\t \t5 (Totally)\n",
      "3484597 RADIO\n",
      "\t Qid: 3740393\n",
      "\t Q: Did the visualization enhance your experience of the music?\n",
      "\t \t1 (Not at all)\n",
      "\t \t2\n",
      "\t \t3\n",
      "\t \t4\n",
      "\t \t5 (Totally)\n",
      "3484598 RADIO\n",
      "\t Qid: 3740394\n",
      "\t Q: Did the visualization help you be more emotionally moved by the music?\n",
      "\t \t1 (Not at all)\n",
      "\t \t2\n",
      "\t \t3\n",
      "\t \t4\n",
      "\t \t5 (Totally)\n",
      "3484599 RADIO\n",
      "\t Qid: 3740395\n",
      "\t Q: Were you familiar with the music that you heard?\n",
      "\t \t1 (I have never heard it before)\n",
      "\t \t2\n",
      "\t \t3\n",
      "\t \t4\n",
      "\t \t5\n",
      "\t \t6\n",
      "\t \t7 (I am very familiar with the music)\n",
      "3484600 RADIO\n",
      "\t Qid: 3740396\n",
      "\t Q: Are you familiar with this style of music (Bach fugues)?\n",
      "\t \t1 (I have never heard it before)\n",
      "\t \t2\n",
      "\t \t3\n",
      "\t \t4\n",
      "\t \t5\n",
      "\t \t6\n",
      "\t \t7 (I am very familiar with this type of music)\n",
      "3484601 RADIO\n",
      "\t Qid: 3740397\n",
      "\t Q: Were you aware of your physical body during this piece?\n",
      "\t \tNot at all\n",
      "\t \tRarely\n",
      "\t \tIntermittently\n",
      "\t \tContinuously\n",
      "3484602 RADIO\n",
      "\t Qid: 3740398\n",
      "\t Q: Were you aware of your own physical movement during this piece? If so, how much did you move relative to your own usual behaviour at this kind of concert?\n",
      "\t \tNot aware of movement\n",
      "\t \tYes and I moved less than usual\n",
      "\t \tYes and I moved a normal amount\n",
      "\t \tYes and I moved more than usual\n",
      "3484603 RADIO\n",
      "\t Qid: 3740399\n",
      "\t Q: Were you aware of others in the audience moving during the piece? If so, how much were they moving relative to the usual behaviour you observe at this kind of concert?\n",
      "\t \tNot aware of others’ movement\n",
      "\t \tYes and they moved less than usual\n",
      "\t \tYes and they moved a normal amount\n",
      "\t \tYes and they moved more than usual\n"
     ]
    }
   ],
   "source": [
    "# checkout out the form structure\n",
    "elem = form_metadata['elements']\n",
    "\n",
    "for m in elem:\n",
    "    print(str(m['elementId']) + ' ' + m['elementType'])\n",
    "    if 'questions' in m: \n",
    "        for q in m['questions']:\n",
    "            print('\\t Qid: ' + str(q['questionId']))\n",
    "            print('\\t Q: ' + str(q['text']))\n",
    "            if 'answerOptions' in q:\n",
    "                for a in q['answerOptions']:\n",
    "                    print('\\t \\t' + a['text'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "29\n",
      "{'submissionId': 16670536, 'createdDate': '2021-10-26T00:00:00.000+0200', 'modifiedDate': '2021-10-26T00:00:00.000+0200', 'delivered': True, 'answerTime': 0, 'answers': [{'answerId': 102726077, 'questionId': 3740392, 'answerOptions': [{'answerOptionId': 8636659, 'sequence': 3, 'text': '3', 'preselected': False, 'correct': False}]}, {'answerId': 102726072, 'questionId': 3740394, 'answerOptions': [{'answerOptionId': 8636667, 'sequence': 1, 'text': '1 (Not at all)', 'preselected': False, 'correct': False}]}, {'answerId': 102726069, 'questionId': 3740398, 'answerOptions': [{'answerOptionId': 8636691, 'sequence': 2, 'text': 'Yes and I moved less than usual', 'preselected': False, 'correct': False}]}, {'answerId': 102726071, 'questionId': 3740395, 'answerOptions': [{'answerOptionId': 8636678, 'sequence': 7, 'text': '7 (I am very familiar with the music)', 'preselected': False, 'correct': False}]}, {'answerId': 102726074, 'questionId': 3740397, 'answerOptions': [{'answerOptionId': 8636688, 'sequence': 3, 'text': 'Intermittently', 'preselected': False, 'correct': False}]}, {'answerId': 102726068, 'questionId': 3740387, 'textAnswer': '09d8163d-80e7-fda3-6919-6a3febc78e27'}, {'answerId': 102726070, 'questionId': 3740391, 'answerOptions': [{'answerOptionId': 8636652, 'sequence': 1, 'text': '1 (Not at all)', 'preselected': False, 'correct': False}]}, {'answerId': 102726075, 'questionId': 3740396, 'answerOptions': [{'answerOptionId': 8636685, 'sequence': 7, 'text': '7 (I am very familiar with this type of music)', 'preselected': False, 'correct': False}]}, {'answerId': 102726073, 'questionId': 3740393, 'answerOptions': [{'answerOptionId': 8636663, 'sequence': 2, 'text': '2', 'preselected': False, 'correct': False}]}, {'answerId': 102726067, 'questionId': 3740399, 'answerOptions': [{'answerOptionId': 8636694, 'sequence': 1, 'text': 'Not aware of others’ movement', 'preselected': False, 'correct': False}]}, {'answerId': 102726066, 'questionId': 3740389, 'answerOptions': [{'answerOptionId': 8636644, 'sequence': 3, 'text': '3', 'preselected': False, 'correct': False}]}, {'answerId': 102726076, 'questionId': 3740390, 'answerOptions': [{'answerOptionId': 8636649, 'sequence': 3, 'text': '3', 'preselected': False, 'correct': False}]}, {'answerId': 102726078, 'questionId': 3740388, 'answerOptions': [{'answerOptionId': 8636640, 'sequence': 4, 'text': '4', 'preselected': False, 'correct': False}]}]}\n"
     ]
    }
   ],
   "source": [
    "# checking out the form of responses\n",
    "\n",
    "formID = formes[9]\n",
    "request_url = 'https://nettskjema.no/api/v2/forms/' + str(formID) + '/submissions?fromSubmissionId=1'\n",
    "response = session.get(request_url)\n",
    "subIDs = json.loads(response.content.decode())#eval(response.content.decode())\n",
    "print(len(subIDs))\n",
    "if len(subIDs)<5:\n",
    "    print(subIDs)\n",
    "else:\n",
    "    print(subIDs[0])\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>submissionId</th>\n",
       "      <th>createdDate</th>\n",
       "      <th>modifiedDate</th>\n",
       "      <th>delivered</th>\n",
       "      <th>answerTime</th>\n",
       "      <th>answers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16670536</td>\n",
       "      <td>2021-10-26T00:00:00.000+0200</td>\n",
       "      <td>2021-10-26T00:00:00.000+0200</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'answerId': 102726077, 'questionId': 3740392...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16666325</td>\n",
       "      <td>2021-10-26T00:00:00.000+0200</td>\n",
       "      <td>2021-10-26T00:00:00.000+0200</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'answerId': 102712401, 'questionId': 3740398...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16666281</td>\n",
       "      <td>2021-10-26T00:00:00.000+0200</td>\n",
       "      <td>2021-10-26T00:00:00.000+0200</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'answerId': 102712298, 'questionId': 3740387...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16666279</td>\n",
       "      <td>2021-10-26T00:00:00.000+0200</td>\n",
       "      <td>2021-10-26T00:00:00.000+0200</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>[{'answerId': 102712284, 'questionId': 3740399...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   submissionId                   createdDate                  modifiedDate  \\\n",
       "0      16670536  2021-10-26T00:00:00.000+0200  2021-10-26T00:00:00.000+0200   \n",
       "1      16666325  2021-10-26T00:00:00.000+0200  2021-10-26T00:00:00.000+0200   \n",
       "2      16666281  2021-10-26T00:00:00.000+0200  2021-10-26T00:00:00.000+0200   \n",
       "3      16666279  2021-10-26T00:00:00.000+0200  2021-10-26T00:00:00.000+0200   \n",
       "\n",
       "   delivered  answerTime                                            answers  \n",
       "0       True           0  [{'answerId': 102726077, 'questionId': 3740392...  \n",
       "1       True           0  [{'answerId': 102712401, 'questionId': 3740398...  \n",
       "2       True           0  [{'answerId': 102712298, 'questionId': 3740387...  \n",
       "3       True           0  [{'answerId': 102712284, 'questionId': 3740399...  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first layer of organisation into pandas\n",
    "df = pd.read_json(response.content.decode())\n",
    "# first flush, answers hid a wealth of information. Would be good to get that broken out \n",
    "df.loc[:3,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'answerId': 102726077,\n",
       "  'questionId': 3740392,\n",
       "  'answerOptions': [{'answerOptionId': 8636659,\n",
       "    'sequence': 3,\n",
       "    'text': '3',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726072,\n",
       "  'questionId': 3740394,\n",
       "  'answerOptions': [{'answerOptionId': 8636667,\n",
       "    'sequence': 1,\n",
       "    'text': '1 (Not at all)',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726069,\n",
       "  'questionId': 3740398,\n",
       "  'answerOptions': [{'answerOptionId': 8636691,\n",
       "    'sequence': 2,\n",
       "    'text': 'Yes and I moved less than usual',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726071,\n",
       "  'questionId': 3740395,\n",
       "  'answerOptions': [{'answerOptionId': 8636678,\n",
       "    'sequence': 7,\n",
       "    'text': '7 (I am very familiar with the music)',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726074,\n",
       "  'questionId': 3740397,\n",
       "  'answerOptions': [{'answerOptionId': 8636688,\n",
       "    'sequence': 3,\n",
       "    'text': 'Intermittently',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726068,\n",
       "  'questionId': 3740387,\n",
       "  'textAnswer': '09d8163d-80e7-fda3-6919-6a3febc78e27'},\n",
       " {'answerId': 102726070,\n",
       "  'questionId': 3740391,\n",
       "  'answerOptions': [{'answerOptionId': 8636652,\n",
       "    'sequence': 1,\n",
       "    'text': '1 (Not at all)',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726075,\n",
       "  'questionId': 3740396,\n",
       "  'answerOptions': [{'answerOptionId': 8636685,\n",
       "    'sequence': 7,\n",
       "    'text': '7 (I am very familiar with this type of music)',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726073,\n",
       "  'questionId': 3740393,\n",
       "  'answerOptions': [{'answerOptionId': 8636663,\n",
       "    'sequence': 2,\n",
       "    'text': '2',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726067,\n",
       "  'questionId': 3740399,\n",
       "  'answerOptions': [{'answerOptionId': 8636694,\n",
       "    'sequence': 1,\n",
       "    'text': 'Not aware of others’ movement',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726066,\n",
       "  'questionId': 3740389,\n",
       "  'answerOptions': [{'answerOptionId': 8636644,\n",
       "    'sequence': 3,\n",
       "    'text': '3',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726076,\n",
       "  'questionId': 3740390,\n",
       "  'answerOptions': [{'answerOptionId': 8636649,\n",
       "    'sequence': 3,\n",
       "    'text': '3',\n",
       "    'preselected': False,\n",
       "    'correct': False}]},\n",
       " {'answerId': 102726078,\n",
       "  'questionId': 3740388,\n",
       "  'answerOptions': [{'answerOptionId': 8636640,\n",
       "    'sequence': 4,\n",
       "    'text': '4',\n",
       "    'preselected': False,\n",
       "    'correct': False}]}]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# single response content\n",
    "df.loc[0,'answers']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}