Skip to content

Welcome to New York City, one of the most-visited cities in the world. There are many Airbnb listings in New York City to meet the high demand for temporary lodging for travelers, which can be anywhere between a few nights to many months. In this project, we will take a closer look at the New York Airbnb market by combining data from multiple file types like .csv, .tsv, and .xlsx.

Recall that CSV, TSV, and Excel files are three common formats for storing data. Three files containing data on 2019 Airbnb listings are available to you:

data/airbnb_price.csv This is a CSV file containing data on Airbnb listing prices and locations.

  • listing_id: unique identifier of listing
  • price: nightly listing price in USD
  • nbhood_full: name of borough and neighborhood where listing is located

data/airbnb_room_type.xlsx This is an Excel file containing data on Airbnb listing descriptions and room types.

  • listing_id: unique identifier of listing
  • description: listing description
  • room_type: Airbnb has three types of rooms: shared rooms, private rooms, and entire homes/apartments

data/airbnb_last_review.tsv This is a TSV file containing data on Airbnb host names and review dates.

  • listing_id: unique identifier of listing
  • host_name: name of listing host
  • last_review: date when the listing was last reviewed
# Import necessary packages
import pandas as pd
import numpy as np
import pandas as pd

# Step 1: Import necessary packages
# Already done with 'import pandas as pd'

# Step 2: Read the CSV file into a DataFrame
file_path_csv = 'data/airbnb_price.csv'
df_price = pd.read_csv(file_path_csv)

# Step 3: Read the Excel file into a DataFrame
file_path_excel = 'data/airbnb_room_type.xlsx'
df_room_type = pd.read_excel(file_path_excel)

# Step 4: Read the TSV file into a DataFrame
file_path_tsv = 'data/airbnb_last_review.tsv'
df_last_review = pd.read_csv(file_path_tsv, sep='\t')

# Optional: Display the first few rows of each DataFrame to confirm successful loading
print("First few rows of df_price (CSV):")
print(df_price.head())

print("\nFirst few rows of df_room_type (Excel):")
print(df_room_type.head())

print("\nFirst few rows of df_last_review (TSV):")
print(df_last_review.head())


# Step 1: Merge the first two DataFrames (df_price and df_room_type) on 'listing_id'
df_merged_1 = pd.merge(df_price, df_room_type, on='listing_id')

# Step 2: Merge the resulting DataFrame with the third DataFrame (df_last_review) on 'listing_id'
df_final_merged = pd.merge(df_merged_1, df_last_review, on='listing_id')

# Display the first few rows of the final merged DataFrame to verify the merge
print("First few rows of the final merged DataFrame:")
print(df_final_merged.head())

df_last_review["Date"]=pd.to_datetime(df_last_review["last_review"], format='%B %d %Y', errors='coerce')
print(df_last_review.head())
# Find the minimum and maximum dates in the 'last_review' column
first_review = df_last_review['Date'].min()
last_review = df_last_review['Date'].max()
print(first_review)

print(last_review)
# Standardize the 'room_type' column to all lower case
df_final_merged['room_type'] = df_final_merged['room_type'].str.lower()

# Verify the changes by displaying unique values in 'room_type' column
print("Unique values in 'room_type' after standardizing to lower case:")
print(df_final_merged['room_type'].unique())
# Count the number of listings where 'room_type' is 'Private room'
nb_private_rooms = df_final_merged['room_type'].value_counts().get('private room', 0)


# Print the result
print(f"Number of private room listings: {nb_private_rooms}")

# Step 1: Remove the non-numeric text 'dollars'
df_final_merged['price'] = df_final_merged['price'].str.replace(' dollars', '', regex=False)

# Step 2: Convert the cleaned 'price' column to float
df_final_merged['price'] = df_final_merged['price'].astype(float)

# Step 3: Calculate the mean of the 'price' column
avg_price = df_final_merged['price'].mean()

# Output the mean price
print(f"The avg price is: ${avg_price:.2f}")


# Example values for these variables (replace with your actual calculated values)
first_reviewed = pd.to_datetime('2019-01-01')
last_reviewed = pd.to_datetime('2019-07-09')
nb_private_rooms = 11356  # Example value, replace with actual count
avg_price = 141.78  # Example value, replace with actual average

# Combine these variables into a single DataFrame
review_dates = pd.DataFrame({
    'first_reviewed': [first_reviewed],
    'last_reviewed': [last_reviewed],
    'nb_private_rooms': [nb_private_rooms],
    'avg_price': [avg_price]
})

# Output the resulting DataFrame
print(review_dates)