Data_Selection_Creation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle as p\n",
    "import metadatabase as mdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2018-2019\n",
    "start = 1523600000.0\n",
    "stop = 1556800000.0\n",
    "\n",
    "# 2021-2022\n",
    "#start = 1634800000.0\n",
    "#stop = 1639400000.0\n",
    "\n",
    "channel = 'EW'\n",
    "instrument = '100MHz'\n",
    "integrity = 1\n",
    "quality = 1\n",
    "completeness = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "category = 'Antenna'\n",
    "file_aliases = ('pol0', 'pol1', 'time_sys_start', 'time_sys_stop')\n",
    "\n",
    "subquery = (\"SELECT   DataFiles.data_directory \"\n",
    "            \"FROM     DataDirectories \"\n",
    "            \"JOIN     DataCategories \"\n",
    "            \"ON       DataDirectories.data_category = DataCategories.data_category \"\n",
    "           f\"AND      DataCategories.category_name = '{category}' \"\n",
    "            \"JOIN     DataFiles \"\n",
    "            \"ON       DataDirectories.data_directory = DataFiles.data_directory \"\n",
    "           f\"AND      DataDirectories.time_start <= {stop} \"\n",
    "           f\"AND      DataDirectories.time_stop >= {start} \"\n",
    "           f\"AND      DataDirectories.directory_completeness IS {completeness} \"\n",
    "            \"JOIN     ChannelGroups \"\n",
    "            \"ON       DataFiles.channel_group = ChannelGroups.channel_group \"\n",
    "            \"JOIN     ChannelOrientations \"\n",
    "            \"ON       ChannelOrientations.channel_orientation = ChannelGroups.channel_orientation \"\n",
    "           f\"AND      ChannelOrientations.orientation_name = '{channel}' \"\n",
    "            \"JOIN     DataTypes \"\n",
    "            \"ON       DataFiles.data_file = DataTypes.data_file \"\n",
    "           f\"AND      DataTypes.file_alias IN {file_aliases} \"\n",
    "           f\"AND      DataFiles.data_integrity IS {integrity} \"\n",
    "           f\"AND      DataFiles.data_quality IS {quality} \"\n",
    "            \"JOIN     ArrayElements \"\n",
    "            \"ON       ArrayElements.array_element = ChannelGroups.array_element \"\n",
    "           f\"AND      ArrayElements.element_name = '{instrument}' \"\n",
    "            \"GROUP BY DataFiles.data_directory \"\n",
    "            \"HAVING   SUM(DataFiles.data_quality) = 3 \")\n",
    "\n",
    "query = (\"SELECT ArrayElements.element_name AS classification_name, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_name LIKE '%.scio%' THEN 'scio' \"\n",
    "         \"            WHEN DataTypes.file_name LIKE '%.raw%' THEN 'raw' \"\n",
    "         \"       END \"\n",
    "         \"       AS file_extension, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'time\\_sys\\_%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'pol%' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[ABNE]%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[ABNE]%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'time\\_%\\_sys\\_therms' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataCategories.category_name = 'Switch' THEN DataCategories.category_name \"\n",
    "         \"            ELSE 'Housekeeping' \"\n",
    "         \"       END \"\n",
    "         \"       AS subclassification_name, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'pol%' THEN 'pol' \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[AB]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 8) || SUBSTR(DataTypes.file_alias, 10, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[NE]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 8) || SUBSTR(DataTypes.file_alias, 11, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[AB]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 7) || SUBSTR(DataTypes.file_alias, 9, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[NE]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 7) || SUBSTR(DataTypes.file_alias, 10, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            ELSE DataTypes.file_alias \"\n",
    "         \"       END \"\n",
    "         \"       AS file_alias, \"\n",
    "         \"       DataTypes.data_type AS data_type \"\n",
    "         \"FROM   DataDirectories \"\n",
    "         \"JOIN   DataCategories \"\n",
    "         \"ON     DataDirectories.data_category = DataCategories.data_category \"\n",
    "        f\"AND    DataDirectories.data_directory IN ({subquery}) \"\n",
    "         \"JOIN   DataFiles \"\n",
    "         \"ON     DataDirectories.data_directory = DataFiles.data_directory \"\n",
    "        f\"AND    DataTypes.file_alias IN {file_aliases} \"\n",
    "         \"JOIN   DataTypes \"\n",
    "         \"ON     DataFiles.data_file = DataTypes.data_file \"\n",
    "         \"JOIN   ChannelGroups \"\n",
    "         \"ON     DataFiles.channel_group = ChannelGroups.channel_group \"\n",
    "         \"JOIN   ChannelOrientations \"\n",
    "         \"ON     ChannelOrientations.channel_orientation = ChannelGroups.channel_orientation \"\n",
    "        f\"AND    ChannelOrientations.orientation_name = '{channel}' \"\n",
    "         \"JOIN   ArrayElements \"\n",
    "         \"ON     ArrayElements.array_element = ChannelGroups.array_element \"\n",
    "        f\"AND    ArrayElements.element_name = '{instrument}' \")\n",
    "\n",
    "\n",
    "category = 'Switch'\n",
    "file_aliases = ('antenna', 'res50', 'res100', 'short', 'noise')\n",
    "\n",
    "subquery = (\"SELECT   DataFiles.data_directory \"\n",
    "            \"FROM     DataDirectories \"\n",
    "            \"JOIN     DataCategories \"\n",
    "            \"ON       DataDirectories.data_category = DataCategories.data_category \"\n",
    "           f\"AND      DataCategories.category_name = '{category}' \"\n",
    "            \"JOIN     DataFiles \"\n",
    "            \"ON       DataDirectories.data_directory = DataFiles.data_directory \"\n",
    "           f\"AND      DataDirectories.time_start <= {stop} \"\n",
    "           f\"AND      DataDirectories.time_stop >= {start} \"\n",
    "           f\"AND      DataDirectories.directory_completeness IS {completeness} \"\n",
    "            \"JOIN     ChannelGroups \"\n",
    "            \"ON       DataFiles.channel_group = ChannelGroups.channel_group \"\n",
    "            \"JOIN     ChannelOrientations \"\n",
    "            \"ON       ChannelOrientations.channel_orientation = ChannelGroups.channel_orientation \"\n",
    "           f\"AND      ChannelOrientations.orientation_name = '{channel}' \"\n",
    "            \"JOIN     DataTypes \"\n",
    "            \"ON       DataFiles.data_file = DataTypes.data_file \"\n",
    "           f\"AND      DataTypes.file_alias IN {file_aliases} \"\n",
    "           f\"AND      DataFiles.data_integrity IS {integrity} \"\n",
    "           f\"AND      DataFiles.data_quality IS {quality} \"\n",
    "            \"JOIN     ArrayElements \"\n",
    "            \"ON       ArrayElements.array_element = ChannelGroups.array_element \"\n",
    "           f\"AND      ArrayElements.element_name = '{instrument}' \"\n",
    "            \"GROUP BY DataFiles.data_directory \"\n",
    "            \"HAVING   SUM(DataFiles.data_quality) = 5 \")\n",
    "\n",
    "query = (\"SELECT ArrayElements.element_name AS classification_name, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_name LIKE '%.scio%' THEN 'scio' \"\n",
    "         \"            WHEN DataTypes.file_name LIKE '%.raw%' THEN 'raw' \"\n",
    "         \"       END \"\n",
    "         \"       AS file_extension, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'time\\_sys\\_%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'pol%' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[ABNE]%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[ABNE]%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'time\\_%\\_sys\\_therms' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataCategories.category_name = 'Switch' THEN DataCategories.category_name \"\n",
    "         \"            ELSE 'Housekeeping' \"\n",
    "         \"       END \"\n",
    "         \"       AS subclassification_name, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'pol%' THEN 'pol' \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[AB]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 8) || SUBSTR(DataTypes.file_alias, 10, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[NE]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 8) || SUBSTR(DataTypes.file_alias, 11, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[AB]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 7) || SUBSTR(DataTypes.file_alias, 9, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[NE]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 7) || SUBSTR(DataTypes.file_alias, 10, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            ELSE DataTypes.file_alias \"\n",
    "         \"       END \"\n",
    "         \"       AS file_alias, \"\n",
    "         \"       DataTypes.data_type AS data_type \"\n",
    "         \"FROM   DataDirectories \"\n",
    "         \"JOIN   DataCategories \"\n",
    "         \"ON     DataDirectories.data_category = DataCategories.data_category \"\n",
    "        f\"AND    DataDirectories.data_directory IN ({subquery}) \"\n",
    "         \"JOIN   DataFiles \"\n",
    "         \"ON     DataDirectories.data_directory = DataFiles.data_directory \"\n",
    "        f\"AND    DataTypes.file_alias IN {file_aliases} \"\n",
    "         \"JOIN   DataTypes \"\n",
    "         \"ON     DataFiles.data_file = DataTypes.data_file \"\n",
    "         \"JOIN   ChannelGroups \"\n",
    "         \"ON     DataFiles.channel_group = ChannelGroups.channel_group \"\n",
    "         \"JOIN   ChannelOrientations \"\n",
    "         \"ON     ChannelOrientations.channel_orientation = ChannelGroups.channel_orientation \"\n",
    "        f\"AND    ChannelOrientations.orientation_name = '{channel}' \"\n",
    "         \"JOIN   ArrayElements \"\n",
    "         \"ON     ArrayElements.array_element = ChannelGroups.array_element \"\n",
    "        f\"AND    ArrayElements.element_name = '{instrument}' \") + \"UNION ALL \" + query\n",
    "\n",
    "query = \"SELECT classification_name, subclassification_name, file_extension, file_alias, data_type, COUNT(file_alias) FROM (\" + query + \") GROUP BY classification_name, subclassification_name, file_alias\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "count_result_set = mdb.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "category = 'Antenna'\n",
    "file_aliases = ('pol0', 'pol1', 'time_sys_start', 'time_sys_stop')\n",
    "\n",
    "subquery = (\"SELECT   DataFiles.data_directory \"\n",
    "            \"FROM     DataDirectories \"\n",
    "            \"JOIN     DataCategories \"\n",
    "            \"ON       DataDirectories.data_category = DataCategories.data_category \"\n",
    "           f\"AND      DataCategories.category_name = '{category}' \"\n",
    "            \"JOIN     DataFiles \"\n",
    "            \"ON       DataDirectories.data_directory = DataFiles.data_directory \"\n",
    "           f\"AND      DataDirectories.time_start <= {stop} \"\n",
    "           f\"AND      DataDirectories.time_stop >= {start} \"\n",
    "           f\"AND      DataDirectories.directory_completeness IS {completeness} \"\n",
    "            \"JOIN     ChannelGroups \"\n",
    "            \"ON       DataFiles.channel_group = ChannelGroups.channel_group \"\n",
    "            \"JOIN     ChannelOrientations \"\n",
    "            \"ON       ChannelOrientations.channel_orientation = ChannelGroups.channel_orientation \"\n",
    "           f\"AND      ChannelOrientations.orientation_name = '{channel}' \"\n",
    "            \"JOIN     DataTypes \"\n",
    "            \"ON       DataFiles.data_file = DataTypes.data_file \"\n",
    "           f\"AND      DataTypes.file_alias IN {file_aliases} \"\n",
    "           f\"AND      DataFiles.data_integrity IS {integrity} \"\n",
    "           f\"AND      DataFiles.data_quality IS {quality} \"\n",
    "            \"JOIN     ArrayElements \"\n",
    "            \"ON       ArrayElements.array_element = ChannelGroups.array_element \"\n",
    "           f\"AND      ArrayElements.element_name = '{instrument}' \"\n",
    "            \"GROUP BY DataFiles.data_directory \"\n",
    "            \"HAVING   SUM(DataFiles.data_quality) = 3 \")\n",
    "\n",
    "query = (\"SELECT ArrayElements.element_name AS classification_name, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_name LIKE '%.scio%' THEN 'scio' \"\n",
    "         \"            WHEN DataTypes.file_name LIKE '%.raw%' THEN 'raw' \"\n",
    "         \"       END \"\n",
    "         \"       AS file_extension, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'time\\_sys\\_%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'pol%' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[ABNE]%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[ABNE]%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'time\\_%\\_sys\\_therms' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataCategories.category_name = 'Switch' THEN DataCategories.category_name \"\n",
    "         \"            ELSE 'Housekeeping' \"\n",
    "         \"       END \"\n",
    "         \"       AS subclassification_name, \"\n",
    "         \"       DataDirectories.directory_address || '/' || DataTypes.file_name AS file_path, \"\n",
    "         \"       DataDirectories.time_start AS time_start, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'pol%' THEN 'pol' \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[AB]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 8) || SUBSTR(DataTypes.file_alias, 10, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[NE]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 8) || SUBSTR(DataTypes.file_alias, 11, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[AB]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 7) || SUBSTR(DataTypes.file_alias, 9, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[NE]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 7) || SUBSTR(DataTypes.file_alias, 10, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            ELSE DataTypes.file_alias \"\n",
    "         \"       END \"\n",
    "         \"       AS file_alias, \"\n",
    "         \"       DataTypes.data_type AS data_type \"\n",
    "         \"FROM   DataDirectories \"\n",
    "         \"JOIN   DataCategories \"\n",
    "         \"ON     DataDirectories.data_category = DataCategories.data_category \"\n",
    "        f\"AND    DataDirectories.data_directory IN ({subquery}) \"\n",
    "         \"JOIN   DataFiles \"\n",
    "         \"ON     DataDirectories.data_directory = DataFiles.data_directory \"\n",
    "        f\"AND    DataTypes.file_alias IN {file_aliases} \"\n",
    "         \"JOIN   DataTypes \"\n",
    "         \"ON     DataFiles.data_file = DataTypes.data_file \"\n",
    "         \"JOIN   ChannelGroups \"\n",
    "         \"ON     DataFiles.channel_group = ChannelGroups.channel_group \"\n",
    "         \"JOIN   ChannelOrientations \"\n",
    "         \"ON     ChannelOrientations.channel_orientation = ChannelGroups.channel_orientation \"\n",
    "        f\"AND    ChannelOrientations.orientation_name = '{channel}' \"\n",
    "         \"JOIN   ArrayElements \"\n",
    "         \"ON     ArrayElements.array_element = ChannelGroups.array_element \"\n",
    "        f\"AND    ArrayElements.element_name = '{instrument}' \")\n",
    "\n",
    "\n",
    "category = 'Switch'\n",
    "file_aliases = ('antenna', 'res50', 'res100', 'short', 'noise')\n",
    "\n",
    "subquery = (\"SELECT   DataFiles.data_directory \"\n",
    "            \"FROM     DataDirectories \"\n",
    "            \"JOIN     DataCategories \"\n",
    "            \"ON       DataDirectories.data_category = DataCategories.data_category \"\n",
    "           f\"AND      DataCategories.category_name = '{category}' \"\n",
    "            \"JOIN     DataFiles \"\n",
    "            \"ON       DataDirectories.data_directory = DataFiles.data_directory \"\n",
    "           f\"AND      DataDirectories.time_start <= {stop} \"\n",
    "           f\"AND      DataDirectories.time_stop >= {start} \"\n",
    "           f\"AND      DataDirectories.directory_completeness IS {completeness} \"\n",
    "            \"JOIN     ChannelGroups \"\n",
    "            \"ON       DataFiles.channel_group = ChannelGroups.channel_group \"\n",
    "            \"JOIN     ChannelOrientations \"\n",
    "            \"ON       ChannelOrientations.channel_orientation = ChannelGroups.channel_orientation \"\n",
    "           f\"AND      ChannelOrientations.orientation_name = '{channel}' \"\n",
    "            \"JOIN     DataTypes \"\n",
    "            \"ON       DataFiles.data_file = DataTypes.data_file \"\n",
    "           f\"AND      DataTypes.file_alias IN {file_aliases} \"\n",
    "           f\"AND      DataFiles.data_integrity IS {integrity} \"\n",
    "           f\"AND      DataFiles.data_quality IS {quality} \"\n",
    "            \"JOIN     ArrayElements \"\n",
    "            \"ON       ArrayElements.array_element = ChannelGroups.array_element \"\n",
    "           f\"AND      ArrayElements.element_name = '{instrument}' \"\n",
    "            \"GROUP BY DataFiles.data_directory \"\n",
    "            \"HAVING   SUM(DataFiles.data_quality) = 5 \")\n",
    "\n",
    "query = (\"SELECT ArrayElements.element_name AS classification_name, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_name LIKE '%.scio%' THEN 'scio' \"\n",
    "         \"            WHEN DataTypes.file_name LIKE '%.raw%' THEN 'raw' \"\n",
    "         \"       END \"\n",
    "         \"       AS file_extension, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'time\\_sys\\_%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'pol%' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[ABNE]%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[ABNE]%' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'time\\_%\\_sys\\_therms' ESCAPE '\\\\' THEN ChannelOrientations.orientation_name \"\n",
    "         \"            WHEN DataCategories.category_name = 'Switch' THEN DataCategories.category_name \"\n",
    "         \"            ELSE 'Housekeeping' \"\n",
    "         \"       END \"\n",
    "         \"       AS subclassification_name, \"\n",
    "         \"       DataDirectories.directory_address || '/' || DataTypes.file_name AS file_path, \"\n",
    "         \"       DataDirectories.time_start AS time_start, \"\n",
    "         \"       CASE \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'pol%' THEN 'pol' \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[AB]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 8) || SUBSTR(DataTypes.file_alias, 10, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_100[NE]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 8) || SUBSTR(DataTypes.file_alias, 11, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[AB]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 7) || SUBSTR(DataTypes.file_alias, 9, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            WHEN DataTypes.file_alias LIKE 'temp\\_70[NE]%' ESCAPE '\\\\' THEN SUBSTR(DataTypes.file_alias, 1, 7) || SUBSTR(DataTypes.file_alias, 10, LENGTH(DataTypes.file_alias)) \"\n",
    "         \"            ELSE DataTypes.file_alias \"\n",
    "         \"       END \"\n",
    "         \"       AS file_alias, \"\n",
    "         \"       DataTypes.data_type AS data_type \"\n",
    "         \"FROM   DataDirectories \"\n",
    "         \"JOIN   DataCategories \"\n",
    "         \"ON     DataDirectories.data_category = DataCategories.data_category \"\n",
    "        f\"AND    DataDirectories.data_directory IN ({subquery}) \"\n",
    "         \"JOIN   DataFiles \"\n",
    "         \"ON     DataDirectories.data_directory = DataFiles.data_directory \"\n",
    "        f\"AND    DataTypes.file_alias IN {file_aliases} \"\n",
    "         \"JOIN   DataTypes \"\n",
    "         \"ON     DataFiles.data_file = DataTypes.data_file \"\n",
    "         \"JOIN   ChannelGroups \"\n",
    "         \"ON     DataFiles.channel_group = ChannelGroups.channel_group \"\n",
    "         \"JOIN   ChannelOrientations \"\n",
    "         \"ON     ChannelOrientations.channel_orientation = ChannelGroups.channel_orientation \"\n",
    "        f\"AND    ChannelOrientations.orientation_name = '{channel}' \"\n",
    "         \"JOIN   ArrayElements \"\n",
    "         \"ON     ArrayElements.array_element = ChannelGroups.array_element \"\n",
    "        f\"AND    ArrayElements.element_name = '{instrument}' \") + \"UNION \" + query\n",
    "\n",
    "query = \"SELECT classification_name, subclassification_name, file_path, file_extension, file_alias, data_type FROM (\" + query + \") ORDER BY classification_name, subclassification_name, time_start\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "locate_result_set = mdb.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "p.dump((count_result_set, locate_result_set), open(f'2018_{instrument}_{channel}_Prototyping.p', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#NOTE THAT NS AND EW ARE MIXED UP FOR 2022 "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}