scanning_playground_igor.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import packages, connect to the psql database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the relevant packages\n",
    "import psycopg2\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from astropy.table import Table\n",
    "from astropy.time import Time\n",
    "from astropy.io import ascii, fits\n",
    "from astropy.coordinates import SkyCoord \n",
    "import astropy.units as u\n",
    "from astroquery.irsa import Irsa\n",
    "\n",
    "from penquins import Kowalski\n",
    "\n",
    "from functions_misc import make_triplet, plot_triplet, get_cutouts\n",
    "from functions_misc import get_dust_info, plot_lc\n",
    "from functions_misc import get_xmatch_clu_glade, get_bgal_ebv\n",
    "\n",
    "\n",
    "# Read the database secrets\n",
    "info = ascii.read('./db_access.csv', format='csv')\n",
    "info_db = info[info['db'] == 'db_kn_rt_user']\n",
    "db_kn = f\"host={info_db['host'][0]} dbname={info_db['dbname'][0]} \\\n",
    "port={info_db['port'][0]} user={info_db['user'][0]} \\\n",
    "password={info_db['password'][0]}\"\n",
    "\n",
    "# Connect to the database\n",
    "con = psycopg2.connect(db_kn)\n",
    "cur = con.cursor()\n",
    "print(f\"Connected to the '{info_db['dbname'][0]}' database\")\n",
    "\n",
    "# Read the secrets for kowalski access\n",
    "secrets = ascii.read('secrets.csv', format='csv')\n",
    "username_kowalski = secrets['kowalski_user'][0]\n",
    "password_kowalski = secrets['kowalski_pwd'][0]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scoring\n",
    "\n",
    "Assign or remove 'points' based on soft constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start with full list, hard rejects will be removed HERE\n",
    "scoring_df = pd.read_sql_query(\"SELECT name FROM candidate where hard_reject is NULL\", con)\n",
    "\n",
    "# Define the thresholds\n",
    "thresh = {'rise': {'g': -1.0, 'r': -1., 'i': -0.5, 'sign_select': '<', 'sign_reject': '>'},\n",
    "          'fade': {'g': 0.58, 'r': 0.43, 'i': 0.30, 'sign_select': '>', 'sign_reject': '<'}\n",
    "          }\n",
    "\n",
    "# Define the filters (list)\n",
    "list_filters = ['g', 'r', 'i']\n",
    "\n",
    "# Rise, fade, or both? (list, e.g. ['rise', 'fade'])\n",
    "list_rise_fade = ['fade']\n",
    "\n",
    "scores = {'rise_select': 5,\n",
    "          'rise_pen': 0,\n",
    "          'fade_select': 10,\n",
    "          'fade_pen': -100,\n",
    "         }\n",
    "\n",
    "print(f\"Working with {len(scoring_df)} candidates\")\n",
    "print(f\"Considering the following filter(s): {list_filters}\")\n",
    "print(\"---\")\n",
    "print(\"Selected thresholds:\")\n",
    "for k1 in thresh.keys():\n",
    "    for k2 in thresh[k1].keys():\n",
    "        print(f\"{k1} {k2}: {thresh[k1][k2]}\")\n",
    "print(\"---\")\n",
    "print(\"Scoring points:\")\n",
    "for k in scores.keys():\n",
    "    print(f\"{k}: {scores[k]}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter rise and fade (alerts, forced phot, stacked forced phot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter for rise and fade for ALERTS\n",
    "for rf in list_rise_fade:\n",
    "    for f in list_filters:\n",
    "        rf_filt = pd.read_sql_query(f\"SELECT name FROM candidate WHERE index_{rf}_{f} {thresh[rf]['sign_select']} {thresh[rf][f]}\",con).values\n",
    "        print(f\"{rf}_{f}_filt_alerts: {len(rf_filt)}\" )\n",
    "\n",
    "        # Assign points if condition is met, otherwise 0\n",
    "        scoring_df[f'{rf}_{f}_filt_alerts'] = [scores[f\"{rf}_select\"] if name in rf_filt else 0 for name in scoring_df['name']]\n",
    "\n",
    "\n",
    "# Filter for rise and fade for FORCED PHOTOMETRY\n",
    "for rf in list_rise_fade:\n",
    "    for f in list_filters:\n",
    "        rf_filt = pd.read_sql_query(f\"SELECT name FROM candidate WHERE index_{rf}_forced_{f} {thresh[rf]['sign_select']} {thresh[rf][f]}\",con).values\n",
    "        print(f\"{rf}_{f}_filt_forced: {len(rf_filt)}\" )\n",
    "\n",
    "        # Assign points if condition is met, otherwise 0\n",
    "        scoring_df[f'{rf}_{f}_filt_forced'] = [scores[f\"{rf}_select\"] if name in rf_filt else 0 for name in scoring_df['name']]\n",
    "\n",
    "\n",
    "# Filter for rise and fade for STACKED FORCED PHOTOMETRY\n",
    "for rf in list_rise_fade:\n",
    "    for f in list_filters:\n",
    "        rf_filt = pd.read_sql_query(f\"SELECT name FROM candidate WHERE index_{rf}_stack_{f} {thresh[rf]['sign_select']} {thresh[rf][f]}\",con).values\n",
    "        print(f\"{rf}_{f}_filt_stack: {len(rf_filt)}\" )\n",
    "\n",
    "        # Assign points if condition is met, otherwise 0\n",
    "        scoring_df[f'{rf}_{f}_filt_stack'] = [scores[f\"{rf}_select\"] if name in rf_filt else 0 for name in scoring_df['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Penalize slow rise or fade (alerts, forced phot, stacked forced phot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Penalize slow rise and fade for ALERTS\n",
    "for rf in list_rise_fade:\n",
    "    for f in list_filters:\n",
    "        rf_filt = pd.read_sql_query(f\"SELECT name FROM candidate WHERE index_{rf}_{f} {thresh[rf]['sign_reject']} {thresh[rf][f]}\",con).values\n",
    "        print(f\"{rf}_{f}_pen_alerts: {len(rf_filt)}\" )\n",
    "\n",
    "        # Assign points if condition is met, otherwise 0\n",
    "        scoring_df[f'{rf}_{f}_pen_alerts'] = [scores[f\"{rf}_pen\"] if name in rf_filt else 0 for name in scoring_df['name']]\n",
    "        \n",
    "\n",
    "# Penalize slow rise and fade for FORCED PHOTOMETRY\n",
    "for rf in list_rise_fade:\n",
    "    for f in list_filters:\n",
    "        rf_filt = pd.read_sql_query(f\"SELECT name FROM candidate WHERE index_{rf}_forced_{f} {thresh[rf]['sign_reject']} {thresh[rf][f]}\",con).values\n",
    "        print(f\"{rf}_{f}_pen_forced: {len(rf_filt)}\" )\n",
    "\n",
    "        # Assign points if condition is met, otherwise 0\n",
    "        scoring_df[f'{rf}_{f}_pen_forced'] = [scores[f\"{rf}_pen\"] if name in rf_filt else 0 for name in scoring_df['name']]\n",
    "\n",
    "\n",
    "# Penalize slow rise and fade for STACKED FORCED PHOTOMETRY\n",
    "for rf in list_rise_fade:\n",
    "    for f in list_filters:\n",
    "        rf_filt = pd.read_sql_query(f\"SELECT name FROM candidate WHERE index_{rf}_stack_{f} {thresh[rf]['sign_reject']} {thresh[rf][f]}\",con).values\n",
    "        print(f\"{rf}_{f}_pen_stack: {len(rf_filt)}\" )\n",
    "\n",
    "        # Assign points if condition is met, otherwise 0\n",
    "        scoring_df[f'{rf}_{f}_pen_stack'] = [scores[f\"{rf}_pen\"] if name in rf_filt else 0 for name in scoring_df['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Penalize candidates with long duration in forced photometry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Penalize long duration transients - TOTAL\n",
    "duration_pen = pd.read_sql_query(\"SELECT name FROM candidate \\\n",
    "WHERE duration_tot > 14\", con).drop_duplicates('name').values\n",
    "print('duration_pen: ' + str(len(duration_pen)))\n",
    "\n",
    "scoring_df['duration_pen'] = [-100 if name in duration_pen else 0 for name in scoring_df['name']]\n",
    "\n",
    "# Penalize long duration transients - INDIVIDUAL FILTERS\n",
    "duration_dict = {\"g\": 10, \"r\": 12, \"i\": 14}\n",
    "for f in [\"g\", \"r\", \"i\"]:\n",
    "    duration_pen = pd.read_sql_query(f\"SELECT name FROM candidate \\\n",
    "    WHERE duration_{f} > {duration_dict[f]}\", con).drop_duplicates('name').values\n",
    "    print(f'duration_pen_{f}: ' + str(len(duration_pen)))\n",
    "    scoring_df[f'duration_pen_{f}'] = [-100 if name in duration_pen else 0 for name in scoring_df['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Crossmatch scoring\n",
    "Have ranges in assigning/penalizing points based on rising rate -> fast +1, slow-1, between 0. Ranges defined by RCF SN results, and/or 2018cow\n",
    "\n",
    "Points for candidates with more than 6 detections\n",
    "\n",
    "Point there is a non-PSF LS source with phot_z less than x\n",
    "\n",
    "Penalize if PS object within 1.5 arcsec\n",
    "\n",
    "Point is PS source with sgscore less than x and mag brighter than y\n",
    "\n",
    "### Present in either CLU or GLADE \n",
    "NOTE: GLADE crossmatch is not yet implemented, WIP."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter for match in either CLU or GLADE \n",
    "galaxy_match_filt = pd.read_sql_query(\"SELECT name FROM crossmatch \\\n",
    "WHERE (clu_dist_kpc < 100) and (clu_distmpc > 10)\", con).drop_duplicates('name').values\n",
    "print('galaxy_match_filt: ' + str(len(galaxy_match_filt)))\n",
    "\n",
    "\n",
    "# Now the CLU crossmatching is giving zero points, change as desired. \n",
    "scoring_df['galaxy_match_filt'] = [1 if name in galaxy_match_filt else 0 for name in scoring_df['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AGN scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from functions_misc import agn_b_scores\n",
    "\n",
    "agn_wise =[]\n",
    "b_score = []\n",
    "\n",
    "# # print('Quering II/328/allwise \\n## w1-2 0.6..1.7 w2-w3 2.2 .. 4.5')\n",
    "# for name in scoring_df['name']:\n",
    "#     agn,b = agn_b_scores(name,username_kowalski,password_kowalski)\n",
    "#     agn_wise.append(agn)\n",
    "#     b_score.append(b)\n",
    "\n",
    "# scoring_df['agn_wise'] = agn_wise\n",
    "# scoring_df['gal_latitude'] = b_score"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create dataframe with the results\n",
    "result_df = pd.DataFrame([])\n",
    "result_df['name'] = scoring_df['name']\n",
    "result_df['sum'] = scoring_df.sum(axis=1)\n",
    "result_df.sort_values(by='sum', ascending=False)[:12]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Distribution of the resultng scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a scoring threshold\n",
    "score_thresh = 1\n",
    "\n",
    "fig, ax = plt.subplots(1,1)\n",
    "print(f\"There are {len(result_df[result_df['sum'] > score_thresh])} candidates above the scoring threshold\")\n",
    "\n",
    "bins = 'auto'\n",
    "#bins = np.arange(np.min(result_df['sum']), np.max(result_df['sum']), 0.5)\n",
    "bins = np.arange(-10, np.max(result_df['sum'])+1, 0.5)\n",
    "\n",
    "ax.hist(result_df['sum'], bins=bins)\n",
    "ax.set_yscale('log')\n",
    "ax.set_xlabel(\"Score\")\n",
    "#plt.savefig(\"score_distribution.png\")\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plot up the results\n",
    "Define the list of candidates to plot. By default, it plots all those with score larger than the `score_thresh` previously defined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a recency threshold (in days)\n",
    "recency_thresh = 21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Plotting cell…\n",
    "list_names = result_df.sort_values(by='sum', ascending=False)[result_df['sum'] > score_thresh]['name']\n",
    "\n",
    "# Select only recent stuff\n",
    "list_recent = pd.read_sql_query(f\"SELECT name FROM lightcurve WHERE jd > {Time.now().jd - recency_thresh}\", con).drop_duplicates('name')\n",
    "list_names = list(n for n in list_names if n in list(list_recent['name']))\n",
    "\n",
    "# Get CLU and GLADE crossmatch information\n",
    "clu, glade = get_xmatch_clu_glade(list_names, con, cur)\n",
    "\n",
    "# Get coords, Galactic latitude and E(B-V)\n",
    "bgal_ebv = get_bgal_ebv(list_names, con, cur)\n",
    "\n",
    "list_out = []\n",
    "\n",
    "for name in list_names:\n",
    "    print(name)\n",
    "    clu_crossmatch = clu[clu['name'] == name]\n",
    "    if clu_crossmatch.empty:\n",
    "        print(\"No CLU crossmatch\")\n",
    "    else:\n",
    "        print(\"CLU crossmatch:\")\n",
    "        print(clu[clu['name'] == name])\n",
    "    print(f\"Coordinates: RA, Dec = {'{:.6f}'.format(float(bgal_ebv[bgal_ebv['name'] == name]['ra']))}, {'{:.5f}'.format(float(bgal_ebv[bgal_ebv['name'] == name]['dec']))}\")\n",
    "    print(f\"Extinction: E(B-V) = {'{:.2f}'.format(float(bgal_ebv[bgal_ebv['name'] == name]['ebv']))}\")\n",
    "    print(f\"Galactic latitude: b_Gal = {'{:.2f}'.format(float(bgal_ebv[bgal_ebv['name'] == name]['b_gal']))}\")\n",
    "        \n",
    "    ###print(glade[glade['name'] == name])\n",
    "    alerts = get_cutouts(name, username_kowalski, password_kowalski)\n",
    "    triplet = make_triplet(alerts[0])\n",
    "    plot_triplet(triplet)\n",
    "    print(f\"Alerts light curve for {name}\")\n",
    "    plot_lc(name, con, cur, forced=False, stack=False, plot_alerts=True, save=False, inset=False, tr=triplet, plot_cow=False, plot_gfo=False, plot_bulla=False, filtermatch='g')    \n",
    "    print(f\"Forced photometry light curve for {name}\")\n",
    "    plot_lc(name, con, cur, forced=True, stack=False, plot_alerts=True, save=False, inset=False, tr=triplet, plot_cow=False, plot_gfo=False, plot_bulla=False, filtermatch='g')\n",
    "    print(f\"Stacked forced photometry light curve for {name}\")\n",
    "    plot_lc(name, con, cur, forced=True, stack=True, plot_alerts=True, save=False, inset=False, tr=triplet, plot_cow=False, plot_gfo=False, plot_bulla=False, filtermatch='g')\n",
    "    print(\"------\")\n",
    "    list_out.append(name)\n",
    "print(f\"Found {len(list_out)} candidates\")\n",
    "print(list_out)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "****\n",
    "# The End\n",
    "What you actually need for the scanning finishes here.\n",
    "****\n",
    "****"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tables\n",
    "There are multiple tables in the database. Here you can print the names of the tables and the names of the columns available in each table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Table names\n",
    "tables = pd.read_sql_query(\"SELECT table_name FROM information_schema.tables WHERE table_schema='public' \",con)\n",
    "\n",
    "for t in tables[\"table_name\"]:\n",
    "    print(f\"Table {t}:\")\n",
    "    q = pd.read_sql_query(f\"SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{t}'\", con)\n",
    "    print(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Close the database connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "con.close()"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}