Message Boards

GROUPS:

Anton Antonov

[Notebook] Ingest Airbnb reviews data

Anton Antonov, Accendo Data LLC

Posted 1 year ago

2583 Views

1 Reply

4 Total Likes

Follow this post

Introduction

In this notebook we show how to ingest Airbnb reviews data.

(The data can be used for exemplifying relevant Machine Learning algorithms.)

Sources:

MathematicaForPrediction at GitHub
MathematicaForPrediction at WordPress

Downloading the data

The data was downloaded from this site : http://insideairbnb.com/get-the-data.html .

WebImage["http://insideairbnb.com/get-the-data.html"]

In[]:=

Out[]=

Load data

In this section the data is imported. (You have to download the data first, and replace the corresponding names.)

lsFileNames=FileNames["~/Datasets/AirBnB/*reviews.csv"]

In[]:=

{~/Datasets/AirBnB/AirBnB-Austin-Texas-United-States-reviews.csv,~/Datasets/AirBnB/AirBnB-Broward-County-Florida-United-States-reviews.csv}

Out[]=

AbsoluteTimingdsReviews=Join@@MapResourceFunction

ResourceObject[

]

,lsFileNames;

{18.0559,Null}

Out[]=

Dimensions[dsReviews]

In[]:=

{524759,6}

Out[]=

SeedRandom[342];RandomSample[dsReviews,12]

In[]:=

listing_id	id	date	reviewer_id	reviewer_name	comments
32761299	459784643	2019-05-27	260673176	Rachel	This place and location was perfect! We were able to have walking distance to th ⋱
5477413	82235987	2016-06-26	31965194	Melody	Large, lovely home with great 10-min walking access to the beach and broadwalk, ⋱
19721357	515312924	2019-08-22	758794	Alex	Absolutely wonderful experience--beautiful, clean place to stay in a great locat ⋱
17123406	351968299	2018-11-24	182520327	Clint	The place is clean,spacious and located close to restaurants and shopping. Gabri ⋱
2396602	308041001	2018-08-15	56276177	Robbie	This home was the perfect accommodations for us! Everything was clean, spacious, ⋱
69810	202962752	2017-10-13	28368446	Alexandria	We were thrilled with the birds nest, the response, and the thoughtful touch. Th ⋱
32743526	425639227	2019-03-18	1417982	Robert	The place was pristine and very well appointed. Everything seemed new. The self ⋱
17109314	135012128	2017-03-02	72579420	Abraham	Lovely place great location nice hosts highly recommended,
22266927	318868306	2018-09-04	113565142	Tawna	Perfect place for our first trip to Austin! Easy to find, clean, really cute dec ⋱
17109314	226398361	2018-01-10	110497390	Jess	Honestly we thought the place was great and the location was great, but the fact ⋱
646392	14607779	2014-06-23	9920923	Joel	My wife and I spent 5 nights at Kathleen's BNB. The room was very clean when we ⋱
32920994	455451884	2019-05-19	36158427	Bre	This was my second time staying with James in one of his campers. This stay was ⋱

Out[]=

Summaries

Summary of all records:

ResourceFunction["RecordsSummary"][dsReviews]

In[]:=



1 listing_id

Min	2265
1st Qu	5558324
Mean	1.52976× 7 10
Median	15944361
3rd Qu	2.28841× 7 10
Max	38727516

2 id

Min	865
1st Qu	1.59769× 8 10
Mean	2.88211× 8 10
Median	291297296
3rd Qu	4.26657× 8 10
Max	533532956

3 date

2019-05-27	1928
2019-07-07	1841
2019-03-31	1800
2019-03-17	1769
2019-04-14	1690
2019-04-28	1671
(Other)	514060

4 reviewer_id

Min	3
1st Qu	2.467× 7 10
Median	67455091
Mean	9.02347× 7 10
3rd Qu	1.45028× 8 10
Max	296165050

5 reviewer_name

Michael	5133
David	4405
John	3836
Sarah	3535
Chris	3199
Jessica	3050
(Other)	501601

6 comments

Great place!	729
Great place	638
Great place to stay!	407
.	406
Great location!	384
	312
(Other)	521883



Out[]=

Summary of counts per unique “listing_id” value:

ResourceFunction["RecordsSummary"][Query[GroupBy[#["listing_id"]&],Length]@dsReviews]

In[]:=



1 column 1

Min	1
1st Qu	3
Median	12
Mean	32.0385
3rd Qu	37
Max	856



Out[]=

Summary of counts per unique “reviewer_id” value:

ResourceFunction["RecordsSummary"][Query[GroupBy[#["reviewer_id"]&],Length]@dsReviews]

In[]:=



1 column 1

1st Qu	1
3rd Qu	1
Median	1
Min	1
Mean	1.15054
Max	80



Out[]=

Summary of counts per unique “id” value:

ResourceFunction["RecordsSummary"][Query[GroupBy[#["id"]&],Length]@dsReviews]

In[]:=



1 column 1

1st Qu	1
3rd Qu	1
Max	1
Mean	1
Median	1
Min	1



Out[]=

Pareto Principle adherence

Here we see a typical Pareto principle manifestation for the number of reviews distribution per listing ID:

ResourceFunction

ResourceObject[

]

[Values[Normal[Query[GroupBy[#["listing_id"]&],Length]@dsReviews]]]

Out[]=

Here we see that many reviewers do one review only (within that dataset):

ResourceFunction

ResourceObject[

]

[Values[Normal[Query[GroupBy[#["reviewer_id"]&],Length]@dsReviews]]]

Out[]=

Count[Query[GroupBy[#["reviewer_id"]&],Length]@dsReviews,1]%/Dimensions[dsReviews]〚2〛//N

In[]:=

411153

Out[]=

68525.5

Out[]=

Contingency matrix

Here we compute the contingency matrix for listings vs reviewers:

AbsoluteTiming[matNReviews=ResourceFunction["CrossTabulate"][dsReviews[All,{"listing_id","reviewer_id"}],"Sparse"True];]

In[]:=

{7.10633,Null}

Out[]=

MatrixPlot[matNReviews["SparseMatrix"],MaxPlotPoints500]

In[]:=

Out[]=

Latent Semantic Analysis

The Latent Semantic Analysis (LSA) computations are mostly done in order to compute different text derived statistics. (See [AA1, AA2].)

Text data

In this sub-section we extract the text data from the dataset into an association with text values and keys that correspond to the review ID’s.

stopWords=Complement[DictionaryLookup["*"],DeleteStopwords[DictionaryLookup["*"]]];

In[]:=

aComments=Select[AssociationThread[Normal[dsReviews[All,"id"]],Normal[dsReviews[All,"comments"]]],StringQ];Length[aComments]

In[]:=

524743

Out[]=

ResourceFunction["RecordsSummary"][StringLength/@Values[aComments]]

In[]:=



1 column 1

Min	0
1st Qu	87
Median	176
Mean	240.977
3rd Qu	315
Max	5779



Out[]=

ResourceFunction["RecordsSummary"][Map[Length[Select[#,StringLength[#]>0&]]&,StringSplit[Values@aComments,PunctuationCharacter|WhitespaceCharacter]]]

In[]:=



1 column 1

Min	0
1st Qu	15
Median	31
Mean	43.4932
3rd Qu	57
Max	1054



Out[]=

Monad objects

Here we import the package [AA2]:

Import["https://raw.githubusercontent.com/antononcube/MathematicaForPrediction/master/MonadicProgramming/MonadicLatentSemanticAnalysis.m"]

In[]:=

Importing from GitHub:StateMonadCodeGenerator.m

Importing from GitHub:SSparseMatrix.m

Importing from GitHub:DocumentTermMatrixConstruction.m

Importing from GitHub:NonNegativeMatrixFactorization.m

Importing from GitHub:IndependentComponentAnalysis.m

Importing from GitHub:CrossTabulate.m

Importing from GitHub:OutlierIdentifiers.m

Importing from GitHub:MathematicaForPredictionUtilities.m

Importing from GitHub:MosaicPlot.m

All reviews

AbsoluteTiming[lsaComments=LSAMonUnit[aComments]⟹LSAMonMakeDocumentTermMatrix[{},stopWords]⟹LSAMonApplyTermWeightFunctions["IDF","None","Cosine"];]

In[]:=

{104.503,Null}

Out[]=

Tighter reviews

For faster LSA computations -- and better results -- we want to use a tighter set of reviews.

Here we select review with string length between 80 and 400 symbols:

AbsoluteTiming[lsaTightComments=LSAMonUnit[Select[aComments,80<=StringLength[#]≤400&]]⟹LSAMonMakeDocumentTermMatrix[{},stopWords]⟹LSAMonApplyTermWeightFunctions["IDF","None","Cosine"];]

In[]:=

{54.3238,Null}

Out[]=

Document × term statistics -- all reviews

lsaComments⟹LSAMonEchoDocumentTermMatrixStatistics⟹LSAMonEchoDocumentTermMatrixStatistics["LogBase"10];

In[]:=

Context value "documentTermMatrix":

Dimensions:{524743,111897}

Density:0.000170987

Number ofdocuments per termsummary



1 # documents

1st Qu	1
Median	1
Min	1
3rd Qu	5
Mean	89.7244
Max	248701



Context value "documentTermMatrix":

Dimensions:{524743,111897}

Density:0.000170987

Log 10 number ofdocuments per termsummary



1 # documents

1st Qu	0.
Median	0.
Min	0.
Mean	0.444996
3rd Qu	0.69897
Max	5.39568



Document × term statistics -- tight reviews

lsaTightComments⟹LSAMonEchoDocumentTermMatrixStatistics⟹LSAMonEchoDocumentTermMatrixStatistics["LogBase"10];

In[]:=

Context value "documentTermMatrix":

Dimensions:{321579,68352}

Density:0.000244679

Number ofdocuments per termsummary



1 # documents

1st Qu	1
Median	1
Min	1
3rd Qu	5
Mean	78.6836
Max	152351



Context value "documentTermMatrix":

Dimensions:{321579,68352}

Density:0.000244679

Log 10 number ofdocuments per termsummary



1 # documents

1st Qu	0.
Median	0.
Min	0.
Mean	0.464513
3rd Qu	0.69897
Max	5.18285



Topics extraction (over tighter reviews)

AbsoluteTiming[lsaTightComments=lsaTightComments⟹LSAMonExtractTopics["NumberOfTopics"40,"MinNumberOfDocumentsPerTerm"100,Method"SVD",MaxSteps12];]

In[]:=

{180.153,Null}

Out[]=

lsaTightComments⟹LSAMonEchoTopicsTable["NumberOfTableColumns"8];

In[]:=

topics table:

1.000	great
0.929	stay
0.904	place
0.842	location
0.815	clean
0.731	definitely
0.728	austin
0.693	recommend
0.685	house
0.683	nice
0.652	host
0.643	comfortable

1.000	home
-0.758	recommend
-0.719	highly
0.687	easy
0.662	check
-0.373	nice
-0.345	beach
-0.330	place
0.284	like
0.276	felt
0.263	away
-0.263	definitely

1.000	really
-0.845	space
0.760	enjoyed
-0.711	comfortable
0.544	check
-0.536	bed
-0.512	room
0.511	easy
0.477	close
0.412	staying
-0.378	super
-0.349	location

1.000	close
0.962	space
-0.894	nice
0.777	really
-0.631	perfect
0.617	enjoyed
-0.584	just
0.573	downtown
-0.572	distance
0.570	beautiful
-0.562	walking
-0.561	needed

1.000	amazing
-0.764	nice
0.667	quiet
0.535	neighborhood
0.428	really
-0.399	definitely
-0.370	apartment
0.368	enjoyed
0.351	needed
-0.346	austin
-0.334	time
-0.318	area

1.000	awesome
0.536	beautiful
0.529	room
-0.514	nice
-0.449	time
-0.448	wonderful
0.428	needed
0.397	hosts
-0.377	super
0.353	close
-0.329	excellent
0.327	apartment

1.000	needed
0.936	wonderful
-0.807	excellent
0.594	beautiful
0.582	good
0.526