CODEWITHSUNDEEP
rcount
Jenny Pham
Jenny Pham

Reputation: 181

Extract frequency of appeared values in R

I have a table as follow:

dt<-data.frame(Date=c(1,1,2,2,3),Zone1=c(NA,5,6,7,NA),Zone2=c(NA,5,6,7,NA),Zone3=c(NA,6,NA,NA,NA))
  Date Zone1 Zone2 Zone3
1    1    NA    NA    NA
2    1     5     5     6
3    2     6     6    NA
4    2     7     7    NA
5    3    NA    NA    NA

I want to know the frequency of appeared numbers (5,6,7) and correspdonding date like this:

 Date "5" "6" "7"
    1  2  1  0
    2  0  2  2
    3  0  0  0

Can you suggest me a fastest way to have the output like that?

Upvotes: 2

Views: 209

Answers (2)

akrun
akrun

Reputation: 887291

Using data.table

library(data.table)
dcast(melt(setDT(dt), id.var = 'Date'), Date ~ value, 
    length, drop = FALSE)[, .(Date, `5`, `6`, `7`)]
#   Date 5 6 7
#1:    1 2 1 0
#2:    2 0 2 2
#3:    3 0 0 0

Or with base R using table

table(dt$Date[col(dt[-1])], unlist(dt[-1]), useNA = 'always')

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 389055

Using dplyr and tidyr, we can gather the data into long format, count Date and Zone columns and spread the data to wide format.

library(dplyr)
library(tidyr)

dt %>%
  gather(key, value, -Date, na.rm = TRUE) %>%
  count(Date, value) %>%
  spread(value, n, fill = 0)

#   Date   `5`   `6`   `7`
#  <dbl> <dbl> <dbl> <dbl>
#1     1     2     1     0
#2     2     0     2     2

Upvotes: 2

Related Questions