Count 1s in binary variables

Question

My data looks something like this

Colour Q1 Q2 Q3
yellow  1  0  1 
green   0  0  1
red     1  1  1
black   0  1  0
green   1  0  1
white   0  0  1
yellow  1  1  1

and I'd like a table that looks like this so I can plot some bar charts in ggplot2 where the x-axis would have 3 quarters and each quarter would have the counts of each colour. I tried table() (and others that have been suggested here), but it gave me TRUE and FALSE, which only makes it harder since I don't know how to deal with that in order to plot bar graph using ggplot2.

Colour Q1 Q2 Q3
yellow  2  1  2
red     1  1  1
green   1  0  2
white   0  0  1
black   0  1  0

You question has two unrelated parts: do you want to count variables or plot using `ggplot2`? — pogibas, Aug 27 '18 at 11:05
You can try this solution: https://stackoverflow.com/questions/28090119/summing-all-columns-by-group Or this one https://stackoverflow.com/questions/29462651/sum-columns-by-group-in-a-matrix — pogibas, Aug 27 '18 at 11:11
I downvoted because this question consists of 2 questions that have EACH been answered numerous times here on SO. Lack of effort. — Andre Elrico, Aug 27 '18 at 11:16
@PoGibas oh wow. I wasn't thinking about summing at all. Thank you! — pineapple, Aug 27 '18 at 11:44
@AndreElrico, I also expected to find tons of duplicates of this question on SO. To my surprise, I was not able to find an *exact* duplicate. Perhaps, some one else has a good duplicate for this? — Uwe, Sep 03 '18 at 09:14

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

I have searched SO but I have not found an exact duplicate of this question.

The OP has requested:

... so I can plot some bar charts in ggplot2 where the x-axis would have 3 quarters and each quarter would have the counts of each colour.

For this, aggregation is not the crucial point here (ggplot2 has functions to deal with different statistics) but reshaping the data in order to form an x-axis: The separate columns Q1, Q2, and Q3 need to be turned into one column of quarterly data with an additional Quarter column which will become the x-axis.

For reshaping from wide to long format we can use

long <- tidyr::gather(raw_data, "Quarter", "Count", -Colour)

or

long <- data.table::melt(raw_data, "Colour", , "Quarter", "Count")

(just to mention a few options).

Both return a new dataset in long format:

long

   Colour Quarter Count
1  yellow      Q1     1
2   green      Q1     0
3     red      Q1     1
4   black      Q1     0
5   green      Q1     1
6   white      Q1     0
7  yellow      Q1     1
8  yellow      Q2     0
9   green      Q2     0
10    red      Q2     1
11  black      Q2     1
12  green      Q2     0
13  white      Q2     0
14 yellow      Q2     1
15 yellow      Q3     1
16  green      Q3     1
17    red      Q3     1
18  black      Q3     0
19  green      Q3     1
20  white      Q3     1
21 yellow      Q3     1

Now, we can tell ggplot()

where to place the bars along the x-axis (Quarter),
which colour to use for filling the bars (Colour),
and the height of the bars (Count).

by

library(ggplot2)
ggplot(long) + 
  aes(Quarter, Count, fill = Colour) +
  geom_col() + 
  scale_fill_identity(guide = "legend")

Data

raw_data <- data.table::fread("
Colour Q1 Q2 Q3
yellow  1  0  1 
green   0  0  1
red     1  1  1
black   0  1  0
green   1  0  1
white   0  0  1
yellow  1  1  1")

Count 1s in binary variables

1 Answers1

Data