# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# <!--
# house (<u>***house_id*** </u>, name, <u>*host_id* </u>, description, room_type, latitude, longitud, post_time)
#
# host (<u>***host_id***</u>, host_name, about, join_date, location, identity_verified, pic_url)
#
# reviewer (<u>***reviewer_id***</u>, reviewer_name, about, join_date, location, identity_verified, pic_url)
#
# price (<u>**house_id**</u>, **date**, price)
#
# review (<u>**house_id**</u>, <u>**date**</u>, <u>***reviewer_id***</u>, review_name, host_name, score, comment, review_time, rating,reviewer_id, review_profile_path)   
# --->
#
#
#
#
#
# 详细爬取方法参考[这篇文章](https://cloud.tencent.com/developer/article/1523321)   
# [各种api详细信息](https://stevesie.com/apps/airbnb-api)
#
# ### House listing api
# https://www.airbnb.com/api/v2/pdp_listing_details/42471710?_format=for_rooms_show&adults=1&key=<KEY>&    
#
#  
# ### Review detail api
# https://airbnb.com/api/v2/reviews?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en&listing_id=42471710&role=guest&_format=for_p3&_limit=100
#
# ###  user(Host/Reviewer) api
# https://api.airbnb.com/v2/users/13720227?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en
#
# ### Price api
#
# https://www.airbnb.com/api/v2/pdp_listing_booking_details?_format=for_web_dateless&_intents=p3_book_it&_interaction_type=pageload&_p3_impression_id=p3_1552520120_con%2FMRJw4xlFD6zi&_parent_request_uuid=a23f08cc-f28a-48d8-85e9-c05735e18ff9&currency=USD&force_boost_unc_priority_message_type=&guests=1&key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en&show_smart_promotion=0&checkin=2021-11-04&checkout=2021-11-09&listing_id=42471710    
#
# csv文件转为sql insert into: https://www.convertcsv.com/csv-to-sql.htm

import json
import os
import time
import pickle
import requests
from tqdm.notebook import tqdm
import numpy as np
import pandas as pd
from bs4 import BeautifulSoup
from lxml import etree
from selenium import webdriver
import warnings
warnings.filterwarnings('ignore')


# +
# bro.get('https://www.airbnb.com/s/College-Park--MD/homes?adults=1&place_id=ChIJmUZxDR7Et4kRlW3TbeMNjOs&refinement_paths%5B%5D=%2Fhomes&checkin=2021-11-01&checkout=2021-11-04')
def getOnepage(browser, url):
    '''craw a searching page
    '''
    browser.get(url)
    time.sleep(3)
    soup = BeautifulSoup(browser.page_source,'lxml', fromEncoding = 'ISO-8859-1')
    return browser, browser.page_source, soup
# cityPagesource = bro.page_source
def get_list(soup):
    '''return a house listing from searching page
    '''
    urlList = []
    for i in soup.find_all("div", {"class": "_qlq27g"}):
        url = 'https://airbnb.com/' + i.a['href'] + '&locale=en' # force to request english version
        urlList.append(url)
    return urlList

def extract_date(date_string):
    '''extract date'''
    return date_string.split('T')[0]

def extract_house_id(url):
    house_id = url.split('airbnb.com//rooms/')[1].split('?adults')[0]
    return house_id


# -

bro = webdriver.Chrome()
urlList = []
for i in tqdm(range(0,10)):
    search_url = 'https://www.airbnb.com/s/College-Park--MD/homes?adults=1&place_id=ChIJmUZxDR7Et4kRlW3TbeMNjOs&refinement_paths%5B%5D=%2Fhomes&checkin=2021-11-11&checkout=2021-11-16&items_offset={}'.format(i*20)
    # print(search_url)
    _ , page_source, soup = getOnepage(bro, search_url)
    urls = get_list(soup)
    urlList.extend(urls)
bro.close()
print('the length of the url list is {}'.format(len(urlList)))

# urlList
# with open('./urlList.data', 'wb') as r:
    # store the data as binary data stream
#    pickle.dump(urlList, r)

# +
with open('./urlList.data', 'rb') as filehandle:
    urlList = pickle.load(filehandle)
print('the length of the url list is {}'.format(len(urlList)))

house_id_list = [extract_house_id(i) for i in urlList]


# +
## House Table
def extract_json_listing(listing_json):
    '''extract listing json
    '''
    listingDict = {'house_id':'null',
              'host_name':'null',
              'host_id':'null',
              'title':'null', 
              'address':'null',
              'latitude':'null', 
              'longtitude':'null', 
              'checkin_rating':'null',
              'clean_rating':'null',
              'comm_rating':'null',
              'satis_overall':'null',
              'bathroom_type':'null',
              'bed_type':'null',
              'bedroom_type':'null',
              'guest_type':'null',
    }
    
    listingDict['house_id'] = str(listing_json['pdp_listing_detail']['id'])
    listingDict['host_name'] = listing_json['pdp_listing_detail']['primary_host']['host_name']
    listingDict['host_id'] = int(listing_json['pdp_listing_detail']['primary_host']['id'])
    listingDict['title'] = listing_json['pdp_listing_detail']['name'] # title
    listingDict['address'] = listing_json['pdp_listing_detail']['p3_summary_address'] # address 
    listingDict['latitude'] = listing_json['pdp_listing_detail']['p3_event_data_logging']['listing_lat'] # lat
    listingDict['longtitude'] = listing_json['pdp_listing_detail']['p3_event_data_logging']['listing_lng'] # lng
    listingDict['checkin_rating'] = listing_json['pdp_listing_detail']['p3_event_data_logging']['checkin_rating'] 
    listingDict['clean_rating'] = listing_json['pdp_listing_detail']['p3_event_data_logging']['cleanliness_rating'] 
    listingDict['comm_rating'] = listing_json['pdp_listing_detail']['p3_event_data_logging']['communication_rating'] 
    listingDict['satis_overall'] = listing_json['pdp_listing_detail']['p3_event_data_logging']['guest_satisfaction_overall']
    # JSON.pdp_listing_detail.bathroom_label
    listingDict['bathroom_type'] = listing_json['pdp_listing_detail']['bathroom_label']
    listingDict['bed_type'] = listing_json['pdp_listing_detail']['bed_label']
    listingDict['bedroom_type'] = listing_json['pdp_listing_detail']['bedroom_label']
    listingDict['guest_type'] = listing_json['pdp_listing_detail']['guest_label']
    return listingDict

listingDt = pd.DataFrame()
error_houseling_list = []
start = time.time() 
for i in tqdm(house_id_list):
    listing_url = 'https://www.airbnb.com/api/v2/pdp_listing_details/{}?_format=for_rooms_show&adults=1&key=<KEY>&'.format(i)
    time.sleep(1)
    response = requests.get(listing_url)
    json_input = response.json()
    try:
        dt1 = extract_json_listing(json_input)
    except:
        error_houseling_list.append(i)
    listingDt = listingDt.append(dt1, ignore_index = True)
print('data has {} rows and {} columns'.format(listingDt.shape[0], listingDt.shape[1]))
print('Appending data costs %.2f seconds'%(time.time() - start))
print('{} data has errors'.format(len(error_houseling_list)))
listcol = ['house_id',
 'title', 
'address',
'latitude', 
'longtitude', 
'checkin_rating',
'clean_rating',
'comm_rating',
'satis_overall',
'bathroom_type',
'bed_type',
'bedroom_type',
'guest_type',
'host_id',
'host_name'
]
listingDt = listingDt[listcol]
listingDt.to_csv('./listingDict.csv', header=False, index = False)
listingDt
# -



# +
## Host Table
url = 'https://api.airbnb.com/v2/users/13720227?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en'
response = requests.get(url)
user_json = response.json()
user_json
# host (host_id, host_name, about, join_date, location, identity_verified, pic_url)
# reviewer (reviewer_id, reviewer_name, about, join_date, location, identity_verified, pic_url)
def extract_user(user_json, reviewer=True):
    '''extract reviewer/host and host information
    '''
    userDict = {
              'first_name':'null',
              'join_date':'null',
              'about':'null',
              'identity_verified':'null', 
              'picture_url':'null',
              'location':'null'
    }
    if reviewer:
        userDict['reviewer_id'] = user_json['user']['id']
    else:
        userDict['host_id'] = user_json['user']['id']
    userDict['first_name'] = user_json['user']['first_name']
    userDict['join_date'] = extract_date(user_json['user']['created_at'])
    userDict['about'] = user_json['user']['about']
    userDict['identity_verified'] = user_json['user']['identity_verified']
    userDict['picture_url'] = user_json['user']['picture_url']
    # userDict['created_at'] = user_json['user']['created_at']
    userDict['location'] = user_json['user']['location']
    return userDict

extract_user(user_json)

#for i in tqdm(listingDt['host_id']):
#    'https://api.airbnb.com/v2/users/13720227?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en'
hostDt = pd.DataFrame()
error_host_list = []
start = time.time() 
for i in tqdm(listingDt['host_id']):
    host_url = 'https://api.airbnb.com/v2/users/{}?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en'.format(i)
    time.sleep(1)
    response = requests.get(host_url)
    json_input = response.json()
    try:
        dt1 = extract_user(json_input, reviewer = False)
    except:
        error_host_list.append(i)
    hostDt = hostDt.append(dt1, ignore_index = True)
print('data has {} rows and {} columns'.format(hostDt.shape[0], hostDt.shape[1]))
print('Appending data costs %.2f seconds'%(time.time() - start))
print('{} data has errors'.format(len(error_host_list)))
listcol = [
    'host_id',
    'first_name',
    'location',
    'join_date',
    'about',
    'identity_verified', 
    'picture_url']
hostDt = hostDt[listcol]
hostDt.to_csv('./hostDt.csv', header=False, index = False)
hostDt


# +
## Review Table
def extract_review(json_input):
    '''iterally extract review'''
    reviewDT = pd.DataFrame()
    for rev in json_input['reviews']:
        reviewDict = {'reviewer_name':'null',
              'host_name':'null',
              'review_id':'null',
              'comments':'null', 
              'review_time':'null',
              'review_rating':'null', 
              'reviewer_id':'null'}  
        reviewDict['reviewer_name'] = rev['reviewer']['first_name'] # reviewers' first name
        reviewDict['host_name'] = rev['reviewer']['host_name'] # reviewers' first name
        reviewDict['review_id'] = str(rev['id']) # review id
        reviewDict['comments'] = rev['comments'] # comments
        reviewDict['review_time'] = extract_date(rev['created_at']) # review time
        reviewDict['review_rating'] = rev['rating'] # rating
        reviewDict['reviewer_id'] = int(rev['reviewer']['id']) # reviewer id
        # reviewDict['review_profile_path'] = rev['reviewer']['profile_path'] # profile_path
        # print(reviewDict)
        reviewDT = reviewDT.append(reviewDict, ignore_index=True)
    reviewDT['reviewer_id'] = reviewDT['reviewer_id'].astype('int64')
    return reviewDT

reviewDt = pd.DataFrame()
error_houseid_list = []
start = time.time() 
for i in tqdm(house_id_list):
    house_review_url = 'https://www.airbnb.com/api/v2/reviews?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en&listing_id={}&role=guest&_format=for_p3&_limit=100'.format(i)
    time.sleep(1)
    response = requests.get(house_review_url)
    json_input = response.json()
    try:
        dt1 = extract_review(json_input)
        dt1['house_id'] = i
    except:
        error_houseid_list.append(i)
    reviewDt = reviewDt.append(dt1, ignore_index = True)
print('data has {} rows and {} columns'.format(reviewDt.shape[0], reviewDt.shape[1]))
print('Appending data costs %.2f seconds'%(time.time() - start))
print('{} data has errors'.format(len(error_houseid_list)))
reviewCol = ['review_id','reviewer_id','house_id', 'comments', 'review_rating','host_name','review_time','reviewer_name'] 
reviewDt = reviewDt[reviewCol]
reviewDt.to_csv('./reviewDt.csv', header=False, index = False)
reviewDt
# -



# +
## Price Tables
url = 'https://www.airbnb.com/api/v2/pdp_listing_booking_details?_format=for_web_dateless&_intents=p3_book_it&_interaction_type=pageload&_p3_impression_id=p3_1552520120_con%2FMRJw4xlFD6zi&_parent_request_uuid=a23f08cc-f28a-48d8-85e9-c05735e18ff9&currency=USD&force_boost_unc_priority_message_type=&guests=1&key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en&show_smart_promotion=0&checkin=2021-11-04&checkout=2021-11-09&listing_id=42471710'
response = requests.get(url)
price_json = response.json()
price_json

def extract_price(price_json, houseId, thedate):
    userDict = {
              'houseId':'null',
              'Date':'null',
              'price':'null'
    }
    userDict['houseId'] = houseId
    userDict['Date'] = thedate
    userDict['price'] = price_json['pdp_listing_booking_details'][0]['bar_price']['display_prices'][0]['price_string'].split('$')[1]
    return userDict

priceDt = pd.DataFrame()
error_price_list = []
start = time.time() 
date_list = ['2021-11-23', '2021-11-24', '2021-11-25']
for house_id in tqdm(house_id_list):
        for dat in date_list:
            price_url = 'https://www.airbnb.com/api/v2/pdp_listing_booking_details?_format=for_web_dateless&_intents=p3_book_it&_interaction_type=pageload&_p3_impression_id=p3_1552520120_con%2FMRJw4xlFD6zi&_parent_request_uuid=a23f<PASSWORD>&currency=USD&force_boost_unc_priority_message_type=&guests=1&key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en&show_smart_promotion=0&checkin={}&checkout={}&listing_id={}'.format(dat, dat, house_id)
            time.sleep(1)
            response = requests.get(price_url)
            try:
                json_input = response.json()
                dt1 = extract_price(json_input, house_id, dat)
                priceDt = priceDt.append(dt1, ignore_index = True)
            except:
                error_price_list.append(house_id)
print('data has {} rows and {} columns'.format(priceDt.shape[0], priceDt.shape[1]))
print('Appending data costs %.2f seconds'%(time.time() - start))
print('{} data has errors'.format(len(error_price_list)))
priceDt.to_csv('./priceDt.csv', header=False, index = False)
priceDt

# +
## Reviewer Table
url = 'https://api.airbnb.com/v2/users/13720227?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en'
response = requests.get(url)
user_json = response.json()
user_json
# host (host_id, host_name, about, join_date, location, identity_verified, pic_url)
# reviewer (reviewer_id, reviewer_name, about, join_date, location, identity_verified, pic_url)
def extract_user(user_json, reviewer=True):
    '''extract reviewer/host and host information
    '''
    userDict = {
              'first_name':'null',
              'join_date':'null',
              'about':'null',
              'identity_verified':'null', 
              'picture_url':'null',
              'created_at':'null', 
              'location':'null'
    }
    if reviewer:
        userDict['reviewer_id'] = user_json['user']['id']
    else:
        userDict['host_id'] = user_json['user']['id']
    userDict['first_name'] = user_json['user']['first_name']
    userDict['join_date'] = extract_date(user_json['user']['created_at'])
    userDict['about'] = user_json['user']['about']
    userDict['identity_verified'] = user_json['user']['identity_verified']
    userDict['picture_url'] = user_json['user']['picture_url']
    # userDict['created_at'] = user_json['user']['created_at']
    userDict['location'] = user_json['user']['location']
    return userDict
#for i in tqdm(listingDt['host_id']):
#    'https://api.airbnb.com/v2/users/13720227?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en'
reviewerDt = pd.DataFrame()
error_reviewer_list = []
start = time.time() 
for i in tqdm(reviewDt['reviewer_id']):
    host_url = 'https://api.airbnb.com/v2/users/{}?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en'.format(i)
    time.sleep(0.1)
    response = requests.get(host_url)
    json_input = response.json()
    try:
        dt1 = extract_user(json_input)
    except:
        error_reviewer_list.append(i)
    reviewerDt = reviewerDt.append(dt1, ignore_index = True)
print('data has {} rows and {} columns'.format(reviewerDt.shape[0], reviewerDt.shape[1]))
print('Appending data costs %.2f seconds'%(time.time() - start))
print('{} data has errors'.format(len(error_reviewer_list)))

listcol = ['reviewer_id','first_name','location','join_date','about','identity_verified','picture_url']
reviewerDt = reviewerDt[listcol]
reviewerDt.to_csv('./reviewerDt.csv', header=False, index = False)
reviewerDt
# -

reviewerDt.to_csv('./reviewerDt.csv',index = False)

# +
# Making a get request
#house_review_json = 'https://www.airbnb.com/api/v2/reviews?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en&listing_id=42471710&role=guest&_format=for_p3&_limit=100'
#response = requests.get(house_review_json)
# print json content
#print()
#house_review_json = 'https://www.airbnb.com/api/v2/reviews?key=d306zoyjsyarp7ifhu67rjxn52tv0t20&locale=en&listing_id=16465485&role=guest&_format=for_p3&_limit=100'
#response = requests.get(house_review_json)
#response.json()

## response.json()['reviews'][3]['comments']
## listing-42471710 > div > div._ni9axhe > div > div._e296pg > div > div > div > a
## soup = BeautifulSoup(cityPagesource,'lxml', fromEncoding = 'ISO-8859-1')
## url.split('airbnb.com//rooms/')[1].split('?adults')[0] # 爬取room_id
## 
## soup.title.string.split('-')[0] # 爬取title
## soup.title.string.split('-')[1] # 爬取地点
## soup.title.string.split('-')[1].split(',')[-2].strip() # 州
## soup.title.string.split('-')[1].split(',')[-1].strip() # 国
## soup.title.string.split('-')[1].split(',')[-3].split('in')[-1].strip() # city
## 
## soup.find_all("a", {"class": "_105023be"})[0]['href'] # host id
## 
## list1 = []
## for i in soup.find_all("ol", {"class": "_194e2vt2"}):
##     list1.extend([i.string for i in i.find_all('span')])  # 
##     #url =  i.a['href'] # 强行改为英文网页
##     #print(url)
## list1
## soup.find_all("a", {"class": "_105023be"})[0]['href']
## Host information
## for i in soup.find_all("span", {"class": "_1ax9t0a"}):
##   print(i.get_text())
## soup.find_all("h1", {"class": "_14i3z6h"})[1].get_text() # host name
## soup.find_all("div", {"class": "_u5okn1"})[0].get_text() # join date
## soup.find_all("span", {"class": "_1ax9t0a"})[0].get_text() # reviews number
## soup.find_all("span", {"class": "_1ax9t0a"})[1].get_text() # Identity Verification
## soup.find_all("span", {"class": "_1ax9t0a"})[6].get_text() # address 
## soup.find_all("span", {"class": "_1ax9t0a"})[4].get_text()
## soup.find_all("div", {"class": "_u5okn1"})[0].get_text()
## soup.find_all("div", {"class": "_u5okn1"})[0].get_text()
## response.json()['reviews'][4]['comments']
# -



StarcoderdataJupyterScriptsDedupFiltered