def test_distinct():
assert list(map(str, Aggregate("distinct x", "count", {}).get_columns())) == [
"count(distinct x)"
]
assert list(
map(
str,
Aggregate("distinct x", "count", {}).get_columns(
when="date < '2012-01-01'"
),
)
) == ["count(distinct x) FILTER (WHERE date < '2012-01-01')"]
assert list(
map(
str,
Aggregate("distinct(x)", "count", {}).get_columns(
when="date < '2012-01-01'"
),
)
) == ["count(distinct (x)) FILTER (WHERE date < '2012-01-01')"]
assert list(
map(
str,
Aggregate("distinct(x,y)", "count", {}).get_columns(
when="date < '2012-01-01'"
),
)
) == ["count(distinct (x,y)) FILTER (WHERE date < '2012-01-01')"]
def test_aggregate_arithmetic():
n = Aggregate("x", "sum", {})
d = Aggregate("1", "count", {})
m = Aggregate("y", "avg", {})
(e,) = (n / d + m).get_columns(prefix="prefix_")
assert str(e) == "((sum(x)*1.0 / count(1)) + avg(y))"
assert e.name == "prefix_x_sum/1_count+y_avg"
def test_Aggregation_colname_aggregate_lookup():
n = Aggregate("x", "sum", {})
d = Aggregate("1", "count", {})
m = Aggregate("y", "avg", {})
aggregation = Aggregation([n, d, m],
groups=['entity_id'],
from_obj="source",
prefix="mysource",
state_table="tbl")
assert aggregation.colname_aggregate_lookup == {
'mysource_entity_id_x_sum': 'sum',
'mysource_entity_id_1_count': 'count',
'mysource_entity_id_y_avg': 'avg'
}
def test_Aggregation_colname_agg_function():
n = Aggregate("x", "sum", {})
d = Aggregate("1", "count", {})
m = Aggregate("y", "stddev_samp", {})
aggregation = Aggregation([n, d, m],
groups=['entity_id'],
from_obj="source",
prefix="mysource",
state_table="tbl")
assert aggregation.colname_agg_function(
'mysource_entity_id_x_sum') == 'sum'
assert aggregation.colname_agg_function(
'mysource_entity_id_y_stddev_samp') == 'stddev_samp'
def test_aggregate_format_kwargs():
agg = Aggregate("'{collate_date}' - date", "min", {})
assert list(
map(str, agg.get_columns(
format_kwargs={"collate_date": "2012-01-01"}))) == [
"min('2012-01-01' - date)"
]
def test_st_explicit_execute():
agg = Aggregate({"F": "results='Fail'"}, ["count"], IMPUTE_RULES)
mode = Aggregate("", "mode", IMPUTE_RULES, order="zip")
st = SpacetimeAggregation(
[agg, agg + agg, mode],
from_obj=ex.table("food_inspections"),
groups={"license": ex.column("license_no"), "zip": ex.column("zip")},
intervals={"license": ["1 year", "2 years", "all"], "zip": ["1 year"]},
dates=["2016-08-30", "2015-11-06"],
state_table="inspection_states",
state_group="license_no",
date_column="inspection_date",
prefix="food_inspections",
)
with Postgresql() as postgresql:
engine = create_engine(postgresql.url())
st.execute(engine.connect())
def test_Aggregation_imputation_flag_base():
n = Aggregate("x", ["sum", "count"], {})
m = Aggregate("y", "stddev_samp", {})
aggregation = Aggregation([n, m],
groups=['entity_id'],
from_obj="source",
prefix="mysource",
state_table="tbl")
assert aggregation.imputation_flag_base(
'mysource_entity_id_x_sum') == 'mysource_entity_id_x'
assert aggregation.imputation_flag_base(
'mysource_entity_id_x_count') == 'mysource_entity_id_x'
assert aggregation.imputation_flag_base(
'mysource_entity_id_y_stddev_samp'
) == 'mysource_entity_id_y_stddev_samp'
with pytest.raises(KeyError):
aggregation.imputation_flag_base('mysource_entity_id_x_stddev_samp')
def test_execute():
agg = Aggregate("results='Fail'", ["count"], IMPUTE_RULES)
st = Aggregation(
[agg],
from_obj='food_inspections',
groups=['license_no', 'zip'],
state_table='all_licenses',
state_group='license_no',
)
with Postgresql() as postgresql:
engine = create_engine(postgresql.url())
st.execute(engine.connect())
def test_st_explicit_execute():
agg = Aggregate({'F': "results='Fail'"}, ["count"], IMPUTE_RULES)
mode = Aggregate("", "mode", IMPUTE_RULES, order="zip")
st = SpacetimeAggregation([agg, agg + agg, mode],
from_obj=ex.table('food_inspections'),
groups={
'license': ex.column('license_no'),
'zip': ex.column('zip')
},
intervals={
'license': ["1 year", "2 years", "all"],
'zip': ["1 year"]
},
dates=['2016-08-30', '2015-11-06'],
state_table='inspection_states',
state_group='license_no',
date_column='inspection_date',
prefix='food_inspections')
with Postgresql() as postgresql:
engine = create_engine(postgresql.url())
st.execute(engine.connect())
def test_index_column_lookup(test_engine):
aggregations = [
SpacetimeAggregation(
prefix="prefix1",
aggregates=[
Categorical(
col="cat_one",
function="sum",
choices=["good", "bad", "inbetween"],
impute_rules={"coltype": "categorical", "all": {"type": "zero"}},
)
],
groups=["entity_id"],
intervals=["all"],
date_column="knowledge_date",
output_date_column="as_of_date",
dates=["2013-09-30", "2014-09-30"],
state_table="states",
state_group="entity_id",
schema="features",
from_obj="data",
),
SpacetimeAggregation(
prefix="prefix2",
aggregates=[
Aggregate(
quantity="quantity_one",
function="count",
impute_rules={"coltype": "aggregate", "all": {"type": "zero"}},
)
],
groups=["entity_id", "zip_code"],
intervals=["all"],
date_column="knowledge_date",
output_date_column="as_of_date",
dates=["2013-09-30", "2014-09-30"],
state_table="states",
state_group="entity_id",
schema="features",
from_obj="data",
),
]
features_schema_name = "features"
feature_generator = FeatureGenerator(
db_engine=test_engine,
features_schema_name=features_schema_name,
)
lookup = feature_generator.index_column_lookup(aggregations)
assert lookup == {
"prefix1_aggregation_imputed": ["as_of_date", "entity_id"],
"prefix2_aggregation_imputed": ["as_of_date", "entity_id", "zip_code"],
}
def test_st_execute_broadcast_intervals():
agg = Aggregate("results='Fail'", ["count"], IMPUTE_RULES)
st = SpacetimeAggregation([agg],
from_obj='food_inspections',
groups=['license_no', 'zip'],
intervals=["1 year", "2 years", "all"],
dates=['2016-08-30', '2015-11-06'],
state_table='inspection_states',
state_group='license_no',
date_column='"inspection_date"')
with Postgresql() as postgresql:
engine = create_engine(postgresql.url())
st.execute(engine.connect())
def test_st_lazy_execute():
agg = Aggregate("results='Fail'", ["count"], IMPUTE_RULES)
st = SpacetimeAggregation(
[agg],
from_obj="food_inspections",
groups=["license_no", "zip"],
intervals={"license_no": ["1 year", "2 years", "all"], "zip": ["1 year"]},
dates=["2016-08-30", "2015-11-06"],
state_table="inspection_states",
state_group="license_no",
date_column='"inspection_date"',
)
with Postgresql() as postgresql:
engine = create_engine(postgresql.url())
st.execute(engine.connect())
def test_aggregate_imputation_lookup_all():
agg = Aggregate(
"a",
["avg", "sum"],
{
"coltype": "aggregate",
"all": {"type": "zero"},
"sum": {"type": "constant", "value": 3},
"max": {"type": "mean"},
},
)
assert agg.column_imputation_lookup()["a_avg"]["type"] == "zero"
assert agg.column_imputation_lookup()["a_avg"]["coltype"] == "aggregate"
assert agg.column_imputation_lookup()["a_sum"]["type"] == "constant"
assert agg.column_imputation_lookup()["a_sum"]["value"] == 3
assert agg.column_imputation_lookup()["a_sum"]["coltype"] == "aggregate"
def _aggregation(self, aggregation_config, feature_dates, state_table):
logging.info(
"Building collate.SpacetimeAggregation for config %s and %s as_of_dates",
aggregation_config,
len(feature_dates),
)
# read top-level imputation rules from the aggregation config; we'll allow
# these to be overridden by imputation rules at the individual feature
# level as those get parsed as well
agimp = aggregation_config.get("aggregates_imputation", {})
catimp = aggregation_config.get("categoricals_imputation", {})
arrcatimp = aggregation_config.get("array_categoricals_imputation", {})
aggregates = [
Aggregate(
aggregate["quantity"],
aggregate["metrics"],
dict(agimp, coltype="aggregate", **aggregate.get("imputation", {})),
coltype=aggregate.get('coltype', None)
)
for aggregate in aggregation_config.get("aggregates", [])
]
logging.info("Found %s quantity aggregates", len(aggregates))
categoricals = self._build_categoricals(
aggregation_config.get("categoricals", []), catimp
)
logging.info("Found %s categorical aggregates", len(categoricals))
array_categoricals = self._build_array_categoricals(
aggregation_config.get("array_categoricals", []), arrcatimp
)
logging.info("Found %s array categorical aggregates", len(array_categoricals))
return SpacetimeAggregation(
aggregates + categoricals + array_categoricals,
from_obj=aggregation_config["from_obj"],
intervals=aggregation_config["intervals"],
groups=aggregation_config["groups"],
dates=feature_dates,
state_table=state_table,
state_group=self.entity_id_column,
date_column=aggregation_config["knowledge_date_column"],
output_date_column="as_of_date",
input_min_date=self.feature_start_time,
schema=self.features_schema_name,
prefix=aggregation_config["prefix"],
)
def test_execute_schema_output_date_column():
agg = Aggregate("results='Fail'", ["count"], IMPUTE_RULES)
st = SpacetimeAggregation([agg],
from_obj='food_inspections',
groups=['license_no', 'zip'],
intervals={
'license_no': ["1 year", "2 years", "all"],
'zip': ["1 year"]
},
dates=['2016-08-30', '2015-11-06'],
state_table='inspection_states_diff_colname',
state_group='license_no',
schema="agg",
date_column='"inspection_date"',
output_date_column="aggregation_date")
with Postgresql() as postgresql:
engine = create_engine(postgresql.url())
st.execute(engine.connect())
def test_aggregate_imputation_lookup_all():
agg = Aggregate(
"a", ["avg", "sum"], {
"coltype": "aggregate",
"all": {
"type": "zero"
},
"sum": {
"type": "constant",
"value": 3
},
"max": {
"type": "mean"
}
})
assert agg.column_imputation_lookup()['a_avg']['type'] == 'zero'
assert agg.column_imputation_lookup()['a_avg']['coltype'] == 'aggregate'
assert agg.column_imputation_lookup()['a_sum']['type'] == 'constant'
assert agg.column_imputation_lookup()['a_sum']['value'] == 3
assert agg.column_imputation_lookup()['a_sum']['coltype'] == 'aggregate'
def _aggregation(self, aggregation_config, feature_dates, state_table):
logging.info(
'Building collate.SpacetimeAggregation for config %s and as_of_dates %s',
aggregation_config, feature_dates)
# read top-level imputation rules from the aggregation config; we'll allow
# these to be overridden by imputation rules at the individual feature
# level as those get parsed as well
agimp = aggregation_config.get('aggregates_imputation', {})
catimp = aggregation_config.get('categoricals_imputation', {})
arrcatimp = aggregation_config.get('array_categoricals_imputation', {})
aggregates = [
Aggregate(
aggregate['quantity'], aggregate['metrics'],
dict(agimp,
coltype='aggregate',
**aggregate.get('imputation', {})))
for aggregate in aggregation_config.get('aggregates', [])
]
logging.info('Found %s quantity aggregates', len(aggregates))
categoricals = self._build_categoricals(
aggregation_config.get('categoricals', []), catimp)
logging.info('Found %s categorical aggregates', len(categoricals))
array_categoricals = self._build_array_categoricals(
aggregation_config.get('array_categoricals', []), arrcatimp)
logging.info('Found %s array categorical aggregates',
len(array_categoricals))
return SpacetimeAggregation(
aggregates + categoricals + array_categoricals,
from_obj=aggregation_config['from_obj'],
intervals=aggregation_config['intervals'],
groups=aggregation_config['groups'],
dates=feature_dates,
state_table=state_table,
state_group=self.entity_id_column,
date_column=aggregation_config['knowledge_date_column'],
output_date_column='as_of_date',
input_min_date=self.feature_start_time,
schema=self.features_schema_name,
prefix=aggregation_config['prefix'])
def test_basic_spacetime():
with testing.postgresql.Postgresql() as psql:
engine = sqlalchemy.create_engine(psql.url())
engine.execute(
"create table events (entity_id int, event_date date, outcome bool)"
)
for event in events_data:
engine.execute("insert into events values (%s, %s, %s::bool)", event)
engine.execute("create table states (entity_id int, as_of_date date)")
for state in state_data:
engine.execute("insert into states values (%s, %s)", state)
agg = Aggregate(
"outcome::int",
["sum", "avg"],
{
"coltype": "aggregate",
"avg": {"type": "mean"},
"sum": {"type": "constant", "value": 3},
"max": {"type": "zero"},
},
)
st = SpacetimeAggregation(
aggregates=[agg],
from_obj="events",
groups=["entity_id"],
intervals=["1y", "2y", "all"],
dates=["2016-01-01", "2015-01-01"],
state_table="states",
state_group="entity_id",
date_column="event_date",
output_date_column="as_of_date",
)
st.execute(engine.connect())
r = engine.execute(
"select * from events_entity_id order by entity_id, as_of_date"
)
rows = [x for x in r]
assert rows[0]["entity_id"] == 1
assert rows[0]["as_of_date"] == date(2015, 1, 1)
assert rows[0]["events_entity_id_1y_outcome::int_sum"] == 1
assert rows[0]["events_entity_id_1y_outcome::int_avg"] == 0.5
assert rows[0]["events_entity_id_2y_outcome::int_sum"] == 1
assert rows[0]["events_entity_id_2y_outcome::int_avg"] == 0.5
assert rows[0]["events_entity_id_all_outcome::int_sum"] == 1
assert rows[0]["events_entity_id_all_outcome::int_avg"] == 0.5
assert rows[1]["entity_id"] == 1
assert rows[1]["as_of_date"] == date(2016, 1, 1)
assert rows[1]["events_entity_id_1y_outcome::int_sum"] == 1
assert rows[1]["events_entity_id_1y_outcome::int_avg"] == 0.5
assert rows[1]["events_entity_id_2y_outcome::int_sum"] == 2
assert rows[1]["events_entity_id_2y_outcome::int_avg"] == 0.5
assert rows[1]["events_entity_id_all_outcome::int_sum"] == 2
assert rows[1]["events_entity_id_all_outcome::int_avg"] == 0.5
assert rows[2]["entity_id"] == 2
assert rows[2]["as_of_date"] == date(2015, 1, 1)
assert rows[2]["events_entity_id_1y_outcome::int_sum"] == 0
assert rows[2]["events_entity_id_1y_outcome::int_avg"] == 0
assert rows[2]["events_entity_id_2y_outcome::int_sum"] == 1
assert rows[2]["events_entity_id_2y_outcome::int_avg"] == 0.5
assert rows[2]["events_entity_id_all_outcome::int_sum"] == 1
assert rows[2]["events_entity_id_all_outcome::int_avg"] == 0.5
assert rows[3]["entity_id"] == 2
assert rows[3]["as_of_date"] == date(2016, 1, 1)
assert rows[3]["events_entity_id_1y_outcome::int_sum"] is None
assert rows[3]["events_entity_id_1y_outcome::int_avg"] is None
assert rows[3]["events_entity_id_2y_outcome::int_sum"] == 0
assert rows[3]["events_entity_id_2y_outcome::int_avg"] == 0
assert rows[3]["events_entity_id_all_outcome::int_sum"] == 1
assert rows[3]["events_entity_id_all_outcome::int_avg"] == 0.5
assert rows[4]["entity_id"] == 3
assert rows[4]["as_of_date"] == date(2015, 1, 1)
assert rows[4]["events_entity_id_1y_outcome::int_sum"] == 0
assert rows[4]["events_entity_id_1y_outcome::int_avg"] == 0
assert rows[4]["events_entity_id_2y_outcome::int_sum"] == 0
assert rows[4]["events_entity_id_2y_outcome::int_avg"] == 0
assert rows[4]["events_entity_id_all_outcome::int_sum"] == 0
assert rows[4]["events_entity_id_all_outcome::int_avg"] == 0
assert rows[5]["entity_id"] == 3
assert rows[5]["as_of_date"] == date(2016, 1, 1)
assert rows[5]["events_entity_id_1y_outcome::int_sum"] == 1
assert rows[5]["events_entity_id_1y_outcome::int_avg"] == 0.5
assert rows[5]["events_entity_id_2y_outcome::int_sum"] == 1
assert rows[5]["events_entity_id_2y_outcome::int_avg"] == 0.25
assert rows[5]["events_entity_id_all_outcome::int_sum"] == 1
assert rows[5]["events_entity_id_all_outcome::int_avg"] == 0.25
assert rows[6]["entity_id"] == 4
# rows[6]['date'] == date(2015, 1, 1) is skipped due to no data!
assert rows[6]["as_of_date"] == date(2016, 1, 1)
assert rows[6]["events_entity_id_1y_outcome::int_sum"] == 0
assert rows[6]["events_entity_id_1y_outcome::int_avg"] == 0
assert rows[6]["events_entity_id_2y_outcome::int_sum"] == 0
assert rows[6]["events_entity_id_2y_outcome::int_avg"] == 0
assert rows[6]["events_entity_id_all_outcome::int_sum"] == 0
assert rows[6]["events_entity_id_all_outcome::int_avg"] == 0
assert len(rows) == 7
# check some imputation results
r = engine.execute(
"select * from events_aggregation_imputed order by entity_id, as_of_date"
)
rows = [x for x in r]
assert rows[6]["entity_id"] == 4
assert rows[6]["as_of_date"] == date(2015, 1, 1)
assert rows[6]["events_entity_id_1y_outcome::int_sum"] == 3
assert rows[6]["events_entity_id_1y_outcome::int_sum_imp"] == 1
assert (
round(float(rows[6]["events_entity_id_1y_outcome::int_avg"]), 4) == 0.1667
)
assert rows[6]["events_entity_id_1y_outcome::int_avg_imp"] == 1
assert rows[6]["events_entity_id_2y_outcome::int_sum"] == 3
assert rows[6]["events_entity_id_2y_outcome::int_sum_imp"] == 1
assert (
round(float(rows[6]["events_entity_id_2y_outcome::int_avg"]), 4) == 0.3333
)
assert rows[6]["events_entity_id_2y_outcome::int_avg_imp"] == 1
assert rows[6]["events_entity_id_all_outcome::int_sum"] == 3
assert rows[6]["events_entity_id_all_outcome::int_sum_imp"] == 1
assert (
round(float(rows[6]["events_entity_id_all_outcome::int_avg"]), 4) == 0.3333
)
assert rows[6]["events_entity_id_all_outcome::int_avg_imp"] == 1
assert rows[7]["entity_id"] == 4
assert rows[7]["as_of_date"] == date(2016, 1, 1)
assert rows[7]["events_entity_id_1y_outcome::int_sum"] == 0
assert rows[7]["events_entity_id_1y_outcome::int_sum_imp"] == 0
assert rows[7]["events_entity_id_1y_outcome::int_avg"] == 0
assert rows[7]["events_entity_id_1y_outcome::int_avg_imp"] == 0
assert rows[7]["events_entity_id_2y_outcome::int_sum"] == 0
assert rows[7]["events_entity_id_2y_outcome::int_sum_imp"] == 0
assert rows[7]["events_entity_id_2y_outcome::int_avg"] == 0
assert rows[7]["events_entity_id_2y_outcome::int_avg_imp"] == 0
assert rows[7]["events_entity_id_all_outcome::int_sum"] == 0
assert rows[7]["events_entity_id_all_outcome::int_sum_imp"] == 0
assert rows[7]["events_entity_id_all_outcome::int_avg"] == 0
assert rows[7]["events_entity_id_all_outcome::int_avg_imp"] == 0
assert len(rows) == 8
def test_input_min_date():
with testing.postgresql.Postgresql() as psql:
engine = sqlalchemy.create_engine(psql.url())
engine.execute("create table events (entity_id int, date date, outcome bool)")
for event in events_data:
engine.execute("insert into events values (%s, %s, %s::bool)", event)
engine.execute("create table states (entity_id int, date date)")
for state in state_data:
engine.execute("insert into states values (%s, %s)", state)
agg = Aggregate(
"outcome::int",
["sum", "avg"],
{
"coltype": "aggregate",
"avg": {"type": "mean"},
"sum": {"type": "constant", "value": 3},
"max": {"type": "zero"},
},
)
st = SpacetimeAggregation(
aggregates=[agg],
from_obj="events",
groups=["entity_id"],
intervals=["all"],
dates=["2016-01-01"],
state_table="states",
state_group="entity_id",
date_column='"date"',
input_min_date="2015-11-10",
)
st.execute(engine.connect())
r = engine.execute("select * from events_entity_id order by entity_id")
rows = [x for x in r]
assert rows[0]["entity_id"] == 1
assert rows[0]["date"] == date(2016, 1, 1)
assert rows[0]["events_entity_id_all_outcome::int_sum"] == 1
assert rows[0]["events_entity_id_all_outcome::int_avg"] == 1
assert rows[1]["entity_id"] == 4
assert rows[1]["date"] == date(2016, 1, 1)
assert rows[1]["events_entity_id_all_outcome::int_sum"] == 0
assert rows[1]["events_entity_id_all_outcome::int_avg"] == 0
assert len(rows) == 2
st = SpacetimeAggregation(
aggregates=[agg],
from_obj="events",
groups=["entity_id"],
intervals=["1y", "all"],
dates=["2016-01-01", "2015-01-01"],
state_table="states",
state_group="entity_id",
date_column='"date"',
input_min_date="2014-11-10",
)
with pytest.raises(ValueError):
st.validate(engine.connect())
with pytest.raises(ValueError):
st.execute(engine.connect())
def test_join_with_cohort_table(db_engine):
# if we specify joining with the cohort table
# only entity_id/date pairs in the cohort table should show up
db_engine.execute("create table events (entity_id int, date date, outcome bool)")
for event in events_data:
db_engine.execute("insert into events values (%s, %s, %s::bool)", event)
db_engine.execute("create table cohort (entity_id int, date date)")
# use the states list from above except only include entities 1 and 2 in the cohort
smaller_cohort = sorted(
product(
set([l[0] for l in events_data if l[0] == 1 or l[0] == 2]),
set([l[1] for l in events_data] + [date(2016, 1, 1)]),
)
)
for state in smaller_cohort:
db_engine.execute("insert into cohort values (%s, %s)", state)
# create our test aggregation with the important 'join_with_cohort_table' flag
agg = Aggregate(
"outcome::int",
["sum", "avg"],
{
"coltype": "aggregate",
"avg": {"type": "mean"},
"sum": {"type": "constant", "value": 3},
"max": {"type": "zero"},
},
)
st = SpacetimeAggregation(
aggregates=[agg],
from_obj="events",
groups=["entity_id"],
intervals=["all"],
dates=["2016-01-01", "2015-01-01"],
state_table="cohort",
state_group="entity_id",
date_column='"date"',
join_with_cohort_table=True,
)
st.execute(db_engine.connect())
r = db_engine.execute("select * from events_entity_id order by entity_id, date")
rows = [x for x in r]
# these rows should be similar to the rows in the basic spacetime test,
# except only the rows for entities 1 and 2 are present
assert len(rows) == 4
assert rows[0]["entity_id"] == 1
assert rows[0]["date"] == date(2015, 1, 1)
assert rows[0]["events_entity_id_all_outcome::int_sum"] == 1
assert rows[0]["events_entity_id_all_outcome::int_avg"] == 0.5
assert rows[1]["entity_id"] == 1
assert rows[1]["date"] == date(2016, 1, 1)
assert rows[1]["events_entity_id_all_outcome::int_sum"] == 2
assert rows[1]["events_entity_id_all_outcome::int_avg"] == 0.5
assert rows[2]["entity_id"] == 2
assert rows[2]["date"] == date(2015, 1, 1)
assert rows[2]["events_entity_id_all_outcome::int_sum"] == 1
assert rows[2]["events_entity_id_all_outcome::int_avg"] == 0.5
assert rows[3]["entity_id"] == 2
assert rows[3]["date"] == date(2016, 1, 1)
assert rows[3]["events_entity_id_all_outcome::int_sum"] == 1
assert rows[3]["events_entity_id_all_outcome::int_avg"] == 0.5
def test_aggregate():
agg = Aggregate("*", "count", {})
assert list(map(str, agg.get_columns())) == ["count(*)"]
def test_aggregate_tuple_quantity_when():
agg = Aggregate(("x", "y"), "corr", {})
assert list(map(str, agg.get_columns(when="date < '2012-01-01'"))) == [
"corr(x, y) FILTER (WHERE date < '2012-01-01')"
]
def test_aggregate_tuple_quantity():
agg = Aggregate(("x", "y"), "corr", {})
assert list(map(str, agg.get_columns())) == ["corr(x, y)"]
def test_ordered_aggregate_when():
agg = Aggregate("", "mode", {}, "x")
assert list(map(str, agg.get_columns(when="date < '2012-01-01'"))) == [
"mode() WITHIN GROUP (ORDER BY x) FILTER (WHERE date < '2012-01-01')"
]
def test_ordered_aggregate():
agg = Aggregate("", "mode", {}, "x")
(expression,) = agg.get_columns()
assert str(expression) == "mode() WITHIN GROUP (ORDER BY x)"
assert expression.name == "x_mode"
def test_imputation_output(feat_list, exp_imp_cols, feat_table):
with testing.postgresql.Postgresql() as psql:
engine = sqlalchemy.create_engine(psql.url())
engine.execute("create table states (entity_id int, as_of_date date)")
for state in states_table:
engine.execute("insert into states values (%s, %s)", state)
feat_sql = "\n".join(
[", prefix_entity_id_1y_%s_max int" % f for f in feat_list])
engine.execute("""create table prefix_aggregation (
entity_id int
, as_of_date date
%s
)""" % feat_sql)
ins_sql = ("insert into prefix_aggregation values (%s, %s" +
(", %s" * len(feat_list)) + ")")
for rec in feat_table:
engine.execute(ins_sql, rec)
for imp in available_imputations.keys():
# skip error imputation
if imp == "error":
continue
for coltype in ["aggregate", "categorical"]:
# only consider
if not imputation_values[imp][coltype]["avail"]:
continue
impargs = imputation_values[imp][coltype]["kwargs"]
aggs = [
Aggregate(
feat,
["max"],
{
"coltype": coltype,
"all": dict(type=imp, **impargs)
},
) for feat in feat_list
]
st = SpacetimeAggregation(
aggregates=aggs,
from_obj="prefix_events",
prefix="prefix",
groups=["entity_id"],
intervals=["1y"],
dates=["2016-01-01", "2016-02-03", "2016-03-14"],
state_table="states",
state_group="entity_id",
date_column="as_of_date",
input_min_date="2000-01-01",
output_date_column="as_of_date",
)
conn = engine.connect()
trans = conn.begin()
# excute query to find columns with null values and create lists of columns
# that do and do not need imputation when creating the imputation table
res = conn.execute(st.find_nulls())
null_counts = list(zip(res.keys(), res.fetchone()))
impute_cols = [col for col, val in null_counts if val > 0]
nonimpute_cols = [col for col, val in null_counts if val == 0]
# sql to drop and create the imputation table
drop_imp = st.get_drop(imputed=True)
create_imp = st.get_impute_create(
impute_cols=impute_cols, nonimpute_cols=nonimpute_cols)
# create the imputation table
conn.execute(drop_imp)
conn.execute(create_imp)
trans.commit()
# check the results
df = pd.read_sql("SELECT * FROM prefix_aggregation_imputed",
engine)
# we should have a record for every entity/date combo
assert df.shape[0] == len(states_table)
for feat in feat_list:
# all of the input columns should be in the result and be null-free
assert "prefix_entity_id_1y_%s_max" % feat in df.columns.values
assert df["prefix_entity_id_1y_%s_max" %
feat].isnull().sum() == 0
# for non-categoricals, should add an "imputed" column and be non-null
# (categoricals are expected to be handled through the null category)
# zero_noflag imputation should not generate a flag either
if (feat in exp_imp_cols and coltype != "categorical"
and imp != "zero_noflag"):
assert ("prefix_entity_id_1y_%s_imp" % feat
in df.columns.values)
assert (df["prefix_entity_id_1y_%s_imp" %
feat].isnull().sum() == 0)
else:
# should not generate an imputed column when not needed
assert ("prefix_entity_id_1y_%s_imp" % feat
not in df.columns.values)
def test_imputation_output(feat_list, exp_imp_cols, feat_table):
with testing.postgresql.Postgresql() as psql:
engine = sqlalchemy.create_engine(psql.url())
engine.execute('create table states (entity_id int, as_of_date date)')
for state in states_table:
engine.execute('insert into states values (%s, %s)', state)
feat_sql = '\n'.join(
[', prefix_entity_id_1y_%s_max int' % f for f in feat_list])
engine.execute('''create table prefix_aggregation (
entity_id int
, as_of_date date
%s
)''' % feat_sql)
ins_sql = 'insert into prefix_aggregation values (%s, %s'+\
(', %s' * len(feat_list))+')'
for rec in feat_table:
engine.execute(ins_sql, rec)
for imp in available_imputations.keys():
# skip error imputation
if imp == 'error':
continue
for coltype in ['aggregate', 'categorical']:
# only consider
if not imputation_values[imp][coltype]['avail']:
continue
impargs = imputation_values[imp][coltype]['kwargs']
aggs = [
Aggregate(feat, ['max'], {
'coltype': coltype,
'all': dict(type=imp, **impargs)
}) for feat in feat_list
]
st = SpacetimeAggregation(
aggregates=aggs,
from_obj='prefix_events',
prefix='prefix',
groups=['entity_id'],
intervals=['1y'],
dates=['2016-01-01', '2016-02-03', '2016-03-14'],
state_table='states',
state_group='entity_id',
date_column='as_of_date',
input_min_date='2000-01-01',
output_date_column='as_of_date')
conn = engine.connect()
trans = conn.begin()
# excute query to find columns with null values and create lists of columns
# that do and do not need imputation when creating the imputation table
res = conn.execute(st.find_nulls())
null_counts = list(zip(res.keys(), res.fetchone()))
impute_cols = [col for col, val in null_counts if val > 0]
nonimpute_cols = [col for col, val in null_counts if val == 0]
# sql to drop and create the imputation table
drop_imp = st.get_drop(imputed=True)
create_imp = st.get_impute_create(
impute_cols=impute_cols, nonimpute_cols=nonimpute_cols)
# create the imputation table
conn.execute(drop_imp)
conn.execute(create_imp)
trans.commit()
# check the results
df = pd.read_sql('SELECT * FROM prefix_aggregation_imputed',
engine)
# we should have a record for every entity/date combo
assert df.shape[0] == len(states_table)
for feat in feat_list:
# all of the input columns should be in the result and be null-free
assert 'prefix_entity_id_1y_%s_max' % feat in df.columns.values
assert df['prefix_entity_id_1y_%s_max' %
feat].isnull().sum() == 0
# for non-categoricals, should add an "imputed" column and be non-null
# (categoricals are expected to be handled through the null category)
# zero_noflag imputation should not generate a flag either
if feat in exp_imp_cols and coltype != 'categorical' and imp != 'zero_noflag':
assert 'prefix_entity_id_1y_%s_max_imp' % feat in df.columns.values
assert df['prefix_entity_id_1y_%s_max_imp' %
feat].isnull().sum() == 0
else:
# should not generate an imputed column when not needed
assert 'prefix_entity_id_1y_%s_max_imp' % feat not in df.columns.values
def test_aggregate_when():
agg = Aggregate("1", "count", {})
assert list(map(str, agg.get_columns(when="date < '2012-01-01'"))) == [
"count(1) FILTER (WHERE date < '2012-01-01')"
]
def test_generate_table_tasks(test_engine):
test_engine.execute('create schema features')
aggregations = [
SpacetimeAggregation(
prefix="prefix1",
aggregates=[
Categorical(
col="cat_one",
function="sum",
choices=["good", "bad", "inbetween"],
impute_rules={"coltype": "categorical", "all": {"type": "zero"}},
)
],
groups=["entity_id"],
intervals=["all"],
date_column="knowledge_date",
output_date_column="as_of_date",
dates=["2013-09-30", "2014-09-30"],
state_table="states",
state_group="entity_id",
schema="features",
from_obj="data",
),
SpacetimeAggregation(
prefix="prefix2",
aggregates=[
Aggregate(
quantity="quantity_one",
function="count",
impute_rules={"coltype": "aggregate", "all": {"type": "zero"}},
)
],
groups=["entity_id"],
intervals=["all"],
date_column="knowledge_date",
output_date_column="as_of_date",
dates=["2013-09-30", "2014-09-30"],
state_table="states",
state_group="entity_id",
schema="features",
from_obj="data",
),
]
features_schema_name = "features"
table_tasks = FeatureGenerator(
db_engine=test_engine,
features_schema_name=features_schema_name,
).generate_all_table_tasks(aggregations, task_type="aggregation")
for table_name, task in table_tasks.items():
assert "DROP TABLE" in task["prepare"][0]
assert "CREATE TABLE" in str(task["prepare"][1])
assert "CREATE INDEX" in task["finalize"][0]
assert isinstance(task["inserts"], list)
# build the aggregation tables to check the imputation tasks
FeatureGenerator(
db_engine=test_engine,
features_schema_name=features_schema_name,
).process_table_tasks(table_tasks)
table_tasks = FeatureGenerator(
db_engine=test_engine,
features_schema_name=features_schema_name,
).generate_all_table_tasks(aggregations, task_type="imputation")
for table_name, task in table_tasks.items():
assert "DROP TABLE" in task["prepare"][0]
assert "CREATE TABLE" in str(task["prepare"][1])
assert "CREATE INDEX" in task["finalize"][0]
assert isinstance(task["inserts"], list)
def test_aggregate_when_cast():
agg = Aggregate("", "mode", {}, "x", coltype="SMALLINT")
assert list(map(str, agg.get_columns(when="date < '2012-01-01'"))) == [
"mode() WITHIN GROUP (ORDER BY x) FILTER (WHERE date < '2012-01-01')::SMALLINT"
]