Learn Python Series (#33) - Data Science Part 4 - Pandas
Repository
What will I learn?
- You will learn that
pandasprovides you with powerful tools you can use to filter data with; - individual and multiple conditions can be combined;
- inline conditions can be used;
- Boolean variables can be defined, useful for readability and re-use;
- "auto-magical" conditional expression strings can be passed as arguments to the
.query()method, but beware for potential security vulnerabilities in case you're accepting external (user / bot) input!
Requirements
- A working modern computer running macOS, Windows or Ubuntu;
- An installed Python 3(.7) distribution, such as (for example) the Anaconda Distribution;
- The ambition to learn Python programming.
Difficulty
- Beginner, intermediate
Additional sample code files
The full - and working! - iPython tutorial sample code file is included for you to download and run for yourself right here:
https://github.com/realScipio/learn-python-series/blob/master/lps-033/learn-python-series-033-data-science-pt4-pandas.ipynb
The example CSV file that was used in the episodes #31 and #32 is copied to the lps-033 folder as well:
https://github.com/realScipio/learn-python-series/blob/master/lps-033/btcusdt_20190602_20190604_1min_hloc.csv
GitHub Account
Learn Python Series (#33) - Data Science Part 4 - Pandas
Re-loading the actual BTCUSDT financial data using pandas
First, let's again read and open the file btcusdt_20190602_20190604_1min_hloc.csv found here on my GitHub account, (after having saved the file to your current working directory, from which you're also opening it using .read_csv()):
import pandas as pd
df = pd.read_csv('btcusdt_20190602_20190604_1min_hloc.csv',
parse_dates=['datetime'], index_col='datetime')
df.head()
| open | high | low | close | volume | |
|---|---|---|---|---|---|
| datetime | |||||
| 2019-06-02 00:00:00+00:00 | 8545.10 | 8548.55 | 8535.98 | 8537.67 | 17.349543 |
| 2019-06-02 00:01:00+00:00 | 8537.53 | 8543.49 | 8524.00 | 8534.66 | 31.599922 |
| 2019-06-02 00:02:00+00:00 | 8533.64 | 8540.13 | 8529.98 | 8534.97 | 7.011458 |
| 2019-06-02 00:03:00+00:00 | 8534.97 | 8551.76 | 8534.00 | 8551.76 | 5.992965 |
| 2019-06-02 00:04:00+00:00 | 8551.76 | 8554.76 | 8544.62 | 8549.30 | 15.771411 |
Executing pandas conditional filters
If you've been following along about pandas in this Data Science sub-series, hopefully by now you've realised that pandas provides you with quite some powerful built-in methods to analyse, enrich, clean-up, and (re-)model data sets with. Like Excel / OpenOffice spreadsheets and like database management systems, pandas is able to "filter" data.
There exist multiple techniques to execute conditional filters.
Inline conditionals
Using the same CSV file, containing 4320 1-minute ticks, fetched from Binance, on their BTCUSDT trading pair, on dates June 2, 2019 to June 4, 2019, you've already analysed (via the df.describe() statistical overview method) that the price of Bitcoin in the given interval was trading between 7481.02 and 8814.78.
df.describe()
| open | high | low | close | volume | |
|---|---|---|---|---|---|
| count | 4320.000000 | 4320.000000 | 4320.000000 | 4320.000000 | 4320.000000 |
| mean | 8354.033475 | 8359.921905 | 8347.543243 | 8353.818926 | 34.183344 |
| std | 358.395024 | 357.338897 | 359.911089 | 358.538551 | 54.520356 |
| min | 7490.200000 | 7533.430000 | 7481.020000 | 7494.110000 | 1.351415 |
| 25% | 7985.045000 | 7990.270000 | 7979.205000 | 7984.997500 | 11.114809 |
| 50% | 8519.605000 | 8524.985000 | 8513.490000 | 8518.845000 | 19.566122 |
| 75% | 8661.080000 | 8666.992500 | 8656.007500 | 8661.200000 | 35.273851 |
| max | 8808.820000 | 8814.780000 | 8805.850000 | 8809.910000 | 949.563225 |
In order to derive how many 1-minute candles the data set contains in which the opening price (the price data in the "open" column) was higher than 8800, the condition itself is - of course:
df['open'] > 8800
To use this condition inline, pass that exact condition as a squared bracket "slicing argument" to the DataFrame df, like so:
df_over_8000 = df[df['open'] > 8800]
df_over_8000
| open | high | low | close | volume | |
|---|---|---|---|---|---|
| datetime | |||||
| 2019-06-02 12:04:00+00:00 | 8801.84 | 8804.14 | 8774.17 | 8784.35 | 98.534386 |
| 2019-06-02 12:45:00+00:00 | 8804.44 | 8805.51 | 8799.00 | 8799.00 | 45.084453 |
| 2019-06-02 12:47:00+00:00 | 8807.61 | 8814.78 | 8805.85 | 8808.72 | 33.917674 |
| 2019-06-02 12:48:00+00:00 | 8808.82 | 8811.78 | 8797.34 | 8797.35 | 68.527716 |
I hadn't explicitly explained using the .count() method before, for example in the "basic pandas statistic operations"-section in the 2nd Data Science sub-series episode (found here), but using the .describe() stats overview method, which was explained before, it was mentioned and used already.
Regardless, if you want to count (to use for further processing or simply to return its value) the number of instances on which the condition is True, simply chain the .count() method, like so:
df[df['open'] > 8800]['open'].count()
4
And indeed, the df_over_8000 result table printed 4 (in total) 1-minute data ticks correctly, containing opening prices higher than 8800.
Filtering using multiple inline conditions
In order to filter (= keep) using two or more conditions, use multiple conditions as "filtering slice" via the & (and) and | (or) bitwise operators.
Say, for example, you want to filter all 1-minute ticks where opening price was above 8800 and trading volume was above 50 Bitcoins, execute:
df_over_8000_high_volume = df[ (df['open'] > 8800) & (df['volume'] > 50) ]
df_over_8000_high_volume
| open | high | low | close | volume | |
|---|---|---|---|---|---|
| datetime | |||||
| 2019-06-02 12:04:00+00:00 | 8801.84 | 8804.14 | 8774.17 | 8784.35 | 98.534386 |
| 2019-06-02 12:48:00+00:00 | 8808.82 | 8811.78 | 8797.34 | 8797.35 | 68.527716 |
By "eye-balling" the first (single-condition) example output, you can see the results are correct.
If we would replace & for |, we're filtering something completely different, being:
- let's keep all rows in which either opening price is above 8800,
or - volume is above 50:
df_over_8000_OR_high_volume = df[ (df['open'] > 8800) | (df['volume'] > 50) ]
df_over_8000_OR_high_volume.head(10)
| open | high | low | close | volume | |
|---|---|---|---|---|---|
| datetime | |||||
| 2019-06-02 01:22:00+00:00 | 8586.33 | 8595.12 | 8583.23 | 8594.99 | 51.051786 |
| 2019-06-02 03:02:00+00:00 | 8535.29 | 8550.20 | 8529.55 | 8545.56 | 56.543773 |
| 2019-06-02 07:33:00+00:00 | 8588.05 | 8599.85 | 8586.00 | 8599.36 | 61.909827 |
| 2019-06-02 07:34:00+00:00 | 8599.56 | 8615.00 | 8598.09 | 8614.65 | 197.822432 |
| 2019-06-02 07:35:00+00:00 | 8611.87 | 8652.75 | 8611.05 | 8648.88 | 292.018398 |
| 2019-06-02 07:36:00+00:00 | 8648.90 | 8680.00 | 8648.87 | 8670.11 | 239.552508 |
| 2019-06-02 07:37:00+00:00 | 8673.25 | 8673.25 | 8653.92 | 8661.19 | 96.012789 |
| 2019-06-02 07:38:00+00:00 | 8661.68 | 8676.26 | 8661.68 | 8674.99 | 54.226364 |
| 2019-06-02 07:39:00+00:00 | 8674.99 | 8720.00 | 8671.88 | 8715.01 | 188.849318 |
| 2019-06-02 07:40:00+00:00 | 8710.01 | 8714.87 | 8685.24 | 8703.31 | 118.652782 |
df_over_8000_OR_high_volume['open'].count()
671
Remembering that df_over_8000_high_volume returned only 2 rows in which both conditions were met, if we substract those 2 from the individual conditionals combined, indeed we get 671 as a result:
price_over_8800 = df[(df['open'] > 8800)]['open'].count()
volume_over_50 = df[df['volume'] > 50]['open'].count()
num_df_over_8000_high_volume = df_over_8000_high_volume['open'].count()
print(f"price_over_8800: {price_over_8800}")
print(f"volume_over_50: {volume_over_50}")
print(f"num_df_over_8000_high_volume: {num_df_over_8000_high_volume}")
price_over_8800: 4
volume_over_50: 669
num_df_over_8000_high_volume: 2
- In 4 instances, price was over 8800;
- In 669 instances, volume was over 50;
- In 2 instances, price was over 8800 and volume over 50
Ergo: (669 + 4) - 2 = 671
Filtering passing Boolean variables
As you may have noticed, filtering by multipal inline conditions very quickly leads to long lines of code. For readability matters, it's also possible to assign Boolean variables, and pass those instead. The following syntax (same example) also works:
price_over_8800 = df['open'] > 8800
volume_over_50 = df['volume'] > 50
df_over_8000_high_volume = df[price_over_8800 & volume_over_50]
df_over_8000_high_volume
| open | high | low | close | volume | |
|---|---|---|---|---|---|
| datetime | |||||
| 2019-06-02 12:04:00+00:00 | 8801.84 | 8804.14 | 8774.17 | 8784.35 | 98.534386 |
| 2019-06-02 12:48:00+00:00 | 8808.82 | 8811.78 | 8797.34 | 8797.35 | 68.527716 |
--- this looks much better!
Filtering using the .query() method
A third way to filter DataFrame data, is by using the .query() method. .query() expects to receive a string as its expr= argument, using Boolean (instead of bitwise) operators, ergo: and / or.
Also, the column names are referenced inside the conditional expression string. Like so (again: same example):
query_expression = 'open > 8800 and volume > 50'
df.query(query_expression)
| open | high | low | close | volume | |
|---|---|---|---|---|---|
| datetime | |||||
| 2019-06-02 12:04:00+00:00 | 8801.84 | 8804.14 | 8774.17 | 8784.35 | 98.534386 |
| 2019-06-02 12:48:00+00:00 | 8808.82 | 8811.78 | 8797.34 | 8797.35 | 68.527716 |
Nota bene: Please stand still for a second and realise that, in order to make the last mentioned .query() method work, only a "humanized" looking string needed to be passed-in as an argument. It seems to "just work auto-magically"...
Under the hood, .query() utilises the (not yet explained) .eval() method of pandas, which is - in general - able to evaluate strings to derive column-wise vectorised operations from. How magical and convenient that may be (it is very cool actually!) it also allows .eval() to execute arbitrary code. So be careful when using it on an interface where others (users, bots) are allowed to pass their (potentially dangerous) input strings as .eval() arguments!
What did we learn, hopefully?
Hopefully you've learned that pandas provides you with techniques for filtering data based on one or more conditional filters. When using bitwise operators & / |, or boolean operators and / or (using .query()), keep in mind you're writing expressions about which rows you want to keep (not drop).
Thank you for your time!
Curriculum (of the Learn Python Series):
- Learn Python Series - Intro
- Learn Python Series (#2) - Handling Strings Part 1
- Learn Python Series (#3) - Handling Strings Part 2
- Learn Python Series (#4) - Round-Up #1
- Learn Python Series (#5) - Handling Lists Part 1
- Learn Python Series (#6) - Handling Lists Part 2
- Learn Python Series (#7) - Handling Dictionaries
- Learn Python Series (#8) - Handling Tuples
- Learn Python Series (#9) - Using Import
- Learn Python Series (#10) - Matplotlib Part 1
- Learn Python Series (#11) - NumPy Part 1
- Learn Python Series (#12) - Handling Files
- Learn Python Series (#13) - Mini Project - Developing a Web Crawler Part 1
- Learn Python Series (#14) - Mini Project - Developing a Web Crawler Part 2
- Learn Python Series (#15) - Handling JSON
- Learn Python Series (#16) - Mini Project - Developing a Web Crawler Part 3
- Learn Python Series (#17) - Roundup #2 - Combining and analyzing any-to-any multi-currency historical data
- Learn Python Series (#18) - PyMongo Part 1
- Learn Python Series (#19) - PyMongo Part 2
- Learn Python Series (#20) - PyMongo Part 3
- Learn Python Series (#21) - Handling Dates and Time Part 1
- Learn Python Series (#22) - Handling Dates and Time Part 2
- Learn Python Series (#23) - Handling Regular Expressions Part 1
- Learn Python Series (#24) - Handling Regular Expressions Part 2
- Learn Python Series (#25) - Handling Regular Expressions Part 3
- Learn Python Series (#26) - pipenv & Visual Studio Code
- Learn Python Series (#27) - Handling Strings Part 3 (F-Strings)
- Learn Python Series (#28) - Using Pickle and Shelve
- Learn Python Series (#29) - Handling CSV
- Learn Python Series (#30) - Data Science Part 1 - Pandas
- Learn Python Series (#31) - Data Science Part 2 - Pandas
- Learn Python Series (#32) - Data Science Part 3 - Pandas