R help - Nov 2010 - How to "bin"/average" time points?

Dear all,

I am pretty new to R only having an introduction course, so please bare with
me. I am doing my PhD at The Max Planck Institute of Immunobiology where I
am analyzing some calorimetry data from some mice. 
I have a spreadsheet consisting of measurements of the respiratory exchange
rate at different time points measured every 9 minutes over some days.
My goal is "bin"/average the time points of each hour to only one
measurements. 

E.g.
[Time] - [Measurement]
12.09 - 0.730
12.18 - 0.732
12.27 - 0.743
12.36 - 0.757
12.45 - 0.781
12.54 - 0.731
--> should be averaged to fx one time point and one value, fx:
12.30 - [average of the six measurements]

I know how to average the measurements in a whole column but how to average
every six measurements automatically and also how to average every six time
points and make a new sheet consisting of these data?

I hope you guys are able to help, since we are really stuck here. I can of
course do it manually but with >8000 measurements it will take lots of time.

Thank you very much.

Best regards,
Kevin Dalgaard
-- 
View this message in context:
http://r.789695.n4.nabble.com/How-to-bin-average-time-points-tp3059509p3059509.html
Sent from the R help mailing list archive at Nabble.com.

It would be something like this (might have to change the syntax a bit)

bin_ave=0;
while (i <lenth(time)){
	bin_ave[k]=mean(time(i:i+6));
	k=k+1;
	i=i+6;
}

if your data is in a table format replace time with mytable$time.

hope this helps,

Sachin
p.s. sorry about corporate notice

--- Please consider the environment before printing this email --- 

Allianz - Best General Insurance Company of the Year 2010*
Allianz - General Insurance Company of the Year 2009+ 

* Australian Banking and Finance Insurance Awards
+ Australia and New Zealand Insurance Industry Awards 

This email and any attachments has been sent by Allianz ...{{dropped:3}}

try this:
> # create times 9 minutes apart
> time <- seq(as.POSIXct('2010-11-25 00:00'), by = '9
min', length = 480)
> mySamp <- data.frame(time = time, value = sample(1:100, length(time),
TRUE))
> # add column to split by hour
> mySamp$hour <- format(mySamp$time, '%Y-%m-%d %H:30')
> # compute the mean for each hour
> tapply(mySamp$value, mySamp$hour, mean)
2010-11-25 00:30 2010-11-25 01:30 2010-11-25 02:30 2010-11-25 03:30
2010-11-25 04:30 2010-11-25 05:30
        54.42857         59.85714         47.50000         45.71429
     40.28571         56.50000
2010-11-25 06:30 2010-11-25 07:30 2010-11-25 08:30 2010-11-25 09:30
2010-11-25 10:30 2010-11-25 11:30
        46.57143         47.14286         34.00000         53.85714
     50.28571         36.00000
2010-11-25 12:30 2010-11-25 13:30 2010-11-25 14:30 2010-11-25 15:30
2010-11-25 16:30 2010-11-25 17:30
        31.57143         44.57143         42.50000         52.42857
     54.14286         44.66667
2010-11-25 18:30 2010-11-25 19:30 2010-11-25 20:30 2010-11-25 21:30
2010-11-25 22:30 2010-11-25 23:30
        50.28571         60.57143         36.00000         42.14286
     65.14286         37.50000
2010-11-26 00:30 2010-11-26 01:30 2010-11-26 02:30 2010-11-26 03:30
2010-11-26 04:30 2010-11-26 05:30
        51.71429         58.85714         48.50000         45.00000
     44.00000         38.00000
2010-11-26 06:30 2010-11-26 07:30 2010-11-26 08:30 2010-11-26 09:30
2010-11-26 10:30 2010-11-26 11:30
        56.00000         34.14286         64.66667         51.42857
     57.57143         44.50000
2010-11-26 12:30 2010-11-26 13:30 2010-11-26 14:30 2010-11-26 15:30
2010-11-26 16:30 2010-11-26 17:30
        65.00000         59.57143         63.50000         52.57143
     36.85714         63.33333
2010-11-26 18:30 2010-11-26 19:30 2010-11-26 20:30 2010-11-26 21:30
2010-11-26 22:30 2010-11-26 23:30
        44.85714         64.85714         63.00000         62.57143
     62.00000         57.00000
2010-11-27 00:30 2010-11-27 01:30 2010-11-27 02:30 2010-11-27 03:30
2010-11-27 04:30 2010-11-27 05:30
        26.71429         33.57143         37.50000         67.00000
     47.85714         63.00000
2010-11-27 06:30 2010-11-27 07:30 2010-11-27 08:30 2010-11-27 09:30
2010-11-27 10:30 2010-11-27 11:30
        40.28571         46.42857         54.50000         41.00000
     51.00000         58.33333
2010-11-27 12:30 2010-11-27 13:30 2010-11-27 14:30 2010-11-27 15:30
2010-11-27 16:30 2010-11-27 17:30
        62.14286         52.28571         75.33333         43.71429
     53.14286         27.50000
2010-11-27 18:30 2010-11-27 19:30 2010-11-27 20:30 2010-11-27 21:30
2010-11-27 22:30 2010-11-27 23:30
        33.42857         56.85714         57.83333         51.00000
     57.71429         38.66667


On Thu, Nov 25, 2010 at 3:49 PM, DonDolowy <kevin.dalgaard at gmail.com>
wrote:
>
> Dear all,
>
> I am pretty new to R only having an introduction course, so please bare
with
> me. I am doing my PhD at The Max Planck Institute of Immunobiology where I
> am analyzing some calorimetry data from some mice.
> I have a spreadsheet consisting of measurements of the respiratory exchange
> rate at different time points measured every 9 minutes over some days.
> My goal is "bin"/average the time points of each hour to only one
> measurements.
>
> E.g.
> [Time] - [Measurement]
> 12.09 - 0.730
> 12.18 - 0.732
> 12.27 - 0.743
> 12.36 - 0.757
> 12.45 - 0.781
> 12.54 - 0.731
> --> should be averaged to fx one time point and one value, fx:
> 12.30 - [average of the six measurements]
>
> I know how to average the measurements in a whole column but how to average
> every six measurements automatically and also how to average every six time
> points and make a new sheet consisting of these data?
>
> I hope you guys are able to help, since we are really stuck here. I can of
> course do it manually but with >8000 measurements it will take lots of
time.
>
> Thank you very much.
>
> Best regards,
> Kevin Dalgaard
> --
> View this message in context:
http://r.789695.n4.nabble.com/How-to-bin-average-time-points-tp3059509p3059509.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> 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.
>


-- 
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?

Hi Kevin

Here is one way:

yourData <- c(0.730, 0.732, 0.743, 0.757,0.781, 0.731,
0.830, 0.832, 0.843, 0.857, 0.881, 0.831)

nrGroups <- 2
lengthGroups <- 6

tapply(yourData, factor(rep(c(1,nrGroups), each = lengthGroups)), mean)

and you will have to adjust the number of groups and if necessary the
length of the groups to your needs. I am assuming that all groups are
of the same length.

Do you really want to average the time points? In that case you might
want to consider the data structures for representing dates and times,
see e.g.

?POSIXct

or converting the times to minutes, say.

- Niels


On 25/11/10 12.49, DonDolowy wrote:
>
> Dear all,
>
> I am pretty new to R only having an introduction course, so please bare
with
> me. I am doing my PhD at The Max Planck Institute of Immunobiology where I
> am analyzing some calorimetry data from some mice.
> I have a spreadsheet consisting of measurements of the respiratory exchange
> rate at different time points measured every 9 minutes over some days.
> My goal is "bin"/average the time points of each hour to only one
> measurements.
>
> E.g.
> [Time] - [Measurement]
> 12.09 - 0.730
> 12.18 - 0.732
> 12.27 - 0.743
> 12.36 - 0.757
> 12.45 - 0.781
> 12.54 - 0.731
> -->  should be averaged to fx one time point and one value, fx:
> 12.30 - [average of the six measurements]
>
> I know how to average the measurements in a whole column but how to average
> every six measurements automatically and also how to average every six time
> points and make a new sheet consisting of these data?
>
> I hope you guys are able to help, since we are really stuck here. I can of
> course do it manually but with>8000 measurements it will take lots of
time.
>
> Thank you very much.
>
> Best regards,
> Kevin Dalgaard