Data preparatoin using Python/Data preparation using Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preparation using Python \n",
    "\n",
    "\n",
    "#### 1. Import libraries\n",
    "#### 2. Import data\n",
    "     - Read from csv\n",
    "     - Connect to a database\n",
    "#### 3. Metadata and data types\n",
    "     - Metadata\n",
    "     - Modify data types\n",
    "#### 4. Duplicate detection\n",
    "     - Find duplicates\n",
    "     - Remove duplicates\n",
    "#### 5. Dataframe manipulation\n",
    "     - Columns and rows\n",
    "     - Aggregation, filtering and sorting\n",
    "     - Combine dataframes\n",
    "#### 6. Outlier detection\n",
    "     - Tukey’s test for extreme values\n",
    "     - Kernel density estimation\n",
    "#### 7. Variable generation and manipulation\n",
    "     - Generate new variables\n",
    "     - Bucket variables\n",
    "     - Encode categorical variables\n",
    "     - Generate dummy variables\n",
    "#### 8. Prepararation of data for modeling\n",
    "     - Draw samples and split dataset\n",
    "     - Reshape data for modeling\n",
    "\n",
    "##### https://medium.com/@Pichitchai.Pim"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import psycopg2 as ps\n",
    "from IPython.display import HTML\n",
    "%matplotlib inline # show plots inline in Jupyter Notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read from csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('PATH', encoding = 'utf-8') \n",
    "len(df) # N rows imported (add for future reference)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#If you want dates to be recognized as dates, add this argument:\n",
    "pd.read_csv('PATH', parse_dates=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read from Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('PATH', encodeing = 'utf-8')\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to a database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Establish database connection\n",
    "con=ps.connect(dbname= 'DBNAME', host='HOST', \n",
    "port= 'PORT', user= 'USER', password= 'PASSWORD')\n",
    "cur = con.cursor()\n",
    "\n",
    "# Query raw data\n",
    "cur.execute(\"\"\"SELECT STATEMENT\"\"\")\n",
    "data = cur.fetchall()\n",
    "len(data) # Nrows imported, DATE (add for future reference)\n",
    "\n",
    "# Close connection\n",
    "cur.close()\n",
    "\n",
    "# Parse the tables into a Pandas dataframe (specify column names)\n",
    "data = pd.DataFrame(list(data), columns=('COL1', 'COL2', 'CO3'))\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Metadata and data types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Metadata\n",
    "Runtime info for single cells: %%time magic gets you the time spent on cell execution.\n",
    "\n",
    "## Info on data types:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary of missing values:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Replace missings by 0 for numeric variables (if appropriate):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0, 3): \n",
    "    df.iloc[:,i].fillna(value=0, inplace=True)\n",
    "#Specify the range of columns - here: columns 1-4."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Modify data types\n",
    "Cast to other data types:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(2, 2):   \n",
    "    df.iloc[:, [i]] = df.iloc[:, [i]].apply(np.int64) \n",
    "#Specify the range of columns (here: columns 2-3) and the data type (can also be String, etc.)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convert a microseconds integer timestamp to datetime:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.my_ts = pd.to_datetime(df.my_ts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Duplicate detection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find duplicates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count unique values of one column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('my_category').my_user_id.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Show duplicate values for one column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat(i for _, i in df.groupby('my_user_id') if len(i) > 1)\n",
    "\n",
    "#In case you want to show duplicates for a combination of columns, \n",
    "#replace 'my_user_id' by the list of columns to be considered (e.g. ['my_user_id', 'my_country']).\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop_duplicates(subset='my_user_id', keep=False)\n",
    "\n",
    "#Change 'keep' parameter to 'first'/'last' if applicable."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataframe manipulation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Columns and rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop one or more column(s):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(['col1', 'col2'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete a column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['COL1']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop one or more rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(['Index1', 'Index2'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove all rows that do not fulfill a condition:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df.COL1 < x]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replace values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[COL1].replace({\"VALUE1\": 0, \"VALUE2\": 1})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename one column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.rename(columns = {'OLDNAME': 'NEWNAME'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename all columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = ['COL1', 'COL2', 'COL3']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Change the order of columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show the list of columns to then copy and rearrange\n",
    "cols = list(df.columns.values)\n",
    "# Rearrange\n",
    "df = df[['COL2', 'COL3', 'COL1']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transpose a dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.transpose(as_index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot a dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.pivot_table('CATEGORY_COUNT', 'INDEX_VARIABLE', 'CATEGORY')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Aggregation, filtering and sorting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate by grouping (equivalent to SQL \"GROUP BY\"):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(by = ['COL1', 'COL2'], as_index = False, sort = False).COL3.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter a dataframe:\n",
    "\n",
    "### One condition:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.COL1 > 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Multiple conditions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[(df['colCOL11'] >= 1) & (df['COL2'] <=1 )]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sort dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(by = ['COL1', 'COL2'], ascending = (0, 1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add a column containing the sum over different columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "helplist = ['COL1', 'COL2', 'COL3']\n",
    "df['total'] = df[helplist].sum(axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine dataframes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join dataframes (equivalent to SQL JOIN operations):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# left join\n",
    "df = pd.merge(df1, df2, how = 'left', left_on = ['my_user_id'])\n",
    "\n",
    "# Right join\n",
    "df = pd.merge(df1, df2, how = 'right', left_on = ['my_user_id'])\n",
    "\n",
    "#inner join\n",
    "df = pd.merge(df1, df2, how = 'inner', left_on = ['my_user_id','my_department_id'])\n",
    "\n",
    "\n",
    "# outer join\n",
    "df = pd.merge(df1, df2, how = 'outer', left_on = ['my_user_id','my_department_id'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add rows of another dataframe to your dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df1, df2], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df1.append(df2, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Outlier detection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Kernel density estimation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from statsmodels.nonparametric.kde import KDEUnivariate\n",
    "\n",
    "# Define outlier function\n",
    "def find_outliers_kde(x):\n",
    "    x_scaled = scale(list(map(float, x)))\n",
    "    kde = KDEUnivariate(x_scaled)\n",
    "    kde.fit(bw = \"scott\", fft = True)\n",
    "    pred = kde.evaluate(x_scaled)\n",
    "    \n",
    "    n = sum(pred < 0.05)\n",
    "    outlier_ind = np.asarray(pred).argsort()[:n]\n",
    "    outlier_value = np.asarray(x)[outlier_ind]\n",
    "\n",
    "    return outlier_ind, outlier_value\n",
    "\n",
    "# Print outlier values\n",
    "for x in range(1, 7): # Modify to select numeric columns\n",
    "    kde_indices, kde_values = find_outliers_kde(data.ix[:, x])\n",
    "    print(list(data[[x]]), np.sort(kde_values))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tukey's test for extreme values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define function using 1.5x interquartile range deviations from quartile 1/3 as floor/ceiling\n",
    "def find_outliers_tukey(x):\n",
    "    q1 = np.percentile(x, 25)\n",
    "    q3 = np.percentile(x, 75)\n",
    "    iqr = q3-q1 \n",
    "    floor = q1 - 1.5 * iqr\n",
    "    ceiling = q3 + 1.5 * iqr\n",
    "    outlier_indices = list(x.index[(x < floor)|(x > ceiling)])\n",
    "    outlier_values = list(x[outlier_indices])\n",
    "    return outlier_indices, outlier_values\n",
    "\n",
    "# Print outliers for each numeric variable\n",
    "for x in range(1, 7): # Modify to select numeric columns\n",
    "    tukey_indices, tukey_values = find_outliers_tukey(data.ix[:, x])\n",
    "    print(list(data[[x]]), np.sort(tukey_values))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Variable generation and manipulation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generate new variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['NEWCOL'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['NEWCOL'] = df.COL1/df.COL2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def mins_to_secs(x):\n",
    "    x = df.time_spent_minutes/60\n",
    "\n",
    "df['time_spent_seconds'] = df.time_spent_minutes.map(mins_to_secs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bucket low frequency categories as \"Other\"\n",
    "def repl(x):\n",
    "    if x == 'US': return 'US'\n",
    "    elif x == 'BR': return 'BR'\n",
    "    elif x == 'ES': return 'ES'\n",
    "    else: return 'Other'\n",
    "    \n",
    "df['COUNTRY'] = df['COUNTRY'].apply(repl)\n",
    "print(df['COUNTRY'].value_counts().sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Encode categorical variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Encode a boolean variable by casting to integer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['BOOL'] = (df.COL1==\"ABC\").astype(int)\n",
    "dta.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Encode manually by mapping a dictionnary:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dic = {'Yes': 1, 'No': 2}\n",
    "df['VAR'] = df['VAR'].map(dic)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generate dummy variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dummy = pd.get_dummies(dta1['country'], prefix='ct').astype(int)\n",
    "dummy.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preparation of data for modeling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Draw samples and split dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Draw a random sample from a dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data2 = data1.sample(1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Split test and training data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train, X_test, Y_train, Y_test = train_test_split(X, Y, test_size=0.3, random_state=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshape data for modeling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reshape dataframe to array (input for Scikit-Learn or Scipy models):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "array = df.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "X = array[:,1:5]\n",
    "Y = array[:,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Flatten dataframe into a 1-dimensional array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Y = np.ravel(Y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}