<jupyter_start><jupyter_text>Homework 3. Pandas Important notes1. *When you open this file on GitHub, copy the address to this file from the address bar of your browser. Now you can go to [Google Colab](https://colab.research.google.com/), click `File -> Open notebook -> GitHub`, paste the copied URL and click the search button (the one with the magnifying glass to the right of the search input box). Your personal copy of this notebook will now open on Google Colab.*2. *Do not delete or change variable names in the code cells below. You may add to each cell as many lines of code as you need, just make sure to assign your solution to the predefined variable(s) in the corresponding cell. Failing to do so will make the tests fail.*3. *To save your work, click `File -> Save a copy on GitHub` and __make sure to manually select the correct repository from the dropdown list__.*4. *If you mess up with this file and need to start from scratch, you can always find the original one [here](https://github.com/hse-mlwp-2022/assignment3-template/blob/main/pandas_exercise.ipynb). Just open it in Google Colab (see note 1) and save to your repository (see note 3). Remember to backup your code elsewhere, since this action will overwrite your previous work.* 5. *Exercises 1-4 are mandatory. Your work __will not be graded__ if you fail any one of them. Exercises 5-8 are optional, you can skip them if you want* About the DatasetWe will be using 2019 flight statistics from the United States Department of Transportation’s Bureau of Transportation Statistics (available [here](https://www.transtats.bts.gov/DL_SelectFields.asp?gnoyr_VQ=FMF&QO_fu146_anzr=Nv4%20Pn44vr45) and in your repository as `data/T100_MARKET_ALL_CARRIER.zip`). You can load the dataset in pandas using this link: `https://github.com/hse-mlwp-2022/assignment3-template/raw/main/data/T100_MARKET_ALL_CARRIER.zip`.Each row contains information about a specific route for a given carrier in a given month (e.g., JFK &rarr; LAX on Delta Airlines in January). There are 321,409 rows and 41 columns. Note that you don't need to unzip the file to read it in with `pd.read_csv()`. Exercises 1. Read in the data and convert the column names to lowercase to make them easier to work with.<jupyter_code>import pandas as p
df = p.read_csv("https://github.com/hse-mlwp-2022/assignment3-template/raw/main/data/T100_MARKET_ALL_CARRIER.zip")
df.columns = (col.lower() for col in df.columns)<jupyter_output><empty_output><jupyter_text>2. What columns are in the data? (0.5 point)<jupyter_code>columns = df.columns

print(columns)<jupyter_output>Index(['passengers', 'freight', 'mail', 'distance', 'unique_carrier',
       'airline_id', 'unique_carrier_name', 'unique_carrier_entity', 'region',
       'carrier', 'carrier_name', 'carrier_group', 'carrier_group_new',
       'origin_airport_id', 'origin_airport_seq_id', 'origin_city_market_id',
       'origin', 'origin_city_name', 'origin_state_abr', 'origin_state_fips',
       'origin_state_nm', 'origin_country', 'origin_country_name',
       'origin_wac', 'dest_airport_id', 'dest_airport_seq_id',
       'dest_city_market_id', 'dest', 'dest_city_name', 'dest_state_abr',
       'dest_state_fips', 'dest_state_nm', 'dest_country', 'dest_country_name',
       'dest_wac', 'year', 'quarter', 'month', 'distance_group', 'class',
       'data_source'],
      dtype='object')<jupyter_text>3. How many distinct carrier names are in the dataset? (0.5 point)<jupyter_code>carrier_names = df['unique_carrier_name'].nunique()

print(carrier_names)<jupyter_output>318<jupyter_text>4. Calculate the totals of the `freight`, `mail`, and `passengers` columns for flights from the United Kingdom to the United States. (1 point)<jupyter_code>freight_total = df[(df['origin_country_name'] == 'United Kingdom') & (df['dest_country_name'] == 'United States')]['freight'].sum()
mail_total = df[(df['origin_country_name'] == 'United Kingdom') & (df['dest_country_name'] == 'United States')]['mail'].sum()
passengers_total = df[(df['origin_country_name'] == 'United Kingdom') & (df['dest_country_name'] == 'United States')]['passengers'].sum()

print(f"freight total: {freight_total}")
print(f"mail total: {mail_total}")
print(f"passengers total: {passengers_total}")<jupyter_output>freight total: 903296879.0
mail total: 29838395.0
passengers total: 10685608.0<jupyter_text>5. Which 10 carriers flew the most passengers out of the United States to another country? (1.5 points)The result should be a Python iterable, e.g. a list or a corresponding pandas object<jupyter_code>top_10_by_passengers = df[(df['origin_country_name'] == 'United States') & (df['dest_country_name'] != 'United States')].groupby('unique_carrier_name')['passengers'].sum().nlargest(10).index.tolist()

print(f"List of top 10 carriers with max number of passengers flown out of US: {top_10_by_passengers}")<jupyter_output>List of top 10 carriers with max number of passengers flown out of US: ['American Airlines Inc.', 'United Air Lines Inc.', 'Delta Air Lines Inc.', 'JetBlue Airways', 'British Airways Plc', 'Lufthansa German Airlines', 'Westjet', 'Air Canada', 'Southwest Airlines Co.', 'Virgin Atlantic Airways']<jupyter_text>6. Between which two cities were the most passengers flown? Make sure to account for both directions. (1.5 points)<jupyter_code>def direction(row):
  origin = row['origin_city_name']
  dest = row['dest_city_name']
  if origin == dest:
    return 'none'
  elif origin > dest:
    k = origin
    origin = dest 
    dest = k 
  return '?'.join([origin, dest])
df['direction'] = df[['origin_city_name', 'dest_city_name']].apply(direction, axis=1)
top_direction = df.groupby('direction')['passengers'].sum().idxmax()
top_route_origin_city = df[df['origin_city_name']==top_direction.split('?')[0]]['origin_city_name'].iloc[0] 
top_route_dest_city = df[df['origin_city_name']==top_direction.split('?')[1]]['origin_city_name'].iloc[0] 
top_route_passengers_count = df.groupby('direction')['passengers'].sum().max() 
print(f"top route is '{top_route_origin_city} - {top_route_dest_city}' with traffic of {top_route_passengers_count} passengers")<jupyter_output>top route is 'Chicago, IL - New York, NY' with traffic of 4131579.0 passengers<jupyter_text>7. Find the top 3 carriers for the pair of cities found in 6 and calculate the percentage of passengers each accounted for. (2 points)The result should be a pandas dataframe object with two columns: 1. carrier name (string)2. percentage of passengers (float in the range of 0-100)<jupyter_code>all_passengers_series = df[df['direction']==top_direction].groupby('unique_carrier_name')['passengers'].sum()
all_passengers = all_passengers_series.sum()
(df[df['direction']==top_direction].groupby('unique_carrier_name')['passengers'].sum().nlargest(3) / all_passengers * 100).reset_index()
top_3_carriers_df = (df[df['direction']==top_direction].groupby('unique_carrier_name')['passengers'].sum().nlargest(3) / all_passengers * 100)
top_3_carriers_df<jupyter_output><empty_output><jupyter_text>8. Find the percentage of international travel per country using total passengers on class F flights. (3 points)<jupyter_code>international_travel_per_country = ... # Place your code here instead of '...'

international_travel_per_country<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1199616122840691, 'fraction_numerical': 0.02070194680559364, 'mean_word_length': 6.048309178743962, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 32, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '667395', 'license': 'MIT', 'n_tokens_mistral': 2403, 'n_tokens_neox': 2249, 'n_words': 715, 'path': 'pandas_exercise.ipynb', 'repo_name': 'Tima1117/assignment2-template', 'size': '7294'}