CODEWITHSUNDEEP
r
nogbad
nogbad

Reputation: 445

R - count values in multiple columns by group

I have some data:

df <- data.frame(v1 = c('lapse','gone','active','lapse','lapse','active','active','active','gone'),
                 v2 = c('gone','gone','active','lapse','active','lapse','active','lapse','lapse'),
                 v3 = c('gone','gone','gone','gone','active','active','lapse','lapse','lapse'),
                 v4 = c('active','gone','lapse','gone','lapse','gone','lapse','active','lapse'))

I need to count the observations in each column grouped by 'lapse','gone' and 'active'. I need it to look like this:

   group     v1     v2     v3     v4
1  lapse     3      4      3      4
2  gone      2      2      4      3
3  active    4      3      2      2

Any help is greatly appreciated!

Upvotes: 0

Views: 195

Answers (3)

Ronak Shah
Ronak Shah

Reputation: 388797

Using dplyr and tidyr, we can gather the data into long format, count the frequency and then spread the data to wide format.

library(dplyr)
library(tidyr)

df %>%
  gather(key, value) %>%
  count(key, value) %>%
  spread(key, n, fill = 0)

# A tibble: 3 x 5
#  value     v1    v2    v3    v4
#  <chr>  <int> <int> <int> <int>
#1 active     4     3     2     2
#2 gone       2     2     4     3
#3 lapse      3     4     3     4

With the new version of tidyr, gather and spread have been replaced by pivot_longer and pivot_wider respectively. So the above can be done as

df %>%
  pivot_longer(cols = names(df)) %>%
  count(name, value) %>%
  pivot_wider(names_from = name, values_from = n, values_fill = list(n = 0))

Upvotes: 3

akrun
akrun

Reputation: 886938

We can use melt/dcast

library(reshape2)
dcast(melt(as.matrix(df)), value ~ Var2)
#   value v1 v2 v3 v4
#1 active  4  3  2  2
#2   gone  2  2  4  3
#3  lapse  3  4  3  4

Or an option in base R

table(names(df)[col(df)], unlist(df))

Upvotes: 0

lroha
lroha

Reputation: 34291

You can also try:

table(stack(df))

        ind
values   v1 v2 v3 v4
  active  4  3  2  2
  gone    2  2  4  3
  lapse   3  4  3  4

Upvotes: 2

Related Questions