1#!/usr/bin/env python
2
3import agate
4
5tester = agate.TypeTester(force={
6    'fips': agate.Text()
7})
8
9table = agate.Table.from_csv('examples/realdata/ks_1033_data.csv', column_types=tester)
10
11# Question 1: What was the total cost to Kansas City area counties?
12
13# Filter to counties containing Kansas City
14kansas_city = table.where(lambda r: r['county'] in ('JACKSON', 'CLAY', 'CASS', 'PLATTE'))
15
16# Sum total_cost of four counties
17print('Total for Kansas City area: %i' % kansas_city.aggregate(agate.Sum('total_cost')))
18
19# Question 2: Which counties spent the most?
20
21# Group by counties
22counties = table.group_by('county')
23
24# Aggregate totals for all counties
25totals = counties.aggregate([
26    ('total_cost_sum', agate.Sum('total_cost'))
27])
28
29totals = totals.order_by('total_cost_sum', reverse=True)
30totals.limit(20).print_bars('county', 'total_cost_sum', width=80)
31
32print('Five most spendy counties:')
33
34totals.print_table(5)
35
36# Question 3: What are the most recent purchases?
37
38recent = table.order_by('ship_date', reverse=True)
39
40print('Five most recent purchases:')
41
42recent.print_table(5, 5)
43
44# Question 4: What is the standard of deviation of the cost of all purchases?
45
46stdev = table.aggregate(agate.StDev('total_cost'))
47
48print('Standard deviation of total_cost: %.2f' % stdev)
49
50# Question 5: How many robots were purchased?
51
52robots = table.where(lambda r: 'ROBOT' in (r['item_name'] or [])).aggregate(agate.Sum('quantity'))
53
54print('Number of robots purchased: %i' % robots)
55