Data profiling in Python

Featured image

Data profiling is intended to help understand data leading to a better data prepping and data quality.

Data profiling is the systematic up front analysis of the content of a data source, all the way from counting the bytes and checking cardinalities up to the most thoughtful diagnosis of whether the data can meet the high level goals of the data warehouse. Ralph Kimball

pandas-profiling Python package is a great tool to create HTML profiling reports. For a given dataset it computes the following statistics:

  • Essentials: type, unique values, missing values
  • Quantile statistics like minimum value, Q1, median, Q3, maximum, range, interquartile range
  • Descriptive statistics like mean, mode, standard deviation, sum, median absolute deviation, coefficient of variation, kurtosis, skewness
  • Most frequent values
  • Histogram
  • Correlations highlighting of highly correlated variables, Spearman and Pearson matrixes


Profiling data from a CSV file

Let’s see how to use pandas-profiling package on a dataset from the University of California, Irvine: Communities and Crime Data Set

Start Jupyter Notebook

jupyter notebook

Create a new notebook

Load pandas and pandas_profiling

import pandas as pd
import pandas_profiling

Load data into a DataFrame object and cast categorical numeric columns

df=pd.read_csv("./data/communities.csv" , sep=',', na_values=["?"])
# Some other useful read_csv parameters
# encoding='UTF-8'
# low_memory=False
# parse_dates=["date_column_name"]

# Change Pandas DataFrame column data type
# df["y"] = pd.to_numeric(df["y"])
# df["z"] = pd.to_datetime(df["z"]) 
df['state'] = df['state'].astype('category')
df['community'] = df['community'].astype('category')
df['fold'] = df['fold'].astype('category')

Display the data profiling report in a Jupyter notebook


Or export it in an HTML file

profile = pandas_profiling.ProfileReport(df)

Profiling data from SQL Server database

## From SQL to DataFrame Pandas
import pandas as pd
import pyodbc

sql_conn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0}; \
                            SERVER=server_name; \
                            DATABASE=db_name; \
query = "SELECT [BusinessEntityID],[FirstName],[LastName],
                 [PostalCode],[City] FROM [Sales].[vSalesPerson]"
df = pd.read_sql(query, sql_conn)


With credentials replace pyodbc.connect instruction with the following

sql_conn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0}; \
                            SERVER=server_name; \
                            DATABASE=db_name; \
                            uid=User; \

As alternative you could consider the pymssql module.

Other data profiling commands


Plot a hierarchical clustering as a dendrogram.

%matplotlib notebook
import matplotlib.pyplot as plt
from scipy.cluster import hierarchy as hc

corr = 1 - df.corr() 
corr_condensed = hc.distance.squareform(corr) # convert to condensed
z = hc.linkage(corr_condensed, method='average')
dendrogram = hc.dendrogram(z, labels=corr.columns)

Principal Component Analysis (PCA) in Python (

%matplotlib notebook
import numpy as np
import matplotlib.pyplot as plt
from sklearn import datasets
from sklearn.decomposition import PCA
import pandas as pd
from sklearn.preprocessing import StandardScaler

iris = datasets.load_iris()
X =
y =
X =
y =
#In general a good idea is to scale the data
scaler = StandardScaler()

pca = PCA()
x_new = pca.fit_transform(X)

def myplot(score,coeff,labels=None):
    xs = score[:,0]
    ys = score[:,1]
    n = coeff.shape[0]
    scalex = 1.0/(xs.max() - xs.min())
    scaley = 1.0/(ys.max() - ys.min())
    plt.scatter(xs * scalex,ys * scaley, c = y)
    for i in range(n):
        plt.arrow(0, 0, coeff[i,0], coeff[i,1],color = 'r',alpha = 0.5)
        if labels is None:
            plt.text(coeff[i,0]* 1.15, coeff[i,1] * 1.15, "Var"+str(i+1), color = 'g', ha = 'center', va = 'center')
            plt.text(coeff[i,0]* 1.15, coeff[i,1] * 1.15, labels[i], color = 'g', ha = 'center', va = 'center')

#Call the function. Use only the 2 PCs.
myplot(x_new[:,0:2],np.transpose(pca.components_[0:2, :]))

See also

Monitor SSIS job and package executions

date_range 02/09/2020

Featured image

How to monitor SSIS job and package executions.

Enable network connectivity between Docker containers on CentOS 8

date_range 15/08/2020

Featured image

Enable a network connectivity between Docker containers on CentOS 8.

Setup a GitHub repository to serve your Sphinx documentation

date_range 07/04/2020

Featured image

Sphinx and GitHub provide an efficient and free way to publish your documentation online. Here we describe how to do so.