<jupyter_start><jupyter_text>系統維護工具V0.4  Init All<jupyter_code>import pandas as pd
from codes.db import * 
from codes.lib import *
from codes.riverlog import *
import pandasql as ps
import os

gd={}
conn=connect_db()
if 1:
    load_ods(gd)
    riverlog_info_setup(gd)<jupyter_output>Connected to the PostgreSQL database...
sys 的 sheets:
 dict_keys(['description', 'table_def', 's_syspar', 's_info_line', 's_info_area', 's_timeseq_rpt', 's_repeat_job', 's_topology_kind', 's_gis_layer', 's_info_point', 's_topology_transfer', 's_topology_node', 's_topology_edge', 's_village_waterin', 's_waterin_qty', 's_waterwork_qty', 's_waterin_quality', 's_value_domain', 's_waterin_b', 's_df_brief', '污水下水道用戶接管普及率', '工作表24']) 
basic 的 sheets:
 dict_keys(['table_def', 'b_表單說明', 'b_colmeta', 'b_水資源分區', 'b_水資源局', 'b_河川局', 'b_流域', 'b_河川', 'b_排水', 'b_水庫', 'b_水質水量保護區', 'b_水庫集水範圍', 'b_水庫集水區敏感區', 'b_堤防', 'b_排水設施', 'b_水門', 'b_抽水站', 'b_河川斷面樁', 'b_雨量站', 'b_河川水位站', 'b_浮標站', 'b_潮位站', 'b_地下水分區', 'b_地下水觀測井']) 
rain-station: output/rain-station.csv saved, shape = (2251, 6)
reservoir-info: output/reservoir-info.csv saved, shape = (152, 22)
waterLevel-station: output/waterLevel-station.csv saved, shape = (5176, 13)
waterLevelDrain-station: output/waterLevelDrain-station.csv saved, shape = (1189, 4)
wat[...]<jupyter_text>維護將以下的 ods 灌入 DBsys.ods, basic.ods, riverlog 基本資料<jupyter_code>import subprocess
import os
import re
def exec_print(cmd_str):
    print("executing : %s" %(cmd_str)) 
    p = subprocess.Popen(cmd_str, shell=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
    for line in p.stdout.readlines():
        print(line)
    retval = p.wait()    
    
def str_tran(line):
    ret = line
    g = re.match( r'(\d*)年(\d*)月(\d*)日', line, re.M|re.I)

    if g:
        year = int(g.group(1))+1911
        date_str = "%i-%s-%s" %(year,g.group(2),g.group(3))
        #print(date_str)
        ret=date_str
    return ret

def ods_to_scr(tables,sql_name,psql_name,sql_drop_name, white_list=None):
    """
    white_list: 如果有，就只處理這些表
    """
#     loc="/Volumes/F2020/opt/anaconda3/envs/py37/bin/csvsql"
    loc="/Users/eddie/opt/anaconda3/envs/waterswak/bin/csvsql" 
    sqls=[]
    sqls_drop=[]
    table_list=[]
    if 'table_def' in tables:
        def_df=tables['table_def']
        table_list = def_df[def_df['type']=='T']['table_name'].to_list()
        print("table_list = %s" %(table_list))
    for t_name in tables:
        if (not t_name=='table_def') and t_name in table_list:
            if white_list and not (t_name in white_list):
                print("table:%s PASS" %(t_name))
                pass
            else:
                df = tables[t_name]
                sqls_drop.append("DROP TABLE IF EXISTS %s;" %(t_name))

                filename = "output/%s.csv" %(t_name)
                if t_name=="s_waterwork_qty" or t_name=="s_waterin_qty":
                    df['date'] = df['date'].apply(str_tran)
                    df['date']= pd.to_datetime(df['date']).dt.date
                    df_to_db(t_name,df,'replace')
                elif t_name=='s_waterin_b': #use df_to_db list
                    df_to_db(t_name,df,'replace')
                    sql_acts=[]
                    sql_acts.append("SELECT AddGeometryColumn('s_waterin_b','geom','3826','POINT','2')")
                    sql_acts.append("UPDATE s_waterin_b SET geom = ST_PointFromText(wkt_geom)")
                    sql_acts.append("CREATE INDEX s_waterin_b_geom_idx ON public.s_waterin_b USING gist (geom)")
                    for sql in sql_acts:
                        sql_exec(conn,sql)
                elif t_name=='s_topology_transfer': #csvsql 會有 error, 應該是 json_def 欄位的影響
                    df_to_db(t_name,df,'replace')
                elif t_name=="s_topology_kind": #試著轉換到新的方式 
                    df_to_db(t_name,df,'replace')
                else: #using csvsql
                    df.to_csv(filename,index=False)
                    cmd_str="head -n 1000 output/%s.csv | %s -i postgresql --no-constraints --tables '%s' >> %s" %(t_name,loc,t_name,sql_name)
                    exec_print(cmd_str)
                    psql="\\copy \"%s\" FROM 'output/%s.csv' WITH (FORMAT csv,HEADER);" %(t_name,t_name)
                    sqls.append(psql)
    
    with open(psql_name, 'a') as f:
        for line in sqls:
            f.write("%s\n" %(line))

    with open(sql_drop_name, 'a') as f:
        for line in sqls_drop:
            f.write("%s\n" %(line))

sql_drop_name="output/tb_drop.sql"
sql_name = "output/tb.sql"
psql_name = "output/tb.psql"

#產生 sql, psql script
if 1:
    if os.path.exists(sql_drop_name):
        os.remove(sql_drop_name)
    if os.path.exists(sql_name):
        os.remove(sql_name)
    if os.path.exists(psql_name):
        os.remove(psql_name)
        
    # problem:'s_waterin_qty','s_waterwork_qty'
    #ods_to_scr(gd['sys'],sql_name,psql_name,sql_drop_name,['s_topology_transfer'],['s_village_waterin','s_waterin_quality'])
    ods_to_scr(gd['sys'],sql_name,psql_name,sql_drop_name,['s_topology_transfer','s_village_waterin','s_waterin_quality'])
#     ods_to_scr(gd['sys'],sql_name,psql_name,sql_drop_name)
    ods_to_scr(gd['basic'],sql_name,psql_name,sql_drop_name)
    ods_to_scr(gd['riverlog'],sql_name,psql_name,sql_drop_name)

#manual modify schema
# r_waterlevel_station,b_河川水位站
    
#drop, create table
if 0:
    conn=connect_db()
    with open (sql_drop_name, "r") as scrfile:
        sqls=scrfile.readlines()

    sql_str = "".join(sqls)
    print("sql_drop script:\n%s" % (sql_str))
    sql_exec(conn,sql_str)
    
    with open (sql_name, "r") as scrfile:
        sqls=scrfile.readlines()

    sql_str = "".join(sqls)
    print("sql script:\n%s" % (sql_str))
    sql_exec(conn,sql_str)

#import data
if 0:
#     psql="/Applications/Postgres.app/Contents/Versions/13/bin/psql"
    psql = "/Applications/Postgres.app/Contents/Versions/latest/bin/psql"
    with open (psql_name, "r") as sqlfile:
        sqls=sqlfile.readlines()
    psql_str = "".join(sqls)
    print("psql script:\n%s" %(psql_str))

    cmd_str="%s -h localhost -p 5432 -U postgres postgres -f %s -a" %(psql,psql_name)
    exec_print(cmd_str)<jupyter_output>table_list = ['s_syspar', 's_table_desc', 's_info_point', 's_info_line', 's_info_area', 's_timeseq_rpt', 's_repeat_job', 's_topology_kind', 's_topology_transfer', 's_topology_node', 's_topology_edge', 's_village_waterin', 's_waterin_qty', 's_waterwork_qty', 's_waterin_quality', 's_value_domain', 's_waterin_b']
table:s_syspar PASS
table:s_info_line PASS
table:s_info_area PASS
table:s_timeseq_rpt PASS
table:s_repeat_job PASS
table:s_topology_kind PASS
table:s_info_point PASS
PostgreSQL Table s_topology_transfer has been created successfully.
table:s_topology_node PASS
table:s_topology_edge PASS
executing : head -n 1000 output/s_village_waterin.csv | /Users/eddie/opt/anaconda3/envs/waterswak/bin/csvsql -i postgresql --no-constraints --tables 's_village_waterin' >> output/tb.sql
table:s_waterin_qty PASS
table:s_waterwork_qty PASS
executing : head -n 1000 output/s_waterin_quality.csv | /Users/eddie/opt/anaconda3/envs/waterswak/bin/csvsql -i postgresql --no-constraints --tables 's_waterin_qu[...]<jupyter_text>使用範例<jupyter_code>#gd['base']['拓墣-Node']
#gd['riverlog']["r_rain_station"]
if 0:
    df = gd['basic']['河川局']
    #query_str = """SELECT * FROM df_8410 where site_id like '%東區%'"""
    query_str = """SELECT * FROM df where rvb_name='第一河川局'"""
    q1_df= ps.sqldf(query_str, locals())
#q1_df

#新竹縣市各淨水場水質，13 個url 要下載
site_list=[2000,1998,1997,2008,2007,2006,2005,2004,2003,2002,2001,1999]
date_str=date.today()
for site in site_list:
    url="https://www.water.gov.tw/ch/WaterQuality/Detail/%i?nodeId=4631" %(site)
    filename="output/hsinchu_WaterQuality_%s_%i" % (date_str,site)
    url_get(filename, url,True)

print(date.today())<jupyter_output>2022-04-18<jupyter_text>DB 存取範例<jupyter_code>conn=connect_db()

if 0:
    sql = "select * from rivercode order by river_id"
    df = sql_to_df(conn,sql)

if 0:
    df = gd['basic']['河川局']
    df.to_csv('output/河川局.csv')    

if 1:
    sql="""
    CREATE TABLE "河川局" (
        a DECIMAL, 
        rvb_no DECIMAL, 
        rvb_name VARCHAR, 
        area BOOLEAN, 
        "水資源分區代號" BOOLEAN
    );

    """
    sql_exec(conn,sql)
if 0:
    sql = 'select * from 河川局'
    df = sql_to_df(conn,sql)
if 1:
    close_db(conn)
#df<jupyter_output>Connected to the PostgreSQL database...
relation "河川局" already exists

Database connection closed.<jupyter_text>shp 匯入資料庫用 data 目錄下的 shp 產生 script, 再手動到命令列執行，灌入資料庫<jupyter_code>#pathnames_ori = walk_dir("./data",".shp",['工業區污水處理廠分布_121.shp']) #
pathnames_ori = walk_dir("./data",".SHP") #
pathnames = list_remove(pathnames_ori,['_Sanhe'])


#print(pathnames)
shp_tosql(pathnames,"","data/shp.sql",3826,"Big5")
#shp_tosql(pathnames,"8818-工業區污水處理廠分布位置圖","data/shp.sql",102443,"UTF-8")
#shp_tosql(lines,,table_name, sql_path="data/shp.sql", srid=3826, encoding="UTF-8"):<jupyter_output>rm data/shp.sql
shp2pgsql -W Big5 -D -s 3826 -I "./data/新竹縣市人口數據/109年6月新竹縣統計區人口統計_最小統計區_SHP/109年6月新竹縣統計區人口統計_最小統計區.SHP" "109年6月新竹縣統計區人口統計_最小統計區">> data/shp.sql
shp2pgsql -W Big5 -D -s 3826 -I "./data/新竹縣市人口數據/109年6月新竹市統計區人口統計_最小統計區_SHP/109年6月新竹市統計區人口統計_最小統計區.SHP" "109年6月新竹市統計區人口統計_最小統計區">> data/shp.sql
shp2pgsql -W Big5 -D -s 3826 -I "./data/新竹縣市人口數據/109年6月新竹縣統計區人口指標_最小統計區_SHP/109年6月新竹縣統計區人口指標_最小統計區.SHP" "109年6月新竹縣統計區人口指標_最小統計區">> data/shp.sql
shp2pgsql -W Big5 -D -s 3826 -I "./data/新竹縣市人口數據/109年6月新竹市統計區人口指標_最小統計區_SHP/109年6月新竹市統計區人口指標_最小統計區.SHP" "109年6月新竹市統計區人口指標_最小統計區">> data/shp.sql
psql -p 5431 -U postgres -d postgis -f data/shp.sql<jupyter_text>讀取單月淨水場水質資料<jupyter_code>import pandas as pd
filename="/Volumes/F2020/MakerBk2/QGIS/projects/hackathon/自來水水質資訊_202105.csv"

mv_str="。"
df_csv = pd.read_csv(filename,header=[0,1,2,3])

if True:
    values = []
    for index, row in df_csv.iterrows():
        line = str(row['淨水場資訊'][0])
        cols = line.split("(")
        print(cols[0].strip())<jupyter_output><empty_output><jupyter_text>單月淨水場水質 CSV 分析<jupyter_code>#列出頭前溪流域淨水場的 總溶解固體量(Total Dissolved Solids)
import pandas as pd
filename="/Volumes/F2020/MakerBk2/QGIS/projects/hackathon/自來水水質資訊_202105.csv"
#targets=["第一淨水場","第二淨水場","東興淨水場","寶山淨水廠","員崠淨水場","梅花淨水場","尖石淨水場","內灣淨水場","桃山淨水場","芎林淨水場"]
targets=['新竹第一淨水場','新竹第二淨水場','東興淨水場','寶山淨水場','員崠淨水場','梅花淨水場','尖石淨水場','內灣淨水場','桃山淨水場','芎林淨水場']
df_csv = pd.read_csv(filename,header=[0,1,2,3])
item_name='總溶解固體量(Total Dissolved Solids)'

if True:
    idvalue = {}
    for col_info in df_csv.columns:
        if col_info[0]==item_name:
            #print(col_info)
            info_str="%s-單位:%s, 飲用水水質標準:%s" %(col_info[0],col_info[3],col_info[2])
            print(info_str)
            break
    for index, row in df_csv.iterrows():
        factory=row['淨水場名稱'][0]
        if factory in targets:
            value = row[item_name][0]            
            #print("%s:%s" %(factory,value))
            idvalue[factory]=value
    
    items = sorted(idvalue.items(), key=lambda x:x[1])
    for item in items:
        print("%s:%s" %(item[0],item[1]))




# 算感測值跟標準的比例
df_result['test'] = pd.to_numeric(df_result['飲用水水質標準'])
df_result
if 0:
    f1=df_result['飲用水水質標準'].str.isnumeric()
    df3=df_result[f1]
    df4=df3['percent']=df3['值']/df3['飲用水水質標準']*100
    df4
#df_out=df_result['percent']=df_result['值']/df_result['飲用水水質標準']*100
#df_out.to_csv('output/water_quality_result.csv')<jupyter_output><empty_output><jupyter_text>淨水場水質資料灌入資料庫<jupyter_code>#
def lines_to_file(lines,filename):
    fp = open(filename, "w")
    fp.write("\n".join(lines))
    fp.close()
 
def update_waterwork_quantity(src_pairs,sql_filename):
    """ 將列的幾個淨水場水質報告檔案，產生灌入資料庫的 sql
    每個月的第一筆 sql 是 delete 同資料日期的資料，如果改同月資料日期，記得手動刪除
    """
    import pandas as pd

    idx_colname=['區處別','系統代號','淨水場名稱','淨水場資訊']
    idxs={}
    values={}
    sqls=[]

    for [filename, date_str] in src_pairs:
        sql="DELETE FROM m_waterwork_quality WHERE \"日期\" = '%s';" %(date_str)
        sqls.append(sql)
        idxs['日期']=date_str
        df_csv = pd.read_csv(filename,header=[0,1,2,3])
        for index, row in df_csv.iterrows():
            cols = row.keys() #('區處別', 'Unnamed: 0_level_1', 'Unnamed: 0_level_2', 'Unnamed: 0_level_3')

            for col in cols:
                #print("%s=%s" %(col[0],row[col]))

                if col[0] in idx_colname:
                    idxs[col[0]]=row[col]
                else:
                    values['項目']=col[0]
                    if col[0]=='水質合格否(Y/N)':
                        values['值']=row[col]
                        values['項次']=0
                        values['飲用水水質標準']=""
                        values['單位']=""
                    else:
                        values['值']=row[col]
                        values['項次']=col[1]
                        values['飲用水水質標準']=col[2]
                        values['單位']=col[3]
                    #print("idxs=%s\nvalues=%s" %(idxs,values))


                    values_str = "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'" %(idxs['日期'], idxs['區處別'], idxs['系統代號'],idxs['淨水場名稱'],values['項目'],values['值'],values['單位'],values['飲用水水質標準'],values['項次'],idxs['淨水場資訊'])

                    sql="""INSERT INTO m_waterwork_quality("日期", "區處別","系統代號","淨水場名稱","項目","值","單位","飲用水水質標準","項次","淨水場資訊") VALUES (%s);""" %(values_str)
                    #print(sql)
                    sqls.append(sql)
    lines_to_file(sqls,sql_filename)
    return sqls
src_pairs=[["data/自來水水質資訊_202104.csv",'2021-04-08'], \
           ["data/自來水水質資訊_202105.csv",'2021-05-24']]
sql_filename="data/waterwork_quality.sql"
sqls=update_waterwork_quantity(src_pairs,sql_filename)
print("%s saved" %(sql_filename))

if 1: # update DB
    i=0
    for sql in sqls:
        if i % 100 ==0:
            print("%i sqls processed!" %(i))
        sql_exec(conn,sql)
        i=i+1
    print("total %i sqls processed!" %(i))<jupyter_output><empty_output><jupyter_text>環保署 APIhttps://data.epa.gov.tw/api/v1以下 API 測試過/stat_p_123 重要河川水質概況/wqx_p_01 河川水質監測資料/dws_p_28 每月自來水水質監測資料/wqx_p_12 河川水質季監測資料/ems_s_01 環境保護許可管理系統(暨解除列管)對象基本資料/stat_p_44 垃圾處理場(廠)統計/stat_p_45 垃圾清理量資料/stat_p_87 垃圾處理場(廠)座數/ems_s_03 水污染源許可及申報資料 https://data.epa.gov.tw/dataset/ems_s_03<jupyter_code># 這裡可以將 API 資料下載下來，轉換成 df
api_key="yourkey" #yourkey 請更新成自己環保署API key
limit=1000
limit_break=0 #>0 can limit total
offset=0
load_cnt=0
renew_url=True
    
if 0: #/stat_p_123 重要河川水質概況 -> e_river_state_q
    url_t = "https://data.epa.gov.tw/api/v1/stat_p_123?offset=%i&limit=%i&api_key=%s"
    filename_t= "output/stat_p_123-%i.json"
    use_label=True
    pass
if 0: # /wqx_p_01 河川水質監測資料 -> e_river_q
    url_t = "https://data.epa.gov.tw/api/v1/wqx_p_01?offset=%i&limit=%i&api_key=%s"
    filename_t= "output/wqx_p_01-%i.json"
    use_label=False
if 0: #/dws_p_28 每月自來水水質監測資料 -> e_waterwork_q
    url_t = "https://data.epa.gov.tw/api/v1/dws_p_28?offset=%i&limit=%i&api_key=%s"
    filename_t= "output/dws_p_28-%i.json"
    use_label=False
if 0: #/wqx_p_12 河川水質季監測資料 #"total": 117041 -> e_river_season_q
    url_t = "https://data.epa.gov.tw/api/v1/wqx_p_12?offset=%i&limit=%i&api_key=%s"
    filename_t= "output/wqx_p_12-%i.json"
    use_label=False
if 0: #/ems_s_01 環境保護許可管理系統(暨解除列管)對象基本資料 = 118447-環境保護許可管理系統(暨解除列管)對象基本資料     
    #"total": 322225 -> e_factory_base
    url_t = "https://data.epa.gov.tw/api/v1/ems_s_01?offset=%i&limit=%i&api_key=%s"
    filename_t= "output/ems_s_01-%i.json"
    use_label=False
if 0: #/stat_p_44 垃圾處理場(廠)統計 "total": 220  Garbage disposal site, 不進 DB
    url_t="https://data.epa.gov.tw/api/v1/stat_p_44?api_key=%s"
    url= url_t % (api_key)
    renew_url=False
    filename_t= "output/stat_p_44-%i.json"
    use_label=False
if 0: #/stat_p_45 垃圾清理量資料 "total": 902 -> e_trash_stat_qty
    url_t = "https://data.epa.gov.tw/api/v1/stat_p_45?offset=%i&limit=%i&api_key=%s"
    filename_t= "output/stat_p_45-%i.json"
    use_label=False     
if 1: # /stat_p_46 垃圾清理回收率指標資料 "total": 2194 -> e_trash_recycle
    url_t = "https://data.epa.gov.tw/api/v1/stat_p_46?offset=%i&limit=%i&api_key=%s"
    filename_t= "output/stat_p_46-%i.json"
    use_label=False 
if 0: #/stat_p_87 垃圾處理場(廠)座數 "total": 575 -> e_garbage_disposal
    url_t = "https://data.epa.gov.tw/api/v1/stat_p_87?offset=%i&limit=%i&api_key=%s"
    filename_t= "output/stat_p_87-%i.json"
    use_label=False 
if 1: #/ems_s_03 水污染源許可及申報資料
    url_t= "https://data.epa.gov.tw/api/v1/ems_s_03?offset=%i&limit=%i&api_key=%s"
    filename_t= "output/ems_s_03-%i.json"
    use_label=False 
    
    
while True:
    if renew_url==True:
        url= url_t % (offset,limit,api_key)
    filename  = filename_t %(offset)
    print("url=%s" %(url))
    url_get(filename, url,False)
    data = load_json(filename)

    if offset==0:
        labels={}
        d={}
        #print("labels:")
        for i in range(len(data['fields'])):
            #print("%s=%s" %(data['fields'][i]['id'],data['fields'][i]['info']['label']))
            labels[data['fields'][i]['id']]=data['fields'][i]['info']['label']
        print("labels=%s" %(labels))
        print("total=%i" %(data['total']))

    #print("values:")
    records_cnt = len(data['records'])
    for i in range(records_cnt):
        record_cur = data['records'][i]
        #print("%i: %s " %(i, record_cur))
        for key in record_cur.keys():
            if use_label:
                if not labels[key] in d.keys():
                    #print("key=%s" %(key))
                    d[labels[key]]=[]
                d[labels[key]].append(record_cur[key])
            else:
                if not key in d.keys():
                    #print("key=%s" %(key))
                    d[key]=[]
                d[key].append(record_cur[key])
        load_cnt += 1
    #load_cnt += records_cnt
    print("load_cnt=%i" %(load_cnt))
    if load_cnt >= data['total'] or (limit_break>0 and load_cnt >= limit_break):
        break
    offset += limit

df = pd.DataFrame.from_dict(d)
#df_to_db('table_name',df,'replace')
df
df_to_db('e_waterp_record',df,'replace')<jupyter_output>PostgreSQL Table e_waterp_record has been created successfully.<jupyter_text>氣象局 API/v1/rest/datastore/C-B0027-001 月平均-局屬地面測站資料<jupyter_code># 這裡可以將 API 資料下載下來，轉換成 df
api_key="yourkey" #請更新成自己氣象局API key
limit=10
limit_break=0 #>0 can limit total
offset=0
load_cnt=0
renew_url=True
    
if 0: #/v1/rest/datastore/C-B0027-001 月平均-局屬地面測站資料, 新竹站
    stationId=467571
    url_t = "https://opendata.cwb.gov.tw/api/v1/rest/datastore/C-B0027-001?Authorization=%s&limit=%i&offset=%i&format=JSON&stationId=%i&weatherElement=stationPressure,temperature,relativeHumidity,cloudCover,sunshineDuration,precipitation,windSpeed&dataMonth="
    filename_t= "output/C-B0027-001-%i.json"
    url= url_t % (api_key,limit,offset,stationId)
    renew_url=False

if 1: #/v1/rest/datastore/C-B0027-001 月平均-局屬地面測站資料, 全測站
    stationId=467571
    url_t = "https://opendata.cwb.gov.tw/api/v1/rest/datastore/C-B0027-001?Authorization=%s&format=JSON&weatherElement=stationPressure,temperature,relativeHumidity,cloudCover,sunshineDuration,precipitation,windSpeed&dataMonth="
    filename_t= "output/C-B0027-001-%i.json"
    url= url_t % (api_key)
    renew_url=False
    
#目前不支援 limit，多筆的用法，因為這個 API 氣象局不限制比數   
d={}
while True:
    if renew_url==True:
        url= url_t % (api_key,limit,offset)
    filename  = filename_t %(offset)
    print("url=%s" %(url))
    url_get(filename, url,True)
    data = load_json(filename)
    
    if offset==0:
        labels={}
        #print("labels:")
        ids=data['result']['fields']
        for i in range(len(ids)):
            labels[ids[i]['id']]=ids[i]['id']
        print("ids=%s" %(labels))
    
    location = data['records']['data']['surfaceObs']['location']
    for i in range(len(location)):
    
        station = location[i]['station']

        temperature = location[i]['stationObsStatistics']['temperature']
        monthly = temperature['monthly']

        head={}
        tail={}
        for month in monthly:
            #print(month)
            #head
            for key in station.keys():
                head[key]=station[key]
                if not key in d:
                    d[key]=[]
                d[key].append(station[key])
            #tail
            for key in month.keys():
                tail[key]=month[key]
                if not key in d:
                    d[key]=[]

                d[key].append(month[key])
            #print("tail=%s" %(tail))
        load_cnt += 1

        #load_cnt += records_cnt
    print("load_cnt=%i" %(load_cnt))
    if (limit_break>0 and load_cnt >= limit_break):
        break
    offset += limit
    
    break


df = pd.DataFrame.from_dict(d)
df

#debug
if 0:
    location = data['records']['data']['surfaceObs']['location']
    location
    len(location)
if 0:
    #ids=data['result']['fields']
    location = data['records']['data']['surfaceObs']['location']
    station = location[0]['station']
    #station['stationID']

    temperature = location[0]['stationObsStatistics']['temperature']
    monthly = temperature['monthly']

    for month in monthly:
        print(month)<jupyter_output><empty_output><jupyter_text>EPA CWMS 水量水質自動監測連線處理<jupyter_code>filename="/Users/wuulong/Downloads/cwms.xml"
if 0: #新竹縣，正常
    url="HTTP://hsinchuauto.tk/cwmsopendata/cwms.xml"
    url_get(filename,url,reload=True)
    df=xml_to_df(filename)
    df_to_db('e_cwms_hsinchu',df)
if 0: # 新竹市，網站的 xml 檔案無法存取，無法繼續
    url="HTTP://cwms.hccepb.gov.tw/cwmsopendata/cwms.xml"
    url_get(filename,url,reload=True)
    df=xml_to_df(filename)
    df_to_db('e_cwms_hsinchucity',df)<jupyter_output>PostgreSQL Table e_cwms_hsinchu has been created successfully.<jupyter_text>Data Transfer Kit<jupyter_code>import os,fnmatch
def find(pattern, path):
    result = []
    for root, dirs, files in os.walk(path):
        files.sort()
        for name in files:
            if fnmatch.fnmatch(name, pattern):
                result.append(os.path.join(root, name))
    return result

if 0:
    #filename = "data/6932-自來水用水量.csv"
    filename="data/95103-工業局所轄污水處理廠基本資料.csv"
    t_name=filename.split("/")[1][:-4]
    #print(t_name)
    df_csv = pd.read_csv(filename)
    
    df_to_db(t_name,df_csv,'replace')

if 0:
    filename="data/22371-工業區污水處理廠放流水水質資訊.csv"
    t_name="22371-工業區污水處理廠放流水水質資訊"
    df = pd.read_csv(filename)
    df_csv = df.rename(columns={'導電度( μscm)': '導電度','水溫(℃)': '水溫'}) #似乎有括號會有 error
    df_to_db(t_name,df_csv,'replace')
    
if 1: #情境轉換
    f = open("/tmp/lines.txt")
    lines = f.readlines()
    f.close()

    for line in lines:
        line=line.strip()
        cols=line.split()
        color="black"
        if cols[3]=="1": #展示
            color="orange"
        elif cols[3]=="2":#沒資料
            color="red"
        elif cols[3]=="3": #待整合
            color="blue" 
        elif cols[3]=="4": #發想中
            color="green" 
        if color!="":
            print("\"%s\"->\"%s\"[label=\"%s\",color=\"%s\"]" %(cols[0],cols[2],cols[1],color))
        else:    
            print("\"%s\"->\"%s\"[label=\"%s\"]" %(cols[0],cols[2],cols[1]))
        #print("SELECT UpdateGeometrySRID(''%s','geom',3826);" %(line))
        #print("select * from geometry_columns where f_table_name='%s';\n" %(line.lower()))

if 0: #環境指標標籤設計
    f = open("/tmp/lines1.txt")
    lines = f.readlines()
    f.close()
    links={}
    for line in lines:
        line=line.strip()
        cols=line.split()
        link = "%s-%s" %(cols[0],cols[1])
        if not link in links:
            links[link]=1
            print("\"%s\"->\"%s\"" %(cols[0],cols[1]))

        link = "%s-%s" %(cols[1],cols[2])
        if not link in links:
            links[link]=1
            print("\"%s\"->\"%s\"" %(cols[1],cols[2]))
        
        #print("SELECT UpdateGeometrySRID(''%s','geom',3826);" %(line))
        #print("select * from geometry_columns where f_table_name='%s';\n" %(line.lower()))

if 0:
    f = open("/tmp/a1.txt")
    a1 = f.readlines()
    f.close()

    f = open("/tmp/a3.txt")
    a2 = f.readlines()
    f.close()

    a2_new=[]
    for line in a2:
        a2_new.append(line.strip())
        #print(line.strip())

    for line in a1:
        if not line=='':
            line_new = "%s工業區污水處理廠" %(line.strip())
            if not line_new in a2_new:
                print("%s missing" %(line_new))
            else:
                print(line_new)
if 0:
    f = open("/tmp/a.txt")
    lines = f.readlines()
    f.close()
    lines_new= []
    for line in lines:
        lines_new.append(line.strip())
    lines_txt=",".join(lines_new)
    print(lines_txt)
if 0:
    f = open("/tmp/a.txt")
    lines = f.readlines()
    f.close()
    lines_new= []
    for line in lines:
        line=line.replace(",","")
        line=line.replace("\"","").strip()
        if line:
            lines_new.append(line)

if 0:
    pd.set_option('display.max_rows',None)
    filenames = find('*.ods', 'output/sew')
    print(filenames)
    df_all= None
    for filename in filenames:
        sews = pd.read_excel(filename, engine="odf",sheet_name=None,skiprows=3)
        print("%s sheets:\n %s " % (filename,sews.keys()))

        df=sews['用戶接管普及率及污水處理率統計一覽表']
        df=df.rename(columns={"Unnamed: 0": "縣市"})
        #fmt="%Y%m%dT%H%M%S+8"

        basename = os.path.basename(filename)
        df['basename']=basename[:-4]
        print(df)
        if df_all is None:
            df_all = df
        else:
            df_all = pd.concat([df_all, df],axis=0)

#df_all<jupyter_output>"工業污水處理廠"->"工業區域"[label="處理範圍",color="black"]
"工業污水處理廠"->"流路"[label="污染範圍為何",color="green"]
"工業污水處理廠"->"工業污水處理廠"[label="歷史水質",color="green"]
"工業區域"->"工業污水處理廠"[label="哪個污水廠",color="black"]
"工業區域"->"工廠"[label="有哪些工廠",color="black"]
"工業區域"->"取水口"[label="水源在哪",color="green"]
"工廠"->"工業污水處理廠"[label="哪個污水廠",color="black"]
"工廠"->"工業區域"[label="屬於哪個工業區",color="black"]
"工廠"->"工廠"[label="目前排放狀況",color="green"]
"工廠"->"流路"[label="污染範圍為何",color="green"]
"生活污水處理廠"->"流路"[label="污染範圍為何",color="green"]
"生活污水處理廠"->"生活區域"[label="處理哪些區域",color="green"]
"生活污染源"->"流路"[label="污染範圍為何",color="green"]
"生活污染源"->"生活污染源"[label="哪裡有垃圾",color="green"]
"生活污染源"->"生活污染源"[label="哪裡有垃圾場",color="green"]
"生活區域"->"鄰近污染源"[label="有哪些污染源",color="black"]
"生活區域"->"生活區域"[label="用水統計(三級經濟區)",color="black"]
"生活區域"->"取水口"[label="水源在哪",color="orange"]
"生活區域"->"淨水場"[label="淨水廠為何",color="orange"]
"生活區域"->"生活區域"[label="接管率統計",color="green"]
"污染點"->"流路"[label="污染範圍為何",color="green"]
"污染點"->"取水口"[label="會影響哪個取水口",color="green"]
"取水口"->"集水區"[...]<jupyter_text>UTC 時間格式標準化<jupyter_code>from datetime import datetime


now_t = datetime.now()

#date_time = today.strftime("%m/%d/%Y, %H:%M:%S")
fmt="%Y%m%dT%H%M%S+8"
now_str = now_t.strftime(fmt)
now_t2 = datetime.strptime(now_str, fmt)
print(now_str)
#"2018-03-12T10:12:45Z" -> "%Y-%m-%dT%H:%M:%SZ"
now_t2

t_test="110.02.01"
import re
from datetime import datetime,date

def datetime_to_utc(src_str,fmt_id):
    """
    fmt_id 小寫表示回傳字串，大寫表示回傳物件 D 表示回傳 date 物件，T 表示回傳 datetime 物件
    d/D: 可支援 "110.02.01" 中間任何分格符號都可以，也可是民國
    t/T: "2021/3/5 20:10:30","20210911T060226+8", "20210911T060226"
    a/A: 自動判別日期還是時間, 有 T 或是 中間有空白就是時間
    
    回傳
    d:"110-02-01"
    t:"20210911T060226+8"
    """
    fmt="%Y%m%dT%H%M%S+8"
    
    if fmt_id.lower()=="a":
        if src_str.find("T") >= 0 or src_str.find(" ")>=0:
            if fmt_id=="a":
                fmt_id="t"
            else:
                fmt_id="T"
        else:
            if fmt_id=="a":
                fmt_id="d"
            else:
                fmt_id="D"
    
    if fmt_id.lower()=="d":
        regex = r"(\d*)\D(\d*)\D(\d*)"
        #test_str = "1955/02/01"
        matches = re.finditer(regex, src_str, re.MULTILINE)
        for m in matches:
            year=m.group(1)
            if len(year)<4:
                year = 1911 + int(year)
            else:
                year = int(year)

            new_str="%04i-%02i-%02i" %(year,int(m.group(2)),int(m.group(3)))
        if fmt_id=="D":
            return(date.fromisoformat(new_str))
    if fmt_id.lower()=="t":
        if src_str.find("T") >= 0:
            sub_type="T"
            if src_str.find("+") >= 0:
                regex = r"(\d{4})(\d{2})(\d{2})T(\d{2})(\d{2})(\d{2})\+(\d{1})" #20210911T060226+8
            else:
                regex = r"(\d{4})(\d{2})(\d{2})T(\d{2})(\d{2})(\d{2})" #20210911T060226
        else:
            sub_type="S"
            regex = r"(\d*)\D(\d*)\D(\d*).(\d*)\D(\d*)\D(\d*)" #2021/3/5 20:10:30
        
        
        matches = re.finditer(regex, src_str, re.MULTILINE)
        for m in matches:
            v=[]
            for i in range(1,len(m.groups())+1):
                #print(m.group(i))
                v.append(int(m.group(i)))
            if sub_type=="S":
                new_str="%04i%02i%02iT%02i%02i%02i+8" %(v[0],v[1],v[2],v[3],v[4],v[5])
            if sub_type=="T":
                if len(v)>=7:
                    new_str="%04i%02i%02iT%02i%02i%02i+%i" %(v[0],v[1],v[2],v[3],v[4],v[5],v[6])
                else:
                    new_str="%04i%02i%02iT%02i%02i%02i+8" %(v[0],v[1],v[2],v[3],v[4],v[5])
        if fmt_id=="T":
            return(datetime.strptime(new_str, fmt))

    return new_str 
print(datetime_to_utc("2021/3/5 20:10:30","a"))<jupyter_output>20210305T201030+8<jupyter_text>資料比較<jupyter_code># 兩表單的兩欄位值比較異同

def file_to_lines(pathname):
    fo = open(pathname, "r")
    #lines = fo.readlines()
    lines = fo.read().splitlines()
    fo.close()
    return lines

def set_diff(filename1,filename2):
    #lines1=file_to_lines("/Users/wuulong/Downloads/river_id-rivercode.csv")
    #lines2=file_to_lines("/Users/wuulong/Downloads/river_code-riverpoly.csv")
    lines1=file_to_lines(filename1)
    lines2=file_to_lines(filename2)

    s1 = set(lines1)
    s2 = set(lines2)
    print(len(s2.difference(s1)))
    #print(s1.difference(s2))<jupyter_output><empty_output><jupyter_text>Debug<jupyter_code>def str_tran(line):
    import re
    g = re.match( r'(\d*)年(\d*)月(\d*)日', line, re.M|re.I)

    if g:
        year = int(g.group(1))+1911
        date_str = "%i-%s-%s" %(year,g.group(2),g.group(3))
        #print(date_str)
        line=date_str
    return line

df=pd.read_csv("~/Downloads/s_waterwork_qty.csv")

df['date'] = df['date'].apply(str_tran)
df['date']= pd.to_datetime(df['date']).dt.date


df_to_db('t_test',df,'replace')
filename = "/Users/wuulong/Downloads/TaipeiLight_data.xml"
df=xml_to_df(filename)
filename_out="/Users/wuulong/Downloads/TaipeiLight_data.csv"
df.to_csv(filename_out,index=False)
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17463385865469677, 'fraction_numerical': 0.03092952393434029, 'mean_word_length': 3.1639836289222374, 'pattern_counts': {'":': 23, '<': 53, '<?xml version=': 0, '>': 112, 'https://': 15, 'lorem ipsum': 0, 'www.': 1, 'xml': 8}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '531313', 'license': 'MIT', 'n_tokens_mistral': 14338, 'n_tokens_neox': 14037, 'n_words': 1782, 'path': 'maintain.ipynb', 'repo_name': 'senkukogetsu/waterswak', 'size': '30521'}