Recent luminous extragalactic transients - joining tables

(Modified from an original by Joe Lyman). In this notebook we will be looking for luminous extragalactic transients by ustilising the information provided from the Sherlock contextual classifier. We will specifically look for transients that are luminous compared to their host galaxies, where there is a detection in the last 10 days. The aim is to quickly find superluminous supernovae candidates (very bright supernovae that often inhabit dwarf galaxies).

As part of the process we will be looking at joining tables in SQL queries, order to retrieve information about a given object from different tables.

First of all we need to do some imports, setup plotting, and connect to the ZTF database

In [1]:
import mysql.connector
import numpy as np
from astropy.table import Table
import astropy.coordinates as coord
import astropy.units as u
import matplotlib
import matplotlib.pyplot as plt
%matplotlib inline

import settings
In [2]:
msl = mysql.connector.connect(
    user=settings.DB_USER, 
    password=settings.DB_PASS, 
    host=settings.DB_HOST, database='ztf')
cursor = msl.cursor()

As an example, lets take a look at what information is provided by the Sherlock crossmatching:

In [3]:
names=['transient_object_id', 'catalogue_object_id', 'catalogue_table_id', 
                                   'separationArcsec', 'northSeparationArcsec', 'eastSeparationArcsec', 'id', 'z', 
                                   'scale', 'distance', 'distance_modulus', 'photoZ', 'photoZErr', 
                                   'association_type', 'dateCreated', 'physical_separation_kpc', 
                                   'catalogue_object_type', 'catalogue_object_subtype', 'association_rank', 
                                   'catalogue_table_name', 'catalogue_view_name', 'rank', 'rankScore', 
                                   'search_name', 'major_axis_arcsec', 'direct_distance', 'direct_distance_scale', 
                                   'direct_distance_modulus', 'raDeg', 'decDeg', 'original_search_radius_arcsec', 
                                   'catalogue_view_id', 'U', 'UErr', 'B', 'BErr', 'V', 'VErr', 'R', 'RErr', 
                                   'I', 'IErr', 'J', 'JErr', 'H', 'HErr', 'K', 'KErr', '_u', '_uErr', 
                                   '_g', '_gErr', '_r', '_rErr', '_i', '_iErr', '_z', '_zErr', '_y', '_yErr', 
                                   'G', 'GErr', 'unkMag', 'unkMagErr', 'dateLastModified', 'updated', 
                                   'classificationReliability', 'transientAbsMag', 'merged_rank']

cursor.execute("SELECT * FROM sherlock_crossmatches LIMIT 10")
results = cursor.fetchall()
table = Table(rows=results, names=names)
table
Out[3]:
Table length=10
transient_object_idcatalogue_object_idcatalogue_table_idseparationArcsecnorthSeparationArcseceastSeparationArcsecidzscaledistancedistance_modulusphotoZphotoZErrassociation_typedateCreatedphysical_separation_kpccatalogue_object_typecatalogue_object_subtypeassociation_rankcatalogue_table_namecatalogue_view_namerankrankScoresearch_namemajor_axis_arcsecdirect_distancedirect_distance_scaledirect_distance_modulusraDegdecDegoriginal_search_radius_arcseccatalogue_view_idUUErrBBErrVVErrRRErrIIErrJJErrHHErrKKErr_u_uErr_g_gErr_r_rErr_i_iErr_z_zErr_y_yErrGGErrunkMagunkMagErrdateLastModifiedupdatedclassificationReliabilitytransientAbsMagmerged_rank
int64str30int64float64float64float64int64objectobjectobjectobjectobjectobjectstr7objectobjectstr7objectobjectstr18str42objectfloat64str28objectobjectobjectobjectfloat64float64float64int64objectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectobjectint64int64objectobject
1235661232996065935604352/NCFO028307710.07260430046270.0726-0.00086787825NoneNoneNoneNoneNoneNoneVS2018-11-02 07:38:47NonestarmultipleNoneGAIA/GSCmultiple11002.0726043multipleNoneNoneNoneNone64.334156591445.59427317340.036NoneNone15.36870.42536314.76530.379915NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone15.9081842601NoneNoneNone2018-11-02 07:38:4711NoneNone
12405371920670857505734144/23364861+4710.2176610473470.21677-0.019756787826NoneNoneNoneNoneNoneNoneVS2018-11-02 07:38:47NonestarmultipleNoneGAIA/2MASS/GSC/NEDmultiple11002.21766105multipleNoneNoneNoneNone354.2025354540.81196081440.036NoneNone15.53530.42588414.87790.318287NoneNoneNoneNone13.6110.02613.2190.03213.1570.029NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone14.7892467175NoneNoneNone2018-11-02 07:38:4701NoneNone
12405371920670857505734144710.2176610473470.21677-0.019756787827NoneNoneNoneNoneNoneNoneVS2018-11-02 07:38:47NonestarNoneNoneGaia DR1tcs_view_star_gaia_dr1None1002.21766105gaia star angularNoneNoneNoneNone354.2025354540.8119608144100.036NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone14.7892467175NoneNoneNone2018-11-02 07:38:4701None1
124053723364861+404843010.2350237723580.2210.07986787828NoneNoneNoneNoneNoneNoneVS2018-11-02 07:38:47NonestarNoneNone2MASS PSCtcs_view_star_2mass_psc_finalNone1002.217661052mass star angularNoneNoneNoneNone354.20257240.8119622.520NoneNoneNoneNoneNoneNoneNoneNoneNoneNone13.6110.02613.2190.03213.1570.029NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone2018-11-02 07:38:4701None1
1240537N07B00333730.4043020559480.241930.323956787829NoneNoneNoneNoneNoneNoneVS2018-11-02 07:38:47Nonestar0NoneGSC v2.3tcs_view_star_guide_star_catalogue_v2_3None1002.21766105GSC star 1 angularNoneNoneNoneNone354.20266159240.8119678014100.021NoneNone15.53530.42588414.87790.318287NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone2018-11-02 07:38:4701None1
12405372MASS J23364861+4048430681.065696667541.040.226787830NoneNoneNoneNoneNoneNoneSN2018-11-02 07:38:47NonegalaxyUvSNoneNEDtcs_view_galaxy_like_ned_streamNone1002.21766105ned phot galaxy-like angularNoneNoneNoneNone354.20262540.8121910.039NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone2018-11-02 07:38:4703None1
124053823543460+4147382/192158249008410.02915858874650.03420.03276787831NoneNoneNoneNoneNoneNoneVS2018-11-02 07:38:47NonestarmultipleNone2MASS/GAIA/GSC/NEDmultiple11002.02915859multipleNoneNoneNoneNone358.6442009241.79396918240.020NoneNone16.90450.41979116.16790.303329NoneNoneNoneNone15.1610.05114.9360.09514.7220.115NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone16.1944577882NoneNoneNone2018-11-02 07:38:4701NoneNone
124053823543460+414738210.0474169619030.03420.03276787832NoneNoneNoneNoneNoneNoneVS2018-11-02 07:38:47NonestarNoneNone2MASS PSCtcs_view_star_2mass_psc_finalNone1002.029158592mass star angularNoneNoneNoneNone358.64420741.7939722.520NoneNoneNoneNoneNoneNoneNoneNoneNoneNone15.1610.05114.9360.09514.7220.115NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone2018-11-02 07:38:4701None1
12405381921582490084254336710.02915858874650.02405669010.01642568246787833NoneNoneNoneNoneNoneNoneVS2018-11-02 07:38:47NonestarNoneNoneGaia DR1tcs_view_star_gaia_dr1None1002.02915859gaia star angularNoneNoneNoneNone358.6442009241.79396918242.536NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone16.1944577882NoneNoneNone2018-11-02 07:38:4702None1
1240538N07H00523130.1824063368310.11797650290.13914320236787834NoneNoneNoneNoneNoneNoneUNCLEAR2018-11-02 07:38:47Noneunknown3NoneGSC v2.3tcs_view_unknown_guide_star_catalogue_v2_3None1002.02915859GSC unknown angularNoneNoneNoneNone358.64424664241.79399527132.038NoneNone16.90450.41979116.16790.303329NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNone2018-11-02 07:38:4703None1

Of interest to us here is to select on objects that have a sherlock_crossmatches.rank = 1 (i.e. the most likely) crossmatch object that has sherlock_crossmatches.catalogue_object_type = 'galaxy'. (Note we could also try to select objects where the sherlock_crossmatches.association_type = 'SN' (supernova) if we wished, you can alter the query below as appropriate, if desired) We will impose a cut on our objects that the must have at least 8 detections also, for us to assess the light curves - i.e. objects.ncand > 8.

We know what objects we want to select, now we need to decide the information (columns) we want to retrieve about the objects and their Sherlock crossmatches. Since we are comparing transient and host galaxy magnitudes, we want to retrieve the brightest g and r magnitudes from the objects table (confusingly named magrmin and maggmin, currently), and the _r and _g magnitudes from the sherlock_crossmatches table (note the underscores are used distinguish these columns from the other photometric systems in the table, e.g. R) - we should also grab the id, name and coordinates of our objects.

Since we require information from two different tables, and we want to retrieve that information for rows that are associated with the same object id, we must use a table JOIN and specifically and ON condition. You can see this used in the query below - there is plenty of SQL documentation online to find out more about these.

First we compute the current Julian Day from the Unix time:

In [4]:
import time, datetime
now = datetime.datetime.now()
print ("This notbook was run on " + now.isoformat())

jdnow = time.time()/86400 + 2440587.5
print ("and the Julian Date is {}".format(jdnow))
This notbook was run on 2019-06-17T09:04:37.399825
and the Julian Date is 2458651.836543981

Now get the light curve information about all the objects we have found:

In [5]:
query = "SELECT o.primaryid, o.objectid, o.magrmin, o.maggmin, o.ncand, sc._r, sc._g "
query += "FROM sherlock_crossmatches AS sc JOIN objects as o ON sc.transient_object_id = o.primaryid "
query += "WHERE sc.rank = 1 AND sc.catalogue_object_type = 'galaxy' "
query += "AND o.jdmax > {} - 10".format(jdnow)
print(query)
SELECT o.primaryid, o.objectid, o.magrmin, o.maggmin, o.ncand, sc._r, sc._g FROM sherlock_crossmatches AS sc JOIN objects as o ON sc.transient_object_id = o.primaryid WHERE sc.rank = 1 AND sc.catalogue_object_type = 'galaxy' AND o.jdmax > 2458651.836543981 - 10
In [6]:
cursor.execute(query)
results = cursor.fetchall()
table = Table(rows=results, names=["primaryid", "objectid", "magrmin", "maggmin", "ncand", "host_r", "host_g"])
table
Out[6]:
Table length=3310
primaryidobjectidmagrminmaggminncandhost_rhost_g
int64str12objectobjectint64objectobject
1236945ZTF17aaaaztj18.675919.86741417.485718.9144
1238053ZTF17aaadjky17.621219.84827218.639321.1895
1238060ZTF17aaadjru18.235520.17768720.890325.1147
1238141ZTF17aaadost18.548618.94612417.043517.6367
1238219ZTF17aaadqjt17.513817.40822016.630916.7198
1239442ZTF17aaaeckr18.5356None9518.192521.8784
1239816ZTF17aaaedvh17.946218.429314316.957817.0501
1240030ZTF17aaaehfp17.278817.49013315.814116.3199
1240031ZTF17aaaehfq16.872116.97942014.013214.1722
1240077ZTF17aaaehqm18.023418.32774021.775121.7285
.....................
35454645ZTF19aazwkmg17.234418.1126818.499119.4367
35455013ZTF19aazwkzuNone18.843349.7532410.2112
35456829ZTF19aaznxcw19.382519.1979518.295818.7774
35457210ZTF19aazwnsq19.195418.9598418.428619.1944
35457332ZTF19aazwnwy19.555218.2165317.543618.0607
35457521ZTF19aazwoed19.943119.3686317.015717.6937
35461544ZTF19aapvjob19.28318.1891524.801925.1143
35463133ZTF19aazwvzp19.544319.6052318.460419.0422
35464095ZTF19aazwxvz18.848316.2991624.801925.1143
35464611ZTF19aaeopxu17.8747None313.467314.2867
In [7]:
lum_objid = []
for filter_name, tran_mag, host_mag, c in zip(("g", "r"), ("maggmin", "magrmin"), ("host_g", "host_r"), ("C2", "C3")):
    # Remove those where there's no data
    m1 = table[tran_mag] != None
    m2 = table[host_mag] != None
    t = table[m1 & m2]
    # Calculate the magnitude difference
    magdiff = t[tran_mag] - t[host_mag]
    # Store those significantly brighter than their hosts
    lum_objid.extend(t["objectid"][magdiff <= -4])
    # Plot histogram
    plt.hist(magdiff, bins=25, color=c)
    plt.xlabel("transient - host mag")
    plt.ylabel("N")
    plt.title("{}".format(filter_name))
    plt.show()
    
# Remove duplicates (i.e. those added for both g and r filters)
lum_objid = tuple(set(lum_objid))

We can use our list of luminous object ids to grab their lightcurves by querying the candidates table.

In [8]:
print(lum_objid)
('ZTF18aajtinl', 'ZTF18abfedgq', 'ZTF18aaripcr', 'ZTF18abmrpun', 'ZTF19aaucspl', 'ZTF18abmmzav', 'ZTF18aaveldl', 'ZTF18aaovwdx', 'ZTF18abcuyrc', 'ZTF19aaynpez', 'ZTF18abkjfzw', 'ZTF19aamjvie', 'ZTF18abtvnxf', 'ZTF18abcdude', 'ZTF18aanbnjh', 'ZTF17aaaeurz', 'ZTF19aaqfdxl', 'ZTF19aayrhsr', 'ZTF19aaynruw', 'ZTF19aaynpjm', 'ZTF18abvhzxn', 'ZTF19aaynpfo', 'ZTF19aawolyt', 'ZTF19aaynrut', 'ZTF18aabqewr', 'ZTF18abygdtf', 'ZTF18aaadwtg', 'ZTF18acojiqg', 'ZTF19aaxwbyx', 'ZTF19aazwxvz', 'ZTF18acvhqfj', 'ZTF19aamvape', 'ZTF19aadnxat', 'ZTF19aazpjya', 'ZTF19aaynnrv', 'ZTF19aarfyvc', 'ZTF19aazpkun', 'ZTF19aaznynm', 'ZTF19aaxbzqp', 'ZTF19aazwjrv', 'ZTF19aapvjob', 'ZTF19aavwbyn', 'ZTF19aazttud', 'ZTF19aavmucr', 'ZTF18abgzdff', 'ZTF18aaoawyk', 'ZTF19aaynpgs', 'ZTF18acvwbek', 'ZTF19aayrlff', 'ZTF19aazplcx', 'ZTF17aaadjru', 'ZTF19aaynowd', 'ZTF18abccpml', 'ZTF19aaqaumz', 'ZTF19aawfepd', 'ZTF18abahaqt', 'ZTF18abncuii', 'ZTF18aceiqfb', 'ZTF17aaawggq', 'ZTF19aarhhfx', 'ZTF18acnephm', 'ZTF19aaqsuys', 'ZTF18abmahrm', 'ZTF19aazpldq', 'ZTF19aatvmjn', 'ZTF19aamgypy', 'ZTF18aazwddf', 'ZTF18acnbpmd', 'ZTF18abtivig', 'ZTF18abmfcxp', 'ZTF18abtjrxx', 'ZTF19aaynocc', 'ZTF19aazplgx', 'ZTF18abcvcgs', 'ZTF18aamigmk', 'ZTF19aayioyv', 'ZTF18abtkbnf', 'ZTF18acswtoq', 'ZTF19aaynoyi', 'ZTF19aazpken', 'ZTF18abvipbl', 'ZTF19aazplzl', 'ZTF18aagtlfn', 'ZTF19aazcetz', 'ZTF18abnyjvh', 'ZTF18aabqpfu', 'ZTF19aazfkss', 'ZTF19aadnxog', 'ZTF19aayricb', 'ZTF19aaynpkf', 'ZTF18acvgjqv', 'ZTF18abfylqh', 'ZTF18aajskry', 'ZTF19aazpxvy', 'ZTF18abiinnl', 'ZTF18aaqnsia', 'ZTF19aadhafu')
In [9]:
# Get the light curves
query = "SELECT objectid, jd-{} AS ago, magpsf, sigmapsf, fid FROM candidates WHERE objectid IN {};".format(jdnow, lum_objid)
print(query)
cursor.execute(query)
result = cursor.fetchall()
SELECT objectid, jd-2458651.836543981 AS ago, magpsf, sigmapsf, fid FROM candidates WHERE objectid IN ('ZTF18aajtinl', 'ZTF18abfedgq', 'ZTF18aaripcr', 'ZTF18abmrpun', 'ZTF19aaucspl', 'ZTF18abmmzav', 'ZTF18aaveldl', 'ZTF18aaovwdx', 'ZTF18abcuyrc', 'ZTF19aaynpez', 'ZTF18abkjfzw', 'ZTF19aamjvie', 'ZTF18abtvnxf', 'ZTF18abcdude', 'ZTF18aanbnjh', 'ZTF17aaaeurz', 'ZTF19aaqfdxl', 'ZTF19aayrhsr', 'ZTF19aaynruw', 'ZTF19aaynpjm', 'ZTF18abvhzxn', 'ZTF19aaynpfo', 'ZTF19aawolyt', 'ZTF19aaynrut', 'ZTF18aabqewr', 'ZTF18abygdtf', 'ZTF18aaadwtg', 'ZTF18acojiqg', 'ZTF19aaxwbyx', 'ZTF19aazwxvz', 'ZTF18acvhqfj', 'ZTF19aamvape', 'ZTF19aadnxat', 'ZTF19aazpjya', 'ZTF19aaynnrv', 'ZTF19aarfyvc', 'ZTF19aazpkun', 'ZTF19aaznynm', 'ZTF19aaxbzqp', 'ZTF19aazwjrv', 'ZTF19aapvjob', 'ZTF19aavwbyn', 'ZTF19aazttud', 'ZTF19aavmucr', 'ZTF18abgzdff', 'ZTF18aaoawyk', 'ZTF19aaynpgs', 'ZTF18acvwbek', 'ZTF19aayrlff', 'ZTF19aazplcx', 'ZTF17aaadjru', 'ZTF19aaynowd', 'ZTF18abccpml', 'ZTF19aaqaumz', 'ZTF19aawfepd', 'ZTF18abahaqt', 'ZTF18abncuii', 'ZTF18aceiqfb', 'ZTF17aaawggq', 'ZTF19aarhhfx', 'ZTF18acnephm', 'ZTF19aaqsuys', 'ZTF18abmahrm', 'ZTF19aazpldq', 'ZTF19aatvmjn', 'ZTF19aamgypy', 'ZTF18aazwddf', 'ZTF18acnbpmd', 'ZTF18abtivig', 'ZTF18abmfcxp', 'ZTF18abtjrxx', 'ZTF19aaynocc', 'ZTF19aazplgx', 'ZTF18abcvcgs', 'ZTF18aamigmk', 'ZTF19aayioyv', 'ZTF18abtkbnf', 'ZTF18acswtoq', 'ZTF19aaynoyi', 'ZTF19aazpken', 'ZTF18abvipbl', 'ZTF19aazplzl', 'ZTF18aagtlfn', 'ZTF19aazcetz', 'ZTF18abnyjvh', 'ZTF18aabqpfu', 'ZTF19aazfkss', 'ZTF19aadnxog', 'ZTF19aayricb', 'ZTF19aaynpkf', 'ZTF18acvgjqv', 'ZTF18abfylqh', 'ZTF18aajskry', 'ZTF19aazpxvy', 'ZTF18abiinnl', 'ZTF18aaqnsia', 'ZTF19aadhafu');
In [10]:
ztf_lc_res = Table(rows=result, names=("objectid", "ago", "magpsf", "sigmapsf", "fid")).group_by("objectid")
ztf_lc_res
Out[10]:
Table length=2509
objectidagomagpsfsigmapsffid
str12float64float64float64int64
ZTF17aaadjru-367.94576848119.62610.08718632
ZTF17aaadjru-365.92437958119.73020.09333172
ZTF17aaadjru-363.88731948119.86110.1172332
ZTF17aaadjru-362.88290968119.4530.2008222
ZTF17aaadjru-350.93828008119.18150.1793942
ZTF17aaadjru-343.94433338119.19560.1172342
ZTF17aaadjru-332.97131248119.78720.1814592
ZTF17aaadjru-331.98240048119.28170.1430052
ZTF17aaadjru-330.94059488119.39320.1469142
ZTF17aaadjru-328.93610418119.50340.1766572
...............
ZTF19aazwjrv-6.8903981810419.22470.1972511
ZTF19aazwjrv-6.8588587809419.59690.1725352
ZTF19aazwjrv-3.8658610810519.20830.2097181
ZTF19aazwjrv-0.85740048112319.36420.2069381
ZTF19aazwxvz-23.122192081116.54630.6295781
ZTF19aazwxvz-18.078685181216.29910.608161
ZTF19aazwxvz-15.081115780916.38120.618371
ZTF19aazwxvz-6.1018217811416.40630.7458061
ZTF19aazwxvz-3.0160231809116.33090.5387511
ZTF19aazwxvz-0.086115781217818.84830.1967952

For each luminous object, plot the light curve

In [11]:
for objid in lum_objid:
    lc = ztf_lc_res[ztf_lc_res["objectid"] == objid]
    if len(lc) < 8:
        continue
    lcg = lc[[lc["fid"] == 1]]
    plt.errorbar(lcg["ago"], lcg["magpsf"], yerr=lcg["sigmapsf"], c="C2")
    lcr = lc[[lc["fid"] == 2]]
    plt.errorbar(lcr["ago"], lcr["magpsf"], yerr=lcr["sigmapsf"], c="C3")
    plt.xlabel("days in the past")
    plt.ylabel("psf mag")
    plt.title("{}".format(objid))
    plt.gca().invert_yaxis()
    plt.show()