Skip to content

Counting with multiple criteria using data table

4 messages · Ek Esawi, Bert Gunter, David Winsemius +1 more

Ek Esawi
# 
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
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
Month N

1: April 1

2:   May 1
Month N

1: August 1

2:  April 1

3:    May 1
Month     N

1: August     1

2:  April     1

3:    May     1
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
Bert Gunter
# 
Have you gone through any R tutorials? If not, why not? If so, maybe
you need to spend some more time with them.

It looks like you want us to do your work for you. We don't do this.
See (and follow) the posting guide below for what we might do (we're
volunteers, so no guarantees).

Cheers,
Bert


Bert Gunter

"The trouble with having an open mind is that people keep coming along
and sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )
On Wed, Jun 21, 2017 at 2:50 PM, Ek Esawi <esawiek at gmail.com> wrote:
David Winsemius
# 
You should have offered the output of:

 dput(Test)
I count 4 data.tables and a total of 11 items so why only 3 columns and 9 items?

Were you tabulating colors by month?
(Color=="red"&Grade=="F") |
(Color=="orange"&Grade=="F")|
(Color=="orange"&Grade=="F")|
(Color=="red"&Grade=="F") ,        table(Month, Color)]
        Color
Month    green orange red
  April      1      1   1
  August     0      1   1
  June       0      0   1
  May        1      1   0
Rhelp is plain-text. Do read the Posting Guide:

Another possibility:

 Test[(Color=="green"&Grade=="A") |    These are the conditions separated by logical OR's in the first argument to `[data.table`
      (Color=="red"&Grade=="F") |
      (Color=="orange"&Grade=="F")|
      (Color=="orange"&Grade=="F")|
      (Color=="red"&Grade=="F") ,          table(Month, Grade)]


        Grade
Month    A F
  April  1 2
  August 0 2
  June   0 1
  May    1 1
David Winsemius
Alameda, CA, USA
Jeff Newmiller
# 
To be fair, the OP did provide brief snippets of data.table usage below the data dump indicating some level of effort, but posted it all in HTML (what you see we do not see), did not make the example reproducible (dput is great, and library calls really clear things up [1][2][3]), and this looks suspiciously like homework  (not on topic here, see the Posting Guide).

[1] http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example

[2] http://adv-r.had.co.nz/Reproducibility.html

[3] https://cran.r-project.org/web/packages/reprex/index.html