Take the 2-minute tour ×
Cross Validated is a question and answer site for people interested in statistics, machine learning, data analysis, data mining, and data visualization. It's 100% free, no registration required.

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:

    $Female
    x
                             Burglary                 Criminal Damage and Arson 
                          0.004950495                               0.017326733 
                     Driving Offences                                    Murder 
                          0.371287129                               0.000000000
    $Male
    x
                             Burglary                 Criminal Damage and Arson 
                          0.013001083                               0.058504875 
                     Driving Offences                                    Murder 
                          0.303358613                               0.000000000

    $`Not Stated`
    x
                             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:

iw=read.csv("~/data/recordlevel.csv")
iwp=tapply(iw$Offence_type,iw$AGE,function(x){prop.table(table(x))})

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.

share|improve this question
    
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. –  Chase Nov 29 '11 at 23:10
add comment

1 Answer

up vote 3 down vote accepted

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:

library(plyr)
library(reshape2)
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.

share|improve this answer
    
That's wonderful, thanks. Spot on. I can see I need to get my head round the melt function and plyr routines ... –  psychemedia Nov 30 '11 at 20:10
    
@psychemedia - definitely worth the time investment in my opinion. Hadley has put together some nice work on both packages here and here –  Chase Nov 30 '11 at 20:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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