WhatsApp DB Analysis and Conversation Extraction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# WhatsApp sqlite DB tools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Original Medium post: https://medium.com/@1522933668924/extracting-whatsapp-messages-from-backups-with-code-examples-49186de94ab4\n",
    "#### License: Free to use, include credit / attribution\n",
    "Please note that message information was intentionally removed from this notebook and the cells without outputs are not such by mistake"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First, Let's figure out what tables do we have in the DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ZWABLACKLISTITEM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ZWACHATPROPERTIES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ZWACHATPUSHCONFIG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ZWAMESSAGEINFO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Z_METADATA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Z_MODELCACHE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ZWAGROUPINFO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ZWAMESSAGEDATAITEM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ZWAPROFILEPICTUREITEM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ZWAGROUPMEMBERSCHANGE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ZWAPROFILEPUSHNAME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ZWAMESSAGE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ZWAGROUPMEMBER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ZWACHATSESSION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ZWAMEDIAITEM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ZWAVCARDMENTION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>ZWAZ1PAYMENTTRANSACTION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Z_PRIMARYKEY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       name\n",
       "0          ZWABLACKLISTITEM\n",
       "1         ZWACHATPROPERTIES\n",
       "2         ZWACHATPUSHCONFIG\n",
       "3            ZWAMESSAGEINFO\n",
       "4                Z_METADATA\n",
       "5              Z_MODELCACHE\n",
       "6              ZWAGROUPINFO\n",
       "7        ZWAMESSAGEDATAITEM\n",
       "8     ZWAPROFILEPICTUREITEM\n",
       "9     ZWAGROUPMEMBERSCHANGE\n",
       "10       ZWAPROFILEPUSHNAME\n",
       "11               ZWAMESSAGE\n",
       "12           ZWAGROUPMEMBER\n",
       "13           ZWACHATSESSION\n",
       "14             ZWAMEDIAITEM\n",
       "15          ZWAVCARDMENTION\n",
       "16  ZWAZ1PAYMENTTRANSACTION\n",
       "17             Z_PRIMARYKEY"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con = sqlite3.connect(\"ChatStorage.sqlite\")\n",
    "tables = pd.read_sql_query(\"SELECT name FROM sqlite_master WHERE type='table'\", con)\n",
    "con.close()\n",
    "tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Since I already looked through those tables I know that the interesting one is ZWAMESSAGE, let's open it and start looking around"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect(\"ChatStorage.sqlite\")\n",
    "df = pd.read_sql_query(\"SELECT * from ZWAMESSAGE\", con)\n",
    "con.close()\n",
    "df.tail(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's see what columns are there?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 Z_PK\n",
      "2 Z_ENT\n",
      "3 Z_OPT\n",
      "4 ZCHILDMESSAGESDELIVEREDCOUNT\n",
      "5 ZCHILDMESSAGESPLAYEDCOUNT\n",
      "6 ZCHILDMESSAGESREADCOUNT\n",
      "7 ZDATAITEMVERSION\n",
      "8 ZDOCID\n",
      "9 ZENCRETRYCOUNT\n",
      "10 ZFILTEREDRECIPIENTCOUNT\n",
      "11 ZFLAGS\n",
      "12 ZGROUPEVENTTYPE\n",
      "13 ZISFROMME\n",
      "14 ZMESSAGEERRORSTATUS\n",
      "15 ZMESSAGESTATUS\n",
      "16 ZMESSAGETYPE\n",
      "17 ZSORT\n",
      "18 ZSPOTLIGHTSTATUS\n",
      "19 ZSTARRED\n",
      "20 ZCHATSESSION\n",
      "21 ZGROUPMEMBER\n",
      "22 ZLASTSESSION\n",
      "23 ZMEDIAITEM\n",
      "24 ZMESSAGEINFO\n",
      "25 ZPARENTMESSAGE\n",
      "26 ZMESSAGEDATE\n",
      "27 ZSENTDATE\n",
      "28 ZFROMJID\n",
      "29 ZMEDIASECTIONID\n",
      "30 ZPHASH\n",
      "31 ZPUSHNAME\n",
      "32 ZSTANZAID\n",
      "33 ZTEXT\n",
      "34 ZTOJID\n"
     ]
    }
   ],
   "source": [
    "print('\\n'.join([str(i+1)+' '+x for i, x in enumerate(df.columns)]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now that we have a DataFrame let's start by searching based on the phone number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get df that only contains chats with a specific phone number (or part of it)\n",
    "get_df_by_number = lambda df, num: df[df.ZTOJID.str.contains(num).fillna(False) | df.ZFROMJID.str.contains(num).fillna(False)]\n",
    "#note that the fillna is critical to prevent the weird False | None != None | False behavior"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = get_df_by_number(df, '5027')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#let's check how many messages did we find\n",
    "len(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#let's see the text\n",
    "list(df2.ZTEXT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ok, it works. Let's add some tools to extract interesting messages and test them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "#let's make a few more accessors\n",
    "get_df_by_contact_name = lambda df, name: df[df.ZPUSHNAME.str.contains(name).fillna(False)]\n",
    "get_df_by_chat_session_id = lambda df, sid: df[df.ZCHATSESSION == sid]\n",
    "grep_for_message_text = lambda df, txt: df[df.ZTEXT.str.contains(txt).fillna(False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_df_by_contact_name(df, 'Emma').head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([159.])"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Let's find all chat sessions that a specific contact participated in\n",
    "get_df_by_contact_name(df, 'Emma').ZCHATSESSION.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#now let's read a specific chat session\n",
    "get_df_by_chat_session_id(df, 104.0).head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grep_for_message_text(df, 'URGENT').head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ok, next step - let's go figure out how to format dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(1985, 2, 6, 17, 55, 39, 331183)"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Let's see if it just works if we try to convert ZWMESSAGEDATE to datetime assuming it is a timestamp\n",
    "datetime.fromtimestamp(df4.ZMESSAGEDATE.iloc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Well, that didn't work - how could there be a message from 1985? -- reading around it seems Apple likes to count time on iPhones from 1.1.2001 - Let's see what happens if we add that to our timestamp?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2016, 2, 7, 15, 55, 39, 331183)"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "timestamp_to_apple = lambda x: datetime.fromtimestamp(x) + (datetime(2001,1,1) - datetime.fromtimestamp(0))\n",
    "timestamp_to_apple(df4.ZMESSAGEDATE.iloc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### It works! Let's add a 'Date' column to our DB to show readable dates and make it the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'] = df.ZMESSAGEDATE.apply(timestamp_to_apple)\n",
    "df.index = df.Date    # let's make a date index\n",
    "df = df.sort_index()  # now let's sort by index\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### hmmm, I got messages from 2012? with no text? \n",
    "After looking around it seems to be that these messages indicate the creation dates of chat sessions or groups and are probably received from WhatsApp servers..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ok let's try to print everything in a nice readable format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get the dataframe of all messages where contact Emma appears and show the top 5\n",
    "#values in the ZTEXT (message text) column together with index (which we set to date)\n",
    "get_df_by_chat_session_id(df, 159.0)[['ZTEXT']].head() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### it is a bit annoying that we can't see the full text, so let's fix it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_colwidth', -1)\n",
    "get_df_by_chat_session_id(df, 159.0)[['ZTEXT']].head() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The end - hope it was useful. If it did, please star / fork this repo :)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "my_tf",
   "language": "python",
   "name": "my_tf"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}