profiler.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Data Source"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ideally, this should be parameterized so that you can specify a file type, path, and options for importing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def import_data(file_type, file_path):\n",
    "    \"\"\"\n",
    "    Imports data into a dataframe\n",
    "    file_type: str, defines the type of file, currently only supports csv\n",
    "    file_path: str, file path of file to be used\n",
    "    \"\"\"\n",
    "    if file_type == 'csv':\n",
    "        df = pd.read_csv(file_path)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#df again outside of function\n",
    "df = import_data('csv','datasets/TestFile/TestFile.csv')\n",
    "# Put this file path to git"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### View Sample Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                test_text test_currency test_date  test_float  \\\n",
      "0                                     NaN   $66,300.00     3/9/85    66300.00   \n",
      "1  this description is 37 characters long           NaN       NaN         NaN   \n",
      "2                                                $0.00        NaN        0.00   \n",
      "3    Proper capitalization for a sentence   $72,000.37    2/12/17    72000.37   \n",
      "4             ALL UPPERCASE CAUSE WHY NOT     ($127.27)       NaN     -127.27   \n",
      "\n",
      "   test_float2  test_int  test_int2  test_flag  test_flag2  test_percentage  \n",
      "0     66300.00   66300.0        NaN        NaN         NaN              NaN  \n",
      "1          NaN       NaN        NaN        NaN         NaN              NaN  \n",
      "2         0.00       0.0        NaN        NaN         NaN              NaN  \n",
      "3     72000.37   72000.0        NaN        NaN         NaN              NaN  \n",
      "4      -127.00    -127.0        NaN        NaN         NaN              NaN  \n"
     ]
    }
   ],
   "source": [
    "print(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get general statistics in case this is helpful"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5, 10)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_data_type(col):\n",
    "    \"\"\"\n",
    "    Determines the datatype of a column in the dataframe. Begins with what pandas provides,\n",
    "    and expands. In future, should return a \"subtype\", like a string that is currency. This\n",
    "    may then be useful for modifying data, such as turning currency into float. Is float\n",
    "    really float or is it int with empty decimals?\n",
    "    \n",
    "    If no values are found at all, defaults to Float64, so when count=count_null should change data_type\n",
    "    to [none], but still keep in mind that for purposes of df is still float. At this point, all values\n",
    "    should be set to \"NA\" and field flag as empty set.\n",
    "    \n",
    "    col: index of column being analyzed\n",
    "    \"\"\"\n",
    "    data_type = df.dtypes[col]\n",
    "    \n",
    "    return data_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def check_for_null(col):\n",
    "    \"\"\"\n",
    "    Determines if column has null data\n",
    "    \"\"\"\n",
    "    has_null = df.shape[0] != df[df.columns[col]].count()\n",
    "    \n",
    "      #.count only includes non-nulls\n",
    "    return has_null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def check_for_blank(col):\n",
    "    \"\"\"\n",
    "    Determines if field has any blanks. \n",
    "    \"\"\"\n",
    "    if data_type == 'object':     \n",
    "         if any(df[df.columns[col]].str.strip() == ''):\n",
    "             has_blank = 'True'\n",
    "         else:\n",
    "             has_blank = 'False'\n",
    "    else:\n",
    "        has_blank = 'NA'\n",
    "    \n",
    "    return has_blank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_count_zero(col):\n",
    "    \"\"\"\n",
    "    Determines count of zeros for numeric fields. \n",
    "    \"\"\"\n",
    "    if data_type in ('int64', 'datetime64[ns]', 'float64') :\n",
    "        \n",
    "        count_zero = len(df[df[df.columns[col]] == 0])\n",
    "        percent_zero = round(count_zero/row_count,2)\n",
    "    else:\n",
    "        count_zero= 'NA'\n",
    "        percent_zero = 'NA'\n",
    "    \n",
    "    return count_zero, percent_zero"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_count_null(col):\n",
    "    \"\"\"\n",
    "    Determines count of nulls in this column. \n",
    "    \"\"\"\n",
    "\n",
    "    count_null = len(df[df[df.columns[col]].isnull()])\n",
    "    percent_null = round(count_null / row_count,2)\n",
    "\n",
    "    return count_null, percent_null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_count_blank(col):\n",
    "    \"\"\"\n",
    "    Determines count of blanks in this column. \n",
    "    \"\"\"\n",
    "    if data_type == 'object':\n",
    "        \n",
    "        count_blank = len(df[df[df.columns[col]].str.strip() == ''])\n",
    "        percent_blank = round(count_blank/row_count,2)\n",
    "    else:\n",
    "        count_blank = 'NA'\n",
    "        percent_blank = ''\n",
    "    \n",
    "    return count_blank,percent_blank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_length(col):\n",
    "    \"\"\"\n",
    "    provides length information for column\n",
    "    \"\"\"\n",
    "    if data_type == 'object':\n",
    "        max_length = df[df.columns[col]].str.len().max()\n",
    "        min_length = df[df.columns[col]].str.len().min()\n",
    "    else:\n",
    "        max_length = 'NA'\n",
    "        min_length = 'NA'\n",
    "    \n",
    "    return min_length, max_length    \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_min_max(col):\n",
    "    \"\"\"\n",
    "    provides min/max information for column\n",
    "    \"\"\"\n",
    "    if data_type in ('int64', 'datetime64[ns]', 'float64') :\n",
    "    \n",
    "        max_value = df[df.columns[col]].max() \n",
    "        min_value = df[df.columns[col]].min() \n",
    " \n",
    "    else:\n",
    "        max_value = 'NA'\n",
    "        min_value = 'NA'\n",
    "    \n",
    "    return min_value, max_value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compile results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>column_name</th>\n",
       "      <th>count_distinct</th>\n",
       "      <th>unique_percent</th>\n",
       "      <th>data_type</th>\n",
       "      <th>count_zero</th>\n",
       "      <th>percent_zero</th>\n",
       "      <th>count_null</th>\n",
       "      <th>percent_null</th>\n",
       "      <th>count_blank</th>\n",
       "      <th>percent_blank</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>shortest_string</th>\n",
       "      <th>longest_string</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>test_text</td>\n",
       "      <td>4</td>\n",
       "      <td>0.8</td>\n",
       "      <td>object</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>2</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>test_currency</td>\n",
       "      <td>4</td>\n",
       "      <td>0.8</td>\n",
       "      <td>object</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>test_date</td>\n",
       "      <td>2</td>\n",
       "      <td>0.4</td>\n",
       "      <td>object</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>3</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>test_float</td>\n",
       "      <td>4</td>\n",
       "      <td>0.8</td>\n",
       "      <td>float64</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>-127.27</td>\n",
       "      <td>72000.4</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>test_float2</td>\n",
       "      <td>4</td>\n",
       "      <td>0.8</td>\n",
       "      <td>float64</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>-127</td>\n",
       "      <td>72000.4</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>test_int</td>\n",
       "      <td>4</td>\n",
       "      <td>0.8</td>\n",
       "      <td>float64</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.2</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>-127</td>\n",
       "      <td>72000</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>test_int2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>test_flag</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>test_flag2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>test_percentage</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NA</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       column_name  count_distinct  unique_percent data_type count_zero  \\\n",
       "0        test_text               4             0.8    object         NA   \n",
       "1    test_currency               4             0.8    object         NA   \n",
       "2        test_date               2             0.4    object         NA   \n",
       "3       test_float               4             0.8   float64          1   \n",
       "4      test_float2               4             0.8   float64          1   \n",
       "5         test_int               4             0.8   float64          1   \n",
       "6        test_int2               0             0.0   float64          0   \n",
       "7        test_flag               0             0.0   float64          0   \n",
       "8       test_flag2               0             0.0   float64          0   \n",
       "9  test_percentage               0             0.0   float64          0   \n",
       "\n",
       "  percent_zero  count_null  percent_null count_blank percent_blank     min  \\\n",
       "0           NA           1           0.2           1           0.2      NA   \n",
       "1           NA           1           0.2           0             0      NA   \n",
       "2           NA           3           0.6           0             0      NA   \n",
       "3          0.2           1           0.2          NA               -127.27   \n",
       "4          0.2           1           0.2          NA                  -127   \n",
       "5          0.2           1           0.2          NA                  -127   \n",
       "6            0           5           1.0          NA                   NaN   \n",
       "7            0           5           1.0          NA                   NaN   \n",
       "8            0           5           1.0          NA                   NaN   \n",
       "9            0           5           1.0          NA                   NaN   \n",
       "\n",
       "       max shortest_string longest_string  \n",
       "0       NA               2             38  \n",
       "1       NA               6             11  \n",
       "2       NA               6              7  \n",
       "3  72000.4              NA             NA  \n",
       "4  72000.4              NA             NA  \n",
       "5    72000              NA             NA  \n",
       "6      NaN              NA             NA  \n",
       "7      NaN              NA             NA  \n",
       "8      NaN              NA             NA  \n",
       "9      NaN              NA             NA  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = [] #list to store output\n",
    "\n",
    "row_count = df.shape[0]\n",
    "\n",
    "col = 0\n",
    "while col < len(df.columns): \n",
    "    column_name = df.columns[col]\n",
    "\n",
    "    count_distinct = df[df.columns[col]].nunique()\n",
    "    unique_percent = round(count_distinct/row_count,2)\n",
    "    \n",
    "    data_type=get_data_type(col)\n",
    "    \n",
    "\n",
    "\n",
    "    \n",
    "    count_zero = get_count_zero(col)[0]\n",
    "    percent_zero = get_count_zero(col)[1]\n",
    "    count_null = get_count_null(col)[0]\n",
    "    percent_null = get_count_null(col)[1]\n",
    "    count_blank = get_count_blank(col)[0]\n",
    "    percent_blank = get_count_blank(col)[1]\n",
    "    \n",
    "    min_length = get_length(col)[0]\n",
    "    max_length = get_length(col)[1]\n",
    "    \n",
    "    min_value = get_min_max(col)[0]\n",
    "    max_value = get_min_max(col)[1]\n",
    "    \n",
    "    \n",
    "    \n",
    "    results.append({'column_name' : column_name,\\\n",
    "                    'count_distinct' : count_distinct,\\\n",
    "                    'unique_percent' : unique_percent,\\\n",
    "                    'data_type' : data_type ,\\\n",
    "                    'count_zero' : count_zero,\\\n",
    "                    'percent_zero' : percent_zero,\\\n",
    "                    'count_null' : count_null,\\\n",
    "                    'percent_null' : percent_null,\\\n",
    "                    'count_blank' : count_blank,\\\n",
    "                    'percent_blank' : percent_blank,\\\n",
    "                    'min' : min_value,\\\n",
    "                    'max' : max_value,\\\n",
    "                    'shortest_string' : min_length,\\\n",
    "                    'longest_string' : max_length\n",
    "                   }\n",
    "                  )\n",
    "   \n",
    "    col += 1\n",
    "    \n",
    "pd.DataFrame(results, columns=['column_name', 'count_distinct', 'unique_percent','data_type','count_zero','percent_zero','count_null','percent_null','count_blank','percent_blank','min','max','shortest_string','longest_string']) #reads the list in a table format="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}