利用Python處理Excel數據

本文的數據源是朝陽醫院2016的銷售數據，課程是使用R語言來進行數據處理的，這裡嘗試採用Python來處理。

要求的業務指標是：1）月均消費次數；2）月均消費金額；3）客單價；4）消費趨勢

這幾個指標主要判斷了用戶端的消費趨勢，為了給醫院更多的指導，在此基礎上進行了一定的擴展，個人增加了兩個業務指標，也是為了多熟悉庫的使用：

5）列出各類藥品售出數量的排名。這個指標可以指導醫院多存儲哪類藥品，少存儲哪類藥品，避免藥物不足或過期

6）列出每周7天各天各有多少人來醫院。這樣可以提前安排每周各天的排班，避免某天過於辛 苦或空閑

本文數據的處理基本上使用的是pandas庫，實踐發現該庫確實十分強大。

首先可以按照【原】十分鐘搞定pandas - ChaoSimple - 博客園 將上面的例子逐一熟悉一 下，有個概念即可，號稱十分鐘可以熟悉，但還是花了2個多小時才走了一遍。在實踐中如果遇到不會的可以在該網址中找到相應的模塊，然後進入詳細界面進行深入學習。

數據分析過程：

一、預處理

1、讀取數據

df = pd.read_excel(.../朝陽醫院2016年銷售數據1.xlsx,sheet_name=Sheet)

一句話生成dataframe，接下來基本上就是利用dataframe進行分析了

2、刪除缺失值，雖然實際上沒有，但也需要這一步

df = df.dropna(how = any,axis=0)

3、修改列名，中文名稱是無法在dataframe中直接用字元串表示出來的

#改名

names = [time,cardNum,drugID,drugName,saleNum,virtualMoney,actualMoney]df.columns = names

4、排序，一般按照時間排序

由於時間格式是 「2016-01-01 星期五」 這樣日期星期混合的，需要將中文切割出去，並將時間轉換成datetime格式

df1 = df.copy()df1[time] = pd.to_datetime(df[time].str.split( ).str.get(0))df1.sort(columns = time,inplace = True)

由於接下來需要用到起始時間，最終時間以及時間間隔，所以對日期進行了預處理

startTime = df1.iloc[0, 0]endTime = df1.iloc[-3, 0]delta = (endTime - startTime) / np.timedelta64(1, D) # D代表day，s代表秒month = delta / 30

二、業務指標分析

業務一、月均消費次數

月均消費次數=總消費次數/月份數

def monthConsumeF(dataframe): #按照日期及社保賬號來清洗數據，將每個人一天的所有消費次數算作一次消費 mcdf = dataframe.drop_duplicates([time,cardNum]) consumeNumber = len(mcdf) monthConsume = int(consumeNumber / month) # 月消費次數 print 月消費次數:, monthConsume調用 monthConsumeF(df1)output: 月消費次數: 809

這裡結果有些不同，因為7月有19天，如果直接取整算6個月那計算結果將會有較大偏差，所以我覺得平均月份取小數較好

業務二、月均消費金額

月均消費金額=總消費金額/月份數

def monthMoneyF(dataframe): actualMoneySum = pd.Series.sum(dataframe[actualMoney]) # print actualMoneySum monthMoney = int(actualMoneySum / month) print 月均消費金額:, monthMoney調用 monthMoneyF(df1)output:月均消費金額: 45694

業務三、客單價

客單價=總消費金額/總消費次數

def ptsF(dataframe): actualMoneySum = pd.Series.sum(dataframe[actualMoney]) consumeNumber = len(dataframe.drop_duplicates([time,cardNum])) pts = actualMoneySum/consumeNumber print 客單價:, pts調用 ptsF(df1)output:客單價: 56.4339125602

業務四、消費趨勢

消費趨勢是按周計算的

def trendF(dataframe): trend = dataframe.loc[:, [time, actualMoney]] trend[time] = pd.to_datetime(trend[time]) trend = trend.set_index(time) # 按周分割並計算周消費總和 s = trend[actualMoney].resample(W).sum() trendNew = pd.Series.to_frame(s) # index format 將年月日 改為 年周 trendNew.index = trendNew.index.format(formatter=lambda x:x.strftime(%Y-%W)) # print df5 trendNew.plot() plt.legend(loc=best) plt.show()

主要是這一句

s = trend[actualMoney].resample(W).sum()trendNew = pd.Series.to_frame(s)

按照周計算每周的銷售總額，這樣得出的是一個序列，再將其轉換為dataframe，這樣便於直接作圖

業務五、列出各類藥品售出數量的排名

def saleNumF(dataframe): drug = dataframe.loc[:, [drugName, saleNum]] #按藥品名進行歸類並計算藥品賣出總和 s1 = drug.groupby(drug.drugName.str[:])[saleNum].sum() drugNew = pd.Series.to_frame(s1) drugNew = drugNew.sort_values(by=saleNum, ascending=False) #太擁擠了分成兩張圖 drugPart1 = drugNew.iloc[0:34, :] drugPart2 = drugNew.iloc[34:, :] ax1 = drugPart1.plot.barh(alpha=0.75, rot=0,fontsize=8) ax2 = drugPart2.plot.barh(alpha=0.75, rot=0,fontsize=8) ax1.set_xlim(0,2000) ax2.set_xlim(0,2000) plt.show()

主要難點在如何將藥品進行歸類，在pandas中直接用groupby函數就能做到，中文也可以直接歸類。這裡兩張圖選擇同樣的尺度進行表示，主要是為了能更好的直接對比。

但這樣的表示方法我覺得不是太好看，由於是初學，不知道有什麼更好的方法可以表現我想得到的結果，希望大家給點建議。

最終得到以下兩張圖

業務六、列出每周7天各天各有多少人來醫院

def weekdayF(dataframe): timeDF = dataframe.drop_duplicates([time, cardNum]) timeDF = timeDF.loc[:, [time]] timeDF[num] = 1 timeDF[time] = pd.to_datetime(timeDF[time]) #將日期換算成周幾 timeDF[weekday] = timeDF[time].dt.dayofweek weekday = timeDF.loc[:, [weekday, num]] s2 = weekday.groupby(weekday)[num].sum() weekdayNew = pd.Series.to_frame(s2) weekdayNew[weekdays] = [Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday] weekdayNew = weekdayNew.set_index(weekdays) print weekdayNew weekdayNew.plot(kind=bar) plt.show()

主要思路就是按照業務一的方法將數據進行清洗，然後將其作為一次人流。將日期換算成星期，以此來進行匯總。

最終得到的圖如下：

可以看出周三來的人最少，周四最多，所以可以據此來分配人手。

由於matplotlib有中文顯示的問題，在開頭需要加上

plt.rcParams[font.sans-serif] = [SimHei] # 指定默認字體plt.rcParams[axes.unicode_minus] = False # 解決保存圖像是負號-顯示為方塊的問題

這是一次簡單的數據分析，主要是熟悉了pandas的使用並更多的了解了數據分析流程，接下來將進行更加複雜的數據分析學習。
推薦閱讀：