CODEWITHSUNDEEP
joinkdb+
g123
g123

Reputation: 1

Is there an easier way of creating this table in kdb?

I want this sort of layout for everyday of the year

time       area   disease   no._of_cases
----------------------------------------
2018.01.01 Spain  Influenza 46          
2018.01.02 Spain  Influenza 46          
2018.01.03 Spain  Influenza 41          
2018.01.01 Spain  Cholera   -3          
2018.01.02 Spain  Cholera   -1          
2018.01.03 Spain  Cholera   -2          
2018.01.01 London Influenza 55          
2018.01.02 London Influenza 51          
2018.01.03 London Influenza 55          
2018.01.01 London Cholera   1           
2018.01.02 London Cholera   2           
2018.01.03 London Cholera   3           
2018.01.01 France Influenza 50          
2018.01.02 France Influenza 50          
2018.01.03 France Influenza 55          
2018.01.01 France Cholera   -1          
2018.01.02 France Cholera   -2          
2018.01.03 France Cholera   3

So I have 2 diseases and they each have different no.of cases for each day for each different area for each month of the year

Below is an example of an area with two diseases and results for the number of cases for two months

Spain - Influenza
No.of cases in JAN:{floor [1339 % 30]+first 1?-3+til 7} each til 30 
No.of cases in FEB:{floor [1261 % 28]+first 1?-3+til 7} each til 28 

Spain - Cholera
No.of cases in JAN:{floor [0 % 30]+first 1?-3+til 7} each til 30;
No.of cases in FEB:{floor [0 % 28]+first 1?-3+til 7} each til 28;

This is an example for the month of JAN

times:2020.01.01 + til 3;
area:`Spain
disease1:`Influenza
disease2:`Cholera
no.of_cases1:{floor [1339 % 30]+first 1?-3+til 7} each til 30; 
no.of_cases2:{floor [0 % 30]+first 1?-3+til 7} each til 30;

area2:`London
no.of_cases3:{floor [1583 % 30]+first 1?-3+til 7} each til 30; 
no.of_cases4:{floor [0 % 30]+first 1?-3+til 7} each til 30;

area3:`France
no.of_cases5:{floor [1583 % 30]+first 1?-3+til 7} each til 30; 
no.of_cases6:{floor [0 % 30]+first 1?-3+til 7} each til 30;


a:([]time:times;area:(count [times])# area;disease:count[times]#disease1;no._of_cases:count[times]# no.of_cases1)
b:([]time:times;area:(count [times])# area;disease:count[times]#disease2;no._of_cases:count[times]# no.of_cases2)
c:([]time:times;area:(count [times])# area2;disease:count[times]#disease1;no._of_cases:count[times]# no.of_cases3)
d:([]time:times;area:(count [times])# area2;disease:count[times]#disease2;no._of_cases:count[times]# no.of_cases4)
e:([]time:times;area:(count [times])# area3;disease:count[times]#disease1;no._of_cases:count[times]# no.of_cases5)
f:([]time:times;area:(count [times])# area3;disease:count[times]#disease2;no._of_cases:count[times]# no.of_cases6)

t:a,b,c,d,e,f

Is there an easier way of creating this table for everyday of the year?

This is an example of the table I want to convert to kdb.

             area        |Spain         |London              |France  
____________________________________________________________________-   
disease     |Month/year |2017  2018 ... | 2017   2018  ...   |2017 2018 ...
----------------------------------------------------------------------
Influenza   |Jan        |1     2        |                    |
            |Feb        |2     3        |                    |
            |....       |...
            |
--------------------------------------------------------------------            
Cholera     |Jan        |2      9
            |Feb        |1      2
            |...

Upvotes: 0

Views: 115

Answers (1)

terrylynch
terrylynch

Reputation: 13572

If you're talking purely about how to fabricate the table, you can make use of cross:

q)update cases:count[i]?100 from cross/[(([]area:`Spain`London`France);([]disease:`Influenza`Cholera);([]time:2018.01.01+til 3))]
area   disease   time       cases
---------------------------------
Spain  Influenza 2018.01.01 2
Spain  Influenza 2018.01.02 39
Spain  Influenza 2018.01.03 64
Spain  Cholera   2018.01.01 49
...

However your cases column might need a little more logic involved in it, depending on your use-case

Upvotes: 1

Related Questions