Chapter 8 - How to deal with timestamps.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 8.1 Parsing Unix timestamps"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's not obvious how to deal with Unix timestamps in pandas -- it took me quite a while to figure this out. The file we're using here is a popularity-contest file I found on my system at `/var/log/popularity-contest`.\n",
    "\n",
    "Here's an [explanation of how this file works](http://popcon.ubuntu.com/README).\n",
    "\n",
    "I'm going to hope that nothing in it is sensitive :)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read it, and remove the last row\n",
    "popcon = pd.read_csv('data/popularity-contest', sep=' ', )[:-1]\n",
    "popcon.columns = ['atime', 'ctime', 'package-name', 'mru-program', 'tag']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The colums are the access time, created time, package name, recently used program, and a tag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "popcon[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The magical part about parsing timestamps in pandas is that numpy datetimes are already stored as Unix timestamps. So all we need to do is tell pandas that these integers are actually datetimes -- it doesn't need to do any conversion at all.\n",
    "\n",
    "We need to convert these to ints to start:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "popcon['atime'] = popcon['atime'].astype(int)\n",
    "popcon['ctime'] = popcon['ctime'].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Every numpy array and pandas series has a dtype -- this is usually `int64`, `float64`, or `object`. Some of the time types available are `datetime64[s]`, `datetime64[ms]`, and `datetime64[us]`. There are also `timedelta` types, similarly.\n",
    "\n",
    "We can use the `pd.to_datetime` function to convert our integer timestamps into datetimes. This is a constant-time operation -- we're not actually changing any of the data, just how pandas thinks about it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "popcon['atime'] = pd.to_datetime(popcon['atime'], unit='s')\n",
    "popcon['ctime'] = pd.to_datetime(popcon['ctime'], unit='s')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we look at the dtype now, it's `<M8[ns]`. As far as I can tell `M8` is secret code for `datetime64`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "popcon['atime'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So now we can look at our `atime` and `ctime` as dates!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "popcon[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now suppose we want to look at all packages that aren't libraries.\n",
    "\n",
    "First, I want to get rid of everything with timestamp 0. Notice how we can just use a string in this comparison, even though it's actually a timestamp on the inside? That is because pandas is amazing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "popcon = popcon[popcon['atime'] > '1970-01-01']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can use pandas' magical string abilities to just look at rows where the package name doesn't contain 'lib'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nonlibraries = popcon[~popcon['package-name'].str.contains('lib')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nonlibraries.sort_values('ctime', ascending=False)[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, cool, it says that I I installed ddd recently. And postgresql! I remember installing those things. Neat."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The whole message here is that if you have a timestamp in seconds or milliseconds or nanoseconds, then you can just \"cast\" it to a `'datetime64[the-right-thing]'` and pandas/numpy will take care of the rest."
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}