記錄
http://blog.sina.com.cn/s/blog_73b339390102yoio.html PE：市盈率 = 股價 / 每股盈利 PEG：(市盈率相對盈利增長比率/市盈增長比率) PEG=PE/（企業年盈利增長率*100） PB：市凈率=股價 / 每股凈資產 PS：市銷率=股價 / 每股收入=總市值 / 銷售收入 ROE：凈資產收益率＝報告期凈利潤／報告期末凈資產 EPS：每股盈余=盈余 / 流通在外股數 beta值：每股收益=期末凈利潤 / 期末總股本 # 投資收益率計算器 import math 年均投資收益率 = (pow(終值/本金, 1/年限) -1)*100 投資收益本息 = pow((1+預期年收益率),年限)*本金 投資目標年限 = math.log(終值/本金)/math.log(1+預期年收益率) 年化收益率 = ((終值-本金)/本金)/年限 或 利息*365/(本金*天數) 利息收益 = 本金*年化收益率*天數/365 單利終值 = 本金*(1+年利率*年限) 單利現值 = 終值/(1+年利率*年限) 復利終值 = 本金*((1+年利率)**年限) 復利現值 = 終值/(1+年利率)**年限 等額本金月供 =(貸款本金÷還款月數)+(貸款本金-已歸還本金累計額)×月利率 等額本息月供 = 本金*月利率*[(1+月利率)**(年限*12)]/[(1+月利率)**(年限*12)-1]
時間轉換
import time a = '2020-03-06 19:18:00' a1 = time.strptime(a,'%Y-%m-%d %H:%M:%S') #格式化str為time格式 print(time.strftime('%Y%m%d',a1)) #格式化time格式為str print(time.asctime(time.localtime(time.time()))) #格式化當前時間為 Thu Apr 7 10:29:13 2016 print (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())) # 格式化成2016-03-20 11:45:39形式 print (time.strftime("%a %b %d %H:%M:%S %Y", time.localtime())) # 格式化成Sat Mar 28 22:24:24 2016形式 a = "Sat Mar 28 22:24:24 2016" print (time.mktime(time.strptime(a,"%a %b %d %H:%M:%S %Y"))) # 將格式字串轉換為時間戳 import calendar calendar.month(2016, 1) #輸出2016年1月份的日歷 import pandas as pd pd.to_datetime('2016-03-20').strftime('%Y%m%d') #pandas 格式化str輸出 from datetime import datetime,timedelta datetime.today() # 回傳當前時間時分秒都為0 now.isoweekday() # 回傳的1-7代表周一--周日 now.weekday() # 回傳的0-6代表周一--到周日
datetime.strptime('20150101', "%Y%m%d") # 格式化字串成datetime格式 (pd.to_datetime('20200130')+timedelta(days=3)).strftime('%Y%m%d') #格式化后三天的日期 now = datetime.now()+timedelta(days=3) print(now.strftime('%Y-%m-%d')) #格式化當天后三天的日期
串列排序
import operator,json aa = [{"key": "780", "A": ["01", "03", "05", "07", "09"], "T": "1"}, {"key": "781", "A": ["01", "03", "05", "07", "09"], "T": "3"}, {"key": "782", "A": ["01", "03", "05", "07", "09"], "T": "9"}] print(json.dumps(aa,indent=2, ensure_ascii=False)) b = sorted(aa,key=operator.itemgetter('key')) # 串列或json資料排序 #雖說loads是轉回json 但是像這樣key是單引號不能直接轉 需要先dumps data =https://www.cnblogs.com/da-guo/p/"[{'a':1,'b':2,'c':3,'d':4,'e':5}]" json1 = json.dumps(data) print(json.loads(json1)) print(type(json1),json1) with open('222.txt','r') as f2: a = json.load(f2) json.dump(aa,open('111.txt','w'),indent=4) json.loads() #str轉json json.load() #讀取文本str格式轉json json.dumps() #輸出成字串 json.dump() #將json寫入文本 a = ''.join(str(i)+',' for i in df1['cod'].tolist())[:-1] #list轉換str
[i,v for i,v in enumerate(list)]
a = ['e', 'a', 'u', 'o', 'i'] a.sort() #升序 正序 a.sort(reverse=True) # 降序 逆序 不能存變數 a.sort(key= lambda x : x[1]) # 根據第二個字母排序 默認根據第一個字母排序 sorted(a) # 可存變數 保留原list 可傳引數 reverse = True 降序 ， reverse = False 升序（默認） sorted([[6,7,8],[2,3,4],[1,2,3]], key=lambda x: x[2]) #多維串列 根據元素排序 sorted(lis,key=lambda x:cod.index(x[0])) #多維串列 根據單維串列進行指定排序 lis為多維 cod是單串列 [[k,v] for k,v in dict(new).items() if k not in dict(B1).keys()] #二維串列轉化成dict，比較兩個串列i[0]的差集 ['別墅' if '別墅' in i else '車位' if '車位' in i else '高層' for i in a] #串列推導示例 [[i[0],i[2]] for i in old for v in new if i[0] == v[0] and i[2] != '0'] d = {'lily':25, 'wangjun':22, 'John':25, 'Mary':19} sorted_keys = sorted(d) # 對字典而言，默認是對keys進行排序 print(sorted_keys) sorted_keys1 = sorted(d, key=lambda x : x[1]) print(d_new2) d_new = sorted(d.items(), key=lambda x: x[1], reverse=True) # 根據年齡排序，回傳串列形式 print(d_new) d_new = dict(d_new) # 使用內置函式把嵌套串列轉換成字典 print(d_new) sorted_values = sorted(d.values(), key=lambda x:x, reverse=False) # 排序值 print(sorted_values) 輸出： ['John', 'Mary', 'lily', 'wangjun'] ['wangjun', 'Mary', 'lily', 'John'] [('lily', 25), ('John', 25), ('wangjun', 22), ('Mary', 19)] {'lily': 25, 'John': 25, 'wangjun': 22, 'Mary': 19} [19, 22, 25, 25] #互換dick的key和value d = {'lily':25, 'wangjun':22, 'John':25, 'Mary':19} d_new = {v:key for key,v in d.items()} print(d_new) 輸出：{25: 'John', 22: 'wangjun', 19: 'Mary'}
f.readlines()
編碼轉換
df.to_csv('abdata.csv', mode='a', encoding='utf_8_sig') # pandas匯出csv 要指定編碼 #python2 指定utf8 #coding:utf-8 import sys reload(sys) sys.setdefaultencoding("utf-8") f.write(unicode('%s-日期 成交:%s萬 成交額:%s億'%(i[0],i[1],i[2]),"utf-8")+ '\n') #py2寫入中文也有毛病要加unicode
pandas操作
from sqlalchemy import create_engine from datetime import datetime,timedelta import numpy as np import pandas as pd import tushare as ts import matplotlib.pyplot as plt from matplotlib import colors from pylab import mpl #正常顯示畫圖時出現的中文和符號 import time ts.set_token("123") pro = ts.pro_api() pd.set_option('display.unicode.ambiguous_as_wide', True) #設定中文列名對齊 pd.set_option('display.unicode.east_asian_width', True) #設定列名對齊 pd.set_option('display.max_rows',None) #顯示所有行 pd.set_option('display.max_columns',None) #顯示所有列 pd.set_option('expand_frame_repr', False) #設定不換行 pd.set_option('max_colwidth',100) #設定顯示最大字符 np.set_printoptions(suppress=True) # 非科學計數法 mpl.rcParams['font.sans-serif']=['SimHei'] mpl.rcParams['axes.unicode_minus']=False pd.options.mode.chained_assignment = None %matplotlib inline #jupyter畫圖用
df['aa'].astype('float') #轉換整列格式 df.reset_index(drop=True) #重置index索引 并洗掉原索引 dfs.drop_duplicates() #去除完全相同的行保留第一行 .loc['a1']#根據index獲取行 .iloc[0].name #根據行號獲取行的某個值 aa.loc[:,'比_1':]獲取所有行和指定列 # loc和iloc 可以更換單行、單列、多行、多列的值 df1.loc[0,'age']=25 # 思路：先用loc找到要更改的值，再用賦值（=）的方法實作更換值 df1.iloc[0,2]=25 # iloc：用索引位置來查找
.drop_duplicates().sort_values(by=['trade_date']) #洗掉重復值并排序
# at 、iat只能更換單個值 df1.at[0,'age']=25 # iat 用來取某個單值,引數只能用數字索引 df1.iat[0,2]=25 # at 用來取某個單值,引數只能用index和columns索引名稱 df.columns = ['c','b'] #修改索引名 df.rename(columns={'a':'c'},inplace=True) #修改索引名 #pivot()和unstack()實作行轉列 dfcod = counts[['cod','key','日期1','日期2']].set_index(['key','日期1','日期2','cod']).unstack() df1 , df2 = df[['日期1','日期2','key']] , df.pivot('日期2','cod',v) #行轉列 列轉行參考 https://www.cnblogs.com/leohahah/p/9778304.html #新增一行 用append 但必須要得先創建DataFrame才行 df1=df1.append(new,ignore_index=True) # ignore_index=True,表示不按原來的索引，從0開始自動遞增 #新增一列 tabsdetail['SH'] = sh.append([sh,sh,sh]).tolist() #sh是Series tabs.insert(0, '總金額', [m,m*2,m*3,m*4],allow_duplicates=True) #指定位置添加一列 np.array(df0[['name','key']]).tolist() #dataframe轉化list dfdata = pd.DataFrame() dfdata = dfdata.append(df1,ignore_index=True) #pandas append必須存入變數 否則不生效 pd.DataFrame([[1,2,3],[1,2,3]]，columns=['a','b','c'],index=df0.index） #創建dataframe df0 = pd.DataFrame({'id':[3,4,5,6,7,3,4,5], 'name':[10.54,11.11,12.80,10.05,11.21,10.98,11.12,10.55]}, index=('a1','a2','a3','a4','a5','a6','a7','a8')) df0.loc[df0['id'] == 3 ,'key'] = 1 df0.loc[df0['id'] == 5 ,'key'] = 0 # 進行布林值判斷 輸出符合條件 df0['key'] = np.where(df0['id'] == 3 ,1,0) pd.concat([df0, df1], axis=1) #合并兩個dataframe df.index=pd.to_datetime(df.date) # 將index 改為時間 df=df.sort_index() #排序index df['ret']=df.close/df.close.shift(1)-1 # .shift(1) 獲取下一個 .shift(-1) 獲取上一個 data.sort_values(by=['標記','時間'],ascending=[False,True]) #多列排序指定升降序 df['當天'].fillna(method='ffill',inplace=True) #根據一列nan值填充上一個不為nan的值 df['a'] = (df_new.ret+1.0).cumprod() #計算當前值并累計連乘 .cumsum()累積連加 df1['ret'].diff() # 比較上一值與當前值的差 [i for i in df["close"].rolling(k).mean()] # 移動視窗list的均值 df['c'].rolling(window=10, min_periods=1, center=False).mean() #Series中計算均值 #dataframe行轉列 - 只能根據相同列名不同行名資料轉置 適合matplotlib用 單index日期畫圖 比如多個日期 每個日期中需要轉置的行名不得重復 df1 = df[['cod','盈虧','日期2']].pivot('日期2','cod','盈虧').rename_axis([None], axis=1) # pivot 指定列名 行名 資料 只能固定這三個引數 df1 = df1.rename_axis(None, axis=1).reset_index() # 取消第一個columns 將其拍平 df1.index=pd.to_datetime(df1.日期2) #dataframe行轉列 - 整合統計用 可以根據多個指定的index 但是set_index必須是前面串列-1的列 不然會亂 前面串列剩下的一個元素就是資料其他為index dfcod = counts[['cod','key','盈虧','日期2','日期1']].set_index(['key','日期1','日期2','cod']).unstack() dfcod.columns = [s1 +'_'+ str(s2) for (s1,s2) in dfcod.columns.tolist()] # 將其拍平 # dfcod.reset_index(inplace=True) # 重置index 轉成正常的dataframe dfcod.loc[['前10']] # 根據key分組顯示index和資料 dfcod a1.index = a1.index.droplevel() #洗掉一個多索引的index-names # series 根據list 判斷是否存在 df0[df0['id'].isin([3,4])] #根據list獲取串列資訊 df0[~df0['id'].isin([3,4])] #根據list獲取串列資訊 取反 # series 根據list 排序 df['words'] = df['words'].astype('category') #必須轉換成這個格式 df['words'].cat.reorder_categories([1,2,3], inplace=True) # list長度相等用這個 df['words'].cat.set_categories([1,2,3], inplace=True) # list多 用這個 df['words'].cat.set_categories([1,2,3], inplace=True) # list少 用這個 df.sort_values('words', inplace=True) #pandas 讀寫mysql from sqlalchemy import create_engine mysq = create_engine('mysql+pymysql://root:mysql.123@localhost/abdata?charset=utf8') df.to_sql('coun',mysq,if_exists='append',index=False) # 追加資料 df.to_sql('counts',mysq,if_exists='replace',index=False) #洗掉并寫入表 df = pd.read_sql_query('select * from cod1',mysq) # 查詢mysql表 #pymysql讀寫mysql import pymysql conn = pymysql.connect('127.0.0.1', 'root', 'mysql.123', 'data',charset='utf8') cur = conn.cursor() sql1 = "SELECT * from (SELECT * from data1 ORDER BY id DESC LIMIT %s ) aa order by id" %sum cur.execute(sql1) c1 = cur.fetchall() #讀取mysql conn.commit() #寫入mysql cur.close() conn.close()
歸納整理了一些作業中常用到的pandas使用技巧，方便更高效地實作資料分析，
1.計算變數缺失率 df=pd.read_csv('titanic_train.csv') def missing_cal(df): """ df :資料集 return：每個變數的缺失率 """ missing_series = df.isnull().sum()/df.shape[0] missing_df = pd.DataFrame(missing_series).reset_index() missing_df = missing_df.rename(columns={'index':'col', 0:'missing_pct'}) missing_df = missing_df.sort_values('missing_pct',ascending=False).reset_index(drop=True) return missing_df missing_cal(df) 如果需要計算樣本的缺失率分布，只要加上引數axis=1.
2.獲取分組里最大值所在的行方法 分為分組中有重復值和無重復值兩種，無重復值的情況， df = pd.DataFrame({'Sp':['a','b','c','d','e','f'], 'Mt':['s1', 's1', 's2','s2','s2','s3'], 'Value':[1,2,3,4,5,6], 'Count':[3,2,5,10,10,6]}) df df.iloc[df.groupby(['Mt']).apply(lambda x: x['Count'].idxmax())] 先按Mt列進行分組，然后對分組之后的資料框使用idxmax函式取出Count最大值所在的列，再用iloc位置索引將行取出，有重復值的情況 df["rank"] = df.groupby("ID")["score"].rank(method="min", ascending=False).astype(np.int64) df[df["rank"] == 1][["ID", "class"]] 對ID進行分組之后再對分數應用rank函式，分數相同的情況會賦予相同的排名，然后取出排名為1的資料，
3.多列合并為一行 df = pd.DataFrame({'id_part':['a','b','c','d'], 'pred':[0.1,0.2,0.3,0.4], 'pred_class':['women','man','cat','dog'], 'v_id':['d1','d2','d3','d1']}) df.groupby(['v_id']).agg({'pred_class': [', '.join],'pred': lambda x: list(x), 'id_part': 'first'}).reset_index()
4.洗掉包含特定字串所在的行 df = pd.DataFrame({'a':[1,2,3,4], 'b':['s1', 'exp_s2', 's3','exps4'], 'c':[5,6,7,8], 'd':[3,2,5,10]}) df[df['b'].str.contains('exp')]
5.組內排序 df = pd.DataFrame([['A',1],['A',3],['A',2],['B',5],['B',9]], columns = ['name','score']) 介紹兩種高效地組內排序的方法， df.sort_values(['name','score'], ascending = [True,False]) df.groupby('name').apply(lambda x: x.sort_values('score', ascending=False)).reset_index(drop=True)
6.選擇特定型別的列 drinks = pd.read_csv('data/drinks.csv') # 選擇所有數值型的列 drinks.select_dtypes(include=['number']).head() # 選擇所有字符型的列 drinks.select_dtypes(include=['object']).head() drinks.select_dtypes(include=['number','object','category','datetime']).head() # 用 exclude 關鍵字排除指定的資料型別 drinks.select_dtypes(exclude=['number']).head()
7.字串轉換為數值 df = pd.DataFrame({'列1':['1.1','2.2','3.3'], '列2':['4.4','5.5','6.6'], '列3':['7.7','8.8','-']}) df df.astype({'列1':'float','列2':'float'}).dtypes 用這種方式轉換第三列會出錯，因為這列里包含一個代表 0 的下劃線，pandas 無法自動判斷這個下劃線，為了解決這個問題，可以使用 to_numeric() 函式來處理第三列，讓 pandas 把任意無效輸入轉為 NaN， df = df.apply(pd.to_numeric, errors='coerce').fillna(0)
8.優化 DataFrame 對記憶體的占用 方法一：只讀取切實所需的列，使用usecols引數 cols = ['beer_servings','continent'] small_drinks = pd.read_csv('data/drinks.csv', usecols=cols) 方法二：把包含類別型資料的 object 列轉換為 Category 資料型別，通過指定 dtype 引數實作， dtypes ={'continent':'category'} smaller_drinks = pd.read_csv('data/drinks.csv',usecols=cols, dtype=dtypes)
9.根據最大的類別篩選 DataFrame movies = pd.read_csv('data/imdb_1000.csv') counts = movies.genre.value_counts() movies[movies.genre.isin(counts.nlargest(3).index)].head()
10.把字串分割為多列 df = pd.DataFrame({'姓名':['張 三','李 四','王 五'], '所在地':['北京-東城區','上海-黃浦區','廣州-白云區']}) df df.姓名.str.split(' ', expand=True)
11.把 Series 里的串列轉換為 DataFrame df = pd.DataFrame({'列1':['a','b','c'],'列2':[[10,20], [20,30], [30,40]]}) df df_new = df.列2.apply(pd.Series) pd.concat([df,df_new], axis='columns')
12.用多個函式聚合 orders = pd.read_csv('data/chipotle.tsv', sep='\t') orders.groupby('order_id').item_price.agg(['sum','count']).head()
13.分組聚合 import pandas as pd df = pd.DataFrame({'key1':['a', 'a', 'b', 'b', 'a'], 'key2':['one', 'two', 'one', 'two', 'one'], 'data1':np.random.randn(5), 'data2':np.random.randn(5)}) df for name, group in df.groupby('key1'): print(name) print(group) dict(list(df.groupby('key1')))
通過字典或Series進行分組 people = pd.DataFrame(np.random.randn(5, 5), columns=['a', 'b', 'c', 'd', 'e'], index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis']) mapping = {'a':'red', 'b':'red', 'c':'blue', 'd':'blue', 'e':'red', 'f':'orange'} by_column = people.groupby(mapping, axis=1) by_column.sum()
DataFrame樣式設定
def show(v): col = 'black' if v > 0 else 'green' return 'color:%s'%col def background_gradient(s, m, M, cmap='PuBu', low=0, high=0.8): rng = M - m norm = colors.Normalize(m - (rng * low),M + (rng * high)) normed = norm(s.values) c = [colors.rgb2hex(x) for x in plt.cm.get_cmap(cmap)(normed)] return ['" style="color: rgb(128, 0, 0);">' % color for color in c] def highlight_max(s,m): is_max = s == m return ['" style="color: rgb(128, 0, 0);">' if v else '' for v in is_max] tabs.style.applymap(show).background_gradient(cmap='Reds',axis = 1,low = 0,high = 1,subset = set1).\ apply(background_gradient,cmap='Purples',m=tabs[set2].min().min(),M=tabs[set2].max().max(),low=0,high=1,subset = set2).\ apply(highlight_max,m=tabs[set2].max().max()).background_gradient(cmap='Wistia',axis = 1,subset=['總金額']) accdeteil.style.applymap(show).background_gradient(cmap='Reds',axis = 1,low = 0,high = 1).\ background_gradient(cmap='Reds',axis = 1,low = 0,high = 1 ,subset=set2).\ background_gradient(cmap='Purples',axis = 1,low = 0,high = 1,subset = pd.IndexSlice['前10',:'9']).\ background_gradient(cmap='Purples',axis = 1,low = 0,high = 1,subset = pd.IndexSlice['前20',:'9']).\ background_gradient(cmap='Purples',axis = 1,low = 0,high = 1,subset = pd.IndexSlice['前05','1_':]).\ background_gradient(cmap='Purples',axis = 1,low = 0,high = 1,subset = pd.IndexSlice['前15','1_':]).\ background_gradient(cmap='GnBu',axis = 0,low = 0,high = 1 ,subset=['SH_']).\ apply(highlight_max,m=tabs[set2].max().max()) #可參考 https://blog.csdn.net/xiaodongxiexie/article/details/71202279 #顏色樣式 https://matplotlib.org/tutorials/colors/colormaps.html
pandas作圖
import matplotlib.pyplot as plt ax1 = df1[['策略凈值','指數凈值']].plot(figsize=(15,8)) #dataframe折線圖 ax1 = ax1.axhline(y=1,ls=":",c="r"),ax1.legend(loc = 'upper right') #標記0線和指定圖例位置 plt.title('策略簡單回測%s'%x,size=15) plt.xlabel('') for i in range(len(df1)): if df1['當天倉位'][i]==0 and df1['當天倉位'].shift(-1)[i]==1: plt.annotate('買',xy=(df1.index[i],df1.策略凈值[i]),arrowprops=dict(facecolor='r',shrink=0.05)) #標記買賣點 if df1['當天倉位'][i]==0 and df1['當天倉位'].shift(1)[i]==1: plt.annotate('賣',xy=(df1.index[i],df1.策略凈值[i]),arrowprops=dict(facecolor='g',shrink=0.1)) bbox = dict(boxstyle="round", fc="w", ec="0.5", alpha=0.9) #指定文字邊框樣式 t = f'累計收益率：策略{TA1}%，指數{TA2}%;\n年化收益率：策略{AR1}%，指數{AR2}%;'+\ f'\n最大回撤： 策略{MD1}%，指數{MD2}%;\n策略alpha: {round(alpha,2)}，策略beta：{round(beta,2)}; \n夏普比率： {S}' plt.text(df1.index[0], df1['指數凈值'].min(),text,size=13,bbox=bbox) #指定位置加文字框 ax=plt.gca() #設定圖形樣式 ax.spines['right'].set_color('none') ax.spines['top'].set_color('none') plt.show()
爬蟲
from bs4 import BeautifulSoup import requests headers = { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36' } htm = requests.get(url=url,headers=headers,timeout=30,stream=False).text soup = BeautifulSoup(htm, 'html.parser') txt = soup.find_all('div', class_='lax-s') #txt = soup.find('div', class_='qi').children #etree方式獲取 原文 https://mp.weixin.qq.com/s/c2Sg_LVTjOokePY2lxCGSA import requests import pandas as pd from pprint import pprint from lxml import etree import time import warnings warnings.filterwarnings("ignore") for i in range(1,15): print("正在爬取第" + str(i) + "頁的資料") url = "https://search.51job.com/list/000000,000000,0000,00,9,99,%25E6%2595%25B0%25E6%258D%25AE,2,"+str(i)+'.html?' headers = { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36' } web = requests.get(url, headers=headers) web.encoding = "gbk" dom = etree.HTML(web.text) #print(etree.tostring(dom, encoding="utf-8", pretty_print=True).decode("utf-8")) #列印整個html 不能直接print # 1、崗位名稱 job_name = dom.xpath('//div[@]/div[@]//p/span/a[@target="_blank"]/@title') # 2、公司名稱 company_name = dom.xpath('//div[@]/div[@]/span[@]/a[@target="_blank"]/@title') # 3、作業地點 address = dom.xpath('//div[@]/div[@]/span[@]/text()') # 4、工資：工資這一列有空值，為了保證資料框的一致性，采取以下方式進行資料的獲取 salary_mid = dom.xpath('//div[@]/div[@]/span[@]') salary = [i.text for i in salary_mid] #這里None也占一個元素 保持長度一致 # 5、發布日期 release_time = dom.xpath('//div[@]/div[@]/span[@]/text()') #----------------------------------------------------------------------------------------------# # 下面獲取二級網址的資訊，為了獲取二級網址的資訊，首先需要獲取二級網址的url # 6、獲取二級網址url deep_url = dom.xpath('//div[@]/div[@]//p/span/a[@target="_blank"]/@href') RandomAll = [] JobDescribe = [] CompanyType = [] CompanySize = [] Industry = [] for i in range(len(deep_url)): web_test = requests.get(deep_url[i], headers=headers) web_test.encoding = "gbk" dom_test = etree.HTML(web_test.text) # 7、爬取經驗、學歷資訊，先合在一個欄位里面，以后再做資料清洗，命名為random_all random_all = dom_test.xpath('//div[@]//div[@]/p[@]/text()') # 8、崗位描述性息 job_describe = dom_test.xpath('//div[@]//div[@]/p/text()') # 9、公司型別 company_type = dom_test.xpath('//div[@]//div[@]/p[1]/@title') # 10、公司規模(人數) company_size = dom_test.xpath('//div[@]//div[@]/p[2]/@title') # 11、所屬行業(公司) industry = dom_test.xpath('//div[@]//div[@]/p[3]/@title') # 將上述資訊保存到各自的串列中 RandomAll.append(random_all) JobDescribe.append(job_describe) CompanyType.append(company_type) CompanySize.append(company_size) Industry.append(industry) # 為了反爬，設定睡眠時間 time.sleep(1) # 由于我們需要爬取很多頁，為了防止最后一次性保存所有資料出現的錯誤，因此，我們每獲取一夜的資料，就進行一次資料存取， df = pd.DataFrame() df["崗位名稱"] = job_name df["公司名稱"] = company_name df["作業地點"] = address df["工資"] = salary df["發布日期"] = release_time df["經驗、學歷"] = RandomAll df["公司型別"] = CompanyType df["公司規模"] = CompanySize df["所屬行業"] = Industry df["崗位描述"] = JobDescribe # 這里在寫出程序中，有可能會寫入失敗，為了解決這個問題，我們使用例外處理， try: df.to_csv("job_info.csv", mode="a+", header=None, index=None, encoding="gbk") except: print("當頁資料寫入失敗") time.sleep(1) print("完畢")
OCR圖片識別
#需要安裝 tesseract-ocr（需要環境變數） 、chi_sim.traineddata 、 pytesseract-0.2.4 from PIL import Image import pytesseract,os,re png = r'D:\123\111.png' pytesseract.pytesseract.tesseract_cmd = r'C:\Program Files (x86)\Tesseract-OCR\tesseract.exe' img = Image.open(png) tim = os.stat(png).st_mtime img1 = img.size aa = pytesseract.image_to_string(img, lang='chi_sim') print(img1,tim) print(aa)
webdriver自動化測驗
#需要安裝 chromedriver-v69 、ChromeSetup_64_69.exe from selenium import webdriver from selenium.webdriver.common.keys import Keys try: driver = webdriver.Chrome() driver.get("http://user/login") time.sleep(1) driver.find_element_by_id('username').send_keys('123123') driver.find_element_by_id('password').send_keys('123123') driver.find_element_by_id('login').click() time.sleep(2) driver.find_element_by_xpath('//*[@id="header"]/div[7]/div/div[1]/ul/li[4]/a').click() time.sleep(2) driver.find_elements_by_class_name('content')[2].click() time.sleep(2) s1 = driver.find_element_by_class_name('i1').text s2 = s1[3:6] s3 = driver.find_element_by_id('pre-kanjiang').text s4 = driver.find_element_by_xpath('//*[@id="money"]/strong').text s5 = driver.find_element_by_xpath('//*[@id="money"]/em').text print('key=', s2, 'time=', s3, s5 + '=', s4) fs.write('key=' + s2 + '\n' + 'time=' + s3 + '\n' + s5 + '=' + s4 + '\n') time.sleep(2) if int(s2) == int(s.get('key')): elements = driver.find_elements_by_class_name('code') if 'A' in s.keys(): data_values = s.get('A') for i in data_values: a_button_index = int(i) - 1 elements[a_button_index].click() print('a_button_index = ', a_button_index) fs.write('a_button_index = ' + str(a_button_index) + '\n') if 'B' in s.keys(): data_values = s.get('B') for j in data_values: b_button_index = int(j) + 9 elements[b_button_index].click() print('b_button_index = ', b_button_index) fs.write('b_button_index = ' + str(b_button_index) + '\n') if 'C' in s.keys(): data_values = s.get('C') for k in data_values: c_button_index = int(k) + 19 elements[c_button_index].click() print('c_button_index = ', c_button_index) fs.write('c_button_index = ' + str(c_button_index) + '\n') time.sleep(1) driver.find_elements_by_name('danwei')[1].click() driver.find_element_by_class_name('txt').clear() driver.find_element_by_class_name('txt').send_keys(int(s.get('T')) * 1) driver.find_element_by_class_name('tztj-hover').click() time.sleep(2) driver.find_element_by_class_name('tz-true-hover').click() time.sleep(2) driver.find_element_by_xpath("/html/body/div[2]/div[3]/div/button[1]").send_keys(Keys.ENTER) time.sleep(2) driver.quit() except Exception as e: print(e)
cs客戶端自動化測驗
import os,sys,time import pywinauto import pywinauto.clipboard import pywinauto.application import win32clipboard as wincb import win32con def winmax(): #視窗最大化 if main_window.get_show_state() != 3: main_window.maximize() main_window.set_focus() def winmin(): #視窗最小化 if main_window.GetShowState() != 2: main_window.Minimize() def closepopup(): #關閉彈窗 popup_hwnd = main_window.PopupWindow() if popup_hwnd: popup_window = app.window_(handle=popup_hwnd) popup_window.SetFocus() popup_window.Button.Click() return True return False def pos(): #獲取持倉并重繪復制到剪切板 dialog_window.CCustomTabCtrl.ClickInput(coords=(30, 8)) #點擊持倉 dialog_window.Button5.click() time.sleep(0.5) dialog_window.Button5.click() # time.sleep(0.2) # dialog_window.CVirtualGridCtrl.RightClick(coords=(100, 70)) # 右擊持倉 # main_window.TypeKeys('C') #如果能復制了 就把這些打開 def copypos(): #獲取剪切板資訊 wincb.OpenClipboard() t = wincb.GetClipboardData(win32con.CF_TEXT) wincb.CloseClipboard() return t def copyposition(): #匯出持倉并讀取 dialog_window.CVirtualGridCtrl.RightClick(coords=(100, 70)) # 右擊持倉 main_window.TypeKeys('S') time.sleep(0.1) closepopup() closepopup() with open('C:/Users/Administrator/Desktop/table.xls','r') as f: return [[i.split('\t')[1],i.split('\t')[3],i.split('\t')[4]] for i in f.readlines()[1:]] def order(x): #B是買 S是賣 開始下單 dialog_window.TypeKeys("{F6}") if x == 'B': for i in Blis: # dialog_window.window(title_re='重填').click() time.sleep(0.1) dialog_window.Edit1.set_focus() dialog_window.Edit1.set_edit_text(i[0]) dialog_window.Edit3.set_edit_text(i[1]) time.sleep(0.2) dialog_window.Button1.click() if x == 'S': for i in Slis: time.sleep(0.1) dialog_window.Edit4.set_focus() dialog_window.Edit4.set_edit_text(i[0]) dialog_window.Edit6.set_edit_text(i[1]) time.sleep(0.2) dialog_window.Button2.click() def cancel(x): #撤單 B:撤買 S:撤賣 all:全撤 dialog_window.CCustomTabCtrl.ClickInput(coords=(140, 8)) #點擊委托 try: dialog_window.Button6.Click() time.sleep(0.1) dialog_window.Button6.Click() except Exception as e: pass if x == 'B': dialog_window.Button8.Click() if x == 'S': dialog_window.Button9.Click() if x == 'all': dialog_window.Button7.Click() time.sleep(0.1) closepopup() def BSlist(x): #回傳買賣剩余量 B是買 S是賣 global Blis global Slis pos() #可以復制了就打開old # old = [[i.split('\t')[1],i.split('\t')[3],i.split('\t')[4]] for i in copypos().decode("gb2312").split('\r\n')[1:]] old = copyposition() new = [[i[0],'0'] for i in Slis if int(i[1]) > 0 ]+Blis if x == 'B': B1 = [[v[0],str(int(i[1])-int(v[1]))] for i in [i for i in new if i[1] != '0'] for v in old if i[0] == v[0]] B2 = [[k,v] for k,v in dict([i for i in new if i[1] != '0']).items() if k not in dict(B1).keys()] Blis = [i for i in B1 if i[1] != '0']+B2 return Blis if x == 'S': Slis = [[i[0],i[2]] for i in old for v in [i for i in new if i[1] == '0'] if i[0] == v[0] and i[2] != '0'] return Slis if __name__ == '__main__': files = [i for i in os.listdir('D:/abdata/csv/') if 'cod' in i] Blis = [] Slis = [] with open('D:/abdata/csv/'+sorted(files)[-1],'r',encoding='utf-8') as f: for i in f: i = i.strip().split(',') if i[4] == '0' and int(i[2]) >0:Blis.append([i[0],i[2]]) if i[4] == '1' and int(i[2]) >0:Slis.append([i[0],i[2]]) ''' order(x): # 需要傳參 B是買 S是賣 cancel(x): # 撤單 B:撤買 S:撤賣 all:全撤 BSlist(x): # 回傳買賣剩余量 B是買 S是賣 winmax(): # 視窗最大化 winmin(): # 視窗最小化 pos(): # 獲取持倉并重繪復制到剪切板 copypos(): # 獲取剪切板資訊 closepopup(): #關閉彈窗 copyposition(): #匯出持倉并讀取 ''' title = '網上股票交易系統5.0' app = pywinauto.application.Application() app.connect(title=title) top_hwnd = pywinauto.findwindows.find_window(title=title) dialog_hwnd = pywinauto.findwindows.find_windows(top_level_only=False, class_name=u'#32770', parent=top_hwnd)[0] wanted_hwnds = pywinauto.findwindows.find_windows(top_level_only=False, parent=dialog_hwnd) main_window = app.window(handle=top_hwnd) dialog_window = app.window(handle=dialog_hwnd) winmax() #視窗最大 # pos() #獲取復制持倉 # old = [[i.split('\t')[1],i.split('\t')[3]] for i in copypos().decode("gb2312").split('\r\n')[1:]] # new = [[i[0],'0'] for i in Slis if int(i[1]) > 0 ]+Blis B = 1 S = 1 while S > 0 : closepopup() time.sleep(0.5) # pos() #獲取復制持倉 Slis = BSlist('S') S = len(Slis) if S > 0: closepopup() order('S') closepopup() time.sleep(2) cancel('all') time.sleep(2) while B > 0 : time.sleep(0.5) closepopup() # pos() #獲取復制持倉 Blis = BSlist('B') B = len(Blis) if B > 0: closepopup() order('B') closepopup() time.sleep(2) cancel('all') time.sleep(2)
vba合并所有作業表sheet為一個 Sub 合并當前作業簿下的所有作業表() Application.ScreenUpdating = False For j = 1 To Sheets.Count If Sheets(j).Name <> ActiveSheet.Name Then X = Range("A65536").End(xlUp).Row + 1 Sheets(j).UsedRange.Copy Cells(X, 1) '復制內容 End If Next Range("B1").Select '表明從B1單元格開始復制合并的內容 Application.ScreenUpdating = True MsgBox "當前作業簿下的全部作業表已經合并完畢！", vbInformation, "提示" End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/158888.html
標籤：Python
上一篇：05、MyBatis 快取