<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Illustration of datascience Tables on Open Data from Berkeley
David E. Culler
Datascience Table provides a simple, yet powerful data structure for a range of analyses.  The basic concept is an ordered set of named columns.  
It builds on the intuition many develop with excel - data is represented as rectangular regions.  But, rather than labeling cells, the column labels really mean something. 
Tables embed smoothly in jupyter notebooks, so the user experience is that of a computational document, rather than a spreadsheet. This provides a much clearer sequence of steps from raw data to finished product, at least if they are well constructed.  There is no need to break out into visual basic or the like when you need more than the basics.
Tables draws heavily from relational database tables, but there is no separate language (e.g., SQL) required to do relational operations in them.
Tables provide also the concepts associated with pivot tables in Excel, which are closely related to relational operations, but often more natural.
Tables can be viewed as simple variants of the dataframes in R or Pandas.  The key is simplicity.  They are simple rectangular structures.  The cells of a table can hold arbitrary values, although columns are homogeneous, so the additional power (and complexity) of higher dimensions are rarely needed.
Tables builds directly on the scipy ecosystem.  Almost any sequence can go in, numpy.arrays come out.  Thus, it is natural to manipulate data directly from Tables. Basic visualization is provide directly in terms of Tables, so you can go a long ways before reading matplotlib documentation.  However, if you need more, a lot of it can be gained through keyword args - and if that is not enough, drop into scipy.
This notebook illustrates some of the use of Tables using municipal salary data made possible though the City of Berkeley's open data portal.
Step1:  Reading raw data into a Table
Let's pull a big wad of City Employee salary data from the Berkeley Open Data portal.  
Since this was a trial till June 30, 2015 and you have to mouse around to get csv files, we happen to have pulled a local copy.
Step2:  Accessing data in a Table
A column of Table data is accessed by indexing it by name.  This returns the column as a numpy array.
Step3:  Some prefer the selectors - column and row
Step4:  Rows in the table can be indexed and sliced.  A row is a little like a record or dict.  It is an tupled, ordered according to the table it comes from and keeping the column names.
Step5:  Converting data in a Table to make it workable
When we read in data from a csv file we got a bunch of columns filled with a bunch of strings.  As is often the case, we want the data in a column to represent values that we can analyze, whereas we want the printed format of a column to reflect its meaning.  Currency is the most common such situation.  Let's clean up our salary table.
We might start by getting ahold of the names of column that we want to clean up
Step6:  Clean derivatives of raw tables
It is good hygene to keep the raw data raw and produce distinct, clean derivatives.  Let's start by making a copy of the raw table.  A new name and a new table.
Step7:  Tables allow columns to have customized formatters
In Excel you do this by formatting the cells.  We want to have the data as numbers, keep track of the type, and have it look nice.
Step8:  Now we get values we can compute on - and they still display as currency.
Step9:  Descriptive Statistics Summary
Now we can try to get a summary of the data with some descriptive statistics.  
The stats method on Tables computes a list of statistics over each column and creates a new Table containing these statistics.  The default is tailored to the Berkeley Data8 course. Here we provide what you expect from the summary operation in R
Step10:  OK, so it looks like the average salary is about 86k, and it ranges up to 300k with some hefty overtime pay.  Let's see if we can understand what is going on.
Visualizing data
Table.select creates a new table consisting of the specified list of columns.
Table.hist plots a histogram of each of the columns in a table.  It can either overlay the histograms or show them separately.  Here we have specified the number of bins
Step11:  Interesting.  Base pay is bimodal.  Most employees get no overtime, but there is a looong tale.  Let's look at the individual level.  Who's at the top?
Step12:  So where does the $alary go? First, how many employees?
Table.num_rows returns just what you'd think. The number of rows.  Which in this case is the number of employees on the city payroll.
Step13:  Grouping and Sorting Table data
Table.drop creates a new Table without some columns.  It is like select, but you don't have to name everythng you want.
Table.group aggregates data by grouping all the rows that contain a common value in one (or more) columns.  Here we group in "Job Title" summing the entries in all other columns for each group.  We placed a column full of 1 to get a count, while summing salaries and such.
Table.sort sorts the rows in a Table by a column - just like sort in Excel.
Step14:  Wow, 305 Job Titles for 1437 employees!
Step15:  Plotting data
Table.plot plots each of the columns in a table, either on separate charts or overlayed on a single chart.  Optionally one of the columns can be specified as the horizontal axis and all others plotted against this.
Step16:  How about that, a few job categories have most of the employes and most of the spend, but it is far from uniform.  Let's look a little deeper.  Which categories consume most of the budget?
Step17:  As is often the case in the real world, the categorization used for operations is not directly useful for analysis.  We often need to build categories in order to get a handle on what's going on.
What do all those job titles look like
Step18:  Applying a function to create a new column
table.apply
Step19:  As is often the case working with real data, we often need to iterate a bit to get what we want out of it.  With all those titles, a lot of stuff is likely to end up as other.  
Here we have a little iterative process to get enough of the job titles categorized
Step20:  So no job title left has more than 10 employees in it, but some have quite a bit of cost.  We could go back and add more entries to our category table and iterate a bit.  The important thing is that we create new tables, we don't clobber old ones.  
Well this shows the challenge in managing budget pretty nicely.  Most of the money is spent in a few job categories.  But then there are still over 200 employees in a zillion other categories that are stile the #2 spend.
Step21:  So let's try to understand the police category a bit more.
Step22:  How do the pay labels spread across the force?
We can look at histograms by pay label.  First all toegether and then broken apart.
Step23:  Base pay seems to chunk into categories, perhaps by job title.  
Most members of the force do little overtime, but a few do a lot!
How many are in each Job Title?
Step24:  We can't just pivot by Job Title because we don't have a uniform number of rows, but what we can do is for pivot and bin (or histogram) so we can see the distribution of a column by job title.
Sure enough.  Officers cluster around 100-120k, sergeants at 130-140k, but there's a little overlap.
Step25:  Let's compare the 2011 data with more recent 2013 data.
Step26:  Well, the data base changed.  It picked up a few columns over the years. And we need to convert the salary strings to numbers so we can do analysis on them.  All in one go...
Step27:  Isn't that interesting.  They seem to have gotten their overtime under control.  Was that management, end of the occupy movement, something else?  Let's do a bit of comparison.
First we need to do some clean up and get labels we can deal with.
Step28:  Snap! They decided that case was a good idea for proper nouns.  Let's go back to the old way.
Step29:  Now we can put the two tables together to see what happened with employees who were around in both years.  Here we get to use another powerful operations on tables.
Table.join
Step30:  Let's add a column with increase in total pay.
Step31:  On the tails we have people who joined part way through 2011 or left part way through 2013.
Step32:  Well that's interesting.  Total compensation seems to have dropped.  Did the budget actually go down?
Step33:  Look at that.
Step34:  Let's see who got promoted or demoted
Step35:  Perhaps we might want to look at the relationship of these two variables.  That leads to another useful operator
Table.scatter
<ASSISTANT_TASK:>
Python Code:
# This useful nonsense just goes at the top
from datascience import *
import numpy as np
import matplotlib.pyplot as plots
plots.style.use('fivethirtyeight')
%matplotlib inline
# datascience version number of last run of this notebook
version.__version__
Explanation: Illustration of datascience Tables on Open Data from Berkeley
David E. Culler
Datascience Table provides a simple, yet powerful data structure for a range of analyses.  The basic concept is an ordered set of named columns.  
It builds on the intuition many develop with excel - data is represented as rectangular regions.  But, rather than labeling cells, the column labels really mean something. 
Tables embed smoothly in jupyter notebooks, so the user experience is that of a computational document, rather than a spreadsheet. This provides a much clearer sequence of steps from raw data to finished product, at least if they are well constructed.  There is no need to break out into visual basic or the like when you need more than the basics.
Tables draws heavily from relational database tables, but there is no separate language (e.g., SQL) required to do relational operations in them.
Tables provide also the concepts associated with pivot tables in Excel, which are closely related to relational operations, but often more natural.
Tables can be viewed as simple variants of the dataframes in R or Pandas.  The key is simplicity.  They are simple rectangular structures.  The cells of a table can hold arbitrary values, although columns are homogeneous, so the additional power (and complexity) of higher dimensions are rarely needed.
Tables builds directly on the scipy ecosystem.  Almost any sequence can go in, numpy.arrays come out.  Thus, it is natural to manipulate data directly from Tables. Basic visualization is provide directly in terms of Tables, so you can go a long ways before reading matplotlib documentation.  However, if you need more, a lot of it can be gained through keyword args - and if that is not enough, drop into scipy.
This notebook illustrates some of the use of Tables using municipal salary data made possible though the City of Berkeley's open data portal.
End of explanation
raw_berkeley_sal_2011 = Table.read_table("./data/City_of_Berkeley_Employee_Salaries_-_2011.csv")
# Tables are rendered to work in a notebook, even if they are large.  Only part shows.
# You can adjust how much of it you see, but here we'd have ~1500 rows!
raw_berkeley_sal_2011
Explanation: Reading raw data into a Table
Let's pull a big wad of City Employee salary data from the Berkeley Open Data portal.  
Since this was a trial till June 30, 2015 and you have to mouse around to get csv files, we happen to have pulled a local copy.
End of explanation
raw_berkeley_sal_2011['Base Pay']
Explanation: Accessing data in a Table
A column of Table data is accessed by indexing it by name.  This returns the column as a numpy array.
End of explanation
raw_berkeley_sal_2011.column('Overtime Pay')
Explanation: Some prefer the selectors - column and row
End of explanation
raw_berkeley_sal_2011.rows[0]
raw_berkeley_sal_2011.row(0).item('Base Pay')
raw_berkeley_sal_2011.row(0)[2]
raw_berkeley_sal_2011.row(0).asdict()
raw_berkeley_sal_2011.rows[0:10]
Explanation: Rows in the table can be indexed and sliced.  A row is a little like a record or dict.  It is an tupled, ordered according to the table it comes from and keeping the column names.
End of explanation
paylabels = raw_berkeley_sal_2011.labels[2:]
paylabels
Explanation: Converting data in a Table to make it workable
When we read in data from a csv file we got a bunch of columns filled with a bunch of strings.  As is often the case, we want the data in a column to represent values that we can analyze, whereas we want the printed format of a column to reflect its meaning.  Currency is the most common such situation.  Let's clean up our salary table.
We might start by getting ahold of the names of column that we want to clean up
End of explanation
berkeley_sal_2011 = raw_berkeley_sal_2011.copy()
Explanation: Clean derivatives of raw tables
It is good hygene to keep the raw data raw and produce distinct, clean derivatives.  Let's start by making a copy of the raw table.  A new name and a new table.
End of explanation
berkeley_sal_2011.set_format(paylabels, CurrencyFormatter)
berkeley_sal_2011
Explanation: Tables allow columns to have customized formatters
In Excel you do this by formatting the cells.  We want to have the data as numbers, keep track of the type, and have it look nice.
End of explanation
berkeley_sal_2011['Base Pay']
max(berkeley_sal_2011['Total Pay Benefits'])
Explanation: Now we get values we can compute on - and they still display as currency.
End of explanation
def firstQtile(x) : return np.percentile(x,25)
def thirdQtile(x) : return np.percentile(x,25)
summary_ops = (min, firstQtile, np.median, np.mean, thirdQtile, max)
berkeley_sal_2011.select(paylabels).stats(ops=summary_ops)
Explanation: Descriptive Statistics Summary
Now we can try to get a summary of the data with some descriptive statistics.  
The stats method on Tables computes a list of statistics over each column and creates a new Table containing these statistics.  The default is tailored to the Berkeley Data8 course. Here we provide what you expect from the summary operation in R
End of explanation
berkeley_sal_2011.select(["Base Pay", 
                          "Overtime Pay", 
                          "Total Pay Benefits"]).hist(overlay=False,normed=False,
                                                      bins=40)
Explanation: OK, so it looks like the average salary is about 86k, and it ranges up to 300k with some hefty overtime pay.  Let's see if we can understand what is going on.
Visualizing data
Table.select creates a new table consisting of the specified list of columns.
Table.hist plots a histogram of each of the columns in a table.  It can either overlay the histograms or show them separately.  Here we have specified the number of bins
End of explanation
berkeley_sal_2011.sort('Total Pay Benefits', descending=True)
Explanation: Interesting.  Base pay is bimodal.  Most employees get no overtime, but there is a looong tale.  Let's look at the individual level.  Who's at the top?
End of explanation
berkeley_sal_2011.num_rows
Explanation: So where does the $alary go? First, how many employees?
Table.num_rows returns just what you'd think. The number of rows.  Which in this case is the number of employees on the city payroll.
End of explanation
# lose the individual names
job_titles = berkeley_sal_2011.drop("Employee Name") 
# Build a handy column full of 1s
job_titles["Title"] = 1
# Group by title summing the number of rows per
by_title = job_titles.group("Job Title", sum)  
# Sort by the number of employees per title
ordered_by_title = by_title.sort('Title sum', descending = True) 
# let's see what we get
ordered_by_title 
ordered_by_title.num_rows
Explanation: Grouping and Sorting Table data
Table.drop creates a new Table without some columns.  It is like select, but you don't have to name everythng you want.
Table.group aggregates data by grouping all the rows that contain a common value in one (or more) columns.  Here we group in "Job Title" summing the entries in all other columns for each group.  We placed a column full of 1 to get a count, while summing salaries and such.
Table.sort sorts the rows in a Table by a column - just like sort in Excel.
End of explanation
"{0:.3} employees per Job Code".format(berkeley_sal_2011.num_rows/ordered_by_title.num_rows)
Explanation: Wow, 305 Job Titles for 1437 employees!
End of explanation
ordered_by_title.select(['Title sum','Total Pay Benefits sum']).sort('Title sum').plot(overlay=False)
Explanation: Plotting data
Table.plot plots each of the columns in a table, either on separate charts or overlayed on a single chart.  Optionally one of the columns can be specified as the horizontal axis and all others plotted against this.
End of explanation
by_title.sort('Total Pay Benefits sum', descending = True)
by_title.sort('Total Pay Benefits sum', descending = True).row(0)
by_title.select(('Job Title', 'Total Pay Benefits sum')).sort('Total Pay Benefits sum', descending=True)
Explanation: How about that, a few job categories have most of the employes and most of the spend, but it is far from uniform.  Let's look a little deeper.  Which categories consume most of the budget?
End of explanation
ordered_by_title['Job Title']
categories = {
    'Police': ["POLICE"], 
    'Fire': ["FIRE"], 
    'Animal Control':["ANIMAL"], 
    'Health': ["HEALTH", "PSYCH", "HLTH"],
    'Library': ['LIBRARY','LIBRARIAN'],
    'Offical' : ['MAYOR','COUNCIL', 'COMMISSIONER', 'CITY MANAGER'],
    'Trades' :["ELECTRICIAN","MECHANIC", "ENGINEER"],
    'Parking' : ["PARKING"],
    'Recreation' : ["RECREATION", "AQUATICS"],
    'Gardener' : ["GARDEN"],
    "Labor" : ["LABOR", "JANITOR"],
    'Community': ["COMMUNITY"],
    'Admin' : ["ADMIN"],
    'Traffic' : ["TRAFFIC"],
    'Accounting' : ["ACCOUNT"],
    'Dispatch' : ["DISPATCH"],
    'Waste' : ["WASTE", "SEWER"],
    'Analyst' : ["ANALYS"],
    'Office' : ["OFFICE "],
    'Legal' : ['LEGISLAT', 'ATTORN', 'ATTY'],
    'IT' : ["PROG", "INFORMATION SYSTEMS"],
    'School' : ["SCHOOL"],
    'Architect' : ["ARCHITECT"],
    'Planner' : ["PLANNER", "PERMIT"]
    }
categories
Explanation: As is often the case in the real world, the categorization used for operations is not directly useful for analysis.  We often need to build categories in order to get a handle on what's going on.
What do all those job titles look like
End of explanation
def categorize (title) : 
    for category, keywords in categories.items():
        for word in keywords :
            if title.find(word) >= 0 : return category
    return 'Other'
berkeley_sal_2011['Category'] = berkeley_sal_2011.apply(categorize, 'Job Title')
berkeley_sal_2011
# lose the individual names
job_categories = berkeley_sal_2011.drop("Employee Name") 
job_categories["Cat"] = 1
by_categories = job_categories.group("Category", sum)
by_categories.sort("Total Pay Benefits sum", descending=True).show()
Explanation: Applying a function to create a new column
table.apply: applies a function to every element in a column.  
One of the best examples of high-order functions and tables is in categorizing data. As is often the case, we create a new column with the results
End of explanation
job_categories.where('Category', 'Other')
job_categories.where('Category', 'Other').group('Job Title',sum).sort('Cat sum', descending=True)
job_categories.where('Category', 'Other').group('Job Title',sum).sort('Total Pay Benefits sum', descending=True)
Explanation: As is often the case working with real data, we often need to iterate a bit to get what we want out of it.  With all those titles, a lot of stuff is likely to end up as other.  
Here we have a little iterative process to get enough of the job titles categorized
End of explanation
by_categories.sort('Total Pay Benefits sum', descending=True).barh('Category', select=['Total Pay Benefits sum', 'Cat sum'], overlay=False)
Explanation: So no job title left has more than 10 employees in it, but some have quite a bit of cost.  We could go back and add more entries to our category table and iterate a bit.  The important thing is that we create new tables, we don't clobber old ones.  
Well this shows the challenge in managing budget pretty nicely.  Most of the money is spent in a few job categories.  But then there are still over 200 employees in a zillion other categories that are stile the #2 spend.
End of explanation
police = job_categories.where('Category', 'Police')
police
Explanation: So let's try to understand the police category a bit more.
End of explanation
police.select(paylabels).hist(bins=30,normed=False)
police.select(paylabels).hist(bins=30,normed=False, overlay=False)
Explanation: How do the pay labels spread across the force?
We can look at histograms by pay label.  First all toegether and then broken apart.
End of explanation
police.group('Job Title')
# We can actually get all the data by title
police.select(['Job Title','Base Pay', 'Overtime Pay']).group('Job Title', collect=lambda x:x)
Explanation: Base pay seems to chunk into categories, perhaps by job title.  
Most members of the force do little overtime, but a few do a lot!
How many are in each Job Title?
End of explanation
police.pivot_bin('Job Title', 'Base Pay', bins=np.arange(0,200000,10000)).show()
police.pivot_bin('Job Title', 'Base Pay', bins=np.arange(0,200000,10000)).bar('bin')
police.pivot_bin('Job Title', 'Overtime Pay', bins=np.arange(0,200000,10000)).bar('bin')
police.pivot_bin('Job Title', 'Total Pay Benefits', bins=np.arange(0,420000,10000)).bar('bin')
fire = job_categories.where('Category','Fire')
fire.select(paylabels).hist(bins=30)
fire.group('Job Title')
fire.pivot_bin('Job Title', 'Total Pay Benefits', bins=np.arange(0,420000,10000)).bar('bin')
Explanation: We can't just pivot by Job Title because we don't have a uniform number of rows, but what we can do is for pivot and bin (or histogram) so we can see the distribution of a column by job title.
Sure enough.  Officers cluster around 100-120k, sergeants at 130-140k, but there's a little overlap.
End of explanation
raw_berkeley_sal_2013 = Table.read_table("./data/City_of_Berkeley_Employee_Salaries_-_2013.csv")
raw_berkeley_sal_2013
Explanation: Let's compare the 2011 data with more recent 2013 data.
End of explanation
berkeley_sal_2013 = raw_berkeley_sal_2013.drop(['Year','Notes','Agency'])
berkeley_sal_2013
berkeley_sal_2013.set_format(berkeley_sal_2013.labels[2:], CurrencyFormatter)
berkeley_sal_2013["Total Pay & Benefits"]
berkeley_sal_2013.sort('Total Pay & Benefits',descending=True)
Explanation: Well, the data base changed.  It picked up a few columns over the years. And we need to convert the salary strings to numbers so we can do analysis on them.  All in one go...
End of explanation
b2011 = berkeley_sal_2011.select(["Employee Name", "Job Title", "Total Pay Benefits"])
b2011.relabel('Total Pay Benefits', "Total 2011")
b2011.sort('Total 2011', descending=True)
b2013 = berkeley_sal_2013.select(["Employee Name", "Job Title", "Total Pay & Benefits"])
b2013.relabel('Job Title','Title 2013')
b2013.relabel("Total Pay & Benefits", "Total 2013")
b2013.sort('Total 2013', descending=True)
Explanation: Isn't that interesting.  They seem to have gotten their overtime under control.  Was that management, end of the occupy movement, something else?  Let's do a bit of comparison.
First we need to do some clean up and get labels we can deal with.
End of explanation
b2013['Employee Name'] = b2013.apply(str.upper, 'Employee Name')
b2013
Explanation: Snap! They decided that case was a good idea for proper nouns.  Let's go back to the old way.
End of explanation
b11_13 = b2011.join('Employee Name', b2013)
b11_13
Explanation: Now we can put the two tables together to see what happened with employees who were around in both years.  Here we get to use another powerful operations on tables.
Table.join: joins two tables together using a column of each that contains common values.
Here we have the employee names in each table.  The join will give us the title and salary in both years for those employees in both tables, i.e., working for the city in both years
End of explanation
b11_13["Increase"] =b11_13['Total 2013'] - b11_13['Total 2011']
b11_13.sort('Increase', "decr").select('Increase').plot()
Explanation: Let's add a column with increase in total pay.
End of explanation
b11_13.stats(summary_ops)
Explanation: On the tails we have people who joined part way through 2011 or left part way through 2013.
End of explanation
sum2011 = np.sum(berkeley_sal_2011['Total Pay Benefits'])
"${:,}".format(int(sum2011))
sum2013 = np.sum(berkeley_sal_2013['Total Pay & Benefits'])
"${:,}".format(int(sum2013))
"${:,}".format(int(sum2013-sum2011))
"{:.1%}".format((sum2013-sum2011)/sum2011)
Explanation: Well that's interesting.  Total compensation seems to have dropped.  Did the budget actually go down?
End of explanation
np.sum(berkeley_sal_2011['Overtime Pay'])
np.sum(berkeley_sal_2013['Overtime Pay'])
Explanation: Look at that.
End of explanation
b11_13.where(b11_13['Job Title'] != b11_13['Title 2013']).sort('Total 2013', descending=True).show()
b11_13.where(b11_13['Job Title'] == b11_13['Title 2013']).sort('Increase', descending=True).show()
Explanation: Let's see who got promoted or demoted
End of explanation
b11_13.scatter('Total 2011', 'Total 2013')
Explanation: Perhaps we might want to look at the relationship of these two variables.  That leads to another useful operator
Table.scatter: does a scatter plot of columns against one columns
End of explanation
<END_TASK>