python处理表格数据教程_利用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 = names4、排序，一般按照时间排序

由于时间格式是 “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的使用并更多的了解了数据分析流程，接下来将进行更加复杂的数据分析学习。