分析Excel里的数据

背景：

有一个类似这样的Excel文件(文件内容随便写的)

分为三个运营商---电信、联通、移动
每个运营商有四个参数首包、尾包、成功率、慢速比
我需要统计某个运营商下阿里云流量更优的所有省份，或者自建机房流量更优的所有省份，比如一个省份的某个运营商的四个参数中阿里云更优的个数>自建机房更优的个数，则输出该省份。

方法一：

将excel数据导入到数据库里，通过数据库的sql语句进行查询

• 将excel文件转存为制表符分割的文本(.txt)

  
  image.png

• 数据库操作
  新建表zl （注意CHARSET为utf8 否则会出现中文乱码）

CREATE DATABASE zhao;
USE zhao;
CREATE TABLE `zl` (
  `province` char(20) NOT NULL,
  `dxsb` char(20) DEFAULT NULL,
  `dxwb` char(20) DEFAULT NULL,
  `dxcgl` char(20) DEFAULT NULL,
  `dxmsb` char(20) DEFAULT NULL,
  `ltsb` char(20) DEFAULT NULL,
  `ltwb` char(20) DEFAULT NULL,
  `ltcgl` char(20) DEFAULT NULL,
  `ltmsb` char(20) DEFAULT NULL,
  `ydsb` char(20) DEFAULT NULL,
  `ydwb` char(20) DEFAULT NULL,
  `ydcgl` char(20) DEFAULT NULL,
  `ydmsb` char(20) DEFAULT NULL,
  PRIMARY KEY (`province`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 查看我们刚保存的txt文件
  把头部的信息删掉

  
  image.png

• 导入txt文件到mysql
  此步在mysql命令行操作(USE zhao; 这句话之后就可以)
  确保sla.txt是跟mysql在一个服务器上

load data local infile "/sla.txt" into table male fields terminated by "\t" lines terminated by "\r\n";

• 通过sql语句进行分析

我们现在从数据库查看就出现txt文本里的内容了

image.png

我们的查询逻辑开始已经讲过了，下面直接贴sql语句
栗子：
电信运营商阿里云更优的省份

select * from (select province, if(dxsb = '阿里云更优', '1', '0') + if(dxwb = '阿里云更优', '1', '0') + if(dxcgl = '阿里云更优', '1', '0') + if(dxmsb = '阿里云更优', '1', '0') as aliyu, if (dxsb = '自建机房更优', '1', '0') + if(dxwb = '自建机房更优', '1', '0') + if(dxcgl = '自建机房更优', '1', '0') + if(dxmsb = '自建机房更优', '1', 0) as zijian from zl) as zl_inner where aliyu > zijian;

移动运营商自建更优的省份

select * from (select province, if(ydsb = '阿里云更优', '1', '0') + if(ydwb = '阿里云更优', '1', '0') + if(ydcgl = '阿里云更优', '1', '0') + if(ydmsb = '阿里云更优', '1', '0') as aliyu, if (ydsb = '自建机房更优', '1', '0') + if(ydwb = '自建机房更优', '1', '0') + if(ydcgl = '自建机房更优', '1', '0') + if(ydmsb = '自建机房更优', '1', 0) as zijian from zl) as zl_inner where zijian > aliyu;

方法二：

通过python分析

python代码：

#!/usr/bin/env python
# -*-coding:utf-8-*-
import xlrd
from collections import Counter, defaultdict

#打开文件
data = xlrd.open_workbook("sla.xlsx")
data_list = data.sheet_names()
# output 工作表名称
print "表名称：",data_list
print "#" * 50

def read_xlsx(datax):
    table = data.sheet_by_name(datax)
    # 分行
    nrows = table.nrows
    nrowsx = table.nrows
    l_list = []
    for i in range(nrowsx):
        if i:
            l_list.append(table.row_values(i))

    l = u"阿里云更优"
    ll = u"自建机房更优"
    for lis in l_list:
        #print lis
        #all_list = [lis[0],lis[1:5],lis[5:9],lis[9:13]]
        # city ; start: end: 4
        all_list = [lis[0],lis[1:13:4]]

        for ii in all_list[1:]:
            c_num = Counter(ii)
            if l in c_num or ll in c_num:
                # cmp num
                if c_num.get(l) > c_num.get(ll):
                    print all_list[0],c_num,"||","name:",l,"num:",c_num.get(l)
                else:
                    pass
    return "ok"

if __name__ == '__main__':

    # 情况是  输入三张工作表
    print read_xlsx(u'\u5de5\u4f5c\u88681')

表名称datax指的是excel里工作表sheet的名字。