python读取mysql实现散点图绘制、相关性验证、对数函数拟合

python读取mysql实现对数函数拟合

# -*- coding:utf-8 -*-
# __author__ = "LQ"
import numpy
import numpy as np
from scipy import log
import scipy
from scipy.optimize import curve_fit
import matplotlib.pyplot as plt
import pandas as pd
import scipy.stats as stats
from DataAnalysis.TeachingModel.dbc import dbcConnect

def func(x, a, b):
    y = a * log(x) + b
    return y

def polyfit(x, y, degree):
    results = {}
    #coeffs = numpy.polyfit(x, y, degree)
    popt, pcov = curve_fit(func, x, y)
    results['polynomial'] = popt

    # r-squared
    yhat = func(x ,popt[0] ,popt[1] )                         # or [p(z) for z in x]
    ybar = numpy.sum(y)/len(y)          # or sum(y)/len(y)
    ssreg = numpy.sum((yhat-ybar)**2)   # or sum([ (yihat - ybar)**2 for yihat in yhat])
    sstot = numpy.sum((y - ybar)**2)    # or sum([ (yi - ybar)**2 for yi in y])
    results['determination'] = ssreg / sstot

    return results

def main(sql,index):
    conn=dbcConnect.dbcconnect()
    cursor = conn.cursor()
    cursor.execute(sql)
    # 获取剩余结果所有数据
    results = cursor.fetchall()

    # 获取列名
    cols = [i[0] for i in cursor.description]
    # sql内表转换pandas的DF
    df = pd.DataFrame(np.array(results), columns=cols).astype(float)
    # pearson相关系数
    corr = df.corr()
    print(corr)
    # 输出结果第一个值为pearsonr相关系数，
    # 第二个为p-value，所以这里Guba列和Value值是显著相关的
    pearsonr = stats.pearsonr(df[index], df['getscore'])
    print(pearsonr)
    x = []
    y = []
    for row in results:
        x.append(row[0])
        y.append(float(row[1]))
    print(x)
    print(y)
    t1 = numpy.polyfit(numpy.log(x), y, 1)
    print(t1)
    plt.scatter(x, y)
    plt.show()
    a0 = t1[0]
    b0 = t1[1]
    y1 = [a0 * log(a) + b0 for a in x]
    lab = "y = "+str(a0)+"log(x)+"+str(b0)
    print(lab)
    plot1 = plt.plot(x, y1, '-g', label=lab)
    plot2 = plt.scatter(x, y)
    plt.legend(loc='lower right')
    plt.show()

if __name__ == '__main__':
    sql1 = '''SELECT
    	actual_discuss_num,
    	getscore
    FROM
    	t_study_behavior_bak
    WHERE
    	course_id in (select course_id from t_study_behavior_corr where actual_discuss_num>=0.5 and actual_discuss_num<1)
    AND getscore > 0
    AND actual_discuss_num > 0
    AND getscore < 100'''
    index= "actual_discuss_num"
    main(sql1,index)

自定义方法dbcConnect
在下面博客中
https://blog.csdn.net/qq_30868737/article/details/103995174