Asking questions of the data

Once your data is clean, the real work begins: asking it questions. How many days did this stock rise more than two percent? What were its returns? Which rows fall in June? pandas answers questions like these by selecting the columns and filtering to the rows that matter, and doing so in a line or two is exactly the leverage over data that made coding worth learning. This chapter is where a table of prices starts to talk back.

Selecting and computing a column

You select a column by name, df["Close"], which returns it as a Series you can compute on, and you can add a new column just by assigning to one. This program adds a daily-return column and then filters on it.

# Ask questions of the data by selecting and filtering.
import pandas as pd

df = pd.read_csv("sample_prices.csv", parse_dates=["Date"], index_col="Date")

df["return_pct"] = df["Close"].pct_change() * 100   # daily percentage change

big_up = df[df["return_pct"] > 2]                   # filter: days up more than 2%
print("Days up more than 2%:", len(big_up))
print(big_up[["Close", "return_pct"]].head().round(2))

print("Average daily return: {:.3f}%".format(df["return_pct"].mean()))

Days up more than 2%: 7
              Close  return_pct
Date                           
2024-02-13  1342.49        2.26
2024-05-28  1268.74        2.30
2024-06-06  1285.75        3.00
2024-06-24  1345.35        2.67
2024-08-06  1297.32        2.28
Average daily return: 0.010%

The first useful line, df["Close"].pct_change() * 100, computes the daily percentage change of the close across the whole series, a return column, in one call, and assigns it to a new column named return_pct. That single method replaces the loop you wrote by hand back in Part 2, which is the payoff of pandas: common operations on a whole series are one call, not a hand-written iteration.

Filtering with a condition

The heart of the chapter is the next line. Writing df[df["return_pct"] > 2] keeps only the rows where the return was greater than two percent, a technique called boolean filtering: the condition inside the brackets produces a column of true and false values, and pandas returns just the rows that were true. The output reports 7 such days in this sample and shows the first few, each with its close and its return. You have asked a precise question, on which days did this stock jump more than two percent, and the table answered it in one line.

Filtering extends naturally. You can combine conditions with the same and-or logic from Part 2 (written with & and | in pandas, each condition in brackets) to ask compound questions, such as big up-days on high volume. You can select several columns at once by passing a list of names. And the final line of the program, the average of the return column, shows that once you have a computed column you can summarise it directly; here the average daily return is a near-flat 0.010%, a reminder that a stock that swings a lot day to day can drift very little over a stretch. Selecting, computing, filtering, and summarising, all on whole columns at once, are the core moves of data analysis, and you now have them.

What to carry forward

pandas answers questions of your data: select and add columns, compute whole-series operations like pct_change in one call, and filter to the rows that meet a condition with boolean indexing, combining conditions with & and |. In this sample, seven days rose more than two percent, found in a single line. Selecting, computing, filtering, and summarising whole columns at once are the everyday moves of analysis.

Market data has one more dimension you have been using without exploring: time. The next chapter works with the date index directly, resampling daily prices to weekly or monthly and selecting by date range.