代码部分
# -*- coding:utf-8 -*-
#统计#运行环境python2.7
#需要安装python,numpy,pandas,matplotlib等
import sys
reload(sys)
sys.setdefaultencoding('gb18030')
#sys.setdefaultencoding('utf8')
import numpy as np
import pandas as pd
from pandas import Series,DataFrame
file_name = r'D:/workspace/pythonML/yucefenxi/telephonedirectory.xlsx'
dframe = pd.read_excel(file_name,skiprows=[0])
#######################################
#查看数据
#print dframe
########################################
##统计年度业务总量
dframe.columns = ['index','name','telephonenumber','date']
dframe['date'] = pd.to_datetime(dframe['date'])
dframe = dframe.set_index('date')
df2014 = dframe['2014']
df2015 = dframe['2015']
df2016 = dframe['2016']
#print df2014.size,df2015.size,df2016.size
#########################################
#统计某个单位的业务量,折线图,柱状图显示
#以2016年为例
df2016 = df2016.reset_index()
df_x = df2016.groupby(['name']).count().reset_index().iloc[:,[0]]
df_y = df2016.groupby(['name']).count().reset_index().iloc[:,[1]]
x = [i[0] for i in np.array(df_x)]
y = [j[0] for j in np.array(df_y)]
#y.sort()#如果需要排序显示,用sort()函数
#柱状图显示
import matplotlib.pyplot as plt
def draw_bar(labels,y):
plt.figure(2, figsize=(6,6))
plt.rcParams['font.sans-serif']=['SimHei']#用来显示中文
plt.rcParams['axes.unicode_minus']=False#用来显示负号
plt.bar(range(len(y)),y,color='rgb',tick_label=labels,align='center',alpha = 0.5)
plt.title("2016年业务量柱状图显示")
plt.xlabel("业务姓名")
plt.ylabel("业务数量")
plt.show()
draw_bar(x,y)
#饼图显示
import numpy as np
import matplotlib.pyplot as plt
import matplotlib as mpl
def draw_pie(labels,y):
plt.figure(1, figsize=(6,6))
plt.rcParams['font.sans-serif']=['SimHei']
plt.rcParams['axes.unicode_minus']=False
expl = [0]*len(y)
colors = ["blue","red","coral","green","yellow","orange"]
plt.pie(y, explode=expl, colors=colors, labels=labels, autopct='%1.1f%%',pctdistance=0.8, shadow=True)
plt.title('2016年业务量饼图显示', bbox={'facecolor':'0.8', 'pad':5})
plt.show()
draw_pie(x,y)
#折线图显示
def draw_plot(labels,y):
plt.figure(1, figsize=(6,6))
plt.rcParams['font.sans-serif']=['SimHei']
plt.rcParams['axes.unicode_minus']=False
plt.plot(range(len(y)), y,linewidth=3,color='r',marker='o',markerfacecolor='blue',markersize=8)
plt.xticks(range(len(y)),labels, rotation=0)
plt.title('2016年业务量柱状图显示')
plt.xlabel('业务姓名')
plt.ylabel('业务数量')
plt.show()
draw_plot(x,y)
#########################################
#按月统计
#获取某月的数据
#print dframe['2016-11'] # 获取某月的数据
#获取某个时间段内的数据
#print dframe['2015-11-06':'2016-11-06']
#########################################
#以2016年为例,按月统计
df_period = dframe['2016-01-01':'2016-12-31'].to_period('M')
#按年统计为A 按季度统计为Q 按月统计为M
#print df_period
df2016m_x = df_period.reset_index().groupby(['date']).count().reset_index().iloc[:,[0]]
df2016m_y = df_period.reset_index().groupby(['date']).count().reset_index().iloc[:,[1]]
m_x = [i[0].month for i in np.array(df2016m_x)]
m_y = [j[0] for j in np.array(df2016m_y)]
#按月柱状图显示
def draw_bar(labels,y):
labels = [str(i)+'月' for i in labels]
plt.figure(2, figsize=(6,6))
plt.rcParams['font.sans-serif']=['SimHei']#用来显示中文
plt.rcParams['axes.unicode_minus']=False#用来显示负号
plt.bar(range(len(y)),y,color='rgb',tick_label=labels,align='center',alpha = 0.5)
plt.title("2016年业务量按月统计柱状图显示")
plt.xlabel("业务月份")
plt.ylabel("业务数量")
plt.show()
draw_bar(m_x,m_y)
数据部分
A | B | C | D |
1 | 老爸 | 13385452147 | 2014/4/23 |
2 | 老婆 | 15236985241 | 2014/5/3 |
3 | 老婆 | 15236985241 | 2014/1/12 |
4 | 老姐 | 15447877758 | 2014/4/5 |
5 | 老妹 | 13747855698 | 2014/5/3 |
6 | 哥哥 | 13152563698 | 2014/1/12 |
7 | 姐姐 | 13566982325 | 2014/4/5 |
8 | 姐姐 | 13566982325 | 2014/5/3 |
9 | 弟弟 | 13254122323 | 2015/1/12 |
10 | 老妹 | 13747855698 | 2014/4/5 |
11 | 老妹 | 13747855699 | 2015/5/3 |
12 | 老妹 | 13747855700 | 2015/1/12 |
13 | 老妹 | 13747855701 | 2014/4/5 |
14 | 老妹 | 13747855702 | 2014/5/3 |
15 | 老妹 | 13747855703 | 2014/1/12 |
16 | 老妹 | 13747855704 | 2014/4/5 |
17 | 老妹 | 13747855705 | 2014/5/3 |
18 | 老妹 | 13747855706 | 2015/1/12 |
19 | 老妹 | 13747855707 | 2014/4/5 |
20 | 老妹 | 13747855708 | 2014/5/3 |
21 | 老妹 | 13747855709 | 2014/1/12 |
22 | 老妹 | 13747855710 | 2014/4/5 |
23 | 老妹 | 13747855711 | 2014/5/3 |
24 | 老爸 | 13385452147 | 2014/1/12 |
25 | 老妈 | 13014754588 | 2014/4/5 |
26 | 老师 | 15896583323 | 2014/5/3 |
27 | 老姐 | 15447877758 | 2016/1/12 |
28 | 老婆 | 15236985241 | 2014/4/5 |
29 | 老爸 | 13385452147 | 2014/5/3 |
30 | 老爸 | 13385452147 | 2014/4/5 |
31 | 老婆 | 15236985241 | 2014/5/3 |
32 | 老婆 | 15236985241 | 2014/1/12 |
33 | 老姐 | 15447877758 | 2014/4/5 |
34 | 老妹 | 13747855698 | 2014/5/3 |
35 | 哥哥 | 13152563698 | 2014/1/12 |
36 | 姐姐 | 13566982325 | 2014/4/5 |
37 | 姐姐 | 13566982325 | 2014/5/3 |
38 | 弟弟 | 13254122323 | 2015/1/12 |
39 | 老妹 | 13747855698 | 2014/4/5 |
40 | 老妹 | 13747855699 | 2015/5/3 |
41 | 老妹 | 13747855700 | 2014/1/12 |
42 | 老妹 | 13747855701 | 2014/4/5 |
43 | 老妹 | 13747855702 | 2014/5/3 |
44 | 老妹 | 13747855703 | 2014/1/12 |
45 | 老妹 | 13747855704 | 2014/4/5 |
46 | 老妹 | 13747855705 | 2014/5/3 |
47 | 老妹 | 13747855706 | 2014/1/12 |
48 | 老妹 | 13747855707 | 2014/4/5 |
49 | 老妹 | 13747855708 | 2014/5/3 |
50 | 老妹 | 13747855709 | 2014/6/12 |
51 | 老妹 | 13747855710 | 2014/4/5 |
52 | 老妹 | 13747855711 | 2014/5/3 |
53 | 老爸 | 13385452147 | 2014/1/12 |
54 | 老妈 | 13014754588 | 2014/4/5 |
55 | 老师 | 15896583323 | 2014/5/3 |
56 | 老姐 | 15447877758 | 2015/7/12 |
57 | 老婆 | 15236985241 | 2015/4/5 |
58 | 老爸 | 13385452147 | 2015/5/3 |
59 | 老爸 | 13385452147 | 2015/4/5 |
60 | 老婆 | 15236985241 | 2015/5/3 |
61 | 老婆 | 15236985241 | 2015/1/12 |
62 | 老姐 | 15447877758 | 2015/4/5 |
63 | 老妹 | 13747855698 | 2015/5/3 |
64 | 哥哥 | 13152563698 | 2015/1/12 |
65 | 姐姐 | 13566982325 | 2015/4/5 |
66 | 姐姐 | 13566982325 | 2015/5/3 |
67 | 弟弟 | 13254122323 | 2015/1/12 |
68 | 老妹 | 13747855698 | 2015/4/5 |
69 | 老妹 | 13747855699 | 2015/5/3 |
70 | 老妹 | 13747855700 | 2015/1/12 |
71 | 老妹 | 13747855701 | 2015/4/25 |
72 | 老妹 | 13747855702 | 2015/5/3 |
73 | 老妹 | 13747855703 | 2015/1/12 |
74 | 老妹 | 13747855704 | 2015/4/5 |
75 | 老妹 | 13747855705 | 2015/5/3 |
76 | 老妹 | 13747855706 | 2016/1/12 |
77 | 老妹 | 13747855707 | 2016/4/5 |
78 | 老妹 | 13747855708 | 2016/5/3 |
79 | 老妹 | 13747855709 | 2016/1/12 |
80 | 老妹 | 13747855710 | 2016/4/5 |
81 | 老妹 | 13747855711 | 2016/5/3 |
82 | 老爸 | 13385452147 | 2016/1/12 |
83 | 老妈 | 13014754588 | 2016/4/5 |
84 | 老师 | 15896583323 | 2016/5/3 |
85 | 老姐 | 15447877758 | 2016/1/12 |
86 | 老婆 | 15236985241 | 2016/4/5 |
87 | 老爸 | 13385452147 | 2016/5/3 |
88 | 老爸 | 13385452147 | 2016/4/5 |
89 | 老婆 | 15236985241 | 2016/5/3 |
90 | 老婆 | 15236985241 | 2016/1/12 |
91 | 老姐 | 15447877758 | 2016/4/5 |
92 | 老妹 | 13747855698 | 2016/5/3 |
93 | 哥哥 | 13152563698 | 2016/1/12 |
94 | 姐姐 | 13566982325 | 2016/4/5 |
95 | 姐姐 | 13566982325 | 2016/5/3 |
96 | 弟弟 | 13254122323 | 2016/1/12 |
97 | 老妹 | 13747855698 | 2016/4/5 |
98 | 老妹 | 13747855699 | 2016/5/3 |
99 | 老妹 | 13747855700 | 2016/1/12 |
100 | 老妹 | 13747855701 | 2016/4/5 |
101 | 老妹 | 13747855702 | 2016/5/3 |
102 | 老妹 | 13747855703 | 2016/1/12 |
103 | 老妹 | 13747855704 | 2016/4/5 |
104 | 老妹 | 13747855705 | 2016/5/3 |
105 | 老妹 | 13747855706 | 2016/6/12 |
106 | 老妹 | 13747855707 | 2016/7/5 |
107 | 老妹 | 13747855708 | 2016/5/3 |
108 | 老妹 | 13747855709 | 2016/1/12 |
109 | 老妹 | 13747855710 | 2016/4/5 |
110 | 老妹 | 13747855711 | 2016/5/3 |
111 | 老爸 | 13385452147 | 2016/1/12 |
112 | 老妈 | 13014754588 | 2016/4/5 |
113 | 老师 | 15896583323 | 2016/5/3 |
114 | 老姐 | 15447877758 | 2016/1/12 |
115 | 老婆 | 15236985241 | 2016/4/5 |
116 | 老爸 | 13385452147 | 2016/5/3 |
100 | 老妹 | 13747855701 | 2016/7/5 |
101 | 老妹 | 13747855702 | 2016/8/3 |
102 | 老妹 | 13747855703 | 2016/9/12 |
103 | 老妹 | 13747855704 | 2016/10/5 |
104 | 老妹 | 13747855705 | 2016/11/3 |
105 | 老妹 | 13747855706 | 2016/10/12 |
106 | 老妹 | 13747855707 | 2016/10/5 |
107 | 老妹 | 13747855708 | 2016/10/3 |
108 | 老妹 | 13747855709 | 2016/11/12 |
109 | 老妹 | 13747855710 | 2016/12/5 |
110 | 老妹 | 13747855711 | 2016/12/3 |
111 | 老爸 | 13385452147 | 2016/7/12 |
112 | 老妈 | 13014754588 | 2016/9/5 |
113 | 老师 | 15896583323 | 2016/9/3 |
114 | 老姐 | 15447877758 | 2016/9/12 |
115 | 老婆 | 15236985241 | 2016/7/5 |
116 | 老爸 | 13385452147 | 2016/9/3 |
112 | 老妈 | 13014754588 | 2016/2/5 |
113 | 老师 | 15896583323 | 2016/3/3 |
114 | 老姐 | 15447877758 | 2016/2/12 |
115 | 老婆 | 15236985241 | 2016/3/5 |
116 | 老爸 | 13385452147 | 2016/3/3 |