Python利用matplotlib，读取excel数据生产scatter图

# -*- coding: utf-8 -*-
# @Time    : 2018/9/20 9:38
# @Author  : Lin Sen
# @Site    : 
# @File    : scatter.py
# @Software: PyCharm Community Edition

import numpy as np
import matplotlib.pyplot as plt
# from matplotlib.ticker import MultipleLocator
import xlrd
import pandas as pd
import string
import math
import platform

import pandas._libs.tslibs.timedeltas
import pandas._libs.tslibs.np_datetime
import pandas._libs.tslibs.nattype
import pandas._libs.skiplist
import warnings
#忽略warning显示
warnings.filterwarnings('ignore')

def scatter(args):
    _FILENAME = args
    _COL = 1
    _LOW = 2
    _HIGH = 3

    workbook = xlrd.open_workbook(_FILENAME)
    title = _FILENAME.split('.')[0]
    sheets = workbook.sheets()
    sheetCount = len(sheets)


    for j in range(0,sheetCount,1):
        sheet = workbook.sheet_by_index(j)
        # print(sheet.nrows)
        if sheet.nrows==0:
            return
        ylabel = workbook.sheet_names()[j]
        cols = sheet.col_values(int(_COL))
        excel_data = pd.read_excel(_FILENAME, header=None, sheet_name=ylabel,
                                       usecols=[int(_COL)])
        x = []
        y = []
        x = np.arange(0,sheet.nrows,1)
        #转换时增加dtype=float,将unicode字符串转成float
        y = np.array(cols,dtype=float)
        # print(y)
        higeLine = []
        lowLine = []
        index = []
        # print sheet.nrows
        if(sheet.nrows>1):
            for i in range(0,sheet.nrows,1):
                index.append(i)
            higeLine = np.array(sheet.col_values(int(_HIGH)))
            lowLine = np.array(sheet.col_values(int(_LOW)))
        else:
            for i in range(0,2,1):
                index.append(i)
                higeLine.append(sheet.cell_value(0,int(_HIGH)))
                lowLine.append(sheet.cell_value(0,int(_LOW)))

        lsl = float(lowLine[0])
        usl = float(higeLine[0])
        # print lsl,usl

        data_list = []
        for d in excel_data.get_values():
            f = string.atof(d)
            if lsl <= f <= usl:
                data_list.append(f)
        pure_data = pd.DataFrame(data_list)
        # print(pure_data)
        sigma = 3
        if int(lsl)==0:
            lsl = 0-usl
        # diff = float(usl - lsl)
        if len(pure_data)>1:
            u = pure_data.mean()[0]
        else:
            u = pure_data[0][0]
        # print(u)
        # stdev = float(pure_data.std())
        # print pure_data.get_values()
        stdev = np.std(pure_data.get_values(),ddof=1)
        # print(stdev)
        # x1 = np.arange(lsl - diff / 10, usl + diff / 10, diff / 1000)
        # y1 = norm.pdf(x1, u, stdev)
        x1 = np.linspace(u - 3 * stdev, u + 3 * stdev, 1000)
        y1 = np.exp(-(x1 - u) ** 2 / (2 * stdev ** 2)) / (math.sqrt(2 * math.pi) * stdev)

        # print  usl,lsl,u,stdev
        cpu = (usl - u) / (sigma * stdev)
        cpl = (u - lsl) / (sigma * stdev)
        # print cpu,cpl
        cpk = min(cpu, cpl)


        fig = None
        fig = plt.figure(num=title, figsize=(8, 6))
        plt.title('Scatter of '+ylabel, size=14)

        plt.xlabel('index', size=14)
        plt.ylabel(ylabel, size=14)
        plt.scatter(x,y,color='b',marker='.')
        plt.plot(x,y,color='b',linewidth=1)
        plt.plot(index,higeLine,color='r',linestyle="--",linewidth=1, label='upper limit')
        plt.plot(index,lowLine,color='m',linestyle="--",linewidth=1, label='lower limit')
        plt.legend(loc='upper right')


        if int(lowLine[0]) > 2:
            plt.ylim(int(lowLine[0])-2,int(higeLine[0])+2)
        else:
            plt.ylim(-2*float(higeLine[0]),2*float(higeLine[0]))

        ax = None
        ax = fig.add_axes( [0.2, 0.72, 0.12, 0.12])
        # ymajorLocator = MultipleLocator(1)  # 将y轴主刻度标签设置为0.5的倍数
        # ax.yaxis.set_major_locator(ymajorLocator)
        ax.hist(pure_data.get_values(), 20, normed=1)
        ax.plot(x1,y1)
        ax.set_title('cpk=%f' % cpk)
        plt.savefig(title+'_'+ylabel+'.png', format='png', dpi=100)
        # plt.show()
        plt.close('all')
        # del sheet
    # plt.savefig(title+'_'+ylabel+'.eps', format='eps')
    # plt.savefig(title+'_'+ylabel+'.svg', format='svg')



if __name__ == "__main__":
    scatter('2019-01-22/6710A3.xls')