DataClean.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(7788, 95)\n",
      "(3818, 92)\n",
      "(3585, 95)\n",
      "after deleting columns\n",
      "(7788, 95)\n",
      "(3818, 92)\n",
      "(3585, 95)\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "import sys\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "pathboston=\"C:/Users/singh/Desktop/AIRBNB DATA/BOSTON/listings.csv\"\n",
    "pathseattle=\"C:/Users/singh/Desktop/AIRBNB DATA/Seattle/listings_seattle.csv\"\n",
    "pathdc=\"C:/Users/singh/Desktop/AIRBNB DATA/DC/listings_dc.csv\"\n",
    "\n",
    "boston_df = pd.read_csv(pathboston)\n",
    "seattle_df = pd.read_csv(pathseattle)\n",
    "dc_df = pd.read_csv(pathdc)\n",
    "print(dc_df.shape)\n",
    "print(seattle_df.shape)\n",
    "print(boston_df.shape)\n",
    "\n",
    "\n",
    "# boston_df = boston_df.drop(['access', 'interaction', 'house_rules'], axis=1)\n",
    "# dc_df = dc_df.drop(['access', 'interaction', 'house_rules'], axis=1)\n",
    "#same number columns\n",
    "print(\"after deleting columns\")\n",
    "print(dc_df.shape)\n",
    "print(seattle_df.shape)\n",
    "print(boston_df.shape)\n",
    "#f = pd.merge(left=dc_df, right=seattle_df, how='outer', on='id')\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#merge CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = pd.concat([boston_df, dc_df,seattle_df])\n",
    "df_new.shape\n",
    "df_new.to_csv(\"Rawfinal.csv\", index=None)\n",
    "print(\"rawfile saved\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                              access  accommodates  \\\n",
      "0  You will have access to 2 bedrooms, a living r...             4   \n",
      "1  Apt has one more bedroom (which I use) and lar...             2   \n",
      "2  I am living in the apartment during your stay,...             2   \n",
      "3  The basement has a washer dryer and gym area. ...             4   \n",
      "4  You will have access to the front and side por...             2   \n",
      "\n",
      "                                           amenities  availability_30  \\\n",
      "0  {TV,\"Wireless Internet\",Kitchen,\"Free Parking ...                0   \n",
      "1  {TV,Internet,\"Wireless Internet\",\"Air Conditio...               26   \n",
      "2  {TV,\"Cable TV\",\"Wireless Internet\",\"Air Condit...               19   \n",
      "3  {TV,Internet,\"Wireless Internet\",\"Air Conditio...                6   \n",
      "4  {Internet,\"Wireless Internet\",\"Air Conditionin...               13   \n",
      "\n",
      "   availability_365  availability_60  availability_90  bathrooms  bed_type  \\\n",
      "0                 0                0                0        1.5  Real Bed   \n",
      "1               359               54               84        1.0  Real Bed   \n",
      "2               319               46               61        1.0  Real Bed   \n",
      "3                98               16               26        1.0  Real Bed   \n",
      "4               334               34               59        1.5  Real Bed   \n",
      "\n",
      "   bedrooms   ...                                                space  \\\n",
      "0       2.0   ...    The house has an open and cozy feel at the sam...   \n",
      "1       1.0   ...    Small but cozy and quite room with a full size...   \n",
      "2       1.0   ...    Come stay with a friendly, middle-aged guy in ...   \n",
      "3       1.0   ...    Most places you find in Boston are small howev...   \n",
      "4       1.0   ...    Clean, attractive, private room, one block fro...   \n",
      "\n",
      "   square_feet state                                             street  \\\n",
      "0          NaN    MA      Birch Street, Boston, MA 02131, United States   \n",
      "1          NaN    MA  Pinehurst Street, Boston, MA 02131, United States   \n",
      "2          NaN    MA        Ardale St., Boston, MA 02131, United States   \n",
      "3          NaN    MA                          Boston, MA, United States   \n",
      "4          NaN    MA    Durnell Avenue, Boston, MA 02131, United States   \n",
      "\n",
      "                                             summary  \\\n",
      "0  Cozy, sunny, family home.  Master bedroom high...   \n",
      "1  Charming and quiet room in a second floor 1910...   \n",
      "2  Come stay with a friendly, middle-aged guy in ...   \n",
      "3  Come experience the comforts of home away from...   \n",
      "4  My comfy, clean and relaxing home is one block...   \n",
      "\n",
      "                                       thumbnail_url  \\\n",
      "0  https://a2.muscache.com/im/pictures/c0842db1-e...   \n",
      "1  https://a1.muscache.com/im/pictures/39327812/d...   \n",
      "2  https://a2.muscache.com/im/pictures/6ae8335d-9...   \n",
      "3  https://a2.muscache.com/im/pictures/39764190-1...   \n",
      "4  https://a1.muscache.com/im/pictures/97154760/8...   \n",
      "\n",
      "                                             transit weekly_price  \\\n",
      "0  The bus stop is 2 blocks away, and frequent. B...          NaN   \n",
      "1  Plenty of safe street parking. Bus stops a few...      $400.00   \n",
      "2  PUBLIC TRANSPORTATION: From the house, quick p...      $395.00   \n",
      "3  There are buses that stop right in front of th...          NaN   \n",
      "4  From Logan Airport  and South Station you have...          NaN   \n",
      "\n",
      "                                      xl_picture_url zipcode  \n",
      "0  https://a2.muscache.com/im/pictures/c0842db1-e...   02131  \n",
      "1  https://a1.muscache.com/im/pictures/39327812/d...   02131  \n",
      "2  https://a2.muscache.com/im/pictures/6ae8335d-9...   02131  \n",
      "3  https://a2.muscache.com/im/pictures/39764190-1...     NaN  \n",
      "4  https://a1.muscache.com/im/pictures/97154760/8...   02131  \n",
      "\n",
      "[5 rows x 95 columns]\n",
      "         id                                        picture_url  \\\n",
      "0  12147973  https://a2.muscache.com/im/pictures/c0842db1-e...   \n",
      "1   3075044  https://a1.muscache.com/im/pictures/39327812/d...   \n",
      "2      6976  https://a2.muscache.com/im/pictures/6ae8335d-9...   \n",
      "3   1436513  https://a2.muscache.com/im/pictures/39764190-1...   \n",
      "4   7651065  https://a1.muscache.com/im/pictures/97154760/8...   \n",
      "\n",
      "                                      xl_picture_url   host_id host_name  \\\n",
      "0  https://a2.muscache.com/im/pictures/c0842db1-e...  31303940  Virginia   \n",
      "1  https://a1.muscache.com/im/pictures/39327812/d...   2572247    Andrea   \n",
      "2  https://a2.muscache.com/im/pictures/6ae8335d-9...     16701      Phil   \n",
      "3  https://a2.muscache.com/im/pictures/39764190-1...   6031442    Meghna   \n",
      "4  https://a1.muscache.com/im/pictures/97154760/8...  15396970     Linda   \n",
      "\n",
      "  host_response_rate host_acceptance_rate  host_total_listings_count  \\\n",
      "0                NaN                  NaN                        1.0   \n",
      "1               100%                 100%                        1.0   \n",
      "2               100%                  88%                        1.0   \n",
      "3               100%                  50%                        1.0   \n",
      "4               100%                 100%                        1.0   \n",
      "\n",
      "  neighbourhood_cleansed    city        ...            price  \\\n",
      "0             Roslindale  Boston        ...          $250.00   \n",
      "1             Roslindale  Boston        ...           $65.00   \n",
      "2             Roslindale  Boston        ...           $65.00   \n",
      "3             Roslindale  Boston        ...           $75.00   \n",
      "4             Roslindale  Boston        ...           $79.00   \n",
      "\n",
      "  cancellation_policy weekly_price  monthly_price  security_deposit  \\\n",
      "0            moderate          NaN            NaN               NaN   \n",
      "1            moderate      $400.00            NaN            $95.00   \n",
      "2            moderate      $395.00      $1,350.00               NaN   \n",
      "3            moderate          NaN            NaN           $100.00   \n",
      "4            flexible          NaN            NaN               NaN   \n",
      "\n",
      "  cleaning_fee number_of_reviews  review_scores_rating  instant_bookable  \\\n",
      "0       $35.00                 0                   NaN                 f   \n",
      "1       $10.00                36                  94.0                 t   \n",
      "2          NaN                41                  98.0                 f   \n",
      "3       $50.00                 1                 100.0                 f   \n",
      "4       $15.00                29                  99.0                 f   \n",
      "\n",
      "   reviews_per_month  \n",
      "0                NaN  \n",
      "1               1.30  \n",
      "2               0.47  \n",
      "3               1.00  \n",
      "4               2.25  \n",
      "\n",
      "[5 rows x 31 columns]\n"
     ]
    }
   ],
   "source": [
    "print(df_new.head(5))\n",
    "#list(df_new)\n",
    "df_new1= df_new[['id',\n",
    " 'picture_url',\n",
    " 'xl_picture_url',\n",
    " 'host_id',\n",
    " 'host_name',\n",
    " 'host_response_rate',\n",
    " 'host_acceptance_rate',\n",
    " 'host_total_listings_count',\n",
    " 'neighbourhood_cleansed',\n",
    " 'city',\n",
    " 'state',\n",
    " 'zipcode',\n",
    " 'country',\n",
    " 'latitude',\n",
    " 'longitude',\n",
    " 'property_type',\n",
    " 'room_type',\n",
    " 'accommodates',\n",
    " 'bathrooms',\n",
    " 'beds',\n",
    " 'amenities',\n",
    " 'price',\n",
    "   'cancellation_policy',              \n",
    " 'weekly_price',\n",
    " 'monthly_price',\n",
    " 'security_deposit',\n",
    " 'cleaning_fee',\n",
    " 'number_of_reviews',\n",
    " 'review_scores_rating',\n",
    " 'instant_bookable',\n",
    " 'reviews_per_month']]\n",
    "print(df_new1.head(5))\n",
    "                \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py:3660: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self._update_inplace(new_data)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(15191, 31)"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new1.shape\n",
    "# fill 0  with no data\n",
    "\n",
    "df_new1.id.fillna(0, inplace=True)\n",
    "\n",
    "df_new1.reviews_per_month.fillna(0, inplace=True)\n",
    "df_new1.instant_bookable.fillna('f', inplace=True)\n",
    "df_new1.review_scores_rating.fillna(0, inplace=True)\n",
    "\n",
    "df_new1.number_of_reviews.fillna(0, inplace=True)\n",
    "df_new1.cleaning_fee.fillna(0, inplace=True)\n",
    "df_new1.security_deposit.fillna(0, inplace=True)\n",
    "df_new1.monthly_price.fillna(0, inplace=True)\n",
    "#df_new1.amenities.fillna('na', inplace=True)\n",
    "df_new1.amenities.fillna(0, inplace=True)\n",
    "df_new1.price.fillna(0, inplace=True)\n",
    "\n",
    "df_new1.weekly_price.fillna(0, inplace=True)\n",
    "df_new1.beds.fillna(0, inplace=True)\n",
    "df_new1.bathrooms.fillna(0, inplace=True)\n",
    "df_new1.accommodates.fillna(0, inplace=True)\n",
    "df_new1.room_type.fillna(0, inplace=True)\n",
    "df_new1.property_type.fillna(0, inplace=True)\n",
    "df_new1.longitude.fillna(0, inplace=True)\n",
    "df_new1.latitude.fillna(0, inplace=True)\n",
    "df_new1.country.fillna(0, inplace=True)\n",
    "df_new1.zipcode.fillna(0, inplace=True)\n",
    "df_new1.state.fillna(0, inplace=True)\n",
    "df_new1.city.fillna(0, inplace=True)\n",
    "df_new1.neighbourhood_cleansed.fillna(0, inplace=True)\n",
    "df_new1.host_total_listings_count.fillna(0, inplace=True)\n",
    "df_new1.host_acceptance_rate.fillna(0, inplace=True)\n",
    "df_new1.host_response_rate.fillna(0, inplace=True)\n",
    "df_new1.host_name.fillna(0, inplace=True)\n",
    "df_new1.host_id.fillna(0, inplace=True)\n",
    "df_new1.xl_picture_url.fillna(0, inplace=True)\n",
    "df_new1.picture_url.fillna(0, inplace=True)\n",
    "df_new1.shape\n",
    "#remove rows with 0 values\n",
    "\n",
    "\n",
    "#df_new1.to_csv('Clean.csv', index=False)\n",
    "df_new1.shape\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(15036, 31)\n",
      "csv created\n"
     ]
    }
   ],
   "source": [
    "df_new1 = df_new1[df_new1.id != 0]  #remove  rows with no id\n",
    "df_new1 = df_new1[df_new1.price != 0] # remove rows with no price for venue\n",
    "df_new1 = df_new1[df_new1.longitude != 0] # remove rows with no latitude for venue\n",
    "df_new1 = df_new1[df_new1.latitude != 0] # remove rows with no longitude for venue\n",
    "df_new1 = df_new1[df_new1.zipcode != 0] # remove rows with no longitude for venue\n",
    "df_new1 = df_new1[df_new1.state != 0] # remove rows with no longitude for venue\n",
    "df_new1 = df_new1[df_new1.city != 0] # remove rows with no longitude for venue\n",
    "df_new1 = df_new1[df_new1.host_name != 0] # remove rows with no longitude for venue\n",
    "df_new1 = df_new1[df_new1.host_id != 0] # remove rows with no longitude for venue\n",
    "\n",
    "#df_new1.host_id.shape\n",
    "\n",
    "print(df_new1.shape)\n",
    "\n",
    "df_new1.to_csv(\"clean.csv\", index=None)\n",
    "\n",
    "print(\"csv created\")"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}