agate 1.6.0¶

From data in memory¶

From a list of lists.

column_names = ['letter', 'number']
column_types = [agate.Text(), agate.Number()]

rows = [
    ('a', 1),
    ('b', 2),
    ('c', None)
]

table = agate.Table(rows, column_names, column_types)

From a list of dictionaries.

rows = [
    dict(letter='a', number=1),
    dict(letter='b', number=2),
    dict(letter='c', number=None)
]

table = agate.Table.from_object(rows)

From a CSV¶

By default, loading a table from a CSV will use agate’s builtin TypeTester to infer column types:

table = agate.Table.from_csv('filename.csv')

Override type inference¶

In some cases agate’s TypeTester may guess incorrectly. To override the type for some columns and use TypeTester for the rest, pass a dictionary to the column_types argument.

specified_types = {
    'column_name_one': agate.Text(),
    'column_name_two': agate.Number()
}

table = agate.Table.from_csv('filename.csv', column_types=specified_types)

This will use a generic TypeTester and override your specified columns with TypeTester.force.

Limit type inference¶

For large datasets TypeTester may be unreasonably slow. In order to limit the amount of data it uses you can specify the limit argument. Note that if data after the limit invalidates the TypeTester’s inference you may get errors when the data is loaded.

tester = agate.TypeTester(limit=100)

table = agate.Table.from_csv('filename.csv', column_types=tester)

Manually specify columns¶

If you know the types of your data you may find it more efficient to manually specify the names and types of your columns. This also gives you an opportunity to rename columns when you load them.

text_type = agate.Text()
number_type = agate.Number()

column_names = ['city', 'area', 'population']
column_types = [text_type, number_type, number_type]

table = agate.Table.from_csv('population.csv', column_names, column_types)

Or, you can use this method to load data from a file that does not have a header row:

table = agate.Table.from_csv('population.csv', column_names, column_types, header=False)

From a unicode CSV¶

You don’t have to do anything special. It just works!

From a latin1 CSV¶

table = agate.Table.from_csv('census.csv', encoding='latin1')

From a semicolon delimited CSV¶

Normally, agate will automatically guess the delimiter of your CSV, but if that guess fails you can specify it manually:

table = agate.Table.from_csv('filename.csv', delimiter=';')

From a TSV (tab-delimited CSV)¶

This is the same as the previous example, but in this case we specify that the delimiter is a tab:

table = agate.Table.from_csv('filename.csv', delimiter='\t')

From JSON¶

table = agate.Table.from_json('filename.json')

From newline-delimited JSON¶

table = agate.Table.from_json('filename.json', newline=True)

From a SQL database¶

Use the agate-sql extension.

import agatesql

agatesql.patch()

table = agate.Table.from_sql('postgresql:///database', 'input_table')

From an Excel spreadsheet¶

Use the agate-excel extension. It supports both .xls and .xlsx files.

import agateexcel

agateexcel.patch()

table = agate.Table.from_xls('test.xls', sheet='data')

table2 = agate.Table.from_xlsx('test.xlsx', sheet='data')

From a DBF table¶

DBF is the file format used to hold tabular data for ArcGIS shapefiles. agate-dbf extension.

import agatedbf

agatedbf.patch()

table = agate.Table.from_dbf('test.dbf')

From a remote file¶

Use the agate-remote extension.

import agateremote

agateremote.patch()

table = agate.Table.from_url('https://raw.githubusercontent.com/onyxfish/agate/master/examples/test.csv')

agate-remote also let’s you create an Archive, which is a reference to a group of tables with a known path structure.

archive = agateremote.Archive('https://github.com/vincentarelbundock/Rdatasets/raw/master/csv/')

table = archive.get_table('sandwich/PublicSchools.csv')

To a CSV¶

table.to_csv('filename.csv')

To JSON¶

table.to_json('filename.json')

To newline-delimited JSON¶

table.to_json('filename.json', newline=True)

To a SQL database¶

Use the agate-sql extension.

import agatesql

table.to_sql('postgresql:///database', 'output_table')

Include specific columns¶

Create a new table with only a specific set of columns:

include_columns = ['column_name_one', 'column_name_two']

new_table = table.select(include_columns)

Exclude specific columns¶

Create a new table without a specific set of columns:

exclude_columns = ['column_name_one', 'column_name_two']

new_table = table.exclude(exclude_columns)

By regex¶

You can use Python’s builtin re module to introduce a regular expression into a Table.where() query.

For example, here we find all states that start with “C”.

import re

new_table = table.where(lambda row: re.match('^C', row['state']))

This can also be useful for finding values that don’t match your expectations. For example, finding all values in the “phone number” column that don’t look like phone numbers:

new_table = table.where(lambda row: not re.match('\d{3}-\d{3}-\d{4}', row['phone']))

By glob¶

Hate regexes? You can use glob (fnmatch) syntax too!

from fnmatch import fnmatch

new_table = table.where(lambda row: fnmatch('C*', row['state']))

Values within a range¶

This snippet filters the dataset to incomes between 100,000 and 200,000.

new_table = table.where(lambda row: 100000 < row['income'] < 200000)

Dates within a range¶

This snippet filters the dataset to events during the summer of 2015:

import datetime

new_table = table.where(lambda row: datetime.datetime(2015, 6, 1) <= row['date'] <= datetime.datetime(2015, 8, 31))

If you want to filter to events during the summer of any year:

new_table = table.where(lambda row: 6 <= row['date'].month <= 8)

Top N percent¶

To filter a dataset to the top 10% percent of values we first compute the percentiles for the column and then use the result in the Table.where() truth test:

percentiles = table.aggregate(agate.Percentiles('salary'))
top_ten_percent = table.where(lambda r: r['salary'] >= percentiles[90])

Random sample¶

By combining a random sort with limiting, we can effectively get a random sample from a table.

import random

randomized = table.order_by(lambda row: random.random())
sampled = table.limit(10)

Ordered sample¶

With can also get an ordered sample by simply using the step parameter of the Table.limit() method to get every Nth row.

sampled = table.limit(step=10)

Distinct values¶

You can retrieve a distinct list of values in a column using Column.values_distinct() or Table.distinct().

Table.distinct() returns the entire row so it’s necessary to chain a select on the specific column.

columns = ('value',)
rows = ([1],[2],[2],[5])
new_table = agate.Table(rows, columns)

new_table.columns['value'].values_distinct()
# or
new_table.distinct('value').columns['value'].values()
(Decimal('1'), Decimal('2'), Decimal('5'))

Alphabetical¶

Order a table by the last_name column:

new_table = table.order_by('last_name')

Numerical¶

Order a table by the cost column:

new_table = table.order_by('cost')

By date¶

Order a table by the birth_date column:

new_table = table.order_by('birth_date')

Reverse order¶

The order of any sort can be reversed by using the reverse keyword:

new_table = table.order_by('birth_date', reverse=True)

Multiple columns¶

Because Python’s internal sorting works natively with sequences, we can implement multi-column sort by returning a tuple from the key function.

new_table = table.order_by(lambda row: (row['last_name'], row['first_name']))

This table will now be ordered by last_name, then first_name.

Random order¶

import random

new_table = table.order_by(lambda row: random.random())

Exact search¶

Find all individuals with the last_name “Groskopf”:

family = table.where(lambda r: r['last_name'] == 'Groskopf')

Fuzzy search by edit distance¶

By leveraging an existing Python library for computing the Levenshtein edit distance it is trivially easy to implement a fuzzy string search.

For example, to find all names within 2 edits of “Groskopf”:

from Levenshtein import distance

fuzzy_family = table.where(lambda r: distance(r['last_name'], 'Groskopf') <= 2)

These results will now include all those “Grosskopfs” and “Groskoffs” whose mail I am always getting.

Fuzzy search by phonetic similarity¶

By using Fuzzy to calculate phonetic similarity, it is possible to implement a fuzzy phonetic search.

For example to find all rows with first_name phonetically similar to “Catherine”:

import fuzzy

dmetaphone = fuzzy.DMetaphone(4)
phonetic_search = dmetaphone('Catherine')

def phonetic_match(r):
    return any(x in dmetaphone(r['first_name']) for x in phonetic_search)

phonetic_family = table.where(lambda r: phonetic_match(r))

Standardize row and columns names¶

The Table.rename() method has arguments to convert row or column names to slugs and append unique identifiers to duplicate values.

Using an existing table object:

# Convert column names to unique slugs
table.rename(slug_columns=True)

# Convert row names to unique slugs
table.rename(slug_rows=True)

# Convert both column and row names to unique slugs
table.rename(slug_columns=True, slug_rows=True)

Standardize column values¶

agate has a Slug computation that can be used to also standardize text column values. The computation has an option to also append unique identifiers to duplicate values.

Using an existing table object:

# Convert the values in column 'title' to slugs
new_table = table.compute([
    ('title-slug', agate.Slug('title'))
])

# Convert the values in column 'title' to unique slugs
new_table = table.compute([
    ('title-slug', agate.Slug('title', ensure_unique=True))
])

Descriptive statistics¶

agate includes a full set of standard descriptive statistics that can be applied to any column containing Number data.

table.aggregate(agate.Sum('salary'))
table.aggregate(agate.Min('salary'))
table.aggregate(agate.Max('salary'))
table.aggregate(agate.Mean('salary'))
table.aggregate(agate.Median('salary'))
table.aggregate(agate.Mode('salary'))
table.aggregate(agate.Variance('salary'))
table.aggregate(agate.StDev('salary'))
table.aggregate(agate.MAD('salary'))

Or, get several at once:

table.aggregate([
    agate.Min('salary'),
    agate.Mean('salary'),
    agate.Max('salary')
])

Aggregate statistics¶

You can also generate aggregate statistics for subsets of data (sometimes referred to as “rolling up”):

doctors = patients.group_by('doctor')
patient_ages = doctors.aggregate([
    ('patient_count', agate.Count()),
    ('age_mean', agate.Mean('age')),
    ('age_median', agate.Median('age'))
])

The resulting table will have four columns: doctor, patient_count, age_mean and age_median.

You can roll up by multiple columns by chaining agate’s Table.group_by() method.

doctors_by_state = patients.group_by("state").group_by('doctor')

Distribution by count (frequency)¶

Counting the number of each unique value in a column can be accomplished with the Table.pivot() method:

# Counts of a single column's values
table.pivot('doctor')

# Counts of all combinations of more than one column's values
table.pivot(['doctor', 'hospital'])

The resulting tables will have a column for each key column and another Count column counting the number of instances of each value.

Distribution by percent¶

Table.pivot() can also be used to calculate the distribution of values as a percentage of the total number:

# Percents of a single column's values
table.pivot('doctor', computation=agate.Percent('Count'))

# Percents of all combinations of more than one column's values
table.pivot(['doctor', 'hospital'], computation=agate.Percent('Count'))

The output table will be the same format as the previous example, except the value column will be named Percent.

Identify outliers¶

The agate-stats extension adds methods for finding outliers.

import agatestats

agatestats.patch()

outliers = table.stdev_outliers('salary', deviations=3, reject=False)

By specifying reject=True you can instead return a table including only those values not identified as outliers.

not_outliers = table.stdev_outliers('salary', deviations=3, reject=True)

The second, more robust, method for identifying outliers is by identifying values which are more than some number of “median absolute deviations” from the median (typically 3).

outliers = table.mad_outliers('salary', deviations=3, reject=False)

As with the first example, you can specify reject=True to exclude outliers in the resulting table.

Custom statistics¶

You can also generate custom aggregated statistics for your data by defining your own ‘summary’ aggregation. This might be especially useful for performing calculations unique to your data. Here’s a simple example:

# Create a custom summary aggregation with agate.Summary
# Input a column name, a return data type and a function to apply on the column
count_millionaires = agate.Summary('salary', agate.Number(), lambda r: sum(salary > 1000000 for salary in r.values()))

table.aggregate([
    count_millionaires
])

Your custom aggregation can be used to determine both descriptive and aggregate statistics shown above.

Change¶

new_table = table.compute([
    ('2000_change', agate.Change('2000', '2001')),
    ('2001_change', agate.Change('2001', '2002')),
    ('2002_change', agate.Change('2002', '2003'))
])

Or, better yet, compute the whole decade using a loop:

computations = []

for year in range(2000, 2010):
    change = agate.Change(year, year + 1)
    computations.append(('%i_change' % year, change))

new_table = table.compute(computations)

Percent¶

Calculate the percentage for each value in a column with Percent. Values are divided into the sum of the column by default.

columns = ('value',)
rows = ([1],[2],[2],[5])
new_table = agate.Table(rows, columns)

new_table = new_table.compute([
    ('percent', agate.Percent('value'))
])

new_table.print_table()

| value | percent |
| ----- | ------- |
|     1 |      10 |
|     2 |      20 |
|     2 |      20 |
|     5 |      50 |

Override the denominator with a keyword argument.

new_table = new_table.compute([
    ('percent', agate.Percent('value', 5))
])

new_table.print_table()

| value | percent |
| ----- | ------- |
|     1 |      20 |
|     2 |      40 |
|     2 |      40 |
|     5 |     100 |

Percent change¶

Want percent change instead of value change? Just swap out the Computation:

computations = []

for year in range(2000, 2010):
    change = agate.PercentChange(year, year + 1)
    computations.append(('%i_change' % year, change))

new_table = table.compute(computations)

Indexed/cumulative change¶

Need your change indexed to a starting year? Just fix the first argument:

computations = []

for year in range(2000, 2010):
    change = agate.Change(2000, year + 1)
    computations.append(('%i_change' % year, change))

new_table = table.compute(computations)

Of course you can also use PercentChange if you need percents rather than values.

Round to two decimal places¶

agate stores numerical values using Python’s decimal.Decimal type. This data type ensures numerical precision beyond what is supported by the native float() type, however, because of this we can not use Python’s builtin round() function. Instead we must use decimal.Decimal.quantize().

We can use Table.compute() to apply the quantize to generate a rounded column from an existing one:

from decimal import Decimal

number_type = agate.Number()

def round_price(row):
    return row['price'].quantize(Decimal('0.01'))

new_table = table.compute([
    ('price_rounded', agate.Formula(number_type, round_price))
])

To round to one decimal place you would simply change 0.01 to 0.1.

Difference between dates¶

Calculating the difference between dates (or dates and times) works exactly the same as it does for numbers:

new_table = table.compute([
    ('age_at_death', agate.Change('born', 'died'))
])

Levenshtein edit distance¶

The Levenshtein edit distance is a common measure of string similarity. It can be used, for instance, to check for typos between manually-entered names and a version that is known to be spelled correctly.

Implementing Levenshtein requires writing a custom Computation. To save ourselves building the whole thing from scratch, we will lean on the python-Levenshtein library for the actual algorithm.

import agate
from Levenshtein import distance
import six

class LevenshteinDistance(agate.Computation):
    """
    Computes Levenshtein edit distance between the column and a given string.
    """
    def __init__(self, column_name, compare_string):
        self._column_name = column_name
        self._compare_string = compare_string

    def get_computed_data_type(self, table):
        """
        The return value is a numerical distance.
        """
        return agate.Number()

    def validate(self, table):
        """
        Verify the column is text.
        """
        column = table.columns[self._column_name]

        if not isinstance(column.data_type, agate.Text):
            raise agate.DataTypeError('Can only be applied to Text data.')

    def run(self, table):
        """
        Find the distance, returning null when the input column was null.
        """
        new_column = []

        for row in table.rows:
          val = row[self._column_name]

          if val is None:
              new_column.append(None)
          else:
              new_column.append(distance(val, self._compare_string))

        return new_column

This code can now be applied to any Table just as any other Computation would be:

new_table = table.compute([
    ('distance', LevenshteinDistance('column_name', 'string to compare'))
])

The resulting column will contain an integer measuring the edit distance between the value in the column and the comparison string.

USA Today Diversity Index¶

The USA Today Diversity Index is a widely cited method for evaluating the racial diversity of a given area. Using a custom Computation makes it simple to calculate.

Assuming that your data has a column for the total population, another for the population of each race and a final column for the hispanic population, you can implement the diversity index like this:

class USATodayDiversityIndex(agate.Computation):
    def get_computed_data_type(self, table):
        return agate.Number()

    def run(self, table):
        new_column = []

        for row in table.rows:
          race_squares = 0

          for race in ['white', 'black', 'asian', 'american_indian', 'pacific_islander']:
              race_squares += (row[race] / row['population']) ** 2

          hispanic_squares = (row['hispanic'] / row['population']) ** 2
          hispanic_squares += (1 - (row['hispanic'] / row['population'])) ** 2

          new_column.append((1 - (race_squares * hispanic_squares)) * 100)

        return new_column

We apply the diversity index like any other computation:

with_index = table.compute([
    ('diversity_index', USATodayDiversityIndex())
])

Simple Moving Average¶

A simple moving average is the average of some number of prior values in a series. It is typically used to smooth out variation in time series data.

The following custom Computation will compute a simple moving average. This example assumes your data is already sorted.

class SimpleMovingAverage(agate.Computation):
    """
    Computes the simple moving average of a column over some interval.
    """
    def __init__(self, column_name, interval):
        self._column_name = column_name
        self._interval = interval

    def get_computed_data_type(self, table):
        """
        The return value is a numerical average.
        """
        return agate.Number()

    def validate(self, table):
        """
        Verify the column is numerical.
        """
        column = table.columns[self._column_name]

        if not isinstance(column.data_type, agate.Number):
            raise agate.DataTypeError('Can only be applied to Number data.')

    def run(self, table):
        new_column = []

        for i, row in enumerate(table.rows):
            if i < self._interval:
                new_column.append(None)
            else:
                values = tuple(r[self._column_name] for r in table.rows[i - self._interval:i])

                if None in values:
                    new_column.append(None)
                else:
                    new_column.append(sum(values) / self._interval)

        return new_column

You would use the simple moving average like so:

with_average = table.compute([
    ('six_month_moving_average', SimpleMovingAverage('price', 6))
])

Specify a date format¶

By default agate will attempt to guess the format of a Date or DateTime column. In some cases, it may not be possible to automatically figure out the format of a date. In this case you can specify a datetime.datetime.strptime() formatting string to specify how the dates should be parsed. For example, if your dates were formatted as “15-03-15” (March 15th, 2015) then you could specify:

date_type = agate.Date('%d-%m-%y')

Another use for this feature is if you have a column that contains extraneous data. For instance, imagine that your column contains hours and minutes, but they are always zero. It would make more sense to load that data as type Date and ignore the extra time information:

date_type = agate.Date('%m/%d/%Y 00:00')

Specify a timezone¶

Timezones are hard. Under normal circumstances (no arguments specified), agate will not try to parse timezone information, nor will it apply a timezone to the datetime.datetime instances it creates. (They will be naive in Python parlance.) There are two ways to force timezone data into your agate columns.

The first is to use a format string, as shown above, and specify a pattern for timezone information:

datetime_type = agate.DateTime('%Y-%m-%d %H:%M:%S%z')

The second way is to specify a timezone as an argument to the type constructor:

import pytz

eastern = pytz.timezone('US/Eastern')
datetime_type = agate.DateTime(timezone=eastern)

In this case all timezones that are processed will be set to have the Eastern timezone. Note, the timezone will be set, not converted. You cannot use this method to convert your timezones from UTC to another timezone. To do that see Convert timezones.

Calculate a time difference¶

See Difference between dates.

Sort by date¶

See By date.

Convert timezones¶

If you load data from a spreadsheet in one timezone and you need to convert it to another, you can do this using a Formula. Your datetime column must have timezone data for the following example to work. See Specify a timezone.

import pytz

us_eastern = pytz.timezone('US/Eastern')
datetime_type = agate.DateTime(timezone=us_eastern)

column_names = ['what', 'when']
column_types = [text_type, datetime_type]

table = agate.Table.from_csv('events.csv', columns)

rome = timezone('Europe/Rome')
timezone_shifter = agate.Formula(lambda r: r['when'].astimezone(rome))

table = agate.Table.compute([
    ('when_in_rome', timezone_shifter)
])

SELECT¶

SQL:

SELECT state, total FROM table;

agate:

new_table = table.select(['state', 'total'])

WHERE¶

SQL:

SELECT * FROM table WHERE LOWER(state) = 'california';

agate:

new_table = table.where(lambda row: row['state'].lower() == 'california')

ORDER BY¶

SQL:

SELECT * FROM table ORDER BY total DESC;

agate:

new_table = table.order_by(lambda row: row['total'], reverse=True)

DISTINCT¶

SQL:

SELECT DISTINCT ON (state) * FROM table;

agate:

new_table = table.distinct('state')

INNER JOIN¶

SQL (two ways):

SELECT * FROM patient, doctor WHERE patient.doctor = doctor.id;

SELECT * FROM patient INNER JOIN doctor ON (patient.doctor = doctor.id);

agate:

joined = patients.join(doctors, 'doctor', 'id', inner=True)

LEFT OUTER JOIN¶

SQL:

SELECT * FROM patient LEFT OUTER JOIN doctor ON (patient.doctor = doctor.id);

agate:

joined = patients.join(doctors, 'doctor', 'id')

FULL OUTER JOIN¶

SQL:

SELECT * FROM patient FULL OUTER JOIN doctor ON (patient.doctor = doctor.id);

agate:

joined = patients.join(doctors, 'doctor', 'id', full_outer=True)

GROUP BY¶

agate’s Table.group_by() works slightly different than SQLs. It does not require an aggregate function. Instead it returns TableSet. To see how to perform the equivalent of a SQL aggregate, see below.

doctors = patients.group_by('doctor')

You can group by two or more columns by chaining the command.

doctors_by_state = patients.group_by('state').group_by('doctor')

HAVING¶

agate’s TableSet.having() works very similar to SQL’s keyword of the same name.

doctors = patients.group_by('doctor')
popular_doctors = doctors.having([
    ('patient_count', Count())
], lambda t: t['patient_count'] > 100)

This filters to only those doctors whose table includes at least 100 results. Can add as many aggregations as you want to the list and each will be available, by name in the test function you pass.

For example, here we filter to popular doctors with more an average review of at least three stars:

doctors = patients.group_by('doctor')
popular_doctors = doctors.having([
    ('patient_count', Count()),
    ('average_stars', Average('stars'))
], lambda t: t['patient_count'] > 100 and t['average_stars'] >= 3)

Chain commands together¶

SQL:

SELECT state, total FROM table WHERE LOWER(state) = 'california' ORDER BY total DESC;

agate:

new_table = table \
    .select(['state', 'total']) \
    .where(lambda row: row['state'].lower() == 'california') \
    .order_by('total', reverse=True)

Aggregate functions¶

SQL:

SELECT mean(age), median(age) FROM patients GROUP BY doctor;

agate:

doctors = patients.group_by('doctor')
patient_ages = doctors.aggregate([
    ('patient_count', agate.Count()),
    ('age_mean', agate.Mean('age')),
    ('age_median', agate.Median('age'))
])

The resulting table will have four columns: doctor, patient_count, age_mean and age_median.

Simple formulas¶

If you need to simulate a simple Excel formula you can use the Formula class to apply an arbitrary function.

Excel:

=($A1 + $B1) / $C1

agate:

def f(row):
    return (row['a'] + row['b']) / row['c']

new_table = table.compute([
    ('new_column', agate.Formula(agate.Number(), f))
])

If this still isn’t enough flexibility, you can also create your own subclass of Computation.

SUM¶

number_type = agate.Number()

def five_year_total(row):
    columns = ('2009', '2010', '2011', '2012', '2013')

    return sum(tuple(row[c] for c in columns)]

formula = agate.Formula(number_type, five_year_total)

new_table = table.compute([
    ('five_year_total', formula)
])

TRIM¶

new_table = table.compute([
    ('name_stripped', agate.Formula(text_type, lambda r: r['name'].strip()))
])

CONCATENATE¶

new_table = table.compute([
    ('full_name', agate.Formula(text_type, lambda r: '%(first_name)s %(middle_name)s %(last_name)s' % r))
])

IF¶

new_table = table.compute([
    ('mvp_candidate', agate.Formula(boolean_type, lambda r: row['batting_average'] > 0.3))
])

VLOOKUP¶

There are two ways to get the equivalent of Excel’s VLOOKUP with agate. If your lookup source is another agate Table, then you’ll want to use the Table.join() method:

new_table = mvp_table.join(states, 'state_abbr')

This will add all the columns from the states table to the mvp_table, where their state_abbr columns match.

If your lookup source is a Python dictionary or some other object you can implement the lookup using a Formula computation:

states = {
    'AL': 'Alabama',
    'AK': 'Alaska',
    'AZ': 'Arizona',
    ...
}

new_table = table.compute([
    ('mvp_candidate', agate.Formula(text_type, lambda r: states[row['state_abbr']]))
])

Pivot tables as cross-tabulations¶

Pivot tables in Excel implement a tremendous range of functionality. Agate divides this functionality into a few different methods.

If what you want is to convert rows to columns to create a “crosstab”, then you’ll want to use the Table.pivot() method:

jobs_by_state_and_year = employees.pivot('state', 'year')

This will generate a table with a row for each value in the state column and a column for each value in the year column. The intersecting cells will contains the counts grouped by state and year. You can pass the aggregation keyword to aggregate some other value, such as Mean or Median.

Pivot tables as summaries¶

On the other hand, if what you want is to summarize your table with descriptive statistics, then you’ll want to use Table.group_by() and TableSet.aggregate():

jobs = employees.group_by('job_title')
summary = jobs.aggregate([
    ('employee_count', agate.Count()),
    ('salary_mean', agate.Mean('salary')),
    ('salary_median', agate.Median('salary'))
])

The resulting summary table will have four columns: job_title, employee_count, salary_mean and salary_median.

You may also want to look at the Table.normalize() and Table.denormalize() methods for examples of functionality frequently accomplished with Excel’s pivot tables.

c()¶

Agate’s Table.select() and Table.exclude() are the equivalent of R’s c for selecting columns.

R:

selected <- data[c("last_name", "first_name", "age")]
excluded <- data[!c("last_name", "first_name", "age")]

agate:

selected = table.select(['last_name', 'first_name', 'age'])
excluded = table.exclude(['last_name', 'first_name', 'age'])

subset¶

Agate’s Table.where() is the equivalent of R’s subset.

R:

newdata <- subset(data, age >= 20 | age < 10)

agate:

new_table = table.where(lambda row: row['age'] >= 20 or row['age'] < 10)

order¶

Agate’s Table.order_by() is the equivalent of R’s order.

R:

newdata <- employees[order(last_name),]

agate:

new_table = employees.order_by('last_name')

merge¶

Agate’s Table.join() is the equivalent of R’s merge.

R:

joined <- merge(employees, states, by="usps")

agate:

joined = employees.join(states, 'usps')

rbind¶

Agate’s Table.merge() is the equivalent of R’s rbind.

R:

merged <- rbind(first_year, second_year)

agate:

merged = agate.Table.merge(first_year, second_year)

aggregate¶

Agate’s Table.group_by() and TableSet.aggregate() can be used to recreate the functionality of R’s aggregate.

R:

aggregates = aggregate(employees$salary, list(job = employees$job), mean)

agate:

jobs = employees.group_by('job')
aggregates = jobs.aggregate([
    ('mean', agate.Mean('salary'))
])

melt¶

Agate’s Table.normalize() is the equivalent of R’s melt.

R:

melt(employees, id=c("last_name", "first_name"))

agate:

employees.normalize(['last_name', 'first_name'])

cast¶

Agate’s Table.denormalize() is the equivalent of R’s cast.

R:

melted = melt(employees, id=c("name"))
casted = cast(melted, name~variable, mean)

agate:

normalized = employees.normalize(['name'])
denormalized = normalized.denormalize('name')

filter¶

agate’s Table.where() functions exactly like Underscore’s filter.

new_table = table.where(lambda row: row['state'] == 'Texas')

reject¶

To simulate Underscore’s reject, simply negate the return value of the function you pass into agate’s Table.where().

new_table = table.where(lambda row: not (row['state'] == 'Texas'))

find¶

agate’s Table.find() works exactly like Underscore’s find.

row = table.find(lambda row: row['state'].startswith('T'))

any¶

The Any aggregation works like Underscore’s any.

true_or_false = table.aggregate(Any('salaries', lambda d: d > 100000))

You can also use Table.where() to filter to columns that pass the truth test.

all¶

The All aggregation works like Underscore’s all.

true_or_false = table.aggregate(All('salaries', lambda d: d > 100000))

Create rows for missing values¶

We can insert a default row for each value that is missing in a table from a given sequence of values.

Starting with a table like this, we can fill in rows for all missing years:

key = 'year'
expected_values = (1997, 1998, 1999, 2000, 2001, 2002, 2003)

# Your default row should specify column values not in `key`
default_row = (0, 0)

new_table = table.homogenize(key, expected_values, default_row)

The result will be:

Create dynamic rows based on missing values¶

We can also specify new row values with a value-generating function:

key = 'year'
expected_values = (1997, 1998, 1999, 2000, 2001, 2002, 2003)

# If default row is a function, it should return a full row
def default_row(missing_value):
  return (missing_value, missing_value-1997, missing_value-1997)

new_table = table.homogenize(key, expected_values, default_row)

The new table will be:

Rename columns¶

You can rename the columns in a table by using the Table.rename() method and specifying the new column names as an array or dictionary mapping old column names to new ones.

table = Table(rows, column_names = ['a', 'b', 'c'])
new_table = table.rename(column_names = ['one', 'two', 'three'])
# or
new_table = table.rename(column_names = {'a': 'one', 'b': 'two', 'c': 'three'})

Reorder columns¶

You can reorder the columns in a table by using the Table.select() method and specifying the column names in the order you want:

new_table = table.select(['3rd_column_name', '1st_column_name', '2nd_column_name'])

Pivot by a single column¶

The Table.pivot() method is a general process for grouping data by row and, optionally, by column, and then calculating some aggregation for each group. Consider the following table:

In the very simplest case, this table can be pivoted to count the number occurences of values in a column:

transformed = table.pivot('race')

Result:

Pivot by multiple columns¶

You can pivot by multiple columns either as additional row-groups, or as intersecting columns. For example, given the table in the previous example:

transformed = table.pivot(['race', 'gender'])

Result:

For the column, version you would do:

transformed = table.pivot('race', 'gender')

Result:

Pivot to sum¶

The default pivot aggregation is Count but you can also supply other operations. For example, to aggregate each group by Sum of their ages:

transformed = table.pivot('race', 'gender', aggregation=agate.Sum('age'))

Pivot to percent of total¶

Pivot allows you to apply a Computation to each row of aggregated results prior to returning the table. Use the stringified name of the aggregation as the column argument to your computation:

transformed = table.pivot('race', 'gender', aggregation=agate.Sum('age'), computation=agate.Percent('sum'))

Note: actual computed percentages will be much more precise.

It’s helpful when constructing these cases to think of all the cells in the pivot table as a single sequence.

Denormalize key/value columns into separate columns¶

It’s common for very large datasets to be distributed in a “normalized” format, such as:

The Table.denormalize() method can be used to transform the table so that each unique property has its own column.

transformed = table.denormalize('name', 'property', 'value')

Result:

Normalize separate columns into key/value columns¶

Sometimes you have a dataset where each property has its own column, but your analysis would be easier if all properties were stored together. Consider this table:

The Table.normalize() method can be used to transform the table so that all the properties and their values share two columns.

transformed = table.normalize('name', ['gender', 'race', 'age'])

Result:

Set your locale¶

Setting your locale will change how numbers are displayed when you print an agate Table or serialize it to, for example, a CSV file. This works the same as it does for any other Python module. See the locale documentation for details. Changing your locale will not affect how they are parsed from the files you are using. To change how data is parsed see Specify locale of numbers.

Specify locale of numbers¶

To correctly parse numbers from non-US locales, you must pass a locale parameter to the Number constructor. For example, to parse Dutch numbers (which use a period to separate thousands and a comma to separate fractions):

dutch_numbers = agate.Number(locale='de_DE')

column_names = ['city', 'population']
column_types = [text_type, dutch_numbers]

table = agate.Table.from_csv('dutch_cities.csv', columns)

Competition rank¶

The basic rank supported by agate is standard “competition ranking”. In this model the values [3, 4, 4, 5] would be ranked [1, 2, 2, 4]. You can apply competition ranking using the Rank computation:

new_table = table.compute([
    ('rank', agate.Rank('value'))
])

Rank descending¶

Descending competition ranking is specified using the reverse argument.

new_table = table.compute([
    ('rank', agate.Rank('value', reverse=True))
])

Rank change¶

You can compute the change from one rank to another by combining the Rank and Change computations:

new_table = table.compute([
    ('rank2014', agate.Rank('value2014')),
    ('rank2015', agate.Rank('value2015'))
])

new_table2 = new_table.compute([
    ('rank_change', agate.Change('rank2014', 'rank2015'))
])

Percentile rank¶

“Percentile rank” is a bit of a misnomer. Really, this is the percentile in which each value in a column is located. This column can be computed for your data using the PercentileRank computation:

new_table = table.compute([
    ('percentile_rank', agate.PercentileRank('value'))
])

Note that there is no entirely standard method for computing percentiles. The percentiles computed in this manner may not agree precisely with those generated by other software. See the Percentiles class documentation for implementation details.

Text-based bar chart¶

agate has a builtin text-based bar-chart generator:

table.limit(10).print_bars('State Name', 'TOTAL', width=80)

State Name         TOTAL
ALABAMA           19,582 ▓░░░░░░░░░░░░░
ALASKA             2,705 ▓░░
ARIZONA           46,743 ▓░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░
ARKANSAS           7,932 ▓░░░░░
CALIFORNIA        76,639 ▓░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░
COLORADO          21,485 ▓░░░░░░░░░░░░░░░
CONNECTICUT        4,350 ▓░░░
DELAWARE           1,904 ▓░
DIST. OF COLUMBIA  2,185 ▓░
FLORIDA           59,519 ▓░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░
                         +-------------+------------+------------+-------------+
                         0          20,000       40,000       60,000      80,000

Text-based histogram¶

Table.print_bars() can be combined with Table.pivot() or Table.bins() to produce fast histograms:

table.bins('TOTAL', start=0, end=100000).print_bars('TOTAL', width=80)

TOTAL              Count
[0 - 10,000)          30 ▓░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░
[10,000 - 20,000)     12 ▓░░░░░░░░░░░░░░░░░░░░░░
[20,000 - 30,000)      7 ▓░░░░░░░░░░░░░
[30,000 - 40,000)      1 ▓░░
[40,000 - 50,000)      2 ▓░░░░
[50,000 - 60,000)      1 ▓░░
[60,000 - 70,000)      1 ▓░░
[70,000 - 80,000)      1 ▓░░
[80,000 - 90,000)      0 ▓
[90,000 - 100,000]     0 ▓
                         +-------------+------------+------------+-------------+
                         0.0          7.5         15.0         22.5         30.0

SVG bar chart¶

table.limit(10).bar_chart('State Name', 'TOTAL', 'docs/images/bar_chart.svg')

SVG column chart¶

table.limit(10).column_chart('State Name', 'TOTAL', 'docs/images/column_chart.svg')

SVG line chart¶

by_year_exonerated = table.group_by('exonerated')
counts = by_year_exonerated.aggregate([
    ('count', agate.Count())
])

counts.order_by('exonerated').line_chart('exonerated', 'count', 'docs/images/line_chart.svg')

SVG dots chart¶

table.scatterplot('exonerated', 'age', 'docs/images/dots_chart.svg')

SVG lattice chart¶

top_crimes = table.group_by('crime').having([
    ('count', agate.Count())
], lambda t: t['count'] > 100)

by_year = top_crimes.group_by('exonerated')

counts = by_year.aggregate([
    ('count', agate.Count())
])

by_crime = counts.group_by('crime')

by_crime.order_by('exonerated').line_chart('exonerated', 'count', 'docs/images/lattice.svg')

Using matplotlib¶

If you need to make more complex charts, you can always use agate with matplotlib.

Here is an example of how you might generate a line chart:

import pylab

pylab.plot(table.columns['homeruns'], table.columns['wins'])
pylab.xlabel('Homeruns')
pylab.ylabel('Wins')
pylab.title('How homeruns correlate to wins')

pylab.show()

CPI deflation¶

The agate-lookup extension adds a lookup method to agate’s Table class.

Starting with a table that looks like this:

We can map the year column to its annual CPI index in one lookup call.

import agatelookup

agatelookup.patch()

join_year_cpi = table.lookup('year', 'cpi')

The return table will have now have a new column:

A simple computation tacked on to this lookup can then get the 2015 equivalent values of each cost:

cpi_2015 = Decimal(216.909)

def cpi_adjust_2015(row):
    return (row['cost'] * (cpi_2015 / row['cpi'])).quantize(Decimal('0.01'))

cost_2015 = join_year_cpi.compute([
    ('cost_2015', agate.Formula(agate.Number(), cpi_adjust_2015))
])

And the final table will look like this:

Properties¶

Creating¶

Saving¶

Basic processing¶

Calculating new data¶

Advanced processing¶

Previewing¶

Charting¶

Detailed list¶

class agate.Table(rows, column_names=None, column_types=None, row_names=None, _is_fork=False)¶

Bases: object

A dataset consisting of rows and columns. Columns refer to “vertical” slices of data that must all be of the same type. Rows refer to “horizontal” slices of data that may (and usually do) contain mixed types.

The sequence of Column instances are retrieved via the Table.columns property. They may be accessed by either numeric index or by unique column name.

The sequence of Row instances are retrieved via the Table.rows property. They may be accessed by either numeric index or, if specified, unique row names.

Parameters:

rows – The data as a sequence of any sequences: tuples, lists, etc. If any row has fewer values than the number of columns, it will be filled out with nulls. No row may have more values than the number of columns. column_names – A sequence of string names for each column or None, in which case column names will be automatically assigned using letter_name(). column_types – A sequence of instances of DataType or an instance of TypeTester or None in which case a generic TypeTester will be used. Alternatively, a dictionary with column names as keys and instances of DataType as values to specify some types. row_names – Specifies unique names for each row. This parameter is optional. If specified it may be 1) the name of a single column that contains a unique identifier for each row, 2) a key function that takes a Row and returns a unique identifier or 3) a sequence of unique identifiers of the same length as the sequence of rows. The uniqueness of resulting identifiers is not validated, so be certain the values you provide are truly unique. _is_fork – Used internally to skip certain validation steps when data is propagated from an existing table. When True, rows are assumed to be Row instances, rather than raw data.

aggregate(aggregations)¶

Apply one or more Aggregation instances to this table.

Parameters:	aggregations – A single Aggregation instance or a sequence of tuples in the format (name, aggregation), where each aggregation is an instance of Aggregation.
Returns:	If the input was a single Aggregation then a single result will be returned. If it was a sequence then an OrderedDict of results will be returned.

bar_chart(label=0, value=1, path=None, width=None, height=None)¶

Render a bar chart using leather.Chart.

Parameters:

label – The name or index of a column to plot as the labels of the chart. Defaults to the first column in the table. value – The name or index of a column to plot as the values of the chart. Defaults to the second column in the table. path – If specified, the resulting SVG will be saved to this location. If None and running in IPython, then the SVG will be rendered inline. Otherwise, the SVG data will be returned as a string. width – The width of the output SVG. height – The height of the output SVG.

bins(column_name, count=10, start=None, end=None)¶

Generates (approximately) evenly sized bins for the values in a column. Bins may not be perfectly even if the spread of the data does not divide evenly, but all values will always be included in some bin.

The resulting table will have two columns. The first will have the same name as the specified column, but will be type Text. The second will be named count and will be of type Number.

Parameters:	column_name – The name of the column to bin. Must be of type Number count – The number of bins to create. If not specified then each value will be counted as its own bin. start – The minimum value to start the bins at. If not specified the minimum value in the column will be used. end – The maximum value to end the bins at. If not specified the maximum value in the column will be used.
Returns:	A new Table.

column_chart(label=0, value=1, path=None, width=None, height=None)¶

Render a column chart using leather.Chart.

Parameters:

label – The name or index of a column to plot as the labels of the chart. Defaults to the first column in the table. value – The name or index of a column to plot as the values of the chart. Defaults to the second column in the table. path – If specified, the resulting SVG will be saved to this location. If None and running in IPython, then the SVG will be rendered inline. Otherwise, the SVG data will be returned as a string. width – The width of the output SVG. height – The height of the output SVG.

column_names¶

An tuple of strings.

column_types¶

An tuple DataType instances.

columns¶

A MappedSequence with column names for keys and Column instances for values.

compute(computations, replace=False)¶

Create a new table by applying one or more Computation instances to each row.

Parameters:	computations – A sequence of pairs of new column names and Computation instances. replace – If True then new column names can match existing names, and those columns will be replaced with the computed data.
Returns:	A new Table.

denormalize(key=None, property_column='property', value_column='value', default_value=<object object>, column_types=None)¶

Create a new table with row values converted into columns.

For example:

name	property	value
Jane	gender	female
Jane	race	black
Jane	age	24
...	...	...

Can be denormalized so that each unique value in field becomes a column with value used for its values.

name	gender	race	age
Jane	female	black	24
Jack	male	white	35
Joe	male	black	28

If one or more keys are specified then the resulting table will automatically have row_names set to those keys.

This is the opposite of Table.normalize().

Parameters:

key – A column name or a sequence of column names that should be maintained as they are in the normalized table. Typically these are the tables unique identifiers and any metadata about them. Or, None if there are no key columns. field_column – The column whose values should become column names in the new table. property_column – The column whose values should become the values of the property columns in the new table. default_value – Value to be used for missing values in the pivot table. If not specified Decimal(0) will be used for aggregations that return Number data and None will be used for all others. column_types – A sequence of column types with length equal to number of unique values in field_column or an instance of TypeTester. Defaults to a generic TypeTester.

Returns:

A new Table.

distinct(key=None)¶

Create a new table with only unique rows.

Parameters:	key – Either the name of a single column to use to identify unique rows, a sequence of such column names, a function that takes a row and returns a value to identify unique rows, or None, in which case the entire row will be checked for uniqueness.
Returns:	A new Table.

exclude(key)¶

Create a new table without the specified columns.

Parameters:	key – Either the name of a single column to exclude or a sequence of such names.
Returns:	A new Table.

find(test)¶

Find the first row that passes a test.

Parameters:	test (function) – A function that takes a Row and returns True if it matches.
Returns:	A single Row if found, or None.

classmethod from_csv(path, column_names=None, column_types=None, row_names=None, skip_lines=0, header=True, sniff_limit=0, encoding='utf-8', **kwargs)¶

Create a new table from a CSV.

This method uses agate’s builtin CSV reader, which supplies encoding support for both Python 2 and Python 3.

kwargs will be passed through to the CSV reader.

Parameters:

path – Filepath or file-like object from which to read CSV data. If a file-like object is specified, it must be seekable. If using Python 2, the file should be opened in binary mode (rb). column_names – See Table.__init__(). column_types – See Table.__init__(). row_names – See Table.__init__(). skip_lines – The number of lines to skip from the top of the file. Note that skip lines will not work with header – If True, the first row of the CSV is assumed to contain column names. If header and column_names are both specified then a row will be skipped, but column_names will be used. sniff_limit – Limit CSV dialect sniffing to the specified number of bytes. Set to None to sniff the entire file. Defaults to 0 (no sniffing). encoding – Character encoding of the CSV file. Note: if passing in a file handle it is assumed you have already opened it with the correct encoding specified.

classmethod from_fixed(path, schema_path, column_names=<object object>, column_types=None, row_names=None, encoding='utf-8', schema_encoding='utf-8')¶

Create a new table from a fixed-width file and a CSV schema.

Schemas must be in the “ffs” format. There is a repository of such schemas maintained at wireservice/ffs.

Parameters:

path – File path or file-like object from which to read fixed-width data. schema_path – File path or file-like object from which to read schema (CSV) data. column_names – By default, these will be parsed from the schema. For alternatives, see Table.__init__(). column_types – See Table.__init__(). row_names – See Table.__init__(). encoding – Character encoding of the fixed-width file. Note: if passing in a file handle it is assumed you have already opened it with the correct encoding specified. schema_encoding – Character encoding of the schema file. Note: if passing in a file handle it is assumed you have already opened it with the correct encoding specified.

classmethod from_json(path, row_names=None, key=None, newline=False, column_types=None, **kwargs)¶

Create a new table from a JSON file.

Once the JSON has been deseralized, the resulting Python object is passed to Table.from_object().

If the file contains a top-level dictionary you may specify what property contains the row list using the key parameter.

kwargs will be passed through to json.load().

Parameters:	path – Filepath or file-like object from which to read JSON data. row_names – See the Table.__init__(). key – The key of the top-level dictionary that contains a list of row arrays. newline – If True then the file will be parsed as “newline-delimited JSON”. column_types – See Table.__init__().

classmethod from_object(obj, row_names=None, column_types=None)¶

Create a new table from a Python object.

The object should be a list containing a dictionary for each “row”. Nested objects or lists will also be parsed. For example, this object:

{
    'one': {
        'a': 1,
        'b': 2,
        'c': 3
    },
    'two': [4, 5, 6],
    'three': 'd'
}

Would generate these columns and values:

{
    'one/a': 1,
    'one/b': 2,
    'one/c': 3,
    'two.0': 4,
    'two.1': 5,
    'two.2': 6,
    'three': 'd'
}

Column names and types will be inferred from the data.

Not all rows are required to have the same keys. Missing elements will be filled in with null values.

Parameters:	obj – Filepath or file-like object from which to read JSON data. row_names – See Table.__init__(). column_types – See Table.__init__().

group_by(key, key_name=None, key_type=None)¶

Create a TableSet with a table for each unique key.

Note that group names will always be coerced to a string, regardless of the format of the input column.

Parameters:

key – Either the name of a column from the this table to group by, or a function that takes a row and returns a value to group by. key_name – A name that describes the grouped properties. Defaults to the column name that was grouped on or “group” if grouping with a key function. See TableSet for more. key_type – An instance of any subclass of DataType. If not provided it will default to a :class`.Text`.

Returns:

A TableSet mapping where the keys are unique values from the key and the values are new Table instances containing the grouped rows.

homogenize(key, compare_values, default_row=None)¶

Fill in missing rows in a series.

This can be used, for instance, to add rows for missing years in a time series.

Missing rows are found by comparing the values in the key columns with those provided as compare_values.

Values not found in the table will be used to generate new rows with the given default_row.

default_row should be an array of values or an array-generating function. If not specified, the new rows will have None in columns all columns not specified in key.

If default_row is an array of values, its length should be row length minus the number of column names provided in the key.

If it is an array-generating function, the function should take an array of missing values for each new row and output a full row including those values.

Parameters:

key – Either a column name or a sequence of such names. compare_values – Either an array of column values if key is a single column name or a sequence of arrays of values if key is a sequence of names. It can also be a generator that yields either of the two. A row is created for each value or list of values not found in the rows of the table. default_row – An array of values or a function to generate new rows. The length of the input array should be equal to row length minus column_names count. The length of array generated by the function should be the row length.

Returns:

A new Table.

join(right_table, left_key=None, right_key=None, inner=False, full_outer=False, require_match=False, columns=None)¶

Create a new table by joining two table’s on common values. This method implements most varieties of SQL join, in addition to some unique features.

If left_key and right_key are both None then this method will peform a “sequential join”, which is to say it will join on row number. The inner and full_outer arguments will determine whether dangling left-hand and right-hand rows are included, respectively.

If left_key is specified, then a “left outer join” will be performed. This will combine columns from the right_table anywhere that left_key and right_key are equal. Unmatched rows from the left table will be included with the right-hand columns set to None.

If inner is True then an “inner join” will be performed. Unmatched rows from either table will be left out.

If full_outer is True then a “full outer join” will be performed. Unmatched rows from both tables will be included, with the columns in the other table set to None.

In all cases, if right_key is None then it left_key will be used for both tables.

If left_key and right_key are column names, the right-hand identifier column will not be included in the output table.

If require_match is True unmatched rows will raise an exception. This is like an “inner join” except any row that doesn’t have a match will raise an exception instead of being dropped. This is useful for enforcing expectations about datasets that should match.

Column names from the right table which also exist in this table will be suffixed “2” in the new table.

A subset of columns from the right-hand table can be included in the joined table using the columns argument.

Parameters:

right_table – The “right” table to join to. left_key – Either the name of a column from the this table to join on, the index of a column, a sequence of such column identifiers, a function that takes a row and returns a value to join on, or None in which case the tables will be joined on row number. right_key – Either the name of a column from :code:table` to join on, the index of a column, a sequence of such column identifiers, or a function that takes a ow and returns a value to join on. If None then left_key will be used for both. If left_key is None then this value is ignored. inner – Perform a SQL-style “inner join” instead of a left outer join. Rows which have no match for left_key will not be included in the output table. full_outer – Perform a SQL-style “full outer” join rather than a left or a right. May not be used in combination with inner. require_match – If true, an exception will be raised if there is a left_key with no matching right_key. columns – A sequence of column names from right_table to include in the final output table. Defaults to all columns not in right_key. Ignored when full_outer is True.

Returns:

A new Table.

limit(start_or_stop=None, stop=None, step=None)¶

Create a new table with fewer rows.

See also: Python’s builtin slice().

Parameters:	start_or_stop – If the only argument, then how many rows to include, otherwise, the index of the first row to include. stop – The index of the last row to include. step – The size of the jump between rows to include. (step=2 will return every other row.)
Returns:	A new Table.

line_chart(x=0, y=1, path=None, width=None, height=None)¶

Render a line chart using leather.Chart.

Parameters:

x – The name or index of a column to plot as the x-axis. Defaults to the first column in the table. y – The name or index of a column to plot as the y-axis. Defaults to the second column in the table. path – If specified, the resulting SVG will be saved to this location. If None and running in IPython, then the SVG will be rendered inline. Otherwise, the SVG data will be returned as a string. width – The width of the output SVG. height – The height of the output SVG.

classmethod merge(tables, row_names=None, column_names=None)¶

Create a new table from a sequence of similar tables.

This method will not carry over row names from the merged tables, but new row names can be specified with the row_names argument.

It is possible to limit the columns included in the new Table with column_names argument. For example, to only include columns from a specific table, set column_names equal to table.column_names.

Parameters:	tables – An sequence of Table instances. row_names – See Table for the usage of this parameter. column_names – A sequence of column names to include in the new Table. If not specified, all distinct column names from tables are included.
Returns:	A new Table.

normalize(key, properties, property_column='property', value_column='value', column_types=None)¶

Create a new table with columns converted into rows values.

For example:

name	gender	race	age
Jane	female	black	24
Jack	male	white	35
Joe	male	black	28

can be normalized on columns ‘gender’, ‘race’ and ‘age’:

name	property	value
Jane	gender	female
Jane	race	black
Jane	age	24
...	...	...

This is the opposite of Table.denormalize().

Parameters:

key – A column name or a sequence of column names that should be maintained as they are in the normalized self. Typically these are the tables unique identifiers and any metadata about them. properties – A column name or a sequence of column names that should be converted to properties in the new self. property_column – The name to use for the column containing the property names. value_column – The name to use for the column containing the property values. column_types – A sequence of two column types for the property and value column in that order or an instance of TypeTester. Defaults to a generic TypeTester.

Returns:

A new Table.

order_by(key, reverse=False)¶

Create a new table that is sorted.

Parameters:	key – Either the name of a single column to sort by, a sequence of such names, or a function that takes a row and returns a value to sort by. reverse – If True then sort in reverse (typically, descending) order.
Returns:	A new Table.

pivot(key=None, pivot=None, aggregation=None, computation=None, default_value=<object object>, key_name=None)¶

Create a new table by grouping the data, aggregating those groups, applying a computation, and then organizing the groups into new rows and columns.

This is sometimes called a “crosstab”.

name	race	gender
Joe	white	male
Jane	black	female
Josh	black	male
Jim	asian	female

This table can be pivoted with key equal to “race” and columns equal to “gender”. The default aggregation is Count. This would result in the following table.

race	male	female
white	1	0
black	1	1
asian	0	1

If one or more keys are specified then the resulting table will automatically have row_names set to those keys.

See also the related method Table.denormalize().

Parameters:

key – Either the name of a column from the this table to group by, a sequence of such column names, a function that takes a row and returns a value to group by, or None, in which case there will be only a single row in the output table. pivot – A column name whose unique values will become columns in the new table, or None in which case there will be a single value column in the output table. aggregation – An instance of an Aggregation to perform on each group of data in the pivot table. (Each cell is the result of an aggregation of the grouped data.) If not specified this defaults to Count with no arguments. computation – An optional Computation instance to be applied to the aggregated sequence of values before they are transposed into the pivot table. Use the class name of the aggregation as your column name argument when constructing your computation. (This is “Count” if using the default value for aggregation.) default_value – Value to be used for missing values in the pivot table. Defaults to Decimal(0). If performing non-mathematical aggregations you may wish to set this to None. key_name – A name for the key column in the output table. This is most useful when the provided key is a function. This argument is not valid when key is a sequence.

Returns:

A new Table.

print_bars(label_column_name='group', value_column_name='Count', domain=None, width=120, output=<open file '<stdout>', mode 'w'>, printable=False)¶

Print a text-based bar chart based on this table.

Parameters:

label_column_name – The column containing the label values. Defaults to group, which is the default output of Table.pivot() or Table.bins(). value_column_name – The column containing the bar values. Defaults to Count, which is the default output of Table.pivot() or Table.bins(). domain – A 2-tuple containing the minimum and maximum values for the chart’s x-axis. The domain must be large enough to contain all values in the column. width – The width, in characters, to use for the bar chart. Defaults to 120. output – A file-like object to print to. Defaults to sys.stdout. printable – If true, only printable characters will be outputed.

print_csv(**kwargs)¶

Print this table as a CSV.

This is the same as passing sys.stdout to Table.to_csv().

kwargs will be passed on to Table.to_csv().

print_html(max_rows=20, max_columns=6, output=<open file '<stdout>', mode 'w'>, max_column_width=20, locale=None)¶

Print an HTML version of this table.

Parameters:

max_rows – The maximum number of rows to display before truncating the data. This defaults to 20 to prevent accidental printing of the entire table. Pass None to disable the limit. max_columns – The maximum number of columns to display before truncating the data. This defaults to 6 to prevent wrapping in most cases. Pass None to disable the limit. output – A file-like object to print to. Defaults to sys.stdout, unless running in Jupyter. (See above.) max_column_width – Truncate all columns to at most this width. The remainder will be replaced with ellipsis. locale – Provide a locale you would like to be used to format the output. By default it will use the system’s setting.

print_json(**kwargs)¶

Print this table as JSON.

This is the same as passing sys.stdout to Table.to_json().

kwargs will be passed on to Table.to_json().

print_structure(output=<open file '<stdout>', mode 'w'>, max_rows=None)¶

Print this table’s column names and types as a plain-text table.

Parameters:	output – The output to print to.

print_table(max_rows=20, max_columns=6, output=<open file '<stdout>', mode 'w'>, max_column_width=20, locale=None, max_precision=3)¶

Print a text-based view of the data in this table.

The output of this method is Github Friendly Markdown (GFM) compatible.

Parameters:

max_rows – The maximum number of rows to display before truncating the data. This defaults to 20 to prevent accidental printing of the entire table. Pass None to disable the limit. max_columns – The maximum number of columns to display before truncating the data. This defaults to 6 to prevent wrapping in most cases. Pass None to disable the limit. output – A file-like object to print to. max_column_width – Truncate all columns to at most this width. The remainder will be replaced with ellipsis. locale – Provide a locale you would like to be used to format the output. By default it will use the system’s setting.

Max_precision:

Puts a limit on the maximum precision displayed for number types. Numbers with lesser precision won’t be affected. This defaults to 3. Pass None to disable limit.

rename(column_names=None, row_names=None, slug_columns=False, slug_rows=False, **kwargs)¶

Create a copy of this table with different column names or row names.

By enabling slug_columns or slug_rows and not specifying new names you may slugify the table’s existing names.

kwargs will be passed to the slugify method in python-slugify. See: https://github.com/un33k/python-slugify

Parameters:

column_names – New column names for the renamed table. May be either an array or a dictionary mapping existing column names to new names. If not specified, will use this table’s existing column names. row_names – New row names for the renamed table. May be either an array or a dictionary mapping existing row names to new names. If not specified, will use this table’s existing row names. slug_columns – If True, column names will be converted to slugs and duplicate names will have unique identifiers appended. slug_rows – If True, row names will be converted to slugs and dupicate names will have unique identifiers appended.

row_names¶

An tuple of strings, if this table has row names.

If this table does not have row names, then None.

rows¶

A MappedSeqeuence with row names for keys (if specified) and Row instances for values.

scatterplot(x=0, y=1, path=None, width=None, height=None)¶

Render a scatterplot using leather.Chart.

Parameters:

x – The name or index of a column to plot as the x-axis. Defaults to the first column in the table. y – The name or index of a column to plot as the y-axis. Defaults to the second column in the table. path – If specified, the resulting SVG will be saved to this location. If None and running in IPython, then the SVG will be rendered inline. Otherwise, the SVG data will be returned as a string. width – The width of the output SVG. height – The height of the output SVG.

select(key)¶

Create a new table with only the specified columns.

Parameters:	key – Either the name of a single column to include or a sequence of such names.
Returns:	A new Table.

to_csv(path, **kwargs)¶

Write this table to a CSV. This method uses agate’s builtin CSV writer, which supports unicode on both Python 2 and Python 3.

kwargs will be passed through to the CSV writer.

Parameters:	path – Filepath or file-like object to write to.

to_json(path, key=None, newline=False, indent=None, **kwargs)¶

Write this table to a JSON file or file-like object.

kwargs will be passed through to the JSON encoder.

Parameters:

path – File path or file-like object to write to. key – If specified, JSON will be output as an hash instead of a list. May be either the name of a column from the this table containing unique values or a function that takes a row and returns a unique value. newline – If True, output will be in the form of “newline-delimited JSON”. indent – If specified, the number of spaces to indent the JSON for formatting.

where(test)¶

Create a new Table with only those rows that pass a test.

Parameters:	test (function) – A function that takes a Row and returns True if it should be included in the new Table.
Returns:	A new Table.