R help - Mar 2013 - How to list the all products' information of the latest month?

Hi,

I have data frame like this:

Product     Price    Year_Month  PE
A                 100        201012         -2
A                 98           201101        -3
A                 97           201102        -2.5
B                 110         201101        -1
B                 100         201102        -2
B                  90          201103        -4


How can I achieve the following result using R:
Product     Price    Year_Month  PE
A                 97           201102        -2.5
B                  90          201103        -4

in other words, list the all products' information of the latest month?

Thanks for your help.

Kind regards,
Lingyi




 		 	   		  
	[[alternative HTML version deleted]]

Try this:
> x <- read.table(text = "Product     Price    Year_Month  PE
+ A                 100        201012         -2
+ A                 98           201101        -3
+ A                 97           201102        -2.5
+ B                 110         201101        -1
+ B                 100         201102        -2
+ B                  90          201103        -4", header = TRUE, as.is
TRUE)
> do.call(rbind
+     , lapply(split(x, x$Product), tail, 1)
+     )
  Product Price Year_Month   PE
A       A    97     201102 -2.5
B       B    90     201103 -4.0
>

On Fri, Mar 15, 2013 at 5:56 AM, Tammy Ma <metal_licaling@live.com> wrote:
> Hi,
>
> I have data frame like this:
>
> Product     Price    Year_Month  PE
> A                 100        201012         -2
> A                 98           201101        -3
> A                 97           201102        -2.5
> B                 110         201101        -1
> B                 100         201102        -2
> B                  90          201103        -4
>
>
> How can I achieve the following result using R:
> Product     Price    Year_Month  PE
> A                 97           201102        -2.5
> B                  90          201103        -4
>
> in other words, list the all products' information of the latest month?
>
> Thanks for your help.
>
> Kind regards,
> Lingyi
>
>
>
>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> 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?
Tell me what you want to do, not how you want to do it.

	[[alternative HTML version deleted]]

On 15-03-2013, at 10:56, Tammy Ma <metal_licaling at live.com> wrote:
> Hi,
> 
> I have data frame like this:
> 
> Product     Price    Year_Month  PE
> A                 100        201012         -2
> A                 98           201101        -3
> A                 97           201102        -2.5
> B                 110         201101        -1
> B                 100         201102        -2
> B                  90          201103        -4
> 
> 
> How can I achieve the following result using R:
> Product     Price    Year_Month  PE
> A                 97           201102        -2.5
> B                  90          201103        -4
> 
Another option is to use aggregate like this

aggregate(x, by=list(x$Product), FUN=function(z) tail(z,1))[,-1]

or

aggregate(. ~ Product, data=x, FUN=function(z) tail(z,1))


Berend

dat1<- read.table(text="
Product??? Price??? Year_Month? PE
A??????????????? 100??????? 201012??????? -2
A??????????????? 98????????? 201101??????? -3
A??????????????? 97????????? 201102??????? -2.5
B??????????????? 110??????? 201101??????? -1
B??????????????? 100??????? 201102??????? -2
B????????????????? 90????????? 201103??????? -4
",sep="",header=TRUE,stringsAsFactors=FALSE)
dat1[as.logical(with(dat1,ave(Year_Month,Product,FUN=function(x) x==max(x)))),]
#? Product Price Year_Month?? PE
#3?????? A??? 97???? 201102 -2.5
#6?????? B??? 90???? 201103 -4.0
A.K.




----- Original Message -----
From: Tammy Ma <metal_licaling at live.com>
To: "r-help at r-project.org" <r-help at r-project.org>
Cc: 
Sent: Friday, March 15, 2013 5:56 AM
Subject: [R] How to list the all products' information of the latest month?

Hi,

I have data frame like this:

Product? ?  Price? ? Year_Month? PE
A? ? ? ? ? ? ? ?  100? ? ? ? 201012? ? ? ?  -2
A? ? ? ? ? ? ? ?  98? ? ? ? ?  201101? ? ? ? -3
A? ? ? ? ? ? ? ?  97? ? ? ? ?  201102? ? ? ? -2.5
B? ? ? ? ? ? ? ?  110? ? ? ?  201101? ? ? ? -1
B? ? ? ? ? ? ? ?  100? ? ? ?  201102? ? ? ? -2
B? ? ? ? ? ? ? ? ? 90? ? ? ? ? 201103? ? ? ? -4


How can I achieve the following result using R:
Product? ?  Price? ? Year_Month? PE
A? ? ? ? ? ? ? ?  97? ? ? ? ?  201102? ? ? ? -2.5
B? ? ? ? ? ? ? ? ? 90? ? ? ? ? 201103? ? ? ? -4

in other words, list the all products' information of the latest month?

Thanks for your help.

Kind regards,
Lingyi




??? ???  ??? ?  ??? ??? ? 
??? [[alternative HTML version deleted]]

______________________________________________
R-help at 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.