python-data-files.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python Data Files\n",
    "\n",
    "File operations using Python and libraries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CSV files in native Python\n",
    "\n",
    "Python provides a native module to perform CSV file operations.\n",
    "\n",
    "[Official documentation: CSV module in Python 2.7](https://docs.python.org/2/library/csv.html)\n",
    "\n",
    "### List to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "game-scores.csv\n",
      "vehicles.csv\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# For reading/writing CSV files\n",
    "import csv\n",
    "# For listing system file folders\n",
    "from subprocess import check_output\n",
    "\n",
    "# Use with open to ensure file is closed when block ends\n",
    "# The wb flag opens file for writing\n",
    "with open('data/fileops/vehicles.csv', 'wb') as csv_file:\n",
    "    # Prepare csv writer\n",
    "    wtr = csv.writer(csv_file, delimiter=',', quotechar='\"',\n",
    "                   quoting=csv.QUOTE_MINIMAL)\n",
    "    # Write CSV header row\n",
    "    wtr.writerow(['type', 'wheels', 'speed', 'weight', 'invented'])\n",
    "    # Write CSV data rows\n",
    "    wtr.writerow(['Scooter', 2, 150, 109.78, 1817])\n",
    "    wtr.writerow(['Car', 4, 250, 1818.45, 1885]) \n",
    "    wtr.writerow(['Plane', 10, 850, 270000, 1903])\n",
    "\n",
    "# Check file created\n",
    "print(check_output([\"ls\", \"data/fileops\"]).decode(\"utf8\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CSV to List"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type\twheels\tspeed\tweight\tinvented\n",
      "Scooter\t2\t150\t109.78\t1817\n",
      "Car\t4\t250\t1818.45\t1885\n",
      "Plane\t10\t850\t270000\t1903\n"
     ]
    }
   ],
   "source": [
    "# The rb flag opens file for reading\n",
    "with open('data/fileops/vehicles.csv', 'rb') as csv_file:\n",
    "    rdr = csv.reader(csv_file, delimiter=',', quotechar='\"')\n",
    "    for row in rdr:\n",
    "        print '\\t'.join(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dictionary to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "game-scores.csv\n",
      "vehicles.csv\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Dictionary data structures can be used to represent rows\n",
    "game1_scores = {'Game':'Quarter', 'Team A': 45, 'Team B': 90}\n",
    "game2_scores = {'Game':'Semi', 'Team A': 80, 'Team B': 32}\n",
    "game3_scores = {'Game':'Final', 'Team A': 70, 'Team B': 68}\n",
    "\n",
    "headers = ['Game', 'Team A', 'Team B']\n",
    "\n",
    "# Create CSV from dictionaries\n",
    "with open('data/fileops/game-scores.csv', 'wb') as df:\n",
    "    dict_wtr = csv.DictWriter(df, fieldnames=headers)\n",
    "    dict_wtr.writeheader()\n",
    "    dict_wtr.writerow(game1_scores)\n",
    "    dict_wtr.writerow(game2_scores)\n",
    "    dict_wtr.writerow(game3_scores)\n",
    "\n",
    "print(check_output([\"ls\", \"data/fileops\"]).decode(\"utf8\"))    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CSV to Dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Quarter\t45\t90\n",
      "Semi\t80\t32\n",
      "Final\t70\t68\n",
      "Game\tTeam A\tTeam B\n"
     ]
    }
   ],
   "source": [
    "# Read CSV into dictionary data structure\n",
    "with open('data/fileops/game-scores.csv', 'rb') as df:\n",
    "    dict_rdr = csv.DictReader(df)\n",
    "    for row in dict_rdr:\n",
    "        print('\\t'.join([row['Game'], row['Team A'], row['Team B']]))\n",
    "    print('\\t'.join(row.keys()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas for CSV file operations\n",
    "\n",
    "Pandas goal is to become the most powerful and flexible open source data analysis / manipulation tool available in any language. Pandas includes file operations capabilities for CSV, among other formats.\n",
    "\n",
    "CSV operations in Pandas are much faster than in native Python.\n",
    "\n",
    "### DataFrame to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Product</th>\n",
       "      <th>Region</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Josh</td>\n",
       "      <td>PC</td>\n",
       "      <td>North</td>\n",
       "      <td>34.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eli</td>\n",
       "      <td>Phone</td>\n",
       "      <td>South</td>\n",
       "      <td>12.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ram</td>\n",
       "      <td>SW</td>\n",
       "      <td>West</td>\n",
       "      <td>4.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bil</td>\n",
       "      <td>Cloud</td>\n",
       "      <td>East</td>\n",
       "      <td>31.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name Product Region  Sales\n",
       "0  Josh      PC  North  34.32\n",
       "1   Eli   Phone  South  12.10\n",
       "2   Ram      SW   West   4.77\n",
       "3   Bil   Cloud   East  31.63"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Create a DataFrame\n",
    "df = pd.DataFrame({\n",
    "        'Name' : ['Josh', 'Eli', 'Ram', 'Bil'],\n",
    "        'Sales' : [34.32, 12.1, 4.77, 31.63],\n",
    "        'Region' : ['North', 'South', 'West', 'East'],\n",
    "        'Product' : ['PC', 'Phone', 'SW', 'Cloud']})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "game-scores.csv\n",
      "sales.csv\n",
      "sales.xlsx\n",
      "vehicles.csv\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# DataFrame to CSV\n",
    "df.to_csv('data/fileops/sales.csv', index=False)\n",
    "\n",
    "print(check_output([\"ls\", \"data/fileops\"]).decode(\"utf8\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CSV to DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Product</th>\n",
       "      <th>Region</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Josh</td>\n",
       "      <td>PC</td>\n",
       "      <td>North</td>\n",
       "      <td>34.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eli</td>\n",
       "      <td>Phone</td>\n",
       "      <td>South</td>\n",
       "      <td>12.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ram</td>\n",
       "      <td>SW</td>\n",
       "      <td>West</td>\n",
       "      <td>4.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bil</td>\n",
       "      <td>Cloud</td>\n",
       "      <td>East</td>\n",
       "      <td>31.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name Product Region  Sales\n",
       "0  Josh      PC  North  34.32\n",
       "1   Eli   Phone  South  12.10\n",
       "2   Ram      SW   West   4.77\n",
       "3   Bil   Cloud   East  31.63"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# CSV to DataFrame\n",
    "df2 = pd.read_csv('data/fileops/sales.csv')\n",
    "\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataFrame to Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "game-scores.csv\n",
      "sales.csv\n",
      "sales.xlsx\n",
      "vehicles.csv\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# DataFrame to XLSX Excel file\n",
    "df.to_excel('data/fileops/sales.xlsx', index=False)\n",
    "\n",
    "print(check_output([\"ls\", \"data/fileops\"]).decode(\"utf8\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excel to DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Product</th>\n",
       "      <th>Region</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Josh</td>\n",
       "      <td>PC</td>\n",
       "      <td>North</td>\n",
       "      <td>34.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eli</td>\n",
       "      <td>Phone</td>\n",
       "      <td>South</td>\n",
       "      <td>12.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ram</td>\n",
       "      <td>SW</td>\n",
       "      <td>West</td>\n",
       "      <td>4.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bil</td>\n",
       "      <td>Cloud</td>\n",
       "      <td>East</td>\n",
       "      <td>31.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Name Product Region  Sales\n",
       "0  Josh      PC  North  34.32\n",
       "1   Eli   Phone  South  12.10\n",
       "2   Ram      SW   West   4.77\n",
       "3   Bil   Cloud   East  31.63"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Excel to DataFrame\n",
    "df3 = pd.read_excel('data/fileops/sales.xlsx')\n",
    "\n",
    "df3"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}