数据拟合

# coding=utf-8
import pymysql
import numpy as np
import matplotlib.pyplot as plt
from scipy import optimize
from collections import OrderedDict
from pyexcel_xls import get_data
from pyexcel_xls import save_data


def c_mysql():
    '''
    数 据 库
    :return: 
    '''
    conn = pymysql.connect(host='1.200', port=3306, user='hts', passwd='xn', db='dbun',charset='utf8')
    cursor = conn.cursor()
    cursor.execute('select log_date, ad_pv, click_num from api_ind_tb_shop_zzh_table where id > 63 and id<95')
    con_info = cursor.fetchall()
    time_list = []
    ad_pv_list = []
    click_num_list = []
    for con in con_info:
        time_list.append(int(con[0][8:10]))
        ad_pv_list.append(con[1])
        click_num_list.append(con[2])
    cursor.close()
    conn.close()
    return time_list, ad_pv_list, click_num_list

# t_list, a_list, c_list = c_mysql()

def get_excel(xx, yy, excel_name):
    '''
    excel 表格
    '''
    xls_data = get_data(r"%s.xlsx" % excel_name)
    all_list = xls_data['Sheet1']
    # print(all_list)
    x_list = []
    y_list = []
    for i in range(1, len(all_list)):
        if all_list[i] == []:
            pass
        else:
            one_data = all_list[i]
            x_list.append(i)
            y_list.append(one_data[yy])
    # print(x_list,'\n',y_list)
    y_name = all_list[0][yy]
    return x_list, y_list, y_name

# xx = 0
# yy = 2
# get_excel(xx, yy)


def fitting(xx, yy, few_times, excel_name):
    # t_list = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
    # a_list = [5700, 2401, 1602, 1097, 840, 834, 793, 615, 409, 412, 755, 404, 840, 404, 834, 226]

    t_list, a_list, y_name = get_excel(xx, yy, excel_name)    # 读取excel文件数据
    # print(t_list)
    x = np.array(t_list)
    y = np.array(a_list)
    # z = np.array(c_list)

    # #用3次多项式拟合
    # f1 = np.polyfit(x, y, 4)
    f1 = np.polyfit(x, y, few_times)
    p1 = np.poly1d(f1)
    print('\n下为公式\n\n',p1)    # 打印公式
    yvals = p1(x)

    plot1 = plt.plot(x, y, 'b-o', label='try')
    plot2 = plt.plot(x, yvals, 'r', label='fitting')
    # plot3 = plt.plot(x,z, 'g', label='click_num')
    plt.xlabel('排名', fontproperties='SimHei')
    plt.ylabel('%s' % y_name, fontproperties='SimHei')
    plt.legend(loc=1)
    plt.show()
    # plt.savefig("%s.png" % picture_name)




if __name__ == '__main__':
    # xx = 0  # excel  列数 从 0 开始数  第几列作为 x 轴
    # yy = 1  # excel  列数 从 0 开始数  第几列作为 y 轴
    excel_name = input('输入excel文件名字 ：')
    xx = int(input('第几列作为 x 轴(列数从 0 开始数）:'))
    yy = int(input('第几列作为 y 轴(列数从 0 开始数）:'))
    few_times = int(input('几次多项式拟合 :'))
    fitting(xx, yy, few_times, excel_name)