In the first article, I focused on common math tasks in Excel and their pandas counterparts. In this article, I’ll focus on some common selection and filtering tasks and illustrate how to do the same thing in pandas.

I have been very excited by the response to the first post in this series. Thank you to all for the positive feedback. I want to keep the series going by highlighting some other tasks that you commonly execute in Excel and show how you can perform similar functions in pandas .

Getting Set Up

If you would like to follow along, you can download the excel file.

Import the pandas and numpy modules.

import pandas as pd import numpy as np

Load in the Excel data that represents a year’s worth of sales for our sample company.

df = pd . read_excel ( "sample-salesv3.xlsx" )

Take a quick look at the data types to make sure everything came through as expected.

df . dtypes

account number int64 name object sku object quantity int64 unit price float64 ext price float64 date object dtype: object

You’ll notice that our date column is showing up as a generic object . We are going to convert it to datetime object to make some future selections a little easier.

df [ 'date' ] = pd . to_datetime ( df [ 'date' ]) df . head ()

account number name sku quantity unit price ext price date 0 740150 Barton LLC B1-20000 39 86.69 3380.91 2014-01-01 07:21:51 1 714466 Trantow-Barrows S2-77896 -1 63.16 -63.16 2014-01-01 10:00:47 2 218895 Kulas Inc B1-69924 23 90.70 2086.10 2014-01-01 13:24:58 3 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.05 2014-01-01 15:05:22 4 412290 Jerde-Hilpert S2-34077 6 83.21 499.26 2014-01-01 23:26:55

df . dtypes

account number int64 name object sku object quantity int64 unit price float64 ext price float64 date datetime64[ns] dtype: object

The date is now a datetime object which will be useful in future steps.

Filtering the data I think one of the handiest features in Excel is the filter. I imagine that almost anytime someone gets an Excel file of any size and they want to filter the data, they use this function. Here is an image of using it for this data set: Similar to the ilter function in Excel, you can use pandas to filter and select certain subsets of data. For instance, if we want to just see a specific account number, we can easily do that with Excel or with pandas. Here is the Excel filter solution: It is relatively straightforward to do in pandas. Note, I am going to use the head function to show the top results. This is purely for the purposes of keeping the article shorter. df [ df [ "account number" ] == 307599 ] . head () account number name sku quantity unit price ext price date 3 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.05 2014-01-01 15:05:22 13 307599 Kassulke, Ondricka and Metz S2-10342 17 12.44 211.48 2014-01-04 07:53:01 34 307599 Kassulke, Ondricka and Metz S2-78676 35 33.04 1156.40 2014-01-10 05:26:31 58 307599 Kassulke, Ondricka and Metz B1-20000 22 37.87 833.14 2014-01-15 16:22:22 70 307599 Kassulke, Ondricka and Metz S2-10342 44 96.79 4258.76 2014-01-18 06:32:31 You could also do the filtering based on numeric values. I am not going to show any more Excel-based samples. I am sure you get the idea. df [ df [ "quantity" ] > 22 ] . head () account number name sku quantity unit price ext price date 0 740150 Barton LLC B1-20000 39 86.69 3380.91 2014-01-01 07:21:51 2 218895 Kulas Inc B1-69924 23 90.70 2086.10 2014-01-01 13:24:58 3 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.05 2014-01-01 15:05:22 14 737550 Fritsch, Russel and Anderson B1-53102 23 71.56 1645.88 2014-01-04 08:57:48 15 239344 Stokes LLC S1-06532 34 71.51 2431.34 2014-01-04 11:34:58 If we want to do more complex filtering, we can use map to filter on various criteria. In this example, let’s look for items with sku’s that start with B1. df [ df [ "sku" ] . map ( lambda x : x . startswith ( 'B1' ))] . head () account number name sku quantity unit price ext price date 0 740150 Barton LLC B1-20000 39 86.69 3380.91 2014-01-01 07:21:51 2 218895 Kulas Inc B1-69924 23 90.70 2086.10 2014-01-01 13:24:58 6 218895 Kulas Inc B1-65551 2 31.10 62.20 2014-01-02 10:57:23 14 737550 Fritsch, Russel and Anderson B1-53102 23 71.56 1645.88 2014-01-04 08:57:48 17 239344 Stokes LLC B1-50809 14 16.23 227.22 2014-01-04 22:14:32 It’s easy to chain two or more statements together using the &. df [ df [ "sku" ] . map ( lambda x : x . startswith ( 'B1' )) & ( df [ "quantity" ] > 22 )] . head () account number name sku quantity unit price ext price date 0 740150 Barton LLC B1-20000 39 86.69 3380.91 2014-01-01 07:21:51 2 218895 Kulas Inc B1-69924 23 90.70 2086.10 2014-01-01 13:24:58 14 737550 Fritsch, Russel and Anderson B1-53102 23 71.56 1645.88 2014-01-04 08:57:48 26 737550 Fritsch, Russel and Anderson B1-53636 42 42.06 1766.52 2014-01-08 00:02:11 31 714466 Trantow-Barrows B1-33087 32 19.56 625.92 2014-01-09 10:16:32 Another useful function that pandas supports is called isin . It allows us to define a list of values we want to look for. In this case, we look for all records that include two specific account numbers. df [ df [ "account number" ] . isin ([ 714466 , 218895 ])] . head () account number name sku quantity unit price ext price date 1 714466 Trantow-Barrows S2-77896 -1 63.16 -63.16 2014-01-01 10:00:47 2 218895 Kulas Inc B1-69924 23 90.70 2086.10 2014-01-01 13:24:58 5 714466 Trantow-Barrows S2-77896 17 87.63 1489.71 2014-01-02 10:07:15 6 218895 Kulas Inc B1-65551 2 31.10 62.20 2014-01-02 10:57:23 8 714466 Trantow-Barrows S1-50961 22 84.09 1849.98 2014-01-03 11:29:02 Pandas supports another function called query which allows you to efficiently select subsets of data. It does require the installation of numexpr so make sure you have it installed before trying this step. If you would like to get a list of customers by name, you can do that with a query, similar to the python syntax shown above. df . query ( 'name == ["Kulas Inc","Barton LLC"]' ) . head () account number name sku quantity unit price ext price date 0 740150 Barton LLC B1-20000 39 86.69 3380.91 2014-01-01 07:21:51 2 218895 Kulas Inc B1-69924 23 90.70 2086.10 2014-01-01 13:24:58 6 218895 Kulas Inc B1-65551 2 31.10 62.20 2014-01-02 10:57:23 33 218895 Kulas Inc S1-06532 3 22.36 67.08 2014-01-09 23:58:27 36 218895 Kulas Inc S2-34077 16 73.04 1168.64 2014-01-10 12:07:30 The query function allows you do more than just this simple example but for the purposes of this discussion, I’m showing it so you are aware that it is out there for your needs.

Additional String Functions Pandas has support for vectorized string functions as well. If we want to identify all the sku’s that contain a certain value, we can use str.contains . In this case, we know that the sku is always represented in the same way, so B1 only shows up in the front of the sku. You need to understand your data to make sure you are getting back what you expected. df [ df [ 'sku' ] . str . contains ( 'B1' )] . head () account number name sku quantity unit price ext price date 0 740150 Barton LLC B1-20000 39 86.69 3380.91 2014-01-01 07:21:51 2 218895 Kulas Inc B1-69924 23 90.70 2086.10 2014-01-01 13:24:58 6 218895 Kulas Inc B1-65551 2 31.10 62.20 2014-01-02 10:57:23 14 737550 Fritsch, Russel and Anderson B1-53102 23 71.56 1645.88 2014-01-04 08:57:48 17 239344 Stokes LLC B1-50809 14 16.23 227.22 2014-01-04 22:14:32 We can string queries together and use sort to control how the data is ordered. df [( df [ 'sku' ] . str . contains ( 'B1-531' )) & ( df [ 'quantity' ] > 40 )] . sort ( columns = [ 'quantity' , 'name' ], ascending = [ 0 , 1 ]) account number name sku quantity unit price ext price date 684 642753 Pollich LLC B1-53102 46 26.07 1199.22 2014-06-08 19:33:33 792 688981 Keeling LLC B1-53102 45 41.19 1853.55 2014-07-04 21:42:22 176 383080 Will LLC B1-53102 45 89.22 4014.90 2014-02-11 04:14:09 1213 604255 Halvorson, Crona and Champlin B1-53102 41 55.05 2257.05 2014-10-18 19:27:01 1215 307599 Kassulke, Ondricka and Metz B1-53102 41 93.70 3841.70 2014-10-18 23:25:10 1128 714466 Trantow-Barrows B1-53102 41 55.68 2282.88 2014-09-27 10:42:48 1001 424914 White-Trantow B1-53102 41 81.25 3331.25 2014-08-26 11:44:30