读取mysql数据库的数据表,了解数据的分布情况、了解分析方向、排除该单个变量的异常值 ,只需给定表名或视图名称。
import math
import sqlalchemy
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import pymysql
class EDA:
def __init__(self,d):
self.engine = sqlalchemy.create_engine("mysql+pymysql://%s:%s@%s/%s?charset=utf8" %(d['user'],d['pwd'],d['ins'],d['db']))
def get_df_from_table(self,table_name):
df = pd.read_sql_table(table_name, self.engine)
return df
def get_df_from_query(self,sql):
df = pd.read_sql_query(sql, self.engine)
return df
#读取表各字段数据类型
# def get_table_type(self,table_name):
# sql = """select c.name as colname,t.name as typename
# from sys.sysobjects o inner join syscolumns c on o.id=c.id and o.name<>'dtproperties'
# inner join sys.systypes t on c.xusertype=t.xusertype
# where o.name='%s'""" % table_name
# df = self.get_df_from_query(sql)
# return df
def get_table_type(self,table_name):
sql="select DISTINCT DATA_TYPE as typename,TABLE_NAME,COLUMN_NAME as colname,IS_NULLABLE,COLUMN_COMMENT from INFORMATION_SCHEMA.Columns\
where table_name='%s'"%table_name
df=self.get_df_from_query(sql)
return df
#绘图
def eda_plot(self,table_name):
list_char = ['char','nchar','varchar','nvarchar','text','ntext','sysname']
list_num = ['tinyint','smallint','int','real','money','float','decimal','numeric','smallmoney','bigint']
df_type = self.get_table_type(table_name)
df_date = self.get_df_from_table(table_name)
date_count = df_date.shape[0]
k = 0
for row in df_type.itertuples():
k = k + 1
#字符类型,绘柱状图
if row.typename in list_char:
col = df_date.groupby([row.colname]).agg({row.colname:['count']})
row_count = col.shape[0]
#col_count = col.shape[1]
col = col.sort_index()
val = col.values.tolist()
#只绘不重复数占总数比小于 5% 的
if math.floor(row_count*100/date_count) <5:
df_ = pd.DataFrame(col.index.values.tolist(), columns=[row.colname])
df_['count'] = list(i[0] for i in val)
x_axle = range(len(df_[row.colname]))
y_axle = df_['count'].tolist()
x_label = df_[row.colname].tolist()
fig, (ax1, ax2) = plt.subplots(2)
ax1.bar(x_axle,y_axle)
ax1.set_xticks(x_axle)
ax1.set_xticklabels(x_label)
ax1.set_title('表[%s] %s 分布' % (table_name,row.colname))
ax2.pie(y_axle,labels=x_label, autopct='%1.2f%%')
#数值类型,其他分布图
elif row.typename in list_num:
df__ = pd.DataFrame(df_date[row.colname])
df__ = df__[(df__[row.colname].notnull())].sort_values(row.colname, ascending=True).reset_index(drop=True)
k = k + 1
plt.figure(k)
plt.subplot(1,3,1)
plt.hist(df__[row.colname])
plt.subplot(1,3,2)
plt.boxplot(df__[row.colname])
plt.gca().set_title('表[%s] %s 分布' % (table_name,row.colname))
plt.subplot(1,3,3)
plt.violinplot(df__[row.colname])
plt.tight_layout()
else:
pass
plt.show()
if __name__ == "__main__":
#conn = {'user':'root','pwd':'','ins':'localhost:3306','db':'test'}
conn = {'user':'root','pwd':'','ins':'localhost:3306','db':'test'}
#conn = {'user':'用户名','pwd':'密码','ins':'实例','db':'数据库'}
eda = EDA(conn)
eda.eda_plot("titanic_train")