{ "metadata": { "name": "", "signature": "sha256:3bae7a327d0b52fa2fc5ef64c7e64dc31f325d310b0abeeb67c5ddab441a0b8d" }, "nbformat": 3, "nbformat_minor": 0, "worksheets": [ { "cells": [ { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Introduction" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The first part of this [series](http://pbpython.com/excel-pandas-comp.html) was very well received so I thought I would continue the theme of showing how to do common Excel tasks in pandas.\n", "\n", "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. \n", "\n", "Please refer to [this post](http://pbpython.com/excel-pandas-comp-2.html) for the full post." ] }, { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Getting Set Up" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Import the pandas and numpy modules." ] }, { "cell_type": "code", "collapsed": false, "input": [ "import pandas as pd\n", "import numpy as np" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 1 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Load in the Excel data that represents a year's worth of sales." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df = pd.read_excel(\"../in/sample-salesv3.xlsx\")" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 2 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Take a quick look at the data types to make sure everything came through as expected." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df.dtypes" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 3, "text": [ "account number int64\n", "name object\n", "sku object\n", "quantity int64\n", "unit price float64\n", "ext price float64\n", "date object\n", "dtype: object" ] } ], "prompt_number": 3 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df['date'] = pd.to_datetime(df['date'])" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 4 }, { "cell_type": "code", "collapsed": false, "input": [ "df.head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
0 740150 Barton LLC B1-20000 39 86.69 3380.912014-01-01 07:21:51
1 714466 Trantow-Barrows S2-77896 -1 63.16 -63.162014-01-01 10:00:47
2 218895 Kulas Inc B1-69924 23 90.70 2086.102014-01-01 13:24:58
3 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.052014-01-01 15:05:22
4 412290 Jerde-Hilpert S2-34077 6 83.21 499.262014-01-01 23:26:55
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 5, "text": [ " account number name sku quantity \\\n", "0 740150 Barton LLC B1-20000 39 \n", "1 714466 Trantow-Barrows S2-77896 -1 \n", "2 218895 Kulas Inc B1-69924 23 \n", "3 307599 Kassulke, Ondricka and Metz S1-65481 41 \n", "4 412290 Jerde-Hilpert S2-34077 6 \n", "\n", " unit price ext price date \n", "0 86.69 3380.91 2014-01-01 07:21:51 \n", "1 63.16 -63.16 2014-01-01 10:00:47 \n", "2 90.70 2086.10 2014-01-01 13:24:58 \n", "3 21.05 863.05 2014-01-01 15:05:22 \n", "4 83.21 499.26 2014-01-01 23:26:55 " ] } ], "prompt_number": 5 }, { "cell_type": "code", "collapsed": false, "input": [ "df.dtypes" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 6, "text": [ "account number int64\n", "name object\n", "sku object\n", "quantity int64\n", "unit price float64\n", "ext price float64\n", "date datetime64[ns]\n", "dtype: object" ] } ], "prompt_number": 6 }, { "cell_type": "markdown", "metadata": {}, "source": [ "The date is now a datetime object which will be useful in future steps." ] }, { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Filtering the data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Similar to the autofilter function in Excel, you can use pandas to filter and select certain subsets of data.\n", "\n", "For instance, if we want to just see a specific account number, we can easily do that with pandas.\n", "\n", "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[df[\"account number\"]==307599].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
3 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.052014-01-01 15:05:22
13 307599 Kassulke, Ondricka and Metz S2-10342 17 12.44 211.482014-01-04 07:53:01
34 307599 Kassulke, Ondricka and Metz S2-78676 35 33.04 1156.402014-01-10 05:26:31
58 307599 Kassulke, Ondricka and Metz B1-20000 22 37.87 833.142014-01-15 16:22:22
70 307599 Kassulke, Ondricka and Metz S2-10342 44 96.79 4258.762014-01-18 06:32:31
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 8, "text": [ " account number name sku quantity \\\n", "3 307599 Kassulke, Ondricka and Metz S1-65481 41 \n", "13 307599 Kassulke, Ondricka and Metz S2-10342 17 \n", "34 307599 Kassulke, Ondricka and Metz S2-78676 35 \n", "58 307599 Kassulke, Ondricka and Metz B1-20000 22 \n", "70 307599 Kassulke, Ondricka and Metz S2-10342 44 \n", "\n", " unit price ext price date \n", "3 21.05 863.05 2014-01-01 15:05:22 \n", "13 12.44 211.48 2014-01-04 07:53:01 \n", "34 33.04 1156.40 2014-01-10 05:26:31 \n", "58 37.87 833.14 2014-01-15 16:22:22 \n", "70 96.79 4258.76 2014-01-18 06:32:31 " ] } ], "prompt_number": 8 }, { "cell_type": "markdown", "metadata": {}, "source": [ "You could also do the filtering based on numeric values." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[df[\"quantity\"] > 22].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
0 740150 Barton LLC B1-20000 39 86.69 3380.912014-01-01 07:21:51
2 218895 Kulas Inc B1-69924 23 90.70 2086.102014-01-01 13:24:58
3 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.052014-01-01 15:05:22
14 737550 Fritsch, Russel and Anderson B1-53102 23 71.56 1645.882014-01-04 08:57:48
15 239344 Stokes LLC S1-06532 34 71.51 2431.342014-01-04 11:34:58
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 9, "text": [ " account number name sku quantity \\\n", "0 740150 Barton LLC B1-20000 39 \n", "2 218895 Kulas Inc B1-69924 23 \n", "3 307599 Kassulke, Ondricka and Metz S1-65481 41 \n", "14 737550 Fritsch, Russel and Anderson B1-53102 23 \n", "15 239344 Stokes LLC S1-06532 34 \n", "\n", " unit price ext price date \n", "0 86.69 3380.91 2014-01-01 07:21:51 \n", "2 90.70 2086.10 2014-01-01 13:24:58 \n", "3 21.05 863.05 2014-01-01 15:05:22 \n", "14 71.56 1645.88 2014-01-04 08:57:48 \n", "15 71.51 2431.34 2014-01-04 11:34:58 " ] } ], "prompt_number": 9 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[df[\"sku\"].map(lambda x: x.startswith('B1'))].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
0 740150 Barton LLC B1-20000 39 86.69 3380.912014-01-01 07:21:51
2 218895 Kulas Inc B1-69924 23 90.70 2086.102014-01-01 13:24:58
6 218895 Kulas Inc B1-65551 2 31.10 62.202014-01-02 10:57:23
14 737550 Fritsch, Russel and Anderson B1-53102 23 71.56 1645.882014-01-04 08:57:48
17 239344 Stokes LLC B1-50809 14 16.23 227.222014-01-04 22:14:32
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 10, "text": [ " account number name sku quantity \\\n", "0 740150 Barton LLC B1-20000 39 \n", "2 218895 Kulas Inc B1-69924 23 \n", "6 218895 Kulas Inc B1-65551 2 \n", "14 737550 Fritsch, Russel and Anderson B1-53102 23 \n", "17 239344 Stokes LLC B1-50809 14 \n", "\n", " unit price ext price date \n", "0 86.69 3380.91 2014-01-01 07:21:51 \n", "2 90.70 2086.10 2014-01-01 13:24:58 \n", "6 31.10 62.20 2014-01-02 10:57:23 \n", "14 71.56 1645.88 2014-01-04 08:57:48 \n", "17 16.23 227.22 2014-01-04 22:14:32 " ] } ], "prompt_number": 10 }, { "cell_type": "markdown", "metadata": {}, "source": [ "It's easy to chain two statements together using the &." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[df[\"sku\"].map(lambda x: x.startswith('B1')) & (df[\"quantity\"] > 22)].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
0 740150 Barton LLC B1-20000 39 86.69 3380.912014-01-01 07:21:51
2 218895 Kulas Inc B1-69924 23 90.70 2086.102014-01-01 13:24:58
14 737550 Fritsch, Russel and Anderson B1-53102 23 71.56 1645.882014-01-04 08:57:48
26 737550 Fritsch, Russel and Anderson B1-53636 42 42.06 1766.522014-01-08 00:02:11
31 714466 Trantow-Barrows B1-33087 32 19.56 625.922014-01-09 10:16:32
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 11, "text": [ " account number name sku quantity \\\n", "0 740150 Barton LLC B1-20000 39 \n", "2 218895 Kulas Inc B1-69924 23 \n", "14 737550 Fritsch, Russel and Anderson B1-53102 23 \n", "26 737550 Fritsch, Russel and Anderson B1-53636 42 \n", "31 714466 Trantow-Barrows B1-33087 32 \n", "\n", " unit price ext price date \n", "0 86.69 3380.91 2014-01-01 07:21:51 \n", "2 90.70 2086.10 2014-01-01 13:24:58 \n", "14 71.56 1645.88 2014-01-04 08:57:48 \n", "26 42.06 1766.52 2014-01-08 00:02:11 \n", "31 19.56 625.92 2014-01-09 10:16:32 " ] } ], "prompt_number": 11 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Another useful function that pandas supports is called `isin`. It allows us to define a list of values we want to look for.\n", "\n", "In this case, we look for all records that include two specific account numbers." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[df[\"account number\"].isin([714466,218895])].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
1 714466 Trantow-Barrows S2-77896 -1 63.16 -63.162014-01-01 10:00:47
2 218895 Kulas Inc B1-69924 23 90.70 2086.102014-01-01 13:24:58
5 714466 Trantow-Barrows S2-77896 17 87.63 1489.712014-01-02 10:07:15
6 218895 Kulas Inc B1-65551 2 31.10 62.202014-01-02 10:57:23
8 714466 Trantow-Barrows S1-50961 22 84.09 1849.982014-01-03 11:29:02
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 12, "text": [ " account number name sku quantity unit price ext price \\\n", "1 714466 Trantow-Barrows S2-77896 -1 63.16 -63.16 \n", "2 218895 Kulas Inc B1-69924 23 90.70 2086.10 \n", "5 714466 Trantow-Barrows S2-77896 17 87.63 1489.71 \n", "6 218895 Kulas Inc B1-65551 2 31.10 62.20 \n", "8 714466 Trantow-Barrows S1-50961 22 84.09 1849.98 \n", "\n", " date \n", "1 2014-01-01 10:00:47 \n", "2 2014-01-01 13:24:58 \n", "5 2014-01-02 10:07:15 \n", "6 2014-01-02 10:57:23 \n", "8 2014-01-03 11:29:02 " ] } ], "prompt_number": 12 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pandas supports another function called `query` which allows you to efficiently select subsets of data. It does require the installation of [numexpr](https://github.com/pydata/numexpr) so make sure you have it installed before trying this step.\n", "\n", "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df.query('name == [\"Kulas Inc\",\"Barton LLC\"]').head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
0 740150 Barton LLC B1-20000 39 86.69 3380.912014-01-01 07:21:51
2 218895 Kulas Inc B1-69924 23 90.70 2086.102014-01-01 13:24:58
6 218895 Kulas Inc B1-65551 2 31.10 62.202014-01-02 10:57:23
33 218895 Kulas Inc S1-06532 3 22.36 67.082014-01-09 23:58:27
36 218895 Kulas Inc S2-34077 16 73.04 1168.642014-01-10 12:07:30
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 13, "text": [ " account number name sku quantity unit price ext price \\\n", "0 740150 Barton LLC B1-20000 39 86.69 3380.91 \n", "2 218895 Kulas Inc B1-69924 23 90.70 2086.10 \n", "6 218895 Kulas Inc B1-65551 2 31.10 62.20 \n", "33 218895 Kulas Inc S1-06532 3 22.36 67.08 \n", "36 218895 Kulas Inc S2-34077 16 73.04 1168.64 \n", "\n", " date \n", "0 2014-01-01 07:21:51 \n", "2 2014-01-01 13:24:58 \n", "6 2014-01-02 10:57:23 \n", "33 2014-01-09 23:58:27 \n", "36 2014-01-10 12:07:30 " ] } ], "prompt_number": 13 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Working with Dates" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df = df.sort('date')\n", "df.head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
0 740150 Barton LLC B1-20000 39 86.69 3380.912014-01-01 07:21:51
1 714466 Trantow-Barrows S2-77896 -1 63.16 -63.162014-01-01 10:00:47
2 218895 Kulas Inc B1-69924 23 90.70 2086.102014-01-01 13:24:58
3 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.052014-01-01 15:05:22
4 412290 Jerde-Hilpert S2-34077 6 83.21 499.262014-01-01 23:26:55
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 14, "text": [ " account number name sku quantity \\\n", "0 740150 Barton LLC B1-20000 39 \n", "1 714466 Trantow-Barrows S2-77896 -1 \n", "2 218895 Kulas Inc B1-69924 23 \n", "3 307599 Kassulke, Ondricka and Metz S1-65481 41 \n", "4 412290 Jerde-Hilpert S2-34077 6 \n", "\n", " unit price ext price date \n", "0 86.69 3380.91 2014-01-01 07:21:51 \n", "1 63.16 -63.16 2014-01-01 10:00:47 \n", "2 90.70 2086.10 2014-01-01 13:24:58 \n", "3 21.05 863.05 2014-01-01 15:05:22 \n", "4 83.21 499.26 2014-01-01 23:26:55 " ] } ], "prompt_number": 14 }, { "cell_type": "markdown", "metadata": {}, "source": [ "The python filtering syntax shown before works with dates." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[df['date'] >='20140905'].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
1042 163416 Purdy-Kunde B1-38851 41 98.69 4046.292014-09-05 01:52:32
1043 714466 Trantow-Barrows S1-30248 1 37.16 37.162014-09-05 06:17:19
1044 729833 Koepp Ltd S1-65481 48 16.04 769.922014-09-05 08:54:41
1045 729833 Koepp Ltd S2-11481 6 26.50 159.002014-09-05 16:33:15
1046 737550 Fritsch, Russel and Anderson B1-33364 4 76.44 305.762014-09-06 08:59:08
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 15, "text": [ " account number name sku quantity \\\n", "1042 163416 Purdy-Kunde B1-38851 41 \n", "1043 714466 Trantow-Barrows S1-30248 1 \n", "1044 729833 Koepp Ltd S1-65481 48 \n", "1045 729833 Koepp Ltd S2-11481 6 \n", "1046 737550 Fritsch, Russel and Anderson B1-33364 4 \n", "\n", " unit price ext price date \n", "1042 98.69 4046.29 2014-09-05 01:52:32 \n", "1043 37.16 37.16 2014-09-05 06:17:19 \n", "1044 16.04 769.92 2014-09-05 08:54:41 \n", "1045 26.50 159.00 2014-09-05 16:33:15 \n", "1046 76.44 305.76 2014-09-06 08:59:08 " ] } ], "prompt_number": 15 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[df['date'] >='2014-03'].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
242 163416 Purdy-Kunde S1-30248 19 65.03 1235.572014-03-01 16:07:40
243 527099 Sanford and Sons S2-82423 3 76.21 228.632014-03-01 17:18:01
244 527099 Sanford and Sons B1-50809 8 70.78 566.242014-03-01 18:53:09
245 737550 Fritsch, Russel and Anderson B1-50809 20 50.11 1002.202014-03-01 23:47:17
246 688981 Keeling LLC B1-86481 -1 97.16 -97.162014-03-02 01:46:44
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 16, "text": [ " account number name sku quantity \\\n", "242 163416 Purdy-Kunde S1-30248 19 \n", "243 527099 Sanford and Sons S2-82423 3 \n", "244 527099 Sanford and Sons B1-50809 8 \n", "245 737550 Fritsch, Russel and Anderson B1-50809 20 \n", "246 688981 Keeling LLC B1-86481 -1 \n", "\n", " unit price ext price date \n", "242 65.03 1235.57 2014-03-01 16:07:40 \n", "243 76.21 228.63 2014-03-01 17:18:01 \n", "244 70.78 566.24 2014-03-01 18:53:09 \n", "245 50.11 1002.20 2014-03-01 23:47:17 \n", "246 97.16 -97.16 2014-03-02 01:46:44 " ] } ], "prompt_number": 16 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Of course, you can chain the criteria." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[(df['date'] >='20140701') & (df['date'] <= '20140715')].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
778 737550 Fritsch, Russel and Anderson S1-65481 35 70.51 2467.852014-07-01 00:21:58
779 218895 Kulas Inc S1-30248 9 16.56 149.042014-07-01 00:52:38
780 163416 Purdy-Kunde S2-82423 44 68.27 3003.882014-07-01 08:15:52
781 672390 Kuhn-Gusikowski B1-04202 48 99.39 4770.722014-07-01 11:12:13
782 642753 Pollich LLC S2-23246 1 51.29 51.292014-07-02 04:02:39
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 17, "text": [ " account number name sku quantity \\\n", "778 737550 Fritsch, Russel and Anderson S1-65481 35 \n", "779 218895 Kulas Inc S1-30248 9 \n", "780 163416 Purdy-Kunde S2-82423 44 \n", "781 672390 Kuhn-Gusikowski B1-04202 48 \n", "782 642753 Pollich LLC S2-23246 1 \n", "\n", " unit price ext price date \n", "778 70.51 2467.85 2014-07-01 00:21:58 \n", "779 16.56 149.04 2014-07-01 00:52:38 \n", "780 68.27 3003.88 2014-07-01 08:15:52 \n", "781 99.39 4770.72 2014-07-01 11:12:13 \n", "782 51.29 51.29 2014-07-02 04:02:39 " ] } ], "prompt_number": 17 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Because pandas understands date columns, you can express the date value in multiple formats and it will give you the results you expect." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[df['date'] >= 'Oct-2014'].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
1168 307599 Kassulke, Ondricka and Metz S2-23246 6 88.90 533.402014-10-08 06:19:50
1169 424914 White-Trantow S2-10342 25 58.54 1463.502014-10-08 07:31:40
1170 163416 Purdy-Kunde S1-27722 22 34.41 757.022014-10-08 09:01:18
1171 163416 Purdy-Kunde B1-33087 7 79.29 555.032014-10-08 15:39:13
1172 672390 Kuhn-Gusikowski B1-38851 30 94.64 2839.202014-10-09 00:22:33
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 26, "text": [ " account number name sku quantity \\\n", "1168 307599 Kassulke, Ondricka and Metz S2-23246 6 \n", "1169 424914 White-Trantow S2-10342 25 \n", "1170 163416 Purdy-Kunde S1-27722 22 \n", "1171 163416 Purdy-Kunde B1-33087 7 \n", "1172 672390 Kuhn-Gusikowski B1-38851 30 \n", "\n", " unit price ext price date \n", "1168 88.90 533.40 2014-10-08 06:19:50 \n", "1169 58.54 1463.50 2014-10-08 07:31:40 \n", "1170 34.41 757.02 2014-10-08 09:01:18 \n", "1171 79.29 555.03 2014-10-08 15:39:13 \n", "1172 94.64 2839.20 2014-10-09 00:22:33 " ] } ], "prompt_number": 26 }, { "cell_type": "code", "collapsed": false, "input": [ "df[df['date'] >= '10-10-2014'].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
1174 257198 Cronin, Oberbrunner and Spencer S2-34077 13 12.24 159.122014-10-10 02:59:06
1175 740150 Barton LLC S1-65481 28 53.00 1484.002014-10-10 15:08:53
1176 146832 Kiehn-Spinka S1-27722 15 64.39 965.852014-10-10 18:24:01
1177 257198 Cronin, Oberbrunner and Spencer S2-16558 3 35.34 106.022014-10-11 01:48:13
1178 737550 Fritsch, Russel and Anderson B1-53636 10 56.95 569.502014-10-11 10:25:53
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 27, "text": [ " account number name sku quantity \\\n", "1174 257198 Cronin, Oberbrunner and Spencer S2-34077 13 \n", "1175 740150 Barton LLC S1-65481 28 \n", "1176 146832 Kiehn-Spinka S1-27722 15 \n", "1177 257198 Cronin, Oberbrunner and Spencer S2-16558 3 \n", "1178 737550 Fritsch, Russel and Anderson B1-53636 10 \n", "\n", " unit price ext price date \n", "1174 12.24 159.12 2014-10-10 02:59:06 \n", "1175 53.00 1484.00 2014-10-10 15:08:53 \n", "1176 64.39 965.85 2014-10-10 18:24:01 \n", "1177 35.34 106.02 2014-10-11 01:48:13 \n", "1178 56.95 569.50 2014-10-11 10:25:53 " ] } ], "prompt_number": 27 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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.\n", "\n", "Set the new index using `set_index`." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df2 = df.set_index(['date'])\n", "df2.head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext price
date
2014-01-01 07:21:51 740150 Barton LLC B1-20000 39 86.69 3380.91
2014-01-01 10:00:47 714466 Trantow-Barrows S2-77896 -1 63.16 -63.16
2014-01-01 13:24:58 218895 Kulas Inc B1-69924 23 90.70 2086.10
2014-01-01 15:05:22 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.05
2014-01-01 23:26:55 412290 Jerde-Hilpert S2-34077 6 83.21 499.26
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 28, "text": [ " account number name sku \\\n", "date \n", "2014-01-01 07:21:51 740150 Barton LLC B1-20000 \n", "2014-01-01 10:00:47 714466 Trantow-Barrows S2-77896 \n", "2014-01-01 13:24:58 218895 Kulas Inc B1-69924 \n", "2014-01-01 15:05:22 307599 Kassulke, Ondricka and Metz S1-65481 \n", "2014-01-01 23:26:55 412290 Jerde-Hilpert S2-34077 \n", "\n", " quantity unit price ext price \n", "date \n", "2014-01-01 07:21:51 39 86.69 3380.91 \n", "2014-01-01 10:00:47 -1 63.16 -63.16 \n", "2014-01-01 13:24:58 23 90.70 2086.10 \n", "2014-01-01 15:05:22 41 21.05 863.05 \n", "2014-01-01 23:26:55 6 83.21 499.26 " ] } ], "prompt_number": 28 }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can slice the data to get a range." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df2[\"20140101\":\"20140201\"].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext price
date
2014-01-01 07:21:51 740150 Barton LLC B1-20000 39 86.69 3380.91
2014-01-01 10:00:47 714466 Trantow-Barrows S2-77896 -1 63.16 -63.16
2014-01-01 13:24:58 218895 Kulas Inc B1-69924 23 90.70 2086.10
2014-01-01 15:05:22 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.05
2014-01-01 23:26:55 412290 Jerde-Hilpert S2-34077 6 83.21 499.26
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 29, "text": [ " account number name sku \\\n", "date \n", "2014-01-01 07:21:51 740150 Barton LLC B1-20000 \n", "2014-01-01 10:00:47 714466 Trantow-Barrows S2-77896 \n", "2014-01-01 13:24:58 218895 Kulas Inc B1-69924 \n", "2014-01-01 15:05:22 307599 Kassulke, Ondricka and Metz S1-65481 \n", "2014-01-01 23:26:55 412290 Jerde-Hilpert S2-34077 \n", "\n", " quantity unit price ext price \n", "date \n", "2014-01-01 07:21:51 39 86.69 3380.91 \n", "2014-01-01 10:00:47 -1 63.16 -63.16 \n", "2014-01-01 13:24:58 23 90.70 2086.10 \n", "2014-01-01 15:05:22 41 21.05 863.05 \n", "2014-01-01 23:26:55 6 83.21 499.26 " ] } ], "prompt_number": 29 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Once again, we can use various date representations to remove any ambiguity around date naming conventions." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df2[\"2014-Jan-1\":\"2014-Feb-1\"].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext price
date
2014-01-01 07:21:51 740150 Barton LLC B1-20000 39 86.69 3380.91
2014-01-01 10:00:47 714466 Trantow-Barrows S2-77896 -1 63.16 -63.16
2014-01-01 13:24:58 218895 Kulas Inc B1-69924 23 90.70 2086.10
2014-01-01 15:05:22 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.05
2014-01-01 23:26:55 412290 Jerde-Hilpert S2-34077 6 83.21 499.26
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 38, "text": [ " account number name sku \\\n", "date \n", "2014-01-01 07:21:51 740150 Barton LLC B1-20000 \n", "2014-01-01 10:00:47 714466 Trantow-Barrows S2-77896 \n", "2014-01-01 13:24:58 218895 Kulas Inc B1-69924 \n", "2014-01-01 15:05:22 307599 Kassulke, Ondricka and Metz S1-65481 \n", "2014-01-01 23:26:55 412290 Jerde-Hilpert S2-34077 \n", "\n", " quantity unit price ext price \n", "date \n", "2014-01-01 07:21:51 39 86.69 3380.91 \n", "2014-01-01 10:00:47 -1 63.16 -63.16 \n", "2014-01-01 13:24:58 23 90.70 2086.10 \n", "2014-01-01 15:05:22 41 21.05 863.05 \n", "2014-01-01 23:26:55 6 83.21 499.26 " ] } ], "prompt_number": 38 }, { "cell_type": "code", "collapsed": false, "input": [ "df2[\"2014-Jan-1\":\"2014-Feb-1\"].tail()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext price
date
2014-01-31 22:51:18 383080 Will LLC B1-05914 43 80.17 3447.31
2014-02-01 09:04:59 383080 Will LLC B1-20000 7 33.69 235.83
2014-02-01 11:51:46 412290 Jerde-Hilpert S1-27722 11 21.12 232.32
2014-02-01 17:24:32 412290 Jerde-Hilpert B1-86481 3 35.99 107.97
2014-02-01 19:56:48 412290 Jerde-Hilpert B1-20000 23 78.90 1814.70
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 39, "text": [ " account number name sku quantity \\\n", "date \n", "2014-01-31 22:51:18 383080 Will LLC B1-05914 43 \n", "2014-02-01 09:04:59 383080 Will LLC B1-20000 7 \n", "2014-02-01 11:51:46 412290 Jerde-Hilpert S1-27722 11 \n", "2014-02-01 17:24:32 412290 Jerde-Hilpert B1-86481 3 \n", "2014-02-01 19:56:48 412290 Jerde-Hilpert B1-20000 23 \n", "\n", " unit price ext price \n", "date \n", "2014-01-31 22:51:18 80.17 3447.31 \n", "2014-02-01 09:04:59 33.69 235.83 \n", "2014-02-01 11:51:46 21.12 232.32 \n", "2014-02-01 17:24:32 35.99 107.97 \n", "2014-02-01 19:56:48 78.90 1814.70 " ] } ], "prompt_number": 39 }, { "cell_type": "code", "collapsed": false, "input": [ "df2[\"2014\"].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext price
date
2014-01-01 07:21:51 740150 Barton LLC B1-20000 39 86.69 3380.91
2014-01-01 10:00:47 714466 Trantow-Barrows S2-77896 -1 63.16 -63.16
2014-01-01 13:24:58 218895 Kulas Inc B1-69924 23 90.70 2086.10
2014-01-01 15:05:22 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.05
2014-01-01 23:26:55 412290 Jerde-Hilpert S2-34077 6 83.21 499.26
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 40, "text": [ " account number name sku \\\n", "date \n", "2014-01-01 07:21:51 740150 Barton LLC B1-20000 \n", "2014-01-01 10:00:47 714466 Trantow-Barrows S2-77896 \n", "2014-01-01 13:24:58 218895 Kulas Inc B1-69924 \n", "2014-01-01 15:05:22 307599 Kassulke, Ondricka and Metz S1-65481 \n", "2014-01-01 23:26:55 412290 Jerde-Hilpert S2-34077 \n", "\n", " quantity unit price ext price \n", "date \n", "2014-01-01 07:21:51 39 86.69 3380.91 \n", "2014-01-01 10:00:47 -1 63.16 -63.16 \n", "2014-01-01 13:24:58 23 90.70 2086.10 \n", "2014-01-01 15:05:22 41 21.05 863.05 \n", "2014-01-01 23:26:55 6 83.21 499.26 " ] } ], "prompt_number": 40 }, { "cell_type": "code", "collapsed": false, "input": [ "df2[\"2014-Dec\"].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext price
date
2014-12-01 20:15:34 714466 Trantow-Barrows S1-82801 3 77.97 233.91
2014-12-02 20:00:04 146832 Kiehn-Spinka S2-23246 37 57.81 2138.97
2014-12-03 04:43:53 218895 Kulas Inc S2-77896 30 77.44 2323.20
2014-12-03 06:05:43 141962 Herman LLC B1-53102 20 26.12 522.40
2014-12-03 14:17:34 642753 Pollich LLC B1-53636 19 71.21 1352.99
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 42, "text": [ " account number name sku quantity \\\n", "date \n", "2014-12-01 20:15:34 714466 Trantow-Barrows S1-82801 3 \n", "2014-12-02 20:00:04 146832 Kiehn-Spinka S2-23246 37 \n", "2014-12-03 04:43:53 218895 Kulas Inc S2-77896 30 \n", "2014-12-03 06:05:43 141962 Herman LLC B1-53102 20 \n", "2014-12-03 14:17:34 642753 Pollich LLC B1-53636 19 \n", "\n", " unit price ext price \n", "date \n", "2014-12-01 20:15:34 77.97 233.91 \n", "2014-12-02 20:00:04 57.81 2138.97 \n", "2014-12-03 04:43:53 77.44 2323.20 \n", "2014-12-03 06:05:43 26.12 522.40 \n", "2014-12-03 14:17:34 71.21 1352.99 " ] } ], "prompt_number": 42 }, { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Additional String Functions" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[df['sku'].str.contains('B1')].head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
0 740150 Barton LLC B1-20000 39 86.69 3380.912014-01-01 07:21:51
2 218895 Kulas Inc B1-69924 23 90.70 2086.102014-01-01 13:24:58
6 218895 Kulas Inc B1-65551 2 31.10 62.202014-01-02 10:57:23
14 737550 Fritsch, Russel and Anderson B1-53102 23 71.56 1645.882014-01-04 08:57:48
17 239344 Stokes LLC B1-50809 14 16.23 227.222014-01-04 22:14:32
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 43, "text": [ " account number name sku quantity \\\n", "0 740150 Barton LLC B1-20000 39 \n", "2 218895 Kulas Inc B1-69924 23 \n", "6 218895 Kulas Inc B1-65551 2 \n", "14 737550 Fritsch, Russel and Anderson B1-53102 23 \n", "17 239344 Stokes LLC B1-50809 14 \n", "\n", " unit price ext price date \n", "0 86.69 3380.91 2014-01-01 07:21:51 \n", "2 90.70 2086.10 2014-01-01 13:24:58 \n", "6 31.10 62.20 2014-01-02 10:57:23 \n", "14 71.56 1645.88 2014-01-04 08:57:48 \n", "17 16.23 227.22 2014-01-04 22:14:32 " ] } ], "prompt_number": 43 }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can string queries together and use sort to control how the data is ordered." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[(df['sku'].str.contains('B1-531')) & (df['quantity']>40)].sort(columns=['quantity','name'],ascending=[0,1])" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
684 642753 Pollich LLC B1-53102 46 26.07 1199.222014-06-08 19:33:33
792 688981 Keeling LLC B1-53102 45 41.19 1853.552014-07-04 21:42:22
176 383080 Will LLC B1-53102 45 89.22 4014.902014-02-11 04:14:09
1213 604255 Halvorson, Crona and Champlin B1-53102 41 55.05 2257.052014-10-18 19:27:01
1215 307599 Kassulke, Ondricka and Metz B1-53102 41 93.70 3841.702014-10-18 23:25:10
1128 714466 Trantow-Barrows B1-53102 41 55.68 2282.882014-09-27 10:42:48
1001 424914 White-Trantow B1-53102 41 81.25 3331.252014-08-26 11:44:30
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 44, "text": [ " account number name sku quantity \\\n", "684 642753 Pollich LLC B1-53102 46 \n", "792 688981 Keeling LLC B1-53102 45 \n", "176 383080 Will LLC B1-53102 45 \n", "1213 604255 Halvorson, Crona and Champlin B1-53102 41 \n", "1215 307599 Kassulke, Ondricka and Metz B1-53102 41 \n", "1128 714466 Trantow-Barrows B1-53102 41 \n", "1001 424914 White-Trantow B1-53102 41 \n", "\n", " unit price ext price date \n", "684 26.07 1199.22 2014-06-08 19:33:33 \n", "792 41.19 1853.55 2014-07-04 21:42:22 \n", "176 89.22 4014.90 2014-02-11 04:14:09 \n", "1213 55.05 2257.05 2014-10-18 19:27:01 \n", "1215 93.70 3841.70 2014-10-18 23:25:10 \n", "1128 55.68 2282.88 2014-09-27 10:42:48 \n", "1001 81.25 3331.25 2014-08-26 11:44:30 " ] } ], "prompt_number": 44 }, { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Bonus Task" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[\"name\"].unique()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 45, "text": [ "array([u'Barton LLC', u'Trantow-Barrows', u'Kulas Inc',\n", " u'Kassulke, Ondricka and Metz', u'Jerde-Hilpert', u'Koepp Ltd',\n", " u'Fritsch, Russel and Anderson', u'Kiehn-Spinka', u'Keeling LLC',\n", " u'Frami, Hills and Schmidt', u'Stokes LLC', u'Kuhn-Gusikowski',\n", " u'Herman LLC', u'White-Trantow', u'Sanford and Sons',\n", " u'Pollich LLC', u'Will LLC', u'Cronin, Oberbrunner and Spencer',\n", " u'Halvorson, Crona and Champlin', u'Purdy-Kunde'], dtype=object)" ] } ], "prompt_number": 45 }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we wanted to include the account number, we could use `drop_duplicates`." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df.drop_duplicates(subset=[\"account number\",\"name\"]).head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbernameskuquantityunit priceext pricedate
0 740150 Barton LLC B1-20000 39 86.69 3380.912014-01-01 07:21:51
1 714466 Trantow-Barrows S2-77896 -1 63.16 -63.162014-01-01 10:00:47
2 218895 Kulas Inc B1-69924 23 90.70 2086.102014-01-01 13:24:58
3 307599 Kassulke, Ondricka and Metz S1-65481 41 21.05 863.052014-01-01 15:05:22
4 412290 Jerde-Hilpert S2-34077 6 83.21 499.262014-01-01 23:26:55
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 48, "text": [ " account number name sku quantity \\\n", "0 740150 Barton LLC B1-20000 39 \n", "1 714466 Trantow-Barrows S2-77896 -1 \n", "2 218895 Kulas Inc B1-69924 23 \n", "3 307599 Kassulke, Ondricka and Metz S1-65481 41 \n", "4 412290 Jerde-Hilpert S2-34077 6 \n", "\n", " unit price ext price date \n", "0 86.69 3380.91 2014-01-01 07:21:51 \n", "1 63.16 -63.16 2014-01-01 10:00:47 \n", "2 90.70 2086.10 2014-01-01 13:24:58 \n", "3 21.05 863.05 2014-01-01 15:05:22 \n", "4 83.21 499.26 2014-01-01 23:26:55 " ] } ], "prompt_number": 48 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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`." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df.drop_duplicates(subset=[\"account number\",\"name\"]).ix[:,[0,1]]" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbername
0 740150 Barton LLC
1 714466 Trantow-Barrows
2 218895 Kulas Inc
3 307599 Kassulke, Ondricka and Metz
4 412290 Jerde-Hilpert
7 729833 Koepp Ltd
9 737550 Fritsch, Russel and Anderson
10 146832 Kiehn-Spinka
11 688981 Keeling LLC
12 786968 Frami, Hills and Schmidt
15 239344 Stokes LLC
16 672390 Kuhn-Gusikowski
18 141962 Herman LLC
20 424914 White-Trantow
21 527099 Sanford and Sons
30 642753 Pollich LLC
37 383080 Will LLC
51 257198 Cronin, Oberbrunner and Spencer
67 604255 Halvorson, Crona and Champlin
106 163416 Purdy-Kunde
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 49, "text": [ " account number name\n", "0 740150 Barton LLC\n", "1 714466 Trantow-Barrows\n", "2 218895 Kulas Inc\n", "3 307599 Kassulke, Ondricka and Metz\n", "4 412290 Jerde-Hilpert\n", "7 729833 Koepp Ltd\n", "9 737550 Fritsch, Russel and Anderson\n", "10 146832 Kiehn-Spinka\n", "11 688981 Keeling LLC\n", "12 786968 Frami, Hills and Schmidt\n", "15 239344 Stokes LLC\n", "16 672390 Kuhn-Gusikowski\n", "18 141962 Herman LLC\n", "20 424914 White-Trantow\n", "21 527099 Sanford and Sons\n", "30 642753 Pollich LLC\n", "37 383080 Will LLC\n", "51 257198 Cronin, Oberbrunner and Spencer\n", "67 604255 Halvorson, Crona and Champlin\n", "106 163416 Purdy-Kunde" ] } ], "prompt_number": 49 }, { "cell_type": "markdown", "metadata": {}, "source": [ "I hope you found this useful. I encourage you to try and apply these ideas to some of your own repetitive Excel tasks and streamline your work flow." ] }, { "cell_type": "code", "collapsed": false, "input": [], "language": "python", "metadata": {}, "outputs": [] } ], "metadata": {} } ] }