[datatable-help] Data table syntax
David Winsemius
dwinsemius at comcast.net
Mon Sep 6 00:56:20 CEST 2010
I went back and reviewed the merge.data.frame method and then
(re-)reviewed the data.table documents. I was disappointed to find the
there were relatively few worked examples of data.table merges, but
finally got something that seems to make sense:
dftxt <- as.data.frame(dtxt)
# The dataframe method
> merge(dftxt[dftxt$YEAR==2008, c(1,2,4)], dftxt[dftxt$YEAR==2009,
c(1,2,4)], by="STUDENT_ID")
STUDENT_ID SCHOOL_NUMBER.x SCORE.x SCHOOL_NUMBER.y SCORE.y
1 1 100 39 200 48
2 2 100 64 200 73
3 3 100 35 200 35
4 4 100 52 200 61
5 5 100 51 200 58
6 6 300 45 400 55
7 7 300 69 400 77
8 8 300 47 400 47
9 9 300 57 400 58
10 10 300 47 400 53
#The datatable method:
> setkey(dtxt) <- "STUDENT_ID" #as far as I can tell this is needed
to repalce the by= argument in merge.data.frame()
#( I tried several failed efforts at incorporating the key= statement
within the data.table calls.)
> merge(dtxt[YEAR==2008, ], dtxt[YEAR==2009, ])
STUDENT_ID SCHOOL_NUMBER YEAR SCORE SCHOOL_NUMBER.1 YEAR.1
SCORE.1
[1,] 1 100 2008 39 200 2009
48
[2,] 2 100 2008 64 200 2009
73
[3,] 3 100 2008 35 200 2009
35
[4,] 4 100 2008 52 200 2009
61
[5,] 5 100 2008 51 200 2009
58
[6,] 6 300 2008 45 400 2009
55
[7,] 7 300 2008 69 400 2009
77
[8,] 8 300 2008 47 400 2009
47
[9,] 9 300 2008 57 400 2009
58
[10,] 10 300 2008 47 400 2009 53
> mtxt <- merge(dtxt[YEAR==2008, ], dtxt[YEAR==2009, ])
> mtxt[, mean(SCORE) , by=list("2009_SCH"=SCHOOL_NUMBER.1)]
X2009_SCH V1
[1,] 200 48.2
[2,] 400 53.0
On Sep 5, 2010, at 4:43 PM, David Winsemius wrote:
>
> On Sep 5, 2010, at 3:02 PM, Damian Betebenner wrote:
>
>> Hi David,
>>
>> Thanks for the quick and thoughtful reply.
>>
>> Sorry for not being clearer.
>>
>> There are two schools that the students attended in 2009 (200 and
>> 400). I'd like to break on those, and calculate the mean for all
>> the students in those two schools but for their 2008 scores.
>>
>> Thus, the output would have 2 rows:
>>
>> YEAR, 2009_SCHOOL_NUMBER, 2008_SCORE_MEAN
>>
>> 2009 200 54.4
>> 2009 400 53
>
> I dont see what sort of linkage you have between the 2008 and 2009
> school-numbers but see if this satisfies:
>
> > dtxt[YEAR==2008 & STUDENT_ID %in% dtxt[YEAR==2009, STUDENT_ID],
> mean(SCORE), by=SCHOOL_NUMBER]
> SCHOOL_NUMBER V1
> [1,] 100 48.2
> [2,] 300 53.0
>
>>
>> Thanks for considering this,
>>
>> Damian
>>
>>
>> Damian Betebenner
>> Center for Assessment
>> PO Box 351
>> Dover, NH 03821-0351
>>
>> Phone (office): (603) 516-7900
>> Phone (cell): (857) 234-2474
>> Fax: (603) 516-7910
>>
>> dbetebenner at nciea.org
>> www.nciea.org
>>
>>
>>
>>
>> -----Original Message-----
>> From: David Winsemius [mailto:dwinsemius at comcast.net]
>> Sent: Sunday, September 05, 2010 1:03 PM
>> To: David Winsemius
>> Cc: Damian Betebenner; datatable-help at lists.r-forge.r-project.org
>> Subject: Re: [datatable-help] Data table syntax
>>
>>
>> On Sep 5, 2010, at 12:43 PM, David Winsemius wrote:
>>
>>>
>>> On Sep 5, 2010, at 11:38 AM, Damian Betebenner wrote:
>>>
>>>> Thanks for the invaluable help on my previous questions. The speed
>>>> up in create summary tables has been immense and I'm enthused about
>>>> all the possibilities going forward.
>>>>
>>>> I'm currently stuck in trying to put together syntax for a "long"
>>>> for table. In the example below, each case is a unique Student by
>>>> Year combination. What I'm trying to do is take
>>>> such a table, aggregate on the student's current year (i.e., 2009
>>>> in this data) SCHOOL_NUMBER, and calculate their mean score in the
>>>> previous year (i.e., 2008 in this data).
>>>>
>>>> If the file were "wide", with each case representing a unique
>>>> student with separate variables for the year, then it would be easy
>>>> to break on the 2009 SCHOOL_NUMBER and take the
>>>> mean of the 2008 SCORE.
>>
>> But there is only one 2008 SCORE for each student???
>>
>>>>
>>>> Is conversion of long to wide necessary to do this?
>>>
>>> Probably not. Are you familiar with the "ave" function in base R?
>>
>> I am having some difficulty understanding the structure of the
>> desired
>> output. I initially thought it might be something like:
>> rd.txt <-
>> function(txt, header=TRUE, ...) {
>> rd <- read.table(textConnection(txt), header=header, ...)
>> closeAllConnections()
>> rd }
>> txt <- rd.txt("STUDENT_ID SCHOOL_NUMBER YEAR SCORE
>> 1 100 2008 39
>> 1 200 2009 48
>> 2 100 2008 64
>> 2 200 2009 73
>> 3 100 2008 35
>> 3 200 2009 35
>> 4 100 2008 52
>> 4 200 2009 61
>> 5 100 2008 51
>> 5 200 2009 58
>> 6 300 2008 45
>> 6 400 2009 55
>> 7 300 2008 69
>> 7 400 2009 77
>> 8 300 2008 47
>> 8 400 2009 47
>> 9 300 2008 57
>> 9 400 2009 58
>> 10 300 2008 47
>> 10 400 2009 53")
>> dtxt <- data.table(txt)
>>
>>> dtxt$avScr <- dtxt[ , ave(SCORE, list(STUDENT_ID))] # returns a
>> vector as long as its input
>>> dtxt
>>
>> But now I am wondering if you wanted:
>>
>>> dtxt[ , tapply(SCORE, list(STUDENT_ID), mean)] # returns vector
>> only as long as product of category levels.
>> 1 2 3 4 5 6 7 8 9 10
>> 43.5 68.5 35.0 56.5 54.5 50.0 73.0 47.0 57.5 50.0
>>
>>>
>>>>
>>>>
>>>> STUDENT_ID SCHOOL_NUMBER YEAR SCORE
>>>> [1,] 1 100 2008 39
>>>> [2,] 1 200 2009 48
>>>> [3,] 2 100 2008 64
>>>> [4,] 2 200 2009 73
>>>> [5,] 3 100 2008 35
>>>> [6,] 3 200 2009 35
>>>> [7,] 4 100 2008 52
>>>> [8,] 4 200 2009 61
>>>> [9,] 5 100 2008 51
>>>> [10,] 5 200 2009 58
>>>> [11,] 6 300 2008 45
>>>> [12,] 6 400 2009 55
>>>> [13,] 7 300 2008 69
>>>> [14,] 7 400 2009 77
>>>> [15,] 8 300 2008 47
>>>> [16,] 8 400 2009 47
>>>> [17,] 9 300 2008 57
>>>> [18,] 9 400 2009 58
>>>> [19,] 10 300 2008 47
>>>> [20,] 10 400 2009 53
>>>>
>>>>
>>>> Thanks,
>>>>
>>>> Damian
>>>
>>
>>
>> David Winsemius, MD
>> West Hartford, CT
>>
>
> David Winsemius, MD
> West Hartford, CT
>
> _______________________________________________
> datatable-help mailing list
> datatable-help at lists.r-forge.r-project.org
> https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help
David Winsemius, MD
West Hartford, CT
More information about the datatable-help
mailing list