兼职教师留存

#### 主题:兼职教师留存
#### 执行时间:xxxs,shape:xxx,新脱敏库
#### 需求方: 王新迎
#### created_by:汪国强
#### update by:
#### checkd by:
#!/usr/bin/env python
# coding: utf-8
# In[56]:
import sys
sys.path.append(r'e:\Desktop\hfjy_clean\DataMachine_for_clean\src')
import pandas as pd
import numpy as np
import pymysql
from ML import data_suit,plot_suit
from utils import mysqlhelper,elapse
from config import config
from datetime import datetime , time
mysqlinstance_dmart_dmart = mysqlhelper(**config.dmart_dmart)
# In[67]:
end_time = pd.date_range('2018-01-01',periods=200,freq='BM').astype('str')
start_time = pd.date_range('2018-01-01',periods=200,freq='BMS').astype('str')
# In[59]:
####先求出各月消课兼职教师数
x = 0
for t in range(15):
    sql1 = '''
SELECT
        '{2}' 统计日期,
        count(a.tid) 总量
FROM
        (
        SELECT
                t.teacher_id as tid
        FROM
                view_teacher t
        LEFT JOIN
                lesson_plan lp on lp.teacher_id = t.teacher_id
        WHERE
                lp.lesson_type = 1 and lp.status in (3,5) and lp.solve_status <> 6 and lp.adjust_start_time >='{0}' and lp.adjust_start_time < '{1}' and t.quarters_type = 3
        GROUP BY
                t.teacher_id)a
'''.format(start_time[x],end_time[x],start_time[x])
    my_conn = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com", port=3306, user="bi_newbaby",passwd="wFwQqEffIYcLVD8trWhe", db="hfjydb", charset="utf8")
    df1 = pd.read_sql(sql1,my_conn)
    if x == 0:
         res = pd.DataFrame()
    res = pd.concat([res,df1], axis=0)
    x=x+1
res
# In[64]:
y=0
for t in range(15):
    sql2 = '''
    SELECT
            t.teacher_id as tid
    FROM
            view_teacher t
    LEFT JOIN
            lesson_plan lp on lp.teacher_id = t.teacher_id
    WHERE
            lp.lesson_type = 1 and lp.status in (3,5) and lp.solve_status <> 6 and lp.adjust_start_time >='{0}' and lp.adjust_start_time < '{1}' and t.quarters_type = 3
    GROUP BY
            t.teacher_id'''.format(start_time[y],end_time[y])
    my_conn = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com", port=3306, user="bi_newbaby",passwd="wFwQqEffIYcLVD8trWhe", db="hfjydb", charset="utf8")
    df2 = pd.read_sql(sql2,my_conn)
    print('>>>>>> 成功获取第'+str(y+1)+'月所有兼职教师的teacher_id')
    print('>>>>>> 开始将所有teacher_id和后面各月进行匹配')
    p = 1
    jsq = 1 ####子循环计数器
    for t in range(14):
        if p > 0:
            z = y + 1 ####下一个月
            res1 = pd.DataFrame()
            p = 0
        sql3 = '''
SELECT
        count(a.tid) 第{3}个月
FROM
        (
        SELECT
                t.teacher_id as tid
        FROM
                view_teacher t
        LEFT JOIN
                lesson_plan lp on lp.teacher_id = t.teacher_id
        WHERE
                lp.lesson_type = 1 and lp.status in (3,5) and lp.solve_status <> 6 and lp.adjust_start_time >='{0}' and lp.adjust_start_time < '{1}' and t.quarters_type = 3
                and t.teacher_id in {2}
        GROUP BY
                t.teacher_id)a'''.format(start_time[z],end_time[z],tuple(df2['tid']),jsq)
        my_conn = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com", port=3306, user="bi_newbaby",passwd="wFwQqEffIYcLVD8trWhe", db="hfjydb", charset="utf8")
        df3 = pd.read_sql(sql3,my_conn)
        res1 = pd.concat([res1,df3], axis=1)
        z = z + 1
        jsq = jsq + 1
    if y == 0:
         data_no = pd.DataFrame()
    data_no = pd.concat([data_no,res1], axis=0)
    y = y + 1
    print('>>>>>> 成功获取 '+str(y)+'月所有兼职教师留存情况')
    print(data_no)
# In[65]:
sub_data = pd.concat([res,data_no], axis=1)
sub_data
# In[66]:
#### 重命名columns,以方便插入表
sub_data = sub_data.rename(columns={'统计日期':'statistics_time','总量':'num','第1个月':'one','第2个月':'two','第3个月':'three','第4个月':'four','第5个月':'five','第6个月':'six','第7个月':'seven','第8个月':'eight','第9个月':'nine','第10个月':'eight','第10个月':'nine','第10个月':'ten','第11个月':'eleven','第12个月':'twelve','第13个月':'thirteen','第14个月':'fourteen'})
sub_data
# In[48]:
#### 导出到excel
sub_data.to_excel(r'e:\Desktop\兼职教师留存.xlsx',index=False,encoding='GB18030')
# In[49]:
#### 插入dmart数据库
mysqlinstance_dmart_dmart.insertmany_bydf(sub_data,'wgq_teacher_keep',if_exists='replace')
文档更新时间: 2019-03-29 11:15   作者:admin