02-selection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Analysis and Visualization in Python\n",
    "## Indexing, Slicing and Subsetting DataFrames in Python\n",
    "Questions\n",
    "* How can I access specific data within my data set?\n",
    "* How can I manage undefined (null) values?\n",
    "* How can I save a dataframe to a file?\n",
    "\n",
    "Objectives\n",
    "* Employ slicing to select sets of data from a DataFrame.\n",
    "* Employ label and integer-based indexing to select ranges of data in a dataframe.\n",
    "* Reassign values within subsets of a DataFrame.\n",
    "* Create a copy of a DataFrame.\n",
    "* Query/select a subset of data using a set of criteria using the following operators: `=`, `!=`, `>`, `<`, `>=`, `<=`.\n",
    "* Manipulate data using boolean masks.\n",
    "* Transform or remove null values.\n",
    "* Write modified data to a CSV file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading our data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First make sure pandas is loaded\n",
    "import pandas as pd\n",
    "\n",
    "# Read in the survey csv\n",
    "surveys_df = pd.read_csv(\"data/surveys.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Indexing & Slicing in Python\n",
    "### Selecting Data Using Labels (Column Headings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surveys_df###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surveys_### = surveys_df['species_id']\n",
    "surveys_species.###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select two columns with a list of column names\n",
    "surveys_df[ 'species_id', 'plot_id' ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Slicing Subsets of Rows and Columns in Python\n",
    "We can select specific ranges of our data in both the row and column directions using `loc`: primarily label based indexing. Integers may be used but they are interpreted as a label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What does this do?\n",
    "# surveys_df.loc[0, ['species_id', 'plot_id', 'weight']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select all columns for rows of index values 0 and 10\n",
    "surveys_df.loc[###, ###]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What happens when you type the code below?\n",
    "# surveys_df.loc[[0, 10, 35549], :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise - Range\n",
    "What happens when you execute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# surveys_df.loc[0:4, 1:4] # 'month':'plot_id'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subsetting Data Using Criteria"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surveys_df['year'] ### ###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surveys_df[surveys_df['year'] ### ###]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surveys_df[(surveys_df['year'] >= 2001### ### (surveys_df['weight'] <= 8###]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Here are the most common operators for conditions:\n",
    "\n",
    "* Equal, not equal: `==`, `!=`\n",
    "* Greater than, less than: `>` or `<`\n",
    "* Greater than or equal to, less than or equal to: `>=`, `<=`\n",
    "* Element-wise AND and OR operators: `&` and `|`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercises - Selection by presence\n",
    "`1`. You can use the `isin` method in python to query a DataFrame based upon a list of values as follows:\n",
    "```\n",
    "surveys_df[surveys_df['column_name'].isin([value1, value2, ...])]\n",
    "```\n",
    "Use the `isin` method to find all different sites (`plot_id`) that contain particular species (`AS`, `CQ`, `OX` and `UL`) in the surveys DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surveys_df[surveys_df['species_id'].###(###)]['plot_id'].###"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`2`. Create a new DataFrame that contains only observations that are of sex female or male and where weight values are greater than 0. Create a stacked bar plot of average weight by site with male vs female values stacked for each site."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selection of the data with isin()\n",
    "selection = surveys_df[(surveys_df['sex'].###([\"F\", \"M\"])) & \n",
    "                       (surveys_df['weight'] ###)][[###]]\n",
    "selection.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the mean weight for each plot_id and sex combination: \n",
    "selection = selection.groupby(['plot_id', 'sex']).###().unstack()\n",
    "selection.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The legend header contains two levels. In order to remove this,\n",
    "# the column naming needs to be reduced by one level : \n",
    "selection.columns = selection.columns.droplevel()\n",
    "selection.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# And we can make a stacked bar plot from this:\n",
    "selection.plot(kind='###', ###=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`3`. The `~` symbol in Python can be used to return the OPPOSITE of the selection that you specify in python. It is equivalent to **is not in**. Write a query that selects all rows that are NOT equal to `F` or `M` in the surveys data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surveys_df[###surveys_df['sex'].###([\"F\", \"M\"])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting and cleaning undefined values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.###(surveys_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To select rows with at least one undefined value, we can use the .any() method\n",
    "surveys_df[pd.###(surveys_df).###(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What does this do?\n",
    "one_selection = surveys_df[pd.###(surveys_df['weight'])]\n",
    "one_selection.groupby('species_id')['record_id'].###()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting Rid of the NaN’s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Before the cleanup\n",
    "print(surveys_df['###']###, surveys_df['weight']###)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a copy to avoid modifying the original object\n",
    "###_surveys_df = surveys_df.###()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For a stable mean value\n",
    "### = copy_surveys_df['weight'].mean()\n",
    "copy_surveys_df['###'] = copy_surveys_df['weight']###(averageW)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# After the cleanup\n",
    "print(###_surveys_df['weight']###, copy_surveys_df['weight']###)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing Out Data to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only keep (complete) records that have no NA\n",
    "df_no_na = surveys_df.###()\n",
    "###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_no_na.###('surveys_complete.###', ###)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}