Summary multiple columns with dplyr - categorical version

Question

Following this question and this one, I wondered what was the best option to summarise categorical variables in one dataset.

I have a dataset such as

# A tibble: 10 <U+00D7> 4
    empstat_couple     nssec7_couple3  nchild07 age_couple
              <chr>             <fctr>    <fctr>      <dbl>
1         Neo-Trad   Lower Managerial    1child         39
2         Neo-Trad  Higher Managerial    1child         31
3         Neo-Trad Manual and Routine    1child         33
4             Trad  Higher Managerial    1child         43

The 3 first variables are categorical (character or factor) and the last numerical.

What I would like is something like (output)

                  var n   p
1:           Neo-Trad 6 0.6
2:    OtherArrangment 2 0.2
3:               Trad 2 0.2
4:  Higher Managerial 4 0.4
5:   Lower Managerial 5 0.5
6: Manual and Routine 1 0.1
7:             1child 9 0.9
8:          2children 1 0.1

Well for the numerical variable, I am unsure how to add it meaningfully to the summary.

I guess the most basic way to go is

library(dplyr) 
library(data.table) 

a = count(dt, empstat_couple) %>% mutate(p = n / sum(n))
b = count(dt, nssec7_couple3) %>% mutate(p = n / sum(n))
c = count(dt, nchild07) %>% mutate(p = n / sum(n))

rbindlist(list(a,b,c))

I wondered if a summarise_each solution existed ?

This doesn't work

dt %>% summarise_each(funs(count))

Using apply I could come up with this

apply(dt, 2, as.data.frame(table)) %>% rbindlist()

But it's not great.

Any suggestions ?

data

dt = structure(list(empstat_couple = c("Neo-Trad", "Neo-Trad", "Neo-Trad", 
"Trad", "OtherArrangment", "Neo-Trad", "Trad", "OtherArrangment", 
"Neo-Trad", "Neo-Trad"), nssec7_couple3 = structure(c(2L, 1L, 
4L, 1L, 2L, 2L, 1L, 2L, 1L, 2L), .Label = c("Higher Managerial", 
"Lower Managerial", "Intermediate", "Manual and Routine"), class = "factor"), 
nchild07 = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
1L), .Label = c("1child", "2children", ">2children"), class = "factor"), 
age_couple = c(39, 31, 33, 43, 32, 28, 28, 40, 33, 26), hldid = 1:10), .Names = c("empstat_couple", 
"nssec7_couple3", "nchild07", "age_couple", "hldid"), row.names = c(NA, 
-10L), class = "data.frame")

Regarding the calculation of 'p', is it correct? – akrun Jan 04 '17 at 10:01 — akrun, Jan 04 '17 at 10:01
@akrun I think so. It is simply the proportion. – giac Jan 04 '17 at 10:04 — giac, Jan 04 '17 at 10:04

akrun · Accepted Answer · 2017-01-04T10:10:34.810

5

We can melt with data.table and get the .N and proportion

library(data.table)
unique(melt(setDT(dt), id.var = "age_couple")[,  n := .N , value], 
       by = c("variable", "value", "n"))[,  p := n/sum(n), variable 
        ][, c("age_couple", "variable" ) := NULL][]

Or using dplyr/tidyr

library(dplyr)
library(tidyr)
gather(dt, var1, var, -age_couple) %>%
           group_by(var) %>%
           mutate(n = n()) %>%
           select(-age_couple) %>%
           unique() %>% 
           group_by(var1) %>%
           mutate(p= n/sum(n)) %>%
           ungroup() %>%
           select(-var1)

edited Jan 04 '17 at 10:10

answered Jan 04 '17 at 09:58

akrun

874,273
37
540
662

very neat. I like the `data.table` solution. I can use my couple personal identifier as `id.var`. Works well. thanks – giac Jan 04 '17 at 10:06
however, your `p` doesn't give me the correct results I think. – giac Jan 04 '17 at 10:08
@giacomoV I changed the first solution, Please check if that is what you wanted, I will change the second also – akrun Jan 04 '17 at 10:10
for the sake of clarity I added the `personal identifier`. But the first solution works fine with `hldid` now. – giac Jan 04 '17 at 10:15

Summary multiple columns with dplyr - categorical version

1 Answers1