In [5]:

    

import pandas as pd
import numpy as np
from datetime import datetime
from sqlalchemy import create_engine
import matplotlib.pyplot as plt

np.random.seed(1445)
ZERO_TIMESTAMP = datetime(2017, 8, 16, 21)

class MySQLConfig:
    HOST = '10.0.149.62'
    USER = "root"
    PASS = "root123"
    DB = "hangzhouhubqa_v1_2"
    CHARSET = 'utf8'

    engine = create_engine(
        f'mysql+pymysql://{USER}:{PASS}@{HOST}/{DB}?charset={CHARSET}',
        isolation_level="READ UNCOMMITTED")


def load_from_mysql(table_name: str=None, user_name: str=None):
    """读取远程mysql数据表"""
    query_str = f"select * from {table_name} where user_name = '{user_name}'"
    table = pd.read_sql_query(sql=query_str, con=MySQLConfig.engine)
    return table


    

In [7]:

    

# ============================================数据表统计 o_machine table===================================
table_o_m = load_from_mysql('o_machine_table', 'kissf')
table_o_m.groupby(['parcel_id'])['small_id'].apply(set).apply(len)
# table_o_m[table_o_m.equipment_id.str.startswith('u')]
# t_o_m = table_o_m.groupby(['parcel_type']).small_id.apply(set).apply(len);t_o_m
# table_o_m[table_o_m.equipment_id.str.startswith('r')]


    

    
Out[7]:





parcel_id
1160447         23
1173714          1
1173715          1
1173717          1
1173719          1
1173720          1
1173721          1
1173723          1
1254956          1
1254999          1
980000000007     1
980000000008     1
980000000009     6
980000000010     9
980000000011     8
Name: small_id, dtype: int64

In [4]:

    

# ------------------------------------------------------------各个机器处理件量------------------------------------------
t_p_1 = table_o_m[table_o_m.equipment_id.str.startswith('r')].groupby(table_o_m.equipment_id).parcel_id.apply(set).apply(len)
t_p_1.plot(kind='bar', 
#            grid  =True,
           position =  0.5,
           legend = True)
plt.show()
t_p = table_o_m[table_o_m.equipment_id.str.startswith('a')].groupby(table_o_m.equipment_id).parcel_id.apply(set).apply(len)
t_p.plot(kind='bar', 
#          grid  =True, 
         legend = True)
plt.show()
t_p_u = table_o_m[table_o_m.equipment_id.str.startswith('c')].groupby(table_o_m.equipment_id).parcel_id.apply(set).apply(len)
t_p_u.plot(kind='bar', 
#          grid  =True, 
         legend = True)
plt.show()