Summary
Highlights
The video introduces a data cleaning tutorial using a real-world Airbnb dataset for New York City and provides a link to download the dataset. It highlights the importance of Airbnb as a service and briefly explains the dataset's context as part of the Airbnb Insight initiative, describing listing activity in NYC. The tutorial will use VS Code and a Jupyter notebook for coding.
The first step involves importing the Pandas library and loading the Airbnb dataset (AB_NYC_2019_open_data.csv) into a DataFrame. An initial check using `data.head()` confirms successful loading. The video then displays all column names using `data.columns` and provides a general overview of the dataset's information with `data.info()`, revealing a range index of around 100,000 entries and identifying several columns with a significant number of null values, particularly 'license', 'last review', and 'reviews per month'.
A task list for data cleaning is presented, including: deleting redundant columns, renaming columns, dropping duplicates, cleaning individual columns, removing NA values, and performing further transformations. This checklist will guide the cleaning process throughout the tutorial.
The first method for deleting redundant columns involves filtering, by creating a list of 'columns to keep'. The video demonstrates selecting desired columns to form a new DataFrame, but notes that this does not modify the original DataFrame. It shows how the shape of the original DataFrame remains unchanged. To apply the changes, the filtered DataFrame needs to be assigned back to the original or a new variable.
The second method for handling redundant columns is by explicitly dropping them. A list of 'columns to drop' is created, including 'license' which has many null values. The `data.drop()` method is used with `inplace=True` to directly modify the original DataFrame, ensuring the selected columns are permanently removed. This results in a DataFrame with the desired number of columns.
Column renaming is demonstrated. First, a single column ('name') is renamed to lowercase using a dictionary mapping and `inplace=True`. The video then shows how to rename all columns to uppercase or lowercase programmatically using a loop and the `.upper()` or `.lower()` string methods, appending them to a new list. This new list is then assigned to the DataFrame's `columns` attribute to update all column names simultaneously in a copy of the data.
The tutorial checks for duplicate rows using `data.duplicated()` and confirms their presence by `sum()`. It then proceeds to drop these duplicates using `data.drop_duplicates(inplace=True)`, which modifies the DataFrame directly. A subsequent check confirms that no duplicate rows remain.
Missing values are identified using `data.isna().sum()`, which reveals column-wise NA counts. Before dropping all NA rows, the 'last review' column, containing a large number of missing values, is dropped entirely to prevent losing too much data. Subsequently, all remaining rows with NA values across any column are dropped using `data.dropna(inplace=True)`, and a final check confirms no NA values are left.
This section focuses on cleaning specific columns. The 'host identity verified' column is transformed to all uppercase using `.str.upper()`. The 'instant bookable' column, containing boolean values (True/False), is converted to numeric (1/0) using a lambda function with the `.apply()` method, a crucial step for machine learning model preparation.
The DataFrame's index is reset using `data.reset_index(inplace=True)` to ensure a continuous numerical index, which is often crucial for subsequent operations and clear data referencing. This step is necessary after rows have been removed, potentially leading to a non-sequential index.
The 'price' column, which currently contains string values with dollar signs ('$') and commas (','), is cleaned. The tutorial iteratively removes these characters and any extra spaces using `str.replace()`. Finally, the column's data type is converted to integer using `.astype(int)` to enable numerical operations. The 'service fee' column, also containing string numerical values, would follow a similar cleaning process.
The video concludes by demonstrating how to export the cleaned DataFrame to different file formats. It shows how to save the data to a CSV file using `data.to_csv()`, emphasizing the use of `index=False` to prevent writing the DataFrame index as an extra column. The same process is then applied to export the data to an Excel file using `data.to_excel()`, again highlighting the `index=False` parameter for cleaner output.