又到周末了，東哥文末贈送 5 本書《Python大數據分析從入門到精通》，規則見文末，歡迎留言參與~
最近有不少粉絲問我關於Python批量操作Excel的問題。
大家的關注點主要是如何循環遍歷表格、如何用Pandas批量處理，當然，還有在996的壓迫下如何提效（來擠出更多摸魚時間）。
為此，我特意肝了幾天，用基於實際業務的脫敏數據，以完整小項目的形式，來集中解決這些問題。
我的Pandas實戰系列老傳統，完整案例代碼和數據源，已經打包好放在文末。
不吹牛集團這幾年孵化了50個品牌，在各渠道做了大量品宣層面的曝光。現在集團首席吹牛官提了兩個需求：
1. 要一張大表，包含每個月搜索人數TOP5的品牌相關數據，以及對應品牌在當月的搜索份額和排名。
2. 在現有數據基礎上，找到最近一年投放效果還不錯的品牌，要吹吹牛，做年度表彰。
這是小z特別準備的兩個具有代表性的需求：
話音未落，集團首席吹牛官的跟屁蟲就把相關源數據丟過來了
一共24張Excel表格，按月存儲，涵蓋了從2019年1月到2020年12月的數據。
表格內部數據大同小異：
註：點擊人數和支付人數，都是在搜索人數基礎上統計的
每張表都有50個品牌，包括了品牌名、品牌搜索人數、點擊人數和對應的支付人數這幾個關鍵欄位。
源數據就是這樣簡簡單單中又透漏著麻麻煩煩，接下來，我們就開始手撕需求。
開始動手前，我們要明確需求。
再回顧一下首席吹牛官的第一個需求：要一張大表，包含每個月搜索人數TOP5品牌的相關數據，以及對應品牌在當月的搜索份額和排名。
提煉：在現有源數據的基礎上，我們還需要對各品牌月內按搜索人數排序，然後計算每個品牌搜索份額，取其前5，最後遍歷匯總。
自動化之哥曾經說過：Python批量操作Excel，無論表格再多，處理邏輯再複雜，只要我們集中力量擊破一張，就能夠實現批量操作的全面勝利。
首先，我們要解決的是單張表的問題。
按搜索人數排序：
調用rank快速給到對應的排名：
再來計算搜索份額，搜索份額的計算公式：單品牌搜索人數/所有品牌搜索人數匯總，用Pandas計算，怎一個easy了的！
正當我們準備批量執行操作，首席吹牛官發來了消息：
「需求一略有調整，投資人最關注的是凌雲這個品牌，要求在匯總表中，每個月凌雲品牌的相關指標排在最前面，後面跟著搜索排名TOP5的品牌」。
具體排名邏輯如圖所示：
面對需求的臨時改動，見過大風大浪的我們內心沒有一絲波瀾，甚至還有一點想笑。小事一樁，改改Pandas邏輯就好了。
先找到目標品牌凌云：
再按照順延的邏輯，定位TOP5品牌相關數據：
合併即可獲取我們想要的結果：
單表操作完成，批量操作，只需要建立好循環+合併關係，並引入日期列，在合併結果中對不同的表數據做好區分：
result = pd.DataFrame()
for name in os.listdir(): df = pd.read_excel(name) df = df.sort_values('品牌搜索人數',ascending = False) df['搜索人數排名'] = df['品牌搜索人數'].rank(ascending = False) df['搜索份額'] = df['品牌搜索人數'] / df['品牌搜索人數'].sum() brand = '凌雲' brand_data = df.loc[df['品牌'].str.find(brand) != -1,:] other = df.loc[df['品牌'].str.find(brand) == -1,:] other_top5 = other.iloc[:5,:] data = pd.concat([brand_data,other_top5]) data['日期'] = name[4:-5] result = pd.concat([result,data])Pandas批量操作，就是如此絲滑~
第一個需求搞定。
還記得那個明（che）確（dan）的需求二嗎？
「在現有數據基礎上，找到最近一年投放效果還不錯的品牌，要吹吹牛，做年度表彰。」
首席吹牛官以成本數據過於機密為由，除了說各品牌費用基本無差別之外，沒有透露任何關於成本方面的數據，我們自然也無法計算投放ROI了這些核心指標了。
目前能夠拿到的，只有品牌、搜索人數、點擊人數和對應支付人數這幾個指標。
要找到最近一年投放效果還不錯的品牌，我們可以用漏鬥思維，從量級（人數）和效率（轉化率）兩個角度來考慮：
在費用無差別的情況下：
在項目二場景中，三個指標越高越好。接下來，我們就結合搜索人數，搜索-點擊轉化率和點擊-支付轉化率，用Pandas做一波分析。
要對最近一年的數據做分析，我們先把2020年所有數據合併，拿到匯總表：
再按品牌的維度，做指標匯總：
計算對應的搜索-點擊轉化率，點擊-支付轉化率：
基礎數據齊活了！
從僅有的head5數據可以看到，雙敏品牌以160萬的搜索人數獨佔鰲頭，但是！排名第二的九方，雖然搜索人數少了40多萬，卻能憑藉較高的搜索-點擊轉化率和點擊-支付轉化率，在支付人數上遠超雙敏，成為支付之王。
表格太晦澀，我們畫個圖吧：
註：因為分析背景是無差別投放，搜索人數重要性非常高，為了可視化簡潔清晰，我們簡單粗暴的篩選TOP15品牌來繪圖
氣泡大小代表著品牌搜索人數量級
根據氣泡圖，我們按照搜索-點擊轉化率和點擊-支付轉化率的高低劃分了4個區間：
區間1：高搜索-點擊轉化，高點擊-支付轉化
區間2：低搜索-點擊轉化，高點擊-支付轉化
區間3：低搜索-點擊轉化，低點擊-支付轉化
區間4：高搜索-點擊轉化，低點擊-支付轉化
再結合數據表，看的更加清晰：
結果顯而易見，高搜索量級的品牌，主要呈現出兩種形態：
正當我們準備把這一步結果同步給首席吹牛官，順便探討進一步的數據分析方向，比如結合支付人數的金額貢獻、留存率、LTV，以及引入兩年增速的維度，結合業務動作來定位深層原因。
沒想到首席吹牛官發來了這樣的消息：
「第二個需求我可能沒說清楚，這次不僅是表彰，也是給融資機構秀肌肉的一部分，我們關注的只是品牌聲量，對應的就是品牌搜索人數這個指標，你匯總好排個序就好」
我們每個人會說超過5種語言的髒話，但在這個場景，大部分人只能條件反射般的打出這8個字：
「嗯嗯，好的，馬上給到」
實例故事告一段落，上述數據維度和驗證角度，大家可以做更多的探索。
相關數據源和代碼已經打包好，獲取地址：
連結：
https://pan.baidu.com/s/1e-rP7lrSXLrO2KAz8dXz3Q
提取碼：chui
贈送新書《Python大數據分析從入門到精通》5 本！已經贈送多次了，這本書非常棒，由「北京大學出版社」贊助提供，感興趣的朋友非常推薦入手一本。介紹：本書結合Python在數據分析領域的特點，介紹如何在數據平臺上集成使用Python。本書內容分為3大部分。第1部分（第1~3章）為搭建開發環境和導入測試數據；第2部分（第4~12章）為Python對HDFS、Hive、Pig、HBase、Spark的操作，主要是對常用API的說明；第3部分（第13~16章）是在前面章節的基礎上，介紹如何進行數據的分析、挖掘、可視化等內容。
參與方式：本篇文章底部「點讚」+「在看」+「留言」，文章內容相關的優質留言才可上牆！留言點讚數量最多前5位讀者將獲得這本書，截止時間「1月26日20:00」，最終獲贈者添加我的微信領取。PS：禁止惡意刷贊！發現後將進入黑名單，取消上牆資格。🧐分享、點讚、在看，給個三連擊唄！