import numpy as np
import pandas as pd
import matplotlib.pyplot as pltWorkshop 3 - SOLUTION, Algorithms and Data Analysis
Data management for panel data with pandas
Abstract
This is an INDIVIDUAL workshop. We learn the data-management skills that come before any financial model: importing, exploring, merging, filtering, transforming and summarizing a panel dataset with the historical financial statements of all US public firms. We calculate financial variables and ratios correctly within each firm, build industry-year summary tables, reshape data between long and wide formats, and treat extreme values with winsorization.
1 General directions
Work in a new Google Colab notebook named W3-Algorithms-YourFirstName-YourLastName, and share it with cdorante@tec.mx (Edit privileges). Same rules as before: re-type and run the code, write your notes, predict before you run, and verify your results. If you use an LLM, write your algorithm first and paste your prompt.
Grading: notebook (60%) + Canvas Quiz W3 (40%), due on the same date as the workshop.
2 Data structures in Finance
In Finance, datasets usually have one of the following structures:
- Time series: many periods for one subject (a company, an index, a currency). With several subjects, each subject is usually a column. This is the wide format we used in Workshop 2 with the monthly prices:
ts = pd.DataFrame({"p_stock1": [10, 11], "r_stock1": [0.02, 0.10],
"p_stock2": [20, 21], "r_stock2": [0.01, 0.05]},
index=["2014m1", "2014m2"])
ts| p_stock1 | r_stock1 | p_stock2 | r_stock2 | |
|---|---|---|---|---|
| 2014m1 | 10 | 0.02 | 20 | 0.01 |
| 2014m2 | 11 | 0.10 | 21 | 0.05 |
- Cross-section: many subjects for one period. For example, some financial ratios of Mexican firms in the last quarter of 2016:
cs = pd.DataFrame({"Ticker": ["ALFAA.MX", "AMXB.MX"], "ROA": [0.023, 0.015],
"ATO": [0.90, 1.1], "ProfitMargin": [0.15, 0.10]})
cs| Ticker | ROA | ATO | ProfitMargin | |
|---|---|---|---|---|
| 0 | ALFAA.MX | 0.023 | 0.9 | 0.15 |
| 1 | AMXB.MX | 0.015 | 1.1 | 0.10 |
- Panel data: many subjects, and several periods for each subject. Each row is one subject-period (for example, one firm-year). This is the long format:
panel = pd.DataFrame({"Ticker": ["ALFAA.MX", "ALFAA.MX", "BIMBOA.MX", "BIMBOA.MX"],
"quarter": ["2014q1", "2014q2", "2014q1", "2014q2"],
"ROA": [0.20, 0.21, 0.15, 0.20]})
panel| Ticker | quarter | ROA | |
|---|---|---|---|
| 0 | ALFAA.MX | 2014q1 | 0.20 |
| 1 | ALFAA.MX | 2014q2 | 0.21 |
| 2 | BIMBOA.MX | 2014q1 | 0.15 |
| 3 | BIMBOA.MX | 2014q2 | 0.20 |
Most of the data used in corporate finance and in quantitative investing is panel data. Panel data requires special care: when we calculate changes or lags, we must never mix the data of one firm with the data of another firm. This is the most important lesson of this workshop.
3 Importing the datasets
We will work with 2 datasets:
- usdata.csv: a panel dataset with the annual financial statements of all US public firms (firms that issue shares in the stock market).
- usfirms.csv: a catalog (cross-section) with general information of each firm: name, status (active or cancelled) and industry.
def import_csv(file, base_url="https://www.apradie.com/datos/"):
"""Downloads a file from base_url and import it into a data frame ."""
import os
import io
import requests
import pandas
headers = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36"}
try:
response = requests.get(base_url + file, headers= headers)
response.raise_for_status() # Error if the response is 4xx or 5xx
#Reading the content un a dataframe:
df = pd.read_csv(io.StringIO(response.text))
print(f"File {file} was downloaded and imported as a data frame")
except requests.exceptions.HTTPError as e:
print(f"HTTP Error occured: {e}")
raise
except requests.exceptions.RequestException as e:
print(f"An error ocurred dufing the request: {e}")
raise
return df
usdata = import_csv("usdata.csv")
usfirms = import_csv("usfirms.csv")
usdata.head()File usdata.csv was downloaded and imported as a data frame
File usfirms.csv was downloaded and imported as a data frame| firm | year | revenue | cogs | sgae | depreciation | otherincome | extraordinaryitems | finexp | incometax | ... | totalliabilities | currentliabilities | accountspayable | shortdebt | longdebt | retainedearnings | adjprice | originalprice | sharesoutstanding | fixedassets | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | A | 2000 | 10773000.0 | 5522000.0 | 3703000.0 | 495000.0 | 111000.0 | 0.0 | 0.0 | 407000.0 | ... | 3160000.0 | 2758000.0 | 857000.0 | 830000.0 | 0.0 | 757000.0 | 33.102282 | 54.75 | 456366.381 | 1741000.0 |
| 1 | A | 2001 | 8396000.0 | 5166000.0 | 3274000.0 | 734000.0 | 301000.0 | 580000.0 | 0.0 | -71000.0 | ... | 2327000.0 | 2002000.0 | 386000.0 | 761000.0 | 0.0 | 931000.0 | 17.237371 | 28.51 | 463695.160 | 1848000.0 |
| 2 | A | 2002 | 6010000.0 | 3694000.0 | 3188000.0 | 735000.0 | 60000.0 | -10000.0 | 0.0 | -525000.0 | ... | 3576000.0 | 2181000.0 | 305000.0 | 574000.0 | 1150000.0 | -101000.0 | 10.858758 | 17.96 | 467024.421 | 1579000.0 |
| 3 | A | 2003 | 6056000.0 | 3762000.0 | 2657000.0 | 362000.0 | 35000.0 | -268000.0 | 0.0 | 1100000.0 | ... | 3473000.0 | 1906000.0 | 441000.0 | 311000.0 | 1150000.0 | -2159000.0 | 17.678735 | 29.24 | 476149.083 | 1447000.0 |
| 4 | A | 2004 | 7181000.0 | 4058000.0 | 2445000.0 | 292000.0 | 54000.0 | 0.0 | 0.0 | 91000.0 | ... | 3487000.0 | 1871000.0 | 441000.0 | 261000.0 | 1150000.0 | -1810000.0 | 14.571050 | 24.10 | 486841.087 | 1258000.0 |
5 rows × 28 columns
We wrote a small function to avoid repeating the same download code twice. Whenever you copy and paste a block of code to change only one value, consider writing a function.
3.1 Data dictionaries
All $ amounts are in thousands of US dollars.
| Variable | Description |
|---|---|
| firm | Firm ticker: the unique code of the company |
| year | Fiscal year |
| revenue | Total sales of the firm in the fiscal year |
| cogs | Cost of goods sold in the fiscal year: variable costs of the products sold |
| sgae | Sales, general and administrative expenses in the fiscal year |
| depreciation | Depreciation and amortization in the fiscal year |
| otherincome | Other operating income/expenses that are not directly from the core operations of the firm |
| extraordinaryitems | Extraordinary income/expenses not related to regular operations |
| finexp | Financial expenses: interest expenses paid in the year (generated by loans) |
| incometax | Income tax paid in the year |
| cashflowoper | Cash flow generated by operations during the year |
| cashflowfin | Cash flow from financing activities during the year |
| cashflowinv | Cash flow from investing activities during the year |
| cashinbanks | Cash balance in banks at the end of the year |
| totalassets | Total assets of the firm at the end of the year |
| currentassets | Current assets of the firm at the end of the year |
| accountsreceivables | Accounts receivable at the end of the year |
| inventory | Inventory at the end of the year |
| totalliabilities | Total liabilities of the firm at the end of the year |
| currentliabilities | Current liabilities of the firm at the end of the year |
| accountspayable | Accounts payable at the end of the year |
| shortdebt | Short-term financial debt at the end of the year |
| longdebt | Long-term financial debt at the end of the year (loans to be paid in more than 1 year) |
| retainedearnings | Retained earnings at the end of the year |
| adjprice | Stock price at the end of the year, adjusted for stock splits and dividends; used to calculate stock returns |
| originalprice | Historical stock price (not adjusted); used to calculate the historical market value |
| sharesoutstanding | Historical number of shares outstanding |
| fixedassets | Fixed assets at the end of the year |
| Variable | Description |
|---|---|
| empresa | Firm ticker: the unique code of the company |
| Nombre | Name of the firm |
| status | Status of the firm: active or cancelled |
| partind | Percentage participation in the S&P 500 index |
| naics1 | North American Industry Classification System: level 1 (the broadest industry) |
| naics2 | North American Industry Classification System: level 2 |
| SectorEconomatica | Economatica industry classification |
For income-statement variables (revenue, cogs, etc.), the value is the amount generated during the fiscal year. For balance-sheet variables (totalassets, longdebt, etc.), the value is the balance at the end of the fiscal year. This difference matters when we build ratios: a flow of the year (like earnings) should be compared with the balance at the beginning of the year, which is the balance at the end of the previous year.
4 Exploring a dataset
Before any analysis, answer these questions: How many rows and columns? What are the types of the columns? How many missing values? How many subjects and periods?
print(usdata.shape)
usdata.info()(62453, 28)
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 62453 entries, 0 to 62452
Data columns (total 28 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 firm 62453 non-null object
1 year 62453 non-null int64
2 revenue 62393 non-null float64
3 cogs 62393 non-null float64
4 sgae 62393 non-null float64
5 depreciation 62453 non-null float64
6 otherincome 62393 non-null float64
7 extraordinaryitems 62392 non-null float64
8 finexp 62398 non-null float64
9 incometax 62451 non-null float64
10 cashflowoper 62366 non-null float64
11 cashflowfin 62366 non-null float64
12 cashflowinv 62366 non-null float64
13 cashinbanks 51416 non-null float64
14 totalassets 62452 non-null float64
15 currentassets 60658 non-null float64
16 accountsreceivables 51416 non-null float64
17 inventory 51416 non-null float64
18 totalliabilities 62451 non-null float64
19 currentliabilities 51412 non-null float64
20 accountspayable 51393 non-null float64
21 shortdebt 51393 non-null float64
22 longdebt 57723 non-null float64
23 retainedearnings 61901 non-null float64
24 adjprice 59594 non-null float64
25 originalprice 59626 non-null float64
26 sharesoutstanding 61717 non-null float64
27 fixedassets 62393 non-null float64
dtypes: float64(26), int64(1), object(1)
memory usage: 13.3+ MBprint("Number of firms:", usdata["firm"].nunique())
print("Years:", usdata["year"].min(), "to", usdata["year"].max())
print("Missing values in revenue:", usdata["revenue"].isna().sum())Number of firms: 5609
Years: 2000 to 2023
Missing values in revenue: 60nunique() counts the distinct values of a column, and isna().sum() counts the missing values (NaN): isna() returns True/False for each row, and the sum counts the True values. The dataset has one row for each firm and each year, even for the years before the firm went public; those rows are empty (NaN). Then, the first year with non-empty data is the year when the firm started to trade in the stock market.
describe() shows descriptive statistics of the numeric columns. We transpose it (.T) so that each variable is a row:
usdata[["revenue", "totalassets", "originalprice"]].describe().T| count | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|
| revenue | 62393.0 | 4.989409e+06 | 1.936746e+07 | -4.214926e+06 | 134790.00 | 769432.00 | 2.974489e+06 | 6.481250e+08 |
| totalassets | 62452.0 | 1.559356e+07 | 1.114561e+08 | 0.000000e+00 | 364028.75 | 1640936.50 | 6.017116e+06 | 4.325437e+09 |
| originalprice | 59626.0 | 4.274569e+01 | 1.140603e+02 | 1.500000e-03 | 10.13 | 24.32 | 4.656188e+01 | 7.000450e+03 |
5 Merging datasets
The panel usdata does not have the name, the status or the industry of each firm. That information is in usfirms. Both datasets have a common column: the firm ticker, but it has different names: firm in usdata and empresa in usfirms.
We do a left join: we keep all the rows of the left dataset (usdata), and we pull the matching columns from the right dataset (usfirms). This is exactly what VLOOKUP does in Excel.
merged = pd.merge(usdata,
usfirms[["empresa", "Nombre", "status", "naics1"]],
left_on="firm", right_on="empresa", how="left")
merged = merged.drop(columns="empresa") # drop the redundant ticker column
merged.head()| firm | year | revenue | cogs | sgae | depreciation | otherincome | extraordinaryitems | finexp | incometax | ... | shortdebt | longdebt | retainedearnings | adjprice | originalprice | sharesoutstanding | fixedassets | Nombre | status | naics1 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | A | 2000 | 10773000.0 | 5522000.0 | 3703000.0 | 495000.0 | 111000.0 | 0.0 | 0.0 | 407000.0 | ... | 830000.0 | 0.0 | 757000.0 | 33.102282 | 54.75 | 456366.381 | 1741000.0 | Agilent Technologies, Inc | active | Manufacturing |
| 1 | A | 2001 | 8396000.0 | 5166000.0 | 3274000.0 | 734000.0 | 301000.0 | 580000.0 | 0.0 | -71000.0 | ... | 761000.0 | 0.0 | 931000.0 | 17.237371 | 28.51 | 463695.160 | 1848000.0 | Agilent Technologies, Inc | active | Manufacturing |
| 2 | A | 2002 | 6010000.0 | 3694000.0 | 3188000.0 | 735000.0 | 60000.0 | -10000.0 | 0.0 | -525000.0 | ... | 574000.0 | 1150000.0 | -101000.0 | 10.858758 | 17.96 | 467024.421 | 1579000.0 | Agilent Technologies, Inc | active | Manufacturing |
| 3 | A | 2003 | 6056000.0 | 3762000.0 | 2657000.0 | 362000.0 | 35000.0 | -268000.0 | 0.0 | 1100000.0 | ... | 311000.0 | 1150000.0 | -2159000.0 | 17.678735 | 29.24 | 476149.083 | 1447000.0 | Agilent Technologies, Inc | active | Manufacturing |
| 4 | A | 2004 | 7181000.0 | 4058000.0 | 2445000.0 | 292000.0 | 54000.0 | 0.0 | 0.0 | 91000.0 | ... | 261000.0 | 1150000.0 | -1810000.0 | 14.571050 | 24.10 | 486841.087 | 1258000.0 | Agilent Technologies, Inc | active | Manufacturing |
5 rows × 31 columns
5.1 Always check a merge
A left join must return exactly the same number of rows as the left dataset. If the right dataset had a repeated ticker, every matching row of the left dataset would be duplicated, silently. We check both things with assert:
assert usfirms["empresa"].is_unique, "usfirms has repeated tickers!"
assert len(merged) == len(usdata), "The merge changed the number of rows!"
print("Rows usdata:", usdata.shape, " rows merged:", merged.shape)
print("Firms without a match in usfirms:", merged["Nombre"].isna().sum(), "rows")Rows usdata: (62453, 28) rows merged: (62453, 31)
Firms without a match in usfirms: 0 rows
WarningA silent error: merges that duplicate rows
If a key is repeated in the right table, merge repeats the rows of the left table without any warning. All your later sums and averages would be wrong. Always check is_unique on the key of the right table, and compare the number of rows before and after the merge.
6 Selecting, filtering and creating columns
We select columns with a list of names, and we filter rows with boolean conditions (as in Workshop 2):
active = merged[merged["status"] == "active"]
big_2023 = merged[(merged["year"] == 2023) & (merged["totalassets"] > 100_000_000)]
print(len(active), len(big_2023))
big_2023[["firm", "Nombre", "totalassets"]].head()43073 93| firm | Nombre | totalassets | |
|---|---|---|---|
| 186 | AAPL | Apple Inc | 352583000.0 |
| 230 | ABBV | Abbvie Inc | 134711000.0 |
| 583 | ACF_old | General Motors Financial Company, Inc | 132011000.0 |
| 1499 | AFL | Aflac Inc | 126724000.0 |
| 1842 | AIG | American Intl Group, Inc | 539306000.0 |
To combine conditions in pandas, we use & (and), | (or) and ~ (not), and each condition must be in parentheses. The Python words and/or do not work with data frame columns.
New columns are calculated with vectorized operations, row by row:
merged["grossprofit"] = merged["revenue"] - merged["cogs"]
merged[["firm", "year", "revenue", "cogs", "grossprofit"]].dropna().head(3)| firm | year | revenue | cogs | grossprofit | |
|---|---|---|---|---|---|
| 0 | A | 2000 | 10773000.0 | 5522000.0 | 5251000.0 |
| 1 | A | 2001 | 8396000.0 | 5166000.0 | 3230000.0 |
| 2 | A | 2002 | 6010000.0 | 3694000.0 | 2316000.0 |
7 Grouping: summary tables
groupby splits the data into groups, applies a function to each group, and combines the results. It is the pandas version of a pivot table in Excel.
# Total revenue of all firms in each year (in billions of dollars):
rev_by_year = merged.groupby("year")["revenue"].sum() / 1_000_000
rev_by_year.tail()year
2019 16378.886831
2020 15662.475472
2021 18375.103165
2022 20776.048144
2023 21254.103133
Name: revenue, dtype: float64With agg we can calculate several statistics at the same time, and give names to the results:
summary = merged.groupby("naics1").agg(
n_obs=("revenue", "count"),
median_revenue=("revenue", "median"),
total_assets=("totalassets", "sum"))
summary.sort_values("n_obs", ascending=False).head()| n_obs | median_revenue | total_assets | |
|---|---|---|---|
| naics1 | |||
| Manufacturing | 24301 | 580222.0 | 1.626045e+11 |
| Finance and Insurance | 13076 | 434319.5 | 6.186014e+11 |
| Information | 4516 | 720605.0 | 4.869501e+10 |
| Retail Trade | 3175 | 3086608.0 | 2.520594e+10 |
| Mining, Quarrying, and Oil and Gas Extraction | 2659 | 809974.0 | 1.553227e+10 |
Each line inside agg reads: new_column=("existing_column", "function"). We can also group by two columns, for example industry and year:
by_ind_year = merged.groupby(["naics1", "year"])["revenue"].median()
by_ind_year.head()naics1 year
- 2016 28804.0
2017 159879.0
2018 388858.0
2019 718019.0
2020 803287.0
Name: revenue, dtype: float648 Lags and changes in panel data: groupby + shift
To calculate a growth rate, a return or a ratio with a beginning-of-year balance, we need the value of the previous year of the same firm. shift(1) moves a column one row down. But in a panel, the previous row of the first year of a firm belongs to another firm! Look at this small example:
demo = pd.DataFrame({"firm": ["A", "A", "A", "B", "B", "B"],
"year": [2021, 2022, 2023, 2021, 2022, 2023],
"assets": [100, 110, 121, 50, 55, 60]})
demo["assets_lag_WRONG"] = demo["assets"].shift(1)
demo["assets_lag"] = demo.groupby("firm")["assets"].shift(1)
demo| firm | year | assets | assets_lag_WRONG | assets_lag | |
|---|---|---|---|---|---|
| 0 | A | 2021 | 100 | NaN | NaN |
| 1 | A | 2022 | 110 | 100.0 | 100.0 |
| 2 | A | 2023 | 121 | 110.0 | 110.0 |
| 3 | B | 2021 | 50 | 121.0 | NaN |
| 4 | B | 2022 | 55 | 50.0 | 50.0 |
| 5 | B | 2023 | 60 | 55.0 | 55.0 |
The WRONG column assigns the assets of firm A in 2023 (121) as the previous assets of firm B in 2021. Any growth or return calculated with it would be garbage. With groupby("firm"), the shift is done within each firm, and the first year of each firm correctly gets NaN.
ImportantThe golden rule of panel data
- Sort the data by firm and year:
df = df.sort_values(["firm", "year"]). - Always calculate lags, changes and returns within each firm:
df.groupby("firm")["x"].shift(1).
9 Reshaping: long and wide formats
Sometimes we need a panel in wide format: one row per year and one column per firm (as the price file of Workshop 2). pivot converts long into wide, and melt converts wide into long:
wide = demo.pivot(index="year", columns="firm", values="assets")
print(wide)
long_again = wide.reset_index().melt(id_vars="year", var_name="firm", value_name="assets")
print(long_again)firm A B
year
2021 100 50
2022 110 55
2023 121 60
year firm assets
0 2021 A 100
1 2022 A 110
2 2023 A 121
3 2021 B 50
4 2022 B 55
5 2023 B 60reset_index() converts the index (the years) back into a regular column, which melt needs.
10 CHALLENGE 1: Apple’s financial performance
Using the merged dataset:
- Create a data frame
applewith the rows of Apple (ticker AAPL) and non-missing revenue, sorted by year. - Calculate
grossprofit=revenue-cogs,ebit=grossprofit-sgae-depreciation, andmarketvalue=originalprice\times sharesoutstanding. We use the original (not adjusted) price because the number of shares is the historical number. - Calculate the annual growth rate of revenue, grossprofit and ebit (\frac{X_t}{X_{t-1}}-1). Only one firm is in this data frame, so a simple
shift(1)is fine here. If the current or the previous value is negative, set the growth toNaN(a “growth” from a negative number has no meaning). - Create a growth index for revenue, grossprofit, ebit and marketvalue: the value of each variable divided by its value in the first year.
- Show a plot of the growth indexes over the years, and a table with the annual growth rates.
- Provide an insight: what can you say about Apple’s performance in terms of revenue, gross profit, ebit and market value? Did the market value grow faster than the operating results? What could explain it?
10.1 SOLUTION: Challenge 1
apple = merged[(merged["firm"] == "AAPL") & merged["revenue"].notna()].copy()
apple = apple.sort_values("year")
apple["grossprofit"] = apple["revenue"] - apple["cogs"]
apple["ebit"] = apple["grossprofit"] - apple["sgae"] - apple["depreciation"]
apple["marketvalue"] = apple["originalprice"] * apple["sharesoutstanding"]
for var in ["revenue", "grossprofit", "ebit"]:
previous = apple[var].shift(1)
growth = apple[var] / previous - 1
# growth has no meaning when the current or the previous value is negative:
growth[(apple[var] < 0) | (previous < 0)] = np.nan
apple[var + "_growth"] = growth
apple[["year", "revenue_growth", "grossprofit_growth", "ebit_growth"]].round(4).tail(8)| year | revenue_growth | grossprofit_growth | ebit_growth | |
|---|---|---|---|---|
| 179 | 2016 | -0.0773 | -0.1000 | -0.1573 |
| 180 | 2017 | 0.0630 | 0.0466 | 0.0220 |
| 181 | 2018 | 0.1586 | 0.1548 | 0.1557 |
| 182 | 2019 | -0.0204 | -0.0338 | -0.0983 |
| 183 | 2020 | 0.0551 | 0.0667 | 0.0369 |
| 184 | 2021 | 0.3326 | 0.4562 | 0.6436 |
| 185 | 2022 | 0.0779 | 0.1174 | 0.0963 |
| 186 | 2023 | -0.0280 | -0.0096 | -0.0430 |
Explanation.
.copy()creates an independent data frame. Without it, pandas may show aSettingWithCopyWarningwhen we add columns to a filtered data frame, because it does not know whether we want to modify the original data.merged["revenue"].notna()keeps only the years with data (Apple’s empty rows would produce missing growth rates).- We use a loop over variable names to avoid writing the same 4 lines three times.
var + "_growth"builds the name of the new column:"revenue_growth", etc. growth[(apple[var] < 0) | (previous < 0)] = np.nanassignsNaNto the rows where either condition is True.
first = apple.iloc[0] # the first year of data
for var in ["revenue", "grossprofit", "ebit", "marketvalue"]:
apple[var + "_index"] = apple[var] / first[var]
apple.plot(x="year", y=["revenue_index", "grossprofit_index", "ebit_index", "marketvalue_index"],
figsize=(10, 5), logy=True, title="Apple: growth indexes (first year = 1)")
plt.ylabel("Times the value of the first year (log scale)")
plt.grid(alpha=0.3)
plt.show()
last = apple.iloc[-1]
for var in ["revenue", "grossprofit", "ebit", "marketvalue"]:
print(f"{var}: grew {last[var + '_index']:,.1f} times from {int(first['year'])} to {int(last['year'])}")revenue: grew 48.0 times from 2000 to 2023
grossprofit: grew 78.1 times from 2000 to 2023
ebit: grew 219.0 times from 2000 to 2023
marketvalue: grew 319.9 times from 2000 to 2023Insight (example). Apple’s revenue, gross profit and EBIT grew enormously from 2000 to 2023. EBIT grew faster than revenue, which means that Apple’s operating margin improved: its costs grew more slowly than its sales (operating leverage and pricing power of its brand). The market value grew even faster than EBIT. The market value reflects the expected future earnings, not only the current ones: investors became more optimistic about Apple’s future cash flows (services, the ecosystem of products) and were willing to pay a higher multiple of its earnings. In recent years, revenue growth slowed down, while the market value kept growing, so the valuation multiple increased.
(Your insight must be based on your own plots and tables; be specific about the periods.)
11 CHALLENGE 2: The whole market over time
Using the merged dataset with all firms:
- Create the columns
grossprofit,ebitandmarketvaluefor all firms (as in Challenge 1). - Create a summary table by year with the sum of revenue, grossprofit, ebit and marketvalue of all firms (in billions of dollars), and the number of firms with revenue data each year.
- Plot the growth index of each variable (first year = 1).
- Provide an insight: what can you say about the performance of the whole US market? Which years show a drop in ebit or market value, and what happened in those years?
- Critical thinking: the number of firms changes every year. Why could this be a problem when we compare sums over time? Suggest an alternative measure.
11.1 SOLUTION: Challenge 2
merged["ebit"] = merged["grossprofit"] - merged["sgae"] - merged["depreciation"]
merged["marketvalue"] = merged["originalprice"] * merged["sharesoutstanding"]
market = merged.groupby("year").agg(
revenue=("revenue", "sum"),
grossprofit=("grossprofit", "sum"),
ebit=("ebit", "sum"),
marketvalue=("marketvalue", "sum"),
n_firms=("revenue", "count"))
market[["revenue", "grossprofit", "ebit", "marketvalue"]] /= 1_000_000 # thousands -> billions
market.round(1).tail()| revenue | grossprofit | ebit | marketvalue | n_firms | |
|---|---|---|---|---|---|
| year | |||||
| 2019 | 16378.9 | 7197.0 | 1546.7 | 32044.2 | 3563 |
| 2020 | 15662.5 | 6908.6 | 984.4 | 39439.9 | 3582 |
| 2021 | 18375.1 | 8215.0 | 2132.4 | 48840.0 | 3726 |
| 2022 | 20776.0 | 9034.0 | 2130.5 | 39560.0 | 3609 |
| 2023 | 21254.1 | 9193.0 | 2197.6 | 48207.3 | 3424 |
count counts the non-missing values, so n_firms is the number of firms with revenue data in each year. The line with /= divides the 4 columns by one million and stores the result in the same columns (x /= 2 is short for x = x / 2).
index = market[["revenue", "grossprofit", "ebit", "marketvalue"]] / market[["revenue", "grossprofit", "ebit", "marketvalue"]].iloc[0]
index.plot(figsize=(10, 5), title="US public firms: growth indexes (first year = 1)")
plt.ylabel("Times the value of the first year"); plt.grid(alpha=0.3); plt.show()
Dividing a data frame by a row (.iloc[0]) divides each column by its own first value: pandas aligns the column names automatically.
market_growth = market[["ebit", "marketvalue"]].pct_change()
print("Years with a drop in ebit:", market_growth[market_growth["ebit"] < 0].index.tolist())
print("Years with a drop in market value:", market_growth[market_growth["marketvalue"] < 0].index.tolist())Years with a drop in ebit: [2001, 2007, 2008, 2012, 2014, 2015, 2020, 2022]
Years with a drop in market value: [2001, 2002, 2008, 2012, 2015, 2018, 2022]Insight (example). The total revenue, gross profit and EBIT of US public firms grew over the period, but not in a straight line. EBIT fell sharply in 2001 (the burst of the dot-com bubble and the recession), in 2008 (the global financial crisis: EBIT fell by almost half), in 2015 (the collapse of oil prices, which hit energy and mining firms) and in 2020 (the COVID-19 pandemic), and it recovered quickly after each crisis. The market value is more volatile than the accounting variables: it fell in 2001-2002, in 2008 and in 2022 (high inflation and fast increases of interest rates). Notice that in 2020 EBIT fell, but the market value increased: the market value reflects the expectations of investors about the future (a fast recovery, low interest rates), not the current results.
Critical thinking. The sums mix two effects: the growth of the firms and the change in the number of firms (firms that go public or that are delisted). A sum can grow just because there are more firms in the dataset. Look at the column n_firms: from 2010 to 2011 the number of firms with data more than doubles (the database added many firms), so part of the growth of the sums in 2011 is not real growth of the firms. Better alternatives: use the median (or the mean) per firm, use ratios that do not depend on size (for example, the aggregate EBIT divided by the aggregate total assets), or follow a fixed set of firms that exist in all years (keeping in mind that this introduces survivorship bias: we would only keep the firms that survived).
12 CHALLENGE 3: Financial variables and ratios for all firms
Using the merged dataset sorted by firm and year, calculate the following for all firm-years. Remember the golden rule of panel data.
1. Financial variables
grossprofit,ebitandmarketvalue(already done in Challenge 2)- Net income:
netincome=ebit+otherincome+extraordinaryitems-finexp-incometax - Book value of equity:
bookvalue=totalassets-totalliabilities - Annual stock return:
stockreturn, the continuously compounded return of the adjusted price: \ln(adjprice_t/adjprice_{t-1}), within each firm.
2. Financial ratios
- Operating return on assets: roabit=\frac{ebit_{t}}{totalassets_{t-1}}
- Return on assets: roa=\frac{netincome_{t}}{totalassets_{t-1}}
- Operating earnings per share: oeps=\frac{ebit_t}{sharesoutstanding_t}
- Operating earnings per share deflated by price: oepsp=\frac{oeps_t}{originalprice_t}
- Book-to-market ratio: bmr=\frac{bookvalue_t}{marketvalue_t}
We divide flows (ebit, net income) by the total assets at the beginning of the year (t-1), because the assets that generated the earnings of the year are the ones the firm had at the beginning of the year.
- Do your own research and briefly explain with your own words: (a) what is the earnings per share deflated by price and how it is related to the P/E ratio, and (b) what the book-to-market ratio measures and what a high value means.
- Show the descriptive statistics (
describe()) of the 5 ratios. Do you see strange values? Where could they come from? - Verify: check with
assertthat thestockreturnof the first year with data of every firm isNaN.
12.1 SOLUTION: Challenge 3
df = merged.sort_values(["firm", "year"]).copy()
# 1. Financial variables
df["netincome"] = (df["ebit"] + df["otherincome"] + df["extraordinaryitems"]
- df["finexp"] - df["incometax"])
df["bookvalue"] = df["totalassets"] - df["totalliabilities"]
df["stockreturn"] = np.log(df["adjprice"] / df.groupby("firm")["adjprice"].shift(1))
# 2. Financial ratios
df["totalassets_lag"] = df.groupby("firm")["totalassets"].shift(1)
df["roabit"] = df["ebit"] / df["totalassets_lag"]
df["roa"] = df["netincome"] / df["totalassets_lag"]
df["oeps"] = df["ebit"] / df["sharesoutstanding"]
df["oepsp"] = df["oeps"] / df["originalprice"]
df["bmr"] = df["bookvalue"] / df["marketvalue"]
# A division by zero (for example, a price or total assets of 0) gives +inf or -inf.
# We treat those values as missing:
ratios = ["stockreturn", "roabit", "roa", "oeps", "oepsp", "bmr"]
print("Infinite values before cleaning:", np.isinf(df[ratios]).sum().sum())
df[ratios] = df[ratios].replace([np.inf, -np.inf], np.nan)
df[["firm", "year", "stockreturn", "roabit", "roa", "oepsp", "bmr"]].dropna().head()Infinite values before cleaning: 34| firm | year | stockreturn | roabit | roa | oepsp | bmr | |
|---|---|---|---|---|---|---|---|
| 1 | A | 2001 | -0.652522 | -0.092344 | 0.020653 | -0.058850 | 0.428065 |
| 2 | A | 2002 | -0.462108 | -0.201227 | -0.129226 | -0.191589 | 0.551637 |
| 3 | A | 2003 | 0.487391 | -0.088382 | -0.250884 | -0.052074 | 0.202836 |
| 4 | A | 2004 | -0.193326 | 0.061299 | 0.055423 | 0.032899 | 0.304188 |
| 5 | A | 2005 | 0.323045 | 0.025652 | 0.046344 | 0.011002 | 0.248067 |
Explanation.
- The long formula of
netincomeis inside parentheses, which allows us to split it into two lines. df.groupby("firm")["adjprice"].shift(1)is the adjusted price of the previous year of the same firm. Dividing the current price by it and taking the log gives the annual cc return.- We created
totalassets_lagonce and reused it in two ratios. Creating an intermediate column also makes the code easier to check. - In pandas, a division by zero does not produce an error: it produces
inf(infinite) or-inf. Infinite values break statistics like the mean or the percentiles, so we replace them withNaN.np.isinf(...)returns True/False for each cell, and the double.sum().sum()counts them in all the columns (the first sum counts by column, the second adds the columns). This is another silent error to watch for when you calculate ratios.
3. Concepts. (a) The earnings per share deflated by price, EPS/P, is the earnings yield: how many dollars of earnings the firm generates for each dollar invested in its shares. It is the inverse of the price-to-earnings ratio (P/E). A high EPS/P means that the stock is “cheap” relative to its earnings. We use operating earnings (EBIT) to avoid the effect of financing decisions and extraordinary items. We prefer EPS/P to P/E because P/E explodes when earnings are close to zero and is meaningless when earnings are negative, while EPS/P is well-behaved. (b) The book-to-market ratio compares the accounting value of equity with its market value. A high value (for example, above 1) means that the market values the firm at less than its book value: it is a value stock, often a firm with low growth expectations or in trouble. A low value indicates a growth stock: investors pay much more than the book value because they expect high future growth.
4. Descriptive statistics
df[["roabit", "roa", "oeps", "oepsp", "bmr"]].describe().T| count | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|
| roabit | 56790.0 | -5.177834 | 7.394713e+02 | -1.651791e+05 | -0.014960 | 0.041332 | 0.103901 | 2.265365e+02 |
| roa | 56790.0 | -7.291576 | 1.162228e+03 | -2.693134e+05 | -0.023329 | 0.022677 | 0.070687 | 2.800496e+02 |
| oeps | 61627.0 | -80931.289117 | 1.064225e+07 | -1.559200e+09 | -0.202293 | 1.222518 | 3.470602 | 7.778302e+08 |
| oepsp | 59171.0 | 186.239037 | 4.551174e+04 | -3.887477e+05 | -0.020854 | 0.048579 | 0.092586 | 1.105995e+07 |
| bmr | 59219.0 | 1474.315606 | 3.077861e+05 | -2.865723e+03 | 0.230808 | 0.452773 | 0.774199 | 7.388870e+07 |
There are very extreme values (look at the min and max compared with the 25% and 75% percentiles). For example, oepsp can reach absurd values when the stock price is extremely small (a few cents, as in firms close to bankruptcy or with data errors), and roa explodes when a firm had almost no assets in the previous year. These extreme values (outliers) can distort averages and regression models; we deal with them in Challenge 5.
5. Verification
first_rows = df[df["adjprice"].notna()].groupby("firm").head(1) # first row with a price of each firm
assert first_rows["stockreturn"].isna().all()
print("OK: the first return of every firm is NaN")OK: the first return of every firm is NaNgroupby("firm").head(1) takes the first row of each group (the data are sorted by year). .isna().all() returns True only if all those returns are missing: no firm “borrowed” a price from another firm.
13 CHALLENGE 4: Industry-year summary tables
Using the data frame of Challenge 3:
- Create a table with the median
roabitby industry (naics1) and year. Why is the median a better choice than the mean for financial ratios? - Reshape the table into wide format: one row per year and one column per industry. Plot the median
roabitof the 3 industries with more firms over time. - Which industry had the highest median
roabitin the last year of the data? Which one had the lowest? - Add to your merged data frame a column
ind_median_roabitwith the median roabit of the industry of each firm in each year, and a columnabove_indequal to 1 if the firm’sroabitis higher than its industry median, and 0 otherwise. Hint: usegroupby(["naics1", "year"])["roabit"].transform("median").transformreturns one value per row (the median of the group of that row), instead of one value per group.
13.1 SOLUTION: Challenge 4
ind_year = df.groupby(["naics1", "year"])["roabit"].median().reset_index()
ind_year.head()| naics1 | year | roabit | |
|---|---|---|---|
| 0 | - | 2016 | NaN |
| 1 | - | 2017 | 0.079561 |
| 2 | - | 2018 | 0.100925 |
| 3 | - | 2019 | 0.084168 |
| 4 | - | 2020 | 0.056442 |
Why the median? Financial ratios have extreme values (Challenge 3). The mean is very sensitive to them: one firm with a roabit of 500 moves the mean of its industry a lot. The median is the value in the middle, so it is not affected by a few extreme values. It represents the typical firm of the industry.
ind_wide = ind_year.pivot(index="year", columns="naics1", values="roabit")
top3 = df.groupby("naics1")["firm"].nunique().sort_values(ascending=False).head(3).index
ind_wide[top3].plot(figsize=(10, 5), title="Median operating ROA (roabit) by industry")
plt.ylabel("Median roabit"); plt.grid(alpha=0.3); plt.show()
df.groupby("naics1")["firm"].nunique() counts the distinct firms of each industry; we sort it and keep the names (the .index) of the top 3, which we use to select 3 columns of the wide table.
last_year = ind_wide.dropna(how="all").index.max()
ranking = ind_wide.loc[last_year].sort_values(ascending=False)
print(f"Median roabit by industry in {last_year}:")
print(ranking.round(4))Median roabit by industry in 2023:
naics1
Mining, Quarrying, and Oil and Gas Extraction 0.0942
Construction 0.0930
Arts, Entertainment, and Recreation 0.0811
Wholesale Trade 0.0715
Transportation and Warehousing 0.0662
Accommodation and Food Services 0.0609
Retail Trade 0.0602
- 0.0564
Educational Services 0.0441
Administrative and Support and Waste Management and Remediation Services 0.0441
Utilities 0.0422
Other Services (except Public Administration) 0.0402
Real Estate and Rental and Leasing 0.0133
Agriculture, Forestry, Fishing and Hunting 0.0124
Finance and Insurance 0.0103
Professional, Scientific, and Technical Services 0.0066
Health Care and Social Assistance 0.0031
Manufacturing -0.0197
Information -0.0206
Management of Companies and Enterprises NaN
Name: 2023, dtype: float64The first industry of the ranking has the highest median roabit and the last one the lowest. dropna(how="all") drops the years where all industries are missing, so that last_year is the last year with data.
df["ind_median_roabit"] = df.groupby(["naics1", "year"])["roabit"].transform("median")
df["above_ind"] = (df["roabit"] > df["ind_median_roabit"]).astype(int)
df.loc[df["roabit"].isna(), "above_ind"] = np.nan # no roabit, no comparison
df[["firm", "year", "naics1", "roabit", "ind_median_roabit", "above_ind"]].dropna().head()| firm | year | naics1 | roabit | ind_median_roabit | above_ind | |
|---|---|---|---|---|---|---|
| 1 | A | 2001 | Manufacturing | -0.092344 | 0.079063 | 0.0 |
| 2 | A | 2002 | Manufacturing | -0.201227 | 0.072077 | 0.0 |
| 3 | A | 2003 | Manufacturing | -0.088382 | 0.079784 | 0.0 |
| 4 | A | 2004 | Manufacturing | 0.061299 | 0.099104 | 0.0 |
| 5 | A | 2005 | Manufacturing | 0.025652 | 0.101775 | 0.0 |
agg vs transform. groupby(...).median() returns one row per group (a summary table). groupby(...).transform("median") returns a column with the same number of rows as the data frame, where each row gets the median of its group. We use transform when we want to compare each firm with its group. (condition).astype(int) converts True/False into 1/0. Finally, we set above_ind to NaN when roabit is missing, because a missing value compared with a number gives False, which would be wrongly counted as “below the industry”. This is the same kind of variable we will predict with a Machine Learning model in Workshop 4.
14 CHALLENGE 5: Winsorization
Winsorization is a technique to treat extreme values (outliers): the values below a low percentile are replaced by the value of that percentile, and the values above a high percentile are replaced by the value of that percentile. Unlike deleting outliers, winsorization keeps all the observations.
- Do your own research and explain winsorization with your own words. Why is it important before running a regression?
- Write a function
winsorize(x, low=0.01, high=0.99)that receives a pandas Series and returns the winsorized Series. Hint: use thequantileandclipmethods. - Winsorize
oepspat the 1st and 99th percentiles within each year (the distribution of ratios changes over time), and call the new columnoepspw. Hint:df.groupby("year")["oepsp"].transform(winsorize). - Compare the histograms and the descriptive statistics of
oepspandoepspw.
14.1 SOLUTION: Challenge 5
Explanation. Winsorization “flattens” the extreme values of a variable. For the high values, we choose a percentile (for example, the 99th), and all the values above it are replaced by the value of that percentile; the same for the low values with a low percentile (for example, the 1st). The observations are not deleted: they keep their position (very high or very low), but they lose their extreme magnitude. It is important before a regression because the estimated coefficients (ordinary least squares, logistic regression) are very sensitive to extreme values: a few absurd observations, like an oepsp of 10,000 caused by a price of $0.001, can completely change the estimated beta coefficients and make them unreliable.
def winsorize(x, low=0.01, high=0.99):
"""Replaces the values of x below its `low` quantile and above its `high` quantile
by those quantiles."""
lower = x.quantile(low)
upper = x.quantile(high)
return x.clip(lower=lower, upper=upper)
df["oepspw"] = df.groupby("year")["oepsp"].transform(winsorize)
df[["oepsp", "oepspw"]].describe().T| count | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|
| oepsp | 59171.0 | 186.239037 | 45511.736515 | -388747.731397 | -0.020854 | 0.048579 | 0.092586 | 1.105995e+07 |
| oepspw | 59171.0 | -0.049547 | 0.509496 | -12.078869 | -0.020854 | 0.048579 | 0.092586 | 7.507293e-01 |
Explanation. x.quantile(0.01) is the value below which 1% of the observations are. clip(lower, upper) replaces the values lower than lower with lower, and the values higher than upper with upper. With transform(winsorize), pandas applies our function to each year separately and returns a column aligned with the rows of df. We pass the function name without parentheses: we give pandas the function, and pandas calls it once for each year. Missing values are ignored by quantile and remain missing.
fig, axes = plt.subplots(1, 2, figsize=(12, 4))
df["oepsp"].plot(kind="hist", bins=60, ax=axes[0], title="oepsp (original)")
df["oepspw"].plot(kind="hist", bins=60, ax=axes[1], title="oepspw (winsorized 1%-99% by year)")
plt.show()
The histogram of the original oepsp is a single bar: a few extreme values force a huge scale on the x axis, and we cannot see the distribution. After winsorization, the distribution is visible: most firms have an operating earnings yield between approximately −0.3 and +0.3, with a long left tail of firms with operating losses.
15 CHALLENGE 6: Bug hunt
Each piece of code has a bug that does not produce an error message: the code runs, but the result is wrong. Find the bug, explain it, and fix it.
6.1 Annual growth of total assets for all firms:
df = df.sort_values(["firm", "year"])
df["asset_growth"] = df["totalassets"] / df["totalassets"].shift(1) - 16.2 A student merges a table of industry names with the panel. The catalog industries has the columns naics1 and industry_name, but some naics1 codes appear twice in it (with two different spellings of the name):
panel2 = pd.merge(df, industries, on="naics1", how="left")
print(panel2["revenue"].sum())6.3 Number of active firms:
n_active = merged[merged["status"] == "active"].shape[0]15.1 SOLUTION: Challenge 6
6.1 The shift is not done within each firm, so the first year of each firm uses the total assets of the last year of the previous firm. Fix: df.groupby("firm")["totalassets"].shift(1).
6.2 Since some keys are repeated in industries, the merge duplicates the rows of those industries, and the total revenue is overstated. Fix: remove the duplicates of the catalog before merging (industries.drop_duplicates(subset="naics1")) and always check len(panel2) == len(df).
6.3 merged has one row per firm-year, so this counts firm-years, not firms. Fix: merged.loc[merged["status"] == "active", "firm"].nunique().
16 DataCamp online course
You MUST TAKE Chapter 1: Getting to know a dataset from the course Exploratory Data Analysis in Python.
17 W3 submission
- Submit the link of your Google Colab notebook in Canvas, and make sure you shared it with cdorante@tec.mx (Edit privileges).
- Answer the Canvas Quiz W3 before the deadline, with your notebook open.