import numpy as np
import pandas as pd
import matplotlib.pyplot as pltWorkshop 3, 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?
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.
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.
13 CHALLENGE 4: Industry-year summary tables
(OPTIONAL)
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.
14 CHALLENGE 5: 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.
5.1 Annual growth of total assets for all firms:
df = df.sort_values(["firm", "year"])
df["asset_growth"] = df["totalassets"] / df["totalassets"].shift(1) - 15.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())5.3 Number of active firms:
n_active = merged[merged["status"] == "active"].shape[0]15 DataCamp online course
You MUST TAKE Chapter 1: Getting to know a dataset from the course Exploratory Data Analysis in Python.
16 W3 submission
Submit the .ipynb file of your Google Colab notebook in Canvas
Answer the Canvas Quiz W3 before the deadline, with your notebook open.