Why agate?

Principles

agate is a intended to fill a very particular programming niche. It should not be allowed to become as complex as numpy or pandas. Please bear in mind the following principles when considering a new feature:

Users

To use agate install it with pip:

pip install agate

For non-English locale support, install PyICU.

Developers

If you are a developer that also wants to hack on agate, install it from git:

git clone git://github.com/wireservice/agate.git
cd agate
mkvirtualenv agate
pip install -e .[test]
python setup.py develop

NOTE:

pytest --cov agate

Supported platforms

agate supports the following versions of Python:

It is tested primarily on OSX, but due to its minimal dependencies it should work perfectly on both Linux and Windows.

NOTE:

Creating tables

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/wireservice/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')

Save a table

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')

Remove columns

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)

Filter rows

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', str(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}', str(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'))

Sort

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())

Search

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 names and values

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))
])

Statistics

Common descriptive and aggregate statistics are included with the core agate library. For additional statistical methods beyond the scope of agate consider using the agate-stats extension or integrating with scipy.

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([


    ('salary_min', agate.Min('salary')),


    ('salary_ave', agate.Mean('salary')),


    ('salary_max', 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.

Compute new values

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
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))
])

Dates and times

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)
])

Emulate SQL

agate's command structure is very similar to SQL. The primary difference between agate and SQL is that commands like SELECT and WHERE explicitly create new tables. You can chain them together as you would with SQL, but be aware each command is actually creating a new table.

NOTE:

If you want to read and write data from SQL, see From a SQL database.

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')

NOTE:

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)

NOTE:

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.

Emulate Excel

One of agate's most powerful assets is that instead of a wimpy "formula" language, you have the entire Python language at your disposal. Here are examples of how to translate a few common Excel operations.

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.

Emulate R

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')

Emulate underscore.js

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))

Homogenize rows

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

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:

Renaming and reordering columns

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'])

Transform

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:

Locales

agate strives to work equally well for users from all parts of the world. This means properly handling foreign currencies, date formats, etc. To facilitate this, agate makes a hard distinction between your locale and the locale of the data you are working with. This allows you to work seamlessly with data from other countries.

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='nl_NL')
column_names = ['city', 'population']
column_types = [text_type, dutch_numbers]
table = agate.Table.from_csv('dutch_cities.csv', columns)

Rank

There are many ways to rank a sequence of values. agate strives to find a balance between simple, intuitive ranking and flexibility when you need it.

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.

Charts

Agate offers two kinds of built in charting: very simple text bar charts and SVG charting via leather. Both are intended for efficiently exploring data, rather than producing publication-ready charts.

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()

Lookup

Generate new columns by mapping existing data to common lookup tables.

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:

Basics

Coming from other tools

Advanced techniques

Have a common use case that isn't covered? Please submit an issue on the GitHub repository.

Using extensions

agate support's plugin-style extensions using a monkey-patching pattern. Libraries can be created that add new methods onto Table and TableSet. For example, agate-sql adds the ability to read and write tables from a SQL database:

import agate
import agatesql
# After calling patch the from_sql and to_sql methods are now part of the Table class
table = agate.Table.from_sql('postgresql:///database', 'input_table')
table.to_sql('postgresql:///database', 'output_table')

List of extensions

Here is a list of agate extensions that are known to be actively maintained:

Writing your own extensions

Writing your own extensions is straightforward. Create a function that acts as your "patch" and then dynamically add it to Table or TableSet.

import agate
def new_method(self):


    print('I do something to a Table when you call me.')
agate.Table.new_method = new_method

You can also create new classmethods:

def new_class_method(cls):


    print('I make Tables when you call me.')
agate.Table.new_method = classmethod(new_method)

These methods can now be called on Table class in your code:

>>> import agate
>>> import myextension
>>> table = agate.Table(rows, column_names, column_types)
>>> table.new_method()
'I do something to a Table when you call me.'
>>> agate.Table.new_class_method()
'I make Tables when you call me.'

The same pattern also works for adding methods to TableSet.

Table

Properties

Creating

Saving

Basic processing

Calculating new data

Advanced processing

Previewing

Charting

Detailed list

TableSet

Properties

Creating

Saving

Processing

Previewing

Charting

Table Proxy Methods

Detailed list

Columns and rows

Data types

Supported types

Detailed list

Type inference

Aggregations

Basic aggregations

Statistical aggregations

Text aggregations

Detailed list

Computations

Mathematical computations

Detailed list

CSV reader and writer

Agate contains CSV readers and writers that are intended to be used as a drop-in replacement for csv. These versions add unicode support for Python 2 and several other minor features.

Agate methods will use these version automatically. If you would like to use them in your own code, you can import them, like this:

from agate import csv

Due to nuanced differences between the versions, these classes are implemented seperately for Python 2 and Python 3. The documentation for both versions is provided below, but only the one for your version of Python is imported with the above code.

Python 3

Python 2

Python 3 details

Python 2 details

Fixed-width reader

Agate contains a fixed-width file reader that is designed to work like Python's csv.

These readers work with CSV-formatted schemas, such as those maintained at wireservice/ffs.

Detailed list

Miscellaneous

Exceptions

Warnings

Unit testing helpers

Config

Process for documentation

Not a developer? That's fine! As long as you can use git (there are many tutorials) then you can contribute to agate. Please follow this process:

Process for code

Hacker? We'd love to have you hack with us. Please follow this process to make your contribution:

Licensing

To the extent that they care, contributors should keep in mind that the source of agate and therefore of any contributions are licensed under the permissive MIT license. By submitting a patch or pull request you are agreeing to release your code under this license. You will be acknowledged in the AUTHORS list, the commit history and the hearts and minds of journalists everywhere.

1.7.1 - Jan 4, 2023

1.7.0 - Jan 3, 2023

1.6.3 - July 15, 2021

1.6.2 - March 10, 2021

1.6.1 - March 11, 2018

1.6.0 - February 28, 2017

This update should not cause any breaking changes, however, it is being classified as major release because the dependency on awesome-slugify, which is licensed with GPLv3, has been replaced with python-slugify, which is licensed with MIT.

1.5.5 - December 29, 2016

1.5.4 - December 27, 2016

1.5.3 - December 26, 2016

This is a minor release that adds one feature: sequential joins (by row number). It also fixes several small bugs blocking a downstream release of csvkit.

1.5.2 - December 24, 2016

1.5.1 - December 23, 2016

This is a minor release fixing several small bugs that were blocking a downstream release of csvkit.

1.5.0 - November 16, 2016

This release adds SVG charting via the leather charting library. Charts methods have been added for both Table and TableSet. (The latter create lattice plots.) See the revised tutorial and new cookbook entries for examples. Leather is still an early library. Please report any bugs.

Also in this release are a Slugify computation and a variety of small fixes and improvements.

The complete list of changes is as follows:

1.4.0 - May 26, 2016

This release adds several new features, fixes numerous small bug-fixes, and improves performance for common use cases. There are some minor breaking changes, but few user are likely to encounter them. The most important changes in this release are:

The complete list of changes is as follows:

1.3.1 - March 30, 2016

The major feature of this release is new API documentation. Several minor features and bug fixes are also included. There are no major breaking changes in this release.

Internally, the agate codebase has been reorganized to be more modular, but this should be invisible to most users.

1.3.0 - February 28, 2016

This version implements several new features and includes two major breaking changes.

Please take note of the following breaking changes:

The major new features in this release are primarly related to transforming (reshaping) tables. They are:

Please see the following complete list of changes for a variety of other bug fixes and improvements.

1.2.2 - February 5, 2016

This release adds several minor features. The only breaking change is that default column names will now be lowercase instead of uppercase. If you depended on these names in your scripts you will need to update them accordingly.

1.2.1 - February 5, 2016

This release implements several new features and bug fixes. There are no significant breaking changes.

Special thanks to Neil Bedi for his extensive contributions to this release.

1.2.0 - January 18, 2016

This version introduces one breaking change, which is only relevant if you are using custom Computation subclasses.

1.1.0 - November 4, 2015

This version of agate introduces three major changes.

1.0.1 - October 29, 2015

1.0.0 - October 22, 2015

0.11.0 - October 6, 2015

0.10.0 - September 22, 2015

0.9.0 - September 14, 2015

0.8.0 - September 9, 2015

0.7.0 - September 3, 2015

0.6.0 - September 1, 2015

0.5.0 - August 28, 2015

0.4.0 - September 27, 2014

0.3.0

0.2.0

0.1.0