Counting with multiple criteria using data table
I have a data.table which is shown below. I want to count combinations of columns on i and count on j with by. A few examples are given below the table. I want to: all months to show on the output including those that they have zero value I want the three statements combined in on if possible so the output will be one data table; that is the outputs are next to each other as manually illustrated on the last part (desired output). Thanks--EK
Test
Color Grade Value Month Day 1: yellow A 20 May 1 2: green B 25 June 2 3: green A 10 April 3 4: black A 17 August 3 5: red C 5 May 5 6: orange D 0 June 13 7: orange E 12 April 5 8: orange F 11 August 8 9: orange F 99 April 23 10: orange F 70 May 7 11: black A 77 June 11 12: green B 87 August 33 13: black A 79 April 9 14: green A 68 May 14 15: black C 90 June 31 16: green D 79 August 11 17: black E 101 April 17 18: red F 90 June 21 19: red F 112 August 13 20: red F 101 April 20
Test[Color=="green"&Grade=="A", .N, by=Month]
Month N 1: April 1 2: May 1
Test[Color=="orange"&Grade=="F", .N, by=Month]
Month N 1: August 1 2: April 1 3: May 1
Test[Color=="orange"&Grade=="F", .N, by=Month]
Month N 1: August 1 2: April 1 3: May 1
Test[Color=="red"&Grade=="F", .N, by=Month]
Month N
1: June 1
2: August 1
3: April 1
Desired output
N1 N2 N3
April 1 1 1
May 1 1 1
June 0 0 0
August 0 1 1