<jupyter_start><jupyter_text>Querying ALeRCE - LC Classifier```Author: <NAME>, Last updated 20210426```  Table of contents:* [Querying the ALeRCE API Python Client](api)* [Replicating process with direct database query](db)* [Comparing API and DB outputs](compare)* [Understanding retrieved object data](data)* [Exporting to VOTable](export) **Goal:** 1) To query the ALeRCE database for objects with the following attributes:* detected 24 - 48 hours from the current time* classified by the LC classifier2) To return a table consisting of ALeRCE alert objects that includes, per row:* aggregated detection properties per object (e.g. mean RA/Dec, number of detections)* probability of the highest ranking class assigned by the LC classifierWe will try this by querying the ALeRCE API first, and then directly querying the ZTF database.<jupyter_code>import sys

# Packages for direct database access
# %pip install psycopg2
import psycopg2
import json

# Packages for data and number handling
import numpy as np
import pandas as pd
import math

# Packages for calculating current time and extracting ZTF data to VOTable
from astropy.time import Time
from astropy.table import Table, unique, vstack
from astropy.io.votable import from_table, writeto
from datetime import datetime

# Packages for display and data plotting, if desired
from IPython.display import HTML
from IPython.display import display
import matplotlib.pyplot as plt
%matplotlib inline
# Set up ALeRCE python client
from alerce.core import Alerce
client = Alerce()<jupyter_output><empty_output><jupyter_text>Querying the ALeRCE API Python Client We will retrieve these objects per class, first by building a function that uses the ALeRCE client to query objects according to stamp classifier predictions.Note that according to the ZTF API (```ztf-api/api/sql/astro_object/astro_object.py```), the default ranking for ```query_objects``` when ranking is not specified is 1.<jupyter_code># Define function that queries objects according to class
def query_class_objects(cn, min_lastmjd, max_lastmjd):
    objects = client.query_objects(classifier = 'lc_classifier',
                                   classifier_version = 'hierarchical_random_forest_1.0.0',
                                   class_name = cn,
                                   lastmjd = [min_lastmjd, max_lastmjd],
                                   page_size = int(1e6),
                                   format='pandas')
    return objects
# Querying the ALeRCE client for objects detected 24 - 48 hours from the current time, over a range of classes
min_lastmjd = Time(datetime.today(), scale='utc').mjd - 2
max_lastmjd = Time(datetime.today(), scale='utc').mjd - 1
classes = client.query_classifiers()[0]['classes']
apiobjects = Table()

for class_name in classes:
    class_objects = query_class_objects(class_name, min_lastmjd, max_lastmjd)
    if class_name == classes[0]:
        apiobjects = class_objects
    else:
        apiobjects = pd.concat([apiobjects, class_objects])
    
    print('Class queried: %s' % (class_name))
    
    if class_name == classes[-1]:
        print('Done.')
# Prints the dataframe shape: (number of selected objects, number of selected filters)
print(apiobjects.shape)

# Sorting detections by lastMJD, firstMJD, and OID in descending order
apiobjects = apiobjects.sort_values(by=['lastmjd', 'firstmjd', 'oid'], ascending=False)
apiobjects.head()<jupyter_output>(110105, 23)<jupyter_text>Replicating process with direct database query<jupyter_code># Open and load credentials
credentials_file = "../alercereaduser_v4.json"
with open(credentials_file) as jsonfile:
    params = json.load(jsonfile)["params"]
    
# Open a connection to the database
conn = psycopg2.connect(dbname=params['dbname'], 
                        user=params['user'], 
                        host=params['host'], 
                        password=params['password'])
query='''
SELECT
    object.oid, object.meanra, object.meandec, object.sigmara, object.sigmadec,
    object.firstmjd, object.lastmjd, object.ndet, 
    pr.classifier_name, pr.classifier_version, pr.class_name, 
    pr.ranking, pr.probability

FROM 
    object INNER JOIN (
        SELECT 
            probability.oid, probability.classifier_name, probability.classifier_version,
            probability.class_name, probability.ranking, probability.probability
        FROM
            probability
        WHERE
            probability.classifier_name = 'lc_classifier'
            AND probability.classifier_version = 'hierarchical_random_forest_1.0.0'
            AND probability.ranking = 1
    ) AS pr
    ON object.oid = pr.oid

WHERE 
    object.lastMJD >= %s
    AND object.lastMJD <= %s
''' % (min_lastmjd, max_lastmjd)

# Outputs as a pd.DataFrame
dbobjects = pd.read_sql_query(query, conn)
# Prints the dataframe shape: (number of selected objects, number of selected filters)
print(dbobjects.shape)

# Sorting detections by lastMJD in descending order
dbobjects = dbobjects.sort_values(by=['lastmjd', 'firstmjd', 'oid'], ascending=False)
dbobjects.head()<jupyter_output>(110105, 13)<jupyter_text>Comparing API and DB outputs<jupyter_code># Check that the OIDs of each row in the API table are identical to that of the corresponding row in the DB table
print(set(dbobjects['oid'].values==apiobjects['oid'].values))<jupyter_output>{True}<jupyter_text>Understanding retrieved object data For this, we'll only look at the dataframe retrieved from the API client (which is alright, as the ```dbobjects``` and ```apiobjects``` dataframes encompass the same OIDs.) The following prints out the number of OIDs that correspond to each class name:<jupyter_code># Count number of OIDs that correspond to each class name
print('Total rows : %i' % (len(apiobjects.index)))
obj_classes = apiobjects.groupby('class')
for key in obj_classes.groups.keys():
    l = obj_classes.groups[key].size
    print('%s : %i' % (key, l))
# Identify duplicate OID entries - rows with same OID but different classes and probabilities
obj_oid = apiobjects.groupby(['oid'])
duplicates = []
for key in obj_oid.groups.keys():
    l = obj_oid.groups[key].size
    if l > 1:
        oid = key
        duplicates.append(oid)

print('Number of OIDs with more than one row : %i' % (len(duplicates)))
print('Number of unique OIDs : %i' % len(obj_oid))
# Print example rows with duplicate OIDs
if len(duplicates) > 0:
    l = duplicates[0:3]
    display(apiobjects[apiobjects['oid'].isin(l)])<jupyter_output>Number of OIDs with more than one row : 173
Number of unique OIDs : 109932<jupyter_text>Exporting to VOTable To save this data as a VOTable requires converting it from its current form (a ```pd.DataFrame```). This is possible with the ```Table``` object from ```astropy.table```, and the functions we initially imported from ```astropy.io.votable```. Essentially, we'll convert our ```pd.DataFrame``` to an ```astropy.table.Table``` to a ```astropy.io.votable.VOTableFile```, which can then be exported._A buggy caveat, however_ -- ```astropy.io.votable.VOTableFile``` objects throw an error when you attempt to pass on masked/```NaN``` values. I've gotten around this, for now, by filling in the masked values the the _string_ ```"None"``` before the ```pd.DataFrame``` is converted to a ```Table```.<jupyter_code># Defining a function that allows you to export the dataframe into a VOTable
def export_object_data(objects_df, filename):
    # Filling the masked values with the string 'NaN'
    objects_filled = objects_df.fillna('None')

    # Converting filled dataframe to astropy Table, then astropy VOTableFile, then exporting into .xml
    full_dt = Table.from_pandas(objects_filled)
    votable = from_table(full_dt)
    writeto(votable, filename)
# Export VOTable with only the OIDs that have multiple rows (with different classes)
l = duplicates[0:3]
duplicates_df = apiobjects[apiobjects['oid'].isin(l)]
export_object_data(duplicates_df, 'VOTables/ztf_api_lc_objects_duplicates.xml')
# Export VOTable with only unique OIDs
unique_df = apiobjects.drop_duplicates(subset=['oid'])
export_object_data(unique_df, 'VOTables/ztf_api_lc_objects_unique.xml')
# Export VOTable with all OIDs
export_object_data(apiobjects, 'VOTables/ztf_api_lc_objects.xml')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09504830917874396, 'fraction_numerical': 0.00821256038647343, 'mean_word_length': 4.291373801916933, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 4}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '333643', 'license': 'MIT', 'n_tokens_mistral': 2548, 'n_tokens_neox': 2426, 'n_words': 816, 'path': 'example_notebooks/ZTF_Lightcurve_API-DB_Query.ipynb', 'repo_name': 'edengirma/CDAAnnotation', 'size': '8284'}