Re: [R] data manipulation and summaries with few million rows
Factors are you friend here: > myData mydate gender mygroup id mygrp.f 1 2012-03-25 F A 1 1 2 2005-05-23 F B 2 2 3 2005-09-08 F B 2 2 4 2005-12-07 F B 2 2 5 2006-02-26 F C 2 3 6 2006-05-13 F C 2 3 7 2006-09-01 F C 2 3 8 2006-12-12 F D 2 4 9 2006-02-19 F D 2 4 10 2006-05-03 F D 2 4 11 2006-04-23 F D 2 4 12 2007-12-08 F D 2 4 13 2011-03-19 F D 2 4 14 2007-12-20 M A 3 1 15 2008-06-15 M A 3 1 16 2008-12-16 M A 3 1 17 2009-06-07 M B 3 2 18 2009-10-09 M B 3 2 19 2010-01-28 M B 3 2 20 2007-06-05 M A 4 1 > # change 'mygroup' to a factor so you can use 'diff' to count the changes > myData$mygrp.f <- as.integer(factor(myData$mygroup)) > # count the changes for each 'id' > changes <- tapply(myData$mygrp.f, myData$id, function(x){ + sum(diff(x) != 0) + }) > > > changes 1 2 3 4 0 2 1 0 > On Wed, Aug 24, 2011 at 12:48 PM, Juliet Hannah wrote: > I have a data set with about 6 million rows and 50 columns. It is a > mixture of dates, factors, and numerics. > > What I am trying to accomplish can be seen with the following > simplified data, which is given as dput output below. > >> head(myData) > mydate gender mygroup id > 1 2012-03-25 F A 1 > 2 2005-05-23 F B 2 > 3 2005-09-08 F B 2 > 4 2005-12-07 F B 2 > 5 2006-02-26 F C 2 > 6 2006-05-13 F C 2 > > For each id, I want to count the number of changes of the variable > 'mygroup' that occur. For example, id=1 has 0 changes because it is > observed only once. id=2 has 2 changes (B to C, and C to D). I also > need to calculate the total observation time for each id using the > variable mydate. In the end, I am trying to have a new data set in > which each row has an id, days observed, number of changes, and > gender. > > I made some simple summaries using data.table and plyr, but I'm stuck > on this reformatting. > > Thanks for your help. > > myData <- structure(list(mydate = c("2012-03-25", "2005-05-23", "2005-09-08", > "2005-12-07", "2006-02-26", "2006-05-13", "2006-09-01", "2006-12-12", > "2006-02-19", "2006-05-03", "2006-04-23", "2007-12-08", "2011-03-19", > "2007-12-20", "2008-06-15", "2008-12-16", "2009-06-07", "2009-10-09", > "2010-01-28", "2007-06-05"), gender = c("F", "F", "F", "F", "F", > "F", "F", "F", "F", "F", "F", "F", "F", "M", "M", "M", "M", "M", > "M", "M"), mygroup = c("A", "B", "B", "B", "C", "C", "C", "D", > "D", "D", "D", "D", "D", "A", "A", "A", "B", "B", "B", "A"), > id = c(1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, > 3L, 3L, 3L, 3L, 3L, 3L, 4L)), .Names = c("mydate", "gender", > "mygroup", "id"), class = "data.frame", row.names = c(NA, -20L > )) > >> sessionInfo() > R version 2.13.1 (2011-07-08) > Platform: x86_64-unknown-linux-gnu (64-bit) > > locale: > [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C > [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 > [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 > [7] LC_PAPER=en_US.UTF-8 LC_NAME=C > [9] LC_ADDRESS=C LC_TELEPHONE=C > [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C > > attached base packages: > [1] stats graphics grDevices utils datasets methods base > > __ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > -- Jim Holtman Data Munger Guru What is the problem that you are trying to solve? __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] data manipulation and summaries with few million rows
Thanks Dennis! I'll check this out. Just to clarify, I need the total number of switches/changes regardless of if that state had occurred in the past. So A-A-B-A, would have 2 changes: A to B and B to A. Thanks again. On Wed, Aug 24, 2011 at 1:28 PM, Dennis Murphy wrote: > Hi Juliet: > > Here's a Q & D solution: > > # (1) plyr >> f <- function(d) length(unique(d$mygroup)) - 1 >> ddply(myData, .(id), f) > id V1 > 1 1 0 > 2 2 2 > 3 3 1 > 4 4 0 > > # (2) data.table > > myDT <- data.table(myData, key = 'id') > myDT[, list(nswitch = length(unique(mygroup)) - 1), by = 'id'] > > If one can switch back and forth between levels more than once, then > the above is clearly not appropriate. A more robust method would be to > employ rle() [run length encoding]: > > g <- function(d) length(rle(d$mygroup)$lengths) - 1 > ddply(myData, .(id), g) # gives the same answer as above > myDT[, list(nswitch = length(rle(mygroup)$lengths) - 1), by = 'id'] # ditto > > > HTH, > Dennis > > On Wed, Aug 24, 2011 at 9:48 AM, Juliet Hannah > wrote: >> I have a data set with about 6 million rows and 50 columns. It is a >> mixture of dates, factors, and numerics. >> >> What I am trying to accomplish can be seen with the following >> simplified data, which is given as dput output below. >> >>> head(myData) >> mydate gender mygroup id >> 1 2012-03-25 F A 1 >> 2 2005-05-23 F B 2 >> 3 2005-09-08 F B 2 >> 4 2005-12-07 F B 2 >> 5 2006-02-26 F C 2 >> 6 2006-05-13 F C 2 >> >> For each id, I want to count the number of changes of the variable >> 'mygroup' that occur. For example, id=1 has 0 changes because it is >> observed only once. id=2 has 2 changes (B to C, and C to D). I also >> need to calculate the total observation time for each id using the >> variable mydate. In the end, I am trying to have a new data set in >> which each row has an id, days observed, number of changes, and >> gender. >> >> I made some simple summaries using data.table and plyr, but I'm stuck >> on this reformatting. >> >> Thanks for your help. >> >> myData <- structure(list(mydate = c("2012-03-25", "2005-05-23", "2005-09-08", >> "2005-12-07", "2006-02-26", "2006-05-13", "2006-09-01", "2006-12-12", >> "2006-02-19", "2006-05-03", "2006-04-23", "2007-12-08", "2011-03-19", >> "2007-12-20", "2008-06-15", "2008-12-16", "2009-06-07", "2009-10-09", >> "2010-01-28", "2007-06-05"), gender = c("F", "F", "F", "F", "F", >> "F", "F", "F", "F", "F", "F", "F", "F", "M", "M", "M", "M", "M", >> "M", "M"), mygroup = c("A", "B", "B", "B", "C", "C", "C", "D", >> "D", "D", "D", "D", "D", "A", "A", "A", "B", "B", "B", "A"), >> id = c(1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, >> 3L, 3L, 3L, 3L, 3L, 3L, 4L)), .Names = c("mydate", "gender", >> "mygroup", "id"), class = "data.frame", row.names = c(NA, -20L >> )) >> >>> sessionInfo() >> R version 2.13.1 (2011-07-08) >> Platform: x86_64-unknown-linux-gnu (64-bit) >> >> locale: >> [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C >> [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 >> [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 >> [7] LC_PAPER=en_US.UTF-8 LC_NAME=C >> [9] LC_ADDRESS=C LC_TELEPHONE=C >> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C >> >> attached base packages: >> [1] stats graphics grDevices utils datasets methods base >> >> __ >> R-help@r-project.org mailing list >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >> and provide commented, minimal, self-contained, reproducible code. >> > __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] data manipulation and summaries with few million rows
Hi Juliet: Here's a Q & D solution: # (1) plyr > f <- function(d) length(unique(d$mygroup)) - 1 > ddply(myData, .(id), f) id V1 1 1 0 2 2 2 3 3 1 4 4 0 # (2) data.table myDT <- data.table(myData, key = 'id') myDT[, list(nswitch = length(unique(mygroup)) - 1), by = 'id'] If one can switch back and forth between levels more than once, then the above is clearly not appropriate. A more robust method would be to employ rle() [run length encoding]: g <- function(d) length(rle(d$mygroup)$lengths) - 1 ddply(myData, .(id), g)# gives the same answer as above myDT[, list(nswitch = length(rle(mygroup)$lengths) - 1), by = 'id'] # ditto HTH, Dennis On Wed, Aug 24, 2011 at 9:48 AM, Juliet Hannah wrote: > I have a data set with about 6 million rows and 50 columns. It is a > mixture of dates, factors, and numerics. > > What I am trying to accomplish can be seen with the following > simplified data, which is given as dput output below. > >> head(myData) > mydate gender mygroup id > 1 2012-03-25 F A 1 > 2 2005-05-23 F B 2 > 3 2005-09-08 F B 2 > 4 2005-12-07 F B 2 > 5 2006-02-26 F C 2 > 6 2006-05-13 F C 2 > > For each id, I want to count the number of changes of the variable > 'mygroup' that occur. For example, id=1 has 0 changes because it is > observed only once. id=2 has 2 changes (B to C, and C to D). I also > need to calculate the total observation time for each id using the > variable mydate. In the end, I am trying to have a new data set in > which each row has an id, days observed, number of changes, and > gender. > > I made some simple summaries using data.table and plyr, but I'm stuck > on this reformatting. > > Thanks for your help. > > myData <- structure(list(mydate = c("2012-03-25", "2005-05-23", "2005-09-08", > "2005-12-07", "2006-02-26", "2006-05-13", "2006-09-01", "2006-12-12", > "2006-02-19", "2006-05-03", "2006-04-23", "2007-12-08", "2011-03-19", > "2007-12-20", "2008-06-15", "2008-12-16", "2009-06-07", "2009-10-09", > "2010-01-28", "2007-06-05"), gender = c("F", "F", "F", "F", "F", > "F", "F", "F", "F", "F", "F", "F", "F", "M", "M", "M", "M", "M", > "M", "M"), mygroup = c("A", "B", "B", "B", "C", "C", "C", "D", > "D", "D", "D", "D", "D", "A", "A", "A", "B", "B", "B", "A"), > id = c(1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, > 3L, 3L, 3L, 3L, 3L, 3L, 4L)), .Names = c("mydate", "gender", > "mygroup", "id"), class = "data.frame", row.names = c(NA, -20L > )) > >> sessionInfo() > R version 2.13.1 (2011-07-08) > Platform: x86_64-unknown-linux-gnu (64-bit) > > locale: > [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C > [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 > [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 > [7] LC_PAPER=en_US.UTF-8 LC_NAME=C > [9] LC_ADDRESS=C LC_TELEPHONE=C > [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C > > attached base packages: > [1] stats graphics grDevices utils datasets methods base > > __ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
[R] data manipulation and summaries with few million rows
I have a data set with about 6 million rows and 50 columns. It is a mixture of dates, factors, and numerics. What I am trying to accomplish can be seen with the following simplified data, which is given as dput output below. > head(myData) mydate gender mygroup id 1 2012-03-25 F A 1 2 2005-05-23 F B 2 3 2005-09-08 F B 2 4 2005-12-07 F B 2 5 2006-02-26 F C 2 6 2006-05-13 F C 2 For each id, I want to count the number of changes of the variable 'mygroup' that occur. For example, id=1 has 0 changes because it is observed only once. id=2 has 2 changes (B to C, and C to D). I also need to calculate the total observation time for each id using the variable mydate. In the end, I am trying to have a new data set in which each row has an id, days observed, number of changes, and gender. I made some simple summaries using data.table and plyr, but I'm stuck on this reformatting. Thanks for your help. myData <- structure(list(mydate = c("2012-03-25", "2005-05-23", "2005-09-08", "2005-12-07", "2006-02-26", "2006-05-13", "2006-09-01", "2006-12-12", "2006-02-19", "2006-05-03", "2006-04-23", "2007-12-08", "2011-03-19", "2007-12-20", "2008-06-15", "2008-12-16", "2009-06-07", "2009-10-09", "2010-01-28", "2007-06-05"), gender = c("F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "M", "M", "M", "M", "M", "M", "M"), mygroup = c("A", "B", "B", "B", "C", "C", "C", "D", "D", "D", "D", "D", "D", "A", "A", "A", "B", "B", "B", "A"), id = c(1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 4L)), .Names = c("mydate", "gender", "mygroup", "id"), class = "data.frame", row.names = c(NA, -20L )) > sessionInfo() R version 2.13.1 (2011-07-08) Platform: x86_64-unknown-linux-gnu (64-bit) locale: [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C [3] LC_TIME=en_US.UTF-8LC_COLLATE=en_US.UTF-8 [5] LC_MONETARY=C LC_MESSAGES=en_US.UTF-8 [7] LC_PAPER=en_US.UTF-8 LC_NAME=C [9] LC_ADDRESS=C LC_TELEPHONE=C [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C attached base packages: [1] stats graphics grDevices utils datasets methods base __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.