Results 1 to 5 of 5

Thread: Timestamp data with missing time

  1. #1
    Neophyte Jennings1's Avatar
    Join Date
    Dec 2019
    Posts
    3
    Articles
    0
    Excel Version
    16

    Timestamp data with missing time



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone,

    I am having trouble with some timestamped data. This is match event data from a sports game with both a timestamp and the accumulated time in seconds at which each event occurs. In order for me to work with it and time match it to some other data, I really need the data to show every second of match time (even if there was no event. I have posted data below. As you can see, a row only exists if an event occurred. I would like a row for each second that has elapsed even if no statistic_code occured.

    PERIOD PERIOD_SECS MATCH_TRX_TIME_UTC MATCH_TRX_ID STATISTIC_CODE
    1 0 7:49:50 100 CETU
    1 8 7:49:58 200 GELON
    1 8 7:49:58 210 CBFP
    1 9 7:49:59 310 HBEF
    1 9 7:49:59 320 CBCL

    This is a very large dataset so I am trying to find a quick method to perform this in excel or I may have the capacity to do it in Python.
    Any help would be very much appreciated.
    Cheers
    Jake

  2. #2
    Neophyte Jennings1's Avatar
    Join Date
    Dec 2019
    Posts
    3
    Articles
    0
    Excel Version
    16
    Here is a larger portion

    PERIOD PERIOD_SECS MATCH_TRX_TIME_UTC MATCH_TRX_ID STATISTIC_CODE
    1 0 7:49:50 100 CETU
    1 8 7:49:58 200 GELON
    1 8 7:49:58 210 CBFP
    1 9 7:49:59 310 HBEF
    1 9 7:49:59 320 CBCL
    1 12 7:50:02 400 HBRE
    1 12 7:50:02 500 KKLO
    1 12 7:50:02 510 KIN50
    1 13 7:50:03 600 IN50
    1 15 7:50:05 700 OOBO
    1 27 7:50:17 800 THIN
    1 32 7:50:22 900 GEHAN
    1 32 7:50:22 910 TIFP
    1 32 7:50:22 1000 KKIN
    1 32 7:50:22 1100 TACKL
    1 36 7:50:26 1200 GELOO
    1 36 7:50:26 1310 HBEF
    1 36 7:50:26 1320 TICL
    1 36 7:50:26 1400 HBRE
    1 38 7:50:28 1510 HBEF
    1 41 7:50:31 1600 HBRE

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,658
    Articles
    0
    Excel Version
    365
    Try the button in the attached which runs this code:
    Code:
    Sub blah()
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For rw = lr To 3 Step -1
      x = Cells(rw, 2).Value - Cells(rw - 1, 2).Value
      If x > 1 Then
        Rows(rw).Resize(x - 1).Insert
        Range(Cells(rw - 1, 2), Cells(rw + x - 1, 3)).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True
      End If
    Next rw
    End Sub
    You say: "In order for me to work with it and time match it to some other data, I really need the data to show every second of match time (even if there was no event." but you may still not need to have a value for every second - what formula are you using to match (or what are you doing to match)?
    Attached Files Attached Files
    Last edited by p45cal; 2019-12-15 at 12:13 AM.

  4. #4
    Neophyte Jennings1's Avatar
    Join Date
    Dec 2019
    Posts
    3
    Articles
    0
    Excel Version
    16
    Wow! Thank you that works perfectly. This is probably a dumb question but is that just assigning a macro?
    I want to time match this data with some GPS data which is sampled at 10hz so if I could split this into .10 of a second that would be even more amazing.
    Thank you

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,658
    Articles
    0
    Excel Version
    365
    The attached workbook's macro currently gives 1 second intervals as before, but if you change one line:
    Code:
    myinterval = 1 'seconds
    to:
    Code:
    myinterval = 0.1 'seconds
    you'll get tenth of a second intervals. If you change it to 0.5 you'll get half second intervals.
    If you change it to
    Code:
    myinterval = 1 / 3 'seconds
    you'll get third of a second intervals.

    But I strongly suspect you may still not need to have a value for every second/tenth of a second - what formula are you using to match (or what are you doing to match)? I want to know what formula you're using.

    The whole adjusted code is:
    Code:
    Sub blah()
    myinterval = 1 'seconds
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For rw = lr To 3 Step -1
      x = Cells(rw, 2).Value - Cells(rw - 1, 2).Value
      If x > myinterval Then
        RowsToInsert = Int(x / myinterval) - 1
        If RowsToInsert > 0 Then
          Rows(rw).Resize(RowsToInsert).Insert
          Range(Cells(rw - 1, 2), Cells(rw + x / myinterval - 1, 3)).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True
        End If
      End If
    Next rw
    End Sub
    Attached Files Attached Files
    Last edited by p45cal; 2019-12-15 at 10:58 PM.

Posting Permissions

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