quantified_features is useful for transforming values of non-number columns to indexed numbers.
Note: The feature is supported from Hivemall v0.4 or later.
desc train;
id int
age int
job string
marital string
education string
default string
balance int
housing string
loan string
contact string
day int
month string
duration int
campaign int
pdays int
previous int
poutcome string
y int
select * from train limit 10;
1 39 blue-collar married secondary no 1756 yes no cellular 3 apr 939 1 -1 0 unknown 1
2 51 entrepreneur married primary no 1443 no no cellular 18 feb 172 10 -1 0 unknown 1
3 36 management single tertiary no 436 no no cellular 13 apr 567 1 595 2 failure 1
4 63 retired married secondary no 474 no no cellular 25 jan 423 1 -1 0 unknown 1
5 31 management single tertiary no 354 no no cellular 30 apr 502 1 9 2 success 1
6 29 blue-collar single secondary no 260 yes no unknown 2 jun 707 14 -1 0 unknown 1
7 37 services married secondary no 52 yes no cellular 6 sep 908 1 185 9 success 1
8 32 technician single secondary no 230 yes no cellular 18 may 442 1 266 8 failure 1
9 31 admin. single secondary no 0 yes no cellular 7 may 895 2 295 2 failure 1
10 32 self-employed single tertiary no 1815 no no telephone 10 aug 235 1 102 2 failure 1
set hivevar:output_row=true;
select quantify(${output_row}, *)
from (
select * from train
order by id asc -- force quantify() runs on a single reducer
) t
limit 10;
1 39 0 0 0 0 1756 0 0 0 3 0 939 1 -1 0 0 1
2 51 1 0 1 0 1443 1 0 0 18 1 172 10 -1 0 0 1
3 36 2 1 2 0 436 1 0 0 13 0 567 1 595 2 1 1
4 63 3 0 0 0 474 1 0 0 25 2 423 1 -1 0 0 1
5 31 2 1 2 0 354 1 0 0 30 0 502 1 9 2 2 1
6 29 0 1 0 0 260 0 0 1 2 3 707 14 -1 0 0 1
7 37 4 0 0 0 52 0 0 0 6 4 908 1 185 9 2 1
8 32 5 1 0 0 230 0 0 0 18 5 442 1 266 8 1 1
9 31 6 1 0 0 0 0 0 0 7 5 895 2 295 2 1 1
10 32 7 1 2 0 1815 1 0 2 10 6 235 1 102 2 1 1
select
quantify(
${output_row}, id, age, job, marital, education, default, balance, housing, loan, contact, day, month, duration, campaign, cast(pdays as string), previous, poutcome, y
) as (id, age, job, marital, education, default, balance, housing, loan, contact, day, month, duration, campaign, pdays, previous, poutcome, y)
from (
select * from train
order by id asc
) t
limit 10;
1 39 0 0 0 0 1756 0 0 0 3 0 939 1 0 0 0 1
2 51 1 0 1 0 1443 1 0 0 18 1 172 10 0 0 0 1
3 36 2 1 2 0 436 1 0 0 13 0 567 1 1 2 1 1
4 63 3 0 0 0 474 1 0 0 25 2 423 1 0 0 0 1
5 31 2 1 2 0 354 1 0 0 30 0 502 1 2 2 2 1
6 29 0 1 0 0 260 0 0 1 2 3 707 14 0 0 0 1
7 37 4 0 0 0 52 0 0 0 6 4 908 1 3 9 2 1
8 32 5 1 0 0 230 0 0 0 18 5 442 1 4 8 1 1
9 31 6 1 0 0 0 0 0 0 7 5 895 2 5 2 1 1
10 32 7 1 2 0 1815 1 0 2 10 6 235 1 6 2 1 1
select
quantified_features(
${output_row}, id, age, job, marital, education, default, balance, housing, loan, contact, day, month, duration, campaign, cast(pdays as string), previous, poutcome, y
) as features
from (
select * from train
order by id asc
) t
limit 10;
[1.0,39.0,0.0,0.0,0.0,0.0,1756.0,0.0,0.0,0.0,3.0,0.0,939.0,1.0,0.0,0.0,0.0,1.0]
[2.0,51.0,1.0,0.0,1.0,0.0,1443.0,1.0,0.0,0.0,18.0,1.0,172.0,10.0,0.0,0.0,0.0,1.0]
[3.0,36.0,2.0,1.0,2.0,0.0,436.0,1.0,0.0,0.0,13.0,0.0,567.0,1.0,1.0,2.0,1.0,1.0]
[4.0,63.0,3.0,0.0,0.0,0.0,474.0,1.0,0.0,0.0,25.0,2.0,423.0,1.0,0.0,0.0,0.0,1.0]
[5.0,31.0,2.0,1.0,2.0,0.0,354.0,1.0,0.0,0.0,30.0,0.0,502.0,1.0,2.0,2.0,2.0,1.0]
[6.0,29.0,0.0,1.0,0.0,0.0,260.0,0.0,0.0,1.0,2.0,3.0,707.0,14.0,0.0,0.0,0.0,1.0]
[7.0,37.0,4.0,0.0,0.0,0.0,52.0,0.0,0.0,0.0,6.0,4.0,908.0,1.0,3.0,9.0,2.0,1.0]
[8.0,32.0,5.0,1.0,0.0,0.0,230.0,0.0,0.0,0.0,18.0,5.0,442.0,1.0,4.0,8.0,1.0,1.0]
[9.0,31.0,6.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,7.0,5.0,895.0,2.0,5.0,2.0,1.0,1.0]
[10.0,32.0,7.0,1.0,2.0,0.0,1815.0,1.0,0.0,2.0,10.0,6.0,235.0,1.0,6.0,2.0,1.0,1.0]
Quantify test dataset
select * from test limit 10;
1 30 management single tertiary no 1028 no no cellular 4 feb 1294 2 -1 0 unknown
2 39 self-employed single tertiary no 426 no no unknown 18 jun 1029 1 -1 0 unknown
3 38 technician single tertiary no -572 yes yes unknown 5 jun 26 24 -1 0 unknown
4 34 technician single secondary no -476 yes no unknown 27 may 92 4 -1 0 unknown
5 37 entrepreneur married primary no 62 no no cellular 31 jul 404 2 -1 0 unknown
6 43 services married primary no 574 yes no cellular 8 may 140 1 -1 0 unknown
7 54 technician married secondary no 324 yes no telephone 13 may 51 1 -1 0 unknown
8 41 blue-collar married secondary no 121 yes no cellular 13 may 16 6 176 5 other
9 52 housemaid married primary no 1466 no yes cellular 20 nov 150 1 -1 0 unknown
10 32 management married secondary no 6217 yes yes cellular 18 nov 486 2 181 2 failure
select
id,
array(age, job, marital, education, default, balance, housing, loan, contact, day, month, duration, campaign, pdays, previous, poutcome) as features
from (
select
quantify(
output_row, id, age, job, marital, education, default, balance, housing, loan, contact, day, month, duration, campaign, if(pdays==-1,0,pdays), previous, poutcome
) as (id, age, job, marital, education, default, balance, housing, loan, contact, day, month, duration, campaign, pdays, previous, poutcome)
from (
select * from (
select
1 as train_first, false as output_row, id, age, job, marital, education, default, balance, housing, loan, contact, day, month, duration, campaign, pdays, previous, poutcome
from
train
union all
select
2 as train_first, true as output_row, id, age, job, marital, education, default, balance, housing, loan, contact, day, month, duration, campaign, pdays, previous, poutcome
from
test
) t0
order by train_first, id asc
) t1
) t2
limit 10;
1 [30,2,1,2,0,1028,1,0,0,4,1,1294,2,0,0,0]
2 [39,7,1,2,0,426,1,0,1,18,3,1029,1,0,0,0]
3 [38,5,1,2,0,-572,0,1,1,5,3,26,24,0,0,0]
4 [34,5,1,0,0,-476,0,0,1,27,5,92,4,0,0,0]
5 [37,1,0,1,0,62,1,0,0,31,8,404,2,0,0,0]
6 [43,4,0,1,0,574,0,0,0,8,5,140,1,0,0,0]
7 [54,5,0,0,0,324,0,0,2,13,5,51,1,0,0,0]
8 [41,0,0,0,0,121,0,0,0,13,5,16,6,176,5,3]
9 [52,8,0,1,0,1466,1,1,0,20,9,150,1,0,0,0]
10 [32,2,0,0,0,6217,0,1,0,18,9,486,2,181,2,1]