from pymongo import MongoClient

import pandas as pd

from datetime import datetime

client = MongoClient()
client = MongoClient('localhost', 27017)

db = client.airbnb
cursor = db.Rawdata.find()

data = pd.DataFrame(list(cursor))

data.head(1)

data.columns

"""
Explanation: Data Preparation for Linear Regression and Classification
From Command Line - Import CSV file (From Data Preparation Part 1) into MongoDB
mongoimport --db airbnb --type csv --file listings_31Mar.csv --headerline -c Rawdata
End of explanation
"""


data = data.drop("listing_url",axis=1)
data = data.drop("scrape_id",axis=1)
data = data.drop("name",axis=1)
data = data.drop("notes",axis=1)
data = data.drop("access",axis=1)

data = data.drop("thumbnail_url",axis=1)
data = data.drop("medium_url",axis=1)

data = data.drop("picture_url",axis=1)
data = data.drop("xl_picture_url",axis=1)

data = data.drop("host_url",axis=1)
data = data.drop("host_thumbnail_url",axis=1)
data = data.drop("host_picture_url",axis=1)
data = data.drop("street",axis=1)
data = data.drop("neighbourhood",axis=1)
data = data.drop("neighbourhood_cleansed",axis=1)


data = data.drop("city",axis=1)
data = data.drop("state",axis=1)
data = data.drop("zipcode",axis=1)
data = data.drop("market",axis=1)
data = data.drop("smart_location",axis=1)


data = data.drop("country_code",axis=1)
data = data.drop("country",axis=1)
data = data.drop("is_location_exact",axis=1)
data = data.drop("property_type",axis=1)
data = data.drop("bed_type",axis=1)
data = data.drop("amenities",axis=1)
data = data.drop("square_feet",axis=1)
data = data.drop("weekly_price",axis=1)
data = data.drop("monthly_price",axis=1)
data = data.drop("availability_30",axis=1)
data = data.drop("availability_60",axis=1)
data = data.drop("availability_90",axis=1)
data = data.drop("calendar_last_scraped",axis=1)

data = data.drop("license",axis=1)
data = data.drop("jurisdiction_names",axis=1)
data = data.drop("first_review",axis=1)
data = data.drop("last_review",axis=1)

data = data.drop("Shampoo",axis=1)

data = data.drop("nearest_attr_lat",axis=1)
data = data.drop("nearest_attr_long",axis=1)
data = data.drop("Dryer",axis=1)
data = data.drop("Doorman",axis=1)

data = data.drop("Essentials",axis=1)
#data = data.drop("translation missing: en.hosting_amenity_50",axis=1)
data = data.drop("Washer",axis=1)
data = data.drop("Washer / Dryer",axis=1)
data = data.drop("First aid kit",axis=1)
data = data.drop("Smoke detector",axis=1)

#data = data.drop("translation missing: en.hosting_amenity_49",axis=1)
data = data.drop("Hangers",axis=1)
data = data.drop("Fire extinguisher",axis=1)


data = data.drop("Iron",axis=1)
data = data.drop("Carbon monoxide detector",axis=1)
data = data.drop("Wireless Internet",axis=1)
data = data.drop("Laptop friendly workspace",axis=1)
data = data.drop("Hot tub",axis=1)
data = data.drop("Dog(s)",axis=1)
data = data.drop("Cat(s)",axis=1)

data = data.drop("Buzzer/wireless intercom",axis=1)
data = data.drop("Hair dryer",axis=1)
data = data.drop("Safety card",axis=1)

data = data.drop("last_scraped",axis=1)


data = data.drop("house_rules",axis=1)
data = data.drop("interaction",axis=1)
data = data.drop("transit",axis=1)
data = data.drop("neighborhood_overview",axis=1)
data = data.drop("experiences_offered",axis=1)

data = data.drop("id",axis=1)
data = data.drop("summary",axis=1)
data = data.drop("space",axis=1)
data = data.drop("description",axis=1)
data = data.drop("host_id",axis=1)
data = data.drop("host_name",axis=1)

data = data.drop("host_about",axis=1)
data = data.drop("latitude",axis=1)
data = data.drop("longitude",axis=1)
data = data.drop("host_neighbourhood",axis=1)
data = data.drop("host_location",axis=1)
data = data.drop("calendar_updated",axis=1)
data = data.drop("host_listings_count",axis=1)


#data = data.drop("Unnamed: 0",axis=1)


data = data.drop("calculated_host_listings_count",axis=1)

data = data.drop("host_acceptance_rate",axis=1)

data.head(1)

data = data.drop("",axis=1)

data.dtypes

data.host_response_rate.tail()

"""
Explanation: Drop columns that are not important
End of explanation
"""


x = pd.to_datetime(data.host_since)

x.head()

today = '2016-04-01'

y = datetime.strptime('2017-04-01', '%Y-%m-%d')

x[16]

y-x

data["host_since_days"] = y-x

data = data.drop("host_since",axis=1)

data = data.drop("host_response_time",axis=1)

data.head(1)

"""
Explanation: Convert string date to type date, then convert it to number of days since user became a host till date of analysis (4th april 2017)
End of explanation
"""


test_host_response = data.host_response_rate

a = test_host_response.map(lambda x: str(x)[:-1])

for i in range(0,len(a)):
    print(i)
    if a[i] == "na":
        continue
    if a[i] == "":
        continue
    else:
        a[i] = int(a[i])
        a[i] = a[i]/100

"""
Explanation: convert host_response_rate from string to integer. if "na", convert to not a number (NaN)
End of explanation
"""


data["host_response_rate"] = a

test_superhost = data['host_is_superhost']

"""
Explanation: for i in range(0,len(a)):
    if a[i] == "na":
        a[i] = float("nan")
End of explanation
"""


a = test_superhost.str.replace('t', '1')
a = a.str.replace('f', '0')

test_superhost.isnull().sum()

"""
Explanation: a = test_superhost.astype("str")
End of explanation
"""


data['host_is_superhost'] = a

data = data.drop("host_verifications",axis=1)

data.head()

"""
Explanation: data['host_is_superhost'] = data['host_is_superhost'].astype("str")
data['host_is_superhost'] = data['host_is_superhost'].str.replace('t', '1')
data['host_is_superhost'] = data['host_is_superhost'].str.replace('f', '0')
data['host_is_superhost'] = data.host_is_superhost.astype(int)
End of explanation
"""


df_dummies1= pd.get_dummies(data, prefix='neighbourhood', columns=['neighbourhood_group_cleansed'])

df_dummies2= pd.get_dummies(df_dummies1, prefix='roomtype', columns=['room_type'])

test_profilepic = df_dummies2['host_has_profile_pic']
a = test_profilepic.str.replace('t', '1')
a = a.str.replace('f', '0')

df_dummies2["host_has_profile_pic"] = a

test_host_identity_verified = df_dummies2['host_identity_verified']
a = test_host_identity_verified.str.replace('t', '1')
a = a.str.replace('f', '0')

df_dummies2["host_identity_verified"] = a

df_dummies2 = df_dummies2.drop("has_availability",axis=1)

df_dummies2 = df_dummies2.drop("requires_license",axis=1)

test_instant_bookable = df_dummies2['instant_bookable']
a = test_instant_bookable.str.replace('t', '1')
a = a.str.replace('f', '0')

df_dummies2['instant_bookable'] = a

df_dummies2.head()

df_dummies3= pd.get_dummies(df_dummies2, prefix='cancellation_policy', columns=['cancellation_policy'])

test_instant_bookable = df_dummies3['require_guest_profile_picture']
a = test_instant_bookable.str.replace('t', '1')
a = a.str.replace('f', '0')

df_dummies3['require_guest_profile_picture'] = a

df_dummies3["require_guest_phone_verification"].head()

df_dummies3["require_guest_phone_verification"].isnull().sum()

test_phone = df_dummies3['require_guest_phone_verification']
a = test_phone.str.replace('t', '1')
a = a.str.replace('f', '0')

df_dummies3['require_guest_phone_verification'] = a

df_dummies3.head()

df_dummies3.nearest_attr_rating.head()

df_dummies3.nearest_attr_rating.isnull().sum()

len(df_dummies3)

data.host_since_days[0]

from datetime import timedelta

data.host_since_days[0].days

data.host_since_days.head()

data.host_since_days[1]

data['host_since_days'] = data['host_since_days'].apply(lambda x: x.days if pd.isnull(x) == False else 0)

#pd.DataFrame.to_csv(df_dummies3, "preprocessed_data.csv")

"""
Explanation: convert columns with string names to dummy variables
End of explanation
"""


