Hi,
There is a potential gotcha with the approach of using head(..., 1) in each of
the solutions that Arun has below, which is the assumption that the data is
sorted, as is the case in the example data. It seems reasonable to consider
that the real data at hand may not be entered in order or presorted.
If the data is not sorted (switching the order of the two K2 related entries):
Period <- c(1, 2, 3, 1, 2, 3, 4, 2, 1)
Forecast <- c(99, 103, 128, 63, 69, 72, 75, 201, 207)
SKU <- c("A1","A1","A1","X4","X4","X4","X4","K2","K2")
PeriodSKUForecast <- data.frame(Period, SKU, Forecast)
> PeriodSKUForecast
Period SKU Forecast
1 1 A1 99
2 2 A1 103
3 3 A1 128
4 1 X4 63
5 2 X4 69
6 3 X4 72
7 4 X4 75
8 2 K2 201
9 1 K2 207
> with(PeriodSKUForecast,tapply(Forecast,SKU,head,1))
A1 K2 X4
99 201 63
> aggregate(Forecast~SKU,data=PeriodSKUForecast,head,1)
SKU Forecast
1 A1 99
2 K2 201
3 X4 63
Note that the wrong value for K2 is returned.
You would either have to pre-sort the data frame before using these approaches:
NewDF <- PeriodSKUForecast[with(PeriodSKUForecast, order(SKU, Period)), ]
> NewDF
Period SKU Forecast
1 1 A1 99
2 2 A1 103
3 3 A1 128
9 1 K2 207
8 2 K2 201
4 1 X4 63
5 2 X4 69
6 3 X4 72
7 4 X4 75
> with(NewDF,tapply(Forecast,SKU,head,1))
A1 K2 X4
99 207 63
Or consider an approach that does not depend upon the sort order, but which
subsets based upon the minimum value of Period for each SKU:
do.call(rbind, lapply(split(PeriodSKUForecast, PeriodSKUForecast$SKU),
function(x) x[which.min(x$Period), ]))
Period SKU Forecast
A1 1 A1 99
K2 1 K2 207
X4 1 X4 63
or remove the Period column if you don't want it:
> do.call(rbind, lapply(split(PeriodSKUForecast, PeriodSKUForecast$SKU),
function(x) x[which.min(x$Period), -1]))
SKU Forecast
A1 A1 99
K2 K2 207
X4 X4 63
Regards,
Marc Schwartz
On Mar 15, 2013, at 12:37 PM, arun <smartpink...@yahoo.com> wrote:
> Hi,
> Try:
> data.frame(Forecast=with(PeriodSKUForecast,tapply(Forecast,SKU,head,1)))
> # Forecast
> #A1 99
> #K2 207
> #X4 63
>
> #or
> aggregate(Forecast~SKU,data=PeriodSKUForecast,head,1)
> # SKU Forecast
> #1 A1 99
> #2 K2 207
> #3 X4 63
>
> #or
> library(plyr)
> ddply(PeriodSKUForecast,.(SKU),summarise, Forecast=head(Forecast,1))
> # SKU Forecast
> #1 A1 99
> #2 K2 207
> #3 X4 63
> A.K.
>
>
>
>
> ----- Original Message -----
> From: Barry King <barry.k...@qlx.com>
> To: r-help@r-project.org
> Cc:
> Sent: Friday, March 15, 2013 1:30 PM
> Subject: [R] Help finding first value in a BY group
>
> I have a large Excel file with SKU numbers (stock keeping units) and
> forecasts which can be mimicked with the following:
>
> Period <- c(1, 2, 3, 1, 2, 3, 4, 1, 2)
> SKU <- c("A1","A1","A1","X4","X4","X4","X4","K2","K2")
> Forecast <- c(99, 103, 128, 63, 69, 72, 75, 207, 201)
> PeriodSKUForecast <- data.frame(Period, SKU, Forecast)
> PeriodSKUForecast
>
> Period SKU Forecast
> 1 1 A1 99
> 2 2 A1 103
> 3 3 A1 128
> 4 1 X4 63
> 5 2 X4 69
> 6 3 X4 72
> 7 4 X4 75
> 8 1 K2 207
> 9 2 K2 201
>
> I need to create a matrix with only the first forecast for each SKU:
>
> A1 99
> X4 63
> K2 207
>
> The Period for the first forecast will always be the minimum value
> for an SKU.
>
> Can anyone suggest how I might accomplish this?
>
> Thank you,
______________________________________________
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.
