notebooks/Pandas_tutorial/Pandas_tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# Data Wrangling with Pandas\n",
    "\n",
    "**Author**: Jeremy Maurer - Missouri University of Science and Technology\n",
    "\n",
    "This notebook provides an overview of data manipulation using Pandas, a Python package that provides similar functionality to spreadsheet programs like Excel or Google Sheets.\n",
    "\n",
    "You can read more details about Pandas __[here](https://pandas.pydata.org/pandas-docs/stable/getting_started/index.html)__\n",
    "\n",
    "In this notebook we will briefly demonstrate the following capabilities of pandas:\n",
    "- Reading data from comma and space-delimited files into pandas dataframes\n",
    "- Manipulating data in a dataframe\n",
    "- Writing dataframes to files\n",
    "\n",
    "<div class=\"alert alert-info\">\n",
    "    <b>Terminology:</b>    \n",
    "\n",
    "- *dataframe*: The equivalent of a spreadsheet in Python.\n",
    "    \n",
    "- *Series*: A single column of a Pandas dataframe; equivalent to a column in a spreadsheet  \n",
    "\n",
    "- *tropospheric zenith delay*: The precise atmospheric delay satellite signals experience when propagating through the troposphere.  \n",
    "</div>\n",
    "\n",
    "Estimated time to run notebook: 15 minutes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "## Table of Contents:\n",
    "<a id='example_TOC'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "[**Overview of the pandas package**](#overview)  \n",
    "[1. Reading data from files](#reading-data)  \n",
    "[2. Manipulating data in dataframes](#manip-data)  \n",
    "[3. Writing data to files](#write-data)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "## Prep: Initial setup of the notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Below we set up the directory structure for this notebook exercise. In addition, we load the required modules into our python environment using the **`import`** command.\n",
    "\n",
    "<div class=\"alert alert-info\">\n",
    "    You can customize the location of your home and working directory when running this notebook by modifying the cell below. \n",
    "</div>\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "\n",
    "## Defining the home and data directories\n",
    "tutorial_home_dir = os.path.abspath(os.getcwd())\n",
    "work_dir = os.path.abspath(os.getcwd())\n",
    "print(\"Tutorial directory: \", tutorial_home_dir)\n",
    "print(\"Work directory: \", work_dir)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview of the Pandas Package\n",
    "<a id='overview'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading data from files\n",
    "<a id='reading-data'></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's start by loading a simple .csv dataset into a pandas dataframe\n",
    "df = pd.read_csv('data/sample_data.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# It's also possible to read space-delimited and excel files using pandas\n",
    "# df = pd.read_csv('space_delimited_file.txt', delim_whitespace=True)\n",
    "# df = pd.read_excel('excel_file.xlsx') # You may need to install xlrd or openpyxl to read excel files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manipulating data in pandas\n",
    "<a id='manip-data'></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pandas uses an \"index\" to keep track of rows. By default it uses integers\n",
    "print(df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# You can change the index to a column in the dataframe, for example a datetime\n",
    "df = df.set_index('Datetime')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# You can reset the index as well\n",
    "df = df.reset_index()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# By default Pandas reads datetimes from files as strings.\n",
    "# we can convert them to actual Python datetimes \n",
    "df['Datetime'] = pd.to_datetime(df['Datetime'])\n",
    "df = df.set_index('Datetime')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can get a subset of the data using the column name\n",
    "index = df['ID'] == 'JME2'\n",
    "df_jme2 = df[index]\n",
    "df_jme2.head()\n",
    "np.sum(index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# It's possible to plot data directly using Pandas\n",
    "df_jme2['ZTD'].plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can perform operations on columns:\n",
    "'Station_' + df['ID'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Or mathematical operations:\n",
    "noisy = np.nanmean(df['ZTD']) + np.nanstd(df['ZTD'])*np.random.randn(len(df))\n",
    "print(noisy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can assign the output of an operation to a new column\n",
    "df['ZTD_noisy'] = noisy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# And we can take operations of several columns\n",
    "df['ZTD_diff'] = df['ZTD'] - df['ZTD_noisy']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can define functions and then call them as operators on a dataframe column or index\n",
    "def dt2fracYear(date):\n",
    "    import datetime as dt\n",
    "    import time\n",
    "\n",
    "    def sinceEpoch(date): # returns seconds since epoch\n",
    "        return time.mktime(date.timetuple())\n",
    "    s = sinceEpoch\n",
    "\n",
    "    # check that the object is a datetime\n",
    "    try:\n",
    "        year = date.year\n",
    "    except AttributeError:\n",
    "        date = numpyDT64ToDatetime(date)\n",
    "        year = date.year\n",
    "\n",
    "    startOfThisYear = dt.datetime(year=year, month=1, day=1)\n",
    "    startOfNextYear = dt.datetime(year=year+1, month=1, day=1)\n",
    "\n",
    "    yearElapsed = s(date) - s(startOfThisYear)\n",
    "    yearDuration = s(startOfNextYear) - s(startOfThisYear)\n",
    "    fraction = yearElapsed/yearDuration\n",
    "    date_frac = date.year + fraction\n",
    "\n",
    "    return date_frac\n",
    "\n",
    "def numpyDT64ToDatetime(dt64):\n",
    "    '''\n",
    "    Convert a numpy datetime64 object to a python datetime object\n",
    "    '''\n",
    "    import datetime\n",
    "    import numpy as np\n",
    "\n",
    "    unix_epoch = np.datetime64(0, 's')\n",
    "    one_second = np.timedelta64(1, 's')\n",
    "    seconds_since_epoch = (dt64 - unix_epoch) / one_second\n",
    "    dt = datetime.datetime.utcfromtimestamp(seconds_since_epoch)\n",
    "    return dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can assign the index to be a column, operate on it, and then drop the added column\n",
    "df['dt'] = df.index\n",
    "df['fracYear'] = df['dt'].apply(lambda x: dt2fracYear(x))\n",
    "df.drop('dt', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can look at summary statistics\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can group variables as needed\n",
    "station_stats = df.value_counts('ID')\n",
    "station_stats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can create different plots, depending on the type of variable we are interested in\n",
    "df['ZTD'].plot.hist(bins=100)\n",
    "plt.xlabel('ZTD (m)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# See the API documentation for keyword arguments, etc.\n",
    "df.plot.scatter(x='ZTD', y='ZTD_noisy', s=1, c='k')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing dataframes to a file\n",
    "<a id='write-data'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas can write to various file formats, including xcel, JSON, HTML, HDF5, STATA, SQL, and pickle formats. \n",
    "\n",
    "Using the __[Scipy.io](https://docs.scipy.org/doc/scipy/reference/io.html)__ module, you can also export data from Python to a .mat file that can be read in Matlab. \n",
    "\n",
    "You can the Pandas I/O documentation __[here](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html)__. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can export a dataframe to a .csv file\n",
    "df_jme2.to_csv(os.path.join(work_dir, 'Station_JME2_ZTD.csv'), index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export to a .mat file by first converting the dataframe to a dictionary\n",
    "import scipy.io as sio\n",
    "sio.savemat('Station_JME2_ZTD.mat', {'data': df_jme2.to_dict()})"
   ]
  }
 ],
 "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.12.3"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": false,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}