CODEWITHSUNDEEP
rdplyr
fafa-gu
fafa-gu

Reputation: 13

Counting “1” in multiple columns in a data-frame

Given the following data frame:

fruit <- c("Orange", "Apple", "Apple", "Orange", "Banana", "Orange", "Banana", "Pear", "Banana", "Pear", "Pear", "Apple")
col2 <- c("0", "0", "1", "0", "0", "0", "1", "0", "0", "0", "0", "1")
col3 <- c("1", "1", "0", "0", "0", "1", "1", "0", "0", "0", "0", "0")
col4 <- c("0", "1", "0", "1", "1", "1", "0", "0", "0", "1", "1", "0")
df <- data.frame(fruit, col2, col3, col4)

    fruit col2 col3 col4
1  Orange    0    1    0
2   Apple    0    1    1
3   Apple    1    0    0
4  Orange    0    0    1
5  Banana    0    0    1
6  Orange    0    1    1
7  Banana    1    1    0
8    Pear    0    0    0
9  Banana    0    0    0
10   Pear    0    0    1
11   Pear    0    0    1
12  Apple    1    0    0

I want to count the frequencies of "1" in each column and show them in a table as follows:

    fruit col2 col3 col4
1  Orange   0    2    2
2  Apple    2    1    1
3  Banana   1    1    1
4  Pear     0    0    1

I tried to use dplyr to get the result, but didn't able to do it in multiple columns. This is the code that I used:

df %>%
  group_by(fruit) %>%
  summarise(Count = n()) %>%
  group_by_all %>%
  spread(fruit, Count, fill = 0)

I am a beginner in R, can anyone help, please?

Upvotes: 1

Views: 45

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388982

Using base R aggregate :

aggregate(.~fruit, df, function(x) sum(x == '1'))

#   fruit col2 col3 col4
#1  Apple    2    1    1
#2 Banana    1    1    1
#3 Orange    0    2    2
#4   Pear    0    0    2

Upvotes: 0

akrun
akrun

Reputation: 887088

We can use summarise with across to loop over the columns after grouping by 'fruit' and get the sum of logical vector

library(dplyr)
df %>%
   group_by(fruit) %>% 
   summarise(across(everything(), ~ sum(. == '1')))

-output

# A tibble: 4 x 4
#  fruit   col2  col3  col4
#* <chr>  <int> <int> <int>
#1 Apple      2     1     1
#2 Banana     1     1     1
#3 Orange     0     2     2
#4 Pear       0     0     2

Upvotes: 2

Related Questions