Grouping time-stamped data into a different sampling interval

1. Grouping time-stamped data into a different sampling interval

Hello,

I've uploaded a sample of the data I'm working with. My question is rather simple although a bit complicated to implement due to the large number of columns in my dataset.

In short, there are three categories of data to transform;

1) Columns A and B are the time-stamps. As you can see, they are taken every 80-seconds.

2) Columns C to E are activity values for 3 different axis

3) Columns F to O contain the number of seconds for each type of Event as seen on the header of the columns for every 80-second period. The total time (in seconds) is summarized in column P

Hence, I would like to transform my data to have time-stamps taken every 4 minutes (i.e. 240-seconds). The columns will have to be transformed as follows:

1) Columns A and B will have a time-step of 240-seconds (starting 6/19/2018 at 10:40:00).

2) Values in columns C to E will have to be grouped every three rows (because 240-seconds = 3 * 80-seconds) and then averaged to match the 240-second intervals

3) Values on columns C to E will have to be grouped every three rows and summed. Column P being the sum of values for columns [F:O], a value of 240 for all rows will have to be displayed to make sure the transformation as been done right

I hope somebody can help me or at least put me on the right track. I'm rather new to Excel so any input is appreciated.

Cheers!

2. Re: Grouping time-stamped data into a different sampling interval

See attached.

After entering the formulas shown in row 4, I selected R4:AF6 and double-clicked the fill handle.

Then you can copy those coulumns, paste as values, delete columns A:Q, and sort by col R to close up.

3. Re: Grouping time-stamped data into a different sampling interval

Originally Posted by shg
See attached.

After entering the formulas shown in row 4, I selected R4:AF6 and double-clicked the fill handle.

Then you can copy those coulumns, paste as values, delete columns A:Q, and sort by col R to close up.
I followed your steps by filling in the formulas for the 4th row. However, I'm not able to find any fill handle when I'm selecting R4:AF6. Indeed, I don't have any option to click on at the corner of the cell as all my selected areas will become grey.

Also, I'm wondering why I should select R4:AF6 and not the full R4:AP6?

4. Re: Grouping time-stamped data into a different sampling interval

The fill handle is the small square that appears in the lower-right corner of the selection marquee. When you hover the mouse cursor over it, the cursor changes to a +

But I gave you bad dope. I actually entered the formulas staring in col Q, filled them down as described, and then decided for cosmetic reasons to insert a blank column. Doing that keeps double-clicking the fill handle from working because of the lack of adjacency. So just grab the fill handle and drag down.

Also, I'm wondering why I should select R4:AF6 and not the full R4:AP6?
The formulas only extend to col AF:

 R S T U V W X Y Z AA AB AC AD AE AF 1 GMT_DATE & Time ACTIVITY_X ACTIVITY_Y ACTIVITY_Z Vigilance Head-up Grazing Browsing Moving Grooming Resting Fleeing Unknown End Total 2 3 4 06/19/2018 10:40 60.7 64.3 88.3 0 232 0 0 0 5 0 0 3 0 240 5 6

5. Re: Grouping time-stamped data into a different sampling interval

You also have proposed solutions in your duplicate of this thread, which a moderator has now closed - check back in that one.

Pete

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1