Reputation: 3361
I have a .csv
file with the following example schema:
item, date
10,2015.01.01
2,2015.02.01
3,2015.01.15
4,2015.03.01
5,2015.03.01
20,2015.03.01
11,2015.02.01
23,2015.01.21
13,2015.01.31
I'm looking to bin the items into year/month/days i.e. 2015-01, 2015-02, 2015-03
.
I tried the following line by it doesn't seem to work:
select item by 1 xbar date.month from table
Also tried updating with a new column:
update column: `mm$date from table
It seems that importing the .csv
file casts the fields to a symbols. Do i need to explicitly cast this back to a date type first?
How can i print out a table with values binned by a certain time/date criteria?
Upvotes: 2
Views: 572
Reputation: 296
To apply types when reading in a csv, use 0:
as follows:
q)("ID";enlist ",") 0: `:test.csv
item date
---------------
10 2015.01.01
2 2015.02.01
3 2015.01.15
4 2015.03.01
5 2015.03.01
20 2015.03.01
11 2015.02.01
23 2015.01.21
13 2015.01.31
The left argument is a two item list, the "ID"
specifies the type of each column (integer, date) and the ","
specifies the separator (and the enlist
indicates column headers are present).
Then the data can be bucketed by month:
q)select item by 1 xbar `month$date from ("ID";enlist ",") 0: `:test.csv
date | item
-------| -----------
2015.01| 10 3 23 13i
2015.02| 2 11i
2015.03| 4 5 20i
Alternatively, if you want to have buckets of custom size, you can use bin
with a list of dates as bucket dividers. This will give an integer value indicating which bin each date falls, then you can use a dictionary to name the buckets:
q)b:2015.01.01 2015.02.01 2015.03.01
q)d:(0 1 2)!`month$b
q)select item by d b bin date from ("ID";enlist ",") 0: `:test.csv
date | item
-------| -----------
2015.01| 10 3 23 13i
2015.02| 2 11i
2015.03| 4 5 20i
Note: bin
will assign -1
for values below the first divider. Using binr
will start counting at 0
:
q)b bin 2014.12.31
-1
q)b binr 2014.12.31
0
Upvotes: 4