Nov 13, 2019

In this post, I detail the solution to Dunder Data Challenge #4 — Finding the Date of the Largest Percentage Stock Price Drop.

To begin, we need to find the percentage drop for each stock for each day. pandas has a built-in method for this called `pct_change`

. By default, it finds the percentage change between the current value and the one immediately above it. Like most DataFrame methods, it treats each column independently from the others.

If we call it on our current DataFrame, we’ll get an error as it will not work on our date column. Let’s re-read in the data, converting the date column to a datetime and place it in the index.

`stocks = pd.read_csv('../data/stocks10.csv', parse_dates=['date'],`

index_col='date')

stocks.head()

Placing the date column in the index is a key part of this challenge that makes our solution quite a bit nicer. Let’s now call the `pct_change`

method to get the percentage change for each trading day.

`stocks.pct_change().head()`

Let’s verify that one of the calculated values is what we desire. MSFT dropped 2 cents from 29.84 to 29.82 on its second trading day in this dataset. The percentage calculated below equals the percentage calculated in the method above.

`>>> (29.82 - 29.84) / 29.82`

-0.0006706908115358676

Most pandas users know how to get the maximum and minimum value of each column with the methods `max`

/`min`

. Let's find the largest drop by calling the `min`

method.

`>>> stocks.pct_change().min()`

MSFT -0.156201

AAPL -0.517964

SLB -0.184057

AMZN -0.247661

TSLA -0.193274

XOM -0.139395

WMT -0.101816

T -0.126392

FB -0.189609

V -0.136295

dtype: float64

For the first part of this challenge, we aren’t interested in the value of the largest percentage one-day drop, but the date that it happened. Since the date is in the index, we can use the lesser-known method called `idxmin`

which returns the index of the minimum. An analogous `idxmax`

method also exists.

`>>> stocks.pct_change().idxmin()`

MSFT 2000-04-24

AAPL 2000-09-29

SLB 2008-10-15

AMZN 2001-07-24

TSLA 2012-01-13

XOM 2008-10-15

WMT 2018-02-20

T 2000-12-19

FB 2018-07-26

V 2008-10-15

dtype: datetime64[ns]

In general mathematical speak, this calculation is known as the arg min or arg max.

Knowing the date of the largest drop is great, but it doesn’t tell us what the value of the drop was. We need to return both the minimum and the date of that minimum. This is possible with help from the `agg`

method which allows us to return any number of aggregations from our DataFrame.

An aggregation is any function that returns a single value. Both `min`

and `idxmin`

return a single value and therefore are considered aggregations. The `agg`

method works by accepting a list of aggregating functions where the functions are written as strings.

`stocks.pct_change().agg(['idxmin', 'min'])`

If you are looking to completely master the pandas library and become a trusted expert for doing data science work, check out my book Master Data Analysis with Python. It comes with over 300 exercises with detailed solutions covering the pandas library in-depth.

50% Complete

Upon registration, you'll get access to three free courses and the discount code to purchase the All Access Pass for 50% off through Cyber Monday (12/2)