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 [104]:
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 [105]:
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 [106]:
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[106]:
<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 [107]:
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 2018-11-26T10:10:20.591184
and the Julian Date is 2458448.9238494374

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

In [108]:
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 > 2458448.9238494374 - 10
In [109]:
cursor.execute(query)
results = cursor.fetchall()
table = Table(rows=results, names=["primaryid", "objectid", "magrmin", "maggmin", "ncand", "host_r", "host_g"])
table
Out[109]:
<Table length=1038>
primaryidobjectidmagrminmaggminncandhost_rhost_g
int64str12objectobjectint64objectobject
1235921ZTF17aaaabds18.261519.548956NoneNone
1237146ZTF17aaabbso18.033118.35889NoneNone
1238004ZTF17aaaddyq18.5449None616.927617.7178
1238219ZTF17aaadqjt17.513817.40821216.630916.7198
1238330ZTF17aaadxrcNone14.17742214.385114.8365
1238527ZTF17aaadyuf16.994818.32592014.965516.0724
1239442ZTF17aaaeckr18.5356None7818.192521.8784
1239816ZTF17aaaedvh17.97918.429311216.957817.0501
1240030ZTF17aaaehfp17.278817.49012115.814116.3199
1240031ZTF17aaaehfq16.872117.1919914.013214.1722
.....................
9553004ZTF18acguzsw16.8192None3NoneNone
9553025ZTF18acmzhmp19.426819.5695316.779517.7128
9553322ZTF18acplkbd19.638720.0824316.504717.5494
9555961ZTF18acgviec19.2204None317.394818.0203
9556262ZTF18acppdfv19.1919None316.039916.9845
9556848ZTF18acppddp18.2037None316.023916.6893
9557522ZTF18acqqyah16.860418.8385316.06115.9838
9557719ZTF18aardsim19.070116.88173NoneNone
9557792ZTF18acnbglg19.638519.9647317.85918.8095
9559726ZTF18abupgps18.907719.60733NoneNone
In [110]:
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 [111]:
print(lum_objid)
('ZTF18abslpjy', 'ZTF18aaaaygy', 'ZTF18abxqemv', 'ZTF18acgvlrr', 'ZTF18abrqfjs', 'ZTF18acmzvqx', 'ZTF18aagpyca', 'ZTF18aazwddf', 'ZTF18aabqewr', 'ZTF18aaasyae', 'ZTF18acepwhb', 'ZTF18achtpcj', 'ZTF18accbwow', 'ZTF18aathgma', 'ZTF18accjvic', 'ZTF18abccpml')
In [112]:
# 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-2458448.9238494374 AS ago, magpsf, sigmapsf, fid FROM candidates WHERE objectid IN ('ZTF18abslpjy', 'ZTF18aaaaygy', 'ZTF18abxqemv', 'ZTF18acgvlrr', 'ZTF18abrqfjs', 'ZTF18acmzvqx', 'ZTF18aagpyca', 'ZTF18aazwddf', 'ZTF18aabqewr', 'ZTF18aaasyae', 'ZTF18acepwhb', 'ZTF18achtpcj', 'ZTF18accbwow', 'ZTF18aathgma', 'ZTF18accjvic', 'ZTF18abccpml');
In [113]:
ztf_lc_res = Table(rows=result, names=("objectid", "ago", "magpsf", "sigmapsf", "fid")).group_by("objectid")
ztf_lc_res
Out[113]:
<Table length=567>
objectidagomagpsfsigmapsffid
str12float64float64float64int64
ZTF18aaaaygy-75.906870237618.84670.1072472
ZTF18aaaaygy-72.908664237218.95640.105862
ZTF18aaaaygy-69.915874937518.93380.08655672
ZTF18aaaaygy-61.979115637518.84770.1529932
ZTF18aaaaygy-58.904856337218.90950.1352282
ZTF18aaaaygy-24.905736037518.86070.08477982
ZTF18aaaaygy-21.991673537518.89930.08218252
ZTF18aaaaygy-19.035180437418.86560.09912782
ZTF18aaaaygy-18.917888837418.89330.08640122
ZTF18aaaaygy-16.055863337618.9220.09264512
...............
ZTF18achtpcj-10.980608737617.42770.0428251
ZTF18achtpcj-7.995712837217.90220.05273761
ZTF18achtpcj-5.0090114371918.40070.06567932
ZTF18achtpcj-4.918687437218.2860.05849391
ZTF18achtpcj-1.1128656375218.75490.1346142
ZTF18achtpcj-1.0112568372918.96180.1630722
ZTF18acmzvqx-11.024914237219.83550.1975991
ZTF18acmzvqx-11.024011437320.04250.19761
ZTF18acmzvqx-7.153317037519.60260.2028141
ZTF18acmzvqx-7.1104003372619.70550.197762

For each luminous object, plot the light curve

In [114]:
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()