python对excel数据统计_python读取excel数据做分类统计

#-*- coding:utf-8 -*-

importxlrdfrom datetime importtimedeltadefread_excel(file_excel):#读excel并将需要的数据分类放在数组里

infos=[]

info_file=xlrd.open_workbook(file_excel)

info_sheet=info_file.sheets()[0]

row_count=info_sheet.nrowsfor row in range(1,row_count):

time_string=info_sheet.cell(row,3).value

time_s_sp=time_string.split(':')

infos.append(

{'type':info_sheet.cell(row,2).value,'other_cellphone':info_sheet.cell(row,0).value,'timespan':timedelta(seconds=int(time_s_sp[2]),minutes=int(time_s_sp[1]),hours=int(time_s_sp[0])),'gpscity':info_sheet.cell(row,5).value

}

)returninfosdefcount_cell(list_dirs,infotype):#统计总通话及分类统计结果，存在字典里

result_dir={}

time_all=timedelta(seconds=0)for list_dir inlist_dirs:

time_all+=list_dir['timespan']

info_type=list_dir[infotype]if info_type not inresult_dir:

result_dir[info_type]=list_dir['timespan']else:

result_dir[info_type]+=list_dir['timespan']returntime_all,result_dirdefprint_result(result_dir):#打印数据for k,v inresult_dir.items():print k.encode('utf-8'),vif __name__=="__main__":

list_dirs=read_excel('src.xls')

time_all,result_type=count_cell(list_dirs,'type')

result_cell=count_cell(list_dirs,'other_cellphone')

result_gpscity= count_cell(list_dirs, 'gpscity')print '总通话时间：%s' %time_allprint '按照通话类型分类：'print_result(result_type)print '按照号码分类：'print_result(result_cell[1])print '按照归属地分类：'print_result(result_gpscity[1])