<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Introduction
The first part of this series was very well received so I thought I would continue the theme of showing how to do common Excel tasks in pandas.
In the first article, I focused on common, math tasks in Excel and how to do them in pandas. In this article, I'll focus on some other Excel tasks related to data selection and how to map them to pandas. 
Please refer to this post for the full post.
Getting Set Up
Import the pandas and numpy modules.
Step1:  Load in the Excel data that represents a year's worth of sales.
Step2:  Take a quick look at the data types to make sure everything came through as expected.
Step3:  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 selections a little easier.
Step4:  The date is now a datetime object which will be useful in future steps.
Filtering the data
Similar to the autofilter 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 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.
Step5:  You could also do the filtering based on numeric values.
Step6:  If we want to do more complex filtering, we can use map to filter. In this example, let's look for items with sku's that start with B1.
Step7:  It's easy to chain two statements together using the &.
Step8:  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.
Step9:  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.
Step10:  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 you.
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.
Step11:  The python filtering syntax shown before works with dates.
Step12:  One of the really nice features of pandas is that it understands dates so 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.
Step13:  Of course, you can chain the criteria.
Step14:  Because pandas understands date columns, you can express the date value in multiple formats and it will give you the results you expect.
Step15:  When working with time series data, if we convert the data to use the date as at the index, we can do some more filtering.
Set the new index using set_index.
Step16:  We can slice the data to get a range.
Step17:  Once again, we can use various date representations to remove any ambiguity around date naming conventions.
Step18:  Additional String Functions
Pandas has support for vectorized string functions as well. If we want to identify all the skus 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.
Step19:  We can string queries together and use sort to control how the data is ordered.
A common need I have in Excel is to understand all the unique items in a column. For instance, maybe I only want to know when customers purchased in this time period. The unique function makes this trivial.
Step20:  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. We just use the unique function on a column to get the list.
Step21:  If we wanted to include the account number, we could use drop_duplicates.
Step22:  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 ix.
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
Explanation: Introduction
The first part of this series was very well received so I thought I would continue the theme of showing how to do common Excel tasks in pandas.
In the first article, I focused on common, math tasks in Excel and how to do them in pandas. In this article, I'll focus on some other Excel tasks related to data selection and how to map them to pandas. 
Please refer to this post for the full post.
Getting Set Up
Import the pandas and numpy modules.
End of explanation
df = pd.read_excel('https://github.com/chris1610/pbpython/blob/master/data/sample-salesv3.xlsx?raw=true')
Explanation: Load in the Excel data that represents a year's worth of sales.
End of explanation
df.dtypes
Explanation: Take a quick look at the data types to make sure everything came through as expected.
End of explanation
df['date'] = pd.to_datetime(df['date'])
df.head()
df.dtypes
Explanation: 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 selections a little easier.
End of explanation
df[df["account number"]==307599].head()
Explanation: The date is now a datetime object which will be useful in future steps.
Filtering the data
Similar to the autofilter 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 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.
End of explanation
df[df["quantity"] > 22].head()
Explanation: You could also do the filtering based on numeric values.
End of explanation
df[df["sku"].map(lambda x: x.startswith('B1'))].head()
Explanation: If we want to do more complex filtering, we can use map to filter. In this example, let's look for items with sku's that start with B1.
End of explanation
df[df["sku"].map(lambda x: x.startswith('B1')) & (df["quantity"] > 22)].head()
Explanation: It's easy to chain two statements together using the &.
End of explanation
df[df["account number"].isin([714466,218895])].head()
Explanation: 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.
End of explanation
df.query('name == ["Kulas Inc","Barton LLC"]').head()
Explanation: 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.
End of explanation
df = df.sort_values(by='date')
df.head()
Explanation: 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 you.
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.
End of explanation
df[df['date'] >='20140905'].head()
Explanation: The python filtering syntax shown before works with dates.
End of explanation
df[df['date'] >='2014-03'].head()
Explanation: One of the really nice features of pandas is that it understands dates so 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.
End of explanation
df[(df['date'] >='20140701') & (df['date'] <= '20140715')].head()
Explanation: Of course, you can chain the criteria.
End of explanation
df[df['date'] >= 'Oct-2014'].head()
df[df['date'] >= '10-10-2014'].head()
Explanation: Because pandas understands date columns, you can express the date value in multiple formats and it will give you the results you expect.
End of explanation
df2 = df.set_index(['date'])
df2.head()
Explanation: When working with time series data, if we convert the data to use the date as at the index, we can do some more filtering.
Set the new index using set_index.
End of explanation
df2["20140101":"20140201"].head()
Explanation: We can slice the data to get a range.
End of explanation
df2["2014-Jan-1":"2014-Feb-1"].head()
df2["2014-Jan-1":"2014-Feb-1"].tail()
df2["2014"].head()
df2["2014-Dec"].head()
Explanation: Once again, we can use various date representations to remove any ambiguity around date naming conventions.
End of explanation
df[df['sku'].str.contains('B1')].head()
Explanation: Additional String Functions
Pandas has support for vectorized string functions as well. If we want to identify all the skus 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.
End of explanation
df[(df['sku'].str.contains('B1-531')) & (df['quantity']>40)].sort_values(by=['quantity','name'],ascending=[0,1])
Explanation: We can string queries together and use sort to control how the data is ordered.
A common need I have in Excel is to understand all the unique items in a column. For instance, maybe I only want to know when customers purchased in this time period. The unique function makes this trivial.
End of explanation
df["name"].unique()
Explanation: 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. We just use the unique function on a column to get the list.
End of explanation
df.drop_duplicates(subset=["account number","name"]).head()
Explanation: If we wanted to include the account number, we could use drop_duplicates.
End of explanation
df.drop_duplicates(subset=["account number","name"]).iloc[:,[0,1]]
Explanation: 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 ix.
End of explanation
<END_TASK>