<jupyter_start><jupyter_text>**Cleaning Data in Python live training**Welcome to this live, hands-on training where you will learn how to effectively diagnose and treat missing data in Python.The majority of data science work often revolves around pre-processing data, and making sure it's ready for analysis. In this session, we will be covering how transform our raw data into accurate insights. In this notebook, you will learn:* Import data into `pandas`, and use simple functions to diagnose problems in our data.* Visualize missing and out of range data using `missingno` and `seaborn`.* Apply a range of data cleaning tasks that will ensure the delivery of accurate insights. **The Dataset**The dataset to be used in this webinar is a CSV file named `airbnb.csv`, which contains data on airbnb listings in the state of New York. It contains the following columns:- `listing_id`: The unique identifier for a listing- `description`: The description used on the listing- `host_id`: Unique identifier for a host- `host_name`: Name of host- `neighbourhood_full`: Name of boroughs and neighbourhoods- `coordinates`: Coordinates of listing _(latitude, longitude)_- `Listing added`: Date of added listing- `room_type`: Type of room - `rating`: Rating from 0 to 5.- `price`: Price per night for listing- `number_of_reviews`: Amount of reviews received - `last_review`: Date of last review- `reviews_per_month`: Number of reviews per month- `availability_365`: Number of days available per year- `Number of stays`: Total number of stays thus far  **Getting started**<jupyter_code># Import libraries
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import seaborn as sns
import missingno as msno
import datetime as dt
# Read in the dataset
airbnb = pd.read_csv('https://github.com/adelnehme/python-for-spreadsheet-users-webinar/blob/master/datasets/airbnb.csv?raw=true', index_col = 'Unnamed: 0')<jupyter_output><empty_output><jupyter_text>**Diagnosing data cleaning problems using simple `pandas` and visualizations**  Some important and common methods needed to get a better understanding of DataFrames and diagnose potential data problems are the following: - `.head()` prints the header of a DataFrame- `.dtypes` prints datatypes of all columns in a DataFrame- `.info()` provides a bird's eye view of column data types and missing values in a DataFrame- `.describe()` returns a distribution of numeric columns in your DataFrame- `.isna().sum()` allows us to break down the number of missing values per column in our DataFrame- `.unique()` finds the number of unique values in a DataFrame column- `sns.displot()` plots the distribution of one column in your DataFrame.<jupyter_code># Print the header of the DataFrame
airbnb.head()<jupyter_output><empty_output><jupyter_text>By merely looking at the data, we can already diagnose a range of potential problems down the line such as:_Data type problems:_- **Problem 1**: We can see that the `coordinates` column is probably a string (`str`) - most mapping functions require a latitude input, and longitude input, so it's best to split this column into two and convert the values to `float`.- **Problem 2**: Similar to `coordinates` - the `price` column also is a string with `$` attached to each price point, we need to convert that to `float` if we want a good understanding of the dataset. - **Problem 3**: We need to make sure date columns (`last_review` and `listing_added`) are in `datetime` to allow easier manipulation of data data._Missing data problems:_- **Problem 4**: We can see that there are missing data in some columns, we'll get a better bird's eye view of that down the line._Text/categorical data problems:_- **Problem 5**: To be able to visualize number of listings by boroughs - we need to separate neighborhoud name from borough name in `neighbourhood_full` column.- **Problem 6**: Looking at `room_type`, let's replace those values to make them `'Shared Room'`, `'Private Home/Apartment'`, `'Private Room'` and `'Hotel Room'`.<jupyter_code># Print data types of DataFrame
airbnb.dtypes<jupyter_output><empty_output><jupyter_text>Printing the data types confirms that `coordinates` and `price` need to be converted to `float`, and date columns need to be converted to `datetime` _(**problems 1,2 3)**_<jupyter_code># Print info of DataFrame
airbnb.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 10019 entries, 0 to 10018
Data columns (total 16 columns):
 #   Column              Non-Null Count  Dtype  
---  ------              --------------  -----  
 0   listing_id          10019 non-null  int64  
 1   name                10014 non-null  object 
 2   host_id             10019 non-null  int64  
 3   host_name           10017 non-null  object 
 4   neighbourhood_full  10019 non-null  object 
 5   coordinates         10019 non-null  object 
 6   room_type           10019 non-null  object 
 7   price               9781 non-null   object 
 8   number_of_reviews   10019 non-null  int64  
 9   last_review         7944 non-null   object 
 10  reviews_per_month   7944 non-null   float64
 11  availability_365    10019 non-null  int64  
 12  rating              7944 non-null   float64
 13  number_of_stays     7944 non-null   float64
 14  5_stars             7944 non-null   float64
 15  listing_added       10019 non-null  object 
dtypes: f[...]<jupyter_text>Printing the info confirms our hunch about the following:- There is missing data in the `price`, `last_review`, `reviews_per_month`, `rating`, `number_of_stays`, `5_stars` columns. It also seems that the missingness of `last_review`, `reviews_per_month`, `rating`, `number_of_stays`, `5_stars` are related since they have the same amount of missing data. We will confirm later with `missingno` _(**problem 4**)_.<jupyter_code># Print number of missing values
airbnb.isna().sum()<jupyter_output><empty_output><jupyter_text>There are a variety of ways of dealing with missing data that is dependent on type of missingness, as well as the business assumptions behind our data - our options could be:- Dropping missing data (if the data dropped does not impact or skew our data)- Setting to missing and impute with statistical measures (median, mean, mode ...)- Imputing with more complex algorithmic/machine learning based approaches- Impute based on business assumptions of our data<jupyter_code># Print description of DataFrame
airbnb.describe()<jupyter_output><empty_output><jupyter_text>- **Problem 7:** Looking at the maximum of the `rating` column - we see that it is out of range of `5` which is the maximum rating possible. We need to make sure we fix the range this column.It's worth noting that `.describe()` does not offer a bird's eye view of all the out of range data we have, for example, what if we have date data in the future? Or given our dataset, `listing_added` dates that are in the future of `last_review` dates?<jupyter_code># Visualize the distribution of the rating column
sns.distplot(airbnb['rating'], bins = 20)
plt.title('Distribution of listing ratings')
plt.show()
# Find number of unique values in room_type column
airbnb['room_type'].unique()<jupyter_output><empty_output><jupyter_text>- **Problem 8**: There are trailing spaces and capitalization issues with `room_type`, we need to fix this problem.<jupyter_code># How many values of different room_types do we have?
airbnb['room_type'].value_counts()<jupyter_output><empty_output><jupyter_text>**Our to do list:**_Data type problems:_- **Task 1**: Split `coordinates` into 2 columns and convert them to `float`- **Task 2**: Remove `$` from `price` and convert it to `float`- **Task 3**: Convert `listing_added` and `last_review` to `datetime`_Text/categorical data problems:_- **Task 4**: We need to collapse `room_type` into correct categories- **Task 5**: Divide `neighbourhood_full` into 2 columns and making sure they are clean_Data range problems:_- **Task 6**: Make sure we set the correct maximum for `rating` column out of range values_Dealing with missing data:_- **Task 7**: Understand the type of missingness, and deal with the missing data in most of the remaining columns. _Is that all though?_- We need to investigate if we duplicates in our data- We need to make sure that data makes sense by applying some sanity checks on our DataFrame   **Q&A**  **Cleaning data**   Data type problems<jupyter_code># Reminder of the DataFrame
airbnb.head()<jupyter_output><empty_output><jupyter_text>**Task 1:** Replace `coordinates` with `latitude` and `longitude` columns To perform this task, we will use the following methods:- `.str.replace("","")` replaces one string in each row of a column with another- `.str.split("")` takes in a string and lets you split a column into two based on that string- `.astype()` lets you convert a column from one type to another<jupyter_code># Remove "(" and ")" from coordinates
airbnb['coordinates'] = airbnb['coordinates'].str.replace("(","")
airbnb['coordinates'] = airbnb['coordinates'].str.replace(")","")
# Print the header of the column
airbnb['coordinates'].head()
# Split column into two
lat_long = airbnb['coordinates'].str.split(",", expand = True)
lat_long.head()
# Assign correct columns to latitude and longitude columns in airbnb
airbnb['latitude'] = lat_long[0]
airbnb['longitude'] = lat_long[1]
# Print the header and confirm new column creation
airbnb.head()
# Print out dtypes again
airbnb.dtypes
# Convert latitude and longitude to float
airbnb['latitude'] = airbnb['latitude'].astype('float')
airbnb['longitude'] = airbnb['longitude'].astype('float')
# Print dtypes again
airbnb.dtypes
# Drop coordinates column
airbnb.drop('coordinates', axis = 1, inplace = True)<jupyter_output><empty_output><jupyter_text>**Task 2:** Remove `$` from `price` and convert it to `float` To perform this task, we will be using the following methods:- `.str.strip()` which removes a specified string from each row in a column- `.astype()`<jupyter_code># Calculate mean of price without conversion
airbnb['price'].mean()
# Remove $ from price before conversion to float
airbnb['price'] = airbnb['price'].str.strip("$")
# Print header to make sure change was done
airbnb['price'].head()
# Convert price to float
airbnb['price'] = airbnb['price'].astype('float')
# Calculate mean of price after conversion
airbnb['price'].mean()
# Visualize distribution of prices
sns.distplot(airbnb['price'], bins = 20)
plt.show()<jupyter_output><empty_output><jupyter_text>**Task 3:** Convert `listing_added` and `last_review` columns to `datetime` To perform this task, we will use the following functions:- `pd.to_datetime(format = "")`  - `format` takes in the desired date format `"%Y-%m-%d"`<jupyter_code># Print header of two columns
airbnb[['listing_added', 'last_review']].head()
# Convert both columns to datetime
airbnb['listing_added'] = pd.to_datetime(airbnb['listing_added'], format = '%Y-%m-%d')
airbnb['last_review'] = pd.to_datetime(airbnb['last_review'], format = '%Y-%m-%d')
# Print header and datatypes of both columns again
print(airbnb[['listing_added', 'last_review']].head())
print(airbnb[['listing_added', 'last_review']].dtypes)<jupyter_output>listing_added last_review
0    2018-06-08  2018-12-12
1    2018-12-25  2019-06-30
2    2018-08-15         NaT
3    2017-03-20  2017-09-23
4    2020-10-23  2019-06-12
listing_added    datetime64[ns]
last_review      datetime64[ns]
dtype: object<jupyter_text>Text and categorical data problems  **Task 4:** We need to collapse `room_type` into correct categories To perform this task, we will be using the following methods:- `.str.lower()` to lowercase all rows in a string column- `.str.strip()` to remove all white spaces of each row in a string column- `.replace()` to replace values in a column with another<jupyter_code># Print unique values of `room_type`
airbnb['room_type'].unique()
# Deal with capitalized values
airbnb['room_type'] = airbnb['room_type'].str.lower()
airbnb['room_type'].unique()
# Deal with trailing spaces
airbnb['room_type'] = airbnb['room_type'].str.strip()
airbnb['room_type'].unique()
# Replace values to 'Shared room', 'Entire place', 'Private room' and 'Hotel room' (if applicable).
mappings = {'private room': 'Private Room', 
            'private': 'Private Room',
            'entire home/apt': 'Entire place',
            'shared room': 'Shared room',
            'home': 'Entire place'}

# Replace values and collapse data
airbnb['room_type'] = airbnb['room_type'].replace(mappings)
airbnb['room_type'].unique()<jupyter_output><empty_output><jupyter_text>**Task 5:** Divide `neighbourhood_full` into 2 columns and making sure they are clean<jupyter_code># Print header of column
airbnb['neighbourhood_full'].head()
# Split neighbourhood_full
borough_neighbourhood = airbnb['neighbourhood_full'].str.split(",", expand = True)
borough_neighbourhood.head()
# Create borough and neighbourhood columns
airbnb['borough'] = borough_neighbourhood[0]
airbnb['neighbourhood'] = borough_neighbourhood[1]
# Print header of columns
airbnb[['neighbourhood_full', 'borough', 'neighbourhood']].head()
# Drop neighbourhood_full column
airbnb.drop('neighbourhood_full', axis = 1, inplace = True)
# Print out unique values of borough and neighbourhood
print(airbnb['borough'].unique())
print(airbnb['neighbourhood'].unique())
# Strip white space from neighbourhood column
airbnb['neighbourhood'] = airbnb['neighbourhood'].str.strip()
# Print unique values again
print(airbnb['neighbourhood'].unique())<jupyter_output>['Flatlands' 'Upper West Side' 'Brooklyn Heights' 'Lower East Side'
 'Greenwich Village' 'Harlem' 'Sheepshead Bay' 'Theater District'
 'Bushwick' 'Laurelton' 'Mott Haven' 'Flushing' 'Crown Heights' 'Midtown'
 'Financial District' 'East Village' 'Park Slope' 'Washington Heights'
 'Williamsburg' 'Chelsea' 'Bedford-Stuyvesant' 'Gowanus' 'Upper East Side'
 'Ditmars Steinway' 'Cypress Hills' "Hell's Kitchen" 'Ridgewood'
 'Marble Hill' 'Kips Bay' 'Prospect Heights' 'East New York' 'Concord'
 'Stapleton' 'Astoria' 'East Harlem' 'Sunnyside' 'Gramercy'
 'Prospect-Lefferts Gardens' 'Sunset Park' 'Forest Hills'
 'Windsor Terrace' 'Clinton Hill' 'Murray Hill' 'Flatiron District'
 'Greenpoint' 'East Flatbush' 'Tribeca' 'Woodhaven' 'Fort Greene' 'Inwood'
 'Chinatown' 'Rockaway Beach' 'Woodside' 'Bayside' 'Bensonhurst' 'SoHo'
 'Red Hook' 'West Village' 'South Slope' 'Richmond Hill' 'Jamaica'
 'Boerum Hill' 'St. George' 'Clason Point' 'College Point' 'Vinegar Hill'
 'Rego Park' 'East Elmhurst' 'Kensin[...]<jupyter_text>**Task 6:** Make sure we set the correct maximum for `rating` column out of range values<jupyter_code># Visualize the rating column again
sns.distplot(airbnb['rating'], bins = 20)
plt.show()
# Isolate rows of rating > 5.0
airbnb[airbnb['rating'] > 5.0]
# Drop these rows and make sure we have effected changes
airbnb.drop(airbnb[airbnb['rating'] > 5.0].index, inplace = True)
# Visualize the rating column again
sns.distplot(airbnb['rating'], bins = 20)
plt.show()
# Get the maximum
airbnb['rating'].max()<jupyter_output><empty_output><jupyter_text>**Q&A**  Dealing with missing data The `missingno` (imported as `msno`) package is great for visualizing missing data - we will be using:- `msno.matrix()` visualizes a missingness matrix- `msno.bar()` visualizes a missngness barplot- `plt.show()` to show the plot<jupyter_code># Visualize the missingness 
msno.matrix(airbnb)
plt.show()<jupyter_output><empty_output><jupyter_text>Looking at the missingness matrix, we can see that missing values are almost identical between `last_review`, `reviews_per_month`, `rating`, `number_of_stays`, and `5_stars`. Let's confirm this further by sorting on `rating`.<jupyter_code># Visualize the missingness on sorted values
msno.matrix(airbnb.sort_values(by = 'rating'))
plt.show()
# Missingness barplot
msno.bar(airbnb)<jupyter_output><empty_output><jupyter_text>**Treating the** `rating`, `number_of_stays`, `5_stars`, `reviews_per_month` **columns**<jupyter_code># Understand DataFrame with missing values in rating, number_of_stays, 5_stars, reviews_per_month
airbnb[airbnb['rating'].isna()].describe()
# Understand DataFrame with missing values in rating, number_of_stays, 5_stars, reviews_per_month
airbnb[~airbnb['rating'].isna()].describe()<jupyter_output><empty_output><jupyter_text>Looking at the missing data in the DataFrame - we can see that `number_of_reviews` across all missing rows is 0. We can infer that these listings have never been visited - hence could be inferred they're inactive/have never been visited. We can impute them as following:- Set `NaN` for `reviews_per_month`, `number_of_stays`, `5_stars` to 0.- Since a `rating` did not happen, let's keep the column as is - but create a new column named `rated` that takes in `1` if yes, `0` if no.- We will also leave `last_review` as is.<jupyter_code># Impute missing data
airbnb = airbnb.fillna({'reviews_per_month':0,
                        'number_of_stays':0,
                        '5_stars':0})

# Create is_rated column
is_rated = np.where(airbnb['rating'].isna() == True, 0, 1)
airbnb['is_rated'] = is_rated<jupyter_output><empty_output><jupyter_text>**Treating the** `price` **column**<jupyter_code># Investigate DataFrame with missing values in price
airbnb[airbnb['price'].isna()].describe()
# Investigate DataFrame with missing values in price
airbnb[~airbnb['price'].isna()].describe()<jupyter_output><empty_output><jupyter_text>From a common sense perspective, the most predictive factor for a room's price is the `room_type` column, so let's visualize how price varies by room type with `sns.boxplot()` which displays the following information:<jupyter_code># Visualize relationship between price and room_type
sns.boxplot(x = 'room_type', y = 'price', data = airbnb)
plt.ylim(0, 400)
plt.xlabel('Room Type')
plt.ylabel('Price')
plt.show()
# Get median price per room_type
airbnb.groupby('room_type').median()['price']
# Impute price based on conditions
airbnb.loc[(airbnb['price'].isna()) & (airbnb['room_type'] == 'Entire place'), 'price'] = 163.0
airbnb.loc[(airbnb['price'].isna()) & (airbnb['room_type'] == 'Private Room'), 'price'] = 70.0
airbnb.loc[(airbnb['price'].isna()) & (airbnb['room_type'] == 'Shared Room'), 'price'] = 50.0
# Confirm price has been imputed
airbnb.isna().sum()<jupyter_output><empty_output><jupyter_text>What's still to be done? Albeit we've done a significant amount of data cleaning tasks, there are still a couple of problems we have yet to diagnose. When cleaning data, we need to consider:- Values that do not make any sense *(for example: are there values of `last_review` that older than `listing_added`? Are there listings in the future?*)- Presence of duplicates values - and how to deal with them?  **Task 8:** Do we have consistent date data?<jupyter_code># Doing some sanity checks on date data
today = dt.date.today()
# Are there reviews in the future?
airbnb[airbnb['last_review'].dt.date > today]
# Are there listings in the future?
airbnb[airbnb['listing_added'].dt.date > today]
# Drop these rows since they are only 4 rows
airbnb = airbnb[~(airbnb['listing_added'].dt.date > today)]
# Are there any listings with listing_added > last_review
inconsistent_dates = airbnb[airbnb['listing_added'].dt.date > airbnb['last_review'].dt.date]
inconsistent_dates
# Drop these rows since they are only 2 rows
airbnb.drop(inconsistent_dates.index, inplace = True)<jupyter_output><empty_output><jupyter_text>**Task 9:** Let's deal with duplicate data There are two notable types of duplicate data:- Identical duplicate data across all columns- Identical duplicate data cross most or some columnsTo diagnose, and deal with duplicate data, we will be using the following methods and functions:- `.duplicated(subset = , keep = )`  - `subset` lets us pick one or more columns with duplicate values.  - `keep` returns lets us return all instances of duplicate values.- `.drop_duplicates(subset = , keep = )`<jupyter_code># Print the header of the DataFrame again
airbnb.head()
# Find duplicates
duplicates = airbnb.duplicated(subset = 'listing_id', keep = False)
print(duplicates)
# Find duplicates
airbnb[duplicates].sort_values('listing_id')
# Remove identical duplicates
airbnb = airbnb.drop_duplicates()
# Find non-identical duplicates
duplicates = airbnb.duplicated(subset = 'listing_id', keep = False)
# Show all duplicates
airbnb[duplicates].sort_values('listing_id')<jupyter_output><empty_output><jupyter_text>To treat identical duplicates across some columns, we will chain the `.groupby()` and `.agg()` methods where we group by the column used to find duplicates (`listing_id`) and aggregate across statistical measures for `price`, `rating` and `list_added`. The `.agg()` method takes in a dictionary with each column's aggregation method - we will use the following aggregations:- `mean` for `price` and `rating` columns- `max` for `listing_added` column- `first` for all remaining column*A note on dictionary comprehensions:*Dictionaries are useful data structures in Python with the following format`my_dictionary = {key: value}` where a `key` is mapped to a `value` and whose `value` can be returned with `my_dictionary[key]` - dictionary comprehensions allow us to programmatically create dicitonaries using the structure:```{x: x*2 for x in [1,2,3,4,5]} {1:2, 2:4, 3:6, 4:8, 5:10}```<jupyter_code># Get column names from airbnb
column_names = airbnb.columns
column_names
# Create dictionary comprehension with 'first' as value for all columns not being aggregated
aggregations = {column_name:'first' for column_name in column_names.difference(['listing_id', 'listing_added', 'rating', 'price'])}
aggregations['price'] = 'mean'
aggregations['rating'] = 'mean'
aggregations['listing_added'] = 'max'
aggregations
# Remove non-identical duplicates
airbnb = airbnb.groupby('listing_id').agg(aggregations).reset_index()
# Make sure no duplication happened
airbnb[airbnb.duplicated('listing_id', keep = False)]
# Print header of DataFrame
airbnb.head()<jupyter_output><empty_output><jupyter_text>**Q&A**  Take home questionTry to answer the following questions about the dataset:- What is the average price of listings by borough? Visualize your results with a bar plot!- What is the average availability in days of listings by borough? Visualize your results with a bar plot!- What is the median price per room type in each borough? Visualize your results with a bar plot!- Visualize the number of listings over time.**Functions that should/could be used:**- `.groupby()` and `.agg(})`- `sns.barplot(x = , y = , hue = , data = )`- `sns.lineplot(x = , y = , data = )`- `.dt.strftime()` for extracting specific dates from a `datetime` column**Bonus points if:**- You finish more than one question**Submission details:**- Share with us a code snippet with your output on LinkedIn, Twitter or Facebook- Tag us on `@DataCamp` with the hashtag `datacamplive`<jupyter_code><jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11070416394157401, 'fraction_numerical': 0.013908872901678656, 'mean_word_length': 5.334161833747584, 'pattern_counts': {'":': 0, '<': 99, '<?xml version=': 0, '>': 107, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '601634', 'license': 'MIT', 'n_tokens_mistral': 7293, 'n_tokens_neox': 6789, 'n_words': 2618, 'path': 'Cleaning_Data_in_Python_live_session.ipynb', 'repo_name': 'cahyoardhi/datacamp-webminar-data-cleaning', 'size': '22935'}