Amsterdam Housing

1.What Amsterdam will receive from tourist tax if the event lasts a week and you will have 30.000 visitors?

According to City of Amsterdam, the details of the tourist tax are shown in Figure 1. However, from 2024 the tourist tax will rise to 12. 5% . And according to Statista, we learnt the average cost of an overnight stay in Amsterdam in Figure 2.

(Figure 1, Tourist Tax, resource: City of Amsterdam)

Average cost of overnight accommodation in Amsterdam in the Netherlands from January 2019 to June 2023(in euros)

(Figure 2, Average cost of overnight accommodation in Amsterdam, resource: statista)

As the event will take place in May, we refer to the average price of €294 per night in May 2023, and we assume that all 30,000 visitors will stay at the hotel. Then there will be two limiting scenarios.

• In the first case, each person is in a separate room:
  (294 * 12.5% + 3) * 30000 * 7 = 8,347,500
• The second scenario is where everyone is sharing a room with someone else (double occupancy):
  (294 * 12.5% + 3 * 2) * 15000 * 7 = 4,488,750

It is assumed that Amsterdam will receive between €4,488,750 and €8,347,500 in tourist tax.

2.Plot the amount of AirBnB locations per neighbourhood.

import pandas as pd
import plotly.express as px

# import airbnb data
airbnb = pd.read_csv('listings.csv')

# calculate the number of rooms for each neighbourhood
neighbourhood = airbnb.neighbourhood.value_counts()

# plotting the result
fig = px.bar(neighbourhood, y='count', text_auto=True)
fig.show()

(Figure 3, the amount of AirBnB locations per neighbourhood)

After aggregating all the Airbnb listings in Amsterdam by neighborhood, the statistical results we obtained are presented in Figure 3. It is evident that the De Baarsjes-Oud-West area has the highest number of Airbnb listings, while Bijlmer-Oost has the fewest.

3.Which street in Amsterdam has the most AirBnB apartments?

import pandas as pd
from geopy.geocoders import Nominatim
import csv

airbnb = pd.read_csv('listings.csv') 
geolocator = Nominatim(user_agent="user_name")
Loc = airbnb[['latitude','longitude']]

#Get a specific address based on latitude and longitude
file = 'location.csv'
header = ['location', 'coordinate']
with open(file,'w',encoding='utf-8',newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(header)
        
for i,row in Loc.iterrows(): 
    a = str(row['latitude']) 
    b = str(row['longitude']) 
    address = geolocator.reverse(a+","+b, timeout=None) 
    print(address)
        
    with open(file,'a+',encoding='utf-8',newline='') as csvfile:
        writer = csv.writer(csvfile)
        writer.writerow(address)
        csvfile.close()

Here is part of csvfile: location:

We can see that not all addresses have the same format, but the second last position is the postcode, so we’re going to extract the postcode information for each address.

import pandas as pd
from collections import Counter

#Get the postcode from the address
Location = pd.read_csv('location.csv') 
street = []
for i, row in Location.iterrows():
    Location_split = row[1].split(',')
    street.append(Location_split[-2]) 

#Counting which postcodes appear most often
count = Counter(street)
postcode = pd.DataFrame(count, index=[0])
postcode.to_csv('postcode.csv', index=False)

Here are the top five postcodes in terms of occurrences:

We can see that 1052 CH appears 175 times far more than any other postcode. Next we will look at which addresses are at 1052 CH.

#Filter out addresses containing the most frequently occurring postcodes
df = pd.DataFrame(street)
#To eliminate spaces in the postcode
post = []
code = []
for i, row in df.iterrows():
    df_split = row[0].split(' ')
    post.append(df_split[1]) 
    code.append(df_split[-1])

df1 = pd.DataFrame(post)
df2 = pd.DataFrame(code)

New_loc = pd.concat([Location,df1,df2],axis=1)
New_loc.columns = ['num','location','coordinate','post','code']
CH_1 = New_loc[New_loc['post'] == '1052']
CH_2 = CH_1[CH_1['code'] == 'CH']

StreetName = pd.DataFrame(CH_2)
StreetName.to_csv('StreetName.csv',sep=',',index=True,header=True) 

These are the AirBnb’s at 1052CH:

Obviously the street called Nassaukade has the most Airbnb’s.

4.Try to cross reference the data from the AirBnB dataset with the BBGA. Can you figure out if all apartments of AirBnB are designated as housing? Which number of apartments are not rented out all the time but are also used as normal housing?

import pandas as pd
import matplotlib.pyplot as plt

airbnb = pd.read_csv('listings.csv')
num = [] 
name = [] 
airbnb_list = airbnb.transpose() 
airbnb1 = airbnb_list.values.tolist() 

# define a function to find duplicates
def find_duplicates(lst): 
    return list(set([x for x in lst if lst.count(x) > 1]))

# find the duplicate house types
house_type = find_duplicates(airbnb1[8]) 
print(house_type) 

# counting the numbers for each room type
count = []
for i in range(4):
    name = house_type[i]
    a = airbnb.query('room_type == @name')
    count.append(len(a))
print(count)

['Hotel room', 'Entire home/apt', 'Private room', 'Shared room']
[54, 6477, 1813, 42]

# check the amount of rooms which is available all year
rooms = sum(count)
num_rented = len(airbnb.query('availability_365 < 365'))
normal = rooms - num_rented
print(normal)

36

For this question, we were unsure about how to utilize the BBGA dataset, so all the responses are based on Airbnb data. Among the four room types, hotel rooms are not categorized as housing. Therefore, as an answer to the first question, 54 rooms were not designated as housing. Regarding the second question, we conducted a count of all properties with fewer than 365 available days, which totaled 8,350. Consequently, the remaining 36 properties have not been rented at all and are utilized as residential housing.

5.How many hotel rooms should be built if Amsterdam wants to accommodate the same number of tourists?

Referring to the data in Table, we assume that Amsterdam typically receives 786,000 overnight visitors in May. Adding 30,000 people to this number brings the expected number of overnight guests in Amsterdam in May to 816,000, which does not exceed the maximum number of guests Amsterdam can accommodate, so no additional hotel rooms are needed.

(Table, Hotels; guests, overnight stays, country of residence, region. Source: Statistics Netherlands)

6.How many different licenses are issued?

import pandas as pd
import matplotlib.pyplot as plt

airbnb = pd.read_csv('listings.csv')

num = [] 
num = []
name = []
airbnb_list = airbnb.transpose()
airbnb1 = airbnb_list.values.tolist()

# define a function to find duplicates
def find_duplicates(lst):
    return list(set([x for x in lst if lst.count(x) > 1]))

# find the duplicate house types
house_type = find_duplicates(airbnb1[8])

# find names and numbers for each house type
for i in house_type:
    # print(str(i[0]))
    sublist = airbnb.loc[airbnb['room_type'] == str(i)]
    num.append(sublist.shape[0])
    name.append(str(i))

# plotting
plt.barh(name, num)
plt.show()

In the Airbnb dataset, the last column contains license data, but understanding the types of licenses requires knowledge of the patterns associated with these licenses. After investigation, we determined that the types of licenses correspond to different room types. Therefore, the statistics on license types can be simplified into statistics on room type categories. The final results are illustrated in Figure 4.

(Figure 4, the statistics on license types)