程序代写代做 Excel go flex algorithm data science C AI graph database 



Please enter your full name here:

This exam will test your knowledge in Data Science.
We will test the following:
• Basic knowledge in Data Science
• How to manipulate data
• How to clean data
• Further data analysis

1. Basic Knowledge Test

A) Create a dictionary and a list.
In [31]:
#YONGZHI WU, DATA SCIENCE PYTHON
#JUST FINISHED STUDY TOUR IN LA ON 1ST FEB AND CAME BACK TO BARCELONA YESTERDAY. SORRY FOR LATE SUBMISSION. BECAUSE I SPENT MOST TIME TRAVELLING DURING LAST TWO DAYS and jet lag.
#dict={key:value}
#list=[1,2,3]

#dictionary:
{“city”:[“beijing”,”shanghai”],”rank”:[1,2]}

#list
[[1,2,3],[“a”,”b”,”c”]]
Out[31]:

city
rank
0
beijing
1
1
shanghai
2

B) Create your own array containing multiple lists (at least 5 rows and 4 columns).
In [155]:
import numpy as np
np_1=np.array([1,2,3,4],
[1,2,3,4],
[1,2,3,4],
[1,2,3,4],
[1,2,3,4])

—————————————————————————
ValueError Traceback (most recent call last)
in
4 [1,2,3,4],
5 [1,2,3,4],
—-> 6 [1,2,3,4])

ValueError: only 2 non-keyword arguments accepted

C) Convert your previous array into a pandas dataframe.
In [33]:
df=pd.Dataframe(np_1)

—————————————————————————
NameError Traceback (most recent call last)
in
—-> 1 df=pd.DataFrame(np_1)

NameError: name ‘np_1’ is not defined

D) Please write a function that will start with the number 2 and will add 3 as long as the number is under 25.
In [23]:
def magic(x):
if x<25: x=x+3 magic(x) else: print(x) magic(2) 26 E) Briefly explain what a lambda function is and its benefits over using regular functions. In [ ]: Lambda functions can only have one expression in their body while regular functions can have multiple expressions and statements in their body. F) What is the difference between relational and non-relational databases? In [ ]: Relational databases are structured while non-relational databases are document-oriented.non-relational databases have more flexibility and adapti 2. Manipulating Data Dataset used: "AB_NYC_2019.csv"

In this part of the exam you will have to play around with a dataset and display subsets of the data.
In [27]:
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = “all”

%matplotlib inline

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
sns.set_style(“whitegrid”)
sns.set_context(“notebook”)

A) Load “AB_NYC_2019.csv”, drop all null values, and name it “df”.
In [91]:
df=pd.read_csv(“AB_NYC_2019.csv”)
df.dropna(inplace=True)
#df.info()
df
Out[91]:

id
name
host_id
host_name
neighbourhood_group
neighbourhood
latitude
longitude
room_type
price
minimum_nights
number_of_reviews
last_review
reviews_per_month
calculated_host_listings_count
availability_365
0
2539
Clean & quiet apt home by the park
2787
John
Brooklyn
Kensington
40.64749
-73.97237
Private room
149
1
9
2018-10-19
0.21
6
365
1
2595
Skylit Midtown Castle
2845
Jennifer
Manhattan
Midtown
40.75362
-73.98377
Entire home/apt
225
1
45
2019-05-21
0.38
2
355
3
3831
Cozy Entire Floor of Brownstone
4869
LisaRoxanne
Brooklyn
Clinton Hill
40.68514
-73.95976
Entire home/apt
89
1
270
2019-07-05
4.64
1
194
4
5022
Entire Apt: Spacious Studio/Loft by central park
7192
Laura
Manhattan
East Harlem
40.79851
-73.94399
Entire home/apt
80
10
9
2018-11-19
0.10
1
0
5
5099
Large Cozy 1 BR Apartment In Midtown East
7322
Chris
Manhattan
Murray Hill
40.74767
-73.97500
Entire home/apt
200
3
74
2019-06-22
0.59
1
129
6
5121
BlissArtsSpace!
7356
Garon
Brooklyn
Bedford-Stuyvesant
40.68688
-73.95596
Private room
60
45
49
2017-10-05
0.40
1
0
7
5178
Large Furnished Room Near B’way
8967
Shunichi
Manhattan
Hell’s Kitchen
40.76489
-73.98493
Private room
79
2
430
2019-06-24
3.47
1
220
8
5203
Cozy Clean Guest Room – Family Apt
7490
MaryEllen
Manhattan
Upper West Side
40.80178
-73.96723
Private room
79
2
118
2017-07-21
0.99
1
0
9
5238
Cute & Cozy Lower East Side 1 bdrm
7549
Ben
Manhattan
Chinatown
40.71344
-73.99037
Entire home/apt
150
1
160
2019-06-09
1.33
4
188
10
5295
Beautiful 1br on Upper West Side
7702
Lena
Manhattan
Upper West Side
40.80316
-73.96545
Entire home/apt
135
5
53
2019-06-22
0.43
1
6
11
5441
Central Manhattan/near Broadway
7989
Kate
Manhattan
Hell’s Kitchen
40.76076
-73.98867
Private room
85
2
188
2019-06-23
1.50
1
39
12
5803
Lovely Room 1, Garden, Best Area, Legal rental
9744
Laurie
Brooklyn
South Slope
40.66829
-73.98779
Private room
89
4
167
2019-06-24
1.34
3
314
13
6021
Wonderful Guest Bedroom in Manhattan for SINGLES
11528
Claudio
Manhattan
Upper West Side
40.79826
-73.96113
Private room
85
2
113
2019-07-05
0.91
1
333
14
6090
West Village Nest – Superhost
11975
Alina
Manhattan
West Village
40.73530
-74.00525
Entire home/apt
120
90
27
2018-10-31
0.22
1
0
15
6848
Only 2 stops to Manhattan studio
15991
Allen & Irina
Brooklyn
Williamsburg
40.70837
-73.95352
Entire home/apt
140
2
148
2019-06-29
1.20
1
46
16
7097
Perfect for Your Parents + Garden
17571
Jane
Brooklyn
Fort Greene
40.69169
-73.97185
Entire home/apt
215
2
198
2019-06-28
1.72
1
321
17
7322
Chelsea Perfect
18946
Doti
Manhattan
Chelsea
40.74192
-73.99501
Private room
140
1
260
2019-07-01
2.12
1
12
18
7726
Hip Historic Brownstone Apartment with Backyard
20950
Adam And Charity
Brooklyn
Crown Heights
40.67592
-73.94694
Entire home/apt
99
3
53
2019-06-22
4.44
1
21
20
7801
Sweet and Spacious Brooklyn Loft
21207
Chaya
Brooklyn
Williamsburg
40.71842
-73.95718
Entire home/apt
299
3
9
2011-12-28
0.07
1
0
21
8024
CBG CtyBGd HelpsHaiti rm#1:1-4
22486
Lisel
Brooklyn
Park Slope
40.68069
-73.97706
Private room
130
2
130
2019-07-01
1.09
6
347
22
8025
CBG Helps Haiti Room#2.5
22486
Lisel
Brooklyn
Park Slope
40.67989
-73.97798
Private room
80
1
39
2019-01-01
0.37
6
364
23
8110
CBG Helps Haiti Rm #2
22486
Lisel
Brooklyn
Park Slope
40.68001
-73.97865
Private room
110
2
71
2019-07-02
0.61
6
304
24
8490
MAISON DES SIRENES1,bohemian apartment
25183
Nathalie
Brooklyn
Bedford-Stuyvesant
40.68371
-73.94028
Entire home/apt
120
2
88
2019-06-19
0.73
2
233
25
8505
Sunny Bedroom Across Prospect Park
25326
Gregory
Brooklyn
Windsor Terrace
40.65599
-73.97519
Private room
60
1
19
2019-06-23
1.37
2
85
27
9357
Midtown Pied-a-terre
30193
Tommi
Manhattan
Hell’s Kitchen
40.76715
-73.98533
Entire home/apt
150
10
58
2017-08-13
0.49
1
75
28
9518
SPACIOUS, LOVELY FURNISHED MANHATTAN BEDROOM
31374
Shon
Manhattan
Inwood
40.86482
-73.92106
Private room
44
3
108
2019-06-15
1.11
3
311
29
9657
Modern 1 BR / NYC / EAST VILLAGE
21904
Dana
Manhattan
East Village
40.72920
-73.98542
Entire home/apt
180
14
29
2019-04-19
0.24
1
67
30
9668
front room/double bed
32294
Ssameer Or Trip
Manhattan
Harlem
40.82245
-73.95104
Private room
50
3
242
2019-06-01
2.04
3
355
31
9704
Spacious 1 bedroom in luxe building
32045
Teri
Manhattan
Harlem
40.81305
-73.95466
Private room
52
2
88
2019-06-14
1.42
1
255
32
9782
Loft in Williamsburg Area w/ Roof
32169
Andrea
Brooklyn
Greenpoint
40.72219
-73.93762
Private room
55
4
197
2019-06-15
1.65
3
284
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
48387
36233091
1 room in bushwick
258998574
Emily
Brooklyn
Bushwick
40.70384
-73.92232
Private room
40
2
1
2019-07-03
1.00
1
16
48391
36235150
Sky View beautiful apt in Williamsburg New York
208514239
Melki
Brooklyn
Williamsburg
40.71232
-73.94220
Entire home/apt
170
1
1
2019-07-07
1.00
3
340
48392
36235154
Top on Manhattan
3850264
Jason
Manhattan
Harlem
40.80658
-73.95736
Private room
138
3
1
2019-07-03
1.00
2
65
48394
36236013
READY BED GO the goal is to make u feel at home
256197494
Emanuel
Brooklyn
Cypress Hills
40.68042
-73.88978
Private room
75
1
1
2019-07-05
1.00
1
82
48401
36237529
Beautiful Cozy Room, 20mn to FREE ferry
272557707
Marouene
Staten Island
Rosebank
40.60750
-74.07979
Private room
65
1
1
2019-07-05
1.00
1
179
48404
36238485
Chic Room in Williamsburg + Backyard +Washer D…
22157126
Ryan
Brooklyn
Williamsburg
40.71773
-73.94159
Private room
91
2
1
2019-07-04
1.00
1
344
48409
36241283
Enjoy your friendly stay at my place
267943725
Lana
Brooklyn
Midwood
40.62555
-73.95724
Private room
50
1
1
2019-07-02
1.00
1
86
48410
36241782
Classic Brooklyn Loft (East Williamsburg/Bushw…
12091973
Ap
Brooklyn
Williamsburg
40.70518
-73.93794
Private room
100
1
1
2019-07-06
1.00
1
1
48413
36242888
Upper East Site Luxury 2 Bed rooms
12579773
Jean-Nicolas
Manhattan
Upper East Side
40.76996
-73.95117
Private room
220
4
1
2019-07-03
1.00
1
15
48453
36272851
Private Bedroom in a Prewar Gramercy Building!*
19962052
Thikshan
Manhattan
Kips Bay
40.73929
-73.98183
Private room
135
2
1
2019-07-07
1.00
3
41
48454
36273046
Elegant Spacious UES private room
267839371
Celine
Manhattan
Upper East Side
40.77551
-73.95404
Private room
120
1
1
2019-07-06
1.00
2
358
48457
36274037
Cozy Wall Street Studio
272816114
Schmid
Manhattan
Financial District
40.70583
-74.01038
Entire home/apt
170
1
1
2019-07-06
1.00
1
323
48486
36280646
Cable and wfi, L/G included.
272872092
Chris
Queens
Forest Hills
40.73657
-73.85088
Entire home/apt
16
9
1
2019-07-07
1.00
1
322
48524
36308600
Cozy and Beautiful Park Slope One Bedroom
74773867
Kailey
Brooklyn
Park Slope
40.67455
-73.98477
Entire home/apt
150
1
1
2019-07-05
1.00
1
8
48526
36309284
Private Room Near JFK, St John’s Hospital, & B…
234090781
Phoenix
Queens
Bayswater
40.60804
-73.75829
Private room
45
3
1
2019-07-04
1.00
2
90
48532
36310268
Full size room
171482981
Alejandro
Queens
Maspeth
40.71327
-73.90982
Private room
65
2
1
2019-07-06
1.00
1
21
48534
36310855
Room 3: Cozy Room w Large Closet & Light Break…
268394581
Rehana
Brooklyn
Bedford-Stuyvesant
40.68969
-73.93285
Private room
68
1
1
2019-07-06
1.00
3
127
48576
36318560
Luxury Sun-filled Private Room near Time Square
52917571
Linda Lou
Manhattan
Midtown
40.75286
-73.99297
Private room
120
2
1
2019-07-06
1.00
1
7
48601
36335990
Sunny Studio steps from Wall Street
269870940
Farina
Manhattan
Financial District
40.70603
-74.01084
Entire home/apt
75
1
1
2019-07-03
1.00
1
181
48615
36344079
The Little Castle in Astoria
3867848
Prince
Queens
Astoria
40.76887
-73.91128
Private room
150
1
1
2019-07-04
1.00
1
165
48634
36351128
One bedroom without roomies, close to everything
273361532
David & Amy
Manhattan
Upper West Side
40.80281
-73.96550
Entire home/apt
110
3
2
2019-07-05
2.00
1
15
48636
36351543
2-MONTH SUBLEASE (WITH EARLY MOVE-IN)
52984497
Friday
Brooklyn
Bedford-Stuyvesant
40.68914
-73.92408
Private room
33
30
2
2019-07-06
2.00
1
87
48701
36390226
Comfortable clean Bedstuy private room
267932490
Angela
Brooklyn
Bedford-Stuyvesant
40.69551
-73.93951
Private room
45
1
2
2019-07-08
2.00
1
14
48732
36411407
Brand new 1 bedroom steps from Soho!
33917435
Mike
Manhattan
Lower East Side
40.71825
-73.99019
Entire home/apt
150
4
1
2019-07-06
1.00
1
13
48740
36413632
Spacious 2BR in Beautiful Brooklyn Heights
6608220
Matt
Brooklyn
Brooklyn Heights
40.69827
-73.99649
Entire home/apt
550
3
1
2019-07-07
1.00
1
230
48782
36425863
Lovely Privet Bedroom with Privet Restroom
83554966
Rusaa
Manhattan
Upper East Side
40.78099
-73.95366
Private room
129
1
1
2019-07-07
1.00
1
147
48790
36427429
No.2 with queen size bed
257683179
H Ai
Queens
Flushing
40.75104
-73.81459
Private room
45
1
1
2019-07-07
1.00
6
339
48799
36438336
Seas The Moment
211644523
Ben
Staten Island
Great Kills
40.54179
-74.14275
Private room
235
1
1
2019-07-07
1.00
1
87
48805
36442252
1B-1B apartment near by Metro
273841667
Blaine
Bronx
Mott Haven
40.80787
-73.92400
Entire home/apt
100
1
2
2019-07-07
2.00
1
40
48852
36455809
Cozy Private Room in Bushwick, Brooklyn
74162901
Christine
Brooklyn
Bushwick
40.69805
-73.92801
Private room
30
1
1
2019-07-08
1.00
1
1
38821 rows × 16 columns

B) Within the dataframe “df”, permanently rename the column “name” to “listing_name”.
In [92]:
df.rename(columns={“name”:”listing_name”}, inplace=True)
df
Out[92]:

id
listing_name
host_id
host_name
neighbourhood_group
neighbourhood
latitude
longitude
room_type
price
minimum_nights
number_of_reviews
last_review
reviews_per_month
calculated_host_listings_count
availability_365
0
2539
Clean & quiet apt home by the park
2787
John
Brooklyn
Kensington
40.64749
-73.97237
Private room
149
1
9
2018-10-19
0.21
6
365
1
2595
Skylit Midtown Castle
2845
Jennifer
Manhattan
Midtown
40.75362
-73.98377
Entire home/apt
225
1
45
2019-05-21
0.38
2
355
3
3831
Cozy Entire Floor of Brownstone
4869
LisaRoxanne
Brooklyn
Clinton Hill
40.68514
-73.95976
Entire home/apt
89
1
270
2019-07-05
4.64
1
194
4
5022
Entire Apt: Spacious Studio/Loft by central park
7192
Laura
Manhattan
East Harlem
40.79851
-73.94399
Entire home/apt
80
10
9
2018-11-19
0.10
1
0
5
5099
Large Cozy 1 BR Apartment In Midtown East
7322
Chris
Manhattan
Murray Hill
40.74767
-73.97500
Entire home/apt
200
3
74
2019-06-22
0.59
1
129
6
5121
BlissArtsSpace!
7356
Garon
Brooklyn
Bedford-Stuyvesant
40.68688
-73.95596
Private room
60
45
49
2017-10-05
0.40
1
0
7
5178
Large Furnished Room Near B’way
8967
Shunichi
Manhattan
Hell’s Kitchen
40.76489
-73.98493
Private room
79
2
430
2019-06-24
3.47
1
220
8
5203
Cozy Clean Guest Room – Family Apt
7490
MaryEllen
Manhattan
Upper West Side
40.80178
-73.96723
Private room
79
2
118
2017-07-21
0.99
1
0
9
5238
Cute & Cozy Lower East Side 1 bdrm
7549
Ben
Manhattan
Chinatown
40.71344
-73.99037
Entire home/apt
150
1
160
2019-06-09
1.33
4
188
10
5295
Beautiful 1br on Upper West Side
7702
Lena
Manhattan
Upper West Side
40.80316
-73.96545
Entire home/apt
135
5
53
2019-06-22
0.43
1
6
11
5441
Central Manhattan/near Broadway
7989
Kate
Manhattan
Hell’s Kitchen
40.76076
-73.98867
Private room
85
2
188
2019-06-23
1.50
1
39
12
5803
Lovely Room 1, Garden, Best Area, Legal rental
9744
Laurie
Brooklyn
South Slope
40.66829
-73.98779
Private room
89
4
167
2019-06-24
1.34
3
314
13
6021
Wonderful Guest Bedroom in Manhattan for SINGLES
11528
Claudio
Manhattan
Upper West Side
40.79826
-73.96113
Private room
85
2
113
2019-07-05
0.91
1
333
14
6090
West Village Nest – Superhost
11975
Alina
Manhattan
West Village
40.73530
-74.00525
Entire home/apt
120
90
27
2018-10-31
0.22
1
0
15
6848
Only 2 stops to Manhattan studio
15991
Allen & Irina
Brooklyn
Williamsburg
40.70837
-73.95352
Entire home/apt
140
2
148
2019-06-29
1.20
1
46
16
7097
Perfect for Your Parents + Garden
17571
Jane
Brooklyn
Fort Greene
40.69169
-73.97185
Entire home/apt
215
2
198
2019-06-28
1.72
1
321
17
7322
Chelsea Perfect
18946
Doti
Manhattan
Chelsea
40.74192
-73.99501
Private room
140
1
260
2019-07-01
2.12
1
12
18
7726
Hip Historic Brownstone Apartment with Backyard
20950
Adam And Charity
Brooklyn
Crown Heights
40.67592
-73.94694
Entire home/apt
99
3
53
2019-06-22
4.44
1
21
20
7801
Sweet and Spacious Brooklyn Loft
21207
Chaya
Brooklyn
Williamsburg
40.71842
-73.95718
Entire home/apt
299
3
9
2011-12-28
0.07
1
0
21
8024
CBG CtyBGd HelpsHaiti rm#1:1-4
22486
Lisel
Brooklyn
Park Slope
40.68069
-73.97706
Private room
130
2
130
2019-07-01
1.09
6
347
22
8025
CBG Helps Haiti Room#2.5
22486
Lisel
Brooklyn
Park Slope
40.67989
-73.97798
Private room
80
1
39
2019-01-01
0.37
6
364
23
8110
CBG Helps Haiti Rm #2
22486
Lisel
Brooklyn
Park Slope
40.68001
-73.97865
Private room
110
2
71
2019-07-02
0.61
6
304
24
8490
MAISON DES SIRENES1,bohemian apartment
25183
Nathalie
Brooklyn
Bedford-Stuyvesant
40.68371
-73.94028
Entire home/apt
120
2
88
2019-06-19
0.73
2
233
25
8505
Sunny Bedroom Across Prospect Park
25326
Gregory
Brooklyn
Windsor Terrace
40.65599
-73.97519
Private room
60
1
19
2019-06-23
1.37
2
85
27
9357
Midtown Pied-a-terre
30193
Tommi
Manhattan
Hell’s Kitchen
40.76715
-73.98533
Entire home/apt
150
10
58
2017-08-13
0.49
1
75
28
9518
SPACIOUS, LOVELY FURNISHED MANHATTAN BEDROOM
31374
Shon
Manhattan
Inwood
40.86482
-73.92106
Private room
44
3
108
2019-06-15
1.11
3
311
29
9657
Modern 1 BR / NYC / EAST VILLAGE
21904
Dana
Manhattan
East Village
40.72920
-73.98542
Entire home/apt
180
14
29
2019-04-19
0.24
1
67
30
9668
front room/double bed
32294
Ssameer Or Trip
Manhattan
Harlem
40.82245
-73.95104
Private room
50
3
242
2019-06-01
2.04
3
355
31
9704
Spacious 1 bedroom in luxe building
32045
Teri
Manhattan
Harlem
40.81305
-73.95466
Private room
52
2
88
2019-06-14
1.42
1
255
32
9782
Loft in Williamsburg Area w/ Roof
32169
Andrea
Brooklyn
Greenpoint
40.72219
-73.93762
Private room
55
4
197
2019-06-15
1.65
3
284
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
48387
36233091
1 room in bushwick
258998574
Emily
Brooklyn
Bushwick
40.70384
-73.92232
Private room
40
2
1
2019-07-03
1.00
1
16
48391
36235150
Sky View beautiful apt in Williamsburg New York
208514239
Melki
Brooklyn
Williamsburg
40.71232
-73.94220
Entire home/apt
170
1
1
2019-07-07
1.00
3
340
48392
36235154
Top on Manhattan
3850264
Jason
Manhattan
Harlem
40.80658
-73.95736
Private room
138
3
1
2019-07-03
1.00
2
65
48394
36236013
READY BED GO the goal is to make u feel at home
256197494
Emanuel
Brooklyn
Cypress Hills
40.68042
-73.88978
Private room
75
1
1
2019-07-05
1.00
1
82
48401
36237529
Beautiful Cozy Room, 20mn to FREE ferry
272557707
Marouene
Staten Island
Rosebank
40.60750
-74.07979
Private room
65
1
1
2019-07-05
1.00
1
179
48404
36238485
Chic Room in Williamsburg + Backyard +Washer D…
22157126
Ryan
Brooklyn
Williamsburg
40.71773
-73.94159
Private room
91
2
1
2019-07-04
1.00
1
344
48409
36241283
Enjoy your friendly stay at my place
267943725
Lana
Brooklyn
Midwood
40.62555
-73.95724
Private room
50
1
1
2019-07-02
1.00
1
86
48410
36241782
Classic Brooklyn Loft (East Williamsburg/Bushw…
12091973
Ap
Brooklyn
Williamsburg
40.70518
-73.93794
Private room
100
1
1
2019-07-06
1.00
1
1
48413
36242888
Upper East Site Luxury 2 Bed rooms
12579773
Jean-Nicolas
Manhattan
Upper East Side
40.76996
-73.95117
Private room
220
4
1
2019-07-03
1.00
1
15
48453
36272851
Private Bedroom in a Prewar Gramercy Building!*
19962052
Thikshan
Manhattan
Kips Bay
40.73929
-73.98183
Private room
135
2
1
2019-07-07
1.00
3
41
48454
36273046
Elegant Spacious UES private room
267839371
Celine
Manhattan
Upper East Side
40.77551
-73.95404
Private room
120
1
1
2019-07-06
1.00
2
358
48457
36274037
Cozy Wall Street Studio
272816114
Schmid
Manhattan
Financial District
40.70583
-74.01038
Entire home/apt
170
1
1
2019-07-06
1.00
1
323
48486
36280646
Cable and wfi, L/G included.
272872092
Chris
Queens
Forest Hills
40.73657
-73.85088
Entire home/apt
16
9
1
2019-07-07
1.00
1
322
48524
36308600
Cozy and Beautiful Park Slope One Bedroom
74773867
Kailey
Brooklyn
Park Slope
40.67455
-73.98477
Entire home/apt
150
1
1
2019-07-05
1.00
1
8
48526
36309284
Private Room Near JFK, St John’s Hospital, & B…
234090781
Phoenix
Queens
Bayswater
40.60804
-73.75829
Private room
45
3
1
2019-07-04
1.00
2
90
48532
36310268
Full size room
171482981
Alejandro
Queens
Maspeth
40.71327
-73.90982
Private room
65
2
1
2019-07-06
1.00
1
21
48534
36310855
Room 3: Cozy Room w Large Closet & Light Break…
268394581
Rehana
Brooklyn
Bedford-Stuyvesant
40.68969
-73.93285
Private room
68
1
1
2019-07-06
1.00
3
127
48576
36318560
Luxury Sun-filled Private Room near Time Square
52917571
Linda Lou
Manhattan
Midtown
40.75286
-73.99297
Private room
120
2
1
2019-07-06
1.00
1
7
48601
36335990
Sunny Studio steps from Wall Street
269870940
Farina
Manhattan
Financial District
40.70603
-74.01084
Entire home/apt
75
1
1
2019-07-03
1.00
1
181
48615
36344079
The Little Castle in Astoria
3867848
Prince
Queens
Astoria
40.76887
-73.91128
Private room
150
1
1
2019-07-04
1.00
1
165
48634
36351128
One bedroom without roomies, close to everything
273361532
David & Amy
Manhattan
Upper West Side
40.80281
-73.96550
Entire home/apt
110
3
2
2019-07-05
2.00
1
15
48636
36351543
2-MONTH SUBLEASE (WITH EARLY MOVE-IN)
52984497
Friday
Brooklyn
Bedford-Stuyvesant
40.68914
-73.92408
Private room
33
30
2
2019-07-06
2.00
1
87
48701
36390226
Comfortable clean Bedstuy private room
267932490
Angela
Brooklyn
Bedford-Stuyvesant
40.69551
-73.93951
Private room
45
1
2
2019-07-08
2.00
1
14
48732
36411407
Brand new 1 bedroom steps from Soho!
33917435
Mike
Manhattan
Lower East Side
40.71825
-73.99019
Entire home/apt
150
4
1
2019-07-06
1.00
1
13
48740
36413632
Spacious 2BR in Beautiful Brooklyn Heights
6608220
Matt
Brooklyn
Brooklyn Heights
40.69827
-73.99649
Entire home/apt
550
3
1
2019-07-07
1.00
1
230
48782
36425863
Lovely Privet Bedroom with Privet Restroom
83554966
Rusaa
Manhattan
Upper East Side
40.78099
-73.95366
Private room
129
1
1
2019-07-07
1.00
1
147
48790
36427429
No.2 with queen size bed
257683179
H Ai
Queens
Flushing
40.75104
-73.81459
Private room
45
1
1
2019-07-07
1.00
6
339
48799
36438336
Seas The Moment
211644523
Ben
Staten Island
Great Kills
40.54179
-74.14275
Private room
235
1
1
2019-07-07
1.00
1
87
48805
36442252
1B-1B apartment near by Metro
273841667
Blaine
Bronx
Mott Haven
40.80787
-73.92400
Entire home/apt
100
1
2
2019-07-07
2.00
1
40
48852
36455809
Cozy Private Room in Bushwick, Brooklyn
74162901
Christine
Brooklyn
Bushwick
40.69805
-73.92801
Private room
30
1
1
2019-07-08
1.00
1
1
38821 rows × 16 columns

C) Drop the column host_id. Save the new dataframe again under the name “df”.
In [93]:
df.drop(“host_id”, axis=1, inplace=True)
df
Out[93]:

id
listing_name
host_name
neighbourhood_group
neighbourhood
latitude
longitude
room_type
price
minimum_nights
number_of_reviews
last_review
reviews_per_month
calculated_host_listings_count
availability_365
0
2539
Clean & quiet apt home by the park
John
Brooklyn
Kensington
40.64749
-73.97237
Private room
149
1
9
2018-10-19
0.21
6
365
1
2595
Skylit Midtown Castle
Jennifer
Manhattan
Midtown
40.75362
-73.98377
Entire home/apt
225
1
45
2019-05-21
0.38
2
355
3
3831
Cozy Entire Floor of Brownstone
LisaRoxanne
Brooklyn
Clinton Hill
40.68514
-73.95976
Entire home/apt
89
1
270
2019-07-05
4.64
1
194
4
5022
Entire Apt: Spacious Studio/Loft by central park
Laura
Manhattan
East Harlem
40.79851
-73.94399
Entire home/apt
80
10
9
2018-11-19
0.10
1
0
5
5099
Large Cozy 1 BR Apartment In Midtown East
Chris
Manhattan
Murray Hill
40.74767
-73.97500
Entire home/apt
200
3
74
2019-06-22
0.59
1
129
6
5121
BlissArtsSpace!
Garon
Brooklyn
Bedford-Stuyvesant
40.68688
-73.95596
Private room
60
45
49
2017-10-05
0.40
1
0
7
5178
Large Furnished Room Near B’way
Shunichi
Manhattan
Hell’s Kitchen
40.76489
-73.98493
Private room
79
2
430
2019-06-24
3.47
1
220
8
5203
Cozy Clean Guest Room – Family Apt
MaryEllen
Manhattan
Upper West Side
40.80178
-73.96723
Private room
79
2
118
2017-07-21
0.99
1
0
9
5238
Cute & Cozy Lower East Side 1 bdrm
Ben
Manhattan
Chinatown
40.71344
-73.99037
Entire home/apt
150
1
160
2019-06-09
1.33
4
188
10
5295
Beautiful 1br on Upper West Side
Lena
Manhattan
Upper West Side
40.80316
-73.96545
Entire home/apt
135
5
53
2019-06-22
0.43
1
6
11
5441
Central Manhattan/near Broadway
Kate
Manhattan
Hell’s Kitchen
40.76076
-73.98867
Private room
85
2
188
2019-06-23
1.50
1
39
12
5803
Lovely Room 1, Garden, Best Area, Legal rental
Laurie
Brooklyn
South Slope
40.66829
-73.98779
Private room
89
4
167
2019-06-24
1.34
3
314
13
6021
Wonderful Guest Bedroom in Manhattan for SINGLES
Claudio
Manhattan
Upper West Side
40.79826
-73.96113
Private room
85
2
113
2019-07-05
0.91
1
333
14
6090
West Village Nest – Superhost
Alina
Manhattan
West Village
40.73530
-74.00525
Entire home/apt
120
90
27
2018-10-31
0.22
1
0
15
6848
Only 2 stops to Manhattan studio
Allen & Irina
Brooklyn
Williamsburg
40.70837
-73.95352
Entire home/apt
140
2
148
2019-06-29
1.20
1
46
16
7097
Perfect for Your Parents + Garden
Jane
Brooklyn
Fort Greene
40.69169
-73.97185
Entire home/apt
215
2
198
2019-06-28
1.72
1
321
17
7322
Chelsea Perfect
Doti
Manhattan
Chelsea
40.74192
-73.99501
Private room
140
1
260
2019-07-01
2.12
1
12
18
7726
Hip Historic Brownstone Apartment with Backyard
Adam And Charity
Brooklyn
Crown Heights
40.67592
-73.94694
Entire home/apt
99
3
53
2019-06-22
4.44
1
21
20
7801
Sweet and Spacious Brooklyn Loft
Chaya
Brooklyn
Williamsburg
40.71842
-73.95718
Entire home/apt
299
3
9
2011-12-28
0.07
1
0
21
8024
CBG CtyBGd HelpsHaiti rm#1:1-4
Lisel
Brooklyn
Park Slope
40.68069
-73.97706
Private room
130
2
130
2019-07-01
1.09
6
347
22
8025
CBG Helps Haiti Room#2.5
Lisel
Brooklyn
Park Slope
40.67989
-73.97798
Private room
80
1
39
2019-01-01
0.37
6
364
23
8110
CBG Helps Haiti Rm #2
Lisel
Brooklyn
Park Slope
40.68001
-73.97865
Private room
110
2
71
2019-07-02
0.61
6
304
24
8490
MAISON DES SIRENES1,bohemian apartment
Nathalie
Brooklyn
Bedford-Stuyvesant
40.68371
-73.94028
Entire home/apt
120
2
88
2019-06-19
0.73
2
233
25
8505
Sunny Bedroom Across Prospect Park
Gregory
Brooklyn
Windsor Terrace
40.65599
-73.97519
Private room
60
1
19
2019-06-23
1.37
2
85
27
9357
Midtown Pied-a-terre
Tommi
Manhattan
Hell’s Kitchen
40.76715
-73.98533
Entire home/apt
150
10
58
2017-08-13
0.49
1
75
28
9518
SPACIOUS, LOVELY FURNISHED MANHATTAN BEDROOM
Shon
Manhattan
Inwood
40.86482
-73.92106
Private room
44
3
108
2019-06-15
1.11
3
311
29
9657
Modern 1 BR / NYC / EAST VILLAGE
Dana
Manhattan
East Village
40.72920
-73.98542
Entire home/apt
180
14
29
2019-04-19
0.24
1
67
30
9668
front room/double bed
Ssameer Or Trip
Manhattan
Harlem
40.82245
-73.95104
Private room
50
3
242
2019-06-01
2.04
3
355
31
9704
Spacious 1 bedroom in luxe building
Teri
Manhattan
Harlem
40.81305
-73.95466
Private room
52
2
88
2019-06-14
1.42
1
255
32
9782
Loft in Williamsburg Area w/ Roof
Andrea
Brooklyn
Greenpoint
40.72219
-73.93762
Private room
55
4
197
2019-06-15
1.65
3
284
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
48387
36233091
1 room in bushwick
Emily
Brooklyn
Bushwick
40.70384
-73.92232
Private room
40
2
1
2019-07-03
1.00
1
16
48391
36235150
Sky View beautiful apt in Williamsburg New York
Melki
Brooklyn
Williamsburg
40.71232
-73.94220
Entire home/apt
170
1
1
2019-07-07
1.00
3
340
48392
36235154
Top on Manhattan
Jason
Manhattan
Harlem
40.80658
-73.95736
Private room
138
3
1
2019-07-03
1.00
2
65
48394
36236013
READY BED GO the goal is to make u feel at home
Emanuel
Brooklyn
Cypress Hills
40.68042
-73.88978
Private room
75
1
1
2019-07-05
1.00
1
82
48401
36237529
Beautiful Cozy Room, 20mn to FREE ferry
Marouene
Staten Island
Rosebank
40.60750
-74.07979
Private room
65
1
1
2019-07-05
1.00
1
179
48404
36238485
Chic Room in Williamsburg + Backyard +Washer D…
Ryan
Brooklyn
Williamsburg
40.71773
-73.94159
Private room
91
2
1
2019-07-04
1.00
1
344
48409
36241283
Enjoy your friendly stay at my place
Lana
Brooklyn
Midwood
40.62555
-73.95724
Private room
50
1
1
2019-07-02
1.00
1
86
48410
36241782
Classic Brooklyn Loft (East Williamsburg/Bushw…
Ap
Brooklyn
Williamsburg
40.70518
-73.93794
Private room
100
1
1
2019-07-06
1.00
1
1
48413
36242888
Upper East Site Luxury 2 Bed rooms
Jean-Nicolas
Manhattan
Upper East Side
40.76996
-73.95117
Private room
220
4
1
2019-07-03
1.00
1
15
48453
36272851
Private Bedroom in a Prewar Gramercy Building!*
Thikshan
Manhattan
Kips Bay
40.73929
-73.98183
Private room
135
2
1
2019-07-07
1.00
3
41
48454
36273046
Elegant Spacious UES private room
Celine
Manhattan
Upper East Side
40.77551
-73.95404
Private room
120
1
1
2019-07-06
1.00
2
358
48457
36274037
Cozy Wall Street Studio
Schmid
Manhattan
Financial District
40.70583
-74.01038
Entire home/apt
170
1
1
2019-07-06
1.00
1
323
48486
36280646
Cable and wfi, L/G included.
Chris
Queens
Forest Hills
40.73657
-73.85088
Entire home/apt
16
9
1
2019-07-07
1.00
1
322
48524
36308600
Cozy and Beautiful Park Slope One Bedroom
Kailey
Brooklyn
Park Slope
40.67455
-73.98477
Entire home/apt
150
1
1
2019-07-05
1.00
1
8
48526
36309284
Private Room Near JFK, St John’s Hospital, & B…
Phoenix
Queens
Bayswater
40.60804
-73.75829
Private room
45
3
1
2019-07-04
1.00
2
90
48532
36310268
Full size room
Alejandro
Queens
Maspeth
40.71327
-73.90982
Private room
65
2
1
2019-07-06
1.00
1
21
48534
36310855
Room 3: Cozy Room w Large Closet & Light Break…
Rehana
Brooklyn
Bedford-Stuyvesant
40.68969
-73.93285
Private room
68
1
1
2019-07-06
1.00
3
127
48576
36318560
Luxury Sun-filled Private Room near Time Square
Linda Lou
Manhattan
Midtown
40.75286
-73.99297
Private room
120
2
1
2019-07-06
1.00
1
7
48601
36335990
Sunny Studio steps from Wall Street
Farina
Manhattan
Financial District
40.70603
-74.01084
Entire home/apt
75
1
1
2019-07-03
1.00
1
181
48615
36344079
The Little Castle in Astoria
Prince
Queens
Astoria
40.76887
-73.91128
Private room
150
1
1
2019-07-04
1.00
1
165
48634
36351128
One bedroom without roomies, close to everything
David & Amy
Manhattan
Upper West Side
40.80281
-73.96550
Entire home/apt
110
3
2
2019-07-05
2.00
1
15
48636
36351543
2-MONTH SUBLEASE (WITH EARLY MOVE-IN)
Friday
Brooklyn
Bedford-Stuyvesant
40.68914
-73.92408
Private room
33
30
2
2019-07-06
2.00
1
87
48701
36390226
Comfortable clean Bedstuy private room
Angela
Brooklyn
Bedford-Stuyvesant
40.69551
-73.93951
Private room
45
1
2
2019-07-08
2.00
1
14
48732
36411407
Brand new 1 bedroom steps from Soho!
Mike
Manhattan
Lower East Side
40.71825
-73.99019
Entire home/apt
150
4
1
2019-07-06
1.00
1
13
48740
36413632
Spacious 2BR in Beautiful Brooklyn Heights
Matt
Brooklyn
Brooklyn Heights
40.69827
-73.99649
Entire home/apt
550
3
1
2019-07-07
1.00
1
230
48782
36425863
Lovely Privet Bedroom with Privet Restroom
Rusaa
Manhattan
Upper East Side
40.78099
-73.95366
Private room
129
1
1
2019-07-07
1.00
1
147
48790
36427429
No.2 with queen size bed
H Ai
Queens
Flushing
40.75104
-73.81459
Private room
45
1
1
2019-07-07
1.00
6
339
48799
36438336
Seas The Moment
Ben
Staten Island
Great Kills
40.54179
-74.14275
Private room
235
1
1
2019-07-07
1.00
1
87
48805
36442252
1B-1B apartment near by Metro
Blaine
Bronx
Mott Haven
40.80787
-73.92400
Entire home/apt
100
1
2
2019-07-07
2.00
1
40
48852
36455809
Cozy Private Room in Bushwick, Brooklyn
Christine
Brooklyn
Bushwick
40.69805
-73.92801
Private room
30
1
1
2019-07-08
1.00
1
1
38821 rows × 15 columns

D) Only show listings with a price higher than the average price. Sort the dataframe by price in an ascending order.
In [94]:
df[df[“price”]>df[“price”].mean()].sort_values(by=”price”, ascending = True)
Out[94]:

id
listing_name
host_name
neighbourhood_group
neighbourhood
latitude
longitude
room_type
price
minimum_nights
number_of_reviews
last_review
reviews_per_month
calculated_host_listings_count
availability_365
16776
13395955
Adorable Prospect Heights 1 Bedroom
Nancy
Brooklyn
Prospect Heights
40.67649
-73.96697
Entire home/apt
143
31
3
2018-07-21
0.08
1
0
15285
12234139
1 Bedroom Outside Columbus Circle
Mckenzie
Manhattan
Upper West Side
40.77116
-73.98796
Entire home/apt
143
2
32
2019-04-22
0.81
1
0
15774
12755571
Studio in the heart of Manhattan
Nina
Manhattan
Midtown
40.75227
-73.97351
Entire home/apt
143
1
1
2016-05-13
0.03
1
0
37425
29699681
Sunny Penthouse with Williamsburg Bridge view
Laura
Brooklyn
Williamsburg
40.71093
-73.96407
Entire home/apt
143
5
2
2019-05-21
0.24
1
9
41791
32494256
Newly renovated 2 Bedroom Apartment
Victoria
Brooklyn
Clinton Hill
40.68771
-73.96304
Entire home/apt
143
2
22
2019-06-18
5.00
1
81
19842
15889590
A Stunning Private 1 Bedroom in Downtown Flushing
Alan
Queens
Flushing
40.76092
-73.82194
Entire home/apt
143
30
4
2019-06-09
0.22
15
7
27089
21432084
Times Square Room with access to a Terrace
Chemme
Manhattan
Hell’s Kitchen
40.75873
-73.99041
Private room
143
3
18
2019-05-20
0.89
4
365
28054
21909717
Nice view apartment
Dan Hua
Queens
Jackson Heights
40.75731
-73.85785
Entire home/apt
143
2
3
2018-01-02
0.15
1
0
19806
15858680
Cozy Private Room close to Central Park/Met Mu…
Francis
Manhattan
Upper East Side
40.77502
-73.95536
Private room
143
2
8
2018-12-05
0.26
1
365
13639
10212685
Maryhills Brownstone in Park Slope
Hilal & Maryam
Brooklyn
South Slope
40.66410
-73.98036
Entire home/apt
143
3
180
2019-06-23
4.20
2
263
3027
1780748
Sunny 1 bedroom In The Heart of NYC
Pk
Manhattan
Murray Hill
40.74881
-73.97234
Private room
143
2
37
2018-12-08
0.53
1
364
28497
22097438
Cozy Flat – Walk to Central Park
Jonathan
Manhattan
Upper East Side
40.78281
-73.94516
Entire home/apt
143
2
88
2019-06-25
4.90
1
29
9291
7094539
Tranquil haven in bubbly Brooklyn
Adriana
Brooklyn
Windsor Terrace
40.65360
-73.97546
Entire home/apt
143
14
2
2016-08-27
0.04
1
10
35419
28101217
Cozy Bushwick apartment with backyard!
Nicole
Brooklyn
Bushwick
40.69552
-73.92341
Entire home/apt
143
2
3
2018-10-14
0.29
2
0
33585
26572767
Cozy Private Upper Eastside Bedroom
David
Manhattan
Upper East Side
40.77673
-73.95525
Private room
143
2
4
2018-10-01
0.34
2
0
20751
16441995
Sunny & Sweet 1BR BK Apt – 25 mins to Manhattan
Julie
Brooklyn
Bedford-Stuyvesant
40.68004
-73.92150
Entire home/apt
143
2
8
2018-09-03
0.26
1
38
18080
14155064
One Bedroom in Heart of Chelsea
Rachel
Manhattan
Chelsea
40.74121
-73.99995
Entire home/apt
143
4
1
2016-10-09
0.03
1
0
27219
21512039
Perfect Studio, Great location!
Alejandra
Manhattan
Financial District
40.70507
-74.00900
Private room
143
2
3
2018-02-08
0.15
1
0
21590
17300167
Spacious, modern, cozy & bright Williamsburg o…
Teresa
Brooklyn
Williamsburg
40.71186
-73.95402
Entire home/apt
144
2
24
2019-07-06
1.43
1
14
19206
15289231
Colorful Artist Apartment in Park Slope Brooklyn
Caroline
Brooklyn
South Slope
40.66634
-73.98185
Entire home/apt
144
1
32
2019-05-27
0.96
1
0
11993
9342019
Cozy 2 Bedroom in Historic District
Benjamin
Queens
Jackson Heights
40.75462
-73.88284
Entire home/apt
144
2
13
2019-03-24
0.30
1
288
25228
20190053
Beautiful, family friendly place with great views
Jonathan
Brooklyn
Columbia St
40.68356
-74.00301
Entire home/apt
144
1
11
2018-09-03
0.48
1
0
20999
16630483
SPACIOUS, trendy Flatiron Apt-Traveler’s Deli…
Matt
Manhattan
Kips Bay
40.73945
-73.98273
Entire home/apt
144
2
2
2018-12-16
0.08
1
0
42822
33243396
Big, Quiet Apartment with Two Cats, Central Park
Rachel
Manhattan
Upper West Side
40.79702
-73.96256
Entire home/apt
144
3
2
2019-07-05
2.00
1
0
4876
3479639
Designer’s duplex loft with 16′ ceilings
Taylor
Brooklyn
Bushwick
40.69642
-73.93361
Entire home/apt
144
1
51
2019-06-15
0.84
2
85
21137
16775958
The Happy Hideaway
Jenn
Brooklyn
South Slope
40.66180
-73.98234
Entire home/apt
144
2
12
2019-01-01
0.90
2
0
18254
14316033
ONLY 4.5 MILES TO MANHATTAN
Charles
Queens
Maspeth
40.72211
-73.90905
Entire home/apt
144
2
113
2019-06-11
3.18
3
293
43042
33416167
Bright, cozy getaway in the center of all..
Chris
Manhattan
Hell’s Kitchen
40.76088
-73.99081
Entire home/apt
144
1
25
2019-07-05
7.98
1
12
15204
12151638
Spacious 2 bedroom Garden apartment
Ruma
Brooklyn
Clinton Hill
40.68099
-73.96343
Entire home/apt
144
2
49
2019-07-03
1.28
1
69
17640
13867110
Comfort First, in Williamsburg
Irma
Brooklyn
Williamsburg
40.70990
-73.95779
Entire home/apt
144
2
14
2016-12-15
0.39
1
0
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
45185
34592851
Beautiful Brooklyn Brownstone Production Space
Cheryl
Brooklyn
Crown Heights
40.67015
-73.94782
Entire home/apt
2500
1
1
2019-06-08
0.94
2
168
2018
893413
Architecturally Stunning Former Synagogue!
Martin
Manhattan
East Village
40.72668
-73.98179
Entire home/apt
2500
30
15
2019-07-01
0.26
1
89
24286
19554980
A Cozy Manhattan Sanctuary
Logan
Manhattan
Upper West Side
40.80160
-73.96409
Entire home/apt
2500
40
17
2018-03-26
0.91
1
0
32041
25018204
Paradise Garden, Spa, Steam & Massage Table #1…
Rasmus
Manhattan
Harlem
40.82263
-73.95181
Entire home/apt
2500
3
14
2019-06-24
1.38
2
150
18356
14408114
Unparalleled Luxury in Midtown Manhattan
Henry
Manhattan
Midtown
40.76455
-73.97959
Entire home/apt
2500
30
4
2017-04-17
0.13
11
364
20551
16348791
Spectacular Townhouse 4Floor 5BD West Village NYC
Maud
Manhattan
West Village
40.73726
-74.00085
Entire home/apt
2590
1
8
2018-12-27
0.41
1
361
42680
33133321
Majestic Mansion LifeStyle 🙂
Shah
Queens
Bayside
40.77811
-73.77069
Entire home/apt
2600
6
3
2019-05-30
1.73
1
362
30824
23860219
Designer’s Beautiful 2BR Apartment in NOLITA/SOHO
Ilo And Richard
Manhattan
Nolita
40.72233
-73.99574
Entire home/apt
2990
2
69
2019-06-29
4.36
1
237
46533
35297214
Amazing Chelsea 4BR Loft!
Viberlyn
Manhattan
Chelsea
40.73999
-73.99806
Entire home/apt
2995
30
1
2019-06-24
1.00
1
214
18632
14730708
Breathtaking City Views 3 Bed Apt. in Lincoln Sq!
Henry
Manhattan
Upper West Side
40.77910
-73.98182
Entire home/apt
3000
30
8
2016-12-10
0.24
11
365
38223
30146411
Luxury West Village private Townhouse 5B Garden
Luxury Property
Manhattan
Gramercy
40.73652
-73.98618
Entire home/apt
3000
1
1
2018-12-16
0.15
1
365
28858
22263855
SPECTACULAR SOHO GREAT ROOM LOFT 6000sq feet
Stephanie
Manhattan
SoHo
40.72605
-74.00572
Entire home/apt
3000
7
1
2019-06-30
1.00
1
325
42542
33029434
West 84th Street by (Hidden by Airbnb)
Sally
Manhattan
Upper West Side
40.78545
-73.97123
Entire home/apt
3613
1
1
2019-06-15
1.00
12
158
17665
13882394
VIP Duplex Townhouse with Private Garden!!!
Joseph
Manhattan
Greenwich Village
40.73305
-73.99373
Entire home/apt
3800
30
2
2017-01-27
0.06
1
180
25901
20692563
Greenwich Village Townhome with Private Garden!
Lloyd
Manhattan
Greenwich Village
40.73300
-73.99413
Entire home/apt
3900
30
7
2018-03-13
0.32
1
180
4376
2952861
Photography Location
Jessica
Brooklyn
Clinton Hill
40.69127
-73.96563
Entire home/apt
4500
1
5
2018-12-29
0.09
11
365
38000
30035166
4-Floor Unique Event Space 50P Cap. – #10299B
Rasmus
Manhattan
Harlem
40.82511
-73.94961
Entire home/apt
5000
1
2
2019-02-23
0.38
2
150
26739
21238053
Broadway 1
Sarah-B
Brooklyn
Bedford-Stuyvesant
40.68742
-73.91957
Entire home/apt
5000
2
8
2017-12-09
0.38
1
0
4345
2919330
NearWilliamsburg bridge 11211 BK
Bianca
Brooklyn
Bedford-Stuyvesant
40.69572
-73.95731
Private room
5000
6
10
2016-01-02
0.16
1
363
25825
20654227
Fulton 2
Sarah-2
Brooklyn
Cypress Hills
40.68185
-73.88128
Entire home/apt
5000
2
4
2018-01-03
0.18
1
0
2698
1448703
Beautiful 1 Bedroom in Nolita/Soho
Jessica
Manhattan
Nolita
40.72193
-73.99379
Entire home/apt
5000
1
2
2013-09-28
0.03
1
365
43009
33397385
Midtown Manhattan great location (Gramacy park)
Debra
Manhattan
Midtown
40.74482
-73.98367
Entire home/apt
5100
30
1
2019-06-22
1.00
2
343
3537
2110145
UWS 1BR w/backyard + block from CP
Jay And Liz
Manhattan
Upper West Side
40.77782
-73.97848
Entire home/apt
6000
14
17
2015-02-17
0.27
1
359
45666
34895693
Gem of east Flatbush
Sandra
Brooklyn
East Flatbush
40.65724
-73.92450
Private room
7500
1
8
2019-07-07
6.15
2
179
4377
2953058
Film Location
Jessica
Brooklyn
Clinton Hill
40.69137
-73.96723
Entire home/apt
8000
1
1
2016-09-15
0.03
11
365
30268
23377410
Beautiful/Spacious 1 bed luxury flat-TriBeCa/Soho
Rum
Manhattan
Tribeca
40.72197
-74.00633
Entire home/apt
8500
30
2
2018-09-18
0.18
1
251
12342
9528920
Quiet, Clean, Lit @ LES & Chinatown
Amy
Manhattan
Lower East Side
40.71355
-73.98507
Private room
9999
99
6
2016-01-01
0.14
1
83
6530
4737930
Spanish Harlem Apt
Olson
Manhattan
East Harlem
40.79264
-73.93898
Entire home/apt
9999
5
1
2015-01-02
0.02
1
0
9151
7003697
Furnished room in Astoria apartment
Kathrine
Queens
Astoria
40.76810
-73.91651
Private room
10000
100
2
2016-02-13
0.04
1
0
17692
13894339
Luxury 1 bedroom apt. -stunning Manhattan views
Erin
Brooklyn
Greenpoint
40.73260
-73.95739
Entire home/apt
10000
5
5
2017-07-27
0.16
1
0
13372 rows × 15 columns

E) Only show listings that have more than the average reviews and that are available for more than 10 nights (column “minimum_nights”). Sort the dataframe by reviews in a descending order.
In [95]:
df[(df[“number_of_reviews”]>df[“number_of_reviews”].mean())&(df[“minimum_nights”]>10)].sort_values(by=”number_of_reviews”, ascending = True)
Out[95]:

id
listing_name
host_name
neighbourhood_group
neighbourhood
latitude
longitude
room_type
price
minimum_nights
number_of_reviews
last_review
reviews_per_month
calculated_host_listings_count
availability_365
33690
26690586
Ionic Room close to NYC
Iris
Brooklyn
Bedford-Stuyvesant
40.68746
-73.95379
Private room
65
30
30
2019-05-31
2.71
2
361
31037
24080143
Beautiful Private Room near Highline, and Train
Colin
Manhattan
Chelsea
40.74648
-74.00266
Private room
89
30
30
2019-06-27
2.73
1
105
2970
1729286
Sunny, Large (Queen Bed) Private room in 2Bedroom
Helga
Manhattan
East Harlem
40.79377
-73.93610
Private room
59
14
30
2019-06-04
0.43
2
36
7697
5816130
AWESOME SUNNY ROOM C IN A CHARMING AREA!
Katie Graham
Brooklyn
Bedford-Stuyvesant
40.69307
-73.96073
Private room
90
30
30
2019-03-30
0.58
6
338
7355
5431845
Beautiful Fully Furnished 1 bed/bth
Aliya
Queens
Long Island City
40.75104
-73.93863
Entire home/apt
134
500
30
2018-06-24
0.57
1
90
296
68735
Prewar Penthouse w Private Terrace
Violetta
Manhattan
Upper West Side
40.78971
-73.97290
Entire home/apt
195
11
30
2019-06-13
0.32
1
249
1577
715102
WILLIAMSBURG 1 private room in loft
Florencia
Brooklyn
Williamsburg
40.71055
-73.94915
Private room
65
14
30
2018-09-02
0.76
2
38
16903
13459841
Sunny Williamsburg w Balcony – Long Term Rental
Jesse
Brooklyn
Williamsburg
40.71216
-73.95853
Entire home/apt
105
15
30
2019-05-26
0.84
1
85
687
255476
The BLUE OWL:\nVEGETARIAN WBURG W PATIO & BACK…
Bree
Brooklyn
Williamsburg
40.71160
-73.95290
Private room
89
30
30
2019-05-31
0.80
1
91
188
47362
LARGE, COMFY 1BDR W/CHARACTER!!!
Robert
Brooklyn
Bedford-Stuyvesant
40.68237
-73.94150
Entire home/apt
175
26
30
2012-09-03
0.29
1
364
1321
578267
*Lovely Bedroom! Big Sunny Apt*Manhattan*
Edel
Manhattan
Chinatown
40.71328
-73.99315
Private room
96
19
30
2019-04-08
0.36
1
165
496
174966
Luxury 2Bed/2.5Bath Central Park View
Henry
Manhattan
Upper West Side
40.77350
-73.98697
Entire home/apt
2000
30
30
2018-05-05
0.33
11
0
4275
2830518
Safe Clean Sunny, 15 min Manhattan
Delia
Queens
Astoria
40.76123
-73.92177
Private room
65
50
30
2015-11-15
0.48
1
198
34968
27723975
Brooklyn Private Room 20 min to Manhattan
Dee Daisy And Peter
Brooklyn
Bedford-Stuyvesant
40.69088
-73.95018
Private room
65
30
30
2019-06-04
2.84
4
365
1152
490278
Lovely 1BR – Midtown East by metro!
A.B.
Manhattan
Midtown
40.75632
-73.96464
Entire home/apt
99
14
30
2018-03-02
0.35
1
317
98
21794
COZY QUIET room 4 DOOGLERS!
Olan
Manhattan
Chelsea
40.74008
-74.00271
Private room
98
30
30
2019-05-01
0.27
2
364
10746
8270710
Gorgeous sunny apt steps to park!
Eric And Aoife
Brooklyn
Windsor Terrace
40.65106
-73.97704
Entire home/apt
99
20
30
2017-06-11
0.65
2
65
2095
949713
Luxury Duplex Loft w. Private Roof & Amazing V…
Tal
Brooklyn
Gowanus
40.67450
-73.99582
Entire home/apt
400
30
30
2019-06-01
0.39
1
67
37013
29424138
*Location, Location* | Chelsea | West Village
Lee
Manhattan
West Village
40.73531
-74.00303
Entire home/apt
182
30
31
2019-07-06
3.88
2
12
469
165824
Lady only Curtain-divided room
Lucy
Queens
Sunnyside
40.74000
-73.91901
Private room
33
44
31
2019-05-01
0.32
3
161
21499
17193761
Ideal Cute Sunny Room
Daniel
Manhattan
Chinatown
40.71573
-73.99176
Private room
63
20
31
2018-09-23
1.09
5
218
11154
8634515
★ Hidden Gem. Spacious. Restaurants, Cafés Gal…
Lei
Manhattan
Harlem
40.80449
-73.94743
Entire home/apt
130
31
31
2019-05-27
0.69
1
66
36314
28895817
SUNNY ROOM WITH SKYLIGHT + 2 BEDS
Emily Wes
Brooklyn
Bedford-Stuyvesant
40.69524
-73.95322
Private room
67
30
31
2019-06-16
3.41
6
333
559
206772
Williamsburg Exposed Brick Loft
Evan
Brooklyn
Williamsburg
40.71413
-73.96596
Entire home/apt
195
14
31
2019-05-26
0.33
1
263
7779
5902510
Clean & Cozy room in Great Location
Jill
Manhattan
Hell’s Kitchen
40.76799
-73.98644
Private room
61
12
31
2018-03-19
0.60
4
102
20288
16221732
A Comfortable, Cozy Room
Mei
Queens
Bayside
40.75465
-73.77361
Private room
30
15
31
2019-05-06
0.99
2
142
315
74404
Luxury 3 bed/ 2 bath apt in Harlem w/ terrace
G & S
Manhattan
Harlem
40.80276
-73.95670
Entire home/apt
250
14
31
2012-08-22
0.31
1
78
6499
4720460
Charming Upper West Side Studio
Anna
Manhattan
Upper West Side
40.78657
-73.97161
Entire home/apt
150
30
31
2019-05-21
0.59
1
213
6413
4667148
1 Bdrm in Greenwich Village
Marie-Claire
Manhattan
West Village
40.73317
-74.00118
Entire home/apt
125
30
31
2019-05-12
0.83
2
346
37740
29904478
Cozy room in the heart of Bushwick
Stella
Brooklyn
Bushwick
40.69276
-73.91465
Private room
35
30
31
2019-07-07
3.94
3
316
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
14517
11438189
Everything you need in NYC! 3BR
Remo
Brooklyn
Bedford-Stuyvesant
40.67834
-73.92442
Entire home/apt
135
30
155
2019-05-21
3.97
1
281
23969
19329734
Large Top Floor Apartment
Luka
Manhattan
Kips Bay
40.74063
-73.98205
Entire home/apt
325
30
155
2019-06-19
6.22
1
240
813
289665
Decorators 5-Star Flat West Village
Elle
Manhattan
West Village
40.72891
-74.00293
Entire home/apt
450
20
157
2016-08-11
1.71
1
0
6116
4479632
Charming duplex garden flat in SoHo
Richard
Manhattan
SoHo
40.72736
-74.00192
Entire home/apt
495
30
159
2019-06-12
2.83
1
129
9279
7091125
Flatiron Studio Loft, Elevator Bldg
Ryan
Manhattan
Flatiron District
40.74138
-73.98814
Entire home/apt
299
28
160
2019-06-22
3.39
1
315
106
25235
Large 2 Bedroom Great for Groups!
Shelly
Brooklyn
Bedford-Stuyvesant
40.68373
-73.92377
Entire home/apt
125
90
162
2019-06-28
1.46
2
137
6898
4932139
Luxury in Best Location
Stacey
Manhattan
Nolita
40.72139
-73.99688
Entire home/apt
346
198
163
2018-11-21
2.96
1
0
7942
6094046
Central Times Square w prvte bath
Troy
Manhattan
Theater District
40.75863
-73.98472
Private room
105
90
163
2019-06-08
3.21
5
316
2671
1426529
Comfy room in vibrant East Village
Brooke & Terry
Manhattan
East Village
40.72418
-73.98283
Private room
95
14
172
2019-05-25
2.39
1
14
2801
1549413
Beautiful Studio in Heart of Harlem
Brinton
Manhattan
Harlem
40.80330
-73.95008
Entire home/apt
135
31
177
2019-06-06
2.49
4
340
3953
2461439
Pristine Lower East Side Sanctuary
Sausan
Manhattan
Lower East Side
40.72007
-73.98946
Entire home/apt
133
14
177
2019-05-03
2.82
2
221
2978
1734724
Williamsburg Luxury- 30 days +
Lane
Brooklyn
Williamsburg
40.72076
-73.95614
Entire home/apt
180
30
183
2017-12-17
2.63
1
0
235
60611
SpaHa Studio Monthly Rental
Blanca
Manhattan
East Harlem
40.79163
-73.94573
Entire home/apt
125
28
183
2018-09-29
1.83
2
365
1172
501082
Private Bdrm & Bath-30-night min-Weekly Maid S…
Joyce
Manhattan
Harlem
40.82392
-73.94205
Private room
79
30
185
2019-06-07
2.15
1
302
470
166006
Nice Manhattan Apt Near Central Park and Subway
Giancarlo
Manhattan
Upper West Side
40.80006
-73.96049
Entire home/apt
250
31
188
2018-08-23
1.94
1
259
2137
983625
Great Studio in the heart of Harlem
Brinton
Manhattan
Harlem
40.80421
-73.94302
Entire home/apt
135
30
190
2019-06-04
2.63
4
253
5640
4109246
Calm Bushwick Room–Next to subway!
Carrie-Anne
Brooklyn
Bushwick
40.70569
-73.92268
Private room
40
28
191
2019-05-15
3.36
2
95
65
16421
Your Heaven in Hells Kitchen
Mark
Manhattan
Hell’s Kitchen
40.75979
-73.99119
Private room
52
30
191
2019-05-16
1.65
1
191
6244
4564292
HEART of NEW YORK // ﾆｭｰﾖｰｸの中心
Marcello
Manhattan
Hell’s Kitchen
40.75811
-73.99073
Entire home/apt
107
21
193
2017-10-27
3.42
1
310
9949
7657879
Beautiful GRDN Apt Chelsea Meatpacking Min 30 …
Vanessa
Manhattan
Chelsea
40.74376
-74.00387
Entire home/apt
270
30
195
2019-03-17
4.26
1
270
1318
577324
Stylish 1BR Apartment Quick to Midtown and LGA!
Dustin
Queens
Ditmars Steinway
40.76961
-73.90451
Entire home/apt
54
45
198
2019-06-15
2.33
1
80
12718
9740010
PRIVATE STUDIO: L TRAIN WATERFRONT
Maria
Brooklyn
Williamsburg
40.71790
-73.95938
Entire home/apt
146
28
208
2019-05-26
4.80
2
200
2244
1063020
Private Brownstone
Amy
Manhattan
East Harlem
40.79417
-73.94376
Entire home/apt
110
31
235
2019-05-31
3.14
2
84
643
243708
Williamsburg – Quiet and Comfy stay
Andy
Brooklyn
Williamsburg
40.70875
-73.95508
Entire home/apt
199
30
242
2019-04-01
2.59
1
102
563
209310
Sunnyside NYC/ AC room/ city views/ near Midtown
Iulia
Queens
Sunnyside
40.73826
-73.92458
Private room
50
28
258
2019-05-28
2.90
1
287
6375
4644632
Brooklyn Life, Easy to Manhattan (30+ Days only)
Joni
Brooklyn
Bedford-Stuyvesant
40.68797
-73.94696
Entire home/apt
130
200
271
2019-07-03
4.84
2
64
7984
6141401
Large Alcove Studio
Jon
Manhattan
Kips Bay
40.74257
-73.98207
Entire home/apt
225
30
276
2019-06-21
5.54
1
338
1242
531091
King size bedroom in 2 bed apartment
Dragan
Queens
Astoria
40.75532
-73.91603
Private room
109
30
304
2019-06-19
3.70
1
0
3605
2154075
Brooklyn Charm, Close to Manhattan (30+ Days O…
Joni
Brooklyn
Bedford-Stuyvesant
40.68640
-73.94529
Entire home/apt
112
200
314
2019-06-20
4.82
2
42
113
26785
Park Slope Green Guest House
Dani
Brooklyn
South Slope
40.66853
-73.98912
Entire home/apt
120
30
467
2018-12-30
4.22
2
192
469 rows × 15 columns

F) Display the number of listings per neighbourhood and only display those neighbourhoods with more than 1000 listings.
In [96]:
df[“listing”]=1
#vgs.head()
df1=df.pivot_table(index=[“neighbourhood”], values=[“listing”], aggfunc=np.sum)
df1[df1[“listing”]>1000]
Out[96]:

listing
neighbourhood

Bedford-Stuyvesant
3141
Bushwick
1942
Crown Heights
1265
East Village
1489
Harlem
2204
Hell’s Kitchen
1528
Upper East Side
1405
Upper West Side
1482
Williamsburg
3163

G) Display the number of the different types of rooms (column “room_type”) per neighbourhood.
In [97]:
df2=df.pivot_table(index=[“neighbourhood”,”room_type”], values=[“listing”], aggfunc=np.sum)
df2
Out[97]:

listing
neighbourhood
room_type

Allerton
Entire home/apt
15
Private room
22
Arden Heights
Entire home/apt
3
Private room
1
Arrochar
Entire home/apt
10
Private room
10
Arverne
Entire home/apt
41
Private room
25
Astoria
Entire home/apt
304
Private room
386
Shared room
19
Bath Beach
Entire home/apt
7
Private room
8
Battery Park City
Entire home/apt
23
Private room
12
Shared room
1
Bay Ridge
Entire home/apt
55
Private room
55
Shared room
5
Bay Terrace
Entire home/apt
2
Private room
2
Shared room
1
Bay Terrace, Staten Island
Entire home/apt
2
Baychester
Entire home/apt
3
Private room
3
Bayside
Entire home/apt
12
Private room
19
Bayswater
Entire home/apt
4
Private room
7
Bedford-Stuyvesant
Entire home/apt
1438
…
…
…
West Brighton
Entire home/apt
4
Private room
12
West Farms
Entire home/apt
1
Private room
1
West Village
Entire home/apt
522
Private room
85
Shared room
3
Westchester Square
Entire home/apt
4
Private room
5
Westerleigh
Entire home/apt
1
Private room
1
Whitestone
Entire home/apt
3
Private room
5
Williamsbridge
Entire home/apt
17
Private room
17
Williamsburg
Entire home/apt
1569
Private room
1573
Shared room
21
Willowbrook
Entire home/apt
1
Windsor Terrace
Entire home/apt
94
Private room
34
Woodhaven
Entire home/apt
14
Private room
56
Shared room
2
Woodlawn
Entire home/apt
4
Private room
6
Shared room
1
Woodside
Entire home/apt
54
Private room
115
Shared room
1
518 rows × 1 columns

H) Replace the values within “room_type” with numerical values, i.e. 0 for “private room”, 1 for “Entire home/apt”, and 2 for “Shared room”.

Save the new dataframe again under the name “df”.
In [98]:
df.replace({“Private room”:”0″,”Entire home/apt”:”1″,”Shared room”:”2″}, inplace=True)
df
Out[98]:

id
listing_name
host_name
neighbourhood_group
neighbourhood
latitude
longitude
room_type
price
minimum_nights
number_of_reviews
last_review
reviews_per_month
calculated_host_listings_count
availability_365
listing
0
2539
Clean & quiet apt home by the park
John
Brooklyn
Kensington
40.64749
-73.97237
0
149
1
9
2018-10-19
0.21
6
365
1
1
2595
Skylit Midtown Castle
Jennifer
Manhattan
Midtown
40.75362
-73.98377
1
225
1
45
2019-05-21
0.38
2
355
1
3
3831
Cozy Entire Floor of Brownstone
LisaRoxanne
Brooklyn
Clinton Hill
40.68514
-73.95976
1
89
1
270
2019-07-05
4.64
1
194
1
4
5022
Entire Apt: Spacious Studio/Loft by central park
Laura
Manhattan
East Harlem
40.79851
-73.94399
1
80
10
9
2018-11-19
0.10
1
0
1
5
5099
Large Cozy 1 BR Apartment In Midtown East
Chris
Manhattan
Murray Hill
40.74767
-73.97500
1
200
3
74
2019-06-22
0.59
1
129
1
6
5121
BlissArtsSpace!
Garon
Brooklyn
Bedford-Stuyvesant
40.68688
-73.95596
0
60
45
49
2017-10-05
0.40
1
0
1
7
5178
Large Furnished Room Near B’way
Shunichi
Manhattan
Hell’s Kitchen
40.76489
-73.98493
0
79
2
430
2019-06-24
3.47
1
220
1
8
5203
Cozy Clean Guest Room – Family Apt
MaryEllen
Manhattan
Upper West Side
40.80178
-73.96723
0
79
2
118
2017-07-21
0.99
1
0
1
9
5238
Cute & Cozy Lower East Side 1 bdrm
Ben
Manhattan
Chinatown
40.71344
-73.99037
1
150
1
160
2019-06-09
1.33
4
188
1
10
5295
Beautiful 1br on Upper West Side
Lena
Manhattan
Upper West Side
40.80316
-73.96545
1
135
5
53
2019-06-22
0.43
1
6
1
11
5441
Central Manhattan/near Broadway
Kate
Manhattan
Hell’s Kitchen
40.76076
-73.98867
0
85
2
188
2019-06-23
1.50
1
39
1
12
5803
Lovely Room 1, Garden, Best Area, Legal rental
Laurie
Brooklyn
South Slope
40.66829
-73.98779
0
89
4
167
2019-06-24
1.34
3
314
1
13
6021
Wonderful Guest Bedroom in Manhattan for SINGLES
Claudio
Manhattan
Upper West Side
40.79826
-73.96113
0
85
2
113
2019-07-05
0.91
1
333
1
14
6090
West Village Nest – Superhost
Alina
Manhattan
West Village
40.73530
-74.00525
1
120
90
27
2018-10-31
0.22
1
0
1
15
6848
Only 2 stops to Manhattan studio
Allen & Irina
Brooklyn
Williamsburg
40.70837
-73.95352
1
140
2
148
2019-06-29
1.20
1
46
1
16
7097
Perfect for Your Parents + Garden
Jane
Brooklyn
Fort Greene
40.69169
-73.97185
1
215
2
198
2019-06-28
1.72
1
321
1
17
7322
Chelsea Perfect
Doti
Manhattan
Chelsea
40.74192
-73.99501
0
140
1
260
2019-07-01
2.12
1
12
1
18
7726
Hip Historic Brownstone Apartment with Backyard
Adam And Charity
Brooklyn
Crown Heights
40.67592
-73.94694
1
99
3
53
2019-06-22
4.44
1
21
1
20
7801
Sweet and Spacious Brooklyn Loft
Chaya
Brooklyn
Williamsburg
40.71842
-73.95718
1
299
3
9
2011-12-28
0.07
1
0
1
21
8024
CBG CtyBGd HelpsHaiti rm#1:1-4
Lisel
Brooklyn
Park Slope
40.68069
-73.97706
0
130
2
130
2019-07-01
1.09
6
347
1
22
8025
CBG Helps Haiti Room#2.5
Lisel
Brooklyn
Park Slope
40.67989
-73.97798
0
80
1
39
2019-01-01
0.37
6
364
1
23
8110
CBG Helps Haiti Rm #2
Lisel
Brooklyn
Park Slope
40.68001
-73.97865
0
110
2
71
2019-07-02
0.61
6
304
1
24
8490
MAISON DES SIRENES1,bohemian apartment
Nathalie
Brooklyn
Bedford-Stuyvesant
40.68371
-73.94028
1
120
2
88
2019-06-19
0.73
2
233
1
25
8505
Sunny Bedroom Across Prospect Park
Gregory
Brooklyn
Windsor Terrace
40.65599
-73.97519
0
60
1
19
2019-06-23
1.37
2
85
1
27
9357
Midtown Pied-a-terre
Tommi
Manhattan
Hell’s Kitchen
40.76715
-73.98533
1
150
10
58
2017-08-13
0.49
1
75
1
28
9518
SPACIOUS, LOVELY FURNISHED MANHATTAN BEDROOM
Shon
Manhattan
Inwood
40.86482
-73.92106
0
44
3
108
2019-06-15
1.11
3
311
1
29
9657
Modern 1 BR / NYC / EAST VILLAGE
Dana
Manhattan
East Village
40.72920
-73.98542
1
180
14
29
2019-04-19
0.24
1
67
1
30
9668
front room/double bed
Ssameer Or Trip
Manhattan
Harlem
40.82245
-73.95104
0
50
3
242
2019-06-01
2.04
3
355
1
31
9704
Spacious 1 bedroom in luxe building
Teri
Manhattan
Harlem
40.81305
-73.95466
0
52
2
88
2019-06-14
1.42
1
255
1
32
9782
Loft in Williamsburg Area w/ Roof
Andrea
Brooklyn
Greenpoint
40.72219
-73.93762
0
55
4
197
2019-06-15
1.65
3
284
1
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
48387
36233091
1 room in bushwick
Emily
Brooklyn
Bushwick
40.70384
-73.92232
0
40
2
1
2019-07-03
1.00
1
16
1
48391
36235150
Sky View beautiful apt in Williamsburg New York
Melki
Brooklyn
Williamsburg
40.71232
-73.94220
1
170
1
1
2019-07-07
1.00
3
340
1
48392
36235154
Top on Manhattan
Jason
Manhattan
Harlem
40.80658
-73.95736
0
138
3
1
2019-07-03
1.00
2
65
1
48394
36236013
READY BED GO the goal is to make u feel at home
Emanuel
Brooklyn
Cypress Hills
40.68042
-73.88978
0
75
1
1
2019-07-05
1.00
1
82
1
48401
36237529
Beautiful Cozy Room, 20mn to FREE ferry
Marouene
Staten Island
Rosebank
40.60750
-74.07979
0
65
1
1
2019-07-05
1.00
1
179
1
48404
36238485
Chic Room in Williamsburg + Backyard +Washer D…
Ryan
Brooklyn
Williamsburg
40.71773
-73.94159
0
91
2
1
2019-07-04
1.00
1
344
1
48409
36241283
Enjoy your friendly stay at my place
Lana
Brooklyn
Midwood
40.62555
-73.95724
0
50
1
1
2019-07-02
1.00
1
86
1
48410
36241782
Classic Brooklyn Loft (East Williamsburg/Bushw…
Ap
Brooklyn
Williamsburg
40.70518
-73.93794
0
100
1
1
2019-07-06
1.00
1
1
1
48413
36242888
Upper East Site Luxury 2 Bed rooms
Jean-Nicolas
Manhattan
Upper East Side
40.76996
-73.95117
0
220
4
1
2019-07-03
1.00
1
15
1
48453
36272851
Private Bedroom in a Prewar Gramercy Building!*
Thikshan
Manhattan
Kips Bay
40.73929
-73.98183
0
135
2
1
2019-07-07
1.00
3
41
1
48454
36273046
Elegant Spacious UES private room
Celine
Manhattan
Upper East Side
40.77551
-73.95404
0
120
1
1
2019-07-06
1.00
2
358
1
48457
36274037
Cozy Wall Street Studio
Schmid
Manhattan
Financial District
40.70583
-74.01038
1
170
1
1
2019-07-06
1.00
1
323
1
48486
36280646
Cable and wfi, L/G included.
Chris
Queens
Forest Hills
40.73657
-73.85088
1
16
9
1
2019-07-07
1.00
1
322
1
48524
36308600
Cozy and Beautiful Park Slope One Bedroom
Kailey
Brooklyn
Park Slope
40.67455
-73.98477
1
150
1
1
2019-07-05
1.00
1
8
1
48526
36309284
Private Room Near JFK, St John’s Hospital, & B…
Phoenix
Queens
Bayswater
40.60804
-73.75829
0
45
3
1
2019-07-04
1.00
2
90
1
48532
36310268
Full size room
Alejandro
Queens
Maspeth
40.71327
-73.90982
0
65
2
1
2019-07-06
1.00
1
21
1
48534
36310855
Room 3: Cozy Room w Large Closet & Light Break…
Rehana
Brooklyn
Bedford-Stuyvesant
40.68969
-73.93285
0
68
1
1
2019-07-06
1.00
3
127
1
48576
36318560
Luxury Sun-filled Private Room near Time Square
Linda Lou
Manhattan
Midtown
40.75286
-73.99297
0
120
2
1
2019-07-06
1.00
1
7
1
48601
36335990
Sunny Studio steps from Wall Street
Farina
Manhattan
Financial District
40.70603
-74.01084
1
75
1
1
2019-07-03
1.00
1
181
1
48615
36344079
The Little Castle in Astoria
Prince
Queens
Astoria
40.76887
-73.91128
0
150
1
1
2019-07-04
1.00
1
165
1
48634
36351128
One bedroom without roomies, close to everything
David & Amy
Manhattan
Upper West Side
40.80281
-73.96550
1
110
3
2
2019-07-05
2.00
1
15
1
48636
36351543
2-MONTH SUBLEASE (WITH EARLY MOVE-IN)
Friday
Brooklyn
Bedford-Stuyvesant
40.68914
-73.92408
0
33
30
2
2019-07-06
2.00
1
87
1
48701
36390226
Comfortable clean Bedstuy private room
Angela
Brooklyn
Bedford-Stuyvesant
40.69551
-73.93951
0
45
1
2
2019-07-08
2.00
1
14
1
48732
36411407
Brand new 1 bedroom steps from Soho!
Mike
Manhattan
Lower East Side
40.71825
-73.99019
1
150
4
1
2019-07-06
1.00
1
13
1
48740
36413632
Spacious 2BR in Beautiful Brooklyn Heights
Matt
Brooklyn
Brooklyn Heights
40.69827
-73.99649
1
550
3
1
2019-07-07
1.00
1
230
1
48782
36425863
Lovely Privet Bedroom with Privet Restroom
Rusaa
Manhattan
Upper East Side
40.78099
-73.95366
0
129
1
1
2019-07-07
1.00
1
147
1
48790
36427429
No.2 with queen size bed
H Ai
Queens
Flushing
40.75104
-73.81459
0
45
1
1
2019-07-07
1.00
6
339
1
48799
36438336
Seas The Moment
Ben
Staten Island
Great Kills
40.54179
-74.14275
0
235
1
1
2019-07-07
1.00
1
87
1
48805
36442252
1B-1B apartment near by Metro
Blaine
Bronx
Mott Haven
40.80787
-73.92400
1
100
1
2
2019-07-07
2.00
1
40
1
48852
36455809
Cozy Private Room in Bushwick, Brooklyn
Christine
Brooklyn
Bushwick
40.69805
-73.92801
0
30
1
1
2019-07-08
1.00
1
1
1
38821 rows × 16 columns

I) Display a bar chart using the distplot (from the seaborn library) that shows the neighbourhood on the x-axis and the number of listings on the y-axis.
In [99]:
sns.factorplot(“neighbourhood”, data=df1, aspect=2, kind=”count”, color=’steelblue’)

//anaconda3/lib/python3.7/site-packages/seaborn/categorical.py:3666: UserWarning: The `factorplot` function has been renamed to `catplot`. The original name will be removed in a future release. Please update your code. Note that the default `kind` in `factorplot` (`’point’`) has changed `’strip’` in `catplot`.
warnings.warn(msg)

—————————————————————————
ValueError Traceback (most recent call last)
in
—-> 1 sns.factorplot(“neighbourhood”, data=df1, aspect=2, kind=”count”, color=’steelblue’)

//anaconda3/lib/python3.7/site-packages/seaborn/categorical.py in factorplot(*args, **kwargs)
3674 kwargs.setdefault(“kind”, “point”)
3675
-> 3676 return catplot(*args, **kwargs)
3677
3678

//anaconda3/lib/python3.7/site-packages/seaborn/categorical.py in catplot(x, y, hue, data, row, col, col_wrap, estimator, ci, n_boot, units, order, hue_order, row_order, col_order, kind, height, aspect, orient, color, palette, legend, legend_out, sharex, sharey, margin_titles, facet_kws, **kwargs)
3714 # facets to ensure representation of all data in the final plot
3715 p = _CategoricalPlotter()
-> 3716 p.establish_variables(x_, y_, hue, data, orient, order, hue_order)
3717 order = p.group_names
3718 hue_order = p.hue_names

//anaconda3/lib/python3.7/site-packages/seaborn/categorical.py in establish_variables(self, x, y, hue, data, orient, order, hue_order, units)
153 if isinstance(input, string_types):
154 err = “Could not interpret input ‘{}'”.format(input)
–> 155 raise ValueError(err)
156
157 # Figure out the plotting orientation

ValueError: Could not interpret input ‘neighbourhood’

J) Scale the column “price” and the column “number_of_reviews” using a method that seems appropriate in your opinion.

Save the new dataframe again under the name “df”.
In [100]:
df[“price”]=df[“price”]*5
df[“number_of_reviews”]=df[“number_of_reviews”]*10
df
Out[100]:

id
listing_name
host_name
neighbourhood_group
neighbourhood
latitude
longitude
room_type
price
minimum_nights
number_of_reviews
last_review
reviews_per_month
calculated_host_listings_count
availability_365
listing
0
2539
Clean & quiet apt home by the park
John
Brooklyn
Kensington
40.64749
-73.97237
0
745
1
90
2018-10-19
0.21
6
365
1
1
2595
Skylit Midtown Castle
Jennifer
Manhattan
Midtown
40.75362
-73.98377
1
1125
1
450
2019-05-21
0.38
2
355
1
3
3831
Cozy Entire Floor of Brownstone
LisaRoxanne
Brooklyn
Clinton Hill
40.68514
-73.95976
1
445
1
2700
2019-07-05
4.64
1
194
1
4
5022
Entire Apt: Spacious Studio/Loft by central park
Laura
Manhattan
East Harlem
40.79851
-73.94399
1
400
10
90
2018-11-19
0.10
1
0
1
5
5099
Large Cozy 1 BR Apartment In Midtown East
Chris
Manhattan
Murray Hill
40.74767
-73.97500
1
1000
3
740
2019-06-22
0.59
1
129
1
6
5121
BlissArtsSpace!
Garon
Brooklyn
Bedford-Stuyvesant
40.68688
-73.95596
0
300
45
490
2017-10-05
0.40
1
0
1
7
5178
Large Furnished Room Near B’way
Shunichi
Manhattan
Hell’s Kitchen
40.76489
-73.98493
0
395
2
4300
2019-06-24
3.47
1
220
1
8
5203
Cozy Clean Guest Room – Family Apt
MaryEllen
Manhattan
Upper West Side
40.80178
-73.96723
0
395
2
1180
2017-07-21
0.99
1
0
1
9
5238
Cute & Cozy Lower East Side 1 bdrm
Ben
Manhattan
Chinatown
40.71344
-73.99037
1
750
1
1600
2019-06-09
1.33
4
188
1
10
5295
Beautiful 1br on Upper West Side
Lena
Manhattan
Upper West Side
40.80316
-73.96545
1
675
5
530
2019-06-22
0.43
1
6
1
11
5441
Central Manhattan/near Broadway
Kate
Manhattan
Hell’s Kitchen
40.76076
-73.98867
0
425
2
1880
2019-06-23
1.50
1
39
1
12
5803
Lovely Room 1, Garden, Best Area, Legal rental
Laurie
Brooklyn
South Slope
40.66829
-73.98779
0
445
4
1670
2019-06-24
1.34
3
314
1
13
6021
Wonderful Guest Bedroom in Manhattan for SINGLES
Claudio
Manhattan
Upper West Side
40.79826
-73.96113
0
425
2
1130
2019-07-05
0.91
1
333
1
14
6090
West Village Nest – Superhost
Alina
Manhattan
West Village
40.73530
-74.00525
1
600
90
270
2018-10-31
0.22
1
0
1
15
6848
Only 2 stops to Manhattan studio
Allen & Irina
Brooklyn
Williamsburg
40.70837
-73.95352
1
700
2
1480
2019-06-29
1.20
1
46
1
16
7097
Perfect for Your Parents + Garden
Jane
Brooklyn
Fort Greene
40.69169
-73.97185
1
1075
2
1980
2019-06-28
1.72
1
321
1
17
7322
Chelsea Perfect
Doti
Manhattan
Chelsea
40.74192
-73.99501
0
700
1
2600
2019-07-01
2.12
1
12
1
18
7726
Hip Historic Brownstone Apartment with Backyard
Adam And Charity
Brooklyn
Crown Heights
40.67592
-73.94694
1
495
3
530
2019-06-22
4.44
1
21
1
20
7801
Sweet and Spacious Brooklyn Loft
Chaya
Brooklyn
Williamsburg
40.71842
-73.95718
1
1495
3
90
2011-12-28
0.07
1
0
1
21
8024
CBG CtyBGd HelpsHaiti rm#1:1-4
Lisel
Brooklyn
Park Slope
40.68069
-73.97706
0
650
2
1300
2019-07-01
1.09
6
347
1
22
8025
CBG Helps Haiti Room#2.5
Lisel
Brooklyn
Park Slope
40.67989
-73.97798
0
400
1
390
2019-01-01
0.37
6
364
1
23
8110
CBG Helps Haiti Rm #2
Lisel
Brooklyn
Park Slope
40.68001
-73.97865
0
550
2
710
2019-07-02
0.61
6
304
1
24
8490
MAISON DES SIRENES1,bohemian apartment
Nathalie
Brooklyn
Bedford-Stuyvesant
40.68371
-73.94028
1
600
2
880
2019-06-19
0.73
2
233
1
25
8505
Sunny Bedroom Across Prospect Park
Gregory
Brooklyn
Windsor Terrace
40.65599
-73.97519
0
300
1
190
2019-06-23
1.37
2
85
1
27
9357
Midtown Pied-a-terre
Tommi
Manhattan
Hell’s Kitchen
40.76715
-73.98533
1
750
10
580
2017-08-13
0.49
1
75
1
28
9518
SPACIOUS, LOVELY FURNISHED MANHATTAN BEDROOM
Shon
Manhattan
Inwood
40.86482
-73.92106
0
220
3
1080
2019-06-15
1.11
3
311
1
29
9657
Modern 1 BR / NYC / EAST VILLAGE
Dana
Manhattan
East Village
40.72920
-73.98542
1
900
14
290
2019-04-19
0.24
1
67
1
30
9668
front room/double bed
Ssameer Or Trip
Manhattan
Harlem
40.82245
-73.95104
0
250
3
2420
2019-06-01
2.04
3
355
1
31
9704
Spacious 1 bedroom in luxe building
Teri
Manhattan
Harlem
40.81305
-73.95466
0
260
2
880
2019-06-14
1.42
1
255
1
32
9782
Loft in Williamsburg Area w/ Roof
Andrea
Brooklyn
Greenpoint
40.72219
-73.93762
0
275
4
1970
2019-06-15
1.65
3
284
1
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
48387
36233091
1 room in bushwick
Emily
Brooklyn
Bushwick
40.70384
-73.92232
0
200
2
10
2019-07-03
1.00
1
16
1
48391
36235150
Sky View beautiful apt in Williamsburg New York
Melki
Brooklyn
Williamsburg
40.71232
-73.94220
1
850
1
10
2019-07-07
1.00
3
340
1
48392
36235154
Top on Manhattan
Jason
Manhattan
Harlem
40.80658
-73.95736
0
690
3
10
2019-07-03
1.00
2
65
1
48394
36236013
READY BED GO the goal is to make u feel at home
Emanuel
Brooklyn
Cypress Hills
40.68042
-73.88978
0
375
1
10
2019-07-05
1.00
1
82
1
48401
36237529
Beautiful Cozy Room, 20mn to FREE ferry
Marouene
Staten Island
Rosebank
40.60750
-74.07979
0
325
1
10
2019-07-05
1.00
1
179
1
48404
36238485
Chic Room in Williamsburg + Backyard +Washer D…
Ryan
Brooklyn
Williamsburg
40.71773
-73.94159
0
455
2
10
2019-07-04
1.00
1
344
1
48409
36241283
Enjoy your friendly stay at my place
Lana
Brooklyn
Midwood
40.62555
-73.95724
0
250
1
10
2019-07-02
1.00
1
86
1
48410
36241782
Classic Brooklyn Loft (East Williamsburg/Bushw…
Ap
Brooklyn
Williamsburg
40.70518
-73.93794
0
500
1
10
2019-07-06
1.00
1
1
1
48413
36242888
Upper East Site Luxury 2 Bed rooms
Jean-Nicolas
Manhattan
Upper East Side
40.76996
-73.95117
0
1100
4
10
2019-07-03
1.00
1
15
1
48453
36272851
Private Bedroom in a Prewar Gramercy Building!*
Thikshan
Manhattan
Kips Bay
40.73929
-73.98183
0
675
2
10
2019-07-07
1.00
3
41
1
48454
36273046
Elegant Spacious UES private room
Celine
Manhattan
Upper East Side
40.77551
-73.95404
0
600
1
10
2019-07-06
1.00
2
358
1
48457
36274037
Cozy Wall Street Studio
Schmid
Manhattan
Financial District
40.70583
-74.01038
1
850
1
10
2019-07-06
1.00
1
323
1
48486
36280646
Cable and wfi, L/G included.
Chris
Queens
Forest Hills
40.73657
-73.85088
1
80
9
10
2019-07-07
1.00
1
322
1
48524
36308600
Cozy and Beautiful Park Slope One Bedroom
Kailey
Brooklyn
Park Slope
40.67455
-73.98477
1
750
1
10
2019-07-05
1.00
1
8
1
48526
36309284
Private Room Near JFK, St John’s Hospital, & B…
Phoenix
Queens
Bayswater
40.60804
-73.75829
0
225
3
10
2019-07-04
1.00
2
90
1
48532
36310268
Full size room
Alejandro
Queens
Maspeth
40.71327
-73.90982
0
325
2
10
2019-07-06
1.00
1
21
1
48534
36310855
Room 3: Cozy Room w Large Closet & Light Break…
Rehana
Brooklyn
Bedford-Stuyvesant
40.68969
-73.93285
0
340
1
10
2019-07-06
1.00
3
127
1
48576
36318560
Luxury Sun-filled Private Room near Time Square
Linda Lou
Manhattan
Midtown
40.75286
-73.99297
0
600
2
10
2019-07-06
1.00
1
7
1
48601
36335990
Sunny Studio steps from Wall Street
Farina
Manhattan
Financial District
40.70603
-74.01084
1
375
1
10
2019-07-03
1.00
1
181
1
48615
36344079
The Little Castle in Astoria
Prince
Queens
Astoria
40.76887
-73.91128
0
750
1
10
2019-07-04
1.00
1
165
1
48634
36351128
One bedroom without roomies, close to everything
David & Amy
Manhattan
Upper West Side
40.80281
-73.96550
1
550
3
20
2019-07-05
2.00
1
15
1
48636
36351543
2-MONTH SUBLEASE (WITH EARLY MOVE-IN)
Friday
Brooklyn
Bedford-Stuyvesant
40.68914
-73.92408
0
165
30
20
2019-07-06
2.00
1
87
1
48701
36390226
Comfortable clean Bedstuy private room
Angela
Brooklyn
Bedford-Stuyvesant
40.69551
-73.93951
0
225
1
20
2019-07-08
2.00
1
14
1
48732
36411407
Brand new 1 bedroom steps from Soho!
Mike
Manhattan
Lower East Side
40.71825
-73.99019
1
750
4
10
2019-07-06
1.00
1
13
1
48740
36413632
Spacious 2BR in Beautiful Brooklyn Heights
Matt
Brooklyn
Brooklyn Heights
40.69827
-73.99649
1
2750
3
10
2019-07-07
1.00
1
230
1
48782
36425863
Lovely Privet Bedroom with Privet Restroom
Rusaa
Manhattan
Upper East Side
40.78099
-73.95366
0
645
1
10
2019-07-07
1.00
1
147
1
48790
36427429
No.2 with queen size bed
H Ai
Queens
Flushing
40.75104
-73.81459
0
225
1
10
2019-07-07
1.00
6
339
1
48799
36438336
Seas The Moment
Ben
Staten Island
Great Kills
40.54179
-74.14275
0
1175
1
10
2019-07-07
1.00
1
87
1
48805
36442252
1B-1B apartment near by Metro
Blaine
Bronx
Mott Haven
40.80787
-73.92400
1
500
1
20
2019-07-07
2.00
1
40
1
48852
36455809
Cozy Private Room in Bushwick, Brooklyn
Christine
Brooklyn
Bushwick
40.69805
-73.92801
0
150
1
10
2019-07-08
1.00
1
1
1
38821 rows × 16 columns

3. Cleaning Data
Dataset used: “defaults.xls”
In [172]:
# Place all packages you need here
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from sklearn import datasets
from sklearn.ensemble import RandomForestClassifier

A) Load the dataset “defaults.xls” and save it under the name “defaults”. Display the datatypes of all columns of the dataset “defaults.xls”.
In [146]:
defaults = pd.read_excel(‘defaults.xls’)
#defaults.dtypes
defaults.info()
defaults.head

RangeIndex: 29601 entries, 0 to 29600
Data columns (total 7 columns):
ID 29601 non-null int64
CREDIT_LIMIT 29601 non-null int64
SEX 29601 non-null object
EDUCATION 29601 non-null object
MARRIAGE 29601 non-null object
AGE 29601 non-null int64
default 29601 non-null int64
dtypes: int64(4), object(3)
memory usage: 1.6+ MB
Out[146]:

B) Some column(s) might not be needed. Get rid of them. Explain why you get rid of the respective column(s).

Save the new dataframe again under the name “defaults”.
In [145]:
#delete the columns of ‘default’ because it only contains two values that not useful for analysis and its format is also not consistent with others, which are written in cpitals
#have no idea why “inplace=True” doesn’t work here, it doesn’t allow me to use inplace=true and use’ defaults=defaults.drop…’ to save the modified dataframe.so I save the new dataframe as ‘defaults1’
defaults1=defaults.drop(‘default’, axis=1)
defaults1
Out[145]:

ID
CREDIT_LIMIT
SEX
EDUCATION
MARRIAGE
AGE
0
1
20000
female
university
married
24
1
2
120000
female
university
single
26
2
3
90000
female
university
single
34
3
4
50000
female
university
married
37
4
5
50000
male
university
married
57
…
…
…
…
…
…
…
29596
29996
220000
male
high school
married
39
29597
29997
150000
male
high school
single
43
29598
29998
30000
male
university
single
37
29599
29999
80000
male
high school
married
41
29600
30000
50000
male
university
married
46
29601 rows × 6 columns

C) Convert all categorical values into dummy variables. Only include columns with valuable information.

Save the new dataframe again under the name “defaults”.
In [153]:
defaults1.head
defaults1=pd.get_dummies(defaults1)
defaults1
Out[153]:

ID
CREDIT_LIMIT
AGE
SEX_female
SEX_male
EDUCATION_graduate school
EDUCATION_high school
EDUCATION_others
EDUCATION_university
MARRIAGE_married
MARRIAGE_others
MARRIAGE_single
0
1
20000
24
1
0
0
0
0
1
1
0
0
1
2
120000
26
1
0
0
0
0
1
0
0
1
2
3
90000
34
1
0
0
0
0
1
0
0
1
3
4
50000
37
1
0
0
0
0
1
1
0
0
4
5
50000
57
0
1
0
0
0
1
1
0
0
…
…
…
…
…
…
…
…
…
…
…
…
…
29596
29996
220000
39
0
1
0
1
0
0
1
0
0
29597
29997
150000
43
0
1
0
1
0
0
0
0
1
29598
29998
30000
37
0
1
0
0
0
1
0
0
1
29599
29999
80000
41
0
1
0
1
0
0
1
0
0
29600
30000
50000
46
0
1
0
0
0
1
1
0
0
29601 rows × 12 columns

D) In order to run a model, we have to define now the X and y variables. Set the y values to the “default” (either 0 or 1). The remaining columns represent the X values.
In [169]:
x=defaults[[‘ID’,’CREDIT_LIMIT’,’SEX’,’EDUCATION’,’MARRIAGE’,’AGE’]]
y=defaults[‘default’]
x
y
Out[169]:
0 1
1 1
2 0
3 0
4 0
..
29596 0
29597 0
29598 1
29599 1
29600 1
Name: default, Length: 29601, dtype: int64

4. Further Data Analysis

A) Please evaluate the importance of each feature of the X values on the y value of the previous exercise using any tree ensamble algorithm and display the results graphically. Please comment your findings. Please tell us what you did and why you did it.
In [170]:

—————————————————————————
NameError Traceback (most recent call last)
in
—-> 1 clf = RandomForestClassifier(n_estimators=10)
2 clf = clf.fit(X, Y)

NameError: name ‘RandomForestClassifier’ is not defined

B) Please assess how accuarely we can predict, whether a customer will default or not using Support Vector Machines. As always plase explain your work.
In [ ]:

C) Now let’s see how accurate the XGBoost algorithm can predict, whether a customer will default or not better than the previous SVM. Please give us the accuracy score and standard deviation. Choose an appropiate model for testing and training.
In [ ]: