<jupyter_start><jupyter_text>2017 and 2018 Flight Delay Data Wrangling by <NAME>The purpose of this data wrangling effort was to reorganize single flight records into routes to map them in Tableau. Tableau requires a record (row) for both origin and destination to map a route, as well as an attribute to identify the route. The attributes created and/or changed to achieve the necessary csv structure are as follows:- origin_dest: to identify the record as origin or destination- path_id: combined ORIGIN and DEST (airport codes) to create an attribute to identify each route- origin/dest_city_name --> city_name: as each route has (2) records - (1) for origin, (1) for destination - generalized this attribute to allow clean concatenation- ORIGIN/DEST --> airport_code: generalized this attribute to allow clean concatenation<jupyter_code># Format CSV to create origin-destination route map; requires (2) records/rows per route - (1) for origin, (1) for destination

import pandas as pd

spider_df = pd.read_csv("/Users/amber/Desktop/GitHub/flight_delay_prediction/data_manipulation_modeling/data_clean/2017_TUS.csv")
spider_df
# create path_id field to id the route
spider_df['path_id'] = spider_df['ORIGIN'] + '_' + spider_df['DEST']
spider_df
# Build origin df

origin_df = spider_df[['origin_city_name', 'FL_DATE', 'OP_CARRIER', 'OP_CARRIER_FL_NUM', 'ORIGIN', 'CRS_DEP_TIME', 'DEP_TIME', 'DEP_DELAY', 'TAXI_OUT', 'WHEELS_OFF', 'WHEELS_ON', 'TAXI_IN', 'CRS_ARR_TIME', 'ARR_TIME', 'ARR_DELAY', 'CANCELLED', 'CANCELLATION_CODE', 'DIVERTED', 'CRS_ELAPSED_TIME', 'ACTUAL_ELAPSED_TIME', 'AIR_TIME', 'DISTANCE', 'CARRIER_DELAY', 'WEATHER_DELAY', 'NAS_DELAY', 'SECURITY_DELAY', 'LATE_AIRCRAFT_DELAY', 'CRS_DEP_HM', 'DEP_TIME_HM', 'WHEELS_OFF_HM', 'WHEELS_ON_HM', 'CRS_ARR_TIME_HM', 'ARR_TIME_HM', 'day_of_week', 'DELAY', 'path_id']].copy()

# create new column to id origin airport
origin_df['origin_dest'] = 'origin'

# rename key origin columns to be generalized between origin and desintation
origin_df.rename(columns={"origin_city_name":"city_name", "ORIGIN":"airport_code"}, inplace=True)
origin_df
# Build destination df

destination_df = spider_df[['dest_city_name', 'FL_DATE', 'OP_CARRIER', 'OP_CARRIER_FL_NUM', 'DEST', 'CRS_DEP_TIME', 'DEP_TIME', 'DEP_DELAY', 'TAXI_OUT', 'WHEELS_OFF', 'WHEELS_ON', 'TAXI_IN', 'CRS_ARR_TIME', 'ARR_TIME', 'ARR_DELAY', 'CANCELLED', 'CANCELLATION_CODE', 'DIVERTED', 'CRS_ELAPSED_TIME', 'ACTUAL_ELAPSED_TIME', 'AIR_TIME', 'DISTANCE', 'CARRIER_DELAY', 'WEATHER_DELAY', 'NAS_DELAY', 'SECURITY_DELAY', 'LATE_AIRCRAFT_DELAY', 'CRS_DEP_HM', 'DEP_TIME_HM', 'WHEELS_OFF_HM', 'WHEELS_ON_HM', 'CRS_ARR_TIME_HM', 'ARR_TIME_HM', 'day_of_week', 'DELAY', 'path_id']].copy()

# create new column to id destination airport
destination_df['origin_dest'] = 'dest'

# rename key destination columns to be generalized between origin and desintation
destination_df.rename(columns={"dest_city_name":"city_name", "DEST":"airport_code"}, inplace=True)
destination_df
# Combine origin and destination dfs, sort by multiple values to match origin-destination records

frames = [origin_df, destination_df]

spider_map_df = pd.concat(frames, sort=False)
spider_map_df.sort_values(["FL_DATE", "DEP_TIME", "ARR_TIME_HM"], ascending=[True, True, True], inplace=True)
spider_map_df
spider_map_df.to_csv(r'/Users/amber/Desktop/GitHub/flight_delay_prediction/data_manipulation_modeling/data_viz_manipulation/2017_TUS_SpiderMap.csv', index=False)
import pandas as pd

spider_df_2018 = pd.read_csv("/Users/amber/Desktop/GitHub/flight_delay_prediction/data_manipulation_modeling/data_clean/2018_TUS.csv")
spider_df_2018
# create path_id field to id the route
spider_df_2018['path_id'] = spider_df_2018['ORIGIN'] + '_' + spider_df_2018['DEST']
spider_df_2018
# Build origin df

origin_df_2018 = spider_df_2018[['origin_city_name', 'FL_DATE', 'OP_CARRIER', 'OP_CARRIER_FL_NUM', 'ORIGIN', 'CRS_DEP_TIME', 'DEP_TIME', 'DEP_DELAY', 'TAXI_OUT', 'WHEELS_OFF', 'WHEELS_ON', 'TAXI_IN', 'CRS_ARR_TIME', 'ARR_TIME', 'ARR_DELAY', 'CANCELLED', 'CANCELLATION_CODE', 'DIVERTED', 'CRS_ELAPSED_TIME', 'ACTUAL_ELAPSED_TIME', 'AIR_TIME', 'DISTANCE', 'CARRIER_DELAY', 'WEATHER_DELAY', 'NAS_DELAY', 'SECURITY_DELAY', 'LATE_AIRCRAFT_DELAY', 'CRS_DEP_HM', 'DEP_TIME_HM', 'WHEELS_OFF_HM', 'WHEELS_ON_HM', 'CRS_ARR_TIME_HM', 'ARR_TIME_HM', 'day_of_week', 'DELAY', 'path_id']].copy()

# create new column to id origin airport
origin_df_2018['origin_dest'] = 'origin'

# rename key origin columns to be generalized between origin and desintation
origin_df_2018.rename(columns={"origin_city_name":"city_name", "ORIGIN":"airport_code"}, inplace=True)
origin_df_2018
# Build destination df

destination_df_2018 = spider_df_2018[['dest_city_name', 'FL_DATE', 'OP_CARRIER', 'OP_CARRIER_FL_NUM', 'DEST', 'CRS_DEP_TIME', 'DEP_TIME', 'DEP_DELAY', 'TAXI_OUT', 'WHEELS_OFF', 'WHEELS_ON', 'TAXI_IN', 'CRS_ARR_TIME', 'ARR_TIME', 'ARR_DELAY', 'CANCELLED', 'CANCELLATION_CODE', 'DIVERTED', 'CRS_ELAPSED_TIME', 'ACTUAL_ELAPSED_TIME', 'AIR_TIME', 'DISTANCE', 'CARRIER_DELAY', 'WEATHER_DELAY', 'NAS_DELAY', 'SECURITY_DELAY', 'LATE_AIRCRAFT_DELAY', 'CRS_DEP_HM', 'DEP_TIME_HM', 'WHEELS_OFF_HM', 'WHEELS_ON_HM', 'CRS_ARR_TIME_HM', 'ARR_TIME_HM', 'day_of_week', 'DELAY', 'path_id']].copy()

# create new column to id destination airport
destination_df_2018['origin_dest'] = 'dest'

# rename key destination columns to be generalized between origin and desintation
destination_df_2018.rename(columns={"dest_city_name":"city_name", "DEST":"airport_code"}, inplace=True)
destination_df_2018
frames_2018 = [origin_df_2018, destination_df_2018]

spider_map_df_2018 = pd.concat(frames_2018, sort=False)
spider_map_df_2018.sort_values(["FL_DATE", "DEP_TIME", "ARR_TIME_HM"], ascending=[True, True, True], inplace=True)
spider_map_df_2018
spider_map_df_2018.to_csv(r'/Users/amber/Desktop/GitHub/flight_delay_prediction/data_manipulation_modeling/data_viz_manipulation/2018_TUS_SpiderMap.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13325530847684333, 'fraction_numerical': 0.021066711252298947, 'mean_word_length': 8.887603305785124, 'pattern_counts': {'":': 8, '<': 6, '<?xml version=': 0, '>': 8, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '157373', 'license': 'MIT', 'n_tokens_mistral': 2471, 'n_tokens_neox': 2242, 'n_words': 474, 'path': 'data_manipulation_modeling/data_viz_manipulation/anr_viz.ipynb', 'repo_name': 'tarakp/flightdelays', 'size': '5988'}