+ Reply to Thread
Results 1 to 5 of 5

Grouping time-stamped data into a different sampling interval

  1. #1
    Registered User
    Join Date
    12-06-2018
    Location
    Norway
    MS-Off Ver
    1811
    Posts
    31

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,595

    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.
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-06-2018
    Location
    Norway
    MS-Off Ver
    1811
    Posts
    31

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

    Quote Originally Posted by shg View Post
    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. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,595

    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. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,141

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

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