{ "metadata": { "name": "", "signature": "sha256:ab64e742a8053b080efcc43a2f547a91386c9e4eadda20b422e6afd5a089ab20" }, "nbformat": 3, "nbformat_minor": 0, "worksheets": [ { "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Apply Operations To Groups 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": 60 }, { "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": 61, "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": 61 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Create a groupby variable that groups preTestScores by regiment" ] }, { "cell_type": "code", "collapsed": false, "input": [ "groupby_regiment = df['preTestScore'].groupby(df['company'])\n", "groupby_regiment" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 62, "text": [ "" ] } ], "prompt_number": 62 }, { "cell_type": "markdown", "metadata": {}, "source": [ "\"This grouped variable is now a GroupBy object. It has not actually computed anything yet except for some intermediate data about the group key df['key1']. The idea is that this object has all of the information needed to then apply some operation to each of the groups.\" - Python for Data Analysis" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### View a grouping\n", "\n", "Use list() to show what a grouping looks like" ] }, { "cell_type": "code", "collapsed": false, "input": [ "list(df['preTestScore'].groupby(df['company']))" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 63, "text": [ "[('1st', 0 4\n", " 1 24\n", " 4 3\n", " 5 4\n", " 8 2\n", " 9 3\n", " Name: preTestScore, dtype: int64), ('2nd', 2 31\n", " 3 2\n", " 6 24\n", " 7 31\n", " 10 2\n", " 11 3\n", " Name: preTestScore, dtype: int64)]" ] } ], "prompt_number": 63 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Descriptive statistics by group" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df['preTestScore'].groupby(df['company']).describe()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 64, "text": [ "company \n", "1st count 6.000000\n", " mean 6.666667\n", " std 8.524475\n", " min 2.000000\n", " 25% 3.000000\n", " 50% 3.500000\n", " 75% 4.000000\n", " max 24.000000\n", "2nd count 6.000000\n", " mean 15.500000\n", " std 14.652645\n", " min 2.000000\n", " 25% 2.250000\n", " 50% 13.500000\n", " 75% 29.250000\n", " max 31.000000\n", "dtype: float64" ] } ], "prompt_number": 64 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Mean of each regiment's preTestScore" ] }, { "cell_type": "code", "collapsed": false, "input": [ "groupby_regiment.mean()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 65, "text": [ "company\n", "1st 6.666667\n", "2nd 15.500000\n", "dtype: float64" ] } ], "prompt_number": 65 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Mean preTestScores grouped by regiment and company" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df['preTestScore'].groupby([df['regiment'], df['company']]).mean()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 66, "text": [ "regiment company\n", "Dragoons 1st 3.5\n", " 2nd 27.5\n", "Nighthawks 1st 14.0\n", " 2nd 16.5\n", "Scouts 1st 2.5\n", " 2nd 2.5\n", "dtype: float64" ] } ], "prompt_number": 66 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Mean preTestScores grouped by regiment and company without heirarchical indexing" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df['preTestScore'].groupby([df['regiment'], df['company']]).mean().unstack()" ], "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", "
company1st2nd
regiment
Dragoons 3.5 27.5
Nighthawks 14.0 16.5
Scouts 2.5 2.5
\n", "

3 rows \u00d7 2 columns

\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 67, "text": [ "company 1st 2nd\n", "regiment \n", "Dragoons 3.5 27.5\n", "Nighthawks 14.0 16.5\n", "Scouts 2.5 2.5\n", "\n", "[3 rows x 2 columns]" ] } ], "prompt_number": 67 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Group the entire dataframe by regiment and company" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df.groupby(['regiment', 'company']).mean()" ], "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", "
preTestScorepostTestScore
regimentcompany
Dragoons1st 3.5 47.5
2nd 27.5 75.5
Nighthawks1st 14.0 59.5
2nd 16.5 59.5
Scouts1st 2.5 66.0
2nd 2.5 66.0
\n", "

6 rows \u00d7 2 columns

\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 68, "text": [ " preTestScore postTestScore\n", "regiment company \n", "Dragoons 1st 3.5 47.5\n", " 2nd 27.5 75.5\n", "Nighthawks 1st 14.0 59.5\n", " 2nd 16.5 59.5\n", "Scouts 1st 2.5 66.0\n", " 2nd 2.5 66.0\n", "\n", "[6 rows x 2 columns]" ] } ], "prompt_number": 68 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Number of observations in each regiment and company" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df.groupby(['regiment', 'company']).size()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 69, "text": [ "regiment company\n", "Dragoons 1st 2\n", " 2nd 2\n", "Nighthawks 1st 2\n", " 2nd 2\n", "Scouts 1st 2\n", " 2nd 2\n", "dtype: int64" ] } ], "prompt_number": 69 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Iterate an operations over groups" ] }, { "cell_type": "code", "collapsed": false, "input": [ "# Group the dataframe by regiment, and for each regiment,\n", "for name, group in df.groupby('regiment'): \n", " # print the name of the regiment\n", " print(name)\n", " # print the data of that regiment\n", " print(group)" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "Dragoons\n", " regiment company name preTestScore postTestScore\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", "\n", "[4 rows x 5 columns]\n", "Nighthawks\n", " 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", "\n", "[4 rows x 5 columns]\n", "Scouts\n", " regiment company name preTestScore postTestScore\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", "[4 rows x 5 columns]\n" ] } ], "prompt_number": 70 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Group by columns\n", "\n", "Specifically in this case: group by the data types of the columns (i.e. axis=1) and then use list() to view what that grouping looks like" ] }, { "cell_type": "code", "collapsed": false, "input": [ "list(df.groupby(df.dtypes, axis=1))" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 71, "text": [ "[(dtype('int64'), preTestScore postTestScore\n", " 0 4 25\n", " 1 24 94\n", " 2 31 57\n", " 3 2 62\n", " 4 3 70\n", " 5 4 25\n", " 6 24 94\n", " 7 31 57\n", " 8 2 62\n", " 9 3 70\n", " 10 2 62\n", " 11 3 70\n", " \n", " [12 rows x 2 columns]), (dtype('O'), regiment company name\n", " 0 Nighthawks 1st Miller\n", " 1 Nighthawks 1st Jacobson\n", " 2 Nighthawks 2nd Ali\n", " 3 Nighthawks 2nd Milner\n", " 4 Dragoons 1st Cooze\n", " 5 Dragoons 1st Jacon\n", " 6 Dragoons 2nd Ryaner\n", " 7 Dragoons 2nd Sone\n", " 8 Scouts 1st Sloan\n", " 9 Scouts 1st Piger\n", " 10 Scouts 2nd Riani\n", " 11 Scouts 2nd Ali\n", " \n", " [12 rows x 3 columns])]" ] } ], "prompt_number": 71 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### In the dataframe \"df\", group by \"regiments, take the mean values of the other variables for those groups, then display them with the prefix_mean" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df.groupby('regiment').mean().add_prefix('mean_')" ], "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", "
mean_preTestScoremean_postTestScore
regiment
Dragoons 15.50 61.5
Nighthawks 15.25 59.5
Scouts 2.50 66.0
\n", "

3 rows \u00d7 2 columns

\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 72, "text": [ " mean_preTestScore mean_postTestScore\n", "regiment \n", "Dragoons 15.50 61.5\n", "Nighthawks 15.25 59.5\n", "Scouts 2.50 66.0\n", "\n", "[3 rows x 2 columns]" ] } ], "prompt_number": 72 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Create a function to get the stats of a group" ] }, { "cell_type": "code", "collapsed": false, "input": [ "def get_stats(group):\n", " return {'min': group.min(), 'max': group.max(), 'count': group.count(), 'mean': group.mean()}" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 73 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Create bins and bin up postTestScore by those pins" ] }, { "cell_type": "code", "collapsed": false, "input": [ "bins = [0, 25, 50, 75, 100]\n", "group_names = ['Low', 'Okay', 'Good', 'Great']\n", "df['categories'] = pd.cut(df['postTestScore'], bins, labels=group_names)" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 75 }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Apply the get_stats() function to each postTestScore bin" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df['postTestScore'].groupby(df['categories']).apply(get_stats).unstack()" ], "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", "
countmaxmeanmin
categories
Good 8 70 63.75 57
Great 2 94 94.00 94
Low 2 25 25.00 25
\n", "

3 rows \u00d7 4 columns

\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 80, "text": [ " count max mean min\n", "categories \n", "Good 8 70 63.75 57\n", "Great 2 94 94.00 94\n", "Low 2 25 25.00 25\n", "\n", "[3 rows x 4 columns]" ] } ], "prompt_number": 80 } ], "metadata": {} } ] }