<jupyter_start><jupyter_text>Unit 2B Portfolio Project (Side Notebook Strictly for Data Cleaning and Refining)See [Full Version](https://colab.research.google.com/drive/1s3SJs2dpnH2LQvR9S3JNH2C-yD1na_4R) for Predictive Models & VisualizationUNIT 2B PORTFOLIO PROJECT COMPLETED BY: [<NAME>](http://ryan.zernach.com/portfolio/)![<NAME>](https://ryan.zernach.com/wp-content/uploads/brizy/503/assets/images/iW=440&iH=440&oX=0&oY=0&cW=440&cH=440/Ryan_Zernach_The_Zernach_Foundation_0.png) Air Flights Datasets Courtesy of Department of Transportation's [Bureau of Transportation Statistics](https://www.transtats.bts.gov/Tables.asp?DB_ID=125&DB_Name=Airline%20Origin%20and%20Destination%20Survey%20%28DB1B%29&DB_Short_Name=Origin%20and%20Destination%20Survey) (once link is clicked, select "DB1BMarket") — Data is from 2018 flights in Q1, Q2, Q3, and Q4 that originate somewhere in the USA and have a destination of somewhere in the USA (USA includes Alaska, Hawaii, and US territories)  NOTE: This code will not run all the way through because the datasets are too large for colab, so I had to breakdown the dataset into more manageable chunks, reduce/refine, concatenate, and do that again until I had one, clean dataset, which I [uploaded to Kaggle](https://www.kaggle.com/zernach/2018-airplane-flights) for others to use.  0.1) The data files were downloaded from the Department of Transportation and were then stored in my Google Drive. So the following lines of code request an authentication code from Google Drive, grant permissions, and package the Q1, Q2, Q3, and Q4 csv files into a format that's transferrable from Google Drive to Pandas dataframes. Then, do a bunch of cleaning/wrangling/refining and bundle them altogether into a single 2018 flights dataset.<jupyter_code>!pip install -U -q PyDrive
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from google.colab import auth
from oauth2client.client import GoogleCredentials
import os
import pandas as pd

# Requests an authentication code, which is received by clicking the unique link
# that it provides when this code cell is run...
auth.authenticate_user()
gauth = GoogleAuth()
gauth.credentials = GoogleCredentials.get_application_default()
drive = GoogleDrive(gauth)

# Searches for all files that contain "Air Flight Information" and are within
# the Google Drive Folder with the long, seemingly-random extension shown in orange
listed = drive.ListFile({'q': "title contains 'Flight' and '14GnxygKyjd1N49QA8_ELzx8GnpbVRB2b' in parents"}).GetList()
for file in listed:
  print('title {}, id {}'.format(file['title'], file['id']))
download_path = os.path.expanduser('~/data')
try:
  os.makedirs(download_path)
except FileExistsError:
  pass

# Using the 'title' and 'id' shown in the output two cells above, copy/paste
# that information into the following:

# Q1 Flights
output_file_q1 = os.path.join(download_path, 'Air Flight Information (2018, Q1).csv')
temp_file_q1 = drive.CreateFile({'id': '1SaeyCJYVLJEFhLbC4KYruMpnPgrm374v'})
temp_file_q1.GetContentFile(output_file_q1)

# Q2 Flights
output_file_q2 = os.path.join(download_path, 'Air Flight Information (2018, Q2).csv')
temp_file_q2 = drive.CreateFile({'id': '1HHiazRI4c9ifgdKmESwEQdMBg6U5s_MB'})
temp_file_q2.GetContentFile(output_file_q2)

# Q3 Flights
output_file_q3 = os.path.join(download_path, 'Air Flight Information (2018, Q3).csv')
temp_file_q3 = drive.CreateFile({'id': '1jys2LPb0MSNrkeYdio-l-qr3-oMLNKoT'})
temp_file_q3.GetContentFile(output_file_q3)

# Q4 Flights
output_file_q4 = os.path.join(download_path, 'Air Flight Information (2018, Q4).csv')
temp_file_q4 = drive.CreateFile({'id': '1TJc8oh9t5Z0wzT4PN2JAxlBizwdFy1QI'})
temp_file_q4.GetContentFile(output_file_q4)

# Q1 & Q2 Cleaned Flights
output_file_q_1_2 = os.path.join(download_path, 'q1_q2 Flights .csv')
temp_file_q_1_2 = drive.CreateFile({'id': '1Vuc4Dwd-1PnYZ_iFcJ2svG8ikBwDDbtj'})
temp_file_q_1_2.GetContentFile(output_file_q_1_2)

# Q3 & Q4 Cleaned Flights
output_file_q_3_4 = os.path.join(download_path, 'q3_q4 Flights .csv')
temp_file_q_3_4 = drive.CreateFile({'id': '1N1A_i7UGb-mhLNkA1m7FVyKCJKR2ruVQ'})
temp_file_q_3_4.GetContentFile(output_file_q_3_4)

#flights = pd.concat([pd.read_csv(output_file_q_1_2), pd.read_csv(output_file_q_3_4)])
flights = pd.concat([pd.read_csv(output_file_q3), pd.read_csv(output_file_q4)])

# Drop the unnecessary/duplicate columns
unnecessary_columns = ['Year', 'OriginAirportID',
                       'OriginAirportSeqID', 'OriginCityMarketID', 'OriginCountry',
                       'OriginStateFips', 'OriginState', 'OriginStateName',
                       'DestAirportID', 'DestAirportSeqID', 'DestCityMarketID', 
                       'DestCountry', 'DestStateFips', 'DestState', 'DestStateName', 
                       'AirportGroup', 'WacGroup', 'TkCarrierGroup', 'OpCarrierGroup',
                       'MktDistanceGroup', 'ItinGeoType', 'Unnamed: 41']
flights = flights.drop(unnecessary_columns, axis=1)

# Use only the data where customer did not switch carrier, then drop the column
flights = flights[flights['TkCarrierChange'] == 0]
flights = flights.drop(['TkCarrierChange'], axis=1)

# Use only the data where customer did not switch operating company, then drop the column
flights = flights[flights['OpCarrierChange'] == 0]
flights = flights.drop(['OpCarrierChange'], axis=1)

# Use only the data where customer experience didn't change from one company to another, then drop two columns
flights = flights[(flights['RPCarrier'] == flights['TkCarrier']) & (flights['RPCarrier'] == flights['OpCarrier'])]
flights = flights.drop(['RPCarrier', 'OpCarrier'], axis=1)

# Use only the data where # miles flown is equal to # nonstop miles then drop one
flights = flights[flights['MktMilesFlown'] == flights['NonStopMiles']]
flights = flights.drop(['NonStopMiles'], axis=1)

# Use only the data where distance equals miles, then drop one of the columns
flights = flights[(flights['MktDistance'] == flights['MktMilesFlown'])]
flights = flights.drop(['MktDistance'], axis=1)

# Eliminate the upper-bound outlier prices and the ultra-cheap discounted coupon prices
flights = flights[(flights['MktFare'] >= 50) & (flights['MktFare'] <= 1000)]

# Eliminate the airlines that have been reduced to just a few tickets sold due to the above refinement processflights
flights = flights[(flights['TkCarrier'] != 'QX') & (flights['TkCarrier'] != 'OO') & (flights['TkCarrier'] != 'EV')]

# Drop the BulkFare column because it has no variety after all the above refining
flights = flights.drop(['BulkFare'], axis=1)

# Refine the dataset so the user cannot select more than 20 passengers for their purchase
flights = flights[flights['Passengers'] <= 20]

flights
flights['Miles'] = flights['MktMilesFlown']
flights = flights.drop(['MktMilesFlown'], axis=1)

flights['ContiguousUSA'] = flights['MktGeoType']
flights = flights.drop(['MktGeoType'], axis=1)

flights['NumTicketsOrdered'] = flights['Passengers']
flights = flights.drop(['Passengers'], axis=1)

flights['AirlineCompany'] = flights['TkCarrier']
flights = flights.drop(['TkCarrier'], axis=1)

flights['PricePerTicket'] = flights['MktFare']
flights = flights.drop(['MktFare'], axis=1)

flights
flights = flights.reset_index(drop=True)
flights
flights.to_csv('q3_q4 Flights .csv')
#flights.to_csv('q3_q4 Flights .csv')<jupyter_output><empty_output><jupyter_text>COMPRESS INTO ONE FILE<jupyter_code>from google.colab import files
uploaded = files.upload()

import io
df2 = pd.read_csv(io.BytesIO(uploaded['Filename.csv']))
# Dataset is now stored in a Pandas Dataframe
from google.colab import drive
drive.mount('/content/drive')
df = pd.read_csv('/content/q1_q2 Flights .csv')
df2 = pd.read_csv('/content/q3_q4 Flights .csv')
flights = pd.concat([df, df2])
flights
flights = flights.reset_index(drop=True)
flights = flights.drop(['Unnamed: 0'], axis=1)
flights
flights.to_csv('Cleaned_2018_Flights.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1165158371040724, 'fraction_numerical': 0.029034690799396683, 'mean_word_length': 6.0603371783496005, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 12, 'https://': 4, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 9, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '92043', 'license': 'MIT', 'n_tokens_mistral': 2830, 'n_tokens_neox': 2702, 'n_words': 700, 'path': 'ipynb_notebooks/Cleaning_and_Wrangling.ipynb', 'repo_name': 'Zernach/Airline-Price-Prediction', 'size': '7968'}