<jupyter_start><jupyter_text>Airline Revenue Project Analyzing revenue per seat mile in the US commercial airline industry  IntroductionTo complement an investment analysis in the US commercial airline industry, I dedicated some time to research indicators beyond the usual financial data available across different market data sources.During this research, I came across the Airline Data Project (ADP) website, an effort leaded by the MIT Global Airline Industry Program "to better understand the opportunities, risks and challenges" of the industry. The ADP website offers consolidated data regarding revenue, expenses, compensation, traffic, capacity and other financial indicators.With the objective to answer the question "which airline is most efficient in terms of revenue?", I decided to run a quick data project to analyze and compare the two main indicators of efficiency across the industry: TRESM (Total Revenue per Equivalent Seat Mile) and PRESM (Passenger Revenue per Equivalent Seat Mile).For some context, the Revenue per Available Seat Mile is a metric obtained by dividing the operating income by the number of seats available multiplied by the number of miles flown. The number of seats available take into account all the seats across the commercial fleet; optimization of both, number of seats and miles flown, are critical to drive up this metric.Three charts or visualizations were produced in this project, all related to RESM: metrics for 2017, time series for last ten years (2008–2017) and a ratio denominated Price/RESM. The latter calculates the stock price by the current revenue per equivalent seat mile (RESM) and tells how many times revenue a particular airline's stock is trading. Taking as an example the price/earnings ratio (P/E), lower values are preferred over high ratios.As an arbitrary classification for the Price/RESM ratio, I used the following scale to analyze the stocks:- Overvalued: ratios higher than 3- Value: ratios between 1.5 and 3- Undervalued: ratios lower 1.5It is necessary to note that the RESM is expressed in cents (1/100) while stock prices are in USD. To facilitate comparison and to have ratios with natural numbers, RESM is used in cents and not in USD.All the data used in the project was sourced from the ADP website and cleaned for the purpose of this study. Airlines not operating as of 2018 (Continental, Northwest, US Airways, America West, Airtran) were removed, as well as those not listed in the stock market (Frontier). The clean dataset can be viewed at the following [Google Spreadsheet](https://docs.google.com/spreadsheets/d/1vhxS5A3Xh026HTdob0vpF_dvyT493IxhdFON2ptJ2GE)<jupyter_code>import matplotlib
matplotlib.__version__
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
src = 'Source: http://web.mit.edu/airlinedata/www/Revenue&Related.html'<jupyter_output><empty_output><jupyter_text>DataThe following cells read the CSV dataset from Google spreadsheet and loads them into a dataframe.TRESM is for Total Revenue per Equivalent Seat Mile (Ex-Transport)PRESM is for Passenger Revenue per Equivalent Seat MileThe difference in value between them is the additional revenue coming from baggage and cancellation fees and other miscellaneous operating revenues (upgrades, changes, etc).<jupyter_code># loads data from Google spreasheet
tresm_id = '0';
presm_id = '1753077094'
base = 'https://docs.google.com/spreadsheets/d/1pkti0TCovPpGtCYi__cbZwbmpCAh_e478MBWgGZMnOo/export?format=csv&gid='
df_t = pd.read_csv(base + tresm_id, index_col=1)
df_t
df_p = pd.read_csv(base + presm_id, index_col=1)
df_p<jupyter_output><empty_output><jupyter_text>Revenue per Equivalent Seat Mile (2017)<jupyter_code>t = 'Total/Passenger Revenue per Equivalent Seat Mile (2017)'
fig, ax = plt.subplots(figsize=(15, 5))
ax.bar(list(df_t.index), df_t['2017'].sort_values(ascending=False), label='TRESM')
ax.bar(list(df_p.index), df_p['2017'].sort_values(ascending=False), label='PRESM')
ax.annotate(src, xy=(1, 1), xytext=(5.5, -2))
ax.legend()
plt.title(t)
plt.show()<jupyter_output><empty_output><jupyter_text>Revenue per Equivalent Seat Mile (RESM, Ex-Transport) - Time Series (2008-2017)<jupyter_code>def plot_series(df):
    fig, ax = plt.subplots(figsize=(15, 5))
    tickers = df.index.values
    for i in tickers:
        ax.plot(df.loc[i][-10:],'-', label=i)
    plt.title('Total Revenue per Equivalent Seat Mile (RESM, Ex-Transport) - Time Series (2008-2017)')
    ax.legend(loc='center left', bbox_to_anchor=(1, 0.5))
    plt.show()
plot_series(df_t)<jupyter_output><empty_output><jupyter_text>Price/RESM RatioThe following calculate the price to revenue per equivalent seat mile (RESM) ratio.  This metric tells how many times revenue a particular airline's stock is trading.Takings as an example the price/earnings ratio (P/E), lower values are preferred over high ratios.As an arbitrary classification, I used the following scale to analyze the stocks:* Overvalued: ratios higher than 3* Value: ratios between 1.5 and 3* Undervalued: ratios lower 1.5---Note: RESM is expressed in cent dollar (1/100). Stock prices are in USD.  To facilitate comparison and to have ratios with natural numbers, RESM is used in cents and not in USD.po<jupyter_code>df_t['price/resm'] = df_t['price']/df_t['2017']
s = df_t['price/resm'].sort_values(ascending=False)
s
s.plot(kind='bar', sort_columns=True, title='Price/RESM ratio', figsize=(15, 5))
plt.xticks(rotation=0)
ax.annotate(src, xy=(10, 10), xytext=(3, 2.5))
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07973241728439703, 'fraction_numerical': 0.023323088049177362, 'mean_word_length': 5.649038461538462, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 2, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '102717', 'license': 'MIT', 'n_tokens_mistral': 1731, 'n_tokens_neox': 1567, 'n_words': 672, 'path': 'airline_revenue_project.ipynb', 'repo_name': 'ricardo-hdz/airlines-data', 'size': '5531'}