Count categories in a column and unique categories in another column

[dsgeek]

##I am trying to create frequency table based on the count of categorical variables

name <- c('A','A','B','C','C','C')
SysVSMan <- c('M','M','M','M','M','M')
Obj <- c('123','123','125','125','125','127')

d <-data.frame(name, SysVSMan, Obj)

d 

# I want to count for each A, for each B, and for each C, the count of M and the unique  number of obj
#So table should look something like this:
  
  
name1 <- c('A','B','C')
SysVSMan1 <- c(2,1,3)
Obj1 <- c(1, 1, 2)

d1 <- data.frame(name1, SysVSMan1, Obj1)
d1

[FJCC]

You can use summarize() from the dplyr package.

name <- c('A','A','B','C','C','C')
SysVSMan <- c('M','M','M','M','M','M')
Obj <- c('123','123','125','125','125','127')

d <-data.frame(name, SysVSMan, Obj)

d 
#>   name SysVSMan Obj
#> 1    A        M 123
#> 2    A        M 123
#> 3    B        M 125
#> 4    C        M 125
#> 5    C        M 125
#> 6    C        M 127
suppressPackageStartupMessages(library(dplyr))

d1 <- d %>% group_by(name) %>% 
  summarize(sysBSMan1 = sum(SysVSMan == "M"), Obj1 = length(unique(Obj)))

d1
#> # A tibble: 3 x 3
#>   name  sysBSMan1  Obj1
#>   <fct>     <int> <int>
#> 1 A             2     1
#> 2 B             1     1
#> 3 C             3     2

^{Created on 2020-01-22 by the reprex package (v0.3.0)}

[technocrat]

suppressPackageStartupMessages(library(dplyr)) 
name <- c('A','A','B','C','C','C')
SysVSMan <- c('M','M','M','M','M','M')
Obj <- c('123','123','125','125','125','127')

d <-data.frame(name, SysVSMan, Obj)

# I want to count for each A, for each B, and for each C, the count of M and the unique  number of obj

d %>% unique()
#>   name SysVSMan Obj
#> 1    A        M 123
#> 3    B        M 125
#> 4    C        M 125
#> 6    C        M 127
d %>% group_by(name) %>% count()
#> # A tibble: 3 x 2
#> # Groups:   name [3]
#>   name      n
#>   <fct> <int>
#> 1 A         2
#> 2 B         1
#> 3 C         3
length(d$SysVSMan)
#> [1] 6

^{Created on 2020-01-22 by the reprex package (v0.3.0)}

[dsgeek]

Works great Thank you!

[dsgeek]

Hi technocrat,

I can follow with your steps except for the last step.

length(d$SysVSMan)

gives one value -- There should be a value for each name where the count of SysVSMan shows in one column and the unique count of Obj shows in a column next to it.

I am wondering if we can convert the last piece of code that does that maybe something else.

love the nick technocrat

[technocrat]

Hi @dsgeek, I was being overliteral, I guess, thinking you want to know just the count of M.

Feeling fuzzy, do you mean

suppressPackageStartupMessages(library(dplyr)) 
name <- c('A','A','B','C','C','C')
SysVSMan <- c('M','M','M','M','M','M')
Obj <- c('123','123','125','125','125','127')
d <-data.frame(name, SysVSMan, Obj)
d %>% group_by(name, SysVSMan) %>% count()
#> # A tibble: 3 x 3
#> # Groups:   name, SysVSMan [3]
#>   name  SysVSMan     n
#>   <fct> <fct>    <int>
#> 1 A     M            2
#> 2 B     M            1
#> 3 C     M            3

^{Created on 2020-01-22 by the reprex package (v0.3.0)}

(Glad you like the nick; I've been using it over 25 years)

[dsgeek]

Yes that works perfectly Thanks again, very helpful!