parseVTRep2CSVExample.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### parseVTRep2CSV Example\n",
    "\n",
    "Given a directory having virustotal's json report\n",
    "Output a CSV table of detection name (column: anti-virus engine; row: malware)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "import pandas as pd\n",
    "from numpy import nan as NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# input\n",
    "in_directory = \"C:/test/VTReport/\" # your VTreport dir\n",
    "in_hooklog_directory = \"C:/Users/hsiao/Downloads/GitHub/MotifAnalysis/hooklogs/somoto_woj/\" # a hooklog dir, or None\n",
    "in_tag = \"somoto_woj\"\n",
    "in_first_seen = True\n",
    "in_save_to_subdir_firstseen = True\n",
    "\n",
    "# output\n",
    "out_tag = in_tag\n",
    "\n",
    "# outfiles\n",
    "out_csvfile = 'output/VTRepo_'+ out_tag + '.csv' # original\n",
    "out_wn_csvfile = 'output/VTRepo_wn_'+ out_tag + '.csv' # winnowed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "59 files\n",
      "23 hashes\n",
      "hooklog\n",
      "save to output/VTRepo_somoto_woj.csv\n",
      "save to output/VTRepo_wn_somoto_woj.csv\n"
     ]
    }
   ],
   "source": [
    "# MIKE: 20170822, hack for TXT (VT report) or hooklog\n",
    "run_directory = in_hooklog_directory if in_hooklog_directory != None else in_directory\n",
    "\n",
    "# iter the directory\n",
    "file_list = next(os.walk(run_directory))[2]\n",
    "hash_set = set(t.split('.')[0].split(\"_\")[0] for t in file_list)\n",
    "ext = file_list[0].split('.')[-1].lower() \n",
    "\n",
    "print(\"%d files\" % len(file_list))\n",
    "print(\"%d hashes\" % len(hash_set))\n",
    "print(ext)\n",
    "print(\"save to\", out_csvfile)\n",
    "print(\"save to\", out_wn_csvfile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find all anti-virus engines and corresponding detection strings\n",
    "\n",
    "av_set = set() # set of all anti-virus engines\n",
    "csv_dict = dict()\n",
    "\n",
    "for h in hash_set:\n",
    "    # open txt file and load it as json\n",
    "    with open(os.path.join(in_directory, h + '.txt')) as txt_file:    \n",
    "        json_report = json.load(txt_file)\n",
    "        \n",
    "    # create a dictionary _dict = {engine: \"detection_name\"}\n",
    "    _dict = dict()\n",
    "    for engine in json_report['scans'].keys(): \n",
    "        scan_result = json_report['scans'].get(engine)\n",
    "        if scan_result.get(\"detected\") == True:\n",
    "            result = scan_result.get(\"result\").encode('ascii', 'ignore')\n",
    "            result = result.decode(\"ascii\").replace(',', '') # special replacement for csv\n",
    "            av_set.add(engine)\n",
    "            \n",
    "            _dict[engine] = result\n",
    "    \n",
    "    # if you don't need first_seen, set in_first_seen as Fasle\n",
    "    if in_first_seen:\n",
    "        _dict[\"first_seen\"] = json_report['first_seen']\n",
    "    \n",
    "    # attach this dictioary to csv_dict = {hash: _dict}\n",
    "    csv_dict[h] = _dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame(csv_dict).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AVG</th>\n",
       "      <th>AVware</th>\n",
       "      <th>Ad-Aware</th>\n",
       "      <th>AegisLab</th>\n",
       "      <th>Agnitum</th>\n",
       "      <th>AhnLab-V3</th>\n",
       "      <th>AntiVir</th>\n",
       "      <th>Antiy-AVL</th>\n",
       "      <th>Arcabit</th>\n",
       "      <th>Avast</th>\n",
       "      <th>...</th>\n",
       "      <th>Symantec</th>\n",
       "      <th>Tencent</th>\n",
       "      <th>TrendMicro</th>\n",
       "      <th>TrendMicro-HouseCall</th>\n",
       "      <th>VBA32</th>\n",
       "      <th>VIPRE</th>\n",
       "      <th>ViRobot</th>\n",
       "      <th>Zillya</th>\n",
       "      <th>first_seen</th>\n",
       "      <th>nProtect</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a339f16fe6bfa2347a2e</th>\n",
       "      <td>AdInstaller.Somoto</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>Application.Bundler.Somoto.I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PUA.Somoto!</td>\n",
       "      <td>Win-PUP/Somoto</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Win32:PUP-gen [PUP]</td>\n",
       "      <td>...</td>\n",
       "      <td>WS.Reputation.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Signed-AdWare.BetterInternet.SomotoLtd</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adware.BetterInternet.Win32.1234</td>\n",
       "      <td>2013-08-16 05:54:57</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31b164b4dc1d2744bf11183604572648</th>\n",
       "      <td>Downloader.ATW</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>Application.Bundler.Somoto.I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Win-AppCare/Somoto.236936.B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trojan/Win32.CF.gen</td>\n",
       "      <td>Application.Bundler.Somoto.I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>PUA.BetterInstaller</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Downloader.MazelCRTD.Win32.1058</td>\n",
       "      <td>2014-03-23 03:01:57</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38fe6f891182ca08e3f6de18ea2153a6</th>\n",
       "      <td>AdLoad.S</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>Application.Bundler.Somoto.AG</td>\n",
       "      <td>Troj.Downloader.W32!c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PUP/Win32.Somoto</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trojan/Generic.ASMalwNS.2749</td>\n",
       "      <td>Application.Bundler.Somoto.AG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>SAPE.Heur.A6182</td>\n",
       "      <td>Win32.Downloader.Bp-somato.Rkbk</td>\n",
       "      <td>ADW_TOMOS.SMN</td>\n",
       "      <td>ADW_TOMOS.SMN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-01-21 16:52:45</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7f8a9c4f991134cafe</th>\n",
       "      <td>Downloader.ATW</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>Application.Bundler.Somoto.I</td>\n",
       "      <td>Troj.Agent.mt2x</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unwanted/Win32.Somoto.N1167800769</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trojan/Win32.CF.gen</td>\n",
       "      <td>Application.Bundler.Somoto.I</td>\n",
       "      <td>Win32:Somoto-J [PUP]</td>\n",
       "      <td>...</td>\n",
       "      <td>PUA.BetterInstaller</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADW_TOMOS.SMN</td>\n",
       "      <td>ADW_TOMOS.SMN</td>\n",
       "      <td>Signed-AdWare.BetterInternet.SomotoLtd</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adware.SomotoCRTD.Win32.1117</td>\n",
       "      <td>2014-04-28 20:06:52</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5169113d8207a5605ff57604a00c030a</th>\n",
       "      <td>Downloader.ATW</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>Application.Bundler.Somoto.I</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Win-AppCare/Somoto.236952.E</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trojan/Win32.CF.gen</td>\n",
       "      <td>Application.Bundler.Somoto.I</td>\n",
       "      <td>Win32:Somoto-J [PUP]</td>\n",
       "      <td>...</td>\n",
       "      <td>Trojan.ADH.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ADW_TOMOS.SMN</td>\n",
       "      <td>ADW_TOMOS.SMN</td>\n",
       "      <td>Signed-AdWare.BetterInternet.SomotoLtd</td>\n",
       "      <td>Trojan.Win32.Generic!BT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2014-03-27 06:10:06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                   AVG  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  AdInstaller.Somoto   \n",
       "31b164b4dc1d2744bf11183604572648                        Downloader.ATW   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                              AdLoad.S   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...      Downloader.ATW   \n",
       "5169113d8207a5605ff57604a00c030a                        Downloader.ATW   \n",
       "\n",
       "                                                                     AVware  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  Trojan.Win32.Generic!BT   \n",
       "31b164b4dc1d2744bf11183604572648                    Trojan.Win32.Generic!BT   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    Trojan.Win32.Generic!BT   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  Trojan.Win32.Generic!BT   \n",
       "5169113d8207a5605ff57604a00c030a                    Trojan.Win32.Generic!BT   \n",
       "\n",
       "                                                                         Ad-Aware  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...   Application.Bundler.Somoto.I   \n",
       "31b164b4dc1d2744bf11183604572648                     Application.Bundler.Somoto.I   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    Application.Bundler.Somoto.AG   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...   Application.Bundler.Somoto.I   \n",
       "5169113d8207a5605ff57604a00c030a                     Application.Bundler.Somoto.I   \n",
       "\n",
       "                                                                 AegisLab  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...                    NaN   \n",
       "31b164b4dc1d2744bf11183604572648                                      NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    Troj.Downloader.W32!c   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...        Troj.Agent.mt2x   \n",
       "5169113d8207a5605ff57604a00c030a                                      NaN   \n",
       "\n",
       "                                                        Agnitum  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  PUA.Somoto!   \n",
       "31b164b4dc1d2744bf11183604572648                            NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                            NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...          NaN   \n",
       "5169113d8207a5605ff57604a00c030a                            NaN   \n",
       "\n",
       "                                                                            AhnLab-V3  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...                     Win-PUP/Somoto   \n",
       "31b164b4dc1d2744bf11183604572648                          Win-AppCare/Somoto.236936.B   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                                     PUP/Win32.Somoto   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  Unwanted/Win32.Somoto.N1167800769   \n",
       "5169113d8207a5605ff57604a00c030a                          Win-AppCare/Somoto.236952.E   \n",
       "\n",
       "                                                   AntiVir  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...     NaN   \n",
       "31b164b4dc1d2744bf11183604572648                       NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                       NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...     NaN   \n",
       "5169113d8207a5605ff57604a00c030a                       NaN   \n",
       "\n",
       "                                                                       Antiy-AVL  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...                           NaN   \n",
       "31b164b4dc1d2744bf11183604572648                             Trojan/Win32.CF.gen   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    Trojan/Generic.ASMalwNS.2749   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...           Trojan/Win32.CF.gen   \n",
       "5169113d8207a5605ff57604a00c030a                             Trojan/Win32.CF.gen   \n",
       "\n",
       "                                                                          Arcabit  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...                            NaN   \n",
       "31b164b4dc1d2744bf11183604572648                     Application.Bundler.Somoto.I   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    Application.Bundler.Somoto.AG   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...   Application.Bundler.Somoto.I   \n",
       "5169113d8207a5605ff57604a00c030a                     Application.Bundler.Somoto.I   \n",
       "\n",
       "                                                                   Avast  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...   Win32:PUP-gen [PUP]   \n",
       "31b164b4dc1d2744bf11183604572648                                     NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                                     NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  Win32:Somoto-J [PUP]   \n",
       "5169113d8207a5605ff57604a00c030a                    Win32:Somoto-J [PUP]   \n",
       "\n",
       "                                                     ...     \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...   ...      \n",
       "31b164b4dc1d2744bf11183604572648                     ...      \n",
       "38fe6f891182ca08e3f6de18ea2153a6                     ...      \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...   ...      \n",
       "5169113d8207a5605ff57604a00c030a                     ...      \n",
       "\n",
       "                                                               Symantec  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...      WS.Reputation.1   \n",
       "31b164b4dc1d2744bf11183604572648                    PUA.BetterInstaller   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                        SAPE.Heur.A6182   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  PUA.BetterInstaller   \n",
       "5169113d8207a5605ff57604a00c030a                           Trojan.ADH.2   \n",
       "\n",
       "                                                                            Tencent  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...                              NaN   \n",
       "31b164b4dc1d2744bf11183604572648                                                NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    Win32.Downloader.Bp-somato.Rkbk   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...                              NaN   \n",
       "5169113d8207a5605ff57604a00c030a                                                NaN   \n",
       "\n",
       "                                                       TrendMicro  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...            NaN   \n",
       "31b164b4dc1d2744bf11183604572648                              NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    ADW_TOMOS.SMN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  ADW_TOMOS.SMN   \n",
       "5169113d8207a5605ff57604a00c030a                    ADW_TOMOS.SMN   \n",
       "\n",
       "                                                   TrendMicro-HouseCall  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...                  NaN   \n",
       "31b164b4dc1d2744bf11183604572648                                    NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                          ADW_TOMOS.SMN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...        ADW_TOMOS.SMN   \n",
       "5169113d8207a5605ff57604a00c030a                          ADW_TOMOS.SMN   \n",
       "\n",
       "                                                                                     VBA32  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  Signed-AdWare.BetterInternet.SomotoLtd   \n",
       "31b164b4dc1d2744bf11183604572648                                                       NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                                                       NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  Signed-AdWare.BetterInternet.SomotoLtd   \n",
       "5169113d8207a5605ff57604a00c030a                    Signed-AdWare.BetterInternet.SomotoLtd   \n",
       "\n",
       "                                                                      VIPRE  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  Trojan.Win32.Generic!BT   \n",
       "31b164b4dc1d2744bf11183604572648                    Trojan.Win32.Generic!BT   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    Trojan.Win32.Generic!BT   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  Trojan.Win32.Generic!BT   \n",
       "5169113d8207a5605ff57604a00c030a                    Trojan.Win32.Generic!BT   \n",
       "\n",
       "                                                   ViRobot  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...     NaN   \n",
       "31b164b4dc1d2744bf11183604572648                       NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                       NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...     NaN   \n",
       "5169113d8207a5605ff57604a00c030a                       NaN   \n",
       "\n",
       "                                                                              Zillya  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  Adware.BetterInternet.Win32.1234   \n",
       "31b164b4dc1d2744bf11183604572648                     Downloader.MazelCRTD.Win32.1058   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                                                 NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...      Adware.SomotoCRTD.Win32.1117   \n",
       "5169113d8207a5605ff57604a00c030a                                                 NaN   \n",
       "\n",
       "                                                             first_seen  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  2013-08-16 05:54:57   \n",
       "31b164b4dc1d2744bf11183604572648                    2014-03-23 03:01:57   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    2016-01-21 16:52:45   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  2014-04-28 20:06:52   \n",
       "5169113d8207a5605ff57604a00c030a                    2014-03-27 06:10:06   \n",
       "\n",
       "                                                   nProtect  \n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...      NaN  \n",
       "31b164b4dc1d2744bf11183604572648                        NaN  \n",
       "38fe6f891182ca08e3f6de18ea2153a6                        NaN  \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...      NaN  \n",
       "5169113d8207a5605ff57604a00c030a                        NaN  \n",
       "\n",
       "[5 rows x 53 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You can print the df here\n",
    "df.head()\n",
    "#df['AVG']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# output\n",
    "df.to_csv(out_csvfile)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Winnowing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# MIKE: 20170731 some hacks for winnowing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# delimiter is used for spliting tokens\n",
    "import re\n",
    "delimiter = '\\,|!|\\(|\\)|\\[|\\]|@|:|/|\\.| '\n",
    "\n",
    "# general_string to remove\n",
    "general_string = ['win32','trojan','adware','generic','application','variant','downloader','not-a-virus','downware',\n",
    "                 'unwanted-program','heur','troj','bundler','antifw','riskware','optional','malware','behaveslike',\n",
    "                 'kcloud','agent','trojandownloader','appl','trojware','installer','trojan-downloader','virus',\n",
    "                 'backdoor','injector','malware-cryptor','dropper','cryptor','bundleapp','suspicious','antifwk',\n",
    "                 'adinstaller','crypt','bundleinstaller','xpack', 'hacktool','patcher','troj_gen','grayware',\n",
    "                 'software','install','click','heuristic','packed','unknown','applicunwnt','dropped','trojan-clicker',\n",
    "                 'net-worm','monitoringtool','worm','tool','toolbar','eldorado','autorun','hw32', 'trojan-dropper']\n",
    "\n",
    "# short family strings that should be kept\n",
    "short_family_string = ['kdz', 'ipz', 'lmn']\n",
    "\n",
    "import string\n",
    "def is_hex(s):\n",
    "    return all(c in string.hexdigits for c in s)\n",
    "\n",
    "def tk_winnow(t):\n",
    "    if len(t) <= 3 and t not in short_family_string:\n",
    "        return None\n",
    "    elif t in general_string:\n",
    "        return None\n",
    "    elif is_hex(t):\n",
    "        return None\n",
    "    \n",
    "    return t\n",
    "\n",
    "def VT_winnow(s):\n",
    "    if s is NaN: return NaN\n",
    "    \n",
    "    tokens = re.split(delimiter, s.lower())\n",
    "    ret_tokens = list(filter(lambda x : x if x is not False else True, [tk_winnow(t) for t in tokens]))\n",
    "    return ret_tokens if len(ret_tokens) != 0 else NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_nw = df.copy().applymap(VT_winnow)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AVG</th>\n",
       "      <th>AVware</th>\n",
       "      <th>Ad-Aware</th>\n",
       "      <th>AegisLab</th>\n",
       "      <th>Agnitum</th>\n",
       "      <th>AhnLab-V3</th>\n",
       "      <th>AntiVir</th>\n",
       "      <th>Antiy-AVL</th>\n",
       "      <th>Arcabit</th>\n",
       "      <th>Avast</th>\n",
       "      <th>...</th>\n",
       "      <th>Symantec</th>\n",
       "      <th>Tencent</th>\n",
       "      <th>TrendMicro</th>\n",
       "      <th>TrendMicro-HouseCall</th>\n",
       "      <th>VBA32</th>\n",
       "      <th>VIPRE</th>\n",
       "      <th>ViRobot</th>\n",
       "      <th>Zillya</th>\n",
       "      <th>first_seen</th>\n",
       "      <th>nProtect</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a339f16fe6bfa2347a2e</th>\n",
       "      <td>[somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>[win-pup, somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[pup-gen]</td>\n",
       "      <td>...</td>\n",
       "      <td>[reputation]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[signed-adware, betterinternet, somotoltd]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[betterinternet]</td>\n",
       "      <td>[2013-08-16]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31b164b4dc1d2744bf11183604572648</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[win-appcare, somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>[betterinstaller]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[mazelcrtd]</td>\n",
       "      <td>[2014-03-23]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38fe6f891182ca08e3f6de18ea2153a6</th>\n",
       "      <td>[adload]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[asmalwns]</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>[sape]</td>\n",
       "      <td>[bp-somato, rkbk]</td>\n",
       "      <td>[adw_tomos]</td>\n",
       "      <td>[adw_tomos]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[2016-01-21]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7f8a9c4f991134cafe</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>[mt2x]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[unwanted, somoto, n1167800769]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>[somoto-j]</td>\n",
       "      <td>...</td>\n",
       "      <td>[betterinstaller]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[adw_tomos]</td>\n",
       "      <td>[adw_tomos]</td>\n",
       "      <td>[signed-adware, betterinternet, somotoltd]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somotocrtd]</td>\n",
       "      <td>[2014-04-28]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5169113d8207a5605ff57604a00c030a</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[win-appcare, somoto]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[somoto]</td>\n",
       "      <td>[somoto-j]</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[adw_tomos]</td>\n",
       "      <td>[adw_tomos]</td>\n",
       "      <td>[signed-adware, betterinternet, somotoltd]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[2014-03-27]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                         AVG  AVware  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  [somoto]     NaN   \n",
       "31b164b4dc1d2744bf11183604572648                         NaN     NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    [adload]     NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...       NaN     NaN   \n",
       "5169113d8207a5605ff57604a00c030a                         NaN     NaN   \n",
       "\n",
       "                                                    Ad-Aware AegisLab  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  [somoto]      NaN   \n",
       "31b164b4dc1d2744bf11183604572648                    [somoto]      NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    [somoto]      NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  [somoto]   [mt2x]   \n",
       "5169113d8207a5605ff57604a00c030a                    [somoto]      NaN   \n",
       "\n",
       "                                                     Agnitum  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  [somoto]   \n",
       "31b164b4dc1d2744bf11183604572648                         NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                         NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...       NaN   \n",
       "5169113d8207a5605ff57604a00c030a                         NaN   \n",
       "\n",
       "                                                                          AhnLab-V3  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...                [win-pup, somoto]   \n",
       "31b164b4dc1d2744bf11183604572648                              [win-appcare, somoto]   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                                           [somoto]   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  [unwanted, somoto, n1167800769]   \n",
       "5169113d8207a5605ff57604a00c030a                              [win-appcare, somoto]   \n",
       "\n",
       "                                                   AntiVir   Antiy-AVL  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...     NaN         NaN   \n",
       "31b164b4dc1d2744bf11183604572648                       NaN         NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                       NaN  [asmalwns]   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...     NaN         NaN   \n",
       "5169113d8207a5605ff57604a00c030a                       NaN         NaN   \n",
       "\n",
       "                                                     Arcabit       Avast  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...       NaN   [pup-gen]   \n",
       "31b164b4dc1d2744bf11183604572648                    [somoto]         NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    [somoto]         NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  [somoto]  [somoto-j]   \n",
       "5169113d8207a5605ff57604a00c030a                    [somoto]  [somoto-j]   \n",
       "\n",
       "                                                     ...     \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...   ...      \n",
       "31b164b4dc1d2744bf11183604572648                     ...      \n",
       "38fe6f891182ca08e3f6de18ea2153a6                     ...      \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...   ...      \n",
       "5169113d8207a5605ff57604a00c030a                     ...      \n",
       "\n",
       "                                                             Symantec  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...       [reputation]   \n",
       "31b164b4dc1d2744bf11183604572648                    [betterinstaller]   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                               [sape]   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  [betterinstaller]   \n",
       "5169113d8207a5605ff57604a00c030a                                  NaN   \n",
       "\n",
       "                                                              Tencent  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...                NaN   \n",
       "31b164b4dc1d2744bf11183604572648                                  NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    [bp-somato, rkbk]   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...                NaN   \n",
       "5169113d8207a5605ff57604a00c030a                                  NaN   \n",
       "\n",
       "                                                     TrendMicro  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...          NaN   \n",
       "31b164b4dc1d2744bf11183604572648                            NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    [adw_tomos]   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  [adw_tomos]   \n",
       "5169113d8207a5605ff57604a00c030a                    [adw_tomos]   \n",
       "\n",
       "                                                   TrendMicro-HouseCall  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...                  NaN   \n",
       "31b164b4dc1d2744bf11183604572648                                    NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                            [adw_tomos]   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...          [adw_tomos]   \n",
       "5169113d8207a5605ff57604a00c030a                            [adw_tomos]   \n",
       "\n",
       "                                                                                         VBA32  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  [signed-adware, betterinternet, somotoltd]   \n",
       "31b164b4dc1d2744bf11183604572648                                                           NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                                                           NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  [signed-adware, betterinternet, somotoltd]   \n",
       "5169113d8207a5605ff57604a00c030a                    [signed-adware, betterinternet, somotoltd]   \n",
       "\n",
       "                                                   VIPRE ViRobot  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...   NaN     NaN   \n",
       "31b164b4dc1d2744bf11183604572648                     NaN     NaN   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                     NaN     NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...   NaN     NaN   \n",
       "5169113d8207a5605ff57604a00c030a                     NaN     NaN   \n",
       "\n",
       "                                                              Zillya  \\\n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  [betterinternet]   \n",
       "31b164b4dc1d2744bf11183604572648                         [mazelcrtd]   \n",
       "38fe6f891182ca08e3f6de18ea2153a6                                 NaN   \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...      [somotocrtd]   \n",
       "5169113d8207a5605ff57604a00c030a                                 NaN   \n",
       "\n",
       "                                                      first_seen nProtect  \n",
       "25fbb984c2ebcd7fb69404a06ac4838a3a0e64293bb3a33...  [2013-08-16]      NaN  \n",
       "31b164b4dc1d2744bf11183604572648                    [2014-03-23]      NaN  \n",
       "38fe6f891182ca08e3f6de18ea2153a6                    [2016-01-21]      NaN  \n",
       "4f2483f23c8eae37a77fdbcf9133d38ba51f8edac4b8fb7...  [2014-04-28]      NaN  \n",
       "5169113d8207a5605ff57604a00c030a                    [2014-03-27]      NaN  \n",
       "\n",
       "[5 rows x 53 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# output\n",
    "df_nw.to_csv(out_wn_csvfile)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save files to new directory based on first-seen year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import shutil\n",
    "\n",
    "#MIKE: set the interval of years \n",
    "in_years = [(0, 2000), (2001, 2010), (2011, 2014), (2015, 2017)]\n",
    "\n",
    "def max_year(y):\n",
    "    global in_years\n",
    "    for min_year, max_year in in_years:\n",
    "        if min_year <= y <= max_year:\n",
    "            return max_year\n",
    "    return -1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "new files with year is saved to hooklogs/somoto_woj_year\n"
     ]
    }
   ],
   "source": [
    "if in_save_to_subdir_firstseen and in_hooklog_directory and ext == \"hooklog\":\n",
    "    first_seen_year_series = df['first_seen'].apply(lambda f: int(f.split('-')[0]))\n",
    "    save_year_series = first_seen_year_series.apply(max_year)\n",
    "    save_year_series.name = \"save_year\"\n",
    "    \n",
    "    for f in file_list:\n",
    "        \n",
    "        name = f.split('_')[0]\n",
    "        save_year = save_year_series[name]\n",
    "        \n",
    "        new_dir = \"hooklogs/\" + in_tag + \"_year\" + str(save_year) +\"/\"\n",
    "        if not os.path.isdir(new_dir):\n",
    "            os.makedirs(new_dir)\n",
    "            \n",
    "        shutil.copy(os.path.join(in_hooklog_directory, f), os.path.join(new_dir, f))\n",
    "        \n",
    "    print(\"new files with year is saved to\", \"hooklogs/\" + in_tag + \"_year\")"
   ]
  }
 ],
 "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}