1_Reading_Data_Files.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " # Reading data files: In search for memory* efficient ways!\n",
    "  \n",
    " In most cases, the initial step of data analysis is to get an overall idea about the content of a given data file. Here some basic techniques of opening and reading files are described with examples\n",
    " \n",
    "######  (*Both comuter's and the data analyst's memories)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  ## (A) Raw python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Opening a file named 'access_subset.log'by read() method and printing its content:\n",
    "# The read() method returns the specified number of bytes from the file. Default is -1 which means the whole file\n",
    "\n",
    "with open('access_subset.log') as f:\n",
    "    content = f.read()\n",
    "    print(content)\n",
    "\n",
    "'''\n",
    "The following slightly different codes will also give similar output:-\n",
    "\n",
    "with open('access_subset.log') as f:\n",
    "    content = f.read()\n",
    "    lines = content.split(\"\\n\")\n",
    "    print(content)\n",
    " ..................   \n",
    "f = open('access_subset.log')\n",
    "content = f.read()\n",
    "lines = content.split(\"\\n\")\n",
    "f.close()\n",
    "print(lines)\n",
    "'''\n",
    "\n",
    "# ! This would print the whole content of the file.\n",
    "# So, trying this method only to have an overview might not be a good idea \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "104.229.180.74 - - [04/Mar/2018:21:32:18 +0000] \"GET / HTTP/1.1\" 404 571 \"-\" \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.140 Safari/537.36\" \"-\"\n",
      "104.229.180.74 - - [04/Mar/2018:21:32:18 +0000] \"GET /favicon.ico HTTP/1.1\" 404 571 \"https://hilite.clarksonmsda.org/\" \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.140 Safari/537.36\" \"-\"\n",
      "104.229.180.74 - - [04/Mar/2018:21:32:24 +0000] \"GET / HTTP/1.1\" 404 571 \"-\" \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.140 Safari/537.36\" \"-\"\n",
      "104.229.180.74 - - [05/Mar/2018:02:12:32 +0000] \"GET / HTTP/1.1\" 200 2518 \"-\" \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.140 Safari/537.36\" \"-\"\n"
     ]
    }
   ],
   "source": [
    "# Reading lines into a list of lists and then iterating over it:\n",
    "\n",
    "with open('access_subset.log') as f:\n",
    "    content = f.read()\n",
    "    lines = content.split(\"\\n\")\n",
    "\n",
    "    n=0 #counter to limit print content\n",
    "    for line in lines:\n",
    "        print(line)\n",
    "        if n==3:\n",
    "            break\n",
    "        n+=1\n",
    "# This is a better approach because we could print only a limited portion of the file using a for loop\n",
    "# ! However, reading ALL LINES into a list of lists and then iterating over it is still not an efficient solution for large files, i.e., file with size in GBs. "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "We are looking for solutions where we read & process only one line at a time while iterating through all rows of a file, so that minimum memory is utilized and hence, the memory would not be a bottleneck for reading big data files."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  ## (B) CSV module\n",
    "\n",
    "CSV module provides two different classes to read the contents of a csv file:  csv.reader and csv.DictReader\n",
    "\n",
    "With csv module’s reader class object we can iterate over the lines of a csv file as a list of values, where each value in the list is a cell value. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (i) Using the csv module's reader class object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'list'>\n",
      "2\n",
      "['104.229.180.74 - - [04/Mar/2018:21:32:18 +0000] \"GET / HTTP/1.1\" 404 571 \"-\" \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML', ' like Gecko) Chrome/64.0.3282.140 Safari/537.36\" \"-\"']\n",
      "<class 'list'>\n",
      "2\n",
      "['104.229.180.74 - - [04/Mar/2018:21:32:18 +0000] \"GET /favicon.ico HTTP/1.1\" 404 571 \"https://hilite.clarksonmsda.org/\" \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML', ' like Gecko) Chrome/64.0.3282.140 Safari/537.36\" \"-\"']\n",
      "<class 'list'>\n",
      "2\n",
      "['104.229.180.74 - - [04/Mar/2018:21:32:24 +0000] \"GET / HTTP/1.1\" 404 571 \"-\" \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML', ' like Gecko) Chrome/64.0.3282.140 Safari/537.36\" \"-\"']\n",
      "<class 'list'>\n",
      "2\n",
      "['104.229.180.74 - - [05/Mar/2018:02:12:32 +0000] \"GET / HTTP/1.1\" 200 2518 \"-\" \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML', ' like Gecko) Chrome/64.0.3282.140 Safari/537.36\" \"-\"']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"\\nIt iterates over all the rows of access_subset.log file.\\nFor each row it fetched the contents of that row as a list and printed that list.\\n\\nHow did it work ?\\n\\nIt performed the following steps:-\\n\\nOpen the file in read mode and create a file object. [with open('access_subset.log', 'r') as f:]\\n\\nCreate a reader object (iterator) by passing file object in csv.reader() function. [rows = reader(f)]\\n\\nNow, once we have an iterator (returned by the reader object) we used this iterator with for loop to read\\nindividual row of the csv as list of values (where each value in the list represents an individual cell).\\n\\nIn this way, only one line will be in the memory at a time while iterating through csv file. \\n\""
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#counter to limit print content\n",
    "n=0\n",
    "\n",
    "#Importing reader function of csv module\n",
    "from csv import reader\n",
    "\n",
    "# open file in read mode\n",
    "with open('access_subset.log', 'r') as f:\n",
    "    \n",
    "    # pass the file object to reader() to get the reader object\n",
    "    rows = reader(f)\n",
    "    \n",
    "    # Iterate over each of the rows in the csv using reader object\n",
    "    for row in rows:\n",
    "        # row variable is a list that represents a row in csv\n",
    "        print(type(row))\n",
    "        print(len(row))\n",
    "        print(row)\n",
    "        if n==3:\n",
    "            break\n",
    "        n+=1\n",
    "'''\n",
    "It iterates over all the rows of access_subset.log file.\n",
    "For each row it fetched the contents of that row as a list and printed that list.\n",
    "\n",
    "How did it work ?\n",
    "\n",
    "It performed the following steps:-\n",
    "\n",
    "Open the file in read mode and create a file object. [with open('access_subset.log', 'r') as f:]\n",
    "\n",
    "Create a reader object (iterator) by passing file object in csv.reader() function. [rows = reader(f)]\n",
    "\n",
    "Now, once we have an iterator (returned by the reader object) we used this iterator with for loop to read\n",
    "individual row of the csv as list of values (where each value in the list represents an individual cell).\n",
    "\n",
    "In this way, only one line will be in the memory at a time while iterating through csv file. \n",
    "'''\n",
    "\n",
    "# Thus, we got a memory efficient solution :-)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (ii) Using csv module's DictReader object\n",
    "With csv module’s DictReader class object we can iterate over the lines of a csv file as a dictionary i.e.:\n",
    "for each row a dictionary is returned, which contains the pair of column names and cell values for that row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OrderedDict([('MAC', 'e0:91:f5:de:fa:4f'), ('SSID', 'NETGEAR-5G'), ('AuthMode', '[WPS][ESS]'), ('FirstSeen', '5/9/2012 2:02'), ('Channel', '36'), ('RSSI', '-67'), ('CurrentLatitude', '44.67023722'), ('CurrentLongitude', '-74.98363701'), ('AltitudeMeters', '104.1999969'), ('AccuracyMeters', '7'), ('Type', 'WIFI')])\n",
      "OrderedDict([('MAC', 'e2:91:f5:de:fa:51'), ('SSID', 'bonesaw_Guest'), ('AuthMode', '[WPA-PSK-TKIP+CCMP][WPA2-PSK-TKIP+CCMP][ESS]'), ('FirstSeen', '5/9/2012 2:02'), ('Channel', '11'), ('RSSI', '-62'), ('CurrentLatitude', '44.67023722'), ('CurrentLongitude', '-74.98363701'), ('AltitudeMeters', '104.1999969'), ('AccuracyMeters', '7'), ('Type', 'WIFI')])\n",
      "OrderedDict([('MAC', 'e0:91:f5:de:fa:50'), ('SSID', 'BONESAW_HQ'), ('AuthMode', '[WPA-PSK-TKIP+CCMP][WPA2-PSK-TKIP+CCMP][WPS][ESS]'), ('FirstSeen', '5/9/2012 2:02'), ('Channel', '11'), ('RSSI', '-62'), ('CurrentLatitude', '44.67023722'), ('CurrentLongitude', '-74.98363701'), ('AltitudeMeters', '104.1999969'), ('AccuracyMeters', '7'), ('Type', 'WIFI')])\n",
      "OrderedDict([('MAC', 'e0:91:f5:5e:34:60'), ('SSID', 'Happy_Clam'), ('AuthMode', '[WPA-PSK-TKIP+CCMP][WPA2-PSK-TKIP+CCMP][WPS][ESS]'), ('FirstSeen', '5/9/2012 2:02'), ('Channel', '6'), ('RSSI', '-61'), ('CurrentLatitude', '44.67023722'), ('CurrentLongitude', '-74.98363701'), ('AltitudeMeters', '104.1999969'), ('AccuracyMeters', '7'), ('Type', 'WIFI')])\n"
     ]
    }
   ],
   "source": [
    "n=0\n",
    "from csv import DictReader\n",
    "\n",
    "with open('wifi_list.csv', 'r') as f:\n",
    "    \n",
    "    # pass the file object to DictReader() to get the DictReader object\n",
    "    rows = DictReader(f)\n",
    "    \n",
    "    # iterate over each line as a ordered dictionary\n",
    "    for row in rows:\n",
    "        \n",
    "        # row variable is a dictionary that represents a row in csv\n",
    "        print(row)\n",
    "        if n==3:\n",
    "            break\n",
    "        n+=1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## (C) Some other useful things"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (i) Reading a csv file without header"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (a) The book-ish method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2011', 'KS', 'Kansas', 'Tobacco Use - Survey Data', 'Cigarette Use (Youth)', 'Smoking Status', 'Ever Cigarette Smoker', 'YRBSS', 'Ever', '%', 'Percentage', '39.5', '', '', '2.4', '34.8', '44.4', '1080', 'Overall', 'All Ages', 'White', 'High School', '(38.3477403, -98.200781227)', 'BEH', '106BEH', '169SSA', '1GEN', '8AGE', '5RAC', '2EDU', 'YRB03', '3']\n",
      "['1999', 'WY', 'Wyoming', 'Tobacco Use - Survey Data', 'Cigarette Use (Youth)', 'Smoking Status', 'Current Cigarette Smoker', 'YRBSS', 'Current', '%', 'Percentage', '34.6', '', '', '2.2', '30.3', '39.1', '747', 'Male', 'All Ages', 'All Races', 'High School', '(43.235541343, -108.109830353)', 'BEH', '106BEH', '169SSA', '2GEN', '8AGE', '6RAC', '2EDU', 'YRB01', '1']\n",
      "['2009', 'DE', 'Delaware', 'Tobacco Use - Survey Data', 'Cigarette Use (Youth)', 'Smoking Status', 'Current Cigarette Smoker', 'YRBSS', 'Current', '%', 'Percentage', '8.3', '', '', '0.8', '6.8', '10.1', '1791', 'Female', 'All Ages', 'All Races', 'Middle School', '(39.008830667, -75.577741168)', 'BEH', '106BEH', '169SSA', '3GEN', '8AGE', '6RAC', '1EDU', 'YRB01', '1']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"\\nIt would skip the header row of csv file and iterate over all the remaining rows of 'tobaccouse.csv' file. \\nFor each row it fetched the contents of that row as a list and printed that list. \\nIt initially saved the header row in a separate variable.\\n\\nHow did it work ?\\n\\nAs reader() function returns an iterator object, which we can use with Python for loop to iterate over the rows.\\nBut in the above example we called the next() function on this iterator object initially,\\nwhich returned the first row of csv. \\nAfter that we used the iterator object with for loop to iterate over remaining rows of the csv file.\\n\\n\""
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# skip first line i.e. read header first and then iterate over each row od csv as a list\n",
    "n=0\n",
    "with open('tobaccouse.csv', 'r') as f:\n",
    "    rows = reader(f)\n",
    "    header = next(rows)\n",
    "    # Check file as empty\n",
    "    if header != None:\n",
    "        # Iterate over each row after the header in the csv\n",
    "        for row in rows:\n",
    "            # row variable is a list that represents a row in csv\n",
    "            print(row)\n",
    "            if n==2:\n",
    "                break\n",
    "            n+=1\n",
    "    \n",
    "'''\n",
    "It would skip the header row of csv file and iterate over all the remaining rows of 'tobaccouse.csv' file. \n",
    "For each row it fetched the contents of that row as a list and printed that list. \n",
    "It initially saved the header row in a separate variable.\n",
    "\n",
    "How did it work ?\n",
    "\n",
    "As reader() function returns an iterator object, which we can use with Python for loop to iterate over the rows.\n",
    "But in the above example we called the next() function on this iterator object initially,\n",
    "which returned the first row of csv. \n",
    "After that we used the iterator object with for loop to iterate over remaining rows of the csv file.\n",
    "\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### (b) Reading a csv file without header: A much simpler approach!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2011', 'KS', 'Kansas', 'Tobacco Use - Survey Data', 'Cigarette Use (Youth)', 'Smoking Status', 'Ever Cigarette Smoker', 'YRBSS', 'Ever', '%', 'Percentage', '39.5', '', '', '2.4', '34.8', '44.4', '1080', 'Overall', 'All Ages', 'White', 'High School', '(38.3477403, -98.200781227)', 'BEH', '106BEH', '169SSA', '1GEN', '8AGE', '5RAC', '2EDU', 'YRB03', '3']\n",
      "['1999', 'WY', 'Wyoming', 'Tobacco Use - Survey Data', 'Cigarette Use (Youth)', 'Smoking Status', 'Current Cigarette Smoker', 'YRBSS', 'Current', '%', 'Percentage', '34.6', '', '', '2.2', '30.3', '39.1', '747', 'Male', 'All Ages', 'All Races', 'High School', '(43.235541343, -108.109830353)', 'BEH', '106BEH', '169SSA', '2GEN', '8AGE', '6RAC', '2EDU', 'YRB01', '1']\n",
      "['2009', 'DE', 'Delaware', 'Tobacco Use - Survey Data', 'Cigarette Use (Youth)', 'Smoking Status', 'Current Cigarette Smoker', 'YRBSS', 'Current', '%', 'Percentage', '8.3', '', '', '0.8', '6.8', '10.1', '1791', 'Female', 'All Ages', 'All Races', 'Middle School', '(39.008830667, -75.577741168)', 'BEH', '106BEH', '169SSA', '3GEN', '8AGE', '6RAC', '1EDU', 'YRB01', '1']\n",
      "['1995', 'WY', 'Wyoming', 'Tobacco Use - Survey Data', 'Cigarette Use (Youth)', 'Smoking Status', 'Frequent Cigarette Smoker', 'YRBSS', 'Frequent', '%', 'Percentage', '19.6', '', '', '1.5', '16.7', '22.8', '798', 'Female', 'All Ages', 'All Races', 'High School', '(43.235541343, -108.109830353)', 'BEH', '106BEH', '169SSA', '3GEN', '8AGE', '6RAC', '2EDU', 'YRB02', '2']\n"
     ]
    }
   ],
   "source": [
    "n=0\n",
    "with open('tobaccouse.csv','r') as f:\n",
    "    rows = reader(f)\n",
    "    for row in rows:\n",
    "        if n>0:\n",
    "            print(row)\n",
    "            if n>3:\n",
    "                break\n",
    "        n+=1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (ii) A (very) handy method to get a list of dictionary out of the serialized data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'MAC': 'e2:91:f5:de:fa:51', 'SSID': 'bonesaw_Guest', 'AuthMode': '[WPA-PSK-TKIP+CCMP][WPA2-PSK-TKIP+CCMP][ESS]', 'FirstSeen': '5/9/2012 2:02', 'Channel': '11', 'RSSI': '-62', 'CurrentLatitude': '44.67023722', 'CurrentLongitude': '-74.98363701', 'AltitudeMeters': '104.1999969', 'AccuracyMeters': '7', 'Type': 'WIFI'}\n"
     ]
    }
   ],
   "source": [
    "#Use the following code to get a list of dictionary\n",
    "\n",
    "import csv\n",
    "with open('wifi_list.csv') as f:\n",
    "    data = [{k: str(v) for k, v in row.items()}\n",
    "    for row in csv.DictReader(f, skipinitialspace=True)]\n",
    "    \n",
    "    print(data[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (iii) Implementing an efficient search technique (Like the  'WHERE' clause of SQL) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "185\n"
     ]
    }
   ],
   "source": [
    "# Open wifi.csv iterate through file. \n",
    "# Print the line number WHERE the row contains ‘CPH’\n",
    "with open ('wifi_list.csv', 'r') as f:\n",
    "    i=0\n",
    "    for line in f:\n",
    "        if 'CPH' in line:\n",
    "            print(i)\n",
    "            break\n",
    "        i+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "97\n"
     ]
    }
   ],
   "source": [
    "# Open wifi.csv iterate through file.\n",
    "# Print the total number of lines which contains ‘CPH’\n",
    "n=0\n",
    "with open ('wifi_list.csv', 'r') as f:\n",
    "    i=0\n",
    "    for line in f:\n",
    "        if 'CPH' in line:\n",
    "            n+=1\n",
    "            \n",
    "        i+=1\n",
    "print(n)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "97\n"
     ]
    }
   ],
   "source": [
    "# Use the CSV parser to read each line at a time as a list.\n",
    "# Count the number of SSIDs which contain CPH.\n",
    "import csv\n",
    "n=0\n",
    "with open ('wifi_list.csv', 'r') as f:\n",
    "    reader = csv.reader(f)\n",
    "    for  line in reader:\n",
    "        if 'CPH' in line[1]:\n",
    "            n +=1                   \n",
    "print(n)"
   ]
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}