用R語言處理Excel數據

01-27

數據源：

問題：解決以下四個業績問題

1、月均消費次數；2、月均消費金額；3、客單價；4、消費趨勢

首先打開Excel表格，對數據進行大體的瀏覽，思考上面四個問題

解決步驟

第一步：導入源數據

R中，XLConnnect和openxlsx包來處理Excel文件，XLConnnect依賴於Java，我們選擇openxlsx包。R在導入源數據的時候注意導入路徑，如果路徑過長，中文等，會出現導入錯誤。

首先安裝並且導入openxlsx包

> install.packages("openxlsx")nInstalling package into 『D:/用戶目錄/我的文檔/R/win-library/3.3』(as 『lib』 is unspecified)ntrying URLnhttps://cran.rstudio.com/bin/windows/contrib/3.3/openxlsx_4.0.0.zipnContent type application/zip lengthn2379870 bytes (2.3 MB)ndownloaded 2.3 MBnpackage 『openxlsx』 successfully unpackednand MD5 sums checkednThe downloaded binary packages are inn C:UsersAdministratorAppDataLocalTempRtmpiKWFIydownloaded_packages n> library("openxlsx")n

> readFilepath <- "F:/bigdata/朝陽醫院2016年銷售數據.xlsx"#注意路徑書寫n> exceldata <- read.xlsx(readFilepath,"Sheet1")#讀入數據n> View(exceldata)#查看導入的Excel數據n第二步：數據預處理n

1、列名重命名：源數據中列名都是中文，為了保證編碼的正常運行，對其進行英文重命名

> names(exceldata) <- c("time","cardno","drugId","drugName","saleNumber","virtualmoney","actualmoney")n

2、刪除缺失值：缺失值以符號NA（not available）表示，「！」這一邏輯運算符，表示「非」的意思，這裡我們把time列的缺失值進行刪除，第6579行缺失數據被刪除

> exceldata <- exceldata[!is.na(exceldata$time),]n

3、處理日期：源數據中time列日期是以字元串的形式出現的（日期+星期），為了便於數據分析，需要將字元串拆分為相應的單列字元。

str_split_fixed 函數使用前，先使用 nstall.packages (stringr) 安裝，再使用nlibrary (stringr) 引用該包

> library(stringr)

> timesplit <-str_split_fixed(exceldata$time," ",n=2)# time里的字元串被成功分成兩列字元

> exceldata$time <- timesplit[,1]

欄位拆分：

欄位分割函數： str_split_fixed (x, split, n)nx：需要處理的欄位/字元nsplit：用於分割的字元串nn：分隔為多少列n返回值： data.framen4、數據類型轉換：由於運算的需要，需將Time中的字元型向量由「character」轉換為以數值形式存儲的日期變數，函數as.Date（）用於執行這種轉化。n

> exceldata$time <-nas.Date(exceldata$time,"%Y-%m-%d")n> class(exceldata$time)n[1] "Date"n#銷售數量、應收金額以及實收金額（"SaleNumber"、"Virtualmoney"以及"Actualmoney"）這些數據也都可以轉換為數值類型nn> exceldata$saleNumber <-nas.numeric(exceldata$saleNumber)n> class(exceldata$saleNumber)n[1] "numeric"n> exceldata$virtualmoney <-nas.numeric(exceldata$virtualmoney)n

> exceldata$actualmoney <-nas.numeric(exceldata$actualmoney)

5、數據排序：對銷售數據按照時間升序進行排列

> exceldata <-nexceldata[order(exceldata$time,decreasing=FALSE),]

第四步：數據分析

1.月均消費次數=總消費次數n/ 月份數（註：同一天內，同一個人發生的所有消費算作一次消費）

2.月均消費金額=總消費金額/n月份數（註：同一天內，同一個人發生的所有消費算作一次消費）

3.客單價（客單價=總消費金額/總消費次數，也即是平均交易金額，門店的銷售額是由客單價和顧客數(客流量)所決定的，因此，要提升門店的銷售額，除了儘可能多地吸引進店客流，增加顧客交易次數以外，提高客單價也是非常重要的途徑。）

消費趨勢（周-消費金額曲線）

問題一、月均消費次數：由於同一天內，同一個人發生的所有消費算作一次消費，所以得剔除其本人當天超過一次的消費記錄。月數則根據30天為1個月進行粗略計算。

> kpi1 <-nexceldata[!duplicated(exceldata[,c("time","cardno")]),]#採用邏輯符號「！」以及"duplicated()"語句就能篩選出非重複的消費次數。

> consumeNumber <- nrow(kpi1) #總消費次數相當於行數n> consumeNumbern[1] 5394n> starttime <- kpi1$time[1] #最小的時間值n> starttimen[1] "2016-01-01"n> endtime <- kpi1$time[nrow(kpi1)] #最大的時間值n> endtimen[1] "2016-07-19"n> day <-nas.numeric(endtime-starttime) #天數n> dayn[1] 200n> month <- day %/% 30#整除法得出月數為6n> monthn[1] 6n> monthconsume <- consumeNumber / monthn> monthconsume#月均消費次數為899次/月n[1] "899.00"n> monthconsume <- format(round(monthconsume,2),nsmall=2)n> monthconsumen[1] "899.00"n

問題二：月均消費金額：月均消費金額=總消費金額/月數，總消費金額是實際消費金額的總和，月數則根據上次計算的6個月計算即可。

> totalmoney <- sum(exceldata$actualmoney,na.rm=TRUE) #採用函數sum求和，並用na.rm剔除缺失值，最終為只對有效值進行求和。n> totalmoneyn[1] 304658.3n> monthmoney <- totalmoney / month#月消費金額為50776.38元n> monthmoneyn[1] 50776.38n

問題三：客單價（pct(per customer transaction)）：客單價=總消費金額/總消費次數

> pct <- totalmoney / consumeNumbern> pctn[1] 56.48095n> pct <- format.factor(round(pct,2),nsmall=2) #保留小數點後兩位n> pct#客單價為56.48元/次n[1] "56.48"n

問題四：消費趨勢：以周為時間橫軸，周消費金額為縱軸，繪製折線圖的方式得出周-消費金額曲線

> week <- tapply(exceldata$actualmoney,format(exceldata$time,"%Y-%U"),sum) #據說利用tapply（）函數進行數組分類，sum()函數分別求和

> week <- as.data.frame.table(week)

> names(week) <- c("time","actualmoney")

> week$time <- as.character(week$time)

> plot(week$timeNumber,week$actualmoney,xlab="時間（年份-第幾周）",ylab="消費金額",xaxt="n",main="2016年朝陽醫院消費曲線",col="blue",type="b")

> axis(1,at=week$timeNumber,labels=week$time,cex.axis=1.5)

總結：堅持！