R - dplyr crosstable of melted paired data

Question

I am wondering how to perform a crosstable using dplyr with melted data. My data looks like this.

         idmen sexe         dip14_rec
1  0110008218    1               Uni
2  0110008218    2 Primary-Secondary
3  0110010366    1               Uni
4  0110010366    2               Uni
5  0110011567    1 Primary-Secondary
6  0110011567    2 Primary-Secondary
7  0110012163    2 Primary-Secondary
8  0110012163    1 Primary-Secondary
9  0110016580    2               Uni
10 0110016580    1        No Diploma

What I want is the cross-table of dipl14_rec by idmen.

The only way I found to this this is

dta1 = dta %>% filter(sexe == 1) 
dta2 = dta %>% filter(sexe == 2) 

dta12 = merge(dta1, dta2, by = 'idmen') 
table( Men = dta12$dip14_rec.x, Women = dta12$dip14_rec.y )

Which gives me the output I want:

#                 Women
# Men                  No Diploma Primary-Secondary Uni
#    No Diploma                 0                 0   1
#    Primary-Secondary          0                 2   0
#    Uni                        0                 1   1

Is there a more direct way to do this using dplyr synthax ?

Thanks

dta = structure(c("0110008218", "0110008218", "0110010366", "0110010366", 
"0110011567", "0110011567", "0110012163", "0110012163", "0110016580", 
"0110016580", "1", "2", "1", "2", "1", "2", "2", "1", "2", "1", 
"Uni", "Primary-Secondary", "Uni", "Uni", "Primary-Secondary", 
"Primary-Secondary", "Primary-Secondary", "Primary-Secondary", 
"Uni", "No Diploma"), .Dim = c(10L, 3L), .Dimnames = list(NULL, 
 c("idmen", "sexe", "dip14_rec")))

yes my dput didnot work to correclty on a df. do not know why — giac, Dec 14 '15 at 15:48

David Arenburg · Accepted Answer · 2015-12-14T16:08:15.903

You could simply spread the data and the run the table function while specifying dnn

library(dplyr)
library(tidyr)
dta %>%
  spread(sexe, dip14_rec) %>%
  select(-idmen) %>%
  table(., dnn = c("Men", "Women"))
#                 Women
# Men                  No Diploma Primary-Secondary Uni
#    No Diploma                 0                 0   1
#    Primary-Secondary          0                 2   0
#    Uni                        0                 1   1

Or similarly with data.table

library(data.table) # V 1.9.6+
dcast(setDT(dta), idmen ~ sexe)[, table(Men = `1`, Women = `2`)]
# Using 'dip14_rec' as value column. Use 'value.var' to override
#                    Women
# Men                 No Diploma Primary-Secondary Uni
#   No Diploma                 0                 0   1
#   Primary-Secondary          0                 2   0
#   Uni                        0                 1   1

Data

dta <- structure(list(idmen = c(110008218L, 110008218L, 110010366L, 
110010366L, 110011567L, 110011567L, 110012163L, 110012163L, 110016580L, 
110016580L), sexe = c(1L, 2L, 1L, 2L, 1L, 2L, 2L, 1L, 2L, 1L), 
    dip14_rec = structure(c(3L, 2L, 3L, 3L, 2L, 2L, 2L, 2L, 3L, 
    1L), .Label = c("No Diploma", "Primary-Secondary", "Uni"), class = "factor")), .Names = c("idmen", 
"sexe", "dip14_rec"), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10"))

See my edit with `dnn` to match your desired output exactly. — David Arenburg, Dec 14 '15 at 16:01

R - dplyr crosstable of melted paired data

1 Answers1