CSV_to_Pipe_Delim.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:14d585441d84d781dbf82834bedfc0c05db56db2f513f509dcb9d9b55593f359"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Changing CSV file to Pipe-delimited text"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = pd.read_csv('yelp_reviews.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 3,
       "text": [
        "Index([u'business_id', u'date', u'review_id', u'stars', u'text', u'type', u'user_id', u'votes.cool', u'votes.funny', u'votes.useful'], dtype=object)"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>business_id</th>\n",
        "      <th>date</th>\n",
        "      <th>review_id</th>\n",
        "      <th>stars</th>\n",
        "      <th>text</th>\n",
        "      <th>type</th>\n",
        "      <th>user_id</th>\n",
        "      <th>votes.cool</th>\n",
        "      <th>votes.funny</th>\n",
        "      <th>votes.useful</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 9yKzy9PApeiPPOUJEtnvkg</td>\n",
        "      <td> 2011-01-26</td>\n",
        "      <td> fWKvX83p0-ka4JS3dc6E5A</td>\n",
        "      <td> 5</td>\n",
        "      <td> My wife took me here on my birthday for breakf...</td>\n",
        "      <td> review</td>\n",
        "      <td> rLtl8ZkDX5vH5nAx9C3q5Q</td>\n",
        "      <td> 2</td>\n",
        "      <td> 0</td>\n",
        "      <td> 5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> ZRJwVLyzEJq1VAihDhYiow</td>\n",
        "      <td> 2011-07-27</td>\n",
        "      <td> IjZ33sJrzXqU-0X6U8NwyA</td>\n",
        "      <td> 5</td>\n",
        "      <td> I have no idea why some people give bad review...</td>\n",
        "      <td> review</td>\n",
        "      <td> 0a2KyEL0d3Yb1V6aivbIuQ</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 6oRAC4uyJCsJl1X0WZpVSA</td>\n",
        "      <td> 2012-06-14</td>\n",
        "      <td> IESLBzqUCLdSzSqm0eCSxQ</td>\n",
        "      <td> 4</td>\n",
        "      <td> love the gyro plate. Rice is so good and I als...</td>\n",
        "      <td> review</td>\n",
        "      <td> 0hT2KtfLiobPvh6cDC8JQg</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> _1QQZuf4zZOyFCvXc0o6Vg</td>\n",
        "      <td> 2010-05-27</td>\n",
        "      <td> G-WvGaISbqqaMHlNnByodA</td>\n",
        "      <td> 5</td>\n",
        "      <td> Rosie, Dakota, and I LOVE Chaparral Dog Park!!...</td>\n",
        "      <td> review</td>\n",
        "      <td> uZetl9T0NcROGOyFfughhg</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 6ozycU1RpktNG2-1BroVtw</td>\n",
        "      <td> 2012-01-05</td>\n",
        "      <td> 1uJFq2r5QfJG_6ExMRCaGw</td>\n",
        "      <td> 5</td>\n",
        "      <td> General Manager Scott Petello is a good egg!!!...</td>\n",
        "      <td> review</td>\n",
        "      <td> vYmM4KTsC8ZfQBg-j5MWkw</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "              business_id        date               review_id  stars  \\\n",
        "0  9yKzy9PApeiPPOUJEtnvkg  2011-01-26  fWKvX83p0-ka4JS3dc6E5A      5   \n",
        "1  ZRJwVLyzEJq1VAihDhYiow  2011-07-27  IjZ33sJrzXqU-0X6U8NwyA      5   \n",
        "2  6oRAC4uyJCsJl1X0WZpVSA  2012-06-14  IESLBzqUCLdSzSqm0eCSxQ      4   \n",
        "3  _1QQZuf4zZOyFCvXc0o6Vg  2010-05-27  G-WvGaISbqqaMHlNnByodA      5   \n",
        "4  6ozycU1RpktNG2-1BroVtw  2012-01-05  1uJFq2r5QfJG_6ExMRCaGw      5   \n",
        "\n",
        "                                                text    type  \\\n",
        "0  My wife took me here on my birthday for breakf...  review   \n",
        "1  I have no idea why some people give bad review...  review   \n",
        "2  love the gyro plate. Rice is so good and I als...  review   \n",
        "3  Rosie, Dakota, and I LOVE Chaparral Dog Park!!...  review   \n",
        "4  General Manager Scott Petello is a good egg!!!...  review   \n",
        "\n",
        "                  user_id  votes.cool  votes.funny  votes.useful  \n",
        "0  rLtl8ZkDX5vH5nAx9C3q5Q           2            0             5  \n",
        "1  0a2KyEL0d3Yb1V6aivbIuQ           0            0             0  \n",
        "2  0hT2KtfLiobPvh6cDC8JQg           0            0             1  \n",
        "3  uZetl9T0NcROGOyFfughhg           1            0             2  \n",
        "4  vYmM4KTsC8ZfQBg-j5MWkw           0            0             0  "
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# The text column might contain pipe characters. Let's check.\n",
      "print [item for item in data.text if '|' in str(item)][0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "We had a group dinner in one of their private rooms... the wine room, which as decent, obviously wines all around us, but a nice room for a group of 10. Anyways, the food we had was amazing... be sure to try the the appetizer - DRUNKEN BREAD (Cabrales fonduta', White Wine, Garlic, Herbs). For my entree I had the RIGATONCINI w/ Sweet & Spicy 'Schreiner's' Lamb Sausage, Heirloom Legumes, Local Greens, Charred Tomatoes, Chili, Pecorino. It was perfect with a nice kick to it. Definitely recommend it. And for dessert...PRADO SUNDAE House Made Vanilla Gelato | Apple Wood Bacon | Caramelized Bananas Amarone Cherries | Warm Chocolate Sauce | Roasted Almonds | Whip Cream | Biscotti Crumble... amazing... my first bacon dessert and it was worth it. Also, try the 'DULCE DE LECHE' BREAD PUDDING Bourbon Caramel Sauce | Vanilla Anglaise | Chantilly Cream. And get yourself a mojito while you are there, surprisingly good. Of course this is the main restaurant at the Montelucia Resort, so expect \"hotel pricing.\" The menus are also seasonal, so my recommendations now might be completely different when you go there.\n"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# some reviews contain only numbers, so need to cast to string \n",
      "# before doing a string replace operation\n",
      "data.text = data.text.map(lambda x: str(x))\n",
      "\n",
      "#There are a few pipe characters inside the text, so let's remove those.\n",
      "data.text = data.text.map(lambda x: x.replace('|', ' '))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print [item for item in data.text if '|' in str(item)]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[]\n"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "len(data)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "220000"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# print new pipe-delimited file\n",
      "data.to_csv('yelp_reviews2.txt', sep='|', encoding='utf-8', index=False)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    }
   ],
   "metadata": {}
  }
 ]
}