<jupyter_start><jupyter_text>Members onlyThroughout the course so far, you've been exposed to some common problems that you may encounter with your data, from data type constraints, data range constrains, uniqueness constraints, and now membership constraints for categorical values.In this exercise, you will map hypothetical problems to their respective categories.Membership Constraint- A has_loan column with the value 12- A GPA column containing a Z- grade- A month column with the value 14- A day_of_week column with the value SuntermondayOther Constraint- A birthdate colmun with values in the future- A age column with values above 130- A revenue column represented as a string  Finding consistencyIn this exercise and throughout this chapter, you'll be working with the airlines DataFrame which contains survey responses on the San Francisco Airport from airline customers.The DataFrame contains flight metadata such as the airline, the destination, waiting times as well as answers to key questions regarding cleanliness, safety, and satisfaction. Another DataFrame named categories was created, containing all correct possible values for the survey columns.In this exercise, you will use both of these DataFrames to find survey answers with inconsistent values, and drop them, effectively performing an outer and inner join on both these DataFrames as seen in the video exercise. The pandas package has been imported as pd, and the airlines and categories DataFrames are in your environment.<jupyter_code>import pandas as pd
airlines = pd.read_csv('../datasets/airlines_final.csv')
airlines.head()
categories = airlines[['cleanliness', 'safety', 'satisfaction']]
categories
# Print unique values of survey columns in airlines
airlines['cleanliness'].unique()
airlines['safety'].unique()
airlines['satisfaction'].unique()<jupyter_output><empty_output><jupyter_text>QuestionTake a look at the output. Out of the cleanliness, safety and satisfaction columns, which one has an inconsistent category and what is it?A. cleanliness because it has an Unacceptable category.B. cleanliness because it has a Terribly dirty category.C. satisfaction because it has a Very satisfied category.D. safety because it has a Neutral category.<jupyter_code># Find the cleanliness category in airlines not in categories
cat_clean = set(airlines['cleanliness']).difference(categories['cleanliness'])

# Find rows with that category
cat_clean_rows = airlines['cleanliness'].isin(cat_clean)

# Print rows with inconsistent category
print(airlines[cat_clean_rows])
# Find the cleanliness category in airlines not in categories
cat_clean = set(airlines['cleanliness']).difference(categories['cleanliness'])

# Find rows with that category
cat_clean_rows = airlines['cleanliness'].isin(cat_clean)

# Print rows with inconsistent category
print(airlines[cat_clean_rows])

# Print rows with consistent categories only
print(airlines[~cat_clean_rows])<jupyter_output>Empty DataFrame
Columns: [Unnamed: 0, id, day, airline, destination, dest_region, dest_size, boarding_area, dept_time, wait_min, cleanliness, safety, satisfaction]
Index: []
      Unnamed: 0    id        day        airline        destination  \
0              0  1351    Tuesday    UNITED INTL             KANSAI   
1              1   373     Friday         ALASKA  <NAME>   
2              2  2820   Thursday          DELTA        LOS ANGELES   
3              3  1157    Tuesday      SOUTHWEST        LOS ANGELES   
4              4  2992  Wednesday       AMERICAN              MIAMI   
...          ...   ...        ...            ...                ...   
2472        2804  1475    Tuesday         ALASKA       NEW YORK-JFK   
2473        2805  2222   Thursday      SOUTHWEST            PHOENIX   
2474        2806  2684     Friday         UNITED            ORLANDO   
2475        2807  2549    Tuesday        JETBLUE         LONG BEACH   
2476        2808  2162   Saturday  CHINA EAST[...]<jupyter_text>Categories of errorsIn the video exercise, you saw how to address common problems affecting categorical variables in your data, including white spaces and inconsistencies in your categories, and the problem of creating new categories and mapping existing ones to new ones.To get a better idea of the toolkit at your disposal, you will be mapping functions and methods from pandas and Python used to address each type of problem.White space and inconsistency- .str.strip()- .str.lower()- .str.upper()Creating or remapping categories- .replace()- pandas.cut()- pandas.qcut()  Inconsistent categoriesIn this exercise, you'll be revisiting the airlines DataFrame from the previous lesson.As a reminder, the DataFrame contains flight metadata such as the airline, the destination, waiting times as well as answers to key questions regarding cleanliness, safety, and satisfaction on the San Francisco Airport.In this exercise, you will examine two categorical columns from this DataFrame, dest_region and dest_size respectively, assess how to address them and make sure that they are cleaned and ready for analysis. The pandas package has been imported as pd, and the airlines DataFrame is in your environment.<jupyter_code>airlines['dest_region'].unique()
airlines['dest_size'].unique()<jupyter_output><empty_output><jupyter_text>QuestionFrom looking at the output, what do you think is the problem with these columns?A. The dest_region column has only inconsistent values due to capitalization.B. The dest_region column has inconsistent values due to capitalization and has one value that needs to be remapped.C. The dest_size column has only inconsistent values due to leading and trailing spaces.D. Both 2 and 3 are correct.<jupyter_code># Print unique values of both columns
print(airlines['dest_region'].unique())
print(airlines['dest_size'].unique())

# Lower dest_region column and then replace "eur" with "europe"
airlines['dest_region'] = airlines['dest_region'].str.lower() 
airlines['dest_region'] = airlines['dest_region'].replace({'eur':'europe'})

airlines['dest_region']
# Print unique values of both columns
print(airlines['dest_region'].unique())
print(airlines['dest_size'].unique())

# Lower dest_region column and then replace "eur" with "europe"
airlines['dest_region'] = airlines['dest_region'].str.lower() 
airlines['dest_region'] = airlines['dest_region'].replace({'eur':'europe'})

# Remove white spaces from `dest_size`
airlines['dest_size'] = airlines['dest_size'].str.strip()

# Verify changes have been effected
print(airlines['dest_region'].unique())
print(airlines['dest_size'].unique())<jupyter_output>['asia' 'canada/mexico' 'west us' 'east us' 'midwest us' 'middle east'
 'europe' 'central/south america' 'australia/new zealand']
['Hub' 'Small' '    Hub' 'Medium' 'Large' 'Hub     ' '    Small'
 'Medium     ' '    Medium' 'Small     ' '    Large' 'Large     ']
['asia' 'canada/mexico' 'west us' 'east us' 'midwest us' 'middle east'
 'europe' 'central/south america' 'australia/new zealand']
['Hub' 'Small' 'Medium' 'Large']<jupyter_text>Remapping categoriesTo better understand survey respondents from airlines, you want to find out if there is a relationship between certain responses and the day of the week and wait time at the gate.The airlines DataFrame contains the day and wait_min columns, which are categorical and numerical respectively. The day column contains the exact day a flight took place, and wait_min contains the amount of minutes it took travelers to wait at the gate. To make your analysis easier, you want to create two new categorical variables:- wait_type: 'short' for 0-60 min, 'medium' for 60-180 and long for 180+- day_week: 'weekday' if day is in the weekday, 'weekend' if day is in the weekend.The pandas and numpy packages have been imported as pd and np. Let's create some new categorical data!<jupyter_code>import numpy as np
# Create ranges for categories
label_ranges = [0, 60, 180, np.inf]
label_names = ['short', 'medium', 'long']

# Create wait_type column
airlines['wait_type'] = pd.cut(airlines.wait_min, bins = label_ranges, 
                                labels = label_names)

# Create mappings and replace
mappings = {'Monday':'weekday', 'Tuesday':'weekday', 'Wednesday': 'weekday', 
            'Thursday': 'weekday', 'Friday': 'weekday', 
            'Saturday': 'weekend', 'Sunday': 'weekend'}

airlines['day_week'] = airlines['day'].replace(mappings)
airlines<jupyter_output><empty_output><jupyter_text>Removing titles and taking namesWhile collecting survey respondent metadata in the airlines DataFrame, the full name of respondents was saved in the full_name column. However upon closer inspection, you found that a lot of the different names are prefixed by honorifics such as "Dr.", "Mr.", "Ms." and "Miss".Your ultimate objective is to create two new columns named first_name and last_name, containing the first and last names of respondents respectively. Before doing so however, you need to remove honorifics.The airlines DataFrame is in your environment, alongside pandas as pd.<jupyter_code># Replace "Dr." with empty string ""
airlines['full_name'] = airlines['full_name'].str.replace("Dr.","")

# Replace "Mr." with empty string ""
airlines['full_name'] = airlines['full_name'].str.replace("Mr.","")

# Replace "Miss" with empty string ""
airlines['full_name'] = airlines['full_name'].str.replace("Miss","")

# Replace "Ms." with empty string ""
airlines['full_name'] = airlines['full_name'].str.replace("Ms.","")

# Assert that full_name has no honorifics
assert airlines['full_name'].str.contains('Ms.|Mr.|Miss|Dr.').any() == False<jupyter_output><empty_output><jupyter_text>Keeping it descriptiveTo further understand travelers' experiences in the San Francisco Airport, the quality assurance department sent out a qualitative questionnaire to all travelers who gave the airport the worst score on all possible categories. The objective behind this questionnaire is to identify common patterns in what travelers are saying about the airport.Their response is stored in the survey_response column. Upon a closer look, you realized a few of the answers gave the shortest possible character amount without much substance. In this exercise, you will isolate the responses with a character count higher than 40 , and make sure your new DataFrame contains responses with 40 characters or more using an assert statement.The airlines DataFrame is in your environment, and pandas is imported as pd.<jupyter_code># Store length of each row in survey_response column
resp_length = airlines['survey_response'].str.len()
print(resp_length)

# Find rows in airlines where resp_length > 40
airlines_survey = airlines[resp_length > 40]
print(airlines_survey)

# Assert minimum survey_response length is > 40
assert airlines_survey['survey_response'].str.len().min() > 40

# Print new survey_response column
print(airlines_survey.survey_response)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0803006691722431, 'fraction_numerical': 0.011825098542487854, 'mean_word_length': 4.563488016318205, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 32, 'https://': 0, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '113295', 'license': 'MIT', 'n_tokens_mistral': 3014, 'n_tokens_neox': 2767, 'n_words': 1231, 'path': 'cleaning-data-in-python/2. Text and categorical data problems/notebook_section_2.ipynb', 'repo_name': 'nhutnamhcmus/datacamp-playground', 'size': '10920'}