kowalski_query_examples.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ffc828a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from penquins import Kowalski\n",
    "from scope.utils import write_parquet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b131a83e",
   "metadata": {},
   "source": [
    "# Authenticate kowalski, gloria, melman (first time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5ce4fa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "kowalski = Kowalski(username = '',\n",
    "                    password = '',\n",
    "                    host = 'kowalski.caltech.edu',\n",
    "                   )\n",
    "\n",
    "\n",
    "gloria = Kowalski(username = '',\n",
    "                  password = '',\n",
    "                  host = 'gloria.caltech.edu',\n",
    "                 )\n",
    "\n",
    "melman = Kowalski(username = '',\n",
    "                  password = '',\n",
    "                  host = 'melman.caltech.edu',\n",
    "                 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4696cfb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate tokens (no need to do this every time)\n",
    "k_token = kowalski.authenticate()\n",
    "g_token = gloria.authenticate()\n",
    "m_token = melman.authenticate()\n",
    "\n",
    "# Print these tokens to add them to your config.yaml file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "46d91a1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill in tokens here after first authentication\n",
    "tokens = {'kowalski': '', #k_token\n",
    "          'gloria': '', #g_token\n",
    "          'melman': '', #m_token\n",
    "         }"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56f96c9c",
   "metadata": {},
   "source": [
    "# Create kowalski, gloria, melman instances using token"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "095422bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "hosts = ['kowalski', 'gloria', 'melman']\n",
    "\n",
    "instances = {\n",
    "    host: {\n",
    "        'protocol': 'https',\n",
    "        'port': 443,\n",
    "        'host': f'{host}.caltech.edu',\n",
    "        'token': tokens[host],\n",
    "    }\n",
    "    for host in hosts\n",
    "}\n",
    "\n",
    "kowalski_instances = Kowalski(instances=instances, timeout=610)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3c7fdda3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "kowalski True\n",
      "gloria True\n",
      "melman True\n"
     ]
    }
   ],
   "source": [
    "# Test connection\n",
    "for host in hosts:\n",
    "    print(host, kowalski_instances.ping(name=host))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91ac58de",
   "metadata": {},
   "source": [
    "# Get kowalski, gloria, melman catalog names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6ddd8634",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'melman': {'status': 'success', 'message': 'Successfully executed query', 'data': ['ZTF_sources_84525009', 'ZTF_sources_60187478', 'ZTF_sources_32604668', 'ZTF_sources_26553423', 'ZTF_sources_20230309', 'ZTF_source_features_DR16', 'ZTF_source_features_20201201', 'ZTF_source_classifications_DR5', 'ZTF_source_classifications_DR16', 'ZTF_public_sources_20210401', 'ZTF_ops', 'ZTF_exposures_84525009', 'ZTF_exposures_60187478', 'ZTF_exposures_32604668', 'ZTF_exposures_26553423', 'ZTF_exposures_20230309', 'ZTF_alerts', 'WNTR_alerts', 'VLASS_DR1', 'TURBO_alerts', 'TNS', 'PTF_sources', 'PTF_exposures', 'PS1_DR1', 'PGIR_alerts', 'IGAPS_DR2', 'Gaia_EDR3', 'GALEX', 'AllWISE']}}\n"
     ]
    }
   ],
   "source": [
    "query = {\n",
    "    \"query_type\": \"info\",\n",
    "    \"query\": {\n",
    "        \"command\": \"catalog_names\",\n",
    "    }\n",
    "}\n",
    "\n",
    "# For queries without a catalog, specify the instance name\n",
    "\n",
    "response_k = kowalski_instances.query(query=query, name='kowalski')\n",
    "data_k = response_k.get(\"kowalski\").get(\"data\")\n",
    "\n",
    "response_g = kowalski_instances.query(query=query, name='gloria')\n",
    "data_g = response_g.get(\"gloria\").get(\"data\")\n",
    "\n",
    "response_m = kowalski_instances.query(query=query, name='melman')\n",
    "data_m = response_m.get(\"melman\").get(\"data\")\n",
    "\n",
    "print(response_m)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f2cfde10",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(['skymaps',\n",
       "  'sdss_ellipticals',\n",
       "  'mzls_ellipticals',\n",
       "  'milliquas_v6',\n",
       "  'legacysurveys_photoz_DR7',\n",
       "  'legacysurveys_photoz_DR6',\n",
       "  'galaxy_redshifts_20200522',\n",
       "  'cfht_w3_photozs',\n",
       "  'ZUDS_alerts_aux',\n",
       "  'ZUDS_alerts',\n",
       "  'ZTF_sources_20200401',\n",
       "  'ZTF_source_features_20201201',\n",
       "  'ZTF_source_features_20200401_20_fields',\n",
       "  'ZTF_source_features_20191101_20_fields',\n",
       "  'ZTF_source_features_20191101',\n",
       "  'ZTF_source_classifications_dr2_20_fields',\n",
       "  'ZTF_source_classifications_dr2',\n",
       "  'ZTF_source_classifications_20191101',\n",
       "  'ZTF_ops',\n",
       "  'ZTF_exposures_20201201',\n",
       "  'ZTF_exposures_20200401',\n",
       "  'ZTF_exposures_20191101',\n",
       "  'ZTF_alerts_aux',\n",
       "  'ZTF_alerts',\n",
       "  'VLASS_DR1',\n",
       "  'TNS',\n",
       "  'TGSS_ADR1',\n",
       "  'RFC_2019d',\n",
       "  'RFC_2019a',\n",
       "  'PS1_STRM',\n",
       "  'PS1_DR1',\n",
       "  'PGIR_alerts_aux',\n",
       "  'PGIR_alerts',\n",
       "  'NVSS_41',\n",
       "  'NED_BetaV3',\n",
       "  'LAMOST_DR5_v3',\n",
       "  'LAMOST_DR4_v2',\n",
       "  'Known_lenses_20180901',\n",
       "  'IPHAS_DR2',\n",
       "  'IGAPS_DR2',\n",
       "  'Gaia_EDR3',\n",
       "  'Gaia_DR2_light_curves',\n",
       "  'Gaia_DR2_WD',\n",
       "  'Gaia_DR2_2MASS_best_neighbour',\n",
       "  'Gaia_DR2',\n",
       "  'GALEX',\n",
       "  'FIRST_20141217',\n",
       "  'CLU_20190625',\n",
       "  'CLU_20190406',\n",
       "  'CLU_20181213V2',\n",
       "  'AllWISE',\n",
       "  '2MASS_XSC',\n",
       "  '2MASS_PSC'],\n",
       " ['ZTF_source_features_DR5_20_fields',\n",
       "  'ZTF_source_features_DR5',\n",
       "  'ZTF_source_features_DR4_20_fields',\n",
       "  'ZTF_source_features_DR3',\n",
       "  'ZTF_source_features_20201201',\n",
       "  'ZTF_source_classifications_DR5',\n",
       "  'ZTF_public_sources_20210401',\n",
       "  'ZTF_ops',\n",
       "  'ZTF_exposures_86625709',\n",
       "  'ZTF_exposures_22993824',\n",
       "  'ZTF_exposures_20210401',\n",
       "  'ZTF_alerts',\n",
       "  'WNTR_alerts',\n",
       "  'VLASS_DR1',\n",
       "  'TURBO_alerts',\n",
       "  'TNS',\n",
       "  'PTF_sources',\n",
       "  'PTF_exposures',\n",
       "  'PS1_DR1',\n",
       "  'PGIR_alerts',\n",
       "  'IGAPS_DR2',\n",
       "  'Gaia_EDR3',\n",
       "  'GALEX',\n",
       "  'AllWISE'],\n",
       " ['ZTF_sources_84525009',\n",
       "  'ZTF_sources_60187478',\n",
       "  'ZTF_sources_32604668',\n",
       "  'ZTF_sources_26553423',\n",
       "  'ZTF_sources_20230309',\n",
       "  'ZTF_source_features_DR16',\n",
       "  'ZTF_source_features_20201201',\n",
       "  'ZTF_source_classifications_DR5',\n",
       "  'ZTF_source_classifications_DR16',\n",
       "  'ZTF_public_sources_20210401',\n",
       "  'ZTF_ops',\n",
       "  'ZTF_exposures_84525009',\n",
       "  'ZTF_exposures_60187478',\n",
       "  'ZTF_exposures_32604668',\n",
       "  'ZTF_exposures_26553423',\n",
       "  'ZTF_exposures_20230309',\n",
       "  'ZTF_alerts',\n",
       "  'WNTR_alerts',\n",
       "  'VLASS_DR1',\n",
       "  'TURBO_alerts',\n",
       "  'TNS',\n",
       "  'PTF_sources',\n",
       "  'PTF_exposures',\n",
       "  'PS1_DR1',\n",
       "  'PGIR_alerts',\n",
       "  'IGAPS_DR2',\n",
       "  'Gaia_EDR3',\n",
       "  'GALEX',\n",
       "  'AllWISE'])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_k, data_g, data_m"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83390881",
   "metadata": {},
   "source": [
    "# Query instances for ZTF high-confidence IDs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d8bc15c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# For queries with a catalog, no need to specify instance name\n",
    "\n",
    "qry = kowalski_instances.query({\n",
    "            \"query_type\": \"find\", # find documents\n",
    "            \"query\": {\n",
    "            \"catalog\": \"ZTF_source_classifications_DR16\", # catalog name\n",
    "            \"filter\": {\n",
    "                \"$or\":[\n",
    "                    {'%s_xgb' % 'vnv': {'$gt': 0.99}}, # filter by vnv_xgb > 0.9 or vnv_dnn > 0.9\n",
    "                                {'%s_dnn' % 'vnv': {'$gt': 0.99}},\n",
    "                ]}\n",
    "            },\n",
    "            \"kwargs\": {\n",
    "                       \"max_time_ms\": 600000 # adjustable max time in milliseconds\n",
    "            },\n",
    "            \"projection\": {\"_id\": 1 # only return the ids\n",
    "                          }\n",
    "            })\n",
    "#data = qry.get('data')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ffab6961",
   "metadata": {},
   "source": [
    "#### Query runs too long and can time out - we have to query in batches "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d06f2a1",
   "metadata": {},
   "source": [
    "# Query instances for ZTF DR5 classifications"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6019ac4c",
   "metadata": {},
   "source": [
    "## Get estimated document count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6de7e91c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "58778905"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qry = kowalski_instances.query({\n",
    "    \"query_type\": \"estimated_document_count\",\n",
    "    \"query\": {\n",
    "    \"catalog\": \"ZTF_source_classifications_DR16\"\n",
    "        }\n",
    "    }\n",
    ")\n",
    "\n",
    "estimated_nlightcurves = qry.get('melman').get('data')\n",
    "estimated_nlightcurves"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0acb2bb3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "melman 58778905\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "58778905"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qry = kowalski_instances.query({\n",
    "    \"query_type\": \"estimated_document_count\",\n",
    "    \"query\": {\n",
    "    \"catalog\": \"ZTF_source_classifications_DR16\"\n",
    "        }\n",
    "    }\n",
    ")\n",
    "\n",
    "for instance_name in qry:\n",
    "    result = qry.get(instance_name).get('data')\n",
    "    print(instance_name, result)\n",
    "\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f009903a",
   "metadata": {},
   "source": [
    "## Get collection info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8f5c704f",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = kowalski_instances.query({\n",
    "    \"query_type\": \"info\",\n",
    "    \"query\": {\n",
    "    \"catalog\": \"ZTF_source_classifications_DR16\",\n",
    "        \"command\": \"catalog_info\"\n",
    "        }\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4819b24b",
   "metadata": {},
   "source": [
    "## Count number of high-vnv-confidence documents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f505884c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "643340"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qry = kowalski_instances.query({\n",
    "    \"query_type\": \"count_documents\",\n",
    "    \"query\": {\n",
    "    \"catalog\": \"ZTF_source_classifications_DR16\",\n",
    "    \"kwargs\": {\n",
    "                \"max_time_ms\": 600000\n",
    "            },\n",
    "    \"filter\": {\n",
    "        \"$or\":[\n",
    "                {'%s_xgb' % 'vnv': {'$gt': 0.99}},\n",
    "                {'%s_dnn' % 'vnv': {'$gt': 0.99}},\n",
    "        ]}\n",
    "    }\n",
    "})\n",
    "\n",
    "nlightcurves = qry.get('melman').get('data')\n",
    "nlightcurves"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b6c9af2",
   "metadata": {},
   "source": [
    "## Note ability to loop over all instances without specifically naming them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "58f27cdd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "melman 643340\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "643340"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qry = kowalski_instances.query({\n",
    "    \"query_type\": \"count_documents\",\n",
    "    \"query\": {\n",
    "    \"catalog\": \"ZTF_source_classifications_DR16\",\n",
    "    \"filter\": {\n",
    "        \"$or\":[\n",
    "                {'%s_xgb' % 'vnv': {'$gt': 0.99}},\n",
    "                {'%s_dnn' % 'vnv': {'$gt': 0.99}},\n",
    "        ]}\n",
    "    }\n",
    "})\n",
    "\n",
    "# If ZTF_source_classifications_DR5 were on multiple instances, queries could be split between them\n",
    "nlightcurves = 0\n",
    "\n",
    "# Loop over instances, updating quantity of interest\n",
    "for instance_name in qry:\n",
    "    result = qry.get(instance_name).get('data')\n",
    "    nlightcurves += result\n",
    "    print(instance_name, result)\n",
    "\n",
    "nlightcurves"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c18c0ce",
   "metadata": {},
   "source": [
    "# Get scores using batch query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "fe93b045",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "65:\n",
      "0\n",
      "1\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "53\n",
      "54\n",
      "55\n",
      "56\n",
      "57\n",
      "58\n",
      "59\n",
      "60\n",
      "61\n",
      "62\n",
      "63\n",
      "64\n"
     ]
    }
   ],
   "source": [
    "ids = []\n",
    "batch_size=10000\n",
    "nb = np.ceil(nlightcurves/batch_size).astype(int)\n",
    "df_all = []\n",
    "\n",
    "print(f'{nb}:')\n",
    "for n in range(nb):\n",
    "    print(n)\n",
    "    qry = kowalski_instances.query({\n",
    "        \"query_type\": \"find\",\n",
    "        \"query\": {\n",
    "            \"catalog\": \"ZTF_source_classifications_DR16\",\n",
    "            \"filter\": {\n",
    "                \"$or\":[\n",
    "                    {'%s_xgb' % 'vnv': {'$gt': 0.99}},\n",
    "                    {'%s_dnn' % 'vnv': {'$gt': 0.99}},\n",
    "                ]}\n",
    "            },\n",
    "        \"kwargs\": {\"skip\": int(n*batch_size),\n",
    "                   \"limit\": int(batch_size),\n",
    "                   \"max_time_ms\": 600000\n",
    "            }\n",
    "        })\n",
    "    data = qry.get('melman').get('data')\n",
    "    df_tmp = pd.DataFrame.from_records(data)\n",
    "    df_all += [df_tmp]\n",
    "    \n",
    "df_scores = pd.concat(df_all).reset_index(drop=True)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "91db76f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': 10488352164728,\n",
       " 'Gaia_EDR3___id': 4292955537630067072,\n",
       " 'AllWISE___id': 0,\n",
       " 'PS1_DR1___id': 114232913156860688,\n",
       " 'ra': 291.3158181,\n",
       " 'dec': 5.1918421,\n",
       " 'period': 402.2276744186922,\n",
       " 'field': 488,\n",
       " 'ccd': 9,\n",
       " 'quad': 4,\n",
       " 'filter': 2,\n",
       " 'e_dnn': 0.0,\n",
       " 'cv_dnn': 0.0,\n",
       " 'el_dnn': 0.0,\n",
       " 'osarg_dnn': 0.0,\n",
       " 'ea_dnn': 0.75,\n",
       " 'rrblz_dnn': 0.0,\n",
       " 'ceph2_dnn': 0.0,\n",
       " 'bis_dnn': 0.0,\n",
       " 'puls_dnn': 0.009999999776482582,\n",
       " 'agn_dnn': 0.0,\n",
       " 'longt_dnn': 0.0,\n",
       " 'mir_dnn': 0.0,\n",
       " 'dscu_dnn': 0.0,\n",
       " 'rrc_dnn': 0.0,\n",
       " 'eb_dnn': 0.0,\n",
       " 'wp_dnn': 0.0,\n",
       " 'rrd_dnn': 0.0,\n",
       " 'hp_dnn': 0.009999999776482582,\n",
       " 'blend_dnn': 0.0,\n",
       " 'wvir_dnn': 0.0,\n",
       " 'lpv_dnn': 0.0,\n",
       " 'emsms_dnn': 0.0,\n",
       " 'i_dnn': 0.0,\n",
       " 'rrlyr_dnn': 0.0,\n",
       " 'rrab_dnn': 0.0,\n",
       " 'sin_dnn': 0.0,\n",
       " 'rscvn_dnn': 0.0,\n",
       " 'bright_dnn': 0.0,\n",
       " 'bogus_dnn': 0.0,\n",
       " 'yso_dnn': 0.0,\n",
       " 'blyr_dnn': 0.0,\n",
       " 'dp_dnn': 0.0,\n",
       " 'saw_dnn': 0.17000000178813934,\n",
       " 'ceph_dnn': 0.0,\n",
       " 'blher_dnn': 0.0,\n",
       " 'ext_dnn': 0.0,\n",
       " 'wuma_dnn': 0.0,\n",
       " 'ew_dnn': 0.0,\n",
       " 'srv_dnn': 0.0,\n",
       " 'fla_dnn': 0.019999999552965164,\n",
       " 'vnv_dnn': 1.0,\n",
       " 'pnp_dnn': 0.36000001430511475,\n",
       " 'dip_dnn': 0.0,\n",
       " 'mp_dnn': 0.0,\n",
       " 'saw_xgb': 0.009999999776482582,\n",
       " 'dp_xgb': 0.029999999329447746,\n",
       " 'i_xgb': 0.27000001072883606,\n",
       " 'longt_xgb': 0.0,\n",
       " 'yso_xgb': 0.0,\n",
       " 'wvir_xgb': 0.009999999776482582,\n",
       " 'puls_xgb': 0.009999999776482582,\n",
       " 'sin_xgb': 0.0,\n",
       " 'dscu_xgb': 0.0,\n",
       " 'blend_xgb': 0.28999999165534973,\n",
       " 'dip_xgb': 0.0,\n",
       " 'mp_xgb': 0.0,\n",
       " 'pnp_xgb': 0.18000000715255737,\n",
       " 'vnv_xgb': 0.9300000071525574,\n",
       " 'rrblz_xgb': 0.07000000029802322,\n",
       " 'ew_xgb': 0.009999999776482582,\n",
       " 'osarg_xgb': 0.0,\n",
       " 'fla_xgb': 0.3799999952316284,\n",
       " 'srv_xgb': 0.0,\n",
       " 'ext_xgb': 0.0,\n",
       " 'ceph2_xgb': 0.0,\n",
       " 'bis_xgb': 0.0,\n",
       " 'blher_xgb': 0.0,\n",
       " 'e_xgb': 0.019999999552965164,\n",
       " 'ea_xgb': 0.029999999329447746,\n",
       " 'wuma_xgb': 0.0,\n",
       " 'ceph_xgb': 0.009999999776482582,\n",
       " 'el_xgb': 0.0,\n",
       " 'bright_xgb': 0.0,\n",
       " 'cv_xgb': 0.009999999776482582,\n",
       " 'lpv_xgb': 0.07000000029802322,\n",
       " 'emsms_xgb': 0.0,\n",
       " 'rrlyr_xgb': 0.019999999552965164,\n",
       " 'wp_xgb': 0.25,\n",
       " 'eb_xgb': 0.019999999552965164,\n",
       " 'hp_xgb': 0.0,\n",
       " 'blyr_xgb': 0.0,\n",
       " 'rscvn_xgb': 0.0,\n",
       " 'rrd_xgb': 0.0,\n",
       " 'mir_xgb': 0.0,\n",
       " 'rrc_xgb': 0.0,\n",
       " 'bogus_xgb': 0.019999999552965164,\n",
       " 'rrab_xgb': 0.28999999165534973,\n",
       " 'agn_xgb': 0.019999999552965164,\n",
       " 'coordinates': {'radec_str': ['19:25:15.7963', '05:11:30.632'],\n",
       "  'radec_geojson': {'type': 'Point', 'coordinates': [111.3158181, 5.1918421]}}}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine last entry of data\n",
    "data[-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8dd0120c",
   "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>_id</th>\n",
       "      <th>Gaia_EDR3___id</th>\n",
       "      <th>AllWISE___id</th>\n",
       "      <th>PS1_DR1___id</th>\n",
       "      <th>ra</th>\n",
       "      <th>dec</th>\n",
       "      <th>period</th>\n",
       "      <th>field</th>\n",
       "      <th>ccd</th>\n",
       "      <th>quad</th>\n",
       "      <th>...</th>\n",
       "      <th>hp_xgb</th>\n",
       "      <th>blyr_xgb</th>\n",
       "      <th>rscvn_xgb</th>\n",
       "      <th>rrd_xgb</th>\n",
       "      <th>mir_xgb</th>\n",
       "      <th>rrc_xgb</th>\n",
       "      <th>bogus_xgb</th>\n",
       "      <th>rrab_xgb</th>\n",
       "      <th>agn_xgb</th>\n",
       "      <th>coordinates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10487361000126</td>\n",
       "      <td>4285763524683958528</td>\n",
       "      <td>0</td>\n",
       "      <td>115642824603100448</td>\n",
       "      <td>282.460330</td>\n",
       "      <td>6.366678</td>\n",
       "      <td>0.432570</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.00</td>\n",
       "      <td>{'radec_str': ['18:49:50.4792', '06:22:00.040'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10487361000870</td>\n",
       "      <td>4285433881660412800</td>\n",
       "      <td>0</td>\n",
       "      <td>115562821782080352</td>\n",
       "      <td>282.178217</td>\n",
       "      <td>6.299935</td>\n",
       "      <td>1.260361</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.00</td>\n",
       "      <td>{'radec_str': ['18:48:42.7720', '06:17:59.766'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10487361001120</td>\n",
       "      <td>4285433709861720448</td>\n",
       "      <td>2825106001351041536</td>\n",
       "      <td>115532821501752224</td>\n",
       "      <td>282.150171</td>\n",
       "      <td>6.276463</td>\n",
       "      <td>110.444123</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.00</td>\n",
       "      <td>{'radec_str': ['18:48:36.0410', '06:16:35.268'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10487361002857</td>\n",
       "      <td>4285380486626796160</td>\n",
       "      <td>0</td>\n",
       "      <td>115362824160645344</td>\n",
       "      <td>282.416051</td>\n",
       "      <td>6.137439</td>\n",
       "      <td>1.538381</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.00</td>\n",
       "      <td>{'radec_str': ['18:49:39.8522', '06:08:14.780'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10487361003026</td>\n",
       "      <td>4285740164362451328</td>\n",
       "      <td>2825106001351030784</td>\n",
       "      <td>115352826534580096</td>\n",
       "      <td>282.653441</td>\n",
       "      <td>6.124625</td>\n",
       "      <td>0.021669</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>{'radec_str': ['18:50:36.8257', '06:07:28.652'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643335</th>\n",
       "      <td>10488352164538</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113772909831912880</td>\n",
       "      <td>290.983166</td>\n",
       "      <td>4.810384</td>\n",
       "      <td>0.047171</td>\n",
       "      <td>488</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.01</td>\n",
       "      <td>{'radec_str': ['19:23:55.9599', '04:48:37.383'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643336</th>\n",
       "      <td>10488352164566</td>\n",
       "      <td>4292715741020464768</td>\n",
       "      <td>0</td>\n",
       "      <td>113692917302599904</td>\n",
       "      <td>291.730300</td>\n",
       "      <td>4.749587</td>\n",
       "      <td>0.104595</td>\n",
       "      <td>488</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>{'radec_str': ['19:26:55.2720', '04:44:58.514'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643337</th>\n",
       "      <td>10488352164621</td>\n",
       "      <td>4294468328175322752</td>\n",
       "      <td>0</td>\n",
       "      <td>114582917148525424</td>\n",
       "      <td>291.714840</td>\n",
       "      <td>5.487507</td>\n",
       "      <td>0.176596</td>\n",
       "      <td>488</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.00</td>\n",
       "      <td>{'radec_str': ['19:26:51.5616', '05:29:15.024'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643338</th>\n",
       "      <td>10488352164635</td>\n",
       "      <td>4292978867891376128</td>\n",
       "      <td>0</td>\n",
       "      <td>114552914554329536</td>\n",
       "      <td>291.455392</td>\n",
       "      <td>5.466413</td>\n",
       "      <td>0.024923</td>\n",
       "      <td>488</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.00</td>\n",
       "      <td>{'radec_str': ['19:25:49.2942', '05:27:59.088'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643339</th>\n",
       "      <td>10488352164728</td>\n",
       "      <td>4292955537630067072</td>\n",
       "      <td>0</td>\n",
       "      <td>114232913156860688</td>\n",
       "      <td>291.315818</td>\n",
       "      <td>5.191842</td>\n",
       "      <td>402.227674</td>\n",
       "      <td>488</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.29</td>\n",
       "      <td>0.02</td>\n",
       "      <td>{'radec_str': ['19:25:15.7963', '05:11:30.632'...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>643340 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   _id       Gaia_EDR3___id         AllWISE___id   \n",
       "0       10487361000126  4285763524683958528                    0  \\\n",
       "1       10487361000870  4285433881660412800                    0   \n",
       "2       10487361001120  4285433709861720448  2825106001351041536   \n",
       "3       10487361002857  4285380486626796160                    0   \n",
       "4       10487361003026  4285740164362451328  2825106001351030784   \n",
       "...                ...                  ...                  ...   \n",
       "643335  10488352164538                    0                    0   \n",
       "643336  10488352164566  4292715741020464768                    0   \n",
       "643337  10488352164621  4294468328175322752                    0   \n",
       "643338  10488352164635  4292978867891376128                    0   \n",
       "643339  10488352164728  4292955537630067072                    0   \n",
       "\n",
       "              PS1_DR1___id          ra       dec      period  field  ccd   \n",
       "0       115642824603100448  282.460330  6.366678    0.432570    487   10  \\\n",
       "1       115562821782080352  282.178217  6.299935    1.260361    487   10   \n",
       "2       115532821501752224  282.150171  6.276463  110.444123    487   10   \n",
       "3       115362824160645344  282.416051  6.137439    1.538381    487   10   \n",
       "4       115352826534580096  282.653441  6.124625    0.021669    487   10   \n",
       "...                    ...         ...       ...         ...    ...  ...   \n",
       "643335  113772909831912880  290.983166  4.810384    0.047171    488    9   \n",
       "643336  113692917302599904  291.730300  4.749587    0.104595    488    9   \n",
       "643337  114582917148525424  291.714840  5.487507    0.176596    488    9   \n",
       "643338  114552914554329536  291.455392  5.466413    0.024923    488    9   \n",
       "643339  114232913156860688  291.315818  5.191842  402.227674    488    9   \n",
       "\n",
       "        quad  ...  hp_xgb  blyr_xgb  rscvn_xgb  rrd_xgb  mir_xgb  rrc_xgb   \n",
       "0          1  ...    0.01      0.00       0.00      0.0      0.0      0.0  \\\n",
       "1          1  ...    0.00      0.44       0.00      0.0      0.0      0.0   \n",
       "2          1  ...    0.01      0.00       0.00      0.0      0.0      0.0   \n",
       "3          1  ...    0.00      0.01       0.00      0.0      0.0      0.0   \n",
       "4          1  ...    0.01      0.01       0.03      0.0      0.0      0.0   \n",
       "...      ...  ...     ...       ...        ...      ...      ...      ...   \n",
       "643335     4  ...    0.00      0.00       0.00      0.0      0.0      0.0   \n",
       "643336     4  ...    0.00      0.00       0.00      0.0      0.0      0.0   \n",
       "643337     4  ...    0.01      0.00       0.00      0.0      0.0      0.0   \n",
       "643338     4  ...    0.00      0.00       0.00      0.0      0.0      0.0   \n",
       "643339     4  ...    0.00      0.00       0.00      0.0      0.0      0.0   \n",
       "\n",
       "        bogus_xgb  rrab_xgb  agn_xgb   \n",
       "0            0.02      0.40     0.00  \\\n",
       "1            0.00      0.19     0.00   \n",
       "2            0.05      0.33     0.00   \n",
       "3            0.00      0.22     0.00   \n",
       "4            0.01      0.20     0.00   \n",
       "...           ...       ...      ...   \n",
       "643335       0.00      0.19     0.01   \n",
       "643336       0.01      0.20     0.00   \n",
       "643337       0.00      0.19     0.00   \n",
       "643338       0.01      0.19     0.00   \n",
       "643339       0.02      0.29     0.02   \n",
       "\n",
       "                                              coordinates  \n",
       "0       {'radec_str': ['18:49:50.4792', '06:22:00.040'...  \n",
       "1       {'radec_str': ['18:48:42.7720', '06:17:59.766'...  \n",
       "2       {'radec_str': ['18:48:36.0410', '06:16:35.268'...  \n",
       "3       {'radec_str': ['18:49:39.8522', '06:08:14.780'...  \n",
       "4       {'radec_str': ['18:50:36.8257', '06:07:28.652'...  \n",
       "...                                                   ...  \n",
       "643335  {'radec_str': ['19:23:55.9599', '04:48:37.383'...  \n",
       "643336  {'radec_str': ['19:26:55.2720', '04:44:58.514'...  \n",
       "643337  {'radec_str': ['19:26:51.5616', '05:29:15.024'...  \n",
       "643338  {'radec_str': ['19:25:49.2942', '05:27:59.088'...  \n",
       "643339  {'radec_str': ['19:25:15.7963', '05:11:30.632'...  \n",
       "\n",
       "[643340 rows x 100 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ec3251ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['_id', 'Gaia_EDR3___id', 'AllWISE___id', 'PS1_DR1___id', 'ra', 'dec',\n",
       "       'period', 'field', 'ccd', 'quad', 'filter', 'e_dnn', 'cv_dnn', 'el_dnn',\n",
       "       'osarg_dnn', 'ea_dnn', 'rrblz_dnn', 'ceph2_dnn', 'bis_dnn', 'puls_dnn',\n",
       "       'agn_dnn', 'longt_dnn', 'mir_dnn', 'dscu_dnn', 'rrc_dnn', 'eb_dnn',\n",
       "       'wp_dnn', 'rrd_dnn', 'hp_dnn', 'blend_dnn', 'wvir_dnn', 'lpv_dnn',\n",
       "       'emsms_dnn', 'i_dnn', 'rrlyr_dnn', 'rrab_dnn', 'sin_dnn', 'rscvn_dnn',\n",
       "       'bright_dnn', 'bogus_dnn', 'yso_dnn', 'blyr_dnn', 'dp_dnn', 'saw_dnn',\n",
       "       'ceph_dnn', 'blher_dnn', 'ext_dnn', 'wuma_dnn', 'ew_dnn', 'srv_dnn',\n",
       "       'fla_dnn', 'vnv_dnn', 'pnp_dnn', 'dip_dnn', 'mp_dnn', 'saw_xgb',\n",
       "       'dp_xgb', 'i_xgb', 'longt_xgb', 'yso_xgb', 'wvir_xgb', 'puls_xgb',\n",
       "       'sin_xgb', 'dscu_xgb', 'blend_xgb', 'dip_xgb', 'mp_xgb', 'pnp_xgb',\n",
       "       'vnv_xgb', 'rrblz_xgb', 'ew_xgb', 'osarg_xgb', 'fla_xgb', 'srv_xgb',\n",
       "       'ext_xgb', 'ceph2_xgb', 'bis_xgb', 'blher_xgb', 'e_xgb', 'ea_xgb',\n",
       "       'wuma_xgb', 'ceph_xgb', 'el_xgb', 'bright_xgb', 'cv_xgb', 'lpv_xgb',\n",
       "       'emsms_xgb', 'rrlyr_xgb', 'wp_xgb', 'eb_xgb', 'hp_xgb', 'blyr_xgb',\n",
       "       'rscvn_xgb', 'rrd_xgb', 'mir_xgb', 'rrc_xgb', 'bogus_xgb', 'rrab_xgb',\n",
       "       'agn_xgb', 'coordinates'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_scores.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f5e4063",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_scores.to_csv('ZTF_DR16_vnv_gt0.99score_classifications.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70fe41f1",
   "metadata": {},
   "source": [
    "# Batch query to get features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "54bfa6ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "source_ids = df_scores['_id'].values.tolist()\n",
    "features_catalog = 'ZTF_source_features_DR16'\n",
    "limit=1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "32b2a3b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only get the first 100000 feature rows\n",
    "# Skip this cell to get all rows of features (takes longer/more memory)\n",
    "source_ids = source_ids[:100000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "fcb51daf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5000 done\n",
      "10000 done\n",
      "15000 done\n",
      "20000 done\n",
      "25000 done\n",
      "30000 done\n",
      "35000 done\n",
      "40000 done\n",
      "45000 done\n",
      "50000 done\n",
      "55000 done\n",
      "60000 done\n",
      "65000 done\n",
      "70000 done\n",
      "75000 done\n",
      "80000 done\n",
      "85000 done\n",
      "90000 done\n",
      "95000 done\n"
     ]
    }
   ],
   "source": [
    "# Get features and dmdt from melman\n",
    "id = 0\n",
    "df_collection = []\n",
    "dmdt_collection = []\n",
    "\n",
    "while 1:\n",
    "    query = {\n",
    "        \"query_type\": \"find\",\n",
    "        \"query\": {\n",
    "            \"catalog\": features_catalog,\n",
    "            \"filter\": {\"_id\": {\"$in\": source_ids[id * limit : (id + 1) * limit]}},\n",
    "        },\n",
    "    }\n",
    "    response = kowalski_instances.query(query=query)\n",
    "    source_data = response.get('melman').get(\"data\")\n",
    "\n",
    "    if source_data is None:\n",
    "        print(response)\n",
    "        raise ValueError(f\"No data found for source ids {source_ids}\")\n",
    "\n",
    "    df_temp = pd.DataFrame.from_records(source_data)\n",
    "    df_collection += [df_temp]\n",
    "    try:\n",
    "        dmdt_temp = np.expand_dims(\n",
    "            np.array([d for d in df_temp['dmdt'].values]), axis=-1\n",
    "        )\n",
    "    except Exception as e:\n",
    "        print(\"Error\", e)\n",
    "        print(df_temp)\n",
    "    dmdt_collection += [dmdt_temp]\n",
    "\n",
    "    if ((id + 1) * limit) >= len(source_ids):\n",
    "        break\n",
    "    id += 1\n",
    "    if (id * limit) % 5000 == 0:\n",
    "        print(id * limit, \"done\")\n",
    "\n",
    "df_features = pd.concat(df_collection, axis=0)\n",
    "dmdt = np.vstack(dmdt_collection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b5532d56",
   "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>_id</th>\n",
       "      <th>ra</th>\n",
       "      <th>dec</th>\n",
       "      <th>field</th>\n",
       "      <th>ccd</th>\n",
       "      <th>quad</th>\n",
       "      <th>filter</th>\n",
       "      <th>avg_err</th>\n",
       "      <th>med_err</th>\n",
       "      <th>std_err</th>\n",
       "      <th>...</th>\n",
       "      <th>PS1_DR1__rMeanPSFMag</th>\n",
       "      <th>PS1_DR1__rMeanPSFMagErr</th>\n",
       "      <th>PS1_DR1__iMeanPSFMag</th>\n",
       "      <th>PS1_DR1__iMeanPSFMagErr</th>\n",
       "      <th>PS1_DR1__zMeanPSFMag</th>\n",
       "      <th>PS1_DR1__zMeanPSFMagErr</th>\n",
       "      <th>PS1_DR1__yMeanPSFMag</th>\n",
       "      <th>PS1_DR1__yMeanPSFMagErr</th>\n",
       "      <th>PS1_DR1__qualityFlag</th>\n",
       "      <th>coordinates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10487361000126</td>\n",
       "      <td>282.460330</td>\n",
       "      <td>6.366678</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.158327</td>\n",
       "      <td>0.161550</td>\n",
       "      <td>0.029707</td>\n",
       "      <td>...</td>\n",
       "      <td>18.619301</td>\n",
       "      <td>0.020096</td>\n",
       "      <td>17.481199</td>\n",
       "      <td>0.003968</td>\n",
       "      <td>16.4561</td>\n",
       "      <td>0.030932</td>\n",
       "      <td>15.871600</td>\n",
       "      <td>0.017195</td>\n",
       "      <td>60.0</td>\n",
       "      <td>{'radec_str': ['18:49:50.4792', '06:22:00.040'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10487361000870</td>\n",
       "      <td>282.178217</td>\n",
       "      <td>6.299935</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.043037</td>\n",
       "      <td>0.042940</td>\n",
       "      <td>0.000721</td>\n",
       "      <td>...</td>\n",
       "      <td>15.714000</td>\n",
       "      <td>0.008957</td>\n",
       "      <td>14.807700</td>\n",
       "      <td>0.001568</td>\n",
       "      <td>14.2851</td>\n",
       "      <td>0.000326</td>\n",
       "      <td>13.901300</td>\n",
       "      <td>0.003348</td>\n",
       "      <td>60.0</td>\n",
       "      <td>{'radec_str': ['18:48:42.7720', '06:17:59.766'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10487361001120</td>\n",
       "      <td>282.150171</td>\n",
       "      <td>6.276463</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.179086</td>\n",
       "      <td>0.179845</td>\n",
       "      <td>0.023020</td>\n",
       "      <td>...</td>\n",
       "      <td>17.511101</td>\n",
       "      <td>0.013439</td>\n",
       "      <td>13.948000</td>\n",
       "      <td>0.000113</td>\n",
       "      <td>11.9440</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.076000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60.0</td>\n",
       "      <td>{'radec_str': ['18:48:36.0410', '06:16:35.268'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10487361002857</td>\n",
       "      <td>282.416051</td>\n",
       "      <td>6.137439</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.041611</td>\n",
       "      <td>0.041530</td>\n",
       "      <td>0.000646</td>\n",
       "      <td>...</td>\n",
       "      <td>15.746600</td>\n",
       "      <td>0.003946</td>\n",
       "      <td>14.786300</td>\n",
       "      <td>0.007354</td>\n",
       "      <td>14.3287</td>\n",
       "      <td>0.028565</td>\n",
       "      <td>13.959100</td>\n",
       "      <td>0.013341</td>\n",
       "      <td>60.0</td>\n",
       "      <td>{'radec_str': ['18:49:39.8522', '06:08:14.780'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10487361003026</td>\n",
       "      <td>282.653441</td>\n",
       "      <td>6.124625</td>\n",
       "      <td>487</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.205640</td>\n",
       "      <td>0.204465</td>\n",
       "      <td>0.017754</td>\n",
       "      <td>...</td>\n",
       "      <td>18.011000</td>\n",
       "      <td>0.038667</td>\n",
       "      <td>13.430000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.8470</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.121000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60.0</td>\n",
       "      <td>{'radec_str': ['18:50:36.8257', '06:07:28.652'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>10778511009049</td>\n",
       "      <td>66.466876</td>\n",
       "      <td>56.988133</td>\n",
       "      <td>778</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.041406</td>\n",
       "      <td>0.040070</td>\n",
       "      <td>0.004709</td>\n",
       "      <td>...</td>\n",
       "      <td>17.784901</td>\n",
       "      <td>0.017927</td>\n",
       "      <td>17.452299</td>\n",
       "      <td>0.009622</td>\n",
       "      <td>17.2747</td>\n",
       "      <td>0.004012</td>\n",
       "      <td>17.100300</td>\n",
       "      <td>0.007755</td>\n",
       "      <td>60.0</td>\n",
       "      <td>{'radec_str': ['04:25:52.0502', '56:59:17.278'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>10778511009242</td>\n",
       "      <td>66.733928</td>\n",
       "      <td>56.959832</td>\n",
       "      <td>778</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.205202</td>\n",
       "      <td>0.206245</td>\n",
       "      <td>0.020789</td>\n",
       "      <td>...</td>\n",
       "      <td>20.616301</td>\n",
       "      <td>0.033496</td>\n",
       "      <td>20.195700</td>\n",
       "      <td>0.033678</td>\n",
       "      <td>20.0425</td>\n",
       "      <td>0.023722</td>\n",
       "      <td>19.761999</td>\n",
       "      <td>0.079766</td>\n",
       "      <td>52.0</td>\n",
       "      <td>{'radec_str': ['04:26:56.1427', '56:57:35.397'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>10778511009481</td>\n",
       "      <td>66.666984</td>\n",
       "      <td>56.946181</td>\n",
       "      <td>778</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.033442</td>\n",
       "      <td>0.032630</td>\n",
       "      <td>0.002931</td>\n",
       "      <td>...</td>\n",
       "      <td>17.180700</td>\n",
       "      <td>0.004842</td>\n",
       "      <td>16.739100</td>\n",
       "      <td>0.007846</td>\n",
       "      <td>16.7134</td>\n",
       "      <td>0.030031</td>\n",
       "      <td>16.459700</td>\n",
       "      <td>0.042119</td>\n",
       "      <td>60.0</td>\n",
       "      <td>{'radec_str': ['04:26:40.0762', '56:56:46.251'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>10778511009575</td>\n",
       "      <td>66.638130</td>\n",
       "      <td>56.940712</td>\n",
       "      <td>778</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.059245</td>\n",
       "      <td>0.057330</td>\n",
       "      <td>0.007718</td>\n",
       "      <td>...</td>\n",
       "      <td>18.131100</td>\n",
       "      <td>0.033658</td>\n",
       "      <td>17.708500</td>\n",
       "      <td>0.041705</td>\n",
       "      <td>17.5884</td>\n",
       "      <td>0.072244</td>\n",
       "      <td>17.246700</td>\n",
       "      <td>0.009060</td>\n",
       "      <td>60.0</td>\n",
       "      <td>{'radec_str': ['04:26:33.1512', '56:56:26.565'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>10778511009607</td>\n",
       "      <td>66.375088</td>\n",
       "      <td>56.951319</td>\n",
       "      <td>778</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.016185</td>\n",
       "      <td>0.016010</td>\n",
       "      <td>0.000442</td>\n",
       "      <td>...</td>\n",
       "      <td>15.130700</td>\n",
       "      <td>0.011739</td>\n",
       "      <td>14.965600</td>\n",
       "      <td>0.007966</td>\n",
       "      <td>14.6623</td>\n",
       "      <td>0.005984</td>\n",
       "      <td>14.482100</td>\n",
       "      <td>0.001611</td>\n",
       "      <td>60.0</td>\n",
       "      <td>{'radec_str': ['04:25:30.0211', '56:57:04.750'...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100000 rows × 193 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                _id          ra        dec  field  ccd  quad  filter   \n",
       "0    10487361000126  282.460330   6.366678    487   10     1       1  \\\n",
       "1    10487361000870  282.178217   6.299935    487   10     1       1   \n",
       "2    10487361001120  282.150171   6.276463    487   10     1       1   \n",
       "3    10487361002857  282.416051   6.137439    487   10     1       1   \n",
       "4    10487361003026  282.653441   6.124625    487   10     1       1   \n",
       "..              ...         ...        ...    ...  ...   ...     ...   \n",
       "995  10778511009049   66.466876  56.988133    778   13     4       1   \n",
       "996  10778511009242   66.733928  56.959832    778   13     4       1   \n",
       "997  10778511009481   66.666984  56.946181    778   13     4       1   \n",
       "998  10778511009575   66.638130  56.940712    778   13     4       1   \n",
       "999  10778511009607   66.375088  56.951319    778   13     4       1   \n",
       "\n",
       "      avg_err   med_err   std_err  ...  PS1_DR1__rMeanPSFMag   \n",
       "0    0.158327  0.161550  0.029707  ...             18.619301  \\\n",
       "1    0.043037  0.042940  0.000721  ...             15.714000   \n",
       "2    0.179086  0.179845  0.023020  ...             17.511101   \n",
       "3    0.041611  0.041530  0.000646  ...             15.746600   \n",
       "4    0.205640  0.204465  0.017754  ...             18.011000   \n",
       "..        ...       ...       ...  ...                   ...   \n",
       "995  0.041406  0.040070  0.004709  ...             17.784901   \n",
       "996  0.205202  0.206245  0.020789  ...             20.616301   \n",
       "997  0.033442  0.032630  0.002931  ...             17.180700   \n",
       "998  0.059245  0.057330  0.007718  ...             18.131100   \n",
       "999  0.016185  0.016010  0.000442  ...             15.130700   \n",
       "\n",
       "     PS1_DR1__rMeanPSFMagErr  PS1_DR1__iMeanPSFMag  PS1_DR1__iMeanPSFMagErr   \n",
       "0                   0.020096             17.481199                 0.003968  \\\n",
       "1                   0.008957             14.807700                 0.001568   \n",
       "2                   0.013439             13.948000                 0.000113   \n",
       "3                   0.003946             14.786300                 0.007354   \n",
       "4                   0.038667             13.430000                      NaN   \n",
       "..                       ...                   ...                      ...   \n",
       "995                 0.017927             17.452299                 0.009622   \n",
       "996                 0.033496             20.195700                 0.033678   \n",
       "997                 0.004842             16.739100                 0.007846   \n",
       "998                 0.033658             17.708500                 0.041705   \n",
       "999                 0.011739             14.965600                 0.007966   \n",
       "\n",
       "     PS1_DR1__zMeanPSFMag  PS1_DR1__zMeanPSFMagErr  PS1_DR1__yMeanPSFMag   \n",
       "0                 16.4561                 0.030932             15.871600  \\\n",
       "1                 14.2851                 0.000326             13.901300   \n",
       "2                 11.9440                      NaN             11.076000   \n",
       "3                 14.3287                 0.028565             13.959100   \n",
       "4                 11.8470                      NaN             11.121000   \n",
       "..                    ...                      ...                   ...   \n",
       "995               17.2747                 0.004012             17.100300   \n",
       "996               20.0425                 0.023722             19.761999   \n",
       "997               16.7134                 0.030031             16.459700   \n",
       "998               17.5884                 0.072244             17.246700   \n",
       "999               14.6623                 0.005984             14.482100   \n",
       "\n",
       "     PS1_DR1__yMeanPSFMagErr  PS1_DR1__qualityFlag   \n",
       "0                   0.017195                  60.0  \\\n",
       "1                   0.003348                  60.0   \n",
       "2                        NaN                  60.0   \n",
       "3                   0.013341                  60.0   \n",
       "4                        NaN                  60.0   \n",
       "..                       ...                   ...   \n",
       "995                 0.007755                  60.0   \n",
       "996                 0.079766                  52.0   \n",
       "997                 0.042119                  60.0   \n",
       "998                 0.009060                  60.0   \n",
       "999                 0.001611                  60.0   \n",
       "\n",
       "                                           coordinates  \n",
       "0    {'radec_str': ['18:49:50.4792', '06:22:00.040'...  \n",
       "1    {'radec_str': ['18:48:42.7720', '06:17:59.766'...  \n",
       "2    {'radec_str': ['18:48:36.0410', '06:16:35.268'...  \n",
       "3    {'radec_str': ['18:49:39.8522', '06:08:14.780'...  \n",
       "4    {'radec_str': ['18:50:36.8257', '06:07:28.652'...  \n",
       "..                                                 ...  \n",
       "995  {'radec_str': ['04:25:52.0502', '56:59:17.278'...  \n",
       "996  {'radec_str': ['04:26:56.1427', '56:57:35.397'...  \n",
       "997  {'radec_str': ['04:26:40.0762', '56:56:46.251'...  \n",
       "998  {'radec_str': ['04:26:33.1512', '56:56:26.565'...  \n",
       "999  {'radec_str': ['04:25:30.0211', '56:57:04.750'...  \n",
       "\n",
       "[100000 rows x 193 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "d4ff7779",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['_id', 'ra', 'dec', 'field', 'ccd', 'quad', 'filter', 'avg_err',\n",
       "       'med_err', 'std_err',\n",
       "       ...\n",
       "       'PS1_DR1__rMeanPSFMag', 'PS1_DR1__rMeanPSFMagErr',\n",
       "       'PS1_DR1__iMeanPSFMag', 'PS1_DR1__iMeanPSFMagErr',\n",
       "       'PS1_DR1__zMeanPSFMag', 'PS1_DR1__zMeanPSFMagErr',\n",
       "       'PS1_DR1__yMeanPSFMag', 'PS1_DR1__yMeanPSFMagErr',\n",
       "       'PS1_DR1__qualityFlag', 'coordinates'],\n",
       "      dtype='object', length=193)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_features.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "56103cba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge features, labels\n",
    "df_merge = pd.merge(df_scores, df_features, on='_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc1a6b9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge.to_csv('golden_merged_scores_features.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0076b730",
   "metadata": {},
   "outputs": [],
   "source": [
    "write_parquet(df_merge, 'golden_merged_scores_features.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20ac09b5",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}