Dunder Data Challenge #4 - Solution

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

Solution

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()

Begin Mastering Data Science Now for Free!

Take my free Intro to Pandas course to begin your journey mastering data analysis with Python.

Solution continued

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.

Extra challenge

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'])

Become a pandas expert

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.