<jupyter_start><jupyter_text>1. What is `execucomp`?This is a database about the US firms' executives and their compensation and benefits. The database starts since 1992 and covers S&P 1,500 firms. So each year, we have around 1,500 large firms.As an example of the database, I will use year 2018 of Execucomp. Due to license, I don't provide the data for public. But you may go to the database room/library to check its availability.The data will be stored as `csv` file and will be imported and analyzed using `pandas` library in Python.<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>To import a csv file by `pandas`, use `read_csv(path_to_csv_file)` function:<jupyter_code>Execucomp = pd.read_csv("data/execucomp2018.csv")<jupyter_output><empty_output><jupyter_text>First few lines/rows of data:<jupyter_code>Execucomp.head()<jupyter_output><empty_output><jupyter_text>2. Discover the data  2.1 FilterUsage: to check a subset of data. For each firm, it will cover not only the CEO but also other directors. For example, in the database, __Microsoft Corp.__ (ticker `MSFT`) has 5 executives in 2018:- <NAME>: CEO- <NAME>: CFO- <NAME>: President and Chief Legal Officer- <NAME>: Executive Vice President of Business Development- <NAME>: Executive VP and President of Microsoft Global Sales, Marketing & Operations<jupyter_code>MSFT = Execucomp[Execucomp['ticker'] == "MSFT"]
MSFT<jupyter_output><empty_output><jupyter_text>How about another high-tech firm: Apple Inc.?<jupyter_code>AAPL = Execucomp[Execucomp['ticker'] == "AAPL"]
AAPL<jupyter_output><empty_output><jupyter_text>So in Apple board, we have a CEO (Tim), a CFO (<NAME>), a COO, one retail manager, and a general one.  2.2 SelectUsage: to select some variables only in the data. Execucomp also offers the gender of each executive, let's see:<jupyter_code>Execucomp[Execucomp['ticker'].isin(["MSFT","AAPL"])][['ticker', 'exec_fullname', 'titleann', 'gender']]<jupyter_output><empty_output><jupyter_text>Next, we care about the compensation of each executive. In Execucomp, we have a variable namely `tdc1`, which is the total compenstation in a year.> Total compensation for the individual year, comprised of the following: Salary, Bonus, Other Annual, Total Value of Restricted Stock Granted, Total Value of Stock Options Granted (using Black-Scholes), Long-Term Incentive Payouts, and All Other Total.<jupyter_code>Execucomp[Execucomp['ticker'].isin(["MSFT","AAPL"])][['ticker', 'exec_fullname', 'titleann', 'gender', 'tdc1']]<jupyter_output><empty_output><jupyter_text>Do you find something? Tim (CEO of Apple) receives less than other directors, but <NAME>	(CEO of MSFT) receives the highest compensation. Let's prove it by code.  2.3 Sort valuesUsage: to sort a column by values.<jupyter_code>MSFT[['ticker', 'exec_fullname', 'titleann', 'gender', 'tdc1']].sort_values('tdc1')
AAPL[['ticker', 'exec_fullname', 'titleann', 'gender', 'tdc1']].sort_values('tdc1')<jupyter_output><empty_output><jupyter_text>2.4 create new variableNow, we go further to see the composition of the total compensation (in thousand dollars).<jupyter_code>TwoFirm = Execucomp[Execucomp['ticker'].isin(["MSFT","AAPL"])][
    ['ticker', 'exec_fullname', 'titleann', 'gender',
    'tdc1', 'salary', 'bonus', 'stock_awards_fv', "option_awards_fv", 'total_curr', 
    "othcomp", 
    #'total_sec', 'rstkgrnt', "option_awards_blk_value", "option_awards_rpt_value", 'allothtot', 'eip_unearn_val'
    #'opt_unex_exer_est_val', 'opt_unex_unexer_est_val', 
    ]
]
TwoFirm<jupyter_output><empty_output><jupyter_text>How many percent the total compensation comes from (fixed) salary and equity parts?<jupyter_code>TwoFirm['salary_pct'] = TwoFirm['salary']/TwoFirm['tdc1']
TwoFirm['stock_pct'] = TwoFirm['stock_awards_fv']/TwoFirm['tdc1']
TwoFirm<jupyter_output><empty_output><jupyter_text>As you can see, most of the executive compensation is from equity (incentive) component, rather than the fixed salary. So, be careful whenever you read that a CEO received only $1 in a year.  2.5 `groupby` and summarize dataSometimes, we wanna group data in groups:- for each firm, find the highest amount of compensation- for each firm, find the average salary- for each firm, find the frequency of genderThen, we may need the `groupby` function.<jupyter_code>TwoFirm.groupby('ticker')['tdc1'].agg(max)
import numpy as np
TwoFirm.groupby('ticker')['tdc1'].agg(np.mean)
TwoFirm.groupby('ticker')['gender'].value_counts()<jupyter_output><empty_output><jupyter_text>3. Time series of compensation componentsIn this section, we will see how the total compensation components change over time across firms.<jupyter_code>CompComponent = pd.read_csv("data/CompComponent.csv")<jupyter_output><empty_output><jupyter_text>This data is the __median__ of all CEOs' compensation components over years.<jupyter_code>CompComponent<jupyter_output><empty_output><jupyter_text>We draw a plot of these time series:<jupyter_code>import matplotlib.pyplot as plt

plt.plot('year', 'salary_pct', data=CompComponent, marker='', color='black', linewidth=2,label="salary")
plt.plot('year', 'stock_pct', data=CompComponent, marker='x', color='blue', linewidth=2, label="stock")
plt.plot('year', 'option_pct', data=CompComponent, marker='o', color='pink', linewidth=2, linestyle='dashed', label="option")
plt.legend()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup