Skip to content

Instantly share code, notes, and snippets.

@dj1711572002

dj1711572002/FIS_ECSMFP_rev026_speed_slack.py Secret

Created December 23, 2024 14:54
Show Gist options
  • Save dj1711572002/1cbcf9693fbc23691fd188b00de8d291 to your computer and use it in GitHub Desktop.
Save dj1711572002/1cbcf9693fbc23691fd188b00de8d291 to your computer and use it in GitHub Desktop.
Download ZIP
FIS Points Estimation System rev026
Raw
FIS_ECSMFP_rev026_speed_slack.py
import pandas as pd
import tkinter
import tkinter.filedialog as FileDialog
import os
from os.path import dirname, basename
from os.path import join
import glob
from datetime import datetime, date, timedelta
import re
import sys
import numpy as np
import itertools
#import seaborn as sns
#import matplotlib.pyplot as plt
from scipy import interpolate
from scipy.signal import find_peaks
from scipy import signal
from bs4 import BeautifulSoup # importする
import requests # type: ignore
from bs4 import BeautifulSoup
import re
import csv
import json
import requests
#from requests_html import HTMLSession
#import geograpy
import pyperclip
import pyautogui
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver.chrome import service
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException
import time
import datetime
from urllib.request import urlopen
import clipboard
import ssl
import pykakasi
import urllib.parse
import kanji_to_time as ktt
import pprint
from ftplib import FTP
import pyshorteners
ssl._create_default_https_context = ssl._create_unverified_context
#----------------------CONST---------------------------------
DRIVE="C:"
WEB_HOOK_SM="https://hooks.slack.com/services/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"#SM
TOKEN_SM="xoxb-7478069236528-7486675612595-y84op1CLVzs1mgs0kPAchvMr"#shinshu-makers
CHN_SM="C07DD4WHQD9"#shinshu-makers python-bot
WEB_HOOK_FP="https://hooks.slack.com/services/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"
TOKEN_FP= "xoxb-7462748487234-7491684617312-Z1KBgwoahUm8BFs38MdrLlco" # FPtokenを入れてください
CHN_FP="C07DLJZ5M7V"#FP web-service
#***********************FIS Terms********************************
# DNF,DSQ,DNS,DNQ2,NPS,DQO,DPD
def chckDN(df):
print("check DNF s")
def addFIS_id(df):
for i in range(0,len(df)):#IS加入
if df.loc[i,"Name"]=="JUNG Sunwoo":
df.loc[i,"FIS_id"]="320552"
if df.loc[i,"Name"]=="LEE Hyunho":
df.loc[i,"FIS_id"]="320531"
if df.loc[i,"Name"]=="HUR Dohyun":
df.loc[i,"FIS_id"]="320527"
if df.loc[i,"Name"]=="KIM Sangseo":
df.loc[i,"FIS_id"]="320550"
if df.loc[i,"Name"]=="原澤 虎太郎":
df.loc[i,"FIS_id"]="6301603"
return df
def missSpell(df):
print("Miss Spell Reparair 佐藤 歌莱=>佐藤 歌萊 KANG Yungseo=>KANG Youngseo LEE Jaejun=>LEE Jae-Jun")
print("KIM Joohyoun=>KIM Joo-hyoun JUNG Sunwoo=> PARK Junwoo=>PARK Jun Woo HUR Dohyun=>")
print("SAJ未加入FIS加入者 原澤 虎太郎 6301603")
for i in range(0,len(df)):#IS加入
if df.loc[i,"Name"]=="佐藤 歌莱":
df.loc[i,"Name"]="佐藤 歌萊"
if df.loc[i,"Name"]=="KANG Yungseo":
df.loc[i,"Name"]="KANG Youngseo"
if df.loc[i,"Name"]=="LEE Jaejun":
df.loc[i,"Name"]="LEE Jae-Jun"
if df.loc[i,"Name"]=="KIM Joohyoun":#MEN
df.loc[i,"Name"]="KIM Jun Hyeong"
if df.loc[i,"Name"]=="PARK Junwoo":
df.loc[i,"Name"]="PARK Jun Woo"
#RYU Geonhwa
if df.loc[i,"Name"]=="RYU Geonhwa":
df.loc[i,"Name"]="RYU Geon Hwa"
return df
def File_selreadFIS( ):
print("Select read file")
root = tkinter.Tk()
root.withdraw()
path=DRIVE+'\FIS'
iDir =path#
fTyp = [("データファイル", "*.csv;*.xlsx;*.xls"), ("すべてのファイル", "*.*")]
filename = FileDialog.askopenfilename(parent=root, initialdir=iDir, filetypes=fTyp)
df = pd.read_csv(filename, low_memory=True)
df=df.reset_index()
# filenameからbasenameをとりだしてファイル種類区別
print(">>>>>>READ FILE;",filename)
basename = os.path.basename(filename)
ReadFile=basename
return df,basename
def P4211():
print("4.2.1.1 BL LastSeazonTwoBest Point Average")
def update_query(url, key, new_val):#key scheme,netloc,path,params,query
pr = urllib.parse.urlparse(url)
d = urllib.parse.parse_qs(pr.query)
d[key] = new_val
return urllib.parse.urlunparse(pr._replace(query=urllib.parse.urlencode(d, doseq=True)))
def gettxt(url):
# 環境設定
driver = webdriver.Chrome()
# URL指定とデータ取得
driver.get(url)
driver.implicitly_wait(10)
#data = driver.page_source
action = ActionChains(driver)
action.key_down(Keys.CONTROL).send_keys('a').key_up(Keys.CONTROL).perform() # Ctrl + A
#time.sleep(10) # 一時停止を入れることで、操作の間に少し待機する
action.key_down(Keys.CONTROL).send_keys('c').key_up(Keys.CONTROL).perform() # Ctrl + C
# クリップボードからコピーした内容を取得
#text = pyperclip.paste()#bugある
#time.sleep(10)
text = clipboard.paste()
return text
def getid(url):
driver = webdriver.Chrome()
# URL指定とデータ取得
driver.get(url)
time.sleep(20)
driver.execute_script('window.scrollTo(0, document.body.scrollHeight);')
data = driver.page_source
action = ActionChains(driver)
time.sleep(5)
action.key_down(Keys.CONTROL).send_keys('a').key_up(Keys.CONTROL).perform() # Ctrl + A
#time.sleep(10) # 一時停止を入れることで、操作の間に少し待機する
action.key_down(Keys.CONTROL).send_keys('c').key_up(Keys.CONTROL).perform() # Ctrl + C
time.sleep(5)
action.key_down(Keys.CONTROL).send_keys('c').key_up(Keys.CONTROL).perform() # Ctrl + C
#time.sleep(10) # 一時停止を入れることで、操作の間に少し待機する
# クリップボードからコピーした内容を取得
#text = pyperclip.paste()#bugある
text = clipboard.paste()
#time.sleep(5)
list_rowstr=[]
tableflag=0
rowstr=text.split("\r\n")
for i in range(0,len(rowstr)):
if 'Birth' in rowstr[i] and tableflag==0:
tableflag=1
if tableflag==1:
rowstrc=rowstr[i].replace("\t",",")
rowsclm=rowstrc.split(",")
#print(i,rowsclm)
list_rowstr.append(rowsclm)
rowstrc=""
df=pd.DataFrame(list_rowstr,columns=["SAJ_id","FIS_id","加盟団体","氏名","チーム名","Birth","SAJ","FIS"])
#SAJ_id(競技者番号) FIS_id(競技者番号) 加盟団体 氏名 チーム名 Birth SAJ FIS
df = df.drop(0)
df=df.reset_index(drop=True)
df.astype('int',errors='ignore')
df.fillna(0)
#print("---------df------------")
#print(df)
dt_now = datetime.datetime.now()
tstmp=str(dt_now.year)+"-"+str(dt_now.month)+"-"+str(dt_now.day)+"-"+str(dt_now.hour)+"-"+str(dt_now.minute)
fname=DRIVE+'\FIS\SAJ_Biography/SAJ_Biography_List_'+tstmp+'_.csv'
#df.to_csv(r'E:\FIS\df_SAJ_FIS_List_'+tstmp+'_.csv', index=False,encoding="utf-8_sig")
df.to_csv(fname, index=False,encoding="utf-8_sig")
result=str(df.shape)+"SaveFile:"+DRIVE+'\FIS\df_SAJList_'+tstmp+'_.csv'
#print(result)
#ini file
f=open(DRIVE+'\FIS\set_idf.txt','w')#SAJ Biographyファイルの名前付きパスをset_idf.txtに記述しておく
f.write(fname)
f.close
return df,result
def getrowstr(text):#rowstr[]を戻す text=gettxt(url)
rowstr=text.split("\r\n")
#print("Text splitted=> rowstr len=",len(rowstr))
list_rowstr=[]
tableflag=0
#columns
#for i in range(0,len(rowstr)):
#print(i,rowstr[i])
return rowstr
def getTitle(url):
text=gettxt(url)
print("get title text=",text)
title=[]
rowstr=getrowstr(text)
for i in range(0,len(rowstr)):
#print(i,rowstr[i])
if len(rowstr[i])>12:
title.append(rowstr[i])
#print("RaceTitle=",title[0])
title.append(rowstr[i+1])
#print("race_date=",title[1])
break
return title
def getFIS_listPage():#FIS listPageをスクレーピングしてlistタイトル名,ファイル名、期間を取得してファイル保存FIS_FileSet_'+tstmp.csv
url="https://www.fis-ski.com/DB/alpine-skiing/fis-points-lists.html"
text=gettxt(url)
dt_now = datetime.datetime.now()
tstmp=str(dt_now.year)+"-"+str(dt_now.month)+"-"+str(dt_now.day)+"-"+str(dt_now.hour)+"-"+str(dt_now.minute)
f=open(DRIVE+'\FIS\FIS-points-list-AL\FIS_listPage_'+tstmp+'.txt','w',encoding="utf-8_sig")#SAJ Biographyファイルの名前付きパスをset_idf.txtに記述しておく
f.write(text)
f.close
#text splitしてファイル名と期間を抽出 df csv出力
fisptxt=getrowstr(text)
fiset=[]
n=0
for i in range(0,len(fisptxt)):
if " FIS points list" in fisptxt[i]:
title=fisptxt[i]
startdate=fisptxt[i+1]
enddate=fisptxt[i+2]
print(title)
titlearry=title.split(" ")
#for k in range(0,len(titlearry)):
#print(i,k,":",titlearry[k])
if(len(titlearry)>4):#
yeararry=titlearry[4].split("/")
#2024 counter cnt=n+390
#file name <No 391 as 2024 No22 season last 01-5-2024><No 370 as 2024 No1 season start 01-07-2023>
# n=0で最新ファイル No392 as 2025 No1 base listはカウントしてない
if (m := re.match(r'^([\d.]+)(\D+)$', titlearry[0])) and (len(titlearry)==5) and (len(yeararry[1])==2):
print(m)
print(i,m.start(),m.end(),m.span())
print("File No=",m.group(1))
fn=int(m.group(1))
#title(4)から年度を抽出
yeararry=titlearry[4].split("/")
if titlearry[4]!=yeararry[0] :
years=titlearry[4][-4:]
print("year=",years)
cnt=392-n
filename="FIS-points-list-AL-2024-"+str(cnt)+".csv"
print(i,"fiset=",title,filename,startdate,enddate)
fiset.append([title,filename,startdate,enddate])
n+=1
df_fiset=pd.DataFrame(fiset,columns=["Title","filename","start_date","last_date"])
df_fiset = df_fiset.drop(0)
df_fiset=df_fiset.reset_index(drop=True)
print(df_fiset)
fname=DRIVE+'\FIS\FIS-points-list-AL\FIS_FileSet_'+tstmp+'.csv'
df_fiset.to_csv(fname, index=False,encoding="utf-8_sig")
return df_fiset
def fitid(df_result,df_id,bn):
print("Fit RaceResult and FIS id")
#print("df_result",df_result)
#print("df_id",df_id)
list_id=[]
df_result["SAJ_id"]="0"
df_result["FIS_id"]="0"
for i in range(0,len(df_result)):#result　一位からid検索
#print()
target_Name=df_result.loc[i,"Name"]
#print(i,target_Name,target_Name.encode('unicode-escape'))
for j in range(0,len(df_id)-3): #for j in range(800,900):
#print(i,j,df_id.loc[j,"SAJ_id"],df_id.loc[j,"FIS_id"])
if target_Name in df_id.loc[j,'氏名']:
#print("Hit index=",j,target_Name,df_id.loc[j,'氏名'])
df_result.loc[i,"SAJ_id"]=df_id.loc[j,"SAJ_id"]
df_result.loc[i,"FIS_id"]=df_id.loc[j,"FIS_id"]
#print("result=",i,"id=",j,df_result.loc[i,"SAJ_id"], df_result.loc[i,"FIS_id"])
break
# elif df_id.loc[j,'氏名']=="佐藤 歌莱":
#print("ERROR:",j,df_id.loc[j,'氏名'],":",target_Name)
#v=input()
#fname='E:\FIS\id_'+bn
#print(fname)
#df_result.to_csv(fname, index=False,encoding="utf-8_sig")
return df_result
def period_71(d,m,y):
#print()
if m<7:
md=m+5
else:
md=m-7
dayd=md*30+d
return dayd
def totalsec(cmt):
if cmt.isnumeric():
tarry=cmt.split(":")
if len(tarry)==2:
if ("DN" in tarry[0]) or ("DN" in tarry[1]) or ("DS" in tarry[0] )or ("DS" in tarry[1]):
tsec=0
#print("DNF")
else:
mint=float(tarry[0])
sect=float(tarry[1])
tsec=mint*60+sect
#print("SUB(totalsec):",mint,sect,tsec)
elif tarry[0].isnumeric():
if ("DN" in tarry[0]) or ("DS" in tarry[0] ):
tsec=0
#print("DNF")
else:
#mint=float(tarry[0])
sect=float(tarry[0])
tsec=sect
#print("SUB(totalsec):",mint,sect,tsec)
else:
tsec=0
else:
tsec=0
return tsec
def run1sec(cmt):
#print("cmt=",cmt)
tsec=0
if "D" in cmt :
tsec=100000
#print("D")
elif ":" in cmt or "." in cmt:
carry=cmt.split("(")
sarry=carry[0]
#pprint.pprint(sarry)
if sarry.count(":") ==0:#ss.tt
#tarry=sarry[0].split(".")
sect=float(sarry)
#sect=float(tarry[1])
tsec=sect
#print("SUB(totalsec):",mint,sect,tsec)
elif sarry.count(":") ==1:#mm:ss.tt
tarry=sarry.split(":")
mint=float(tarry[0])
sect=float(tarry[1])
tsec=mint*60+sect
#print("SUB(totalsec):",mint,sect,tsec)
#pprint.pprint(tarry)
elif sarry.count(":") ==2:#hh:mm:ss.tt
tarry=sarry.split(":")
hint=float(tarry[0])
mint=float(tarry[1])
sect=float(tarry[2])
tsec=hint*3600+mint*60+sect
#pprint.pprint(tarry)
return tsec
def write_html(df, output):
scripts = """
<link href="https://cdn.datatables.net/v/dt/jq-3.6.0/dt-1.13.4/datatables.min.css" rel="stylesheet"/>
<script src="https://cdn.datatables.net/v/dt/jq-3.6.0/dt-1.13.4/datatables.min.js"></script>
<script>$(document).ready(function() {$('.my-table').DataTable({});})</script>
"""
html = df.to_html(classes='my-table')
html = scripts + html
with open(output, mode='w',encoding="utf-8_sig") as f:
f.write(html)
shumoku=""
poicol=""
poscol=""
poicol_B=""
poscol_B=""
def main():
#競技種類　グローバル宣言
v= input(" keyin a:auto Slack s:SEIKO Sports LINK j:SAJ data h:calFile Convert c:point cal =>ENTER")
if v=="h":#df to html file
print("CAL file to HTML Converting")
#df,bn=File_selreadFIS()#FIS dirからresultファイルを指定してdfで返す
print("Select read CAL_ file")
root = tkinter.Tk()
root.withdraw()
path=DRIVE+'\FIS'
iDir =path#
fTyp = [("データファイル", "*.csv;*.xlsx;*.xls"), ("すべてのファイル", "*.*")]
filename = FileDialog.askopenfilename(parent=root, initialdir=iDir, filetypes=fTyp)
df = pd.read_csv(filename, low_memory=True)
colname=df.columns.to_list()
colfisp=[s for s in colname if 'Before_FIS_' in s]
#colname=df.columns()#[df.columns.str.contains('Before_FIS_')]
print("colname=",colname,"colfisp=",colfisp)
basename = os.path.basename(filename)
print(df.shape)
df_html=df[["Rank","Bib","Name","Club","Run 1","Run 2","Total","Diff","ResultPoint",colfisp[0]]]
#print(df_html)
#write_html(df_html, r'E:\FIS\fuga.html')
bnh=basename.strip(".csv")
bnhh=bnh.strip("CAL_df_cal_points_df_")
hname=DRIVE+"\FIS\Test_"+bnhh+".html"
print(bnhh)
#df_html.to_html(hname)
#print()
html = df_html.to_html()
msg = bnhh #"custom mesagges"
title = """
<html>
<head>
<style>
thead {color: green;}
tbody {color: black;}
tfoot {color: red;}
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body>
<h4>
""" + msg + "</h4>"
end_html = """
</body>
</html>
"""
html = title + html + end_html
text_file = open(hname, "w",encoding="utf-8_sig")
text_file.write(html)
text_file.close()
#print()
if v=="c":#pointsファイルからPoint計算
print("CALCULATION:Select points_FILE in FIS dir")
df_cal,bn=File_selreadFIS()#FIS dirからpointsファイルを指定してdfで返す
df = pd.read_csv(filename, low_memory=True)
print("pointsFile read to df_cal:",df_cal.shape)
#0:種目を検索 "RaceTitel"か"Women-Men"のいずれかに単語があるか検索する
shumoku=""
if ("DH" in df_cal.loc[1,"Women-Men"]) or ("DH" in df_cal.loc[1,"RaceTitle"]):
shumoku="DH"
fv=1250
pmax=330
poicol="DHpoints"
poscol="DHpos"
poicol_B="DHpoints_B"
poscol_B="DHpos_B"
elif ("SL" in df_cal.loc[1,"Women-Men"])or ("SL" in df_cal.loc[1,"RaceTitle"]):
shumoku="SL"
fv=730
pmax=165
poicol="SLpoints"
poscol="SLpos"
poicol_B="SLpoints_B"
poscol_B="SLpos_B"
elif ("GS" in df_cal.loc[1,"Women-Men"] )or ("GS" in df_cal.loc[1,"RaceTitle"]):
shumoku="GS"
fv=1010
pmax=220
poicol="GSpoints"
poscol="GSpos"
poicol_B="GSpoints_B"
poscol_B="GSpos_B"
elif ("SG" in df_cal.loc[1,"Women-Men"]) or ("SG" in df_cal.loc[1,"RaceTitle"]):
shumoku="SG"
fv=1190
pmax=270
poicol="SGpoints"
poscol="SGpos"
poicol_B="SGpoints_B"
poscol_B="SGpos_B"
elif ("AC" in df_cal.loc[1,"Women-Men"]) or ("AC" in df_cal.loc[1,"RaceTitle"]):
shumoku="AC"
fv=1360
pmax=270
poicol="ACpoints"
poscol="ACpos"
poicol_B="ACpoints_B"
poscol_B="ACpos_B"
else:
print("EROR No race shumoku ")
print("This Race is ",shumoku)
#1:レースポイント計算-----------------------------------------------------------
T0to=totalsec(df_cal.loc[0,"Total"])#精度悪いのでRun 1 と　Run2の和からトータルを出す
trun0_1=run1sec(df_cal.loc[0,"Run 1"])
trun0_2=run1sec(df_cal.loc[0,"Run 2"])
T0=trun0_1+trun0_2
#Run1 Run2を足してTotalPを生成して計算する
for i in range(0,len(df_cal)):
Txto=totalsec(df_cal.loc[i,"Total"])
trun1=run1sec(df_cal.loc[i,"Run 1"])
trun2=run1sec(df_cal.loc[i,"Run 2"])
Tx=trun1+trun2
df_cal.loc[i,"Tx12"]=Tx
df_cal.loc[i,"RP"]=(Tx/T0-1)*fv
#df_cal.loc[i,"RPto"]=(Txto/T0to-1)*fv
print(i,"RP=",df_cal.loc[i,"RP"])
#print(df_cal.dtypes)
fname1=DRIVE+'\FIS\cal_RP_'+bn
print(fname1)
df_cal.to_csv(fname1, index=False,encoding="utf-8_sig")
#2:ペナルティポイント計算B（df_B) スタート前当該競技のFISポイントをソートして上位５人の和
df_B=df_cal.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_B[poicol] = pd.to_numeric(df_B[poicol], errors='coerce')
print("PREsort_df_B=",df_B)
df_B.sort_values(poicol,inplace=True)
df_B=df_B.reset_index(drop=True)
print("AfterSort_df_B=",df_B)
fname1=DRIVE+'\FIS\sorted_df_B_'+bn
print(fname1)
df_B.to_csv(fname1, index=False,encoding="utf-8_sig")
df_B[poicol] = pd.to_numeric(df_B[poicol], errors='coerce')
print("poicol type=",df_B[poicol].dtypes)
#SORTした最上部はDNFのゼロ点の人がいるので、ポイントゼロを省いて和算
n=0
rankn=1
poiBsum=0
while (rankn<=5):# or (n<len(df_B)-1):
print("df_B[",n,",",poicol,"]=",df_B.loc[n,poicol])
if df_B.loc[n,poicol]!=0 :
tdata=float(df_B.loc[n,poicol])
#print("tdata:",tdata,type(tdata))
poiBsum=poiBsum+tdata
df_B.loc[rankn,"rankB"]=rankn
df_B.loc[rankn,"Bname"]=df_B.loc[n,"Name"]
df_B.loc[rankn,"poiB"]=df_B.loc[n,poicol]
df_cal.loc[rankn,"rankB"]=rankn
df_cal.loc[rankn,"Bname"]=df_B.loc[n,"Name"]
df_cal.loc[rankn,"poiB"]=df_B.loc[n,poicol]
#print("n=",n,"rankn=",rankn,df_cal.loc[rankn,"rankB"],df_cal.loc[rankn,"Bname"],df_cal.loc[rankn,"poiB"])
rankn+=1
n+=1
df_B.loc[0,"Bname"]="poiBsum"
df_B.loc[0,"poiB"]=poiBsum
df_B.loc[0,"rankB"]=0
df_cal.loc[0,"Bname"]="Total_B="
df_cal.loc[0,"poiB"]=poiBsum
df_cal.loc[0,"rankB"]="df_B-Rank1-5"
#print(n,"point_B=",df_cal.loc[0,"point_B"])
# Calculation DEBUG
fname1=DRIVE+'\FIS\cal_df_B_'+bn
print(fname1)
df_B.to_csv(fname1, index=False,encoding="utf-8_sig")
# Calculation DEBUG
#fname2='E:\FIS\cal_df_cal_'+bn
#print(fname2)
#df_cal.to_csv(fname2, index=False,encoding="utf-8_sig")
#3:ペナルティポイント計算C（df_C）完走した時間"Total"をSORTして１０位以内のFISポイント上位５人の和
#df_C=df_cal.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
#10位以内が対象なので、１０位までをコピー
#df_C=df_cal[df_cal["Rank"]<11]
df_C=df_cal.iloc[0:10,:]
print("df_C=",df_C)
df_Cs=df_C.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_Cs.sort_values(poicol,inplace=True)#Raceのrankでソート
df_Cs=df_Cs.reset_index(drop=True)
print("df_Cs=",df_Cs)
n=0
rankn=1
poiCsum=0
while rankn<=5:
print("df_Cs[",n,",'RP']=",df_Cs.loc[n,"RP"])
df_Cs[poicol] = pd.to_numeric(df_Cs[poicol], errors='coerce')
if df_Cs.loc[n,poicol]!=0 :
poiCsum=poiCsum+df_Cs.loc[n,'RP']
df_Cs.loc[rankn,"rankC"]=rankn
df_Cs.loc[rankn,"Cname"]=df_Cs.loc[n,"Name"]
df_Cs.loc[rankn,"poiC"]=df_Cs.loc[n,'RP']
df_cal.loc[rankn,"rankC"]=rankn
df_cal.loc[rankn,"Cname"]=df_Cs.loc[n,"Name"]
df_cal.loc[rankn,"poiC"]=df_Cs.loc[n,'RP']
print("n=",n,"rankn=",rankn,df_cal.loc[rankn,"rankC"],df_cal.loc[rankn,"Cname"],df_cal.loc[rankn,"poiC"])
rankn+=1
n+=1
df_Cs.loc[0,"Cname"]="poiCsum"
df_Cs.loc[0,"poiC"]=poiCsum
df_Cs.loc[0,"rankC"]=0
df_Cs.loc["Bname"]=0
df_Cs.loc["poiB"]=0
df_Cs.loc["rankB"]=0
df_cal.loc[0,"Cname"]="Total_C="
df_cal.loc[0,"poiC"]=poiCsum
df_cal.loc[0,"rankC"]="df_Cs-Rank1-5"
print("Point_C:",df_cal.loc[0,"rankC"],df_cal.loc[0,"Cname"],df_cal.loc[0,"poiC"])
#max値で判定
if poiCsum>pmax:#max値より大きかったらmax値に置き換え
df_Cs.loc[0,"poiC"]=pmax
df_cal.loc[0,"poiC"]=pmax
fname1=DRIVE+'\FIS\cal_df_Cs_'+bn
print(fname1)
df_Cs.to_csv(fname1, index=False,encoding="utf-8_sig")
#4:ペナルティポイント計算A（df_A）
#Totalの10位以内の５人のFISポイント上位を合計する
df_A=df_cal.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
#df_A=df_cal[df_cal["Rank"]<11]
#print("df_A=",df_A)
#df_As=df_A.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_A["Totalsec"]=df_A["Total"].apply(run1sec)
df_A.sort_values("Totalsec",inplace=True)#RaceのTotalsecでソート
df_A=df_A.reset_index(drop=True)
# Calculation DEBUG
fname2=DRIVE+'\FIS\cal_df_A_'+bn
print(fname2)
df_A.to_csv(fname2, index=False,encoding="utf-8_sig")
df_As=df_A.iloc[0:10,:]#Run 1の１０位まで切り抜き
df_As.sort_values(poicol,inplace=True)#RaceのRun1でソートRun1の１０位中
df_As=df_As.reset_index(drop=True)
print("df_As=",df_As)
#fname1='E:\FIS\cal_df_As_'+bn
#print(fname1)
#df_As.to_csv(fname1, index=False,encoding="utf-8_sig")
poiAsum=0
rankn=1
df_As[poicol] = pd.to_numeric(df_As[poicol], errors='coerce')
for i in range(0,5):#5位まで切り取り
#if df_As.loc[i,poicol]!=0 :
poiAsum=poiAsum+df_As.loc[i,poicol]
df_As.loc[i+1,"rankA"]=rankn
df_As.loc[i+1,"Aname"]=df_As.loc[i,"Name"]
df_As.loc[i+1,"poiA"]=df_As.loc[i,poicol]
df_cal.loc[i+1,"rankA"]=rankn
df_cal.loc[i+1,"Aname"]=df_As.loc[i,"Name"]
df_cal.loc[i+1,"poiA"]=df_As.loc[i,poicol]
rankn+=1
df_As.loc[0,"rankA"]=0
df_As.loc[0,"Aname"]="poiAsum"
df_As.loc[0,"poiA"]=poiAsum
df_cal.loc[0,"rankA"]="df_As-Rank1-5"
df_cal.loc[0,"Aname"]="Total_A="
df_cal.loc[0,"poiA"]=poiAsum
# Calculation DEBUG
fname2=DRIVE+'\FIS\cal_df_As_'+bn
print(fname2)
df_As.to_csv(fname2, index=False,encoding="utf-8_sig")
# Calculation DEBUG
#fname3='E:\FIS\cal_df_cal_'+bn
#print(fname3)
#df_cal.to_csv(fname3, index=False,encoding="utf-8_sig")
#max値で判定
df_Cs.loc[0,"pmax"]=pmax
df_cal.loc[0,"pmax"]=pmax
if poiAsum>pmax:#max値より大きかったらmax値に置き換え
df_Cs.loc[0,"poiA"]=pmax
df_cal.loc[0,"poiA"]=pmax
#===================PENALTY POINT==========================================
penaltypsum=poiAsum+poiBsum-poiCsum
penaltyp10=penaltypsum/10
penaltyp=round(penaltyp10,2)
if penaltyp<23:
df_cal.loc[3,"PenalyPoint"]=penaltyp#calculated penalty point
print("Penalty minmum 23,Calculated penaltypoint=",penaltyp)
penaltyp=23# fis minimum rule 23 point
df_cal.loc[0,"PenalyPoint"]=penaltyp
print("point_A=",poiAsum)
print("point_B=",poiBsum)
print("point_C=",poiCsum)
print("penaltypsum=",penaltypsum)
print("PenaltyPoint=",penaltyp)
#===================Result FIS point==========================================
for i in range(0,len(df_cal)):
df_cal.loc[i,"ResultName"]=df_cal.loc[i,"Name"]
df_cal.loc[i,"ResultPoint"]=round((df_cal.loc[i,"RP"]+penaltyp),2)
becol="Before_FIS_"+poicol
df_cal.loc[i,becol]=df_cal.loc[i,poicol]
print(i,df_cal.loc[i,"Name"],"the Race FIS point=",df_cal.loc[i,"ResultPoint"])
# Calculation DEBUG
fname2=DRIVE+'\FIS\CALpoints_df_cal_'+bn
print(fname2)
df_cal.to_csv(fname2, index=False,encoding="utf-8_sig")
#print()
if v=="p":#getFIS_listPage()を読み込んでFIS_points-listファイル名一覧作成
df=getFIS_listPage()
if(v=="ff"):#遅いFITTTING
#レース結果表へIDとポイントデータで選手名にフィットさせて追記する
#次にレースの日時と適合したFIS-points-list-AL-xxx.csvファイルをさがして読み込んでフィット
print("Fitting")
print("Auto READ:set_idf.txt=>iDNo list File & FISPoint list FILEName ")
#f=open(r'E:\FIS\set_idf-ptf.txt','r')#レースresultとFISpointlistファイル名の2個のtxtを読む derimiter カンマ
f=open(DRIVE+'\FIS\set_idf-ptf-base24.txt','r')#レースresultとFIS-points-list,Base-Fis-pointsの3個のtxtを読む delimiter カンマ
idf=f.read()
f.close
print("idf=",idf)
idfarry=idf.split(",")
print("SAJ Biography:",idfarry[0])
print("FIS points Page:",idfarry[1])
print("Base FIS point list:",idfarry[2])
#Baseファイル読み込み
df_base=pd.read_csv( idfarry[2], low_memory=True)#SAJ Biography ファイル読み込み
print("Base Read to df_base:",idfarry[2],"df_base shape=",df_base.shape)
df_id = pd.read_csv( idfarry[0], low_memory=True)#SAJ Biography ファイル読み込み
df_id.fillna(0)
df_id.astype('int',errors='ignore')
#print("df_id")
#print(df_id)
df_fispage = pd.read_csv(idfarry[1], low_memory=True)
#print("dr_fispage=",df_fispage)
#print("===>>Created df_id=",df_id.shape,idf)
#SEIKO フォルダから生データ読み込み
df_result,bn=File_selreadFIS()#FIS dirからresultファイルを指定してdfで返す
df_result=missSpell(df_result)#FIScodeが見つからない人に手動で、コードを付与
print("===>>Created df_result=",df_result.shape,bn)
df_result=fitid(df_result,df_id,bn)
#print("ided df_result=",df_result)
fname=DRIVE+'\FIS\id_'+bn
#print(fname)
df_result.to_csv(fname, index=False,encoding="utf-8_sig")
#---------------------FIS list Fitting-----------------------------------
#df_resultの日付から適応fis-points-listをdf_fispageで索引検索して選定
rdate=df_result.loc[1,"RaceDate"]
datestr=rdate.split("年")
yeari=int(datestr[0])
datestr1=(datestr[1]).split("月")
monthi=int(datestr1[0])
datestr2=(datestr1[1]).split("日")
dayi=int(datestr2[0])
#print(yeari,"/",monthi,"/",dayi)
result_days=period_71(dayi,monthi,yeari)
#fis dateと比較
start_days=360
for i in range(1,len(df_fispage)):
fis_start=df_fispage.loc[i,"start_date"]
fis_sdate=fis_start.split("-")
fis_sday=int(fis_sdate[0])
fis_smonth=int(fis_sdate[1])
fis_syear=int(fis_sdate[2])
start_days_1=start_days
start_days=period_71(fis_sday,fis_smonth,fis_syear) #7/1を起点とした日付単位に変換
fis_last=df_fispage.loc[i,"last_date"]
fis_ldate=fis_last.split("-")
fis_lday=int(fis_ldate[0])
fis_lmonth=int(fis_ldate[1])
fis_lyear=int(fis_ldate[2])
last_days=period_71(fis_lday,fis_lmonth,fis_lyear) #7/1を起点とした日付単位に変換
#perios71同士で比較
if start_days<result_days and start_days_1>result_days:
#print("Hit result_days")
#print(df_fispage.loc[i,"start_date"],"-",rdate,"-",df_fispage.loc[i-1,"start_date"])
target_filename=df_fispage.loc[i,"filename"]
#print("Hit fis-points-list=",target_filename)
break
#target file 読み込み
path=DRIVE+'\FIS\FIS-points-list-AL/'
pfilename=path+target_filename
#print(pfilename)
df_points = pd.read_csv( pfilename, low_memory=True)#fis-points-listファイル読み込み
#print(df_points)
#resultでfiscodeが無い人を名前で検索して、fiscode超出
list_noname=[]
list_nonameidx=[]
for i in range(0,len(df_result)):
#print(i,":FIS_id=",df_result.loc[i,"FIS_id"])
if df_result.loc[i,"FIS_id"]=='0':
no_name=df_result.loc[i,"Name"]
#print(i,no_name)
list_noname.append(no_name)
list_nonameidx.append(i)
#print("no_name=",list_noname)
name_index=[]
fiscodei=[]
for i in range(0,len(list_noname)):#df_pointsからLasenameとFirstnameが一致する行を検出してdf_result修正--
names=list_noname[i].split(" ")
for j in range(0,len(df_points)):
if(df_points.loc[j,"Lastname"]==names[0] and df_points.loc[j,"Firstname"]==names[1]):
name_index.append(list_nonameidx[i])
fiscodei.append(df_points.loc[j,"Fiscode"])
#print("name_index=",name_index)
#print("fiscodei=",fiscodei)
for i in range(0,len(name_index)):
df_result.loc[name_index[i],"FIS_id"]=fiscodei[i]
#print("Result_Noname:",name_index[i],df_result.loc[name_index[i],"FIS_id"],df_result.loc[name_index[i],"Name"])
# index DEBUG
#fname1='E:\FIS\pointsN_'+bn
#print(fname1)
#df_result.to_csv(fname1, index=False,encoding="utf-8_sig")
#fis id検索　FITTING
#追加DHpoints DHpos DHSta SLpoints SLpos SLSta GSpoints GSpos GSSta SGpoints SGpos SGSta ACpoints ACpos ACSta
df_result["Caldate"]="0"
df_result["Fiscode"]="0"
df_result["Lastname"]="0"
df_result["Firstname"]="0"
df_result["Skiclub"]="0"
df_result["DHpoints"]="0"
df_result["DHpos"]="0"
df_result["DHSta"]="0"
df_result["SLpoints"]="0"
df_result["SLpos"]="0"
df_result["SLSta"]="0"
df_result["GSpoints"]="0"
df_result["GSpos"]="0"
df_result["GSSta"]="0"
df_result["SGpoints"]="0"
df_result["SGpos"]="0"
df_result["SGSta"]="0"
df_result["ACpoints"]="0"
df_result["ACpos"]="0"
df_result["ACSta"]="0"
df_result["plistname"]="0"
#Base columns
df_result["Caldate_B"]=0
df_result["DHpoints_B"]="0"
df_result["DHpos_B"]="0"
df_result["DHSta_B"]="0"
df_result["SLpoints_B"]="0"
df_result["SLpos_B"]="0"
df_result["SLSta_B"]="0"
df_result["GSpoints_B"]="0"
df_result["GSpos_B"]="0"
df_result["GSSta_B"]="0"
df_result["SGpoints_B"]="0"
df_result["SGpos_B"]="0"
df_result["SGSta_B"]="0"
df_result["ACpoints_B"]="0"
df_result["ACpos_B"]="0"
df_result["ACSta_B"]="0"
#df_result["plistname_B"]="0"
fiscoderow=[]
for i in range(0,len(df_result)):
target_id=df_result.loc[i,"FIS_id"]
#print("PRE:",i,target_id,df_result.loc[i,"Name"])
if target_id!='0':
#------------レース日に合わせたFIS-Points-listをコピー---------------------------
for j in range(0,len(df_points)):
if target_id==df_points.loc[j,"Fiscode"]:
print("FIS-point:",i,j,target_id,df_points.loc[j,"Fiscode"],df_result.loc[i,"Name"])
df_result.loc[i,"Caldate"]=df_points.loc[j,"Calculationdate"]
df_result.loc[i,"Fiscode"]=df_points.loc[j,"Fiscode"]
df_result.loc[i,"Lastname"]=df_points.loc[j,"Lastname"]
df_result.loc[i,"Firstname"]=df_points.loc[j,"Firstname"]
df_result.loc[i,"Skiclub"]=df_points.loc[j,"Skiclub"]
df_result.loc[i,"DHpoints"]=df_points.loc[j,"DHpoints"]
df_result.loc[i,"DHpos"]=df_points.loc[j,"DHpos"]
df_result.loc[i,"DHSta"]=df_points.loc[j,"DHSta"]
df_result.loc[i,"SLpoints"]=df_points.loc[j,"SLpoints"]
df_result.loc[i,"SLpos"]=df_points.loc[j,"SLpos"]
df_result.loc[i,"SLSta"]=df_points.loc[j,"SLSta"]
df_result.loc[i,"GSpoints"]=df_points.loc[j,"GSpoints"]
df_result.loc[i,"GSpos"]=df_points.loc[j,"GSpos"]
df_result.loc[i,"GSSta"]=df_points.loc[j,"GSSta"]
df_result.loc[i,"SGpoints"]=df_points.loc[j,"SGpoints"]
df_result.loc[i,"SGpos"]=df_points.loc[j,"SGpos"]
df_result.loc[i,"SGSta"]=df_points.loc[j,"SGSta"]
df_result.loc[i,"ACpoints"]=df_points.loc[j,"ACpoints"]
df_result.loc[i,"ACpos"]=df_points.loc[j,"ACpos"]
df_result.loc[i,"ACSta"]=df_points.loc[j,"ACSta"]
df_result.loc[i,"plistname"]=pfilename
#----------------Base-FIS-points-list をコピー-------------------------------
for j in range(0,len(df_base)):
if target_id==df_base.loc[j,"Fiscode"]:
print("Base:",i,j,target_id,df_base.loc[j,"Fiscode"],df_result.loc[i,"Name"])
df_result.loc[i,"Caldate_B"]=df_base.loc[j,"Calculationdate"]
df_result.loc[i,"Fiscode_B"]=df_base.loc[j,"Fiscode"]
df_result.loc[i,"Lastname_B"]=df_base.loc[j,"Lastname"]
df_result.loc[i,"Firstname_B"]=df_base.loc[j,"Firstname"]
df_result.loc[i,"Skiclub_B"]=df_base.loc[j,"Skiclub"]
df_result.loc[i,"DHpoints_B"]=df_base.loc[j,"DHpoints"]
df_result.loc[i,"DHpos_B"]=df_base.loc[j,"DHpos"]
df_result.loc[i,"DHSta_B"]=df_base.loc[j,"DHSta"]
df_result.loc[i,"SLpoints_B"]=df_base.loc[j,"SLpoints"]
df_result.loc[i,"SLpos_B"]=df_base.loc[j,"SLpos"]
df_result.loc[i,"SLSta_B"]=df_base.loc[j,"SLSta"]
df_result.loc[i,"GSpoints_B"]=df_base.loc[j,"GSpoints"]
df_result.loc[i,"GSpos_B"]=df_base.loc[j,"GSpos"]
df_result.loc[i,"GSSta_B"]=df_base.loc[j,"GSSta"]
df_result.loc[i,"SGpoints_B"]=df_base.loc[j,"SGpoints"]
df_result.loc[i,"SGpos_B"]=df_base.loc[j,"SGpos"]
df_result.loc[i,"SGSta_B"]=df_base.loc[j,"SGSta"]
df_result.loc[i,"ACpoints_B"]=df_base.loc[j,"ACpoints"]
df_result.loc[i,"ACpos_B"]=df_base.loc[j,"ACpos"]
df_result.loc[i,"ACSta_B"]=df_base.loc[j,"ACSta"]
df_result.loc[i,"baseFileame_B"]=idfarry[2]
# FIS非会員はポイントを10000でつぶす
startcol=df_result.columns.get_loc("FIS_id")
lastcol=df_result.columns.get_loc("baseFileame_B")
for i in range(0,len(df_result)):
print(i,df_result.loc[i,"FIS_id"])
if df_result.loc[i,"FIS_id"]=="0":
print("Not FIS member:",i,df_result.loc[i,"Name"])
#df_result.loc[i, 'FIS_id':'baseFileame_B'] = "10000"
#print(df_result)
fname1=DRIVE+'\FIS\points_'+bn
print(fname1)
df_result.to_csv(fname1, index=False,encoding="utf-8_sig")
#print()
if(v=="j"):#SAJID　FISID名簿　getid()する
print("SAJ")
url="https://sajdb.shikuminet.jp/alpine/biography?search_sports_code=AL&search_saj_competitors_number=&search_fis_competitors_number=&search_competitors_sex=&search_competitors_lastname=&search_competitors_firstname=&search_competitors_birth_year_from=&search_competitors_birth_year_to=&search_saj_competitors_registrated=1&search_fis_competitors_registrated=&search_organization=&search_team_name="
#html="https://sajdb.shikuminet.jp/alpine/2024/competition/5438/result"
df_id,text=getid(url)
print("=====>>SAJ FIS df_id=:",df_id.shape,text)
if v=="r":#race Title get
title=[]
race_url = "https://seikosportslink.com/sias/201/?i=20008861"
title=getTitle(race_url)
print("getTitle=",title[0],":",title[1])
if(v=="s"):#SEIKO SPORTS LINK Result to csv
# 環境設定
v=" "
while v!='e':
driver = webdriver.Chrome()
driver.get("https://seikosportslink.com/seiko/ssl/sportslink?a=portal&s=as")
#time.sleep(10)
v= input(" Current Page Input(ENTER) or 'e' exit SEIKO INPUT")
if v!='e':
url0 =v# driver.current_url#URL手動コピペ入力
url0 =driver.current_url#chrome 現在のURL自動入力
#sf=skey(url0)
#print("sf=",sf)
#time.sleep(10)
driver.implicitly_wait(30)
print("current url=",url0)
target_url =url0# "https://seikosportslink.com/sias/201/?i=20008861&s=SI&d=AS&g=W&e=030&c=01&p=1&u=00"
race_url=target_url.split("&")
startn=target_url.find("=")+1
stopn=target_url.find("&")
raceid=target_url[startn:stopn]
print(startn,stopn,raceid)
print(race_url)
title=getTitle(race_url[0])
print("getTitle=",title[0],":",title[1])
# URL指定とデータ取得
driver.get(target_url)
driver.implicitly_wait(30)
data = driver.page_source
action = ActionChains(driver)
action.key_down(Keys.CONTROL).send_keys('a').key_up(Keys.CONTROL).perform() # Ctrl + A
time.sleep(1) # 一時停止を入れることで、操作の間に少し待機する
action.key_down(Keys.CONTROL).send_keys('c').key_up(Keys.CONTROL).perform() # Ctrl + C
# クリップボードからコピーした内容を取得
#text = pyperclip.paste()#bugある
text = clipboard.paste()
print(text)
print(type(text))
#time.sleep
#tc=text.replace("\t",",")
#f=open(r'E:\FIS\SEIKO_SPORTS_LINK\df_SEIKO_'+title[0]+'_'+'pre.txt','w',encoding="utf-8_sig")#SAJ Biographyファイルの名前付きパスをset_idf.txtに記述しておく
#f.write(tc)
#f.close
rowstr=text.split("\r\n")
print("splitted rowstr")
list_rowstr=[]
tableflag=0
#columns
idflag=0 #Woman Men　id
wmid=""
for i in range(0,len(rowstr)):
print(i,":",rowstr[i])
w="W"
m="競技結果"
print(w in rowstr[i],m in rowstr[i])
if ( m in rowstr[i]) and idflag==0:
wmid=rowstr[i+1]
if wmid=="":
wmid=rowstr[i+2]
if wmid=="":
wmid=rowstr[i+3]
print("===============wmid=",wmid)
idflag=1
#wmid=poicol
s="Rank"
if rowstr[i].find(s)>=0:
tableflag=1
rowstrc=rowstr[i].replace("\t",",")
rowsclm=rowstrc.split(",")
print("rowclm=",len(rowsclm))
if len(rowsclm)==6:
clname=["Rank","Bib","Name","Club","Total","Diff"]
else:
clname=["Rank","Bib","Name","Club","Run 1","Run 2","Total","Diff"]
if tableflag==1:
rowstrc=rowstr[i].replace("\t",",")
rowsclm=rowstrc.split(",")
#print("rowclm=",len(rowsclm))
#if len(rowsclm)==6:
# clname=["Rank","Bib","Name","Club","Total","Diff"]
#else:
# clname=["Rank","Bib","Name","Club","Run 1","Run 2","Total","Diff"]
print(i,rowsclm)
list_rowstr.append(rowsclm)
rowstrc=""
#print(list_rowstr)
#if wmid=="":
#input("=========wmid ERROR 競技結果したに種目ない=============")
df=pd.DataFrame(list_rowstr,columns=clname)
#df.to_csv(r'E:\FIS\df_rowstr.csv', index=False,encoding="utf-8_sig")
#print(df)
#wmid=poicol
df = df.drop(0)
df=df.reset_index(drop=True)
df["RaceTitle"]=title[0]
df["RaceDate"]=title[1]
df["Women-Men"]=wmid
#print("df.columns=",df.columns.tolist())
#print()
#print(df)
# DNF行 並び替え 　
firstnameH=""
lastnameH=""
# DNF,DSQ,DNS,DNQ2,NPS,DQO,DPDの有無
#bib列が名前になるので
#nums=pd.Series(df.loc[:,"Bib"])
#DNF=[str(num).isnumeric() for num in nums]
#print(DNF)
#list_dns=["DNF","DSQ","DNS","DNQ2","NPS","DQO","DPD"]
df=df.fillna('-')#Nanを"-"で埋める
df=df.replace(['NaN', 'None', ''], '-')
for i in range(0,len(df)-1): #DNF検出
word=df.loc[i,"Bib"]
#word=df.loc[i,"Bib"]
#print("word=",word,"len clname=",len(clname))
re_kanji = re.compile(r'^[\u4E00-\u9FD0]+$')
status_kanji = re_kanji.fullmatch(word)
#if status_kanji==True:
if word.isdecimal()==False:
if len(clname)==8:
#if df.loc[i,"Run 2"]=="DNF" or df.loc[i,"Run 2"]=="DNS":#DNFの並び替え　TotalにDNF
#print(i,"DNF")
df.loc[i,"Total"]=df.loc[i,"Run 2"]
df.loc[i,"Run 2"]=df.loc[i,"Run 1"]
df.loc[i,"Run 1"]=df.loc[i,"Club"]
df.loc[i,"Club"]=df.loc[i,"Name"]
df.loc[i,"Name"]=df.loc[i,"Bib"]
df.loc[i,"Bib"]="0"
elif len(clname)==6:
#if df.loc[i,"Club"]=="DNF" or df.loc[i,"Club"]=="DNS":#DNFの並び替え　TotalにDNF
df.loc[i,"Total"]=df.loc[i,"Club"]
#df.loc[i,"Run 2"]=df.loc[i,"Run 1"]
#df.loc[i,"Run 1"]=df.loc[i,"Club"]
df.loc[i,"Club"]=df.loc[i,"Name"]
df.loc[i,"Name"]=df.loc[i,"Bib"]
df.loc[i,"Bib"]="0"
df.loc[i,"Diff"]="0"
#df　最下部　
# 特定の列に基づいて行を削除する
df.drop(df[df['Name'] =='-'].index, inplace=True)
#race dateをファイル名末尾に追加
rdatestr=df.loc[1,"RaceDate"]
rdate = re.sub(r'\D', '', rdatestr)
print("rdate=",rdate)
#df.to_csv(DRIVE+'\FIS\SEIKO_SPORTS_LINK\df_SEIKO_'+rdate+title[0]+'_'+wmid+'.csv', index=False,encoding="utf-8_sig")
df.to_csv(r'E:\FIS\SEIKO_SPORTS_LINK\df_SEIKO_'+rdate+title[0]+'_'+wmid+'.csv', index=False,encoding="utf-8_sig")
#予備でtextをファイル化
#fs=open(r'E:\FIS\SEIKO_SPORTS_LINK\df_SEIKO_'+title[0]+'_'+wmid+'.txt','w',encoding="utf-8_sig")#SAJ Biographyファイルの名前付きパスをset_idf.txtに記述しておく
#time.sleep(2)
#fs.write(tc)
#time.sleep(2)
#fs.close
if(v=="o"):#Official results html table
driver = webdriver.Chrome()
driver.get("https://www.fis-ski.com/DB/general/results.html?sectorcode=AL&raceid=119466")
#time.sleep(10)
v= input(" Current Page Input(ENTER) or 'e' exit SEIKO INPUT")
if v!='e':
"""
url0 =v# driver.current_url#URL手動コピペ入力
#url0 ="https://www.fis-ski.com/DB/general/results.html?sectorcode=AL&raceid=119466"#driver.current_url#chrome 現在のURL自動入力
#time.sleep(10)
print("current url=",url0)
#data = pd.read_html(url0, header = 0)
#page = requests.get(url0)
# htmlをパース
#soup = BeautifulSoup(page.text, 'html.parser')
#print(soup.prettify())
#print(data)
print()
target_url =url0# "https://seikosportslink.com/sias/201/?i=20008861&s=SI&d=AS&g=W&e=030&c=01&p=1&u=00"
race_url=target_url.split("&")
startn=target_url.find("=")+1
stopn=target_url.find("&")
raceid=target_url[startn:stopn]
print(startn,stopn,raceid)
print(race_url)
title=getTitle(race_url[0])
print("getTitle=",title[0],":",title[1])
# URL指定とデータ取得
driver.get(target_url)
data = driver.page_source
action = ActionChains(driver)
action.key_down(Keys.CONTROL).send_keys('a').key_up(Keys.CONTROL).perform() # Ctrl + A
time.sleep(5) # 一時停止を入れることで、操作の間に少し待機する
action.key_down(Keys.CONTROL).send_keys('c').key_up(Keys.CONTROL).perform() # Ctrl + C
time.sleep(5)
"""
# クリップボードからコピーした内容を取得
#text = pyperclip.paste()#bugある
text = clipboard.paste()
time.sleep(5)
print(text)
print(type(text))
#time.sleep
#tc=text.replace("\r",",")
f=open(DRIVE+'\FIS\SAJ_Official\df_SAJOff_Work.txt','w',encoding="utf-8_sig")#SAJ Biographyファイルの名前付きパスをset_idf.txtに記述しておく
f.write(text)
f.close
rowstr=text.split("\r\n")
pprint.pprint(rowstr)
print("splitted rowstr")
list_rowstr=[]
tableflag=0
wmid=poicol
#columns
idflag=0 #Woman Men　id
for i in range(0,len(rowstr)):
print(i,":",rowstr[i])
#w="W"
m="Event details"
#print(w in rowstr[i],m in rowstr[i])
#if ( m in rowstr[i]) and idflag==0:
#wmid=rowstr[i+1]
#if wmid=="":
#wmid=rowstr[i+2]
#if wmid=="":
#wmid=rowstr[i+3]
#print("wmid=",wmid)
#idflag=1
wmid=poicol
s="Rank"
if rowstr[i].find(s)>=0:
tableflag=1
rowstrc=rowstr[i].replace("\t",",")
rowsclm=rowstrc.split(",")
print("rowclm=",len(rowsclm))
if len(rowsclm)==6:
clname=["Rank","Bib","Name","Club","Total","Diff"]
else:
clname=["Rank","Bib","Name","Club","Run 1","Run 2","Total","Diff"]
if tableflag==1:
rowstrc=rowstr[i].replace("\t",",")
rowsclm=rowstrc.split(",")
#print("rowclm=",len(rowsclm))
#if len(rowsclm)==6:
# clname=["Rank","Bib","Name","Club","Total","Diff"]
#else:
# clname=["Rank","Bib","Name","Club","Run 1","Run 2","Total","Diff"]
print(i,rowsclm)
list_rowstr.append(rowsclm)
rowstrc=""
#print(list_rowstr)
df=pd.DataFrame(list_rowstr,columns=clname)
#df.to_csv(r'E:\FIS\df_rowstr.csv', index=False,encoding="utf-8_sig")
#print(df)
wmid=poicol
df = df.drop(0)
df=df.reset_index(drop=True)
df["RaceTitle"]=title[0]
df["RaceDate"]=title[1]
df["Women-Men"]=wmid
#print("df.columns=",df.columns.tolist())
#print()
#print(df)
# DNF行 並び替え 　
firstnameH=""
lastnameH=""
# DNF,DSQ,DNS,DNQ2,NPS,DQO,DPDの有無
#bib列が名前になるので
#nums=pd.Series(df.loc[:,"Bib"])
#DNF=[str(num).isnumeric() for num in nums]
#print(DNF)
#list_dns=["DNF","DSQ","DNS","DNQ2","NPS","DQO","DPD"]
df=df.fillna('-')#Nanを"-"で埋める
if(v=="f"):#高速15秒　DF　CONCATで早くなるか
#レース結果表へIDとポイントデータで選手名にフィットさせて追記する
#次にレースの日時と適合したFIS-points-list-AL-xxx.csvファイルをさがして読み込んでフィット
print("Fitting")
print("Auto READ:set_idf.txt=>iDNo list File & FISPoint list FILEName ")
#f=open(r'E:\FIS\set_idf-ptf.txt','r')#レースresultとFISpointlistファイル名の2個のtxtを読む derimiter カンマ
#f=open(DRIVE+'\FIS\set_idf-ptf-base24.txt','r')#レースresultとFIS-points-list,Base-Fis-pointsの3個のtxtを読む delimiter カンマ
f=open(r'E:\FIS\set_idf-ptf-base24.txt','r')#レースresultとFIS-points-list,Base-Fis-pointsの3個のtxtを読む delimiter カンマ
idf=f.read()
f.close
print("idf=",idf)
idfarry=idf.split(",")
print("SAJ Biography:",idfarry[0])
print("FIS points Page:",idfarry[1])
print("Base FIS point list:",idfarry[2])
#Baseファイル読み込み
df_base=pd.read_csv( idfarry[2], low_memory=True)#SAJ Biography ファイル読み込み
print("df_base=",df_base)
print("Base Read to df_base:",idfarry[2],"df_base shape=",df_base.shape)
df_id = pd.read_csv( idfarry[0], low_memory=True)#SAJ Biography ファイル読み込み
df_id.fillna(0)
df_id.astype('int',errors='ignore')
#print("df_id")
print(df_id)
df_fispage = pd.read_csv(idfarry[1], low_memory=True)
#print("dr_fispage=",df_fispage)
#print("===>>Created df_id=",df_id.shape,idf)
#SEIKO フォルダから生データ読み込み
df_result,bn=File_selreadFIS()#FIS dirからresultファイルを指定してdfで返す
df_result=missSpell(df_result)
#print("===>>Created df_result=",df_result.shape,bn)
df_result=fitid(df_result,df_id,bn)
#print("ided df_result=",df_result)
#fname='E:\FIS\id_'+bn
#print(fname)
#df_result.to_csv(fname, index=False,encoding="utf-8_sig")
#競技情報取得
#poicol,poscol,poicol_B,poscol=B=selpoicol(df_result)
print("poicol=",poicol)
#---------------------FIS list Fitting-----------------------------------
#df_resultの日付から適応fis-points-listをdf_fispageで索引検索して選定
rdate=df_result.loc[1,"RaceDate"]
datestr=rdate.split("年")
yeari=int(datestr[0])
datestr1=(datestr[1]).split("月")
monthi=int(datestr1[0])
datestr2=(datestr1[1]).split("日")
dayi=int(datestr2[0])
print(yeari,"/",monthi,"/",dayi)
result_days=period_71(dayi,monthi,yeari)#7月１日から通算日数計算
#fis dateと比較
start_days=360
for i in range(1,len(df_fispage)):
fis_start=df_fispage.loc[i,"start_date"]
fis_sdate=fis_start.split("-")
fis_sday=int(fis_sdate[0])
fis_smonth=int(fis_sdate[1])
fis_syear=int(fis_sdate[2])
start_days_1=start_days
start_days=period_71(fis_sday,fis_smonth,fis_syear) #7/1を起点とした日付単位に変換
fis_last=df_fispage.loc[i,"last_date"]
fis_ldate=fis_last.split("-")
fis_lday=int(fis_ldate[0])
fis_lmonth=int(fis_ldate[1])
fis_lyear=int(fis_ldate[2])
last_days=period_71(fis_lday,fis_lmonth,fis_lyear) #7/1を起点とした日付単位に変換
#perios71同士で比較
if start_days<result_days and start_days_1>result_days:#
print("Hit result_days")
print(df_fispage.loc[i,"start_date"],"-",rdate,"-",df_fispage.loc[i-1,"start_date"])
target_filename=df_fispage.loc[i,"filename"]
print("Hit fis-points-list=",target_filename)
break
#target file 読み込み
path=DRIVE+'\FIS\FIS-points-list-AL/'
pfilename=path+target_filename
print(pfilename)
df_points = pd.read_csv( pfilename, low_memory=True)#fis-points-listファイル読み込み
print(df_points)
#競技情報
#resultでfiscodeが無い人を名前で検索して、fiscode超出
list_noname=[]
list_nonameidx=[]
for i in range(0,len(df_result)):
#print(i,":FIS_id=",df_result.loc[i,"FIS_id"])
if df_result.loc[i,"FIS_id"]=='0':
no_name=df_result.loc[i,"Name"]
#print(i,no_name)
list_noname.append(no_name)
list_nonameidx.append(i)
print("no_name=",list_noname)
name_index=[]
fiscodei=[]
for i in range(0,len(list_noname)):#df_pointsからLasenameとFirstnameが一致する行を検出してdf_result修正--
names=list_noname[i].split(" ")
for j in range(0,len(df_points)):
#if(df_points.loc[j,"Lastname"]==names[0] and df_points.loc[j,"Firstname"]==names[1]):
if(df_points.loc[j,"Lastname"] in list_noname[i] and df_points.loc[j,"Firstname"] in list_noname[i]):
name_index.append(list_nonameidx[i])
fiscodei.append(df_points.loc[j,"Fiscode"])
#print("name_index=",name_index)
#print("fiscodei=",fiscodei)
df_result=addFIS_id(df_result)
for i in range(0,len(name_index)):
df_result.loc[name_index[i],"FIS_id"]=fiscodei[i]
print("Repaired_Noname:",name_index[i],df_result.loc[name_index[i],"FIS_id"],df_result.loc[name_index[i],"Name"])
#最終チェック
for i in range(0,len(df_result)):
if (df_result.loc[i,"FIS_id"]=="0") or (df_result.loc[i,"FIS_id"]==""):
print("Not Repaired:",i,df_result.loc[i,"Name"],df_result.loc[i,"FIS_id"])
# index DEBUG
fname1=DRIVE+'\FIS\id_RepairedNoname_'+bn
print(fname1)
df_result.to_csv(fname1, index=False,encoding="utf-8_sig")
#内包化-------------------------
fiscoderow=[]
df_result["FIS_id"]=df_result['FIS_id'].astype('int')
target_col=df_result["FIS_id"]
des_col=df_points["Fiscode"]
print(target_col)
print("Fiscode=",des_col)
#NL 検索
df_fitfis0=df_points.query('Fiscode in @target_col')
df_fitfis=df_fitfis0.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_fitfis.reset_index(inplace=True, drop=True)
print(df_fitfis)
#BL　検索
print("df_base=",df_base)
df_fitBL0=df_base.query('Fiscode in @target_col')
df_fitBL=df_fitBL0.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_fitBL.reset_index(inplace=True, drop=True)
df_fitBL= df_fitBL.add_suffix('_B')
print("df_fit_BL=",df_fitBL)
#resltcol=["Diff","SAJ_id","FIS_id"]
#fispcol=["DHpoints","DHpos","DHSta","SLpoints","SLpos","SLSta","GSpoints","GSpos","GSSta","SGpoints","SGpos","SGSta","ACpoints","ACpos","ACSta"]
#basepcol=["DHpoints_B","DHpos_B","DHSta_B","SLpoints_B","SLpos_B","SLSta_B","GSpoints_B","GSpos_B","GSSta_B","SGpoints_B","SGpos_B","SGSta_B","ACpoints_B","ACpos_B","ACSta_B"]
#df_result[resltcol]=.apply(pd.to_numeric)
#df_result[resltcol].astype("float")
#df_fitfis[fispcol].astype("float")
#df_fitBL[basepcol].astype("float")
#print()
#df_result順にdf_fitfisをソート
fiscol=df_fitfis.columns.values
#print(fiscol)
colN=df_fitBL.shape[1]
l_fitBL0=df_fitBL.loc[0,:]
l_fitBL0=["0"]*colN
print("l_fitBL0=",l_fitBL0)
print(colN,"df_BL.shape=",df_fitBL.shape,"l0_fitBL=",l_fitBL0)
# index DEBUG
#fname1='E:\FIS\FitBLError_'+bn
#print(fname1)
#df_fitBL.to_csv(fname1, index=False,encoding="utf-8_sig")
print("l_fitBL0=",l_fitBL0)
list_fitfis=[]
list_fitBL=[]
list_result3=[]
for i in range(0,len(df_result)):
#l_result=df_result.loc[i,:]
#print("Result:",i,j,l_result)
for j in range(0,len(df_fitfis)):
if df_fitfis.loc[j,"Fiscode"] ==df_result.loc[i,"FIS_id"]:
l_fitfis=df_fitfis.loc[j,:]
list_fitfis.append(l_fitfis)
#print("NL:",i,j,l_fitfis)
break
#BL
hitflag=0
for k in range(0,len(df_fitBL)):
if df_fitBL.loc[k,"Fiscode_B"] ==df_result.loc[i,"FIS_id"]:
l_fitBL=df_fitBL.loc[k,:]
list_fitBL.append(l_fitBL)
print("BL:",i,k,l_fitBL)
hitflag=1
break
if hitflag==0:
l_fitBL=l_fitBL0
#list_fitBL.append(l_fitBL0)
print("BL:",i,k,l_fitBL)
#print()
df_fitfisS=pd.DataFrame(list_fitfis)
df_fitfisS.reset_index(inplace=True, drop=True)
#print(df_fitfisS)
df_fitBLS=pd.DataFrame(list_fitBL)
df_fitBLS.reset_index(inplace=True, drop=True)
#print(df_fitBLS.shape,df_fitBLS.columns.values)
#============BL 不足穴埋め========================
# index DEBUG
fname1=DRIVE+'\FIS\FitFispoints_'+bn
#print(fname1)
df_fitfisS.to_csv(fname1, index=False,encoding="utf-8_sig")
#print()
# index DEBUG
fname1=DRIVE+'\FIS\FitBLpoints_'+bn
#print(fname1)
df_fitBLS.to_csv(fname1, index=False,encoding="utf-8_sig")
#print()
#=================必要なポイントデータだけresultに加える=========================================
#print("df_result:",df_result.shape)
#print("df_fitfisSorted:",df_fitfisS.shape)
#print("df_fitBLSorted:",df_fitBLS.shape)
#print("df_fitfisSorted=",df_fitfisS)
df_result.loc[:,"Firstname"]=df_fitfisS.loc[:,"Firstname"]
df_result.loc[:,"Lastname"]=df_fitfisS.loc[:,"Lastname"]
df_result.loc[:,"Fiscode"]=df_fitfisS.loc[:,"Fiscode"]
df_result.loc[:,poicol]=df_fitfisS.loc[:,poicol]
#BLをresultへコピー　抜け行を飛ばす
for i in range(0,len(df_result)):
for j in range(0,len(df_fitBLS)):
if df_result.loc[i,"FIS_id"]==df_fitBLS.loc[j,"Fiscode_B"]:
df_result.loc[i,"Fiscode_B"]=df_fitBLS.loc[j,"Fiscode_B"]
df_result.loc[i,poicol+"_B"]=df_fitBLS.loc[j,poicol+"_B"]
break
df_result=df_result.fillna('-')#Nanを"-"で埋める
df_result=df_result.replace(['NaN', 'None', ''], 'NaN')
#棄権選手のデータ初期化
if i in range(0,len(df_result)):
#print("DNF DNS DSQ delete")
if df_result.loc[i,"Total"].isnumeric()==False:
df_result.loc[i,"Total"]='D'
#race dateをファイル名末尾に追加
#rdatestr=df_result.loc[1,"RaceDate"]
#rdate = re.sub(r'\D', '', rdatestr)
#print("rdate=",rdate)
#fname1='E:\FIS\points'+rdate+'_result_'+bn
fname1=DRIVE+'\FIS\points+_df_result_'+bn
#print(fname1)
df_result.to_csv(fname1, index=False,encoding="utf-8_sig")
#print()
# index DEBUG
#fname1='E:\FIS\points_df_result3_'+bn
#print(fname1)
#df_result3.to_csv(fname1, index=False,encoding="utf-8_sig")
#print()
"""
# FIS非会員はポイントを10000でつぶす
startcol=df_result.columns.get_loc("FIS_id")
lastcol=len(df_result3.columns)#df_result.columns.get_loc("baseFileame_B")
for i in range(0,len(df_result)):
print(i,df_result.loc[i,"FIS_id"])
if df_result.loc[i,"FIS_id"]=="0":
print("Not FIS member:",i,df_result.loc[i,"Name"])
#df_result.loc[i, 'FIS_id':'baseFileame_B'] = "10000"
#print(df_result)
fname1='E:\FIS\points_'+bn
print(fname1)
df_result.to_csv(fname1, index=False,encoding="utf-8_sig")
"""
if(v=="l"):#Listen Slack return messages list
list_msg=[]
list_msg=ListenSlack()
for i in range(0,len(list_msg)):
#print(i,":",list_msg[i])
if "https://seikosportslink.com/" in list_msg[i]:
text = list_msg[i].lstrip("<")
#text1=text.rstrip(">")
print(text)
print()
if(v=="w"):#WEBHOOK Send FPE #web_service　
words=input("Key in message to send FPE web-service:")
webhook(words)
if(v=="a"):#Auto run
#driver = webdriver.Chrome()
#driver.get("https://seikosportslink.com/seiko/ssl/sportslink?a=portal&s=as")
#v= input(" Current Page Input(ENTER) or 'e' exit SEIKO INPUT")
start = time.time() # 現在時刻（処理開始前）を取得
#url0 =driver.current_url#chrome 現在のURL自動入力
#SLACK Listen
url0=lkey()
print("Get Slack url=",url0)
#SEIKO SCRAPING
fs=skey(url0)
#FIT SEIKO FILE
pf=fkey(fs)
#print("FItted file=",pf)
#Calculation points File
cf=ckey(pf)
#print("Calculation Finished=",cf)
end = time.time() # 現在時刻（処理完了後）を取得
print("HTML")
bname,haname=hkey(cf)
sname=ftp(haname)
webhook(sname)
driver = webdriver.Chrome()
driver.get(sname)
time_diff = end - start
print("=============Elapsed Time =",time_diff,"============================")
input()
driver.quit()
if(v=="t"):#Auto Timer
print("AutoTimer")
interval=10#sec
tst=time.time()
kaisu=0#タイマーカウンタ
url0=""
url0_1=""
while(True):
if(time.time()-tst>interval):
print(kaisu,":tst=",time.time()-tst)
tst=time.time()
start = time.time() # 現在時刻（処理開始前）を取得
#url0 =driver.current_url#chrome 現在のURL自動入力
#SLACK Listen
url0=lkey()
#if url0!="0" and kaisu==0:
#url0_1=url0
print("=================NOW url0,url0_1=",url0,url0_1)
if url0!="0" and url0!=url0_1: #url0!=url0_1 :
url0_1=url0
print("=============SLACK Recieved New URL================")
print(kaisu,":",start,"sec:Get Slack url=",url0)
#SEIKO SCRAPING
fs=skey(url0)
#FIT SEIKO FILE
pf=fkey(fs)
print("FItted file=",pf)
#Calculation points File
cf=ckey(pf)
print("Calculation Finished=",cf)
end = time.time() # 現在時刻（処理完了後）を取得
print("HTML")
bnhh,haname=hkey(cf)
sname=ftp(haname)
report=bnhh+"\r"+sname
webhook(report)
driver = webdriver.Chrome()
driver.get(sname)
time_diff = end - start
print("=============Elapsed Time =",time_diff,"============================")
kaisu+=1
else:
print("===========No URL========================= ",kaisu)
if(v=="ftp"):#ftp tet
hf=DRIVE+"\FIS\Test_+_df_result_df_SEIKO_The 26th Nukabira Gensenkyo Spring Cup GS Race_24M.html"
sname=ftp(hf)
def webhook(mes):
print(mes)
#WEB_HOOK_URL="https://hooks.slack.com/services/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"
WEB_HOOK_URL=WEB_HOOK_SM
requests.post(WEB_HOOK_URL, data=json.dumps({ "text" : mes,}))
def poicol_search(tc):#SEIKO scraping Textファイルの中に競技名" DH "," SL "," GS "," SG "," AC "があるか
with open(tc,'r') as f:#,encoding="utf-8_sig") as f:#SAJ Biographyファイルの名前付きパスをset_idf.txtに記述しておく
text=f.read()
#f.close
text=tc+","+text
global shumoku,poicol,poscol,poicol_B,poscol_B
if " DH " in text:
shumoku="DH"
fv=1250
pmax=330
poicol="DHpoints"
poscol="DHpos"
poicol_B="DHpoints_B"
poscol_B="DHpos_B"
elif " SL " or "スラローム" in text:
shumoku="SL"
fv=730
pmax=165
poicol="SLpoints"
poscol="SLpos"
poicol_B="SLpoints_B"
poscol_B="SLpos_B"
elif " GS " in text:
shumoku="GS"
fv=1010
pmax=220
poicol="GSpoints"
poscol="GSpos"
poicol_B="GSpoints_B"
poscol_B="GSpos_B"
elif " SG " in text:
shumoku="SG"
fv=1190
pmax=270
poicol="SGpoints"
poscol="SGpos"
poicol_B="SGpoints_B"
poscol_B="SGpos_B"
elif " AC " in text:
shumoku="AC"
fv=1360
pmax=270
poicol="ACpoints"
poscol="ACpos"
poicol_B="ACpoints_B"
poscol_B="ACpos_B"
else:
input("===============ERORR No race shumoku================ ")
return shumoku,poicol,poscol,poicol_B,poscol_B
def ListenSlack():#return message list
#print()
url = "https://slack.com/api/conversations.history"
#url = "https://slack.com/api/conversation_archive"
#token = "xoxb-7462748487234-7491684617312-Z1KBgwoahUm8BFs38MdrLlco" # FPtokenを入れてください
token=TOKEN_SM
#token = "xoxb-7462748487234-7495099592096-PRjap6Bnj5XFJGrYKecBgM7I"# powerful
header={ "Authorization": "Bearer {}".format(token) }
#payload ={"channel" : "C07DLJZ5M7V"}#,"oldest":(time.time() -60)} #C07DLJZ5M7V =#WEB_SERVICE C07DHQZ65GD=#general
#payload ={"channel" : CHN_SM,"oldest":(time.time()-1200)}#,"oldest":(time.time() -60)} #C07DLJZ5M7V =#WEB_SERVICE C07DHQZ65GD=#general
payload ={"channel" : CHN_SM}
res = requests.get(url,headers=header,params=payload)
#print(res)
json_channel_history = res.json()
list_mes=[]
i=0
for message in json_channel_history["messages"]:
txts=message["text"]
#list_txts=txts.split("|")
#print(i,":txts=",txts)
#if i<3:
#print(i,":txts=",list_txts)
list_mes.append(txts)
i+=1
#mesarray=list_mes.split(',')
#for i in range(0,len(list_mes)):
#print(i,":",list_mes[i])
#https://fpeshq.slack.com/archives/C07DLJZ5M7V
return list_mes
def skey(url0):#s key SEIKO SPORTS　LINK　指定URLからスクレイピングする
print("---------------------skey---------------------------")
#print("Slack Listen url0=",url0)
#url0=url
driver = webdriver.Chrome()
#driver.get("https://seikosportslink.com/seiko/ssl/sportslink?a=portal&s=as")
#url="https://seikosportslink.com/sias/201/?i=20008468&s=SI&d=AS&g=W&e=030&c=01&p=1&u=00"#DEBUG 12/28
#print("url0 size=",len(url0),"url size=",len(url))
#print("url0=",url0)
#print("url0 size =",len(url0))
#url1=url0.replace('amp;', '')
#for i in range(0,len(url0)):
#if i<len(url):
# print(i,":",url0[i],url[i],url1[i])
#else:
# print(i,":",url0[i])
#print("Slack input url0=",url0)
target_url =url0
#print("target url=",target_url)
#=============TITLE 取得===========================================================
# "https://seikosportslink.com/sias/201/?i=20008861&s=SI&d=AS&g=W&e=030&c=01&p=1&u=00"
race_url=target_url.split("&")
startn=target_url.find("=")+1
stopn=target_url.find("&")
raceid=target_url[startn:stopn]
#print(startn,stopn,raceid)
#print(race_url)
title=getTitle(race_url[0])#タイトルページ表示してゲット
#print("getTitle=",title[0],":",title[1])
#=====================================================================================
# ページ取得とレースタイムデータ取得
driver.get(target_url)
#WebDriverWait(driver, 15).until(EC.presence_of_all_elements_located)
time.sleep(10)
#待ってないからエラー　driver.implicitly_wait(30)
#url0 =driver.current_url#chrome 現在のURL自動入力
#コピペ開始
action = ActionChains(driver)
action.key_down(Keys.CONTROL).send_keys('a').key_up(Keys.CONTROL).perform() # Ctrl + A
#time.sleep(10) # 一時停止を入れることで、操作の間に少し待機する
action.key_down(Keys.CONTROL).send_keys('c').key_up(Keys.CONTROL).perform() # Ctrl + C
#time.sleep(10)
# クリップボードからコピーした内容を取得
#text = pyperclip.paste()#bugある
text = clipboard.paste()
time.sleep(3)
#print("1st text=",text)
#print(type(text))
#time.sleep
tc=text.replace("\t",",")
tc2=tc.replace('\xa0', '')
tc2=tc2.replace('\xa9', '')
#tc3=tc2.encode('CP932')
#print("tc=",tc2)
textname=DRIVE+'\FIS\SEIKO_SPORTS_LINK\df_SEIKO_'+title[0]+'_'+'pre.txt'
with open(textname,'w') as f : #,encoding="utf-8_sig")
f.write(tc2)
#print(tc2, end="", file=f)
#f.close
driver.quit()
print("================Scraping Finished:text size=",len(tc),"=======================")
rowstr=text.split("\r\n")
#print("splitted rowstr")
list_rowstr=[]
tableflag=0
#wmid=""
#columns
idflag=0 #Woman Men　id
shumoku,poicol,poscol,poicol_B,poscol_B =poicol_search(textname)
wmid=poicol
for i in range(0,len(rowstr)):
#print(i,":",rowstr[i])
#time.sleep(1)
#w="W"
"""
m="競技結果"
#print(w in rowstr[i],m in rowstr[i])
if ( m in rowstr[i]) and idflag==0:
wmid=rowstr[i+1]
if wmid=="":
wmid=rowstr[i+2]
if wmid=="":
wmid=rowstr[i+3]
print("wmid=",wmid)
idflag=1
else:
input("wmid null error at 競技結果下")
"""
s="Rank"
if rowstr[i].find(s)>=0:
tableflag=1
rowstrc=rowstr[i].replace("\t",",")
rowsclm=rowstrc.split(",")
#print("rowclm=",len(rowsclm))
if len(rowsclm)==6:
clname=["Rank","Bib","Name","Club","Total","Diff"]
else:
clname=["Rank","Bib","Name","Club","Run 1","Run 2","Total","Diff"]
if tableflag==1:
rowstrc=rowstr[i].replace("\t",",")
rowsclm=rowstrc.split(",")
#print("rowclm=",len(rowsclm))
#if len(rowsclm)==6:
# clname=["Rank","Bib","Name","Club","Total","Diff"]
#else:
# clname=["Rank","Bib","Name","Club","Run 1","Run 2","Total","Diff"]
#print(i,rowsclm)
list_rowstr.append(rowsclm)
rowstrc=""
#print(list_rowstr)
df=pd.DataFrame(list_rowstr,columns=clname)
#df.to_csv(r'E:\FIS\df_rowstr.csv', index=False,encoding="utf-8_sig")
#print(df)
wmid=poicol
df = df.drop(0)
df=df.reset_index(drop=True)
df["RaceTitle"]=title[0]
df["RaceDate"]=title[1]
df["Women-Men"]=wmid
#print("df.columns=",df.columns.tolist())
#print()
#print(df)
# DNF行 並び替え 　
firstnameH=""
lastnameH=""
# DNF,DSQ,DNS,DNQ2,NPS,DQO,DPDの有無
#bib列が名前になるので
#nums=pd.Series(df.loc[:,"Bib"])
#DNF=[str(num).isnumeric() for num in nums]
#print(DNF)
#list_dns=["DNF","DSQ","DNS","DNQ2","NPS","DQO","DPD"]
df=df.fillna('-')#Nanを"-"で埋める
df=df.replace(['NaN', 'None', ''], '-')
for i in range(0,len(df)-1): #DNF検出
word=df.loc[i,"Bib"]
#word=df.loc[i,"Bib"]
#print("word=",word,"len clname=",len(clname))
re_kanji = re.compile(r'^[\u4E00-\u9FD0]+$')
status_kanji = re_kanji.fullmatch(word)
#if status_kanji==True:
if word.isdecimal()==False:
if len(clname)==8:
#if df.loc[i,"Run 2"]=="DNF" or df.loc[i,"Run 2"]=="DNS":#DNFの並び替え　TotalにDNF
#print(i,"DNF")
df.loc[i,"Total"]=df.loc[i,"Run 2"]
df.loc[i,"Run 2"]=df.loc[i,"Run 1"]
df.loc[i,"Run 1"]=df.loc[i,"Club"]
df.loc[i,"Club"]=df.loc[i,"Name"]
df.loc[i,"Name"]=df.loc[i,"Bib"]
df.loc[i,"Bib"]="0"
elif len(clname)==6:
#if df.loc[i,"Club"]=="DNF" or df.loc[i,"Club"]=="DNS":#DNFの並び替え　TotalにDNF
df.loc[i,"Total"]=df.loc[i,"Club"]
#df.loc[i,"Run 2"]=df.loc[i,"Run 1"]
#df.loc[i,"Run 1"]=df.loc[i,"Club"]
df.loc[i,"Club"]=df.loc[i,"Name"]
df.loc[i,"Name"]=df.loc[i,"Bib"]
df.loc[i,"Bib"]="0"
df.loc[i,"Diff"]="0"
#df　最下部　
# 特定の列に基づいて行を削除する
df.drop(df[df['Name'] =='-'].index, inplace=True)
#race dateをファイル名末尾に追加
rdatestr=df.loc[1,"RaceDate"]
rdate = re.sub(r'\D', '', rdatestr)
#print("rdate=",rdate)
sfile=DRIVE+'\FIS\SEIKO_SPORTS_LINK\df_SEIKO_'+rdate+title[0]+'_'+wmid+'.csv'
df.to_csv(sfile, index=False,encoding="utf-8_sig")
#sfile=DRIVE+'\FIS\SEIKO_SPORTS_LINK\df_SEIKO_'+rdate+title[0]+'_'+wmid+'.csv'
return sfile
#予備でtextをファイル化
#fs=open(r'E:\FIS\SEIKO_SPORTS_LINK\df_SEIKO_'+title[0]+'_'+wmid+'.txt','w',encoding="utf-8_sig")#SAJ Biographyファイルの名前付きパスをset_idf.txtに記述しておく
#time.sleep(2)
#fs.write(tc)
#time.sleep(2)
#fs.close
def fkey(fs):# fit SEIKO file list return pf points-file-Name
print("-----------------fkey--------------------")
#レース結果表へIDとポイントデータで選手名にフィットさせて追記する
#次にレースの日時と適合したFIS-points-list-AL-xxx.csvファイルをさがして読み込んでフィット
#print("Fitting")
#print("Auto READ:set_idf.txt=>iDNo list File & FISPoint list FILEName ")
#f=open(r'E:\FIS\set_idf-ptf.txt','r')#レースresultとFISpointlistファイル名の2個のtxtを読む derimiter カンマ
with open(DRIVE+'\FIS\set_idf-ptf-base24.txt','r') as f:#レースresultとFIS-points-list,Base-Fis-pointsの3個のtxtを読む delimiter カンマ
idf=f.read()
#f.close
#print("idf=",idf)
idfarry=idf.split(",")
#print("SAJ Biography:",idfarry[0])
#print("FIS points Page:",idfarry[1])
#print("Base FIS point list:",idfarry[2])
#Baseファイル読み込み
df_base=pd.read_csv( idfarry[2], low_memory=True)#SAJ Biography ファイル読み込み
#print("df_base=",df_base)
#print("Base Read to df_base:",idfarry[2],"df_base shape=",df_base.shape)
df_id = pd.read_csv( idfarry[0], low_memory=True)#SAJ Biography ファイル読み込み
df_id.fillna(0)
df_id.astype('int',errors='ignore')
#print("df_id")
#print(df_id)
df_fispage = pd.read_csv(idfarry[1], low_memory=True)
#print("dr_fispage=",df_fispage)
#print("===>>Created df_id=",df_id.shape,idf)
#SEIKO フォルダから生データ読み込み
#df_result,bn=File_selreadFIS()#FIS dirからresultファイルを指定してdfで返す
filename=fs
df_result = pd.read_csv(filename, low_memory=True)
df_result=df_result.reset_index()
# filenameからbasenameをとりだしてファイル種類区別
#print(">>>>>>READ FILE;",filename)
bn = os.path.basename(filename)
#ReadFile=basename
#print("READ SEIKO File=",filename)
df_result=missSpell(df_result)
#print("===>>Created df_result=",df_result.shape,bn)
df_result=fitid(df_result,df_id,bn)
#print("ided df_result=",df_result)
#fname='E:\FIS\id_'+bn
#print(fname)
#df_result.to_csv(fname, index=False,encoding="utf-8_sig")
#競技情報取得
#poicol,poscol,poicol_B,poscol=B=selpoicol(df_result)
#print("poicol=",poicol)
#---------------------FIS list Fitting-----------------------------------
#df_resultの日付から適応fis-points-listをdf_fispageで索引検索して選定
rdate=df_result.loc[1,"RaceDate"]
datestr=rdate.split("年")
yeari=int(datestr[0])
datestr1=(datestr[1]).split("月")
monthi=int(datestr1[0])
datestr2=(datestr1[1]).split("日")
dayi=int(datestr2[0])
#print(yeari,"/",monthi,"/",dayi)
result_days=period_71(dayi,monthi,yeari)#7月１日から通算日数計算
#fis dateと比較
start_days=360
for i in range(1,len(df_fispage)):
fis_start=df_fispage.loc[i,"start_date"]
fis_sdate=fis_start.split("-")
fis_sday=int(fis_sdate[0])
fis_smonth=int(fis_sdate[1])
fis_syear=int(fis_sdate[2])
start_days_1=start_days
start_days=period_71(fis_sday,fis_smonth,fis_syear) #7/1を起点とした日付単位に変換
fis_last=df_fispage.loc[i,"last_date"]
fis_ldate=fis_last.split("-")
fis_lday=int(fis_ldate[0])
fis_lmonth=int(fis_ldate[1])
fis_lyear=int(fis_ldate[2])
last_days=period_71(fis_lday,fis_lmonth,fis_lyear) #7/1を起点とした日付単位に変換
#perios71同士で比較
if start_days<result_days and start_days_1>result_days:#
#print("Hit result_days")
#print(df_fispage.loc[i,"start_date"],"-",rdate,"-",df_fispage.loc[i-1,"start_date"])
target_filename=df_fispage.loc[i,"filename"]
#print("Hit fis-points-list=",target_filename)
break
#target file 読み込み
path=DRIVE+'\FIS\FIS-points-list-AL/'
pfilename=path+target_filename
#print(pfilename)
df_points = pd.read_csv( pfilename, low_memory=True)#fis-points-listファイル読み込み
#print(df_points)
#競技情報
#resultでfiscodeが無い人を名前で検索して、fiscode超出
list_noname=[]
list_nonameidx=[]
for i in range(0,len(df_result)):
#print(i,":FIS_id=",df_result.loc[i,"FIS_id"])
if df_result.loc[i,"FIS_id"]=='0':
no_name=df_result.loc[i,"Name"]
#print(i,no_name)
list_noname.append(no_name)
list_nonameidx.append(i)
#print("no_name=",list_noname)
name_index=[]
fiscodei=[]
for i in range(0,len(list_noname)):#df_pointsからLasenameとFirstnameが一致する行を検出してdf_result修正--
names=list_noname[i].split(" ")
for j in range(0,len(df_points)):
#if(df_points.loc[j,"Lastname"]==names[0] and df_points.loc[j,"Firstname"]==names[1]):
if(df_points.loc[j,"Lastname"] in list_noname[i] and df_points.loc[j,"Firstname"] in list_noname[i]):
name_index.append(list_nonameidx[i])
fiscodei.append(df_points.loc[j,"Fiscode"])
#print("name_index=",name_index)
#print("fiscodei=",fiscodei)
df_result=addFIS_id(df_result)
for i in range(0,len(name_index)):
df_result.loc[name_index[i],"FIS_id"]=fiscodei[i]
#print("Repaired_Noname:",name_index[i],df_result.loc[name_index[i],"FIS_id"],df_result.loc[name_index[i],"Name"])
#最終チェック
#for i in range(0,len(df_result)):
#if (df_result.loc[i,"FIS_id"]=="0") or (df_result.loc[i,"FIS_id"]==""):
#print("Not Repaired:",i,df_result.loc[i,"Name"],df_result.loc[i,"FIS_id"])
# index DEBUG
fname1=DRIVE+'\FIS\id_RepairedNoname_'+bn
#print(fname1)
df_result.to_csv(fname1, index=False,encoding="utf-8_sig")
#内包化-------------------------
fiscoderow=[]
df_result["FIS_id"]=df_result['FIS_id'].astype('int')
target_col=df_result["FIS_id"]
des_col=df_points["Fiscode"]
#print(target_col)
#print("Fiscode=",des_col)
#NL 検索
df_fitfis0=df_points.query('Fiscode in @target_col')
df_fitfis=df_fitfis0.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_fitfis.reset_index(inplace=True, drop=True)
#print(df_fitfis)
#BL　検索
#print("df_base=",df_base)
df_fitBL0=df_base.query('Fiscode in @target_col')
df_fitBL=df_fitBL0.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_fitBL.reset_index(inplace=True, drop=True)
df_fitBL= df_fitBL.add_suffix('_B')
#print("df_fit_BL=",df_fitBL)
#resltcol=["Diff","SAJ_id","FIS_id"]
#fispcol=["DHpoints","DHpos","DHSta","SLpoints","SLpos","SLSta","GSpoints","GSpos","GSSta","SGpoints","SGpos","SGSta","ACpoints","ACpos","ACSta"]
#basepcol=["DHpoints_B","DHpos_B","DHSta_B","SLpoints_B","SLpos_B","SLSta_B","GSpoints_B","GSpos_B","GSSta_B","SGpoints_B","SGpos_B","SGSta_B","ACpoints_B","ACpos_B","ACSta_B"]
#df_result[resltcol]=.apply(pd.to_numeric)
#df_result[resltcol].astype("float")
#df_fitfis[fispcol].astype("float")
#df_fitBL[basepcol].astype("float")
#print()
#df_result順にdf_fitfisをソート
fiscol=df_fitfis.columns.values
#print(fiscol)
colN=df_fitBL.shape[1]
l_fitBL0=df_fitBL.loc[0,:]
l_fitBL0=["0"]*colN
#print("l_fitBL0=",l_fitBL0)
#print(colN,"df_BL.shape=",df_fitBL.shape,"l0_fitBL=",l_fitBL0)
# index DEBUG
#fname1='E:\FIS\FitBLError_'+bn
#print(fname1)
#df_fitBL.to_csv(fname1, index=False,encoding="utf-8_sig")
#print("l_fitBL0=",l_fitBL0)
list_fitfis=[]
list_fitBL=[]
list_result3=[]
for i in range(0,len(df_result)):
#l_result=df_result.loc[i,:]
#print("Result:",i,j,l_result)
for j in range(0,len(df_fitfis)):
if df_fitfis.loc[j,"Fiscode"] ==df_result.loc[i,"FIS_id"]:
l_fitfis=df_fitfis.loc[j,:]
list_fitfis.append(l_fitfis)
#print("NL:",i,j,l_fitfis)
break
#BL
hitflag=0
for k in range(0,len(df_fitBL)):
if df_fitBL.loc[k,"Fiscode_B"] ==df_result.loc[i,"FIS_id"]:
l_fitBL=df_fitBL.loc[k,:]
list_fitBL.append(l_fitBL)
#print("BL:",i,k,l_fitBL)
hitflag=1
break
if hitflag==0:
l_fitBL=l_fitBL0
#list_fitBL.append(l_fitBL0)
#print("BL:",i,k,l_fitBL)
#print()
df_fitfisS=pd.DataFrame(list_fitfis)
df_fitfisS.reset_index(inplace=True, drop=True)
#print(df_fitfisS)
df_fitBLS=pd.DataFrame(list_fitBL)
df_fitBLS.reset_index(inplace=True, drop=True)
#print(df_fitBLS.shape,df_fitBLS.columns.values)
#============BL 不足穴埋め========================
# index DEBUG
fname1=DRIVE+'\FIS\FitFispoints_'+bn
#print(fname1)
df_fitfisS.to_csv(fname1, index=False,encoding="utf-8_sig")
#print()
# index DEBUG
fname1=DRIVE+'\FIS\FitBLpoints_'+bn
#print(fname1)
df_fitBLS.to_csv(fname1, index=False,encoding="utf-8_sig")
#print()
#=================必要なポイントデータだけresultに加える=========================================
#print("df_result:",df_result.shape)
#print("df_fitfisSorted:",df_fitfisS.shape)
#print("df_fitBLSorted:",df_fitBLS.shape)
#print("df_fitfisSorted=",df_fitfisS)
df_result.loc[:,"Firstname"]=df_fitfisS.loc[:,"Firstname"]
df_result.loc[:,"Lastname"]=df_fitfisS.loc[:,"Lastname"]
df_result.loc[:,"Fiscode"]=df_fitfisS.loc[:,"Fiscode"]
df_result.loc[:,poicol]=df_fitfisS.loc[:,poicol]
#BLをresultへコピー　抜け行を飛ばす
for i in range(0,len(df_result)):
for j in range(0,len(df_fitBLS)):
if df_result.loc[i,"FIS_id"]==df_fitBLS.loc[j,"Fiscode_B"]:
df_result.loc[i,"Fiscode_B"]=df_fitBLS.loc[j,"Fiscode_B"]
df_result.loc[i,poicol+"_B"]=df_fitBLS.loc[j,poicol+"_B"]
break
df_result=df_result.fillna('-')#Nanを"-"で埋める
df_result=df_result.replace(['NaN', 'None', ''], 'NaN')
#棄権選手のデータ初期化
if i in range(0,len(df_result)):
#print("DNF DNS DSQ delete")
if df_result.loc[i,"Total"].isnumeric()==False:
df_result.loc[i,"Total"]='D'
#race dateをファイル名末尾に追加
#rdatestr=df_result.loc[1,"RaceDate"]
#rdate = re.sub(r'\D', '', rdatestr)
#print("rdate=",rdate)
#fname1='E:\FIS\points'+rdate+'_result_'+bn
fname1=DRIVE+'\FIS\points+_df_result_'+bn
#print(fname1)
df_result.to_csv(fname1, index=False,encoding="utf-8_sig")
return fname1
print()
def ckey(pf):#Claculation points-File
print("-----------CALCULATION:Select points_FILE in FIS dir---------------")
#df_cal,bn=File_selreadFIS()#FIS dirからpointsファイルを指定してdfで返す
filename=pf
df_cal = pd.read_csv(filename, low_memory=True)
df_cal=df_cal.reset_index()
# filenameからbasenameをとりだしてファイル種類区別
#print(">>>>>>READ FILE;",filename)
bn= os.path.basename(filename)
#print("pointsFile read to df_cal:",df_cal.shape)
#0:種目を検索 "RaceTitel"か"Women-Men"のいずれかに単語があるか検索する
#shumoku=""
if "DH" in poicol:
#shumoku="DH"
fv=1250
pmax=330
#poicol="DHpoints"
#poscol="DHpos"
#poicol_B="DHpoints_B"
#poscol_B="DHpos_B"
elif "SL" in poicol:
#shumoku="SL"
fv=730
pmax=165
#poicol="SLpoints"
#poscol="SLpos"
#poicol_B="SLpoints_B"
#poscol_B="SLpos_B"
elif "GS" in poicol:
#shumoku="GS"
fv=1010
pmax=220
#poicol="GSpoints"
#poscol="GSpos"
#poicol_B="GSpoints_B"
#poscol_B="GSpos_B"
elif "SG" in poicol:
#shumoku="SG"
fv=1190
pmax=270
#poicol="SGpoints"
#poscol="SGpos"
#poicol_B="SGpoints_B"
#poscol_B="SGpos_B"
elif "AC" in poicol:
#shumoku="AC"
fv=1360
pmax=270
#poicol="ACpoints"
#poscol="ACpos"
#poicol_B="ACpoints_B"
#poscol_B="ACpos_B"
else:
print("ERORR No race shumoku ")
#print("This Race is ",shumoku)
#if poicol=="":
#input("=================poicol null ERROR====================")
#1:レースポイント計算-----------------------------------------------------------
T0to=totalsec(df_cal.loc[0,"Total"])#精度悪いのでRun 1 と　Run2の和からトータルを出す
trun0_1=run1sec(df_cal.loc[0,"Run 1"])
trun0_2=run1sec(df_cal.loc[0,"Run 2"])
T0=trun0_1+trun0_2
#Run1 Run2を足してTotalPを生成して計算する
for i in range(0,len(df_cal)):
Txto=totalsec(df_cal.loc[i,"Total"])
trun1=run1sec(df_cal.loc[i,"Run 1"])
trun2=run1sec(df_cal.loc[i,"Run 2"])
Tx=trun1+trun2
df_cal.loc[i,"Tx12"]=Tx
df_cal.loc[i,"RP"]=(Tx/T0-1)*fv
#df_cal.loc[i,"RPto"]=(Txto/T0to-1)*fv
#print(i,"RP=",df_cal.loc[i,"RP"])
#print(df_cal.dtypes)
fname1=DRIVE+'\FIS\cal_RP_'+bn
#print(fname1)
df_cal.to_csv(fname1, index=False,encoding="utf-8_sig")
#2:ペナルティポイント計算B（df_B) スタート前当該競技のFISポイントをソートして上位５人の和
df_B=df_cal.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_B[poicol] = pd.to_numeric(df_B[poicol], errors='coerce')
#print("PREsort_df_B=",df_B)
df_B.sort_values(poicol,inplace=True)
df_B=df_B.reset_index(drop=True)
#print("AfterSort_df_B=",df_B)
fname1=DRIVE+'\FIS\sorted_df_B_'+bn
#print(fname1)
df_B.to_csv(fname1, index=False,encoding="utf-8_sig")
df_B[poicol] = pd.to_numeric(df_B[poicol], errors='coerce')
#print("poicol type=",df_B[poicol].dtypes)
#SORTした最上部はDNFのゼロ点の人がいるので、ポイントゼロを省いて和算
n=0
rankn=1
poiBsum=0
while (rankn<=5):# or (n<len(df_B)-1):
#print("df_B[",n,",",poicol,"]=",df_B.loc[n,poicol])
if df_B.loc[n,poicol]!=0 :
tdata=float(df_B.loc[n,poicol])
#print("tdata:",tdata,type(tdata))
poiBsum=poiBsum+tdata
df_B.loc[rankn,"rankB"]=rankn
df_B.loc[rankn,"Bname"]=df_B.loc[n,"Name"]
df_B.loc[rankn,"poiB"]=df_B.loc[n,poicol]
df_cal.loc[rankn,"rankB"]=rankn
df_cal.loc[rankn,"Bname"]=df_B.loc[n,"Name"]
df_cal.loc[rankn,"poiB"]=df_B.loc[n,poicol]
#print("n=",n,"rankn=",rankn,df_cal.loc[rankn,"rankB"],df_cal.loc[rankn,"Bname"],df_cal.loc[rankn,"poiB"])
rankn+=1
n+=1
df_B.loc[0,"Bname"]="poiBsum"
df_B.loc[0,"poiB"]=poiBsum
df_B.loc[0,"rankB"]=0
df_cal.loc[0,"Bname"]="Total_B="
df_cal.loc[0,"poiB"]=poiBsum
df_cal.loc[0,"rankB"]="df_B-Rank1-5"
#print(n,"point_B=",df_cal.loc[0,"point_B"])
# Calculation DEBUG
fname1=DRIVE+'\FIS\cal_df_B_'+bn
#print(fname1)
df_B.to_csv(fname1, index=False,encoding="utf-8_sig")
# Calculation DEBUG
#fname2='E:\FIS\cal_df_cal_'+bn
#print(fname2)
#df_cal.to_csv(fname2, index=False,encoding="utf-8_sig")
#3:ペナルティポイント計算C（df_C）完走した時間"Total"をSORTして１０位以内のFISポイント上位５人の和
#df_C=df_cal.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
#10位以内が対象なので、１０位までをコピー
#df_C=df_cal[df_cal["Rank"]<11]
df_C=df_cal.iloc[0:10,:]
#print("df_C=",df_C)
df_Cs=df_C.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_Cs.sort_values(poicol,inplace=True)#Raceのrankでソート
df_Cs=df_Cs.reset_index(drop=True)
#print("df_Cs=",df_Cs)
n=0
rankn=1
poiCsum=0
while rankn<=5:
#print("df_Cs[",n,",'RP']=",df_Cs.loc[n,"RP"])
df_Cs[poicol] = pd.to_numeric(df_Cs[poicol], errors='coerce')
if df_Cs.loc[n,poicol]!=0 :
poiCsum=poiCsum+df_Cs.loc[n,'RP']
df_Cs.loc[rankn,"rankC"]=rankn
df_Cs.loc[rankn,"Cname"]=df_Cs.loc[n,"Name"]
df_Cs.loc[rankn,"poiC"]=df_Cs.loc[n,'RP']
df_cal.loc[rankn,"rankC"]=rankn
df_cal.loc[rankn,"Cname"]=df_Cs.loc[n,"Name"]
df_cal.loc[rankn,"poiC"]=df_Cs.loc[n,'RP']
#print("n=",n,"rankn=",rankn,df_cal.loc[rankn,"rankC"],df_cal.loc[rankn,"Cname"],df_cal.loc[rankn,"poiC"])
rankn+=1
n+=1
df_Cs.loc[0,"Cname"]="poiCsum"
df_Cs.loc[0,"poiC"]=poiCsum
df_Cs.loc[0,"rankC"]=0
df_Cs.loc["Bname"]=0
df_Cs.loc["poiB"]=0
df_Cs.loc["rankB"]=0
df_cal.loc[0,"Cname"]="Total_C="
df_cal.loc[0,"poiC"]=poiCsum
df_cal.loc[0,"rankC"]="df_Cs-Rank1-5"
#print("Point_C:",df_cal.loc[0,"rankC"],df_cal.loc[0,"Cname"],df_cal.loc[0,"poiC"])
#max値で判定
if poiCsum>pmax:#max値より大きかったらmax値に置き換え
df_Cs.loc[0,"poiC"]=pmax
df_cal.loc[0,"poiC"]=pmax
fname1=DRIVE+'\FIS\cal_df_Cs_'+bn
#print(fname1)
df_Cs.to_csv(fname1, index=False,encoding="utf-8_sig")
#4:ペナルティポイント計算A（df_A）
#Totalの10位以内の５人のFISポイント上位を合計する
df_A=df_cal.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
#df_A=df_cal[df_cal["Rank"]<11]
#print("df_A=",df_A)
#df_As=df_A.copy(deep=True)#df_calをcopy複製しないと=では、参照渡しなのでそれぞれの変更が反映されてしまう。
df_A["Totalsec"]=df_A["Total"].apply(run1sec)
df_A.sort_values("Totalsec",inplace=True)#RaceのTotalsecでソート
df_A=df_A.reset_index(drop=True)
# Calculation DEBUG
fname2=DRIVE+'\FIS\cal_df_A_'+bn
#print(fname2)
df_A.to_csv(fname2, index=False,encoding="utf-8_sig")
df_As=df_A.iloc[0:10,:]#Run 1の１０位まで切り抜き
df_As.sort_values(poicol,inplace=True)#RaceのRun1でソートRun1の１０位中
df_As=df_As.reset_index(drop=True)
#print("df_As=",df_As)
#fname1='E:\FIS\cal_df_As_'+bn
#print(fname1)
#df_As.to_csv(fname1, index=False,encoding="utf-8_sig")
poiAsum=0
rankn=1
df_As[poicol] = pd.to_numeric(df_As[poicol], errors='coerce')
for i in range(0,5):#5位まで切り取り
#if df_As.loc[i,poicol]!=0 :
poiAsum=poiAsum+df_As.loc[i,poicol]
df_As.loc[i+1,"rankA"]=rankn
df_As.loc[i+1,"Aname"]=df_As.loc[i,"Name"]
df_As.loc[i+1,"poiA"]=df_As.loc[i,poicol]
df_cal.loc[i+1,"rankA"]=rankn
df_cal.loc[i+1,"Aname"]=df_As.loc[i,"Name"]
df_cal.loc[i+1,"poiA"]=df_As.loc[i,poicol]
rankn+=1
df_As.loc[0,"rankA"]=0
df_As.loc[0,"Aname"]="poiAsum"
df_As.loc[0,"poiA"]=poiAsum
df_cal.loc[0,"rankA"]="df_As-Rank1-5"
df_cal.loc[0,"Aname"]="Total_A="
df_cal.loc[0,"poiA"]=poiAsum
# Calculation DEBUG
fname2=DRIVE+'\FIS\cal_df_As_'+bn
#print(fname2)
df_As.to_csv(fname2, index=False,encoding="utf-8_sig")
# Calculation DEBUG
#fname3='E:\FIS\cal_df_cal_'+bn
#print(fname3)
#df_cal.to_csv(fname3, index=False,encoding="utf-8_sig")
#max値で判定
df_Cs.loc[0,"pmax"]=pmax
df_cal.loc[0,"pmax"]=pmax
if poiAsum>pmax:#max値より大きかったらmax値に置き換え
df_Cs.loc[0,"poiA"]=pmax
df_cal.loc[0,"poiA"]=pmax
#===================PENALTY POINT==========================================
penaltypsum=poiAsum+poiBsum-poiCsum
penaltyp10=penaltypsum/10
penaltyp=round(penaltyp10,2)
if penaltyp<23:
df_cal.loc[3,"PenalyPoint"]=penaltyp#calculated penalty point
print("Penalty minmum 23,Calculated penaltypoint=",penaltyp)
penaltyp=23# fis minimum rule 23 point
df_cal.loc[0,"PenalyPoint"]=penaltyp
print("point_A=",poiAsum)
print("point_B=",poiBsum)
print("point_C=",poiCsum)
print("penaltypsum=",penaltypsum)
print("PenaltyPoint=",penaltyp)
#===================Result FIS point==========================================
for i in range(0,len(df_cal)):
df_cal.loc[i,"ResultName"]=df_cal.loc[i,"Name"]
df_cal.loc[i,"ResultPoint"]=round((df_cal.loc[i,"RP"]+penaltyp),2)
becol="Before_FIS_"+poicol
df_cal.loc[i,becol]=df_cal.loc[i,poicol]
#print(i,df_cal.loc[i,"Name"],"the Race FIS point=",df_cal.loc[i,"ResultPoint"])
# Calculation FINISH
fname2=DRIVE+'\FIS\CALpoints_df_cal_'+bn
#print(fname2)
df_cal.to_csv(fname2, index=False,encoding="utf-8_sig")
return fname2
def hkey(cf):
print("-------------------hkey-------------------------")
filename=cf
#filename = FileDialog.askopenfilename(parent=root, initialdir=iDir, filetypes=fTyp)
df = pd.read_csv(filename, low_memory=True)
colname=df.columns.to_list()
colfisp=[s for s in colname if 'Before_FIS_' in s]
#colname=df.columns()#[df.columns.str.contains('Before_FIS_')]
#print("colname=",colname,"colfisp=",colfisp)
basename = os.path.basename(filename)
#print(df.shape)
df_html=df[["Rank","Bib","Name","Club","Run 1","Run 2","Total","Diff","ResultPoint",colfisp[0]]]
#print(df_html)
#write_html(df_html, r'E:\FIS\fuga.html')
bnh=basename.strip(".csv")
bnhh=bnh.strip("CAL_df_cal_points_df_")
hname=DRIVE+"\FIS\Test_"+bnhh+".html"
#print(bnhh)
#df_html.to_html(hname)
#print()
html = df_html.to_html()
msg = bnhh #"custom mesagges"
title = """
<html>
<head>
<style>
thead {color: green;}
tbody {color: black;}
tfoot {color: red;}
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body>
<h4>
""" + msg + "</h4>"
end_html = """
</body>
</html>
"""
html = title + html + end_html
text_file = open(hname, "w",encoding="utf-8_sig")
text_file.write(html)
text_file.close()
#print()
return bnhh,hname
def ftp(hf):
ftp_host = 'issb.lsv.jp'#'ホスト名'リトルサーバーへ変更
ftp_user = 'issb@issb.lsv.jp'#'ユーザー名'
ftp_pwd = 'y65g4S58R9W4DaC'#'パスワード'
upload_f_path =hf# hf#r'アップロードするファイルのパス'
base_name = os.path.basename(upload_f_path)
print("ftp basename=",base_name)
with FTP(ftp_host,ftp_user,ftp_pwd) as ftp:
# ディレクトリ移動
ftp.cwd('pub/wordpress/')
# ファイルをアップロード
with open(upload_f_path, 'rb') as fp:
ftp.storbinary(f'STOR {base_name}', fp,rest=None)
ftp.quit()
sname="https://issb.lsv.jp/"+base_name
s = pyshorteners.Shortener()
shortname=s.tinyurl.short(sname)
print(shortname)
return shortname
#https://shinshu-makers-ski.qc-plus.jp/Test_+_df_result_df_SEIKO_2024324The%2026th%20Nukabira%20Gensenkyo%20Spring%20Cup%20GS%20Race_24M.html
def akey(url):
start = time.time() # 現在時刻（処理開始前）を取得
fs=skey(url)
#FIT SEIKO FILE
pf=fkey(fs)
print("FItted file=",pf)
#Calculation points File
cf=ckey(pf)
print("Calculation Finished=",cf)
end = time.time() # 現在時刻（処理完了後）を取得
print("HTML")
bname,haname=hkey(cf)
sname=ftp(haname)
webhook(haname)
#driver.get(sname)
time_diff = end - start
print("=============Elapsed Time =",time_diff,"============================")
return sname
def lkey():
text1="0"
text2="0"
list_msg=[]
list_msg=ListenSlack()
for i in range(0,len(list_msg)):
#print(i,":list_msg=",list_msg[i])
if "https://seikosportslink.com/" in list_msg[i]:
text = list_msg[i].lstrip("<")
text1=text.rstrip(">")
#DEL amp;
if "amp;" in text1:
text2=text1.replace("amp;",'')
#print("list_msg[",i,"]=:",text2)
break
else:
text2="0"
return text2
main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment