Extract from data.frame

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Extract from data.frame

Nico Gutierrez
Hi All,

I need to do the following operation from data.frame:

df <- data.frame(Year = c("2001", "2002", "2003", "2004", "2005", "2006",
"2007"), Amount = c(150, 120, 175, 160, 120, 105, 135))
df[which.max(df$Amount),]  #to extract row with max Amount.

Now I need to do 3 years average around the max Amount value (ie:
mean(120,175,160))

Thanks!
N

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Extract from data.frame

R. Mark Sharp
Nico,

I expect there are many better ways to do this, but this does work:
max_row <- (1:nrow(df))[which.max(df$Amount)]
mean(df$Amount[max_row + c(-1, 0, 1)])

> max_row <- (1:nrow(df))[which.max(df$Amount)]
> mean(df$Amount[max_row + c(-1, 0, 1)])
[1] 151.6667

R. Mark Sharp, Ph.D.
Director of Primate Records Database
Southwest National Primate Research Center
Texas Biomedical Research Institute
P.O. Box 760549
San Antonio, TX 78245-0549
Telephone: (210)258-9476
e-mail: [hidden email]




> On Sep 21, 2015, at 9:52 AM, Nico Gutierrez <[hidden email]> wrote:
>
> Hi All,
>
> I need to do the following operation from data.frame:
>
> df <- data.frame(Year = c("2001", "2002", "2003", "2004", "2005", "2006",
> "2007"), Amount = c(150, 120, 175, 160, 120, 105, 135))
> df[which.max(df$Amount),]  #to extract row with max Amount.
>
> Now I need to do 3 years average around the max Amount value (ie:
> mean(120,175,160))
>
> Thanks!
> N
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Extract from data.frame

John McKown
In reply to this post by Nico Gutierrez
On Mon, Sep 21, 2015 at 9:52 AM, Nico Gutierrez <[hidden email]>
wrote:

> Hi All,
>
> I need to do the following operation from data.frame:
>
> df <- data.frame(Year = c("2001", "2002", "2003", "2004", "2005", "2006",
> "2007"), Amount = c(150, 120, 175, 160, 120, 105, 135))
> df[which.max(df$Amount),]  #to extract row with max Amount.
>
> Now I need to do 3 years average around the max Amount value (ie:
> mean(120,175,160))
>
> Thanks!
> N
>
>
​The simplistic answer is something like:

df <- structure(list(Year = structure(1:7, .Label = c("2001", "2002",
"2003", "2004", "2005", "2006", "2007"), class = "factor"), Amount = c(150,
120, 175, 160, 120, 105, 135)), .Names = c("Year", "Amount"), row.names =
c(NA,
-7L), class = "data.frame");
wdf <- which.max(df$Amount);
adf3 <- mean(df$Amount[adf-1:adr+1]);

But that ignores the boundry condition where the maximum is at either end.
What do you want to do in that case?​


--

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Extract from data.frame

Bert Gunter-2
In reply to this post by R. Mark Sharp
Note the following problems:

1. " max_row <- (1:nrow(df))[which.max(df$Amount)]"

This is a bit silly.

max_row <- which.max(df$Amount)


will do. See ?which.max


2. What happens if the max is the first or last row? e.g.

> dat <- data.frame(a=runif(5),b=1:5)
> max_row<- which.max(dat$b)
> mean(dat[max_row+c(-1,0,1),"b"]) ## 2-d indexing
[1] NA


Cheers,
Bert Gunter

"Data is not information. Information is not knowledge. And knowledge
is certainly not wisdom."
   -- Clifford Stoll


On Mon, Sep 21, 2015 at 10:51 AM, Mark Sharp <[hidden email]> wrote:

> Nico,
>
> I expect there are many better ways to do this, but this does work:
> max_row <- (1:nrow(df))[which.max(df$Amount)]
> mean(df$Amount[max_row + c(-1, 0, 1)])
>
>> max_row <- (1:nrow(df))[which.max(df$Amount)]
>> mean(df$Amount[max_row + c(-1, 0, 1)])
> [1] 151.6667
>
> R. Mark Sharp, Ph.D.
> Director of Primate Records Database
> Southwest National Primate Research Center
> Texas Biomedical Research Institute
> P.O. Box 760549
> San Antonio, TX 78245-0549
> Telephone: (210)258-9476
> e-mail: [hidden email]
>
>
>
>
>> On Sep 21, 2015, at 9:52 AM, Nico Gutierrez <[hidden email]> wrote:
>>
>> Hi All,
>>
>> I need to do the following operation from data.frame:
>>
>> df <- data.frame(Year = c("2001", "2002", "2003", "2004", "2005", "2006",
>> "2007"), Amount = c(150, 120, 175, 160, 120, 105, 135))
>> df[which.max(df$Amount),]  #to extract row with max Amount.
>>
>> Now I need to do 3 years average around the max Amount value (ie:
>> mean(120,175,160))
>>
>> Thanks!
>> N
>>
>>       [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> 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.
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Extract from data.frame

Bert Gunter-2
In reply to this post by John McKown
No.


On Mon, Sep 21, 2015 at 10:58 AM, John McKown
<[hidden email]> wrote:

> On Mon, Sep 21, 2015 at 9:52 AM, Nico Gutierrez <[hidden email]>
> wrote:
>
>> Hi All,
>>
>> I need to do the following operation from data.frame:
>>
>> df <- data.frame(Year = c("2001", "2002", "2003", "2004", "2005", "2006",
>> "2007"), Amount = c(150, 120, 175, 160, 120, 105, 135))
>> df[which.max(df$Amount),]  #to extract row with max Amount.
>>
>> Now I need to do 3 years average around the max Amount value (ie:
>> mean(120,175,160))
>>
>> Thanks!
>> N
>>
>>
> The simplistic answer is something like:
>
> df <- structure(list(Year = structure(1:7, .Label = c("2001", "2002",
> "2003", "2004", "2005", "2006", "2007"), class = "factor"), Amount = c(150,
> 120, 175, 160, 120, 105, 135)), .Names = c("Year", "Amount"), row.names =
> c(NA,
> -7L), class = "data.frame");
> wdf <- which.max(df$Amount);
> adf3 <- mean(df$Amount[adf-1:adr+1]);

Typos?!
But it won't work anyway. See ?Syntax for operator precedence and

Example:

> a <- 1:5
> mid <- 3
> a[mid-1:mid+1]
[1] 3 2 1
> a[(mid-1):(mid+1)]
[1] 2 3 4

Cheers,
Bert


>
> But that ignores the boundry condition where the maximum is at either end.
> What do you want to do in that case?
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Extract from data.frame

Frank Schwidom
In reply to this post by Nico Gutierrez

year <- df$Year[ which.max( df$Amount)]
df[ df$Year %in% (as.numeric( as.character( year)) + -1:1), ]
  Year Amount
2 2002    120
3 2003    175
4 2004    160


On Mon, Sep 21, 2015 at 04:52:46PM +0200, Nico Gutierrez wrote:

> Hi All,
>
> I need to do the following operation from data.frame:
>
> df <- data.frame(Year = c("2001", "2002", "2003", "2004", "2005", "2006",
> "2007"), Amount = c(150, 120, 175, 160, 120, 105, 135))
> df[which.max(df$Amount),]  #to extract row with max Amount.
>
> Now I need to do 3 years average around the max Amount value (ie:
> mean(120,175,160))
>
> Thanks!
> N
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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.
>

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Extract from data.frame

Frank Schwidom

better ( if year is an vector of more than 1 element):

df[ df$Year %in% outer(as.numeric( as.character( year)), -1:1, FUN='+'), ]
  Year Amount
2 2002    120
3 2003    175
4 2004    160


On Mon, Sep 21, 2015 at 10:49:34PM +0200, Frank Schwidom wrote:

>
> year <- df$Year[ which.max( df$Amount)]
> df[ df$Year %in% (as.numeric( as.character( year)) + -1:1), ]
>   Year Amount
> 2 2002    120
> 3 2003    175
> 4 2004    160
>
>
> On Mon, Sep 21, 2015 at 04:52:46PM +0200, Nico Gutierrez wrote:
> > Hi All,
> >
> > I need to do the following operation from data.frame:
> >
> > df <- data.frame(Year = c("2001", "2002", "2003", "2004", "2005", "2006",
> > "2007"), Amount = c(150, 120, 175, 160, 120, 105, 135))
> > df[which.max(df$Amount),]  #to extract row with max Amount.
> >
> > Now I need to do 3 years average around the max Amount value (ie:
> > mean(120,175,160))
> >
> > Thanks!
> > N
> >
> > [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> > 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.
> >
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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.
>

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.
Reply | Threaded
Open this post in threaded view
|

Re: Extract from data.frame

Nico Gutierrez
In reply to this post by Bert Gunter-2
Thank you all!
n

On Mon, Sep 21, 2015 at 10:00 PM, Bert Gunter <[hidden email]>
wrote:

> No.
>
>
> On Mon, Sep 21, 2015 at 10:58 AM, John McKown
> <[hidden email]> wrote:
> > On Mon, Sep 21, 2015 at 9:52 AM, Nico Gutierrez <
> [hidden email]>
> > wrote:
> >
> >> Hi All,
> >>
> >> I need to do the following operation from data.frame:
> >>
> >> df <- data.frame(Year = c("2001", "2002", "2003", "2004", "2005",
> "2006",
> >> "2007"), Amount = c(150, 120, 175, 160, 120, 105, 135))
> >> df[which.max(df$Amount),]  #to extract row with max Amount.
> >>
> >> Now I need to do 3 years average around the max Amount value (ie:
> >> mean(120,175,160))
> >>
> >> Thanks!
> >> N
> >>
> >>
> > The simplistic answer is something like:
> >
> > df <- structure(list(Year = structure(1:7, .Label = c("2001", "2002",
> > "2003", "2004", "2005", "2006", "2007"), class = "factor"), Amount =
> c(150,
> > 120, 175, 160, 120, 105, 135)), .Names = c("Year", "Amount"), row.names =
> > c(NA,
> > -7L), class = "data.frame");
> > wdf <- which.max(df$Amount);
> > adf3 <- mean(df$Amount[adf-1:adr+1]);
>
> Typos?!
> But it won't work anyway. See ?Syntax for operator precedence and
>
> Example:
>
> > a <- 1:5
> > mid <- 3
> > a[mid-1:mid+1]
> [1] 3 2 1
> > a[(mid-1):(mid+1)]
> [1] 2 3 4
>
> Cheers,
> Bert
>
>
> >
> > But that ignores the boundry condition where the maximum is at either
> end.
> > What do you want to do in that case?
> >
> >
> > --
> >
> > Schrodinger's backup: The condition of any backup is unknown until a
> > restore is attempted.
> >
> > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will
> be.
> >
> > He's about as useful as a wax frying pan.
> >
> > 10 to the 12th power microphones = 1 Megaphone
> >
> > Maranatha! <><
> > John McKown
> >
> >         [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> > 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.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.