{ "metadata": { "name": "", "signature": "sha256:d5497e4bfa5d9a5fc48f3187e7baffec551045a0119520e693d3663ff724296b" }, "nbformat": 3, "nbformat_minor": 0, "worksheets": [ { "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Pivot Tables In Pandas\n", "\n", "- **Author:** [Chris Albon](http://www.chrisalbon.com/), [@ChrisAlbon](https://twitter.com/chrisalbon)\n", "- **Date:** -\n", "- **Repo:** [Python 3 code snippets for data science](https://github.com/chrisalbon/code_py)\n", "- **Note:**" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### import modules" ] }, { "cell_type": "code", "collapsed": false, "input": [ "import pandas as pd" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 2 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Create dataframe" ] }, { "cell_type": "code", "collapsed": false, "input": [ "raw_data = {'regiment': ['Nighthawks', 'Nighthawks', 'Nighthawks', 'Nighthawks', 'Dragoons', 'Dragoons', 'Dragoons', 'Dragoons', 'Scouts', 'Scouts', 'Scouts', 'Scouts'], \n", " 'company': ['1st', '1st', '2nd', '2nd', '1st', '1st', '2nd', '2nd','1st', '1st', '2nd', '2nd'], \n", " 'name': ['Miller', 'Jacobson', 'Ali', 'Milner', 'Cooze', 'Jacon', 'Ryaner', 'Sone', 'Sloan', 'Piger', 'Riani', 'Ali'], \n", " 'preTestScore': [4, 24, 31, 2, 3, 4, 24, 31, 2, 3, 2, 3],\n", " 'postTestScore': [25, 94, 57, 62, 70, 25, 94, 57, 62, 70, 62, 70]}\n", "df = pd.DataFrame(raw_data, columns = ['regiment', 'company', 'name', 'preTestScore', 'postTestScore'])\n", "df" ], "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", "
regimentcompanynamepreTestScorepostTestScore
0 Nighthawks 1st Miller 4 25
1 Nighthawks 1st Jacobson 24 94
2 Nighthawks 2nd Ali 31 57
3 Nighthawks 2nd Milner 2 62
4 Dragoons 1st Cooze 3 70
5 Dragoons 1st Jacon 4 25
6 Dragoons 2nd Ryaner 24 94
7 Dragoons 2nd Sone 31 57
8 Scouts 1st Sloan 2 62
9 Scouts 1st Piger 3 70
10 Scouts 2nd Riani 2 62
11 Scouts 2nd Ali 3 70
\n", "

12 rows \u00d7 5 columns

\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 3, "text": [ " regiment company name preTestScore postTestScore\n", "0 Nighthawks 1st Miller 4 25\n", "1 Nighthawks 1st Jacobson 24 94\n", "2 Nighthawks 2nd Ali 31 57\n", "3 Nighthawks 2nd Milner 2 62\n", "4 Dragoons 1st Cooze 3 70\n", "5 Dragoons 1st Jacon 4 25\n", "6 Dragoons 2nd Ryaner 24 94\n", "7 Dragoons 2nd Sone 31 57\n", "8 Scouts 1st Sloan 2 62\n", "9 Scouts 1st Piger 3 70\n", "10 Scouts 2nd Riani 2 62\n", "11 Scouts 2nd Ali 3 70\n", "\n", "[12 rows x 5 columns]" ] } ], "prompt_number": 3 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Create a pivot table of group means, by company and regiment" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df.pivot_table(rows=['regiment','company'])" ], "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", "
postTestScorepreTestScore
regimentcompany
Dragoons1st 47.5 3.5
2nd 75.5 27.5
Nighthawks1st 59.5 14.0
2nd 59.5 16.5
Scouts1st 66.0 2.5
2nd 66.0 2.5
\n", "

6 rows \u00d7 2 columns

\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 4, "text": [ " postTestScore preTestScore\n", "regiment company \n", "Dragoons 1st 47.5 3.5\n", " 2nd 75.5 27.5\n", "Nighthawks 1st 59.5 14.0\n", " 2nd 59.5 16.5\n", "Scouts 1st 66.0 2.5\n", " 2nd 66.0 2.5\n", "\n", "[6 rows x 2 columns]" ] } ], "prompt_number": 4 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Create a pivot table of group score counts, by company and regimensts" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df.pivot_table(rows=['regiment','company'], aggfunc='count')" ], "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", "
companynamepostTestScorepreTestScoreregiment
regimentcompany
Dragoons1st 2 2 2 2 2
2nd 2 2 2 2 2
Nighthawks1st 2 2 2 2 2
2nd 2 2 2 2 2
Scouts1st 2 2 2 2 2
2nd 2 2 2 2 2
\n", "

6 rows \u00d7 5 columns

\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 6, "text": [ " company name postTestScore preTestScore regiment\n", "regiment company \n", "Dragoons 1st 2 2 2 2 2\n", " 2nd 2 2 2 2 2\n", "Nighthawks 1st 2 2 2 2 2\n", " 2nd 2 2 2 2 2\n", "Scouts 1st 2 2 2 2 2\n", " 2nd 2 2 2 2 2\n", "\n", "[6 rows x 5 columns]" ] } ], "prompt_number": 6 } ], "metadata": {} } ] }