# group entire rows
for key, group in rowgroupby(table, 'foo'):
print key, list(group)
# group specific values
for key, group in rowgroupby(table, 'foo', 'bar'):
print key, list(group)
# fold
table1 = (('id', 'count'), (1, 3), (1, 5), (2, 4), (2, 8))
from petl import fold, look
look(table1)
import operator
table2 = fold(table1, 'id', operator.add, 'count', presorted=True)
look(table2)
# aggregate
table1 = (('foo', 'bar', 'baz'),
('a', 3, True),
('a', 7, False),
('b', 2, True),
('b', 2, False),
('b', 9, False),
('c', 4, True))
from petl import aggregate, look
look(table1)
# aggregate whole rows
# group entire rows
for key, group in rowgroupby(table, 'foo'):
print key, list(group)
# group specific values
for key, group in rowgroupby(table, 'foo', 'bar'):
print key, list(group)
# fold
table1 = (('id', 'count'), (1, 3), (1, 5), (2, 4), (2, 8))
from petl import fold, look
look(table1)
import operator
table2 = fold(table1, 'id', operator.add, 'count', presorted=True)
look(table2)
# aggregate
table1 = (('foo', 'bar', 'baz'),
('a', 3, True),
('a', 7, False),
('b', 2, True),
('b', 2, False),
('b', 9, False),
('c', 4, True))
from petl import aggregate, look
look(table1)
# aggregate whole rows
def sales_summary(start_dt=None, end_dt=None):
"""tally up gross (sale over list) profits
TODO: tally up net profites (gross profit vs inventory purchase total)
TODO: Keyword Arguments:
start_dt {[type]} -- datetime for start of query (default: {None})
end_dt {[type]} -- datetime for start of query [description] (default: {None})
Returns:
[dict] -- various types of sales information, stored in a dictionary.
"""
# products = db.session.query(Product).all()
# sales = db.session.query(Sale).all()
# retrieve existing tables
products_records = etl.fromdb(db.engine, 'SELECT * FROM product')
sales_records = etl.fromdb(db.engine, 'SELECT * FROM sale')
# join product info to sales data
sales_data = etl.join(sales_records,
products_records,
lkey='product_id',
rkey='id')
# prep joined sales data for tabulation
sales_data = etl.convert(sales_data, 'date', lambda dt: format_date(dt))
sales_data = etl.sort(sales_data, 'date')
sales_data = etl.convert(sales_data, 'quantity',
lambda q: handle_none(q, replace_with=1))
sales_data = etl.addfield(sales_data, 'profit',
lambda rec: calculate_profit(rec))
sales_data = etl.addfield(sales_data, 'gross_sales',
lambda rec: calculate_gross_sales(rec))
# summarize data into charting-friendly data structures
chart_count = etl.fold(sales_data,
'date',
operator.add,
'quantity',
presorted=True)
chart_count = etl.rename(chart_count, {'key': 'x', 'value': 'y'})
chart_count, chart_count_missing_date = etl.biselect(
chart_count, lambda rec: rec.x is not None)
# print(chart_count)
# etl.lookall(chart_count)
chart_gross = etl.fold(sales_data,
'date',
operator.add,
'gross_sales',
presorted=True)
chart_gross = etl.rename(chart_gross, {'key': 'x', 'value': 'y'})
chart_gross, chart_gross_missing_date = etl.biselect(
chart_gross, lambda rec: rec.x is not None)
# print(chart_gross)
# etl.lookall(chart_gross)
chart_profit = etl.fold(sales_data,
'date',
operator.add,
'profit',
presorted=True)
chart_profit = etl.rename(chart_profit, {'key': 'x', 'value': 'y'})
chart_profit, chart_profit_missing_date = etl.biselect(
chart_profit, lambda rec: rec.x is not None)
# tabulate some figures
gross_sales = 0
profits = 0
for sale in etl.dicts(sales_data):
profits += calculate_profit(sale)
gross_sales += calculate_gross_sales(sale)
# for i in etl.dicts(chart_count):
# print(i)
# for i in etl.dicts(chart_gross):
# print(i)
return {
'gross_sales': gross_sales,
'profits': profits,
'chart_gross': list(etl.dicts(chart_gross)),
'chart_gross_missing_date': list(etl.dicts(chart_gross_missing_date)),
'chart_profit': list(etl.dicts(chart_profit)),
'chart_profit_missing_date':
list(etl.dicts(chart_profit_missing_date)),
'chart_count': list(etl.dicts(chart_count)),
'chart_count_missing_date': list(etl.dicts(chart_count_missing_date))
}