本文作者:張孟晗,中南財經政法大學統計與數學學院
本文編輯:趙一帆
技術總編:陳 鼎
為了感謝大家長久以來的支持和信任,爬蟲俱樂部為大家送福利啦!!!Stata&Python特惠課程雙雙上線騰訊課堂~爬蟲俱樂部推出了Python編程培訓課程、Stata基礎課程、Stata進階課程、Stata文本分析、正則表達式、網絡爬蟲、基本字符串課程。報名課程即可加入答疑群,對報名有任何疑問歡迎在公眾號後臺和騰訊課堂留言哦。我們在這篇推文的最後提供了每門課程的課程二維碼,大家有需要的話可以直接掃描二維碼查看課程詳情並進行購買哦~
首先,我們將excel中的數據導入mysql資料庫。大致可以分為兩步,先將excel中的數據轉換成DataFrame格式,然後再將DataFrame格式的數據導入到資料庫中。import pandas as pdfrom sqlalchemy import create_engine# 讀入數據df = pd.read_excel('F:\python\shuju.xlsx')# 建立驅動引擎zzz = create_engine('mysql+pymysql://root:121215zmh@localhost:3306/zzz?charset=utf8')# 將數據導入資料庫df.to_sql('datas',con = zzz, index= False,if_exists= 'replace')通過登錄Navicat查看,我們可以看到數據已成功導入到了資料庫中。如果我們想對導入的數據根據銷量對各品牌進行排序編號,想對銷量進行一階滯後,想取出各品牌最高銷量的那條記錄,想計算一階移動平均數,想……別想了,強大的窗口函數,一切問題輕鬆解決!MySQL從8.0版本開始支持窗口函數。窗口函數的作用類似於在查詢中對數據進行分組,不同的是,分組操作會把分組的結果聚合成一條記錄,而窗口函數是將結果置於每一條數據記錄中。窗口函數可以分為靜態窗口函數和動態窗口函數 。靜態窗口函數的窗口大小是固定的,不會因為記錄的不同而不同;動態窗口函數的窗口大小會隨著記錄的不同而變化。(Tips:計算移動平均值特別有用)窗口函數的語法為:函數 OVER([PARTITION BY 欄位名 ORDER BY 欄位名 ASC|DESC ROWS BETWEEN … AND …])● PARTITION BY子句:指定窗口函數按照哪些欄位進行分組。分組後,窗口函數可以在每個分組中分別執行。● ORDER BY子句:指定窗口函數按照哪些欄位進行排序。執行排序操作使窗口函數按照排序後的數據記錄的順序進行編號。● ROWS BETWEEN … AND …子句:通常用來作為滑動窗口使用,進行逐期遞移。窗口函數總體上可以分為序號函數、分布函數、前後函數、首尾函數和其他函數,具體函數及其說明如下表:import pymysqlconn=pymysql.connect(host='localhost',user='root',password='121215zmh',database='zzz',charset='utf8')下面我們挑幾個重要的窗口函數向大家演示其用法,幫助大家更好的理解窗口函數是如何對數據進行處理的。1.序號函數(1)ROW_NUMBER()
result = pd.read_sql("select *,row_number() over(partition by name order by number desc) as rownumber from datas;",con=conn)result結果可見最後增加了名為rownumber的一列,這一列是按照name類別進行排序,並且序號無間斷且不重複,我們可以看出apple第2季度銷量最高為1200,huawei第4季度銷量最高為1100。a = pd.read_sql("select *,rank() over(partition by name order by number desc) as ranks,dense_rank() over(partition by name order by number desc) as denserank from datas;",con=conn)a從上表的定義我們可以得出,根據RANK()排序後,huawei對應四行得出的結果順序應該為1,2,2,4,而根據DENSE_RANK()排序後,huawei對應四行得出的結果順序應該為1,2,2,3,與結果顯示一致。(3)python基礎語法實現
作為比較,我們演示一下用pandas裡的rank方法對銷量進行排序"""加一列排序序號的兩種方法"""df = pd.concat([df,df.groupby(by='name',as_index=False)['number'].rank(ascending=False,method='dense').rename(columns={'number': 'rank'})],axis=1) df = pd.concat([df,df.groupby(by='name',as_index=False)[['number']].apply(lambda x :x.rank(ascending=False,method='dense')).rename(columns={'number': 'rank'})],axis=1) def sort(df,column='rank'): return df.sort_values(by=column)df.groupby(by='name',group_keys=False).apply(sort)可以看到,使用pandas來進行分組排序比較複雜,首先得調用groupby函數進行分組,之後調用rank函數進行排序,由於是聚合之後的結果,只有rank一列,所以還要藉助concat函數將結果加在原列表的最後面,並定義一個函數對分組後的rank進行排序,才能達到我們所要的效果。兩者一對比,不難發現利用sql的窗口函數語句非常的方便快捷。result = pd.read_sql("select *,cume_dist() over(partition by name order by number) as cumedist from datas;",con=conn)resultCUME_DIST()函數主要用於查詢小於等於某個值的比例,我們用name分類後根據number升序排列,新加的cumedist一列展示了各類別中小於等於各number值的比例。此函數可以用於生成滯後或者提前n數據,從而可以鮮明的對比出不同時期的數量差異。result = pd.read_sql("select *,lag(number,1) over(partition by name) as lags from datas;",con=conn)result從結果上我們可以看到,新生成的lags列是number列滯後一期的結果,從中我們可以清楚地對比本季度與上一季度的銷量差異。其實累計函數就是以聚合函數作為窗口函數並配上order by子句,例如我們想計算各品牌手機本年內各季度的累計銷售額,用累計函數實現十分方便。reresult = pd.read_sql("select *,sum(number) over(partition by name order by quarter) cunsum from datas;",con=conn)result可以看到,新生成的a列為各各品牌從第一季度到第四季度的累計銷售額,apple的前半年銷售額為1700,全年的銷售額為2900。
動態窗口是窗口函數比較高級的用法,具體來說就是我們可以控制每次要聚合的列,這對於求移動平均值來說十分的方便。result = pd.read_sql("select *,avg(number) over(partition by name rows between 1 preceding and current row) as moveavg from datas;",con=conn)result我們以apple類為例進行講解,我們生成的moveavg列是本行所對應的number及其上一行結合起來的平均值,如850=(500+1200)/2。需要注意的是,由於第一行沒有上一行,所以第一行輸出的是500/1=500。大家有沒有覺得用窗口函數完成一些小任務十分的方便呢,不妨趕快動手去試一試吧!最後,我們為大家揭秘雪球網(https://xueqiu.com/)最新所展示的滬深證券和港股關注人數增長Top10。
騰訊課堂課程二維碼
對我們的推文累計打賞超過1000元,我們即可給您開具發票,發票類別為「諮詢費」。用心做事,不負您的支持!
微信公眾號「Stata and Python數據分析」分享實用的Stata、Python等軟體的數據處理知識,歡迎轉載、打賞。我們是由李春濤教授領導下的研究生及本科生組成的大數據處理和分析團隊。
武漢字符串數據科技有限公司一直為廣大用戶提供數據採集和分析的服務工作,如果您有這方面的需求,請發郵件到statatraining@163.com,或者直接聯繫我們的數據中臺總工程司海濤先生,電話:18203668525,wechat: super4ht。海濤先生曾長期在香港大學從事研究工作,現為知名985大學的博士生,爬蟲俱樂部網絡爬蟲技術和正則表達式的課程負責人。
此外,歡迎大家踴躍投稿,介紹一些關於Stata和Python的數據處理和分析技巧。
投稿郵箱:statatraining@163.com投稿要求:
1)必須原創,禁止抄襲;
2)必須準確,詳細,有例子,有截圖;
注意事項:
1)所有投稿都會經過本公眾號運營團隊成員的審核,審核通過才可錄用,一經錄用,會在該推文裡為作者署名,並有賞金分成。2)郵件請註明投稿,郵件名稱為「投稿+推文名稱」。
3)應廣大讀者要求,現開通有償問答服務,如果大家遇到有關數據處理、分析等問題,可以在公眾號中提出,只需支付少量賞金,我們會在後期的推文裡給予解答。