Carrein
Carrein

Reputation: 3361

Bin date values by month from imported CSV in KDB?

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

Answers (1)

Sam McMillen
Sam McMillen

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

Related Questions