Manual_schema_exploration.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b9445638-e360-4810-a580-b52e6411a01b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas\n",
    "import numpy\n",
    "import os\n",
    "from collections import defaultdict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "73fd7d19-f7c4-4a3f-87be-2931cf1e35b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "root = '../DataExport_2022-01-12'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c33292b5-a7a9-428b-a10b-190032207661",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(os.path.join(root, 'result.json'), encoding='utf8') as handle:\n",
    "    data = json.load(handle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e8e80ba8-cee7-41d0-96e3-10b1a69f513e",
   "metadata": {},
   "outputs": [],
   "source": [
    "chats_list = data['chats']['list']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "852deffb-cd4c-46b7-b5a5-2e7aeefd7aeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "saved_messages_chats = [chat for chat in chats_list if chat['type'] == 'saved_messages']\n",
    "assert len(saved_messages_chats) == 1\n",
    "chat = saved_messages_chats[0]['messages']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43d8b094-2f68-461f-a312-81f4a16dcb08",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "460843ed-8f36-4a6d-b372-c8c925511d61",
   "metadata": {},
   "source": [
    "# Manual schema exploration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fcefbb06-a669-457e-91c3-ca4b98a0d37b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22075"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(chat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "82c3a3c3-be31-4c89-aa3b-1c3c849200b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_keys = set()\n",
    "for msg in chat:\n",
    "    for key in msg.keys():\n",
    "        all_keys.add(key)\n",
    "        \n",
    "key_vals = {key: [] for key in all_keys}\n",
    "for msg in chat:\n",
    "    for key in msg.keys():\n",
    "        key_vals[key].append(msg[key])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e96c12b6-08c8-4c82-88d6-ecf8ccea715e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'action',\n",
       " 'actor',\n",
       " 'actor_id',\n",
       " 'date',\n",
       " 'duration_seconds',\n",
       " 'edited',\n",
       " 'file',\n",
       " 'forwarded_from',\n",
       " 'from',\n",
       " 'from_id',\n",
       " 'height',\n",
       " 'id',\n",
       " 'media_type',\n",
       " 'mime_type',\n",
       " 'performer',\n",
       " 'photo',\n",
       " 'poll',\n",
       " 'reply_to_message_id',\n",
       " 'saved_from',\n",
       " 'sticker_emoji',\n",
       " 'text',\n",
       " 'thumbnail',\n",
       " 'title',\n",
       " 'type',\n",
       " 'via_bot',\n",
       " 'width'}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b51086a6-9756-47d9-9860-f8a36363dafc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['clear_history']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "key_vals['action']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "db86f93d-bbb5-4e35-a428-0007026a0c54",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['actor']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b22126b6-d962-4512-98f6-37a94529f55c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['actor_id']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "15fade9c-ae70-4c1c-a3c4-720e2e7c4448",
   "metadata": {},
   "outputs": [],
   "source": [
    "# [date_str[:10] for date_str in key_vals['date']]  # use, cut time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c4198dad-4b45-430a-a041-b0e8c50997ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['duration_seconds']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "160549ba-52f2-470e-9ea8-5e8532bd3372",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['edited']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b53bb2d5-39b2-4781-9451-17784fb1b684",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['file']  # use, relative asset link (though maybe rename the asset to avoid clashes when merging asset folders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "783039be-4e38-400b-a70f-f4d1f92f6711",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['forwarded_from']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "00295a28-66ca-4f89-93a7-735e7dc814f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['from']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "66dd0cad-9184-4759-8a9b-f50f1e460c5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['from_id']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "258ddf76-3026-48c4-8634-4ec6a0578ea5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['height']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "5bfa5c7e-d64c-48ac-b8dc-43a2d7a09847",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['id']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "35c2ccdd-9f4f-4da1-b127-f037ba3b216a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'animation', 'audio_file', 'sticker', 'video_file', 'video_message'}"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(key_vals['media_type'])  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "ba607b18-c364-41b8-afcd-8181168deba7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['mime_type']  # file format, ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "ae59c1ba-6109-477b-83ea-b27bdca576f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['performer']  # song performer, ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "dcefdcca-11d1-4055-8e4e-df73a61b5da4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['photo']  # use, direct relative asset link, photos folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "2aa2e1bb-1627-4f93-bd49-cdb2a99a870d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['poll']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "08293b3b-1146-4c32-91dd-0e3ecc4a598d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['reply_to_message_id']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "bb602eae-f78b-4f63-ad2c-7f394021e393",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['saved_from']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "4ed0c3af-4d8a-4b61-8308-4d371316b59c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['sticker_emoji']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "60a346fb-5cdb-4f86-87df-e9e2764c3425",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['text']  # TODO, investigate below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "0a49709d-c36b-4dae-923b-f0c8590ea29c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['thumbnail']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "1c9026c9-4728-4e76-97ce-767577f935be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['title']  # song title, ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "99c65fcd-1af7-4225-967a-b8fe060c5bae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'message', 'service'}"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(key_vals['type'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "f6990c3d-a4bb-4d66-8122-ba0f51b2ceee",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['via_bot']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "8a661354-06d7-4bed-97e2-dfabc35735d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# key_vals['width']  # ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbc51a42-dfe6-408d-92ef-cc8e419ab334",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "a2c2a2c2-14bf-4fa4-be98-e72ee72b10a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{list, str}"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set([type(entry) for entry in key_vals['text']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "681d4774-152d-4c21-a616-79c0fd0431ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13185"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len([entry for entry in key_vals['text'] if type(entry) == str])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "39745899-6aae-42b1-a4bb-688f77de258c",
   "metadata": {},
   "outputs": [],
   "source": [
    "list_msgs = [entry for entry in key_vals['text'] if type(entry) == list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1714eb3c-f570-4cdd-93f5-d56256fcf9c4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{<class 'str'>, <class 'dict'>}\n"
     ]
    }
   ],
   "source": [
    "list_msgs_key_types = set()\n",
    "for msg_list in list_msgs:\n",
    "    for entry in msg_list:\n",
    "        list_msgs_key_types.add(type(entry))\n",
    "print(list_msgs_key_types)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6103df7a-3448-468e-aa7f-438d166ab943",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bce3652b-5abc-4976-99a6-09a55415ecd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "list_msgs_keys = set()\n",
    "list_msgs_vals = defaultdict(list)\n",
    "for msg_list in list_msgs:\n",
    "    for entry in msg_list:\n",
    "        if type(entry) == dict:\n",
    "            for key, val in entry.items():\n",
    "                list_msgs_keys.add(key)\n",
    "                list_msgs_vals[key].append(val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "d363515c-6f3a-4c87-b757-268b9410374b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'href', 'language', 'text', 'type'}"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_msgs_keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "06c1270b-d44f-4d67-bd52-abda6bf29320",
   "metadata": {},
   "outputs": [],
   "source": [
    "# list_msgs_vals['href']  # list of urls, merge with the text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "fdedf16e-2a46-4506-8ae2-63cfe6ed61b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# list_msgs_vals['language']  # practically empty, ignore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "30f87790-911b-4fa7-80a4-552fa8a067b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# list_msgs_vals['text']  # mix of urls, plain text, hashtags, mentions, terminal commands, phones and emails, etc, merge with the text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7560f551-4e3a-400b-801b-44a4353d199b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{str}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set([type(entry) for entry in list_msgs_vals['text']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "881be7ac-753a-4856-b3b7-6e193f05e7b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "attachment_types = list(set(list_msgs_vals['type']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "dcfaf04d-0c39-4f2c-a525-867b7ad865eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "attachment_results = {}\n",
    "for cur_type in attachment_types:\n",
    "    cur_type_results = []\n",
    "    list_msgs_keys = set()\n",
    "    list_msgs_vals = defaultdict(list)\n",
    "    for msg_list in list_msgs:\n",
    "        for entry in msg_list:\n",
    "            if type(entry) == dict:\n",
    "                if entry['type'] == cur_type:\n",
    "                    cur_type_results.append((entry['text'], 'href' in entry.keys()))\n",
    "    #                 print(entry['text'])\n",
    "    #                 print('href' in entry.keys())\n",
    "    attachment_results[cur_type] = cur_type_results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "92901c24-7bd8-4bdb-850a-650cc38ebaee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['phone',\n",
       " 'italic',\n",
       " 'code',\n",
       " 'bank_card',\n",
       " 'text_link',\n",
       " 'cashtag',\n",
       " 'underline',\n",
       " 'email',\n",
       " 'bold',\n",
       " 'pre',\n",
       " 'mention',\n",
       " 'link',\n",
       " 'hashtag']"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attachment_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "8e055a98-d789-428f-83a2-288bab2cb9d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['phone']  # just insert them into the text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "e604cd25-2baf-4dca-a09f-0783fa48e192",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['italic']  # apply markdown formatting and insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "45e8f474-8c01-4ba2-8c58-319020b8c1d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['code']  # apply markdown formatting and insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "f7bee7db-77b3-4c60-89a2-154a5b7aad67",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['bank_card']  # just insert into the text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "21fa4ce1-eb8a-4016-bed9-8ac5b981661d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['text_link']  # insert both text and href concatenated, href is there iff type == 'text_link'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "6f842669-3322-4f2d-933e-12406d638836",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # simple check\n",
    "# for msg_list in list_msgs:\n",
    "#     for entry in msg_list:\n",
    "#         if type(entry) == dict and 'href' in entry:\n",
    "#             print(entry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "88a34f01-186b-42dd-b3d5-294ea3283329",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['cashtag']  # looks like $PWD, hashtag plus uppercase. Put this into the \"code\" markdown construct to avoid creating hashtags."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "762688f5-30c7-41c8-9ef7-e0fcd25c18c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['underline']  # just insert (markdown weirdly doesn't support this)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "6f8ebd0e-877d-4189-a46b-b1b645966c8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['email']  # just insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "09ee0fb9-61ad-4ba3-b4e8-6476630f9482",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['bold']  # apply markdown formatting and insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "ae1ff6d0-5365-48e7-b9bc-915bf0c0c42a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['pre']  # designate this as \"code\" and then insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "3131ac1b-22d8-4e76-b36f-46114ecfc8c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['mention']  # mentions, can simply insert them as they are in markdown"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "0454ef34-fe47-45c9-818b-e89ad4d1e568",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['link']  # just insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "7f569cc5-2b6c-4f53-a6a3-361036d2a968",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attachment_results['hashtag']  # first filter them with a whitelist of tags I want to keep, then if whitelisted insert w/o changes and remove the hash sign otherwise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6dbdc2f2-18d5-4815-b7dd-33f9fcca0f87",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}