pyjanitor_data_cleaning_adding_columns_removing_columns_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleansing in Python with Pandas and Pyjanitor\n",
    "Here's the code example for the blog post about [data cleaning using pandas](https://www.marsja.se/easiest-data-cleaning-method-using-python-pandas-pyjanitor/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "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>Subject</th>\n",
       "      <th>RT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>n01</td>\n",
       "      <td>455.476461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>n02</td>\n",
       "      <td>456.811823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>n03</td>\n",
       "      <td>456.155956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>n04</td>\n",
       "      <td>457.285037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>n05</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Subject          RT\n",
       "0     n01  455.476461\n",
       "1     n02  456.811823\n",
       "2     n03  456.155956\n",
       "3     n04  457.285037\n",
       "4     n05         NaN"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from scipy.stats import norm\n",
    "from random import shuffle\n",
    "\n",
    "import janitor\n",
    "\n",
    "subject = ['n0' + str(i) for i in range(1, 201)]\n",
    "\n",
    "# Generate response times  using a normal distribution\n",
    "a = 457\n",
    "rt = norm.rvs(a, size=200)\n",
    "\n",
    "# Shuffle the response times \n",
    "shuffle(rt)\n",
    "\n",
    "# Adding some missing data\n",
    "rt[4], rt[9], rt[100] = np.nan, np.nan, np.nan\n",
    "\n",
    "\n",
    "# Creating a dictionary\n",
    "data = {\n",
    "    'Subject': subject,\n",
    "    'RT': rt,\n",
    "}\n",
    "\n",
    "# Pandas Dataframe from dictionary\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Append a Column, or Columns, to Pandas Dataframe:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pandas Method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "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>Subject</th>\n",
       "      <th>RT</th>\n",
       "      <th>NewColumnName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>n01</td>\n",
       "      <td>455.476461</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>n02</td>\n",
       "      <td>456.811823</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>n03</td>\n",
       "      <td>456.155956</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>n04</td>\n",
       "      <td>457.285037</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>n05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Subject          RT  NewColumnName\n",
       "0     n01  455.476461            NaN\n",
       "1     n02  456.811823            NaN\n",
       "2     n03  456.155956            NaN\n",
       "3     n04  457.285037            NaN\n",
       "4     n05         NaN            NaN"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['NewColumnName'] = np.nan\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pyjanitor method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "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>Subject</th>\n",
       "      <th>RT</th>\n",
       "      <th>NewColumnName</th>\n",
       "      <th>NewColumnName2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>n01</td>\n",
       "      <td>455.476461</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>n02</td>\n",
       "      <td>456.811823</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>n03</td>\n",
       "      <td>456.155956</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>n04</td>\n",
       "      <td>457.285037</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>n05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Subject          RT  NewColumnName  NewColumnName2\n",
       "0     n01  455.476461            NaN             NaN\n",
       "1     n02  456.811823            NaN             NaN\n",
       "2     n03  456.155956            NaN             NaN\n",
       "3     n04  457.285037            NaN             NaN\n",
       "4     n05         NaN            NaN             NaN"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newcolvals = [np.nan]*len(df['Subject'])\n",
    "df = df.add_column('NewColumnName2', newcolvals)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove Missing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pandas Dropna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "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>Subject</th>\n",
       "      <th>RT</th>\n",
       "      <th>NewColumnName</th>\n",
       "      <th>NewColumnName2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>n01</td>\n",
       "      <td>455.476461</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>n02</td>\n",
       "      <td>456.811823</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>n03</td>\n",
       "      <td>456.155956</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>n04</td>\n",
       "      <td>457.285037</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>n06</td>\n",
       "      <td>457.421874</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Subject          RT  NewColumnName  NewColumnName2\n",
       "0     n01  455.476461            NaN             NaN\n",
       "1     n02  456.811823            NaN             NaN\n",
       "2     n03  456.155956            NaN             NaN\n",
       "3     n04  457.285037            NaN             NaN\n",
       "5     n06  457.421874            NaN             NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna(subset=['RT']).head() # Set inplace=True to modify the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "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>Subject</th>\n",
       "      <th>RT</th>\n",
       "      <th>NewColumnName</th>\n",
       "      <th>NewColumnName2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>n01</td>\n",
       "      <td>455.476461</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>n02</td>\n",
       "      <td>456.811823</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>n03</td>\n",
       "      <td>456.155956</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>n04</td>\n",
       "      <td>457.285037</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>n06</td>\n",
       "      <td>457.421874</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Subject          RT  NewColumnName  NewColumnName2\n",
       "0     n01  455.476461            NaN             NaN\n",
       "1     n02  456.811823            NaN             NaN\n",
       "2     n03  456.155956            NaN             NaN\n",
       "3     n04  457.285037            NaN             NaN\n",
       "5     n06  457.421874            NaN             NaN"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Pyjanitor\n",
    "df.dropna(subset=['RT']).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Removing Empty Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pandas Method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "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>Subject</th>\n",
       "      <th>RT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>n01</td>\n",
       "      <td>455.476461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>n02</td>\n",
       "      <td>456.811823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>n03</td>\n",
       "      <td>456.155956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>n04</td>\n",
       "      <td>457.285037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>n05</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Subject          RT\n",
       "0     n01  455.476461\n",
       "1     n02  456.811823\n",
       "2     n03  456.155956\n",
       "3     n04  457.285037\n",
       "4     n05         NaN"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna(axis=1, how='all').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pyjanitor Method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "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>Subject</th>\n",
       "      <th>RT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>n01</td>\n",
       "      <td>455.476461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>n02</td>\n",
       "      <td>456.811823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>n03</td>\n",
       "      <td>456.155956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>n04</td>\n",
       "      <td>457.285037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>n05</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Subject          RT\n",
       "0     n01  455.476461\n",
       "1     n02  456.811823\n",
       "2     n03  456.155956\n",
       "3     n04  457.285037\n",
       "4     n05         NaN"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.remove_empty().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning the Column Names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "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>52 Week High</th>\n",
       "      <th>52 Week Low</th>\n",
       "      <th>Dividend Yield</th>\n",
       "      <th>EBITDA</th>\n",
       "      <th>Earnings/Share</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>175.490</td>\n",
       "      <td>259.77</td>\n",
       "      <td>2.332862</td>\n",
       "      <td>9.048000e+09</td>\n",
       "      <td>7.92</td>\n",
       "      <td>1.387211e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48.925</td>\n",
       "      <td>68.39</td>\n",
       "      <td>1.147959</td>\n",
       "      <td>6.010000e+08</td>\n",
       "      <td>1.70</td>\n",
       "      <td>1.078342e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>42.280</td>\n",
       "      <td>64.60</td>\n",
       "      <td>1.908982</td>\n",
       "      <td>5.744000e+09</td>\n",
       "      <td>0.26</td>\n",
       "      <td>1.021210e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60.050</td>\n",
       "      <td>125.86</td>\n",
       "      <td>2.499560</td>\n",
       "      <td>1.031000e+10</td>\n",
       "      <td>3.29</td>\n",
       "      <td>1.813863e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>114.820</td>\n",
       "      <td>162.60</td>\n",
       "      <td>1.714470</td>\n",
       "      <td>5.643228e+09</td>\n",
       "      <td>5.44</td>\n",
       "      <td>9.876586e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   52 Week High  52 Week Low  Dividend Yield        EBITDA  Earnings/Share  \\\n",
       "0       175.490       259.77        2.332862  9.048000e+09            7.92   \n",
       "1        48.925        68.39        1.147959  6.010000e+08            1.70   \n",
       "2        42.280        64.60        1.908982  5.744000e+09            0.26   \n",
       "3        60.050       125.86        2.499560  1.031000e+10            3.29   \n",
       "4       114.820       162.60        1.714470  5.643228e+09            5.44   \n",
       "\n",
       "     Market Cap  \n",
       "0  1.387211e+11  \n",
       "1  1.078342e+10  \n",
       "2  1.021210e+11  \n",
       "3  1.813863e+11  \n",
       "4  9.876586e+10  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "from pandas.io.json import json_normalize\n",
    "\n",
    "url = \"https://datahub.io/core/s-and-p-500-companies-financials/r/constituents-financials.json\"\n",
    "resp = requests.get(url=url)\n",
    "\n",
    "df = json_normalize(resp.json())\n",
    "df.iloc[:,0:6].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using Pandas Rename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['52_Week_High', '52_Week_Low', 'Dividend_Yield', 'EBITDA',\n",
       "       'Earnings_Share', 'Market_Cap', 'Name', 'Price', 'Price_Book',\n",
       "       'Price_Earnings', 'Price_Sales', 'SEC_Filings', 'Sector', 'Symbol'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "df.rename(columns=lambda x: re.sub('(\\s|/)','_',x), \n",
    "          inplace=True)\n",
    "df.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using clean_names from Pyjanitor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['52_week_high', '52_week_low', 'dividend_yield', 'ebitda',\n",
       "       'earnings_share', 'market_cap', 'name', 'price', 'price_book',\n",
       "       'price_earnings', 'price_sales', 'sec_filings', 'sector', 'symbol'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.clean_names().head()\n",
    "df.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping and Aggregating:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "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>sector</th>\n",
       "      <th>52_week_high_mean</th>\n",
       "      <th>52_week_high_std</th>\n",
       "      <th>52_week_low_mean</th>\n",
       "      <th>52_week_low_std</th>\n",
       "      <th>dividend_yield_mean</th>\n",
       "      <th>dividend_yield_std</th>\n",
       "      <th>ebitda_mean</th>\n",
       "      <th>ebitda_std</th>\n",
       "      <th>earnings_share_mean</th>\n",
       "      <th>...</th>\n",
       "      <th>market_cap_mean</th>\n",
       "      <th>market_cap_std</th>\n",
       "      <th>price_mean</th>\n",
       "      <th>price_std</th>\n",
       "      <th>price_book_mean</th>\n",
       "      <th>price_book_std</th>\n",
       "      <th>price_earnings_mean</th>\n",
       "      <th>price_earnings_std</th>\n",
       "      <th>price_sales_mean</th>\n",
       "      <th>price_sales_std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Health Care</td>\n",
       "      <td>51.1650</td>\n",
       "      <td>12.565288</td>\n",
       "      <td>95.23</td>\n",
       "      <td>43.317361</td>\n",
       "      <td>2.204271</td>\n",
       "      <td>0.417601</td>\n",
       "      <td>8.027000e+09</td>\n",
       "      <td>3.228650e+09</td>\n",
       "      <td>1.775</td>\n",
       "      <td>...</td>\n",
       "      <td>1.417537e+11</td>\n",
       "      <td>5.604903e+10</td>\n",
       "      <td>82.375</td>\n",
       "      <td>36.918045</td>\n",
       "      <td>14.665</td>\n",
       "      <td>16.228101</td>\n",
       "      <td>20.960</td>\n",
       "      <td>2.192031</td>\n",
       "      <td>5.016026</td>\n",
       "      <td>1.803893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Industrials</td>\n",
       "      <td>112.2075</td>\n",
       "      <td>89.494970</td>\n",
       "      <td>164.08</td>\n",
       "      <td>135.326096</td>\n",
       "      <td>1.740410</td>\n",
       "      <td>0.837853</td>\n",
       "      <td>4.824500e+09</td>\n",
       "      <td>5.972931e+09</td>\n",
       "      <td>4.810</td>\n",
       "      <td>...</td>\n",
       "      <td>7.475224e+10</td>\n",
       "      <td>9.046557e+10</td>\n",
       "      <td>141.565</td>\n",
       "      <td>115.010918</td>\n",
       "      <td>8.845</td>\n",
       "      <td>3.528463</td>\n",
       "      <td>26.035</td>\n",
       "      <td>2.439518</td>\n",
       "      <td>3.982877</td>\n",
       "      <td>0.576142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Information Technology</td>\n",
       "      <td>114.8200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>162.60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.714470</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.643228e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.440</td>\n",
       "      <td>...</td>\n",
       "      <td>9.876586e+10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150.510</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.620</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.470</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.604117</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   sector  52_week_high_mean  52_week_high_std  \\\n",
       "0             Health Care            51.1650         12.565288   \n",
       "1             Industrials           112.2075         89.494970   \n",
       "2  Information Technology           114.8200               NaN   \n",
       "\n",
       "   52_week_low_mean  52_week_low_std  dividend_yield_mean  dividend_yield_std  \\\n",
       "0             95.23        43.317361             2.204271            0.417601   \n",
       "1            164.08       135.326096             1.740410            0.837853   \n",
       "2            162.60              NaN             1.714470                 NaN   \n",
       "\n",
       "    ebitda_mean    ebitda_std  earnings_share_mean  ...  market_cap_mean  \\\n",
       "0  8.027000e+09  3.228650e+09                1.775  ...     1.417537e+11   \n",
       "1  4.824500e+09  5.972931e+09                4.810  ...     7.475224e+10   \n",
       "2  5.643228e+09           NaN                5.440  ...     9.876586e+10   \n",
       "\n",
       "   market_cap_std  price_mean   price_std  price_book_mean  price_book_std  \\\n",
       "0    5.604903e+10      82.375   36.918045           14.665       16.228101   \n",
       "1    9.046557e+10     141.565  115.010918            8.845        3.528463   \n",
       "2             NaN     150.510         NaN           10.620             NaN   \n",
       "\n",
       "   price_earnings_mean  price_earnings_std  price_sales_mean  price_sales_std  \n",
       "0               20.960            2.192031          5.016026         1.803893  \n",
       "1               26.035            2.439518          3.982877         0.576142  \n",
       "2               25.470                 NaN          2.604117              NaN  \n",
       "\n",
       "[3 rows x 21 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('sector').agg(['mean',\n",
    "                          'std']).collapse_levels().reset_index()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### All together now!\n",
    "Here we use Pyjanitor for all the above tasks (except for summary stats, of course)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "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>subject_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>day</th>\n",
       "      <th>age</th>\n",
       "      <th>response_time</th>\n",
       "      <th>gender</th>\n",
       "      <th>data_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>23</td>\n",
       "      <td>0.562719</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Don</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>27</td>\n",
       "      <td>0.522961</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>James</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>25</td>\n",
       "      <td>0.457103</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Eric</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>23</td>\n",
       "      <td>0.512688</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>James</td>\n",
       "      <td>Sixth</td>\n",
       "      <td>21</td>\n",
       "      <td>0.451782</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    subject_id first_name    day  age  response_time  gender  data_id\n",
       "0            1       John  Sixth   23       0.562719       0        1\n",
       "3            4        Don  Sixth   27       0.522961       0        1\n",
       "5            6      James  Sixth   25       0.457103       0        1\n",
       "11          12       Eric  Sixth   23       0.512688       0        1\n",
       "13          14      James  Sixth   21       0.451782       0        1"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_id = [1]*200\n",
    "df = (\n",
    "    pd.read_csv('./SimData/DF_NA_Janitor.csv', \n",
    "                index_col=0)\n",
    "    .add_column('data_id', data_id)\n",
    "    .remove_empty()\n",
    "    .dropna()\n",
    "    .clean_names()\n",
    ")\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "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
}