04-Data-Wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "Environmental Data Analytics | John Fay and Luana Lima | Developed by Kateri Salk  \n",
    "Spring 2024\n",
    "\n",
    "---\n",
    "# 04: Data Wrangling (in Python & Pandas)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LESSON OBJECTIVES\n",
    "1. Wrangle datasets with Python and Pandas functions\n",
    "2. Compare R's `dplyr` to Python's `pandas` package with respect to wrangling data.\n",
    "3. Apply data wrangling skills to a real-world example dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SET UP YOUR DATA ANALYSIS SESSION\n",
    "Import and explore the `NTL-LTER_Lake_ChemistryPhysics_Raw.csv` dataset. ([info on Pandas dataframe](https://pandas.pydata.org/pandas-docs/stable/reference/frame.html))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read in our data into a dataframe\n",
    "NTL_phys_data = pd.read_csv(\"./data/Raw/NTL-LTER_Lake_ChemistryPhysics_Raw.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#First few rows...\n",
    "NTL_phys_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Structure...\n",
    "NTL_phys_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Summary...\n",
    "NTL_phys_data.describe(include='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Dimensions...\n",
    "NTL_phys_data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## DATA WRANGLING"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  ♦ Filtering\n",
    "Filtering allows us to choose certain rows (observations) in our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Data type of the lakename variable\n",
    "NTL_phys_data['lakename'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Data type of the depth variable\n",
    "NTL_phys_data['depth'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color='blue'>_How might the data type of a column affect how we go about subsetting records?_</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Binary mask filtering\n",
    "Similar to R's matrix filtering, we can create and apply a binary mask to subset records from our dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Construct a binary mask where records with zero depths are set as True\n",
    "zeroDepthMask = NTL_phys_data['depth'] == 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Display the mask\n",
    "zeroDepthMask"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Apply the mask to the original dataframe, which returns only rows where the mask is True\n",
    "NTL_Surface1 = NTL_phys_data[zeroDepthMask]\n",
    "NTL_Surface1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can create and apply a mask all in one line..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Binary mask filtering, in one line\n",
    "NTL_Surface1 = NTL_phys_data[NTL_phys_data['depth'] == 0]\n",
    "NTL_Surface1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Query filtering\n",
    "Similar to TidyR's `filter` verb, we can supply a query string to select rows.\n",
    "\n",
    "<mark>→ → Note: the query statement will NOT work if you have a space in your field name ← ←</mark>\n",
    "If that's the case you should use binary mask filtering..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Query filtering\n",
    "NTL_Surface2 = NTL_phys_data.query(\"depth == 0\")\n",
    "NTL_Surface2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_Surface3 = NTL_phys_data.query(\"depth < 0.25\")\n",
    "NTL_Surface3.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color='blue'>► _How might `<` or `>` work with nominal data (e.g. `lakename`)?_</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color=\"blue\">► *Have a look at the output of your queries. Are the indices sequential? What might this say about our dataframe subsets?*</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Check the index values: are they sequential? \n",
    "NTL_Surface2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Multiple filters...\n",
    "How do we filter records using multiple criteria? Here are examples using the binary masking and query approaches."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#First, generate a list of lake names\n",
    "NTL_phys_data['lakename'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filter for just Peter Lake and Paul Lake using binary masks\n",
    "PeterMask = NTL_phys_data['lakename'] == 'Peter Lake' #Mask 1\n",
    "PaulMask = NTL_phys_data['lakename'] == 'Paul Lake'   #Mask 2\n",
    "PeterPaul1 = NTL_phys_data[PeterMask | PaulMask]      #Combine masks, using the or (|) operator\n",
    "PeterPaul1.shape                                      #Show the dimensions of the result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Same as above, but in one line, note the need for parens around each mask criteria\n",
    "PeterPaul2 = NTL_phys_data[(NTL_phys_data['lakename'] == 'Peter Lake') | (NTL_phys_data['lakename'] == 'Paul Lake')]\n",
    "PeterPaul2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filter for just Peter Lake and Paul Lake, using a query statement\n",
    "PeterPaul1 = NTL_phys_data.query(\"lakename == 'Peter Lake' | lakename == 'Paul Lake'\")\n",
    "PeterPaul1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary><b><font color=\"blue\">EXERCISE: Ensure our list worked by listing the unique lake names in the <code>PeterPaul1</code> dataframe</font></b><br>Hint: see 4 code cells up...</summary>\n",
    "    <code>PeterPaul1['lakename'].unique()</code>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#List the unique values in the 'lakename' field of PeterPaul1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<font color='darkgreen'>**_\\*A note on writing \"tidy\" Python code_**<br>\n",
    "_To span Python statement across multiple lines, we can either use the `\\` character, which tells Python to ignore the line break..._</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filter for just Peter Lake and Paul Lake by eliminating others\n",
    "PeterPaul2 = NTL_phys_data.query(\"lakename != 'Tuesday Lake' & \" + \\\n",
    "                                 \"lakename != 'East Long Lake' &  \" + \\\n",
    "                                 \"lakename != 'West Long Lake' &  \" + \\\n",
    "                                 \"lakename != 'Central Long Lake' &  \" + \\\n",
    "                                 \"lakename != 'Hummingbird Lake' & \" + \\\n",
    "                                 \"lakename != 'Crampton Lake' &  \" + \\\n",
    "                                 \"lakename != 'Ward Lake'\")\n",
    "                                  \n",
    "PeterPaul2.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color='darkgreen'>_...Or, we can wrap code within parentheses which allows us to continue writing single commands on multiple lines._</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filter for just Peter Lake and Paul Lake\n",
    "PeterPaul3 = (NTL_phys_data.query(\"lakename != 'Tuesday Lake' & \" + \n",
    "                                  \"lakename != 'East Long Lake' &  \" + \n",
    "                                  \"lakename != 'West Long Lake' &  \" + \n",
    "                                  \"lakename != 'Central Long Lake' &  \" + \n",
    "                                  \"lakename != 'Hummingbird Lake' & \" + \n",
    "                                  \"lakename != 'Crampton Lake' &  \" + \n",
    "                                  \"lakename != 'Ward Lake'\")\n",
    "             )\n",
    "PeterPaul3.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "More examples..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Better format with masks, notice the \"~\" that negates the compound mask\n",
    "PeterPaul4 = NTL_phys_data[~NTL_phys_data['lakename'].isin(['Tuesday Lake', \n",
    "                                                             'East Long Lake',\n",
    "                                                             'West Long Lake', \n",
    "                                                             'Central Long Lake',                                                           \n",
    "                                                             'Hummingbird Lake',\n",
    "                                                             'Crampton Lake', \n",
    "                                                             'Ward Lake'])]\n",
    "PeterPaul4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using a query and some of the more flexible query string operators\n",
    "PeterPaul3 = NTL_phys_data.query('lakename in [\"Peter Lake\",\"Paul Lake\"]')\n",
    "PeterPaul3.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Querying a range from continuous values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "JuneOct_exclusive = NTL_phys_data.query('daynum > 151 and daynum < 305')\n",
    "JuneOct_inclusive = NTL_phys_data.query('daynum >= 151 and daynum <= 305')\n",
    "JuneOct_range = NTL_phys_data[NTL_phys_data['daynum'].isin(range(151,305))]\n",
    "print(JuneOct_exclusive.shape, JuneOct_inclusive.shape,JuneOct_range.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary><b><font color=\"blue\">► EXERCISE: Filter the <code>NTL_pys_data</code> for the year 1999</font></b></summary>\n",
    "    <code>NTL_1999 = NTL_phys_data.query('year4 == 1999')</code> Using \"query\", OR<br>\n",
    "    <code>NTL_1999 = NTL_phys_data[NTL_phys_data['year4'] == 1999]</code> Using a binary mask...\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_1999 = \n",
    "NTL_1999.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary><b><font color=\"blue\">► EXERCISE: Filter the <code>NTL_pys_data</code> for Tuesday Lake for the year 1990 thru 1999</font></b></summary>\n",
    "    <code>NTL_90to99 = NTL_phys_data.query('year4 >= 1990 and  year4 <= 1999')</code>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_90to99 = \n",
    "NTL_90to99.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Another alternative: using Pandas `iloc[]` and `loc[]` functions.\n",
    "While definitely not as user friendly as TidyR's filter (and select) verbs, Pandas indexing capabilities are quite powerful, particularly when you get into multiple and hierarchical indices (which we won't discuss here). Anyway, Pandas has two types of row and column indices and a function for each..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using `iloc[]` to filter records\n",
    "`iloc[]` is used to extract records by their `i`nteger `loc`ations, i.e. the row number. *[Recall that in Pandas, index values begin at zero, not one!]*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Show the 20th row of datam, a single row is returned as a series...\n",
    "NTL_phys_data.iloc[19]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Show rows 3, 1, and 5 - in that order; multiple rows return a dataframe\n",
    "NTL_phys_data.loc[[2,0,4]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Show rows 10 thru (and including) 14\n",
    "NTL_phys_data.iloc[9:14]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary><b><font color=\"blue\">► EXERCISE: Display the 101st thru (and inlcuding) the 105th rows in the <code>NTL_pys_data</code> dataframe.</font></b></summary>\n",
    "    <code>NTL_phys_data.iloc[100:105]</code>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_phys_data.iloc[]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using `loc()` to filter records\n",
    "`loc` is used to extract records by an index *label* that we assign. We've not explicity set an row index in our `NTL_phys_data` dataframe, so Pandas has assigned sequential values, which we can see via the `index` property:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Display the index of our dataframe\n",
    "NTL_phys_data.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As such, our named index is the same as the row number so `iloc` and `loc` behave *almost* exactly the same. The key difference is that, when we specify a slice of data, `loc` returns the end value, `iloc` does not. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select data from the row with the index = 19\n",
    "NTL_phys_data.loc[19]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Return rows 10 through (and including) 14\n",
    "NTL_phys_data.loc[9:13] #<- Note that loc returns the last value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_phys_data.iloc[9:14] #<- iloc returns upto, but not including our upper index in a slice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Changing our index and using `loc`\n",
    "Just a quick glimpse of how we might change our default index and use it with `loc`..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Set the index to values in the sampledate column\n",
    "NTL_phys_data_idx = NTL_phys_data.set_index('sampledate')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select rows for a given index\n",
    "NTL_phys_data_idx.loc['8/17/16']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "### Sorting (_Arrange_)\n",
    "In Pandas, we use the `sort_values()` function to sort (\"arrange\") our records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_phys_data_depth_ascending = NTL_phys_data.sort_values(\"depth\")\n",
    "NTL_phys_data_depth_ascending.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_phys_data_depth_ascending = NTL_phys_data.sort_values(\"depth\",ascending=False)\n",
    "NTL_phys_data_depth_ascending.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary><b><font color=\"blue\">► EXERCISE: Sort the <code>NTL_pys_data</code> dataframe by `temperature`, in descending order. \n",
    "Which dates, lakes, and depths have the highest temperatures?</font></b></summary>\n",
    "    <code>>NTL_ColSelect = NTL_phys_data.sort_values('temperature_C',ascending=False</code>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Sort the NTL_phys_data dataframe by temperature, in descending order\n",
    "NTL_phys_data_temps_desc = \n",
    "NTL_phys_data_temps_desc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting Columns (_Select_)\n",
    "<font color='red'>Pandas doesn't have a good analog to dplyrs's \"SELECT\" verb, but we have a few workarounds...</font>\n",
    "\n",
    "#### Select by listing column names\n",
    "First, we can select specific columns easily enough by <u>supplying a list of the column names</u> we want in the output:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extract a subset of columns by naming them\n",
    "NTL_phys_data_temps = NTL_phys_data[['lakename','sampledate','depth','temperature_C']]\n",
    "NTL_phys_data_temps.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<details>\n",
    "    <summary><b><font color=\"blue\">► EXERCISE: Subset the <code>NTL_phys_data</code> dataframe for the `depth` and `lakename` columns, in that order. In what order do the columns appear in the result?</font></b></summary>\n",
    "    <code>NTL_ColSelect = NTL_phys_data[['depth','lakename']]</code>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_ColSelect = NTL_phys_data[['depth','lakename']]\n",
    "NTL_ColSelect.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Selecting columns using `.iloc[]`\n",
    "As we saw above, `.iloc` can subset rows by row number. We can also use it to select columns by their column number. We just need to specify rows before columns separated by a comma. To select all rows, we can just insert a semicolon indicating a slice including everything (`[:,10:20]`). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Retrieve the all records in the 4th column\n",
    "NTL_phys_data.iloc[:,3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary><b><font color=\"blue\">► EXERCISE: How would you retrieve the first 3 records in the 4th column? The last 3 records?</font></b></summary>\n",
    "    <code>NTL_phys_data.iloc[:3,3]<br>NTL_phys_data.iloc[:,[1,4,5,6]]<br>NTL_phys_data.iloc[:8,4:7]</code>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Retrieve the first 10 records in the 4th column\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Retrieve the columns 2, 4, 5, and 6 of data (all rows)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Retrieve the columns 4 thru 6 of data (first 8 rows)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<details>\n",
    "    <summary><b><font color=\"blue\">► EXERCISE: Use <code>.iloc()</code> to subset the <code>NTL_phys_data</code> dataframe for the 5th column (<code>depth</code>) and 2nd column (<code>lakename</code>), in that order. In what order do the columns appear in the result?</font></b></summary>\n",
    "    <code>NTL_phys_data_temps1 = NTL_phys_data.iloc[:,[4,1]]</code>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extract a subset of via their integer indices\n",
    "NTL_phys_data_temps1 = \n",
    "NTL_phys_data_temps1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Selecting columns using `.loc[]`\n",
    "As with rows, `.loc`** allows us to use the column names to select specific columns. It offers the ability to return slices of columns. _(However, we still can't pull columns using a mix of single column names and slices, as R's `select` verb can.)_ "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extract columns using a list of column names (rows with an index of 0 thru 5)\n",
    "NTL_phys_data.loc[:5,['lakename','sampledate','depth','temperature_C']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extract columns by slice of column names (rows with an index of 100 thru 105)\n",
    "NTL_phys_data.loc[100:105,'sampledate':'temperature_C']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "<details>\n",
    "    <summary><b><font color=\"blue\">► EXERCISE: Use <code>.loc()</code> to subset the <code>NTL_phys_data</code> dataframe for the <code>depth</code> and <code>lakename</code>, in that order. In what order do the columns appear in the result?</font></b></summary>\n",
    "    <code>NTL_phys_data_temps1 = NTL_phys_data.loc[:,['depth','lakename']]</code>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extract a subset of columns names\n",
    "NTL_phys_data_temps1 = \n",
    "NTL_phys_data_temps1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " → _See this [link](https://medium.com/dunder-data/selecting-subsets-of-data-in-pandas-6fcd0170be9c) for a more complete tutorial on selecting rows and columns in Pandas._  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "### Adding/updating column values (_Mutate_)\n",
    "In Pandas, we can create a new column by applying simple functions to existing columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Compute values into a new column\n",
    "NTL_phys_data[\"temperature_F\"] = NTL_phys_data[\"temperature_C\"] * 9/5 + 32\n",
    "NTL_phys_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, Pandas' `apply` function allows us more flexibility. We can define a function, then apply it to our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a function that computes season from julian day\n",
    "def getSeasonFromDay(day):\n",
    "    if day <= 81: return \"winter\"\n",
    "    elif day <= 173: return \"spring\"\n",
    "    elif day <= 265: return \"summer\"\n",
    "    elif day <= 356: return \"fall\"\n",
    "    else: return \"winter\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Apply that function to create a new column in our dataset\n",
    "NTL_phys_data[\"season\"] = NTL_phys_data[\"daynum\"].apply(getSeasonFromDay)\n",
    "#Display 10 random records\n",
    "NTL_phys_data.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pipes\n",
    "TidyR's pipes (`%>%`) don't convey well to Python or Pandas. So instead of a nested set of commands using pipes, we simply span our commands in sequential lines, or we can use parentheses to span commands across multiple lines. \n",
    "\n",
    "The objective remains to make the code readable. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_processed = (NTL_phys_data\n",
    "                 .query('lakename == \"Paul Lake\" | lakename == \"Paul Lake\"')\n",
    "                 .loc[:,['lakename','sampledate','depth','temperature_C']]\n",
    "                )\n",
    "NTL_processed['temperature_F'] = NTL_phys_data['temperature_C'] * 9/5 + 32"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving processed datasets\n",
    "Pandas has a `to_csv()` command that is similar to R's."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_processed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NTL_processed.to_csv('./Data/Processed/NTL-LTER_Lake_ChemistryPhysics_PeterPaul_Processed.csv',\n",
    "                     index=False, #no row names\n",
    "                    )"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}