In [11]:
import sys
reload(sys)
sys.setdefaultencoding('utf8')
In [67]:
import numpy as np
In [1]:
import pandas as pd
import sqlalchemy
import connect_aws_db as cadb
In [2]:
engine = cadb.connect_aws_db(write_unicode=True)
In [3]:
conn = engine.connect()
In [4]:
cmd = 'SELECT * FROM yelp_reviews'
In [13]:
yelp_reviews = pd.read_sql(cmd, engine)
In [15]:
yelp_reviews.head(3)
Out[15]:
rev_id
business_id
yelp_review_date
yelp_review_id
review_rating
review_text
user_id
0
1
bWWrrsPWuoHuAGUCgH8Tyg
2007-10-28
TMiAAS5RB-P5EwXs7B9aBw
4
I stayed at the hotel for several months durin...
ZqoMKTrJOtyYZOcvP8rTUA
1
2
bWWrrsPWuoHuAGUCgH8Tyg
2008-01-05
F78V6FFuUr9pawcok4YRyg
5
I have been coming to Pittsburgh for quite a w...
uiMTavYKLw9hxskV4xB2tg
2
3
bWWrrsPWuoHuAGUCgH8Tyg
2008-03-08
g6vMKao00XSECOd6JtKPEA
4
This is a very good hotel and with a corporate...
TckShYnQa0eD3WQxdoE3GQ
In [16]:
cmd = 'SELECT * FROM bf_reviews'
In [17]:
bf_reviews = pd.read_sql(cmd, engine)
In [35]:
bf_reviews
Out[35]:
review_id
hotel_id
business_id
user_id
username
review_title
review_rating
review_text
0
1
0
None
0
\nMarti Ackermann\nin Tucson, AZ\n
Pointe Hilton Squaw Peak
5
Really nice property. Great walking areas for ...
1
2
1
None
0
\nJen\nin Vancouver\n
Great place to stay!
5
We've stayed at this La Quinta several times w...
2
3
1
None
0
\nJody\nin Sierra Vista, AZ\n
Awesome Staff!!
5
This place was awesome!! The entire staff was ...
3
4
1
None
0
\nKristi\nin Henderson NV\n
Very dog friendly
5
We've stayed at this hotel two times with our ...
4
5
1
None
0
\nChristopher\nin Los Angeles\n
All around great place to stay.
4
My room while a bit small was very clean and t...
5
6
1
None
0
\nDave\nin Sedona\n
Management and customer service
3
The big gentleman who manages the front desk i...
6
7
1
None
0
\nTeri\nin Southern California\n
Bad Experience.
1
I was driving from So Cal to Houston, TX for t...
7
8
2
None
0
\nemma\nin park city\n
no dog area
2
the hotel was fine but it is all hard scape ar...
In [19]:
cmd = 'SELECT * FROM bf_hotels'
In [20]:
bf_hotels = pd.read_sql(cmd, engine)
In [36]:
bf_hotels
Out[36]:
hotel_id
hotel_url
hotel_name
hotel_address
hotel_city
hotel_state
hotel_rating
hotel_latitude
hotel_longitude
business_id
review_count
0
1
http://www.bringfido.com/lodging/city/phoenix_...
Pointe Hilton Squaw Peak Resort
\n7677 North 16th Street
Phoenix
AZ
5
0
0
None
1
1
2
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
2
3
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
3
4
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
4
5
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
5
6
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
6
7
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
7
8
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix North
\n2510 WGreenway Rd
Phoenix
AZ
2
0
0
None
1
In [22]:
cmd = 'SELECT * FROM yelp_hotels'
In [23]:
yelp_hotels = pd.read_sql(cmd, engine)
In [29]:
yelp_hotels[yelp_hotels['hotel_name'].str.contains('Squaw')]
Out[29]:
hotel_id
hotel_name
hotel_rating
hotel_city
hotel_state
hotel_latitude
hotel_longitude
review_count
hotel_address
business_id
dogs_allowed
61
62
Pointe Hilton Squaw Peak Resort
3.5
Phoenix
AZ
33.5478
-112.047
160
7677 N 16th St\nPhoenix, AZ 85020
N1xMSUfv1GmfHGa9a3VuJw
1
In [42]:
bf_hotels['business_id'][0] = 'N1xMSUfv1GmfHGa9a3VuJw'
In [43]:
bf_hotels
Out[43]:
hotel_id
hotel_url
hotel_name
hotel_address
hotel_city
hotel_state
hotel_rating
hotel_latitude
hotel_longitude
business_id
review_count
0
1
http://www.bringfido.com/lodging/city/phoenix_...
Pointe Hilton Squaw Peak Resort
\n7677 North 16th Street
Phoenix
AZ
5
0
0
N1xMSUfv1GmfHGa9a3VuJw
1
1
2
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
2
3
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
3
4
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
4
5
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
5
6
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
6
7
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix Arcadia
\n4727 E Thomas Rd
Phoenix
AZ
4
0
0
None
6
7
8
http://www.bringfido.com/lodging/city/phoenix_...
La Quinta Inn Phoenix North
\n2510 WGreenway Rd
Phoenix
AZ
2
0
0
None
1
In [44]:
cmd = "DROP TABLE bf_hotels"
result = conn.execute(cmd)
In [45]:
cmd = """
CREATE TABLE bf_hotels
(
hotel_id MEDIUMINT AUTO_INCREMENT,
hotel_url VARCHAR(512),
hotel_name VARCHAR(512),
hotel_address VARCHAR(1024),
hotel_city VARCHAR(512),
hotel_state VARCHAR(32),
hotel_rating INT,
hotel_latitude FLOAT,
hotel_longitude FLOAT,
business_id VARCHAR(256),
review_count MEDIUMINT,
PRIMARY KEY (hotel_id)
)
"""
In [46]:
result = conn.execute(cmd)
In [47]:
bf_hotels.to_sql('bf_hotels', engine, if_exists='append', index=False)
In [51]:
yelp = pd.merge(yelp_hotels, yelp_reviews, on='business_id', how='inner')
In [52]:
yelp
Out[52]:
hotel_id
hotel_name
hotel_rating
hotel_city
hotel_state
hotel_latitude
hotel_longitude
review_count
hotel_address
business_id
dogs_allowed
rev_id
yelp_review_date
yelp_review_id
review_rating
review_text
user_id
0
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
1
2007-10-28
TMiAAS5RB-P5EwXs7B9aBw
4
I stayed at the hotel for several months durin...
ZqoMKTrJOtyYZOcvP8rTUA
1
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
2
2008-01-05
F78V6FFuUr9pawcok4YRyg
5
I have been coming to Pittsburgh for quite a w...
uiMTavYKLw9hxskV4xB2tg
2
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
3
2008-03-08
g6vMKao00XSECOd6JtKPEA
4
This is a very good hotel and with a corporate...
TckShYnQa0eD3WQxdoE3GQ
3
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
4
2008-03-29
PNS9z4aFDbfhpIsHCfNvNg
4
i got upgraded to a junior suite, and then aga...
MquuHY8ar78FUjkbcmVyPw
4
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
5
2008-07-07
gyYXmBwP3jSinLuXtuGRuQ
5
This is a fantastic hotel. I went to a conven...
pzhiDEp8EFltFmdldP9Oow
5
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
6
2008-11-22
fmL0UvXQgPwN6mO3HEs1jA
3
Being a Southern California boy, I was not par...
EV4u-Ct9a0Yl7ecwAffccw
6
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
7
2009-04-06
UcdYzG9E_xDqgIWhZEjz5Q
4
I was in town for an interview and didn't pay ...
5SLDX3bXcRP52QlStIcYLg
7
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
8
2009-07-20
KE9THkNNQ0p9K5KGLSc_9Q
4
The Westin is a great place to stay. The beds...
sOnj2ToEH_q-pajhmHsI4w
8
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
9
2009-11-27
aha23u_Yxh1d8C8ZHfDqTA
4
I really enjoyed my stay at the Westin. Durin...
YqhqFM2Q400HXFy0L6QL2g
9
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
10
2010-02-05
qZZRuL_W7M65BWchFXED7Q
2
When I think of Westins, I think of business-c...
q_jNEFRQfYunkJlqEITSHw
10
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
11
2010-02-17
azlZcBwgiMp2Y8qKgzE5Cg
2
Location is ok for the downtown area. Not too ...
Jhq8AsZccqAWvfw6Dq2fWA
11
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
12
2010-04-14
J2UwiemTJAfkLrgWceUqag
4
Great location, close to the convention center...
EKOf7h3O-5KyTtiC1xi13w
12
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
13
2010-04-21
kfChs8_dyEqq475hsDKPQA
3
I was expecting a high quality hotel from the ...
jRPv3y6eejhaWicnkP_kJQ
13
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
14
2010-05-30
61bFTdo6emrWgppN-Sj5-g
3
I do not understand the new obsession with hot...
J-52g3nkjcOV4wq5oTRXUA
14
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
15
2010-06-30
dZGoTXN8e1F7aTzAnVzKWQ
4
I stayed at this hotel while attending a conve...
yCS_hr75GmRXCFgadFHtuA
15
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
16
2010-07-01
vI2DZpdYO8oVS_eauZKV0g
4
Overall a solid hotel, I've stayed here 4 of t...
FiVUmdW-pk6dyQeD-eqarA
16
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
17
2010-07-14
xIHlfW7zCLFrg8wVSd4SgA
4
Although I was only there one night I was pret...
aZljc_FO-XUnA9VaFlXzEQ
17
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
18
2010-07-15
0clkq-V3Iyul-9s15ZvbrQ
4
The hotel is kept clean and the room rates wer...
DfewvXBVh68PdGWs4cT5YQ
18
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
19
2010-08-25
CBf72qy6We-q-uGtx7tdIw
4
The Westin convention Center is located downt...
qmUczLelWz2fZIp9_1ehJQ
19
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
20
2010-11-25
_CabZZXadsNZOhnZHX6Zmw
3
I stayed here for a 2 day conference and was n...
ncITKl6ATrc5JgXUni97RA
20
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
21
2010-11-26
5lr09o6VQExE75vj0abUlQ
3
The service was good. Tony the shuttle driver ...
xZgzmgY82OEAKnwyVp8WJQ
21
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
22
2011-09-01
wtxvKEI5tSE696HMj38SUw
3
I usually look for the westin whenever im trav...
SchRz_Vt0_xbp2FUNPz5CQ
22
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
23
2011-10-18
PyzIu4YSLHAGJFKXjv1CmQ
4
I've stayed at this Westin a few days a week f...
vMcID1kC8I4OAL6HA3IP1Q
23
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
24
2011-11-04
SaEKmSHe8ngDSpxAkVBuYg
4
Stayed here for a convention next door, mostly...
s-hjAU_t1mKY9YMtV-YX2A
24
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
25
2011-11-09
zyRWBncq180ghTxss4cUlA
4
I've been here for several events over the pas...
D1G8W0SgCbsKTv7cAqKcNg
25
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
26
2011-11-15
EAxJRvftO9BZ4-4EnGB-YA
2
We got this hotel room via Hotwire at a great ...
zEdyUGMP1J2BdYQo0B-CMw
26
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
27
2011-11-20
jQUff39wWMo-gnVW_36eog
4
I stayed here this past weekend for a conferen...
pk9DbYTH3mrDRmmjvMq72w
27
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
28
2012-01-21
iz-xyIwJma7EQUafboyvWQ
4
I travel every week for work and was always a ...
1Kup1mjV8uox4wWeIDEjQQ
28
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
29
2012-01-23
0D_5c5OflekPjtz5Hnqu4w
2
My boyfriend and I stayed here for a long week...
juzL-AXKUceVFi2MXIPZ5Q
29
1
The Westin Convention Center, Pittsburgh
3.5
Pittsburgh
PA
40.4442
-79.9949
70
1000 Penn Ave\nDowntown\nPittsburgh, PA 15222
bWWrrsPWuoHuAGUCgH8Tyg
1
30
2012-01-26
d6dPiKeNlGdWiSjkcxjqlg
3
I stayed here a week ago and was not impressed...
a0UPEnAIdT14_34nMU8B9A
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
6233
126
Basislager
4.5
Karlsruhe
BW
49.0101
8.3915
14
Kaiserstraße 231\n76133 Karlsruhe
O_8KPbCJpKSuFeqgmY0rHQ
1
6234
2007-07-10
qwEXA7sqKIOSP63Mt0cO7Q
4
Ups, ich hatte noch keinen Beitrag zum Basisla...
2tQfo3VoswnM-jR621X9oA
6234
126
Basislager
4.5
Karlsruhe
BW
49.0101
8.3915
14
Kaiserstraße 231\n76133 Karlsruhe
O_8KPbCJpKSuFeqgmY0rHQ
1
6235
2010-01-10
igNAaV_4Bio9zzidDs3o0A
5
Klasse Laden mit viel Auswahl und tollem Servi...
c5baNLWzuQG1rm1wigyFuQ
6235
126
Basislager
4.5
Karlsruhe
BW
49.0101
8.3915
14
Kaiserstraße 231\n76133 Karlsruhe
O_8KPbCJpKSuFeqgmY0rHQ
1
6236
2010-07-23
--b9S77-6ODn_OgLIOvoGw
5
Klasse Laden mit viel Auswahl und tollem Servi...
c5baNLWzuQG1rm1wigyFuQ
6236
126
Basislager
4.5
Karlsruhe
BW
49.0101
8.3915
14
Kaiserstraße 231\n76133 Karlsruhe
O_8KPbCJpKSuFeqgmY0rHQ
1
6237
2012-08-21
M27GP0zBXBF27SONegmRkw
4
Toller Laden mit professioneller und guter Ber...
yu8-rz4bMadUs6c0ajwq6w
6237
126
Basislager
4.5
Karlsruhe
BW
49.0101
8.3915
14
Kaiserstraße 231\n76133 Karlsruhe
O_8KPbCJpKSuFeqgmY0rHQ
1
6238
2012-09-07
kkhOlAV-_gjcmXlRVrblFQ
4
Früher in der Waldstraße, jetzt in der Kaisers...
gjlVdzfaVpMdr_z4RlxX3w
6238
126
Basislager
4.5
Karlsruhe
BW
49.0101
8.3915
14
Kaiserstraße 231\n76133 Karlsruhe
O_8KPbCJpKSuFeqgmY0rHQ
1
6239
2012-12-31
FbTDwSjAhwthodIBqIb2hQ
5
Das Basislager ist für Outdoor-,Trekking- und ...
TRa_00SLt8yVbd1T3nLZ7g
6239
126
Basislager
4.5
Karlsruhe
BW
49.0101
8.3915
14
Kaiserstraße 231\n76133 Karlsruhe
O_8KPbCJpKSuFeqgmY0rHQ
1
6240
2013-06-12
KtnUIz0TO2qDIzY69GLSLw
4
Große Auswahl, ganz klar dem Outdoor-Freund ge...
ya-3mYHRqnMU6IgsUMyOCg
6240
126
Basislager
4.5
Karlsruhe
BW
49.0101
8.3915
14
Kaiserstraße 231\n76133 Karlsruhe
O_8KPbCJpKSuFeqgmY0rHQ
1
6241
2013-08-07
G7XtVeQckp6U1bfwErRcVw
4
Große Auswahl, ganz klar... dem Outdoor-Freund...
ya-3mYHRqnMU6IgsUMyOCg
6241
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6242
2014-05-15
lBnmMdIMp_LXDadOWOcdcA
4
1. No Acknowledgement at the front desk, nice ...
aHYQrxjDmXiTBP4WLP4sIA
6242
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6243
2014-06-11
IICFqs51iZbzQBEUkVQ8cQ
1
I came here last weekend for Power 98.3's Revi...
wRsrr3_qbZIj_-zbKspfpw
6243
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6244
2014-06-13
C3PoIirFBuCc4bS4CX3XEg
5
Where to begin. \n1. Excellent customer servic...
v6TyhMR82EUchb8Ye7co4A
6244
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6245
2014-06-29
en3ujvfysz0G9YaHa-9xGA
5
Great room! Friendly check in! Got a great rat...
_0dxI24jNnYvg7lUBNcFKg
6245
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6246
2014-08-01
IaevvwSL27ffmEkz3mwILQ
4
My experience with this hotel was very differe...
EBrJbXjKMTFGeyEb9YPyrA
6246
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6247
2014-08-07
3k4IB_u0lgfVB7wGa1IAig
4
My room was beautiful ~ spacious, comfortable,...
VKpjvz68qBWdUu08F62VPA
6247
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6248
2014-08-25
VsletHMPCX9kEs9-aC1VFg
5
My recent stay at South Mountain was Fantastic...
aHYQrxjDmXiTBP4WLP4sIA
6248
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6249
2014-10-10
YZJogahne3uhmWemCtqx1Q
1
I don't usually write reviews, but my experien...
aRppvZNrmBPoESnaXAkDuw
6249
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6250
2014-10-27
zQoxGfzangiyK6LJOoqHLg
2
Not the greatest hotel experience I've had. I ...
9iyat9IlO_EnWU97EujWgQ
6250
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6251
2014-11-04
9Vl3agFSMRYVGD6gdMICyg
5
I can't express it any other way but WOW I LOV...
Zg2pzfamQGsKYm-ja2q81g
6251
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6252
2014-11-04
p4EyppNQ-5TaAPtKLD_6Rg
1
I can't believe my experience here..\n\nTo kee...
WJEmeL_3vHIOLepEwd2lrQ
6252
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6253
2014-11-10
ikQV77Hijc6kW7bCrEkP3Q
4
Nice hotel in a central locale near the 10. St...
pfVTG-fFaQ1wuLqNuC8wvQ
6253
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6254
2014-11-16
zz9mEgAmakO43soXtfhCoA
1
This place is completely useless, the staff is...
DrO4IJz1jUFVizPatBEcxw
6254
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6255
2015-01-02
c6zqQM_0O7cTmtj7TEx2FA
1
This hotel was a 3.5 star on Priceline but I'm...
jIpvyIS3s31MbthRGkbNyQ
6255
127
Four Points by Sheraton Phoenix South Mountain
3.5
Phoenix
AZ
33.3477
-111.9730
16
10831 South 51st St\nPhoenix, AZ 85044
q54q6eLD9qsoKQFMu4IM_Q
1
6256
2015-01-07
Dt6FjzbCesA0Y9DcEH0JFQ
4
This is an affordable, clean updated hotel loc...
zGw7Br-XlmrfeCZx6IjFQw
6256
128
Graduate Tempe
4.0
Tempe
AZ
33.4144
-111.9360
9
225 E Apache Blvd\nTempe, AZ 85281
LcqzL_4UKl0ETqRUJKA_7Q
1
6257
2014-11-03
IYvg2HqZLWw3jUwjBzAIbg
5
Listen, I party. A lot. And I don't drink and...
T6eB-L_sgxpRkS739xQQbQ
6257
128
Graduate Tempe
4.0
Tempe
AZ
33.4144
-111.9360
9
225 E Apache Blvd\nTempe, AZ 85281
LcqzL_4UKl0ETqRUJKA_7Q
1
6258
2014-11-09
OiLIvdRij6CGlCrrkqdpnQ
5
Amazing Staff. I was an ASU grad. Had to sta...
_pp_l1ZYznN2OEt7yfCTOg
6258
128
Graduate Tempe
4.0
Tempe
AZ
33.4144
-111.9360
9
225 E Apache Blvd\nTempe, AZ 85281
LcqzL_4UKl0ETqRUJKA_7Q
1
6259
2014-11-11
qhYNKyTwzarNj7vEynYg1g
5
Only one word comes to mind when I think about...
doWD86Iw7B8BVOaqtAC4tA
6259
128
Graduate Tempe
4.0
Tempe
AZ
33.4144
-111.9360
9
225 E Apache Blvd\nTempe, AZ 85281
LcqzL_4UKl0ETqRUJKA_7Q
1
6260
2014-11-16
hg4OivGTzGG468u202HmeA
5
I have 2 words for you, Freaking Fantastic!!!\...
UQlBrZ6ho91lynwdDu8WVQ
6260
128
Graduate Tempe
4.0
Tempe
AZ
33.4144
-111.9360
9
225 E Apache Blvd\nTempe, AZ 85281
LcqzL_4UKl0ETqRUJKA_7Q
1
6261
2014-12-16
DvpcMyWitj-d3u3QSca6lw
5
Large, clean rooms. Very comfortable beds. F...
TNFm9ll5sZwa3SGbIqWKIw
6261
128
Graduate Tempe
4.0
Tempe
AZ
33.4144
-111.9360
9
225 E Apache Blvd\nTempe, AZ 85281
LcqzL_4UKl0ETqRUJKA_7Q
1
6262
2014-12-19
kcy6XndboL99H73SkDjOzw
4
Awesome place to stay when near ASU. Friendly ...
BMcjqDKeU0R9ut8OzvumEA
6262
128
Graduate Tempe
4.0
Tempe
AZ
33.4144
-111.9360
9
225 E Apache Blvd\nTempe, AZ 85281
LcqzL_4UKl0ETqRUJKA_7Q
1
6263
2014-12-27
STzppCFcPUJm4mepGWVdaA
1
I only left one star, because there wasn't an ...
9wDMNsoVkMeZTNdLmRNIFQ
6263 rows × 17 columns
In [53]:
len(yelp)
Out[53]:
6263
In [54]:
yelp_phoenix = yelp[yelp['hotel_city'] == 'Phoenix']
In [76]:
len(yelp_phoenix)
Out[76]:
1120
In [63]:
yelp_dog_review = yelp_phoenix[yelp_phoenix['review_text'].str.contains('dog')].copy()
In [77]:
len(yelp_dog_review)
Out[77]:
36
In [68]:
np.unique(yelp_dog_review['hotel_id'])
Out[68]:
array([ 8, 10, 11, 35, 36, 55, 61, 62, 79, 81, 89, 106, 117, 118])
In [ ]:
In [82]:
average_ratings = [np.mean(yelp_phoenix[yelp_phoenix['hotel_id'] == hotel_id]['hotel_rating'].values) for hotel_id in np.unique(yelp_dog_review['hotel_id'])]
In [80]:
average_dog_ratings = [np.mean(yelp_dog_review[yelp_dog_review['hotel_id'] == hotel_id]['hotel_rating'].values) for hotel_id in np.unique(yelp_dog_review['hotel_id'])]
In [87]:
average_dog_ratings
Out[87]:
[3.0, 3.5, 3.0, 2.5, 3.5, 3.0, 3.5, 3.5, 3.0, 4.0, 3.5, 4.0, 4.5, 1.5]
In [88]:
from matplotlib import pyplot as plt
In [90]:
%matplotlib inline
In [98]:
plt.rcParams['figure.figsize'] = 12.94, 8
fig, ax = plt.subplots(1, 1)
ax.plot(average_ratings, average_dog_ratings, 'o', color='#B2CFEC', ms=10)
ax.set_xlabel('All Ratings')
ax.set_ylabel('Dog Ratings')
ax.set_xlim([0, 5.5])
ax.set_ylim([0, 5.5])
plt.savefig('/Users/matt/Desktop/rating_comparison.png', dpi=144)
In [104]:
print(len(bf_reviews))
print(len(yelp_phoenix))
print(len(yelp_dog_review))
In [101]:
fig = plt.figure()
ax1 = fig.add_subplot(111)
bars = ax1.bar(range(1,4), range(1,4), color='blue', edgecolor='black')
In [ ]:
Content source: mattgiguere/doglodge
Similar notebooks: