超新星資料爬取

      在〈超新星資料爬取〉中尚無留言

超新星爆炸的相關資料非常多，使用用資料庫儲存，相關資料表欄位如下。

object 資料表

CREATE TABLE `object` (
  `id` int NOT NULL AUTO_INCREMENT,
  `objectid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sn` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `z` double DEFAULT NULL,
  `discovery_mjd` double DEFAULT '0',
  `peak_mjd` double DEFAULT '0',
  `query` int DEFAULT '0',
  `final` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `objectid_UNIQUE` (`objectid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

bright 資料表

CREATE TABLE `bright` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sn` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `objectid` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `candid` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `jd` double DEFAULT NULL,
  `ra` double DEFAULT NULL,
  `declination` double DEFAULT NULL,
  `fid` double DEFAULT NULL,
  `nid` double DEFAULT NULL,
  `magpsf` double DEFAULT NULL,
  `sigmapsf` double DEFAULT NULL,
  `magnr` double DEFAULT NULL,
  `sigmagnr` double DEFAULT NULL,
  `magzpsci` double DEFAULT NULL,
  `isdiffpos` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ssdistnr` double DEFAULT NULL,
  `ssnamenr` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `drb` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

final 資料表

CREATE TABLE `final` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sn` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `objectid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mjd` double DEFAULT NULL,
  `ra` double DEFAULT NULL,
  `declination` double DEFAULT NULL,
  `fid` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nid` double DEFAULT NULL,
  `magpsf` double DEFAULT NULL,
  `sigmapsf` double DEFAULT NULL,
  `magnr` double DEFAULT NULL,
  `sigmagnr` double DEFAULT NULL,
  `isdiffpos` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `drb` double DEFAULT NULL,
  `z` double DEFAULT NULL,
  `peak_mjd` double DEFAULT NULL,
  `amw_tmp` double DEFAULT NULL,
  `amw` double DEFAULT NULL,
  `mag_corr` double DEFAULT NULL,
  `rfd` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

dropobject

CREATE TABLE `dropobject` (
  `id` int NOT NULL AUTO_INCREMENT,
  `objectid` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `objectid_UNIQUE` (`objectid`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

bright_view

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `thomas`@`%` 
    SQL SECURITY DEFINER
VIEW `supernova`.`bright_view` AS
    SELECT 
        `a`.`objectid` AS `objectid`,
        `b`.`sn` AS `sn`,
        `b`.`candid` AS `candid`,
        `b`.`jd` AS `jd`,
        `b`.`ra` AS `ra`,
        `b`.`declination` AS `declination`,
        `b`.`fid` AS `fid`,
        `b`.`nid` AS `nid`,
        `b`.`magpsf` AS `magpsf`,
        `b`.`sigmapsf` AS `sigmapsf`,
        `b`.`magnr` AS `magnr`,
        `b`.`sigmagnr` AS `sigmagnr`,
        `b`.`magzpsci` AS `magzpsci`,
        `b`.`isdiffpos` AS `isdiffpos`,
        `b`.`ssdistnr` AS `ssdistnr`,
        `b`.`ssnamenr` AS `ssnamenr`,
        `b`.`drb` AS `drb`,
        `a`.`z` AS `z`,
        `a`.`discovery_mjd` AS `discovery_mjd`,
        `a`.`peak_mjd` AS `peak_mjd`
    FROM
        (`supernova`.`object` `a`
        JOIN `supernova`.`bright` `b` ON ((`a`.`objectid` = `b`.`objectid`)))

下載網址

網址如下

https://sites.astro.caltech.edu/ztf/bts/explorer.php?f=s&subsample=cantrans&classstring=SN+Ia&classexclude=&ps1img=y&lcfig=y&ztflink=lasair&lastdet=&startsavedate=&startpeakdate=&startra=&startdec=&startz=&startdur=&startrise=&startfade=&startpeakmag=&startabsmag=&starthostabs=&starthostcol=&startb=&startav=&endsavedate=&endpeakdate=&endra=&enddec=&endz=&enddur=&endrise=&endfade=&endpeakmag=18.5&endabsmag=&endhostabs=&endhostcol=&endb=&endav=&sort=b

todo

爬取 object

todo

import time
import threading
import re
import requests
from G import G
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.common.by import By
from selenium.webdriver.chrome.options import Options
from bs4 import BeautifulSoup
from webdriver_manager.chrome import ChromeDriverManager
index = 1
def get_objects():
    sns = ["Ia","Ib","Ic","IIP","IIn"]
    conn, cursor=G.connect(host="localhost")
    for sn in sns:
        print(f"取得 sn : {sn}")
        url = f"https://sites.astro.caltech.edu/ztf/bts/explorer.php?f=s&subsample=cantrans&classstring=SN+{sn}&classexclude=&ps1img=y&lcfig=y&ztflink=lasair&lastdet=&startsavedate=&startpeakdate=&startra=&startdec=&startz=&startdur=&startrise=&startfade=&startpeakmag=&startabsmag=&starthostabs=&starthostcol=&startb=&startav=&endsavedate=&endpeakdate=&endra=&enddec=&endz=&enddur=&endrise=&endfade=&endpeakmag=18.5&endabsmag=&endhostabs=&endhostcol=&endb=&endav=&sort=b"
        page = requests.get(url)
        soup = BeautifulSoup(page.text, 'html.parser')
        tables = soup.find_all("table")
        trs = tables[len(tables) - 1].find_all("tr")
        for tr in trs:
            tds = tr.find_all("td")
            if len(tds) > 0:
                node=tds[2].find("a")
                objectid=node.text
                href=node.get("href")
                cursor.execute(f"insert into object (objectid, url, sn) values ('{objectid}','{href}','{sn}')")
                conn.commit()
    conn.close()
def get_db():
    conn, cursor=G.connect(host="localhost")
    cursor.execute("select * from object where z is null")
    rs=cursor.fetchall()
    conn.close()
    total=len(rs)
    lines=1
    batch=total//lines
    for i in range(lines):
        t=threading.Thread(target=task,name=f'Thread-{i}', args=(rs[batch*i:batch*(i+1)],))
        t.start()
    if total>lines and total%lines!=0:
        t=threading.Thread(target=task,name=f'Thread-{lines}', args=(rs[batch*lines:],))
        t.start()
def task(rs):
    t1=time.time()
    global index
    opt = Options()
    opt.add_argument('--headless')
    opt.add_argument('--disable-gpu')
    opt.add_experimental_option('detach', True)
    service = Service(ChromeDriverManager().install())
    browser = webdriver.Chrome(service=service, options=opt)
    for r in rs:
        id=r[0]
        href=r[2]
        peak_mjd = 0
        try:
            browser.get(href)
            browser.find_element(By.CSS_SELECTOR, ".btn.py-1.btn-secondary.mb-2.ms-auto").click()
            soup = BeautifulSoup(browser.page_source, 'html.parser')
            tds = soup.find_all("td")
            for i, td in enumerate(tds):
                if "Peak MJD" in td.text:
                    peak_mjd = tds[i + 1].text
                    break

            nodes = soup.find_all('small', {'class': 'text-gray-500'})
            z = 0
            discovery_mjd = 0
            for node in nodes:
                if "The transient was discovered" in node.text:
                    try:
                        z = node.text.split("at z = ")[1].strip()[:-1]
                        match = re.search(r"MJD\s*([0-9]*\.?[0-9]+)", node.text)
                        discovery_mjd = float(match.group(1))
                    except:
                        pass
            cmd = f"update object set z={z}, discovery_mjd={discovery_mjd},peak_mjd={peak_mjd} where id={id}"
            print(f"第{index:04d}筆 : {cmd}")
            conn, cursor = G.connect(host="localhost")
            cursor.execute(cmd)
            conn.commit()
            conn.close()
            index += 1
        except Exception as e:
            print(e)
    t2=time.time()
    print(f"花費時間 {t2-t1}秒")
ans=input("\n接續上次未完請按 1 , 重新爬取請按 2 : ")

if ans=="1":
    get_db()
elif ans=="2":
    conn_global, cursor_global=G.connect(host="localhost")
    cmd="""
    drop table if exists `object`;
    CREATE TABLE `object` (
      `id` int NOT NULL AUTO_INCREMENT,
      `objectid` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `url` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `sn` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `z` double DEFAULT NULL,
      `discovery_mjd` double DEFAULT '0',
      `peak_mjd` double DEFAULT '0',
      `query` int DEFAULT '0',
      `final` int DEFAULT '0',      
      PRIMARY KEY (`id`),
      UNIQUE KEY `objectid_UNIQUE` (`objectid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """
    Service(ChromeDriverManager().install())
    cursor_global.execute(cmd)
    conn_global.close()
    get_objects()
    get_db()

todo

爬取 bright

todo

import time
from datetime import datetime

import requests
from G import G
def get_data(sn, object_id):
    url = "https://lasair-ztf.lsst.ac.uk/api/lightcurves/"
    params = {
        "objectIds": object_id,
        "format": "json",
        "token": "your lasair token"
    }
    resp = requests.get(url, params=params)
    result = resp.json()
    datas = result[0]["candidates"]

    rows = []
    for data in datas:
        row = [sn, objectid]
        for key in keys:
            if key in ['candid', 'isdiffpos', 'ssnamenr']:
                tmp = data.get(key, "")
            else:
                tmp = data.get(key, 0)
                if tmp is None or tmp == "null":
                    tmp = 0
            row.append(tmp)
        rows.append(row)
    return rows
#主程式
keys = ['candid', 'jd', 'ra', 'dec', 'fid', 'nid', 'magpsf', 'sigmapsf', 'magnr', 'sigmagnr', 'magzpsci', 'isdiffpos', 'ssdistnr', 'ssnamenr', 'drb']
conn, cursor=G.connect(host="localhost")
cursor.execute("select * from bright")
cursor.fetchall()
columns=[d[0] for d in cursor.description]
count=len(columns)-1
columns=str(columns)[7:-1].replace("'","")
conn.close()
cmd = f"insert into bright ({columns}) values ({('%s,'*count)[:-1]})"
print(cmd)

ans=input("\n接續上次未完請按 1 , 重新爬取請按 2 : ")
if ans=="1":
    print("\n接續上次未完.....")
elif ans=="2":
    print("\nReset 資料庫.....")
    conn, cursor = G.connect(host="localhost")
    cursor.execute("truncate table bright")
    cursor.execute("update object set query=0")
    conn.commit()
    conn.close()

index = 0
start_block=0
t1=time.time()
while True:
    try:
        conn, cursor = G.connect(host="localhost")
        #cursor.execute("select * from object where z is not null and z!=0 and query=0")
        cursor.execute("select * from object where z is not null and query=0")
        rs=cursor.fetchall()
        conn.close()
        if len(rs)==0:
            break
        for r in rs:
            objectid=r[1]
            sn=r[3]
            datas = get_data(sn,objectid)
            for data in datas:
                print(data)
            conn, cursor = G.connect(host="localhost")
            cursor.executemany(cmd, datas)
            conn.commit()

            #設定 object 資料表為已查詢
            cursor.execute(f"update object set query=1 where objectid='{objectid}'")
            conn.commit()
            conn.close()
            index+=1
            start_block = 0
            print(f"已成功爬取第{index:04d} object")
    except Exception as e:
        print(e)
        if start_block==0:start_block=datetime.now()
        current=datetime.now()
        second=(current-start_block).total_seconds()
        minutes=int(second/60)
        conn, cursor = G.connect(host="localhost")
        cursor.execute(f"select count(*) from object")
        total=cursor.fetchall()[0][0]
        cursor.execute(f"select count(*) from object where query=0")
        remaining=cursor.fetchall()[0][0]
        conn.close()
        #print(f"\rLasair token 權限已用盡, 需靜待約 1 小時, 已等待 {minutes:3d} 分鐘。 total : {total}, done : {total-remaining}, remaining : {remaining}", end="")
        print(f"Lasair token 權限已用盡, 需靜待約 1 小時, 已等待 {minutes:3d} 分鐘。 total : {total}, done : {total - remaining}, remaining : {remaining}")
        time.sleep(60)
t2=time.time()
print(f"總共花費 : {t2-t1}秒")

todo

計算 rfd

todo

#pip install scipy astroquery  astropy
import threading
import time
from G import G
import pandas as pd
from astroquery.ipac.irsa.irsa_dust import IrsaDust
from astropy.coordinates import SkyCoord
import astropy.units as u
import warnings
display=pd.options.display
display.max_columns=None
display.max_rows=None
display.width=None
display.max_colwidth=None
create_table_cmd="""
CREATE TABLE `final` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sn` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `objectid` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mjd` double DEFAULT NULL,
  `ra` double DEFAULT NULL,
  `declination` double DEFAULT NULL,
  `fid` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nid` double DEFAULT NULL,
  `magpsf` double DEFAULT NULL,
  `sigmapsf` double DEFAULT NULL,
  `magnr` double DEFAULT NULL,
  `sigmagnr` double DEFAULT NULL,
  `isdiffpos` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `drb` double DEFAULT NULL,
  `z` double DEFAULT NULL,
  `peak_mjd` double DEFAULT NULL,  
  `amw_tmp` double DEFAULT NULL,
  `amw` double DEFAULT NULL,
  `mag_corr` double DEFAULT NULL,
  `rfd` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

def get_ebv_mw(ra, dec):
    coord=SkyCoord(ra*u.deg, dec*u.deg)
    tbl=IrsaDust.get_query_table(coord, section="ebv")
    return float(tbl["ext SandF mean"][0])
#warnings.filterwarnings("ignore", category=UserWarning)


def task(rs):
    global index
    for object in rs:
        try:
            t1 = time.time()
            conn, cursor = G.connect(host=host)
            cursor.execute(f"select * from bright_view where objectid='{object}' and isdiffpos='t'")
            rs = cursor.fetchall()
            cols = [d[0] for d in cursor.description]
            conn.close()

            cols[cols.index("jd")] = "mjd"
            cols[cols.index("declination")] = "dec"
            df = pd.DataFrame(data=rs, columns=cols)
            df = df.sort_values(by=["objectid", "fid"], ascending=[True, False])
            df = df[['sn', 'objectid', 'mjd', 'ra', 'dec', 'fid', 'nid', 'magpsf', 'sigmapsf', 'magnr', 'sigmagnr',
                     'isdiffpos', 'drb', 'z', 'peak_mjd']]
            df = df[df['sigmapsf'] < 0.3]
            # df=df[df['isdiffpos']=="t"]
            # df=df[df['drb']>0.5]

            df['mjd'] = df['mjd'] - 2400000.5
            df['fid'] = df['fid'].apply(lambda x: 'r' if x == 1 else ("g" if x == 2 else "b"))
            df.insert(len(df.columns), "amw_tmp", 0)
            df.insert(len(df.columns), "amw", 0)
            df.insert(len(df.columns), "mag_corr", 0)
            df.insert(len(df.columns), "rfd", 0)

            df['amw_tmp'] = df['fid'].apply(lambda x: 3.303 if x == 'r' else (2.285 if x == 'g' else 1.698))
            df['amw'] = df.apply(lambda row: row['amw_tmp'] * get_ebv_mw(row['ra'], row['dec']), axis=1)
            df['mag_corr'] = df['magpsf'] - df['amw']
            df = df[df['mag_corr'].between(14, 25)]
            df = df.reset_index(drop=True)

            df['rfd'] = (df['mjd'] - df['peak_mjd']) / (1 + df['z'])

            datas = []
            for r in df.values:
                datas.append(list(r))

            conn, cursor = G.connect(host=host)
            cursor.executemany(insert_cmd, datas)
            conn.commit()
            cursor.execute(f"update object set final=1 where objectid='{object}'")
            conn.commit()
            conn.close()
            t2=time.time()
            print(f"{index:04d} {object} 計算完成...{len(df):04d}筆....{t2-t1:.3f}秒")
        except Exception as e:
            print(f"{object} 無法計算, {e}")
        index+=1

host="localhost"
lines=100

conn_global, cursor_global = G.connect(host=host)
# cursor.execute('drop table if exists final')
# cursor.execute(create_table_cmd)
ans=input("resume(1) , rebuilt(2) : ")
if ans=="2":
    cursor_global.execute("truncate table final")
    cursor_global.execute("update object set final=0")
    conn_global.commit()
#cursor.execute("select objectid from bright_view group by objectid")
#cursor.execute("select objectid from object where z>0 and objectid not in (select objectid from dropobject)")
cursor_global.execute("select objectid from object where z>0 and objectid not in (select objectid from dropobject) and final=0")
objects=cursor_global.fetchall()

index=1
cursor_global.execute('select * from final')
columns=[d[0] for d in cursor_global.description]
count=len(columns)-1
columns=str(columns)[8:-2].replace("'","")
insert_cmd=f"insert into final ({columns}) values ({('%s,'*count)[:-1]})"
conn_global.close()

objects=[o[0] for o in objects]
total=len(objects)
batch=total//lines
for i in range(lines):
    t=threading.Thread(target=task,name=f"Thread-{i}", args=(objects[i*batch:(i+1)*batch],))
    t.start()
if total%lines!=0:
    t=threading.Thread(target=task, name=f"Thread-{lines}",args=(objects[lines*batch:],))
    t.start()

todo

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *