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

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

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

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

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

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

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.

Working with Dates

Using pandas, you can do complex filtering on dates. Before doing anything with dates, I encourage you to sort by the date column to make sure the results return what you are expecting.

df = df.sort_values(by=['date'])
df.head()

The python filtering syntax shown before works with dates.

df[df['date'] >='20140905'].head()

One of the really nice features of pandas is that it understands dates so it will allow us to do partial filtering. If we want to only look for data more recent than a specific month, we can do so.

df[df['date'] >='2014-03'].head()

Of course, you can chain the criteria.

df[(df['date'] >='20140701') & (df['date'] <= '20140715')].head()

Because pandas understands date columns, you can express the date value in multiple formats and it will give you the results you expect.

df[df['date'] >= 'Oct-2014'].head()

df[df['date'] >= '10-10-2014'].head()

When working with time series data, if we convert the data to use the date as as the index, we can do some more filtering variations.

Set the new index using set_index .

df2 = df.set_index(['date'])
df2.head()

We can slice the data to get a range.

df2["20140101":"20140201"].head()

Once again, we can use various date representations to remove any ambiguity around date naming conventions.

df2["2014-Jan-1":"2014-Feb-1"].head()

df2["2014-Jan-1":"2014-Feb-1"].tail()

df2["2014"].head()

df2["2014-Dec"].head()

As you can see, there are a lot of options when it comes to sorting and filtering based on dates.

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

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_values(by=['quantity','name'],ascending=[0,1])

Bonus Task

I frequently find myself trying to get a list of unique items in a long list within Excel. It is a multi-step process to do this in Excel but is fairly simple in pandas. Here is one way to do this using the Advanced Filter in Excel.

In pandas, we use the unique function on a column to get the list.

df["name"].unique()

array([u'Barton LLC', u'Trantow-Barrows', u'Kulas Inc',
       u'Kassulke, Ondricka and Metz', u'Jerde-Hilpert', u'Koepp Ltd',
       u'Fritsch, Russel and Anderson', u'Kiehn-Spinka', u'Keeling LLC',
       u'Frami, Hills and Schmidt', u'Stokes LLC', u'Kuhn-Gusikowski',
       u'Herman LLC', u'White-Trantow', u'Sanford and Sons',
       u'Pollich LLC', u'Will LLC', u'Cronin, Oberbrunner and Spencer',
       u'Halvorson, Crona and Champlin', u'Purdy-Kunde'], dtype=object)

If we wanted to include the account number, we could use drop_duplicates .

df.drop_duplicates(subset=["account number","name"]).head()

We are obviously pulling in more data than we need and getting some non-useful information, so select only the first and second columns using iloc .

df.drop_duplicates(subset=["account number","name"]).iloc[:,[0,1]]

I think this single command is easier to maintain than trying to remember the Excel steps every time.

If you would like to view the notebook, feel free to download it.