# Casting multidimensional data in R into a data frame

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. - 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 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.

-
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