In [2]:
import sqlite3
from astropy.table import Table, vstack
from cesium import featurize
import numpy as np
In [4]:
curve = Table.read('scripts/ptf_query/byOid/curves_oid___51892000005737.tbl', format = 'ipac')
curve2 = Table.read('scripts/ptf_query/byOid/curves_oid___51892060003794.tbl', format = 'ipac')
curve3 = Table.read('scripts/ptf_query/byOid/curves_oid___51892060016842.tbl', format = 'ipac')
In [4]:
feats_to_use = ['amplitude',
'flux_percentile_ratio_mid20',
'flux_percentile_ratio_mid35',
'flux_percentile_ratio_mid50',
'flux_percentile_ratio_mid65',
'flux_percentile_ratio_mid80',
'max_slope',
'maximum',
'median',
'median_absolute_deviation',
'minimum',
'percent_amplitude',
'percent_beyond_1_std',
'percent_close_to_median',
'percent_difference_flux_percentile',
'period_fast',
'qso_log_chi2_qsonu',
'qso_log_chi2nuNULL_chi2nu',
'skew',
'std',
'stetson_j',
'stetson_k',
'weighted_average',
'fold2P_slope_10percentile',
'fold2P_slope_90percentile',
'freq1_amplitude1',
'freq1_amplitude2',
'freq1_amplitude3',
'freq1_amplitude4',
'freq1_freq',
'freq1_lambda',
'freq1_rel_phase2',
'freq1_rel_phase3',
'freq1_rel_phase4',
'freq1_signif',
'freq2_amplitude1',
'freq2_amplitude2',
'freq2_amplitude3',
'freq2_amplitude4',
'freq2_freq',
'freq2_rel_phase2',
'freq2_rel_phase3',
'freq2_rel_phase4',
'freq3_amplitude1',
'freq3_amplitude2',
'freq3_amplitude3',
'freq3_amplitude4',
'freq3_freq',
'freq3_rel_phase2',
'freq3_rel_phase3',
'freq3_rel_phase4',
'freq_amplitude_ratio_21',
'freq_amplitude_ratio_31',
'freq_frequency_ratio_21',
'freq_frequency_ratio_31',
'freq_model_max_delta_mags',
'freq_model_min_delta_mags',
'freq_model_phi1_phi2',
'freq_n_alias',
'freq_signif_ratio_21',
'freq_signif_ratio_31',
'freq_varrat',
'freq_y_offset',
'linear_trend',
'medperc90_2p_p',
'p2p_scatter_2praw',
'p2p_scatter_over_mad',
'p2p_scatter_pfold_over_mad',
'p2p_ssqr_diff_over_var',
'scatter_res_raw'
]
In [5]:
fset = featurize.featurize_time_series(curve['obsmjd'], curve['mag_autocorr'], curve['magerr_auto'],
meta_features = {'oid': str(curve['oid'][0])}, features_to_use = feats_to_use)
fset2 = featurize.featurize_time_series(curve2['obsmjd'], curve2['mag_autocorr'], curve2['magerr_auto'],
meta_features = {'oid': str(curve2['oid'][0])}, features_to_use = feats_to_use)
fset3 = featurize.featurize_time_series(curve3['obsmjd'], curve3['mag_autocorr'], curve3['magerr_auto'],
meta_features = {'oid': str(curve3['oid'][0])}, features_to_use = feats_to_use)
In [10]:
conn = sqlite3.connect('features.db')
cur = conn.cursor()
In [9]:
print(curve.colnames)
In [55]:
cur.execute("""create table lightcurves(
oid string primary key,
obsmjd Float,
mag_autocorr Float,
magerr_auto Float,
fid Float,
ra Float,
dec Float,
clon Float,
clat Float
)""")
Out[55]:
In [3]:
cur.execute("""PRAGMA table_info(lightcurves)""")
cur.fetchall()
Out[3]:
In [3]:
cur.execute("""select oid from lightcurves""")
cur.fetchall()
Out[3]:
In [12]:
cur.execute("""drop table sigfeats""")
Out[12]:
In [14]:
cur.execute("""create table sigfeats(
amplitude Float,
flux_percentile_ratio_mid20 Float,
flux_percentile_ratio_mid35 Float,
flux_percentile_ratio_mid50 Float,
flux_percentile_ratio_mid65 Float,
flux_percentile_ratio_mid80 Float,
max_slope Float,
maximum Float,
median Float,
median_absolute_deviation Float,
minimum Float,
percent_amplitude Float,
percent_beyond_1_std Float,
percent_close_to_median Float,
percent_difference_flux_percentile Float,
period_fast Float,
qso_log_chi2_qsonu Float,
qso_log_chi2nuNULL_chi2nu Float,
skew Float,
std Float,
stetson_j Float,
stetson_k Float,
weighted_average Float,
fold2P_slope_10percentile Float,
fold2P_slope_90percentile Float,
freq1_amplitude1 Float,
freq1_amplitude2 Float,
freq1_amplitude3 Float,
freq1_amplitude4 Float,
freq1_freq Float,
freq1_lambda Float,
freq1_rel_phase2 Float,
freq1_rel_phase3 Float,
freq1_rel_phase4 Float,
freq1_signif Float,
freq2_amplitude1 Float,
freq2_amplitude2 Float,
freq2_amplitude3 Float,
freq2_amplitude4 Float,
freq2_freq Float,
freq2_rel_phase2 Float,
freq2_rel_phase3 Float,
freq2_rel_phase4 Float,
freq3_amplitude1 Float,
freq3_amplitude2 Float,
freq3_amplitude3 Float,
freq3_amplitude4 Float,
freq3_freq Float,
freq3_rel_phase2 Float,
freq3_rel_phase3 Float,
freq3_rel_phase4 Float,
freq_amplitude_ratio_21 Float,
freq_amplitude_ratio_31 Float,
freq_frequency_ratio_21 Float,
freq_frequency_ratio_31 Float,
freq_model_max_delta_mags Float,
freq_model_min_delta_mags Float,
freq_model_phi1_phi2 Float,
freq_n_alias Float,
freq_signif_ratio_21 Float,
freq_signif_ratio_31 Float,
freq_varrat Float,
freq_y_offset Ffloat,
linear_trend Float,
medperc90_2p_p Float,
p2p_scatter_2praw Float,
p2p_scatter_over_mad Float,
p2p_scatter_pfold_over_mad Float,
p2p_ssqr_diff_over_var Float,
scatter_res_raw Float,
oid string unique primary key,
sigclipped Boolean
)""")
Out[14]:
In [15]:
cur.execute("""PRAGMA table_info(sigfeats)""")
cur.fetchall()
Out[15]:
In [2]:
def saveFeat (lc, tName, cur, conn): #pass in lightcurve table and cursor
feats_to_use = [
'amplitude',
'flux_percentile_ratio_mid20',
'flux_percentile_ratio_mid35',
'flux_percentile_ratio_mid50',
'flux_percentile_ratio_mid65',
'flux_percentile_ratio_mid80',
'max_slope',
'maximum',
'median',
'median_absolute_deviation',
'minimum',
'percent_amplitude',
'percent_beyond_1_std',
'percent_close_to_median',
'percent_difference_flux_percentile',
'period_fast',
'qso_log_chi2_qsonu',
'qso_log_chi2nuNULL_chi2nu',
'skew',
'std',
'stetson_j',
'stetson_k',
'weighted_average',
'fold2P_slope_10percentile',
'fold2P_slope_90percentile',
'freq1_amplitude1',
'freq1_amplitude2',
'freq1_amplitude3',
'freq1_amplitude4',
'freq1_freq',
'freq1_lambda',
'freq1_rel_phase2',
'freq1_rel_phase3',
'freq1_rel_phase4',
'freq1_signif',
'freq2_amplitude1',
'freq2_amplitude2',
'freq2_amplitude3',
'freq2_amplitude4',
'freq2_freq',
'freq2_rel_phase2',
'freq2_rel_phase3',
'freq2_rel_phase4',
'freq3_amplitude1',
'freq3_amplitude2',
'freq3_amplitude3',
'freq3_amplitude4',
'freq3_freq',
'freq3_rel_phase2',
'freq3_rel_phase3',
'freq3_rel_phase4',
'freq_amplitude_ratio_21',
'freq_amplitude_ratio_31',
'freq_frequency_ratio_21',
'freq_frequency_ratio_31',
'freq_model_max_delta_mags',
'freq_model_min_delta_mags',
'freq_model_phi1_phi2',
'freq_n_alias',
'freq_signif_ratio_21',
'freq_signif_ratio_31',
'freq_varrat',
'freq_y_offset',
'linear_trend',
'medperc90_2p_p',
'p2p_scatter_2praw',
'p2p_scatter_over_mad',
'p2p_scatter_pfold_over_mad',
'p2p_ssqr_diff_over_var',
'scatter_res_raw'
]
string = "insert into " + tName + """ values (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
cur.execute("""select oid from {:}""".format(tName))
check = cur.fetchall()
for oid in np.unique(lc['oid']):
if (oid not in check):
mask = np.logical_and(lc['oid'] == oid, lc['mag_autocorr'] > 0)
fset = featurize.featurize_time_series(lc[mask]['obsmjd'], lc[mask]['mag_autocorr'], lc[mask]['magerr_auto'],
meta_features = {'oid': str(oid)}, features_to_use = feats_to_use)
cur.execute(string, fset.get_values()[0])
else:
print('Database already contains a ', oid)
conn.commit()
In [46]:
saveFeat(curve, 'feats2', cur, conn)
In [47]:
saveFeat(curve2, 'feats2', cur, conn)
In [48]:
saveFeat(curve3, 'feats2', cur, conn)
In [11]:
cur.execute("""select oid from feats2""")
len(cur.fetchall())
Out[11]:
In [50]:
sqlite3.Connection.close(conn)