Credit the Austin (Texas) American Statesman with assuming the most disagreeable, but perhaps necessary, task of recording and expounding instances of child abuse perpetrated in the state – in this case, abuse taken to lethal extremes.
The paper’s Child abuse and neglect fatality database maintains data about 779 such instances, and makes them available in spreadsheet form as well:
(You’ll need to auto-fit the C column.)
Because the above-referenced web page features an array of graphical synopses of the abuse data:
We could properly ask if a spreadsheet-driven consideration could abet the information already put into place above. Perhaps it could.
Note the uppermost graphic, a gender-coded block of icons of the victims. Looking past the pink-blue gender associations, any click on an icon triggers a caption naming the victim, his/her age, and cause of death. The icons are not rigorously ordered chronologically, and I am not sure why. Moreover, and perhaps curiously, the graphic does not supplement its content with a simple numeric count of victims by gender; and while a scan of the icons inclines the view toward a male predominance, precision can be served via this simple pivot table:
Rows: Gender
Values: Gender (count; the data are textual)
Gender (again, % of Column Total, and turn off Grand Total)
I get:
I for one am surprised by the disparity; the statistical reality of why boys account for 60% of the reported victims poses a real and important question, though the finding would perhaps need to be cross-checked by data from other studies compiled for other regions. Could it be that boys are seen as somehow sturdier and more accustomed to roughhousing treatment, and hence putatively better able to weather abusive assaults? I don’t know, but again, the gender question is worth asking.
Indeed, if one reconstructs the pivot table:
Columns: Gender
Rows: Cause of Death
Values: Gender (count)
One sees in excerpt:
We learn here that the boy-girl differential persists steadily across abuse categories.
Calculating the average age of victims could be understood as a grim but significant task, one complicated by the structure of the worksheet. Columns E presents age data variously denoted tragically by month, year, and weeks categories in F; and as such we need to unify the parameters. Perhaps the simplest means of reconciliation is to pry open a column between F and G, and enter in what is now G2:
=IF(F2=”Months”,E2/12,IF(F2=”weeks”,E2/52,E2))
The nestedIF expression identifies the time categories stored in the F column, and calibrates these into fractions of years, or whole years. Once we copy the formula down G we can execute a Copy > Paste Values into E, delete the temporary G column, and at the same time delete the Age Type field, containing as it does the now-inaccurate variable age categories. We could then in the interests of clarity retitle the field in E Age in Years.
We need at the same time to appreciate the aggregate understatement of ages the data convey. Because ages in years data were clearly rounded off, we cannot know if a three-year-old victim was in actuality three years and eleven months, for example. An important measure of precision could have been introduced with a date of birth column, and one assumes those data are available.
In any event, we can proceed to group the victim age, perhaps in bins of one years:
Rows: Age in Years (grouped by a value of 1)
Values: Age in Years (count)
Age (again, by % of Column Total)
I get:
We see that more than half the victims were two years’ old or less, qualified by the caution entered above. A first, if distressingly obvious, interpretation would associate vulnerability negatively with age: the younger the child, the more susceptible to deadly abuse.
Another point, apropos the Fault assignment field: the pertinent Statesman graphic – a pie chart captioned Paramour at fault- implicates a relationship partner (Paramour) in 137 of the 779 deaths recorded. That total, which can be swiftly realized by turning on a standard filter and simply entering Paramour in the vacant search field – indeed counts 137 such instances. But you’ll observe that many of these simultaneously arraign a parent; enter that term in the search field and you’ll turn up 581 records (remember that the filter will dredge any record that features the search term anywhere within a cell). Search again for Relative, for example, and 85 more cases populate the filter. In other words, a totalling of all perpetrator types will exceed the 779 records – even as the actual, lower total of is 779 too many.
spreadsheetjournalism 
Leave a Reply