datetime - How can I align high frequency time series data in microsoft excel - Stack Overflow

admin管理员组

文章数量:1122826

I have a barologger pressure time series that takes a reading every 15 minutes and a CT2X pressure time series that takes a measurement every 5 minutes. Unfortunately the time series is shifted and they don't align correctly. How can I filter my CT2X time series data so that it matches the time series of the barologger? How could I take an average of the three closest CT2X readings around the barologger time?

I have listed a picture "sample series columns" that ultimately shows what I wish to obtain.

I've tried the index-match tool in excel but I don't think I've used it correctly or if it can be done with this data.

I have a barologger pressure time series that takes a reading every 15 minutes and a CT2X pressure time series that takes a measurement every 5 minutes. Unfortunately the time series is shifted and they don't align correctly. How can I filter my CT2X time series data so that it matches the time series of the barologger? How could I take an average of the three closest CT2X readings around the barologger time?

I have listed a picture "sample series columns" that ultimately shows what I wish to obtain.

I've tried the index-match tool in excel but I don't think I've used it correctly or if it can be done with this data.

• excel
• datetime
• time
• time-series
• pressure

Share Improve this question Follow edited Nov 22, 2024 at 21:38 Ron Rosenfeld 60k77 gold badges3232 silver badges6565 bronze badges asked Nov 22, 2024 at 20:22 Will MacKenzieWill MacKenzie 2544 bronze badges 3

• The question is what have you tried? There are many options... One option is to have a column that calls out the data source and in a consolidated list add everything together and sort by date/time... you can then act upon that consolidated list. – Cyril Commented Nov 22, 2024 at 21:42
• 1 Your results do not seem to be showing "an average of the three closest CT2X readings around the barologger time". Also, your result table is showing a CT2X reading (12:36) that does not exist in your data table. Please clarify both of these discrepancies. – Ron Rosenfeld Commented Nov 22, 2024 at 23:06
• The screenshot I showed only looks at the first 10 rows. This is why the 12:36 CT2X reading is cut off. I could include more rows in a new screenshot if that clarifies things. Yes, youre correct that they don’t show an average of the three closest readings. This is only raw data represented in the screenshot. I was considering exploring the average option when trying to filter the CT2X data to match the barologger data in the best way possible. – Will MacKenzie Commented Nov 23, 2024 at 22:07

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Since you did not show averages as a part of what you expect for results, I chose to return a single closest value by aligning the values by

• Determine the duration between a Barologger Date/Time and each of the CT2X Date/Time entries.
• Set a threshold for the maximum duration allowable.
  • I chose two (2) minutes.
• Line up the appropriate entries.

I added a line and some information to the data you provided:

This can be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

M-Code
Edited to account for more rows in the last two columns than the first two

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Barologger DateTime (24hr)", type datetime}, {"Barologger LEVEL (cm)", type number}, {"CT2X Date / Time (24 hour)", type datetime}, {"CT2X Pressure (psi)", type number}}),
 
//Split the tables
    Barologger = Table.Buffer(Table.SelectColumns(#"Changed Type",List.Range(Table.ColumnNames(#"Changed Type"),0,2))),
    CT2X = Table.Buffer(Table.SelectColumns(#"Changed Type", List.Skip(Table.ColumnNames(#"Changed Type"),2))),

//Find the matching rows
    #"Matched CT2X" = Table.Buffer(Table.AddColumn(Barologger, "Match CT2X",(r)=>
    let

    //generate a list of differences between each of the times
        difs = List.TransformMany(
                    CT2X[#"CT2X Date / Time (24 hour)"],
                    each {_ -r[#"Barologger DateTime (24hr)"]},
                    (a,b)=> Number.Abs(Number.From(b))),

    //determine the row numbers with the matching min times, using 2 minutes (2/1440) as the threshold
        pos = if List.Min(difs) < 2/1440
                then List.PositionOf(difs,List.Min(difs))
                else null
        in pos)),
    #"Matching CT2X" = Table.TransformColumns(#"Matched CT2X",{"Match CT2X", 
        each CT2X{_},type [#"CT2X Date / Time (24 hour)"=datetime, #"CT2X Pressure (psi)"=number]}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Matching CT2X", {"Match CT2X"}),
    #"Expanded Match CT2X" = Table.ExpandRecordColumn(#"Removed Errors", "Match CT2X", {"CT2X Date / Time (24 hour)", "CT2X Pressure (psi)"})
in
    #"Expanded Match CT2X"

Either method gives the following results from the data I posted:

本文标签： datetimeHow can I align high frequency time series data in microsoft excelStack Overflow