探索性数据分析，主要针对原始数据进行初次了解。了解数据的分布情况、了解分析的方向灯。此脚本读取的是 SQL Server ，只需给定表名或视图名称，如果有数据，将输出每个字段符合要求的每张数据分布图。


# -*- coding: UTF-8 -*-
# python 3.5.0
# 探索性数据分析(Exploratory Data Analysis,EDA)
__author__ = 'HZC'

import math
import sqlalchemy
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

class EDA:
  def __init__(self,d):
    self.engine = sqlalchemy.create_engine("mssql+pymssql://%s:%s@%s/%s" %(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 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':'kk','pwd':'kk','ins':'HYH0109-189\CAT2014','db':'CSMS3'} 
  conn = {'user':'用户名','pwd':'密码','ins':'实例','db':'数据库'} 
  eda = EDA(conn)
  eda.eda_plot("表或视图名")

显示图分为字符型（离散型）和数值型（连续型），示例结果如下：


Python 探索性数据分析(Exploratory Data Analysis,EDA)_sqlalchemy