I'm trying to do some percentage based comparisons across different groups in a criminal sentencing data set (http://dl.dropbox.com/u/1156404/wightCrimRecords.csv)

I have a data in an array of the form:

                             Burglary                 Criminal Damage and Arson 
                          0.004950495                               0.017326733 
                     Driving Offences                                    Murder 
                          0.371287129                               0.000000000
                             Burglary                 Criminal Damage and Arson 
                          0.013001083                               0.058504875 
                     Driving Offences                                    Murder 
                          0.303358613                               0.000000000

    $`Not Stated`
                             Burglary                 Criminal Damage and Arson 
                            0.0000000                                 0.0000000 
                     Driving Offences                                    Murder 
                            0.1111111                                 0.0000000 

This was derived from the original data as follows:


What I would like to do is generate a single data frame that contains a gender column, a frequency column, and rows corresponding to Burglary, Murder etc.

I can extract a single datatable from the multidimensional array, eg using:

iwpF =data.frame(iwp['Female'])

which generates a separate row for each offence and columns referring to offence type and frequency, but can't see how to generate a single datatable.

PS I was also wondering whether it's possible to pull out even more structured data, that for example counts the percentages of offence type sex and age group, so for example I could lookup up what percentage of convictions for males in the 35+ age range are related to murder.

  • $\begingroup$ Is there a discrepancy in your code between AGE and sex? It looks like you are showing tapply() function by AGE, but are referring to it by gender. $\endgroup$
    – Chase
    Nov 29, 2011 at 23:10

1 Answer 1


I'm not sure I followed the PS part of your question, but maybe this will get you on the right path. The trick is to use melt() to get the data into long format, then use ddply() to group by:

iw <- read.csv("http://dl.dropbox.com/u/1156404/wightCrimRecords.csv")
iw.m <- melt(iw, id.vars = "sex", measure.vars = "Offence_type")
ddply(iw.m, "sex", function(x) as.data.frame(prop.table(table(x$value))))

Gives us:

          sex                                      Var1        Freq
1      Female                                  Burglary 0.004950495
2      Female                 Criminal Damage and Arson 0.017326733
3      Female                          Driving Offences 0.371287129
50      Other                           Supply of drugs 0.000000000
51      Other                             Vehicle Crime 0.000000000
52      Other                             Violent Crime 0.000000000

EDIT - after reading the PS again, I think this is what you had in mind:

iw.m <- melt(iw, id.vars = c("sex", "AGE"), measure.vars = "Offence_type")
ddply(iw.m, c("sex", "AGE"), function(x) as.data.frame(prop.table(table(x$value))))

           sex   AGE                                      Var1        Freq
1       Female 18-24                                  Burglary 0.011764706
2       Female 18-24                 Criminal Damage and Arson 0.047058824
3       Female 18-24                          Driving Offences 0.188235294

You can obviously continue to add ID variables which then get passed into plyr to group on to any level of detail that is sufficient for your purposes.

  • $\begingroup$ That's wonderful, thanks. Spot on. I can see I need to get my head round the melt function and plyr routines ... $\endgroup$ Nov 30, 2011 at 20:10
  • $\begingroup$ @psychemedia - definitely worth the time investment in my opinion. Hadley has put together some nice work on both packages here and here $\endgroup$
    – Chase
    Nov 30, 2011 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.