功能3_ab实验

import xgboost as xgb
import lightgbm as lgbm
import pandas as pd
import numpy as np
import pymysql
import xgboost as xgb
import lightgbm as lgbm
from sklearn.metrics import f1_score
from sklearn.metrics import accuracy_score
from sklearn.feature_extraction.text import CountVectorizer
from sklearn.model_selection import StratifiedKFold
from sklearn.linear_model import LogisticRegression
from sklearn import preprocessing
from datetime import date, timedelta
import gc
from sklearn.metrics import mean_squared_error
from sklearn.preprocessing import LabelEncoder
import lightgbm as lgb
from datetime import datetime
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
import pymysql
from sklearn.decomposition import PCA
from flask import Flask, request,session
from sklearn.preprocessing import StandardScaler
from sklearn.linear_model import LinearRegression
from flask_cors import CORS
import psycopg2
from dataclasses import dataclass
import pymysql
from flask import Flask, request, url_for, redirect, render_template, jsonify
app = Flask(__name__)
import warnings
warnings.filterwarnings("ignore")
import joblib
import datetime
#import mysql.connector
import time
import random
import json
import collections
import random
import matplotlib.pyplot as plt
import os
import copy
from sklearn.metrics import confusion_matrix, classification_report
from sklearn.model_selection import train_test_split
import pandas as pd
import xgboost as xgb
import json
import psycopg2
from psycopg2 import Binary
import joblib
import pickle
import lightgbm as lgb
from joblib import dump, load
from sklearn.ensemble import GradientBoostingClassifier
from sklearn.linear_model import LogisticRegression
from sklearn.svm import SVC
from sklearn.svm import LinearSVC
from sklearn.ensemble import RandomForestClassifier
from sklearn.tree import DecisionTreeClassifier
from sklearn.metrics import accuracy_score
from sqlalchemy import create_engine
import json
from gevent import pywsgi
import knn
import creat_table
import random_forest
app = Flask(__name__)
CORS(app)
#################################################################################### 1 获取数据
# host = "localhost"
# port = "5432"
# database = "copd"
# user = "postgres"
# password = 111111
#
host = "10.16.48.219"
port = "5432"
database = "medical"
user = "pg"
password = 111111
def connect_pg():
    pg_connection = psycopg2.connect(database=database,
                     user=user,
                     password=password,
                     host=host,
                     port=port)
    return pg_connection
def connect_pg_software1():
    pg_connection = psycopg2.connect(database="software1",
                     user=user,
                     password=password,
                     host=host,
                     port=port)
    return pg_connection
def connect_mysql():
    connection = pymysql.connect(
        host='10.16.48.219',
        user='root',
        password='111111',
        database='public',
        cursorclass=pymysql.cursors.DictCursor
    )
    return connection
## connection, table_name
def get_data(connection, table_name):
    query = f"select * from \"{table_name}\""
    data = pd.read_sql(query, connection)
    connection.close()
    return data
# 接口 3_5
# 计算 0 1 比例(数据，疾病名称,条件列，范围)
def Interface3_5_hbl(test_name,test_describe,test_conditions,disease,table_name,age,age_left,age_right,sexname,city,zb,zba_left,zba_right,zbb_left,zbb_right):
    # 存储 test 信息 test_name,test_describe,test_conditions
    connection = connect_pg()
    data = get_data(connection, table_name)
    #1 age 条件筛选
    data[age] = data[age].astype(int)
    data = data.loc[data[age] > age_left]
    data = data.loc[data[age] < age_right]
    #2 sexname 条件筛选
    data = data.loc[data['sexname'] == sexname]
    print(data['sexname'])
    #3 city 条件筛选
    data = data.loc[data['city'] == city]
    print(data['city'])
    data['diagname_code'] = 0
    data.loc[data['diagname'] == disease, 'diagname_code'] = 1
    zba = data.copy()
    zbb = data.copy()
    #4 AB试验 统一指标 左右区间
    # A
    zba[zb] = zba[zb].astype(float)
    zba = zba.loc[zba[zb] > zba_left]
    zba = zba.loc[zba[zb] < zba_right]
    # B
    zbb[zb] = zbb[zb].astype(float)
    zbb = zbb.loc[zbb[zb] > zbb_left]
    zbb = zbb.loc[zbb[zb] < zbb_right]
    # 疾病比例计算
    print('zba样本数:', len(zba))
    zba_1 = len(zba[zba['diagname_code'] == 1])
    zba_0 = len(zba[zba['diagname_code'] == 0])
    zba_reat = zba_1 / (zba_1 + zba_0)
    # 疾病比例计算
    print('zbb样本数:',len(zbb))
    zbb_1 = len(zbb[zbb['diagname_code'] == 1])
    zbb_0 = len(zbb[zbb['diagname_code'] == 0])
    zbb_reat = zbb_1 / (zbb_1 + zbb_0)


    ################################ 连接到 pg 数据库
    connection = connect_pg_software1()
    cursor = connection.cursor()
    # current_time = datetime.datetime.now(
    # 构造 SQL INSERT 语句
    query = "INSERT INTO testab_model1 (test_name,test_describe,test_conditions,disease,age,age_left,age_right,sexname,city,zb,zba_left,zba_right,zbb_left,zbb_right,table_name,zba_reat,zbb_reat) VALUES (%s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s)"
    cursor.execute(query, (test_name,test_describe,test_conditions,disease,age,age_left,age_right,sexname,city,zb,zba_left,zba_right,zbb_left,zbb_right,table_name,zba_reat,zbb_reat))
    connection.commit()
    # 关闭数据库连接
    cursor.close()
    connection.close()

    return zba_reat,zbb_reat
# (数据，疾病名称,条件列，范围)
# 特殊标签 AB验证
connection = connect_pg()
data = get_data(connection, "merge")
###### 输入 (test_name,test_describe,test_conditions,disease,table_name,age,age_left,age_right,sexname,city,zb,zba_left,zba_right,zbb_left,zbb_right):
# 试验标题
test_name = '高血压是否影响慢性阻塞性肺病'
# 试验描述
test_describe = '判断高血压是否影响慢性阻塞性肺病'
# 试验场景
test_conditions = '临床试验'
# 疾病      接口
disease = '慢性阻塞性肺病'
# 选择数据   接口
table_name = 'merge'
# 1 年龄 左右区间
age = 'age'
age_left = 60
age_right = 80
# 2 性别
sexname = '男性'
# 3 城市
city = '绵阳市'
# 4 AB试验 统一指标 左右区间
zb = 'LYMPH_per'
zba_left = 6
zba_right = 80
zbb_left = 6
zbb_right = 80
#
zba_reat,zbb_reat = Interface3_5_hbl(test_name,test_describe,test_conditions,disease,table_name,age,age_left,age_right,sexname,city,zb,zba_left,zba_right,zbb_left,zbb_right)
print("zba_reat患病率为：",zba_reat*100,"%")
print("zbb_reat患病率为：",zbb_reat*100,"%")

###### 输出
# 展示样本量