01_16_av_automated_data_profiling_MS_WORD.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# 1. Automated Data Profiling In Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Author : Anandakumar Varatharajah**\n",
    "<br>\n",
    "***http://www.analyticsinsights.ninja***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Version   : 0.17   \n",
    "Date      : 14 July 2019  \n",
    "License   : MIT License"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The main objective of this notebook is **only** to understand raw data profile. i.e. data type, min & max values, ranges, unique values, etc.  \n",
    "In consequent notebooks we will explore further on how to make decisions to make the data tidy and perform the data transformations based on the understanding of the data profile.\n",
    "<br>\n",
    "The code is largely kept generic so that it could be used with any shape of data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Game Changer - Data Profile Dataframe (DPD)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The game changer for exploratory data analysis is the final ***Data Profile Dataframe*** that is generated which combines ***all*** the information required to inform data cleaning, tidy data and optimisations (memory and processing) decisions.  \n",
    "Instead of using various Pandas commands at different instances and going back and forth to cross refer information, Data Profile Dataframe brings all information into a single dataframe. This will be very useful when reviewing the data profile with the business subject matter or other team members as all information related to data profile is in a single easy to understand format.\n",
    "\n",
    "![image.png](https://raw.githubusercontent.com/AnalyticsInsightsNinja/Python_TidyData/master/SAMPLE_FULL_DPD_Image_MSWORD.PNG)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Understanding the data is **the critical step** in preparing the data to be used for analytics. As many experts will point out the data preparation and transforming the data into a tidy format takes about 80% of the effort in any data analytics or data analysis project.<br>\n",
    "***Understanding the data requires good understanding of the domain and/or access to a subject matter expert (SME) to help make decisions about data quality and data usage:***\n",
    "* What are the columns and what do they mean?\n",
    "* How to interpret each columns and possible values of a column?\n",
    "* Should the columns be renamed (and cleaned e.g. trim)?\n",
    "* Are there columns that may have similar information that could be dropped in favour of one master column?\n",
    "* Can columns with no values (or all empty) be dropped?\n",
    "* Can columns which have more than certain threshold of blank values be dropped?\n",
    "* How can the missing values be filled and can it be filled meaningfully?\n",
    "* Can rows that have missing values for certain columns or combination of columns be dropped? i.e. the row is meaningless wihtout those values.\n",
    "* Can the numeric data type columns be converted / down casted to optimise memory usage based on the data values?\n",
    "    - or will there be outliers possibly in future data sets that we cannot do this?\n",
    "    - can the min and max values be used to determine the lowest possible data type?\n",
    "* Can some string/object columns be converted to Category types?\n",
    "    - based on count of unique values\n",
    "* Can any columns be discarded that may not be required for analytics?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Environment setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is recommended best practice to document the execution environment.  \n",
    "e.g. When the initial version of this notebook was developed in Azure Notebooks (Jupyter) the environment was documented in the code. When the notebook was exported to local PC JupyterLab and then imported back into Azure Notebook, the Kernal changed to an older version and some code did not work. Having the initital versions documented in comments saved a lot of effort in trying to understand what went wrong.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the date of execution\n",
    "import datetime\n",
    "date_generated = datetime.datetime.now()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from platform import python_version \n",
    "# use python_version() to get the version. This is used in the final DPD HTML\n",
    "# 3.6.6 in Azure Notebooks in April 2019\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "# use pd.__version__ to get the pandas version. This is used in the final DPD HTML\n",
    "# Pandas version   0.22.0 in Azure Notebooks in April 2019\n",
    "\n",
    "# set maximum number of columns to display in notebook\n",
    "pd.set_option('display.max_columns', 250)\n",
    "\n",
    "# To check whether a column is numeric type\n",
    "from pandas.api.types import is_numeric_dtype\n",
    "\n",
    "# To check whether a column is object/string type\n",
    "from pandas.api.types import is_string_dtype\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the graph packages\n",
    "import matplotlib.pyplot as plt\n",
    "plt.rcParams.update({'figure.max_open_warning': 0})\n",
    "%matplotlib inline\n",
    "\n",
    "import seaborn as sns\n",
    "# Seabotn version   0.9.0 in Azure Notebooks in April 2019\n",
    "# use sns.__version__ to get the pandas version. This is used in the final DPD HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This library is required to generate the MS Word document\n",
    "from docx import Document\n",
    "from docx.shared import Inches, Pt\n",
    "from docx.enum.text import WD_ALIGN_PARAGRAPH  #used to align str(number) in cells "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Raw data file exploration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The raw data file used in this notebook has been derived from the Sales Products csv file from IBM Analytics Community and has been modified to include untidy data for the purposes of this data exploration work.  \n",
    "The raw data should be in a format that can be laoded into pandas. i.e. if there are any rows need to be skipped,  column headers mapped, etc. should be handle in the pandas.read code block."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "\n",
      "  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0\n",
      "100 44227  100 44227    0     0   396k      0 --:--:-- --:--:-- --:--:--  396k\n"
     ]
    }
   ],
   "source": [
    "# Download data file from Github site using curl and save it to local disk   -o \"filename\"\n",
    "!curl -o \"mydataset.csv\" \"https://raw.githubusercontent.com/AnalyticsInsightsNinja/Sample_Analytics_Data/master/titanic.csv\" \n",
    "# Data file to be loaded\n",
    "raw_data_file = \"mydataset.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use Pandas to load the data file into a dataframe\n",
    "try:\n",
    "    df = pd.read_csv(raw_data_file, thousands=',', float_precision=2)\n",
    "except:\n",
    "    print(\"Error: Data file not found!\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note:** If the raw data is a big data file of several GB's in size it may not be possible to load the the whole file into memory. One possibility is using 'pandas pyspark'.<br>\n",
    "Other options to load data incrementally and optimise the data by converting data types will be demonstrated in a seperate notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>Siblings_Spouses_Aboard</th>\n",
       "      <th>Parents_Children Aboard</th>\n",
       "      <th>Fare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>692</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Mr. Charles Henry Chapman</td>\n",
       "      <td>male</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Mr. Bengt Edvin Larsson</td>\n",
       "      <td>male</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Miss. Katherine Gilnagh</td>\n",
       "      <td>female</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>879</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Mr. Frederick James Banfield</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Miss. Laina Heikkinen</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Survived  Pclass                          Name     Sex   Age  \\\n",
       "692         0       2     Mr. Charles Henry Chapman    male  52.0   \n",
       "230         0       3       Mr. Bengt Edvin Larsson    male  29.0   \n",
       "155         1       3       Miss. Katherine Gilnagh  female  16.0   \n",
       "879         0       2  Mr. Frederick James Banfield    male  28.0   \n",
       "2           1       3         Miss. Laina Heikkinen  female  26.0   \n",
       "\n",
       "     Siblings_Spouses_Aboard  Parents_Children Aboard   Fare  \n",
       "692                        0                        0  13.50  \n",
       "230                        0                        0   7.78  \n",
       "155                        0                        0   7.73  \n",
       "879                        0                        0  10.50  \n",
       "2                          0                        0   7.92  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sample raw data rows from dataset\n",
    "df.sample(5).round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Memory Usage Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check whether the file is obatined from url.\n",
    "# If from url, then skip file size in disk check\n",
    "\n",
    "if \"http\" in raw_data_file:\n",
    "    file_size = float('nan')\n",
    "else:\n",
    "    # Calculating file size (in MB) on disk\n",
    "    import os\n",
    "\n",
    "    file_size = (os.stat(raw_data_file).st_size / 1024 **2)\n",
    "    #This is used in the DPD HTML\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate dataset size in memory (MB)\n",
    "df_mem = df.memory_usage(deep=True).sum() / 1024**2\n",
    "#This is used in the DPD HTML\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calclulate dataset size increase in memory (MB)\n",
    "sz_increase = ((df_mem - file_size) / file_size)\n",
    "#This is used in the DPD HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot the memory usage \n",
    "# Create a dictionary from the variables and convert to Pandas DataFrame\n",
    "# Use DataFrame's ploting capabilities\n",
    "raw_data_dict = {\"File on disk\":file_size, \"Dataset in memroy\": df_mem}\n",
    "raw_data_plot = pd.DataFrame.from_dict(raw_data_dict, orient='index').reset_index()\n",
    "\n",
    "# Pandas DataFrame plot\n",
    "raw_data_plot.plot(kind='bar',\\\n",
    "                   x=\"index\" ,\\\n",
    "                   y=0, \\\n",
    "                   legend=False, \\\n",
    "                   title='Data size increase from disk to memory')\n",
    "# plt.subplots_adjust(wspace=0.4, hspace=0.35)\n",
    "plt.xticks(rotation=0)\n",
    "\n",
    "# Save the figure\n",
    "plt.savefig('fig_df_tot_memory.png', dpi=50)\n",
    "plt.close('all')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get memory used by each column in the raw data dataset in MB\n",
    "# This will be later merged with the DPD\n",
    "mem_used_dtypes = pd.DataFrame(df.memory_usage(deep=True) / 1024**2)\n",
    "\n",
    "# Rename column\n",
    "mem_used_dtypes.rename(columns={ 0:'memory'}, inplace=True)\n",
    "\n",
    "# Drop index memory usage since this is not required when merging with Data Quality Dataframe\n",
    "mem_used_dtypes.drop('Index', axis=0, inplace=True) \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Constructing The Data Profile Dataframe (DPD) - The Game Changer "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of rows of the DPD will be the count of columns in the raw date dataframe\n",
    "# Since it there will be one row for each column\n",
    "no_of_rows = len(df.columns)\n",
    "\n",
    "\n",
    "# Constructing the data_qlt_df dataframe and pre-assigning and columns\n",
    "# Pre-assigning the number of rows the dataframe would have is memory and processing efficient\n",
    "# This is a better approach than continuous append or concat operation to dataframe\n",
    "\n",
    "data_qlt_df = pd.DataFrame(index=np.arange(0, no_of_rows), \\\n",
    "                            columns=('column_name', 'col_data_type', 'col_memory','non_null_values', \\\n",
    "                                     'unique_values_count', 'column_dtype')\n",
    "                          )\n",
    "\n",
    "\n",
    "# Add rows to the data_qlt_df dataframe\n",
    "for ind, cols in enumerate(df.columns):\n",
    "    # Count of unique values in the column\n",
    "    col_unique_count = df[cols].nunique()\n",
    "    \n",
    "    data_qlt_df.loc[ind] = [cols, \\\n",
    "                            df[cols].dtype, \\\n",
    "                            mem_used_dtypes['memory'][ind], \\\n",
    "                            df[cols].count(), \\\n",
    "                            col_unique_count, \\\n",
    "                            cols + '~'+ str(df[cols].dtype)\n",
    "                            ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use describe() to get column stats of raw dataframe\n",
    "# This will be merged with the DPD\n",
    "raw_num_df = df.describe().T.round(2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#----- Key Step ---------------\n",
    "# Merging the df.describe() output with rest of the info to create a single Data Profile Dataframe\n",
    "data_qlt_df = pd.merge(data_qlt_df, raw_num_df, how='left', left_on='column_name', right_index=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate percentage of non-null values over total number of values\n",
    "data_qlt_df['%_of_non_nulls'] = (data_qlt_df['non_null_values']/df.shape[0])*100\n",
    "\n",
    "# Calculate null values for the column\n",
    "data_qlt_df['null_values'] = df.shape[0] - data_qlt_df['non_null_values']\n",
    "\n",
    "# Calculate percentage of null values over total number of values\n",
    "data_qlt_df['%_of_nulls'] = 100 - data_qlt_df['%_of_non_nulls']\n",
    "\n",
    "# Calculate percentage of each column memory usage compared to total memory used by raw data datframe\n",
    "data_qlt_df['%_of_total_memory'] = data_qlt_df['col_memory'] / data_qlt_df['col_memory'].sum() * 100\n",
    "\n",
    "# Calculate the total memory used by a given group of data type\n",
    "# See Notes section at the bottom of this notebook for advatages of using 'transform' function with group_by\n",
    "data_qlt_df[\"dtype_total\"] = data_qlt_df.groupby('col_data_type')[\"col_memory\"].transform('sum')\n",
    "\n",
    "# Calculate the percentage memory used by each column data type compared to the total memory used by the group of data type\n",
    "# the above can be merged to one calculation if we do not need the total as separate column\n",
    "#data_qlt_df[\"%_of_dtype_mem2\"] = data_qlt_df[\"Dtype Memory\"] / (data_qlt_df.groupby('Data Type')[\"Dtype Memory\"].transform('sum')) * 100\n",
    "data_qlt_df[\"%_of_dtype_mem\"] = data_qlt_df[\"col_memory\"] / data_qlt_df[\"dtype_total\"] * 100\n",
    "\n",
    "# Calculate the percentage memory used by each group of data type of the total memory used by dataset\n",
    "data_qlt_df[\"dtype_%_total_mem\"] = data_qlt_df[\"dtype_total\"] / df_mem * 100\n",
    "\n",
    "# Calculate the count of each data type\n",
    "data_qlt_df[\"dtype_count\"] = data_qlt_df.groupby('col_data_type')[\"col_data_type\"].transform('count')\n",
    "\n",
    "# Calculate the total count of column values\n",
    "data_qlt_df[\"count\"] = data_qlt_df['null_values'] + data_qlt_df['non_null_values']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reorder the Data Profile Dataframe columns\n",
    "data_qlt_df = data_qlt_df[\n",
    "                            ['column_name', 'col_data_type', 'col_memory', '%_of_dtype_mem', '%_of_total_memory',\\\n",
    "                             'dtype_count', 'dtype_total', 'dtype_%_total_mem', 'non_null_values', '%_of_non_nulls',\\\n",
    "                             'null_values', '%_of_nulls', 'unique_values_count', 'count', 'mean', 'std', 'min', '25%',\\\n",
    "                             '50%', '75%', 'max']\n",
    "                         ]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The above data quality data frame summarises all information required for making data quality decisions.**  \n",
    "Though there are info() and describe() methods to do these, having all the relvant information in one dataframe makes the data quality exploration much easier. This dataframe can be used for summarising information and for plotting to ehnace the ease of Data Understanding effort."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plot Memory Usage Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot count of column data types and memory used by each datatype\n",
    "plt_dtype = data_qlt_df.groupby('col_data_type')['dtype_count', 'dtype_total', 'dtype_%_total_mem'].last().sort_values(by='dtype_count')\n",
    "\n",
    "fig1, (ax, ax2) = plt.subplots(ncols=2, figsize=(10,5))\n",
    "plt.subplots_adjust(wspace=0.4, hspace=0.35, bottom=0.20)\n",
    "\n",
    "plt_dtype.plot(kind='bar', y='dtype_count',  use_index=True, legend=False, ax=ax, title='Count of columns by data type')\n",
    "\n",
    "plt_dtype.plot(kind='bar', y='dtype_total',  use_index=True, legend=False, ax=ax2, title='Memory used by data type')\n",
    "\n",
    "fig1.savefig(\"fig_cols_memory.png\", dpi=50)\n",
    "plt.close('all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Memory used by columns of raw data dataframe\n",
    "fig2, ax = plt.subplots(ncols=1, figsize=(15,5))\n",
    "plt.subplots_adjust(wspace=0.4, hspace=0.35, bottom=0.30)\n",
    "\n",
    "# Memory used by object data type\n",
    "(data_qlt_df[data_qlt_df['col_data_type'] == 'object']\n",
    " .sort_values(by='col_memory', ascending=False)\n",
    " .plot(kind=\"bar\", \n",
    "       x=\"column_name\", \n",
    "       y=\"col_memory\", \n",
    "       title=\"Memory (MB) usage by columns of object data type\",\n",
    "      legend=False, ax=ax)\n",
    ")\n",
    "plt.xticks(rotation=35)\n",
    "fig2.savefig(\"fig_object_cols_memory.png\", dpi=50)\n",
    "plt.close('all')\n",
    "\n",
    "# Memory used by non-object data type\n",
    "fig2, ax1 = plt.subplots(ncols=1, figsize=(15,5))\n",
    "plt.subplots_adjust(wspace=0.4, hspace=0.35, bottom=0.30)\n",
    "\n",
    "(data_qlt_df[data_qlt_df['col_data_type'] != 'object']\n",
    " .sort_values(by='col_memory', ascending=False)\n",
    " .plot(kind=\"bar\", \n",
    "       x=\"column_name\", \n",
    "       y=\"col_memory\", \n",
    "       title=\"Memory (MB) usage by columns of non-object data type\",\n",
    "      legend=False, ax=ax1)\n",
    ")\n",
    "plt.xticks(rotation=35)\n",
    "\n",
    "fig2.savefig(\"fig_non_object_cols_memory.png\", dpi=50)\n",
    "plt.close('all')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate data profile graphs for 'numerical' columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1  of  6  completed    Survived\n",
      "2  of  6  completed    Pclass\n",
      "3  of  6  completed    Age\n",
      "4  of  6  completed    Siblings_Spouses_Aboard\n",
      "5  of  6  completed    Parents_Children Aboard\n",
      "6  of  6  completed    Fare\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "from matplotlib.patches import Rectangle\n",
    "\n",
    "# Get the list of numeric columns from raw dataframe\n",
    "# need this: from pandas.api.types import is_numeric_dtype\n",
    "# get numeric columns which are not empty\n",
    "num_cols = [cols for cols in df.columns if is_numeric_dtype(df[cols]) and len(df[cols].dropna())>0]\n",
    "\n",
    "iter_len = len(num_cols)\n",
    "\n",
    "# For each numeric column in the list\n",
    "for x, col_name in enumerate(num_cols):\n",
    "    print(x+1, \" of \", iter_len, \" completed   \",  col_name)\n",
    "    \n",
    "    # Create a copy of the column values without nulls or NA\n",
    "    no_null_col = df[col_name].dropna()\n",
    "    \n",
    "    \n",
    "    # Calculate the 95 percentile of the values\n",
    "    q25 = np.percentile(no_null_col, 25)\n",
    "    q75 = np.percentile(no_null_col, 75)    \n",
    "    q95 = np.percentile(no_null_col, 95)\n",
    "    \n",
    "    # Plot the graphs\n",
    "    fig3 = plt.figure(figsize=(20,15))\n",
    "    fig3.suptitle(\"Profile of column  \" + col_name, fontsize=25)  #Title for the whole figure\n",
    "    plt.subplots_adjust(wspace=0.4, hspace=0.35)\n",
    "\n",
    "    ax1 = fig3.add_subplot(2,3,1)\n",
    "    ax1.set_title(\"Box plot for all the values\", fontsize=20)\n",
    "    plt.setp(ax1.get_xticklabels(), ha=\"right\", rotation=35)\n",
    "    plt.setp(ax1.get_yticklabels(), ha=\"right\", fontsize=15)\n",
    "    ax1.boxplot(no_null_col)\n",
    "\n",
    "    ax1 = fig3.add_subplot(2,3,2)\n",
    "    ax1.set_title(\"Distribution of all values\", fontsize=20)\n",
    "    plt.setp(ax1.get_xticklabels(), ha=\"right\", rotation=35, fontsize=15)\n",
    "    plt.setp(ax1.get_yticklabels(), ha=\"right\", fontsize=15)\n",
    "    ax1.hist(no_null_col)\n",
    "\n",
    "    ax1 = fig3.add_subplot(2,3,3)\n",
    "    ax1.set_title(\"Boxplot for quartiles (all values)\", fontsize=20)\n",
    "    if len(no_null_col.value_counts()) >= 4:\n",
    "        df[u'quartiles'] = pd.qcut(\n",
    "                        df[col_name],\n",
    "                        4, duplicates='drop')\n",
    "        df.boxplot(column= col_name, by=u'quartiles', ax = ax1)\n",
    "    plt.setp(ax1.get_xticklabels(), ha=\"right\", rotation=35, fontsize=15)\n",
    "    plt.setp(ax1.get_yticklabels(), ha=\"right\", fontsize=15)\n",
    "\n",
    "    ax1 = fig3.add_subplot(2,3,4)\n",
    "    ax1.set_title(\"Box plot without outliers\", fontsize=20)\n",
    "    plt.setp(ax1.get_xticklabels(), ha=\"right\", rotation=35, fontsize=15)\n",
    "    plt.setp(ax1.get_yticklabels(), ha=\"right\", fontsize=15)\n",
    "    ax1.boxplot(no_null_col, showfliers=False)\n",
    "\n",
    "    ax1 = fig3.add_subplot(2,3,5)\n",
    "    ax1.set_title(\"Violin plot (<95% percentile)\", fontsize=20)\n",
    "    plt.setp(ax1.get_xticklabels(), ha=\"right\", rotation=35, fontsize=15)\n",
    "    plt.setp(ax1.get_yticklabels(), ha=\"right\", fontsize=15)\n",
    "    ax1.violinplot(no_null_col[no_null_col <= q95])\n",
    "\n",
    "    \n",
    "    #Histogram with bin ranges, counts and percentile color\n",
    "    ax1 = fig3.add_subplot(2,3,6)\n",
    "    ax1.set_title(\"Histogram (<95% percentile)\", fontsize=20)\n",
    "    plt.setp(ax1.get_xticklabels(), ha=\"right\", rotation=35, fontsize=15)\n",
    "    plt.setp(ax1.get_yticklabels(), ha=\"right\", fontsize=15)\n",
    "\n",
    "    # Take only the data less than 95 percentile\n",
    "    data = no_null_col[no_null_col <= q95]\n",
    "\n",
    "    # Colours for different percentiles\n",
    "    perc_25_colour = 'gold'\n",
    "    perc_50_colour = 'mediumaquamarine'\n",
    "    perc_75_colour = 'deepskyblue'\n",
    "    perc_95_colour = 'peachpuff'\n",
    "\n",
    "    '''\n",
    "    counts  = numpy.ndarray of count of data ponts for each bin/column in the histogram\n",
    "    bins    = numpy.ndarray of bin edge/range values\n",
    "    patches = a list of Patch objects.\n",
    "            each Patch object contains a Rectnagle object. \n",
    "            e.g. Rectangle(xy=(-2.51953, 0), width=0.501013, height=3, angle=0)\n",
    "    '''\n",
    "    counts, bins, patches = ax1.hist(data, bins=10, facecolor=perc_50_colour, edgecolor='gray')\n",
    "\n",
    "    # Set the ticks to be at the edges of the bins.\n",
    "    ax1.set_xticks(bins.round(2))\n",
    "    plt.xticks(rotation=70, fontsize=15)\n",
    "\n",
    "    # Change the colors of bars at the edges\n",
    "    for patch, leftside, rightside in zip(patches, bins[:-1], bins[1:]):\n",
    "        if rightside < q25:\n",
    "            patch.set_facecolor(perc_25_colour)\n",
    "        elif leftside > q95:\n",
    "            patch.set_facecolor(perc_95_colour)\n",
    "        elif leftside > q75:\n",
    "            patch.set_facecolor(perc_75_colour)\n",
    "\n",
    "    # Calculate bar centre to display the count of data points and %\n",
    "    bin_x_centers = 0.5 * np.diff(bins) + bins[:-1]\n",
    "    bin_y_centers = ax1.get_yticks()[1] * 0.25\n",
    "\n",
    "    # Display the the count of data points and % for each bar in histogram\n",
    "    for i in range(len(bins)-1):\n",
    "        bin_label = \"{0:,}\".format(counts[i]) + \"  ({0:,.2f}%)\".format((counts[i]/counts.sum())*100)\n",
    "        plt.text(bin_x_centers[i], bin_y_centers, bin_label, rotation=90, rotation_mode='anchor')\n",
    "\n",
    "    #create legend\n",
    "    handles = [Rectangle((0,0),1,1,color=c,ec=\"k\") for c in [perc_25_colour, perc_50_colour, perc_75_colour, perc_95_colour]]\n",
    "    labels= [\"0-25 Percentile\",\"25-50 Percentile\", \"50-75 Percentile\", \">95 Percentile\"]\n",
    "    plt.legend(handles, labels, bbox_to_anchor=(0.5, 0., 0.85, 0.99))\n",
    "    \n",
    "\n",
    "    fig3.suptitle(\"Profile of column  \" + col_name, fontsize=25)  #Title for the whole figure\n",
    "    fig_name = 'fig_' + col_name\n",
    "    fig3.savefig(fig_name, dpi=50)\n",
    "    plt.close('all')\n",
    "    \n",
    "#     plt.show()\n",
    "\n",
    "df.drop(u'quartiles', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate data profile graphs for 'object' columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1  of  2  completed    Name\n",
      "2  of  2  completed    Sex\n"
     ]
    }
   ],
   "source": [
    "# Get the list of object columns from raw dataframe\n",
    "# get object columns which are not empty\n",
    "obj_cols = [cols for cols in df.columns if is_string_dtype(df[cols]) and len(df[cols].dropna())>0]\n",
    "\n",
    "iter_len = len(obj_cols)\n",
    "\n",
    "\n",
    "# For each object column in the list\n",
    "for x, col_name in enumerate(obj_cols):\n",
    "    print(x+1, \" of \", iter_len, \" completed   \",  col_name)\n",
    "    \n",
    "    # Create a copy of the column values without nulls or NA\n",
    "    no_null_col = df[col_name].dropna()\n",
    "\n",
    "    values_freq_threshold = 25\n",
    "    col_unique_count = df[col_name].nunique()\n",
    "    \n",
    "    # If unique values count is below the threshold value then store the details of unique values\n",
    "    col_unique_vals = df[col_name].value_counts(normalize=True, sort=True)\n",
    "    \n",
    "    # Plot the graphs\n",
    "    fig4 = plt.figure(figsize=(20,7))\n",
    "    fig4.suptitle(\"Profile of column  \" + col_name, fontsize=25)  #Title for the whole figure\n",
    "    plt.subplots_adjust(wspace=0.4, hspace=0.35, bottom=0.35)\n",
    "\n",
    "    ax1 = fig4.add_subplot(1,1,1)\n",
    "    ax1.set_title(\"Bar chart for top 25 values\", fontsize=20)\n",
    "    plt.setp(ax1.get_xticklabels(), ha=\"right\", rotation=45, fontsize=15)\n",
    "    plt.setp(ax1.get_yticklabels(), ha=\"right\", fontsize=15)\n",
    "    \n",
    "    col_unique_vals.head(values_freq_threshold).sort_values(ascending=False).plot.bar()\n",
    "    plt.xticks(rotation=75)\n",
    "    for p in ax1.patches:\n",
    "        ax1.annotate(str(round(p.get_height(),2)), (p.get_x() * 1.005, p.get_height() * 1.005), fontsize=15)\n",
    "    \n",
    "    fig4.suptitle(\"Profile of column  \" + col_name, fontsize=25)  #Title for the whole figure\n",
    "    fig_name = 'fig_' + col_name\n",
    "    fig4.savefig(fig_name, dpi= 50)\n",
    "\n",
    "    plt.close('all')\n",
    "#     plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Candidate columns for Category type"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Analysing how many unique values an 'object' column has will be useful to detrmine which columns are good candidates for *Categorical* data type. In combination with the total memory used by 'object' data type and each 'object' data type column, decisions can be made on converting them Category type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a df and a column for % of memory by each object column\n",
    "cardn_df = data_qlt_df[data_qlt_df['col_data_type'] == 'object'][['column_name', 'col_memory', '%_of_dtype_mem', '%_of_total_memory', 'unique_values_count']]\n",
    "\n",
    "cardn_df = cardn_df.sort_values('unique_values_count')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Candidate columns for down casting type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a df and a column for % of memory by each object column\n",
    "num_cardn_df = data_qlt_df[data_qlt_df['col_data_type'] != 'object'][['column_name', 'col_memory', '%_of_dtype_mem', '%_of_total_memory', 'unique_values_count']]\n",
    "\n",
    "num_cardn_df = num_cardn_df.sort_values('unique_values_count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Columns with high percentage of null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The empty values threshold can be set to a lower/higher value depending on the size of the data sets \n",
    "threshold_perc = 0.75\n",
    "col_vals_threshold = df.shape[0] * threshold_perc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "null_vals_df = data_qlt_df[data_qlt_df['non_null_values'] < col_vals_threshold][['column_name', 'col_data_type', 'col_memory', 'non_null_values', '%_of_non_nulls', 'null_values', '%_of_nulls']]\n",
    "\n",
    "# .style.format({'dtype_memory': \"{:,.2f}\", 'non_null_values': \"{:,.2f}\", '%_of_non_nulls': \"{:,.2f}\", 'null_values': \"{:,.2f}\", '%_of_nulls': \"{:,.2f}\",  'unique_values_count': \"{:,.2f}\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate the Correlation plot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "f, ax = plt.subplots(figsize=(15, 10))\n",
    "plt.subplots_adjust(bottom=0.35)\n",
    "plt.autoscale()\n",
    "\n",
    "corr_data = df.corr()\n",
    "sns.heatmap(corr_data,\n",
    "            mask=np.zeros_like(corr_data, dtype=np.bool), \n",
    "            cmap=sns.diverging_palette(20, 220, as_cmap=True),\n",
    "            vmin=-1, vmax=1,\n",
    "            square=True, \n",
    "            ax=ax)\n",
    "\n",
    "fig_name = 'fig_cor_plot.png'\n",
    "f.savefig(fig_name,  dpi=70)\n",
    "# plt.show()\n",
    "plt.close('all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing the image for the document"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "\n",
    "image_url = \"https://raw.githubusercontent.com/AnalyticsInsightsNinja/Python_TidyData/master/SAMPLE_FULL_DPD_Image_MSWORD.PNG\"\n",
    "\n",
    "Picture_request = requests.get(image_url)\n",
    "if Picture_request.status_code == 200:\n",
    "    with open(\"msword_output.jpg\", 'wb') as f:\n",
    "        f.write(Picture_request.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Construct the MS Word document"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make sure you have the docx package and it is imported\n",
    "# see the environment setup section\n",
    "\n",
    "#Create Document object\n",
    "document = Document()\n",
    "\n",
    "# Add Title\n",
    "document.add_heading('Data Profile Dataframe - Notebook v0.17 - 14 July 2019', 0)\n",
    "document.add_heading(raw_data_file, 0)\n",
    "\n",
    "# COver page paragraph\n",
    "p = document.add_paragraph('The main objective of this notebook is ')\n",
    "p.add_run('only').bold = True\n",
    "p.add_run(' to understand raw data profile. i.e. data type, min & max values, ranges, unique values, etc.')\n",
    "p = document.add_paragraph('In consequent notebooks we will explore further on how to make decisions to make \\\n",
    "the data tidy and perform the data transformations based on the understanding of the data profile.')\n",
    "p = document.add_paragraph('')\n",
    "p.add_run('The code is largely kept generic so that it could be used with any shape of data.').italic = True\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<docx.text.paragraph.Paragraph at 0x22b0988c390>"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Page 2\n",
    "document.add_page_break()\n",
    "# Heading 1\n",
    "document.add_heading('The Game Changer - Data Profile Dataframe (DPD)', level=1)\n",
    "p = document.add_paragraph('The game changer for exploratory data analysis is the final')\n",
    "p.add_run(' Data Profile Dataframe').bold = True\n",
    "p.add_run(' that is generated which combines ')\n",
    "p.add_run('all').bold = True\n",
    "p.add_run(' the information required to inform data cleaning, tidy data and optimisations (memory and processing) decisions.\\\n",
    " Instead of using various Pandas commands at different instances and going back and forth to cross refer information, Data Profile Dataframe brings all information into a single dataframe.\\\n",
    " This will be very useful when reviewing the data profile with the business subject matter or other team members as all information related to data profile is in a single easy to understand format.')\n",
    "\n",
    "document.add_picture('msword_output.jpg', height=Inches(4), width=Inches(4))\n",
    "\n",
    "document.add_page_break()\n",
    "p = document.add_paragraph('Understanding the data is ')\n",
    "p.add_run('the critical step').bold = True\n",
    "p.add_run(' in preparing the data to be used for analytics.\\\n",
    " As many experts will point out the data preparation and transforming the data into a tidy format takes about 80% of the effort in any data analytics or data analysis project.')\n",
    "p = document.add_paragraph('')\n",
    "p.add_run('Understanding the data requires good understanding of the domain and/or access to a subject\\\n",
    "matter expert (SME) to help make decisions about data quality and data usage:').bold = True\n",
    "\n",
    "document.add_paragraph(\n",
    "    'What are the columns and what do they mean?', style='List Bullet'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'How to interpret each columns and possible values of a column?', style='List Bullet'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'Should the columns be renamed (and cleaned e.g. trim)?', style='List Bullet'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'Are there columns that may have similar information that could be dropped in favour of one master column?', style='List Bullet'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'Can columns with no values (or all empty) be dropped?', style='List Bullet'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'Can columns which have more than certain threshold of blank values be dropped?', style='List Bullet'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'Can rows that have missing values for certain columns or combination of columns be dropped?', style='List Bullet'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'i.e. the row is meaningless wihtout those values.', style='List Continue'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'Can the numeric data type columns be converted / down casted to optimise memory usage based on the data values?', style='List Bullet'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'or will there be outliers possibly in future data sets that we cannot do this?', style='List Bullet 2'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'Can the min and max values be used to determine the lowest possible data type?', style='List Bullet 2'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'Can some string/object columns be converted to Category types?', style='List Bullet'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'based on count of unique values', style='List Bullet 2'\n",
    ")\n",
    "document.add_paragraph(\n",
    "    'Can any columns be discarded that may not be required for analytics?', style='List Bullet'\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Word - Data profile summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<docx.text.paragraph.Paragraph at 0x22b11c0d438>"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document.add_page_break()\n",
    "document.add_heading('Columns Data Profile Summary', 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Page 4\n",
    "p = document.add_paragraph(' ')\n",
    "\n",
    "# Heading 1\n",
    "document.add_heading('Dataset shape', level=1)\n",
    "\n",
    "table = document.add_table(rows=2, cols=2, style = 'Medium Shading 1 Accent 3')\n",
    "\n",
    "# Header row\n",
    "cell = table.cell(0, 0)\n",
    "cell.text = 'No.of rows'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = True\n",
    "\n",
    "cell = table.cell(0, 1)\n",
    "cell.text = 'No.of columns'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = True\n",
    "\n",
    "# Values\n",
    "cell = table.cell(1, 0)\n",
    "cell.text = F'{df.shape[0] :,}'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = False\n",
    "\n",
    "cell = table.cell(1, 1)\n",
    "cell.text = F'{df.shape[1] :,}'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = False\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Page 4a\n",
    "# document.add_page_break()\n",
    "p = document.add_paragraph(' ')\n",
    "\n",
    "# Heading 1\n",
    "document.add_heading('Dataframe columns summary', level=1)\n",
    "\n",
    "# Rehsape the column data type dataframe into form that can be printed in MS Word\n",
    "data = round(data_qlt_df[['column_name','col_data_type', 'non_null_values', 'null_values', 'count']], 2)\n",
    "\n",
    "# add a table to the end and create a reference variable\n",
    "# extra row is so we can add the header row\n",
    "table = document.add_table(data.shape[0]+1, data.shape[1], style='Medium Shading 1 Accent 3')\n",
    "\n",
    "# add the header rows.\n",
    "for j in range(data.shape[1]):\n",
    "\n",
    "    #header row first two columns\n",
    "    if j <= 1:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "    else:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "        cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT\n",
    "        \n",
    "    \n",
    "# add the rest of the data frame\n",
    "for i in range(data.shape[0]):\n",
    "    for j in range(data.shape[1]):\n",
    "        if j <= 1:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j]}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False            \n",
    "        else:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j] :,}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False  \n",
    "            cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT           \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Word - Column memory usage profile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<docx.text.paragraph.Paragraph at 0x22b11c2f9b0>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document.add_page_break()\n",
    "document.add_heading('Memory Usage Profile', 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<docx.shape.InlineShape at 0x22b11c30c18>"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Page 5\n",
    "p = document.add_paragraph(' ')\n",
    "\n",
    "# Heading 1\n",
    "document.add_heading('Data file size on disk vs. dataset size in memory', level=1)\n",
    "\n",
    "# Create table\n",
    "table = document.add_table(rows=3, cols=2, style = 'Medium Shading 1 Accent 3')\n",
    "\n",
    "# Add column headers\n",
    "cell = table.cell(0,0)\n",
    "cell.text = 'Description'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = True  \n",
    "\n",
    "cell = table.cell(0,1)\n",
    "cell.text = 'Size in MB'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = True  \n",
    "cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT \n",
    "\n",
    "# Add values : Value Line 1\n",
    "cell = table.cell(1,0)\n",
    "cell.text = 'Data file size on disk'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = False  \n",
    "\n",
    "cell = table.cell(1,1)\n",
    "cell.text = F'{round(file_size, 2)  :,.2f}'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = False  \n",
    "cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT \n",
    "\n",
    "# Add values : Value Line 2\n",
    "cell = table.cell(2,0)\n",
    "cell.text = 'Dataset size in memory'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = False  \n",
    "\n",
    "cell = table.cell(2,1)\n",
    "cell.text = F'{round(df_mem, 2)  :,.2f}'\n",
    "cell_font = cell.paragraphs[0].runs[0].font\n",
    "cell_font.size = Pt(11)\n",
    "cell_font.bold = False  \n",
    "cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT \n",
    "\n",
    "# Memory increase\n",
    "p = document.add_paragraph('')\n",
    "p = document.add_paragraph('Dataset increase in memory :  ')\n",
    "p.add_run(str(round(sz_increase*100, 2)) + '%').bold = True\n",
    "\n",
    "# Add graph\n",
    "document.add_picture('fig_df_tot_memory.png', height=Inches(3), width=Inches(3))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Page 6\n",
    "document.add_page_break()\n",
    "\n",
    "# Heading 1\n",
    "document.add_heading('Dataframe column types and size in memory', level=1)\n",
    "\n",
    "# Rehsape the column data type dataframe into form that can be printed in MS Word\n",
    "# Using .reset_index() will make the index a column\n",
    "data = round(plt_dtype.reset_index(), 2)\n",
    "\n",
    "\n",
    "# add a table to the end and create a reference variable\n",
    "# extra row is so we can add the header row\n",
    "table = document.add_table(data.shape[0]+1, data.shape[1], style = 'Medium Shading 1 Accent 3')\n",
    "\n",
    "# add the header rows.\n",
    "for j in range(data.shape[1]):\n",
    "    #header row first first columns\n",
    "    if j == 0:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "    else:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "        cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT\n",
    "        \n",
    "    \n",
    "# add the rest of the data frame\n",
    "for i in range(data.shape[0]):\n",
    "    for j in range(data.shape[1]):\n",
    "        if j == 0:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j]}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False            \n",
    "        else:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j] :,.2f}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False  \n",
    "            cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT           \n",
    "\n",
    "\n",
    "p = document.add_paragraph(' ')\n",
    "p = document.add_paragraph('\"col_data_type\" : Column data type')\n",
    "p = document.add_paragraph('\"dtype_count\" : Number of oclumns in the dataset of the given data type')\n",
    "p = document.add_paragraph('\"dtype_total\" : Total memory in MB for the given data type')\n",
    "p = document.add_paragraph('\"dtype_%_total_mem\" : Percentage of the memory used by the given data type out of the total memory used by the dataset')\n",
    "\n",
    "document.add_picture('fig_cols_memory.png', height=Inches(3), width=Inches(6))\n",
    "\n",
    "p = document.add_paragraph('In a memory heavy datasets the above information can shed light into which data type you need to focus if you need to optimise the memory usage.')\n",
    "p = document.add_paragraph('e.g. may be convert \"object\" datatype to \"category\" type if the cardinality is low or may be down cast \"float64\" to float16 or smaller.')\n",
    "p = document.add_paragraph('These decision need further information on column cardinality and max/min values which are covered in the next few sections.')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Page 7\n",
    "document.add_page_break()\n",
    "\n",
    "# Heading 1\n",
    "document.add_heading('Memory used by \"object\" data type', level=1)\n",
    "\n",
    "\n",
    "# Rehsape the column data type dataframe into form that can be printed in MS Word\n",
    "# Using .reset_index() will make the index a column\n",
    "data = round(cardn_df.sort_values(\"unique_values_count\"), 2)\n",
    "\n",
    "\n",
    "# add a table to the end and create a reference variable\n",
    "# extra row is so we can add the header row\n",
    "table = document.add_table(data.shape[0]+1, data.shape[1], style = 'Medium Shading 1 Accent 3')\n",
    "\n",
    "# add the header rows.\n",
    "for j in range(data.shape[1]):\n",
    "    #header row first first columns\n",
    "    if j == 0:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "    else:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "        cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT\n",
    "        \n",
    "    \n",
    "# add the rest of the data frame\n",
    "for i in range(data.shape[0]):\n",
    "    for j in range(data.shape[1]):\n",
    "        if j == 0:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j]}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False            \n",
    "        else:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j] :,.2f}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False  \n",
    "            cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT           \n",
    "\n",
    "\n",
    "p = document.add_paragraph(' ')\n",
    "p = document.add_paragraph('\"column_name\" : Name of the column in the dataframe')\n",
    "p = document.add_paragraph('\"col_memory\" : Memory used by the given column')\n",
    "p = document.add_paragraph('\"%_of_dtype_mem\" : Percentage of memory used by the given column out of memory used by the column data type')\n",
    "p = document.add_paragraph('\"%_of_total_memory\" : Percentage of the memory used by the given column out of the total memory used by the dataset')\n",
    "p = document.add_paragraph('\"unique_values_count\" : Count of the unique values for the given column')\n",
    "\n",
    "document.add_picture('fig_object_cols_memory.png', height=Inches(3), width=Inches(6))\n",
    "\n",
    "p = document.add_paragraph(' ')\n",
    "p = document.add_paragraph(\"Analysing how many unique values an 'object' column has will be useful to detrmine\\\n",
    "which columns are good candidates for *Categorical* data type. In combination with the total memory used by 'object'\\\n",
    "data type and each 'object' data type column, decisions can be made on converting them Category type.\\\n",
    "Object or string data type columns with low cardinality is suitable for Category type.\")\n",
    "p.add_run(\"The threshold of 'low cardinality' depends on the domain of the data and data usage patterns.\").bold = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Page 8\n",
    "document.add_page_break()\n",
    "\n",
    "# Heading 1\n",
    "document.add_heading('Memory used by \"Non-Object\" data type', level=1)\n",
    "\n",
    "# Rehsape the column data type dataframe into form that can be printed in MS Word\n",
    "# Using .reset_index() will make the index a column\n",
    "data = round(num_cardn_df.sort_values(\"unique_values_count\"), 2)\n",
    "\n",
    "# add a table to the end and create a reference variable\n",
    "# extra row is so we can add the header row\n",
    "table = document.add_table(data.shape[0]+1, data.shape[1], style = 'Medium Shading 1 Accent 3')\n",
    "\n",
    "# add the header rows.\n",
    "for j in range(data.shape[1]):\n",
    "    #header row first first columns\n",
    "    if j == 0:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "    else:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "        cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT\n",
    "        \n",
    "    \n",
    "# add the rest of the data frame\n",
    "for i in range(data.shape[0]):\n",
    "    for j in range(data.shape[1]):\n",
    "        if j == 0:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j]}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False            \n",
    "        else:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j] :,.2f}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False  \n",
    "            cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT           \n",
    "\n",
    "\n",
    "p = document.add_paragraph(' ')\n",
    "p = document.add_paragraph('\"column_name\" : Name of the column in the dataframe')\n",
    "p = document.add_paragraph('\"col_memory\" : Memory used by the given column')\n",
    "p = document.add_paragraph('\"%_of_dtype_mem\" : Percentage of memory used by the given column out of memory used by the column data type')\n",
    "p = document.add_paragraph('\"%_of_total_memory\" : Percentage of the memory used by the given column out of the total memory used by the dataset')\n",
    "\n",
    "document.add_picture('fig_non_object_cols_memory.png', height=Inches(3), width=Inches(6))\n",
    "\n",
    "p = document.add_paragraph(' ')\n",
    "p = document.add_paragraph(\"By analysing the min and max values of the numeric columns decions can be made to downcast the data type to more memory efficient storage types.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Page 9\n",
    "document.add_page_break()\n",
    "\n",
    "# Heading 1\n",
    "document.add_heading('Columns with non-null values less than ' + \"{:,.2f}\".format(threshold_perc*100) + '%', level=1)\n",
    "\n",
    "p = document.add_paragraph('The columns should contain at least  ' + \"{:,.0f}\".format(col_vals_threshold) + '  (' + \"{:,.2f}\".format((col_vals_threshold/df.shape[0])*100) + '%) non-empty rows out of  '+ \"{:,}\".format(df.shape[0]) + ' rows to be considered useful.')\n",
    "p = document.add_paragraph('The non-empty values threshold can be set using the threshold_perc variable in the code.')\n",
    "\n",
    "\n",
    "# Rehsape the column data type dataframe into form that can be printed in MS Word\n",
    "# Using .reset_index() will make the index a column\n",
    "data = round(null_vals_df.sort_values(\"non_null_values\"), 2)\n",
    "\n",
    "# add a table to the end and create a reference variable\n",
    "# extra row is so we can add the header row\n",
    "table = document.add_table(data.shape[0]+1, data.shape[1], style = 'Medium Shading 1 Accent 3')\n",
    "\n",
    "# add the header rows.\n",
    "for j in range(data.shape[1]):\n",
    "    #header row first first columns\n",
    "    if j <= 1:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "    else:\n",
    "        cell = table.cell(0, j)\n",
    "        cell.text = F'{data.columns[j]}'\n",
    "        cell_font = cell.paragraphs[0].runs[0].font\n",
    "        cell_font.size = Pt(11)\n",
    "        cell_font.bold = True\n",
    "        cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT\n",
    "        \n",
    "    \n",
    "# add the rest of the data frame\n",
    "for i in range(data.shape[0]):\n",
    "    for j in range(data.shape[1]):\n",
    "        if j <= 1:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j]}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False            \n",
    "        else:\n",
    "            cell = table.cell(i+1, j)\n",
    "            cell.text = F'{data.values[i,j] :,.2f}'\n",
    "            cell_font = cell.paragraphs[0].runs[0].font\n",
    "            cell_font.size = Pt(11)\n",
    "            cell_font.bold = False  \n",
    "            cell.paragraphs[0].alignment= WD_ALIGN_PARAGRAPH.RIGHT           \n",
    "        \n",
    "\n",
    "\n",
    "p = document.add_paragraph(' ')\n",
    "p = document.add_paragraph('\"column_name\" : Name of the column in the dataframe')\n",
    "p = document.add_paragraph('\"col_data_type\" : Data type of the given column')\n",
    "p = document.add_paragraph('\"col_memory\" : Memory used by the given column')\n",
    "p = document.add_paragraph('\"non_null_values\" : Count of non-null values in the given column')\n",
    "p = document.add_paragraph('\"%_of_non_nulls\" : Percentage of the non-null values out of total values for the given column')\n",
    "p = document.add_paragraph('\"null_values\" : Count of null values in the given column')\n",
    "p = document.add_paragraph('\"%_of_nulls\" : Percentage of the null values out of total values for the given column')\n",
    "\n",
    "p = document.add_paragraph(' ')\n",
    "p = document.add_paragraph(\"Generally columns with large percentage of empty values can be *dropped* from the dataset as they will not add any value to the analysis.\")\n",
    "p = document.add_paragraph('')\n",
    "p.add_run('But this depends on the domian of the dataset and usage pattern of the columns/data.').bold = True\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Word - Data Correlation plot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<docx.shape.InlineShape at 0x22b09907240>"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document.add_page_break()\n",
    "document.add_heading('Data correlation plot', 0)\n",
    "\n",
    "p = document.add_paragraph('')\n",
    "\n",
    "document.add_picture('fig_cor_plot.png', height=Inches(6), width=Inches(6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Word - Create the detail column profile rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<docx.text.paragraph.Paragraph at 0x22b11c30a20>"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document.add_page_break()\n",
    "document.add_heading('Column Data Profile Details', 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ind = 1  # to be taken from iterrows loop later\n",
    "for ind in range(data_qlt_df.shape[0]):\n",
    "    document.add_page_break()\n",
    "    \n",
    "    # Create table for column profile details\n",
    "    table = document.add_table(rows=6, cols=6, style = 'Medium Shading 1 Accent 3' )\n",
    "    \n",
    "    # Merge cells in header row for COlumn Name\n",
    "    for y in range(len(table.rows[0].cells)-1):\n",
    "        a = table.cell(0,y)\n",
    "        b = table.cell(0,y+1)\n",
    "        a.merge(b)\n",
    "\n",
    "    # Merge cells in detail rows spanning 2 cells x 3 \n",
    "    for row in range(1,6):\n",
    "        a = table.cell(row,0)\n",
    "        b = table.cell(row,1)\n",
    "        a.merge(b)\n",
    "        a = table.cell(row,2)\n",
    "        b = table.cell(row,3)\n",
    "        a.merge(b)\n",
    "        a = table.cell(row,4)\n",
    "        b = table.cell(row,5)\n",
    "        a.merge(b)\n",
    "\n",
    "\n",
    "    #*** ADD VALUES TO TABLE  ***#\n",
    "    # Cell 0,0 (merged 6 cells): Header - Column Name\n",
    "    cell = table.cell(0, 0)\n",
    "    cell.text = data_qlt_df[\"column_name\"][ind]\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(15)\n",
    "    cell_font.bold = True\n",
    "\n",
    "    # Cell 1,0: Blank\n",
    "    cell = table.cell(1, 1)\n",
    "    cell.text = \"TBD Column :\\n\"\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run('no value')\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(12)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 1,0: Column data type\n",
    "    cell = table.cell(1, 3)\n",
    "    cell.text = 'Data Type : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(str(data_qlt_df[\"col_data_type\"][ind]))\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(12)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 1,1: Count of toal values in the column\n",
    "    cell = table.cell(1, 5)\n",
    "    cell.text = 'Values Count : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"count\"][ind] :,.0f}')\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 2,0: Count of unique values in the column\n",
    "    cell = table.cell(2, 1)\n",
    "    cell.text = 'Unique Values Count : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    unique_per = (data_qlt_df[\"unique_values_count\"][ind] / data_qlt_df[\"count\"][ind]) * 100\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"unique_values_count\"][ind] :,.0f}' + \"   \" + F'({unique_per :,.2f}%)' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 2,1: Count of non-null values in the column\n",
    "    cell = table.cell(2, 3)\n",
    "    cell.text = 'Non-Null Values Count : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"non_null_values\"][ind] :,.0f}' + \"   \" + F' ({data_qlt_df[\"%_of_non_nulls\"][ind]  :,.2f}%)' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False       \n",
    "\n",
    "    # Cell 2,2: Count of null values in the column\n",
    "    cell = table.cell(2, 5)\n",
    "    cell.text = 'Null Values Count : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"null_values\"][ind]  :,.0f}' + \"   \" + F' ({data_qlt_df[\"%_of_nulls\"][ind]  :,.2f}%)' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 3,0: Min of values in the column\n",
    "    cell = table.cell(3, 1)\n",
    "    cell.text = 'Min : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"min\"][ind]  :,.2f}' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 3,1: Mean of values in the column\n",
    "    cell = table.cell(3, 3)\n",
    "    cell.text = 'Mean :  \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"mean\"][ind] :,.2f}' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 3,3: Max of values in the column\n",
    "    cell = table.cell(3, 5)\n",
    "    cell.text = 'Max : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"max\"][ind]  :,.2f}' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 4,1: 25th Percentile of values in the column\n",
    "    cell = table.cell(4, 1)\n",
    "    cell.text = '25th Percentile : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"25%\"][ind]  :,.2f}' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 4,2: 50th Percentile of values in the column\n",
    "    cell = table.cell(4, 3)\n",
    "    cell.text = '50th Percentile : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"50%\"][ind]  :,.2f}' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 4,3: 75th Percentile of values in the column\n",
    "    cell = table.cell(4, 5)\n",
    "    cell.text = '75th Percentile : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"75%\"][ind]  :,.2f}' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 5,1: Memory used by the column values\n",
    "    cell = table.cell(5, 1)\n",
    "    cell.text = 'Column Memory : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"col_memory\"][ind] :,.2} MB' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    # Cell 5,2: Memory used by the column values vs. memory used by the data type\n",
    "    cell = table.cell(5, 3)\n",
    "    cell.text = 'As % of Dtype Memory  : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"%_of_dtype_mem\"][ind] :.2f}%' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False                               \n",
    "\n",
    "    # Cell 5,3: Memory used by the column values vs. memory used by the data type\n",
    "    cell = table.cell(5, 5)\n",
    "    cell.text = 'As % of DF Memory : \\n'\n",
    "    cell_font = cell.paragraphs[0].runs[0].font\n",
    "    cell_font.size = Pt(11)\n",
    "    cell_font.bold = True\n",
    "    p = cell.paragraphs[0].add_run(F'{data_qlt_df[\"%_of_total_memory\"][ind] :.2f}%' )\n",
    "    cell_font2 = cell.paragraphs[0].runs[1].font\n",
    "    cell_font2.size = Pt(11)\n",
    "    cell_font2.bold = False\n",
    "\n",
    "    p = document.add_paragraph(' ')\n",
    "    p = document.add_paragraph(' ')\n",
    "\n",
    "    fig_name = 'fig_' + data_qlt_df['column_name'][ind] + '.png'\n",
    "    document.add_picture(fig_name, height=Inches(3.5), width=Inches(6))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the doc\n",
    "document.save('data_profile_df_MS_WORD.docx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Document generated!\n"
     ]
    }
   ],
   "source": [
    "print(\"Document generated!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}