大家好，我是寶器

最近有不少粉絲問我關(guān)于Python批量操作Excel的問題。

大家的關(guān)注點(diǎn)主要是如何循環(huán)遍歷表格、如何用Pandas批量處理，當(dāng)然，還有在996的壓迫下如何提效（來擠出更多摸魚時(shí)間）。

為此，我特意肝了幾天，用基于實(shí)際業(yè)務(wù)的脫敏數(shù)據(jù)，以完整小項(xiàng)目的形式，來集中解決這些問題。

我的Pandas實(shí)戰(zhàn)系列老傳統(tǒng)，完整案例代碼和數(shù)據(jù)源，已經(jīng)打包好放在文末。

不吹牛集團(tuán)這幾年孵化了50個(gè)品牌，在各渠道做了大量品宣層面的曝光。現(xiàn)在集團(tuán)首席吹牛官提了兩個(gè)需求：

1. 要一張大表，包含每個(gè)月搜索人數(shù)TOP5的品牌相關(guān)數(shù)據(jù)，以及對應(yīng)品牌在當(dāng)月的搜索份額和排名。

2. 在現(xiàn)有數(shù)據(jù)基礎(chǔ)上，找到最近一年投放效果還不錯(cuò)的品牌，要吹吹牛，做年度表彰。

這是小z特別準(zhǔn)備的兩個(gè)具有代表性的需求：

• 第一種：業(yè)務(wù)方已經(jīng)定好了條條框框，需要數(shù)據(jù)分析師做的是取數(shù)和處理的工作，這樣的“分析”工作，坑往往在于取數(shù)和清洗的復(fù)雜性。

• 第二種：業(yè)務(wù)方自己想了個(gè)模糊的方向，需要分析師結(jié)合實(shí)際數(shù)據(jù)，定邏輯，給建議。

  
  

話音未落，集團(tuán)首席吹牛官的跟屁蟲就把相關(guān)源數(shù)據(jù)丟過來了

一共24張Excel表格，按月存儲，涵蓋了從2019年1月到2020年12月的數(shù)據(jù)。

表格內(nèi)部數(shù)據(jù)大同小異：

注：點(diǎn)擊人數(shù)和支付人數(shù)，都是在搜索人數(shù)基礎(chǔ)上統(tǒng)計(jì)的

每張表都有50個(gè)品牌，包括了品牌名、品牌搜索人數(shù)、點(diǎn)擊人數(shù)和對應(yīng)的支付人數(shù)這幾個(gè)關(guān)鍵字段。

源數(shù)據(jù)就是這樣簡簡單單中又透漏著麻麻煩煩，接下來，我們就開始手撕需求。

開始動(dòng)手前，我們要明確需求。

再回顧一下首席吹牛官的第一個(gè)需求：要一張大表，包含每個(gè)月搜索人數(shù)TOP5品牌的相關(guān)數(shù)據(jù)，以及對應(yīng)品牌在當(dāng)月的搜索份額和排名。

提煉：在現(xiàn)有源數(shù)據(jù)的基礎(chǔ)上，我們還需要對各品牌月內(nèi)按搜索人數(shù)排序，然后計(jì)算每個(gè)品牌搜索份額，取其前5，最后遍歷匯總。

自動(dòng)化之哥曾經(jīng)說過：Python批量操作Excel，無論表格再多，處理邏輯再復(fù)雜，只要我們集中力量擊破一張，就能夠?qū)崿F(xiàn)批量操作的全面勝利。

首先，我們要解決的是單張表的問題。

按搜索人數(shù)排序：

調(diào)用rank快速給到對應(yīng)的排名：

再來計(jì)算搜索份額，搜索份額的計(jì)算公式：單品牌搜索人數(shù)/所有品牌搜索人數(shù)匯總，用Pandas計(jì)算，怎一個(gè)easy了的！

正當(dāng)我們準(zhǔn)備批量執(zhí)行操作，首席吹牛官發(fā)來了消息：

“需求一略有調(diào)整，投資人最關(guān)注的是凌云這個(gè)品牌，要求在匯總表中，每個(gè)月凌云品牌的相關(guān)指標(biāo)排在最前面，后面跟著搜索排名TOP5的品牌”。

具體排名邏輯如圖所示：

面對需求的臨時(shí)改動(dòng)，見過大風(fēng)大浪的我們內(nèi)心沒有一絲波瀾，甚至還有一點(diǎn)想笑。小事一樁，改改Pandas邏輯就好了。

先找到目標(biāo)品牌凌云：

再按照順延的邏輯，定位TOP5品牌相關(guān)數(shù)據(jù)：

合并即可獲取我們想要的結(jié)果：

單表操作完成，批量操作，只需要建立好循環(huán)+合并關(guān)系，并引入日期列，在合并結(jié)果中對不同的表數(shù)據(jù)做好區(qū)分：

result = pd.DataFrame()
for name in os.listdir():    df = pd.read_excel(name)    df = df.sort_values('品牌搜索人數(shù)',ascending = False)    df['搜索人數(shù)排名'] = df['品牌搜索人數(shù)'].rank(ascending = False)    df['搜索份額'] = df['品牌搜索人數(shù)'] / df['品牌搜索人數(shù)'].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批量操作，就是如此絲滑~

第一個(gè)需求搞定。

還記得那個(gè)明（che）確（dan）的需求二嗎？

“在現(xiàn)有數(shù)據(jù)基礎(chǔ)上，找到最近一年投放效果還不錯(cuò)的品牌，要吹吹牛，做年度表彰?！?/span>

首席吹牛官以成本數(shù)據(jù)過于機(jī)密為由，除了說各品牌費(fèi)用基本無差別之外，沒有透露任何關(guān)于成本方面的數(shù)據(jù)，我們自然也無法計(jì)算投放ROI了這些核心指標(biāo)了。

目前能夠拿到的，只有品牌、搜索人數(shù)、點(diǎn)擊人數(shù)和對應(yīng)支付人數(shù)這幾個(gè)指標(biāo)。

要找到最近一年投放效果還不錯(cuò)的品牌，我們可以用漏斗思維，從量級（人數(shù)）和效率（轉(zhuǎn)化率）兩個(gè)角度來考慮：

在費(fèi)用無差別的情況下：

• 人群基數(shù)大（搜索人數(shù)），表示投放的心智效果不錯(cuò)，讓更多用戶被廣告觸達(dá)后，在平臺主動(dòng)搜相關(guān)的品牌。

• 搜索-點(diǎn)擊轉(zhuǎn)化率高，代表了搜索結(jié)果的精準(zhǔn)度，搜索后展示頁面的吸引力等等

• 點(diǎn)擊-支付轉(zhuǎn)化率高，更可能受產(chǎn)品詳情頁面、活動(dòng)力度等影響

在項(xiàng)目二場景中，三個(gè)指標(biāo)越高越好。接下來，我們就結(jié)合搜索人數(shù)，搜索-點(diǎn)擊轉(zhuǎn)化率和點(diǎn)擊-支付轉(zhuǎn)化率，用Pandas做一波分析。

要對最近一年的數(shù)據(jù)做分析，我們先把2020年所有數(shù)據(jù)合并，拿到匯總表：

再按品牌的維度，做指標(biāo)匯總：

計(jì)算對應(yīng)的搜索-點(diǎn)擊轉(zhuǎn)化率，點(diǎn)擊-支付轉(zhuǎn)化率：

基礎(chǔ)數(shù)據(jù)齊活了！

從僅有的head5數(shù)據(jù)可以看到，雙敏品牌以160萬的搜索人數(shù)獨(dú)占鰲頭，但是！排名第二的九方，雖然搜索人數(shù)少了40多萬，卻能憑借較高的搜索-點(diǎn)擊轉(zhuǎn)化率和點(diǎn)擊-支付轉(zhuǎn)化率，在支付人數(shù)上遠(yuǎn)超雙敏，成為支付之王。

表格太晦澀，我們畫個(gè)圖吧：

注：因?yàn)榉治霰尘笆菬o差別投放，搜索人數(shù)重要性非常高，為了可視化簡潔清晰，我們簡單粗暴的篩選TOP15品牌來繪圖

氣泡大小代表著品牌搜索人數(shù)量級

根據(jù)氣泡圖，我們按照搜索-點(diǎn)擊轉(zhuǎn)化率和點(diǎn)擊-支付轉(zhuǎn)化率的高低劃分了4個(gè)區(qū)間：

• 區(qū)間1：高搜索-點(diǎn)擊轉(zhuǎn)化，高點(diǎn)擊-支付轉(zhuǎn)化

• 區(qū)間2：低搜索-點(diǎn)擊轉(zhuǎn)化，高點(diǎn)擊-支付轉(zhuǎn)化

• 區(qū)間3：低搜索-點(diǎn)擊轉(zhuǎn)化，低點(diǎn)擊-支付轉(zhuǎn)化

• 區(qū)間4：高搜索-點(diǎn)擊轉(zhuǎn)化，低點(diǎn)擊-支付轉(zhuǎn)化

再結(jié)合數(shù)據(jù)表，看的更加清晰：

結(jié)果顯而易見，高搜索量級的品牌，主要呈現(xiàn)出兩種形態(tài)：

• 以雙敏（排名第1）、巨奧（排名第3）為代表的品牌主要分布在第三區(qū)間，量級較大，但兩種轉(zhuǎn)化效率均需要進(jìn)一步提升，品牌沒能較好的承接蜂擁而至的流量。

• 九方（排名第2）、商軟（排名第4）則是高搜索量級、高轉(zhuǎn)化效率的代表，從現(xiàn)有數(shù)據(jù)看，他們才是不吹牛集團(tuán)學(xué)習(xí)的榜樣。

正當(dāng)我們準(zhǔn)備把這一步結(jié)果同步給首席吹牛官，順便探討進(jìn)一步的數(shù)據(jù)分析方向，比如結(jié)合支付人數(shù)的金額貢獻(xiàn)、留存率、LTV，以及引入兩年增速的維度，結(jié)合業(yè)務(wù)動(dòng)作來定位深層原因。

沒想到首席吹牛官發(fā)來了這樣的消息：

“第二個(gè)需求我可能沒說清楚，這次不僅是表彰，也是給融資機(jī)構(gòu)秀肌肉的一部分，我們關(guān)注的只是品牌聲量，對應(yīng)的就是品牌搜索人數(shù)這個(gè)指標(biāo)，你匯總好排個(gè)序就好”

我們每個(gè)人會說超過5種語言的臟話，但在這個(gè)場景，大部分人只能條件反射般的打出這8個(gè)字：

“嗯嗯，好的，馬上給到”

實(shí)例故事告一段落，上述數(shù)據(jù)維度和驗(yàn)證角度，大家可以做更多的探索。

相關(guān)數(shù)據(jù)源和代碼已經(jīng)打包好，獲取地址：

鏈接：

https://pan.baidu.com/s/1e-rP7lrSXLrO2KAz8dXz3Q

提取碼：chui