Combination of two tables with different time lines

Hey!

Attached I have two tables I want to combine. (For table two only the columns A,C,E and G shall be considered) Problem is that I cannot synchronize the times since they have different formats, intervals and lengths. How can I create a common time column with a interval fitting for both with the other information combined?

Thank you in advance!

Answer 1

[Star Strider]

Converting the times in the first file is straightforward. Everything else seems to be NaN.

T1 = readtable('Combining1.xlsx')

T1 = 10943×5 table
              SampleTime               KP45GI004IP_ANALOGMAP    KP45GI002IP_ANALOGMAP    KP45GI001IP_ANALOGMAP    KP45GI003IP_ANALOGMAP
    _______________________________    _____________________    _____________________    _____________________    _____________________

    {'2022-10-01T06:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T07:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T08:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T09:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T10:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T11:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T12:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T13:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T14:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T15:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T16:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T17:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T18:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T19:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T20:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         
    {'2022-10-01T21:23:42.000000Z'}             NaN                      NaN                      NaN                      NaN         

T1.SampleTime = datetime(T1.SampleTime, 'InputFormat','yyyy-MM-dd''T''HH:mm:ss.SSSSSSZ', 'TimeZone','UTC')

T1 = 10943×5 table
         SampleTime         KP45GI004IP_ANALOGMAP    KP45GI002IP_ANALOGMAP    KP45GI001IP_ANALOGMAP    KP45GI003IP_ANALOGMAP
    ____________________    _____________________    _____________________    _____________________    _____________________

    01-Oct-2022 06:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 07:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 08:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 09:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 10:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 11:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 12:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 13:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 14:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 15:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 16:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 17:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 18:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 19:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 20:23:42             NaN                      NaN                      NaN                      NaN         
    01-Oct-2022 21:23:42             NaN                      NaN                      NaN                      NaN         

T2 = readtable('Combining2.xlsx')

T2 = 475×11 table
       Time        SampleTime    KP47GI101IP_ANALOGMAP    Date1_ttCombinedDaily    Lufttemperatur_ttCombinedDaily    Date2_ttCombinedDaily    Nederb_rdsm_ngd_ttCombinedDaily    Date1_ttLufttemperaturDaily    Lufttemperatur_ttLufttemperaturDaily    Date2_ttNederbordsmangdDaily    Nederb_rdsm_ngd_ttNederbordsmangdDaily
    ___________    __________    _____________________    _____________________    ______________________________    _____________________    _______________________________    ___________________________    ____________________________________    ____________________________    ______________________________________

    01-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    02-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    03-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    04-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    05-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    06-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    07-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    08-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    09-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    10-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    11-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    12-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    13-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    14-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    15-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  
    16-Jan-2021       NaN                 NaN                      NaN                          NaN                           NaN                           NaN                              NaN                                NaN                                 NaN                                  NaN                  

.

[Magnus]

Thanks for your answer! Yes, exactly it is NaN since I took out the data (might be confidential).

But, do you know how to synchronize and combine the tables after importing them?

[Star Strider]

It is probably not possible to demonstrate it with these files (because of all the NaN values), however the synchronize function (R2016b) would likely work.

My apologies for the delay — away for a while.

[Magnus]

No problem! I want to synchronize the time series so that the values of the two sheets are merged. Does it really matter what number is written there as long as you know as it is a double? I assume the code remains the same, no?

[Star Strider]

It matters because it is important to understand the results.

That aside, it is straightforward to create random integers for each variable —

T1 = readtable('Combining1.xlsx');
T1.SampleTime = datetime(T1.SampleTime, 'InputFormat','yyyy-MM-dd''T''HH:mm:ss.SSSSSSZ', 'TimeZone','UTC');
s1 = size(T1,1);
tv1 = linspace(0, s1/500, s1).';
for k = 2:size(T1,2)
    T1{:,k} = randi(99,s1,1) + 10*sin(2*pi*tv1);
end

T2 = readtable('Combining2.xlsx');
T2.SampleTime = T2.Time;
T2 = removevars(T2, 'Time');
T2.SampleTime.TimeZone = 'Australia/Sydney';
s2 = size(T2,1);
tv2 = linspace(0, s2/500, s2).';
for k = 2:size(T2,2)
    T2{:,k} = randi(99,s2,1) + 10*sin(2*pi*tv2);
end

TT1 = table2timetable(T1)

TT1 = 10943×4 timetable
         SampleTime         KP45GI004IP_ANALOGMAP    KP45GI002IP_ANALOGMAP    KP45GI001IP_ANALOGMAP    KP45GI003IP_ANALOGMAP
    ____________________    _____________________    _____________________    _____________________    _____________________

    01-Oct-2022 06:23:42               12                        2                       21                       86        
    01-Oct-2022 07:23:42           38.126                   30.126                   42.126                   36.126        
    01-Oct-2022 08:23:42           30.251                   94.251                   92.251                   33.251        
    01-Oct-2022 09:23:42           12.377                   59.377                   44.377                   15.377        
    01-Oct-2022 10:23:42           68.502                   65.502                   35.502                   82.502        
    01-Oct-2022 11:23:42           53.628                   72.628                   87.628                   20.628        
    01-Oct-2022 12:23:42           35.753                   42.753                   19.753                   12.753        
    01-Oct-2022 13:23:42           34.879                   7.8786                   93.879                   9.8786        
    01-Oct-2022 14:23:42           61.004                   58.004                   17.004                   67.004        
    01-Oct-2022 15:23:42           65.129                   45.129                   2.1287                   63.129        
    01-Oct-2022 16:23:42           94.253                   67.253                   31.253                   73.253        
    01-Oct-2022 17:23:42           88.378                   52.378                   57.378                   68.378        
    01-Oct-2022 18:23:42           62.502                   34.502                   52.502                   23.502        
    01-Oct-2022 19:23:42           88.627                   16.627                   4.6265                   32.627        
    01-Oct-2022 20:23:42            86.75                    88.75                    40.75                    47.75        
    01-Oct-2022 21:23:42           96.874                   96.874                   60.874                   94.874        

TT2 = table2timetable(T2, 'RowTimes','SampleTime')

TT2 = 475×9 timetable
    SampleTime     KP47GI101IP_ANALOGMAP    Date1_ttCombinedDaily    Lufttemperatur_ttCombinedDaily    Date2_ttCombinedDaily    Nederb_rdsm_ngd_ttCombinedDaily    Date1_ttLufttemperaturDaily    Lufttemperatur_ttLufttemperaturDaily    Date2_ttNederbordsmangdDaily    Nederb_rdsm_ngd_ttNederbordsmangdDaily
    ___________    _____________________    _____________________    ______________________________    _____________________    _______________________________    ___________________________    ____________________________________    ____________________________    ______________________________________

    01-Jan-2021               32                       63                            99                           38                            14                               39                                  36                                  89                                   90                
    02-Jan-2021           91.126                   61.126                        88.126                       47.126                        64.126                           44.126                              48.126                              16.126                               67.126                
    03-Jan-2021           46.252                   8.2518                        32.252                       39.252                        1.2518                           38.252                              59.252                              1.2518                               32.252                
    04-Jan-2021           82.378                   6.3777                        71.378                       23.378                        86.378                           12.378                              98.378                              44.378                               72.378                
    05-Jan-2021           58.504                   97.504                        64.504                       74.504                        9.5035                           78.504                              86.504                              35.504                               95.504                
    06-Jan-2021           98.629                   64.629                        31.629                       70.629                        31.629                           40.629                              43.629                              46.629                               13.629                
    07-Jan-2021           17.755                   74.755                        48.755                       36.755                        16.755                           46.755                              2.7549                              55.755                               96.755                
    08-Jan-2021            51.88                    42.88                         79.88                       8.8804                         84.88                            77.88                               35.88                               99.88                               9.8804                
    09-Jan-2021           25.006                   91.006                        90.006                       24.006                        44.006                           86.006                              89.006                              5.0057                               47.006                
    10-Jan-2021           62.131                   54.131                        66.131                       91.131                        62.131                           97.131                              20.131                              39.131                               87.131                
    11-Jan-2021            7.256                   52.256                        53.256                       89.256                        96.256                           15.256                              49.256                              99.256                               39.256                
    12-Jan-2021           86.381                   28.381                        13.381                       19.381                        78.381                           22.381                              71.381                              24.381                               33.381                
    13-Jan-2021           85.505                   83.505                        68.505                       65.505                        88.505                           38.505                              39.505                              36.505                               5.5054                
    14-Jan-2021           7.6298                    32.63                         10.63                        68.63                         95.63                            44.63                               97.63                               72.63                                31.63                
    15-Jan-2021           75.754                   66.754                        11.754                       64.754                        20.754                           27.754                              90.754                              60.754                               70.754                
    16-Jan-2021           67.878                   70.878                        66.878                       12.878                        47.878                           53.878                              29.878                              78.878                               16.878                

TTU = synchronize(TT1,TT2,'union','linear')

TTU = 11418×13 timetable
         SampleTime         KP45GI004IP_ANALOGMAP    KP45GI002IP_ANALOGMAP    KP45GI001IP_ANALOGMAP    KP45GI003IP_ANALOGMAP    KP47GI101IP_ANALOGMAP    Date1_ttCombinedDaily    Lufttemperatur_ttCombinedDaily    Date2_ttCombinedDaily    Nederb_rdsm_ngd_ttCombinedDaily    Date1_ttLufttemperaturDaily    Lufttemperatur_ttLufttemperaturDaily    Date2_ttNederbordsmangdDaily    Nederb_rdsm_ngd_ttNederbordsmangdDaily
    ____________________    _____________________    _____________________    _____________________    _____________________    _____________________    _____________________    ______________________________    _____________________    _______________________________    ___________________________    ____________________________________    ____________________________    ______________________________________

    31-Dec-2020 13:00:00         -4.0048e+05              -4.3115e+05              -3.2382e+05              7.6463e+05                     32                       63                            99                           38                            14                               39                                  36                                  89                                   90                
    01-Jan-2021 13:00:00         -3.9985e+05              -4.3047e+05              -3.2332e+05              7.6343e+05                 91.126                   61.126                        88.126                       47.126                        64.126                           44.126                              48.126                              16.126                               67.126                
    02-Jan-2021 13:00:00         -3.9922e+05               -4.298e+05              -3.2281e+05              7.6224e+05                 46.252                   8.2518                        32.252                       39.252                        1.2518                           38.252                              59.252                              1.2518                               32.252                
    03-Jan-2021 13:00:00          -3.986e+05              -4.2912e+05               -3.223e+05              7.6104e+05                 82.378                   6.3777                        71.378                       23.378                        86.378                           12.378                              98.378                              44.378                               72.378                
    04-Jan-2021 13:00:00         -3.9797e+05              -4.2845e+05              -3.2179e+05              7.5984e+05                 58.504                   97.504                        64.504                       74.504                        9.5035                           78.504                              86.504                              35.504                               95.504                
    05-Jan-2021 13:00:00         -3.9734e+05              -4.2777e+05              -3.2129e+05              7.5864e+05                 98.629                   64.629                        31.629                       70.629                        31.629                           40.629                              43.629                              46.629                               13.629                
    06-Jan-2021 13:00:00         -3.9672e+05               -4.271e+05              -3.2078e+05              7.5745e+05                 17.755                   74.755                        48.755                       36.755                        16.755                           46.755                              2.7549                              55.755                               96.755                
    07-Jan-2021 13:00:00         -3.9609e+05              -4.2642e+05              -3.2027e+05              7.5625e+05                  51.88                    42.88                         79.88                       8.8804                         84.88                            77.88                               35.88                               99.88                               9.8804                
    08-Jan-2021 13:00:00         -3.9546e+05              -4.2575e+05              -3.1977e+05              7.5505e+05                 25.006                   91.006                        90.006                       24.006                        44.006                           86.006                              89.006                              5.0057                               47.006                
    09-Jan-2021 13:00:00         -3.9484e+05              -4.2507e+05              -3.1926e+05              7.5386e+05                 62.131                   54.131                        66.131                       91.131                        62.131                           97.131                              20.131                              39.131                               87.131                
    10-Jan-2021 13:00:00         -3.9421e+05               -4.244e+05              -3.1875e+05              7.5266e+05                  7.256                   52.256                        53.256                       89.256                        96.256                           15.256                              49.256                              99.256                               39.256                
    11-Jan-2021 13:00:00         -3.9358e+05              -4.2372e+05              -3.1825e+05              7.5146e+05                 86.381                   28.381                        13.381                       19.381                        78.381                           22.381                              71.381                              24.381                               33.381                
    12-Jan-2021 13:00:00         -3.9295e+05              -4.2305e+05              -3.1774e+05              7.5027e+05                 85.505                   83.505                        68.505                       65.505                        88.505                           38.505                              39.505                              36.505                               5.5054                
    13-Jan-2021 13:00:00         -3.9233e+05              -4.2237e+05              -3.1723e+05              7.4907e+05                 7.6298                    32.63                         10.63                        68.63                         95.63                            44.63                               97.63                               72.63                                31.63                
    14-Jan-2021 13:00:00          -3.917e+05               -4.217e+05              -3.1672e+05              7.4787e+05                 75.754                   66.754                        11.754                       64.754                        20.754                           27.754                              90.754                              60.754                               70.754                
    15-Jan-2021 13:00:00         -3.9107e+05              -4.2102e+05              -3.1622e+05              7.4667e+05                 67.878                   70.878                        66.878                       12.878                        47.878                           53.878                              29.878                              78.878                               16.878                

figure
plot(TT1.SampleTime, TT1{:,1:end})
grid
xlabel('SampleTime')
ylabel('Values')
title('TT1')

figure
plot(TT2.SampleTime, TT2{:,1:end})
grid
xlabel('SampleTime')
ylabel('Values')
title('TT2')

figure
plot(TTU.SampleTime, TTU{:,1:end})
grid
xlabel('SampleTime')
ylabel('Values')
title('TTU')

This result is the reason that the actual data — including the time zones — are important!

See the documentation I linked to, specifically the Input Arguments section, for various ways to combine them.

.

[Magnus]

Thank you first of all! I didn't know that since i started applying Matlab 2 weeks ago.

The problem I encounter is however that one dataset has hourly measurements and the other one daily. Further the amount of rows deviates. I can only send you this one with data, since the other measurements are confidential., but easpecially below row 641 the document gets more complex.

[Star Strider]

My pleasure!

The NaT (‘not a time’, the time equivalent of NaN in MATLAB) values are going to make any time-related operations impossible. Additionally, timetable arrays can only have one time variable (usually the first variable), so the time values in the other variables will simply be treated as data, although time data in the other variables will be able to use the normal datetime functions.

Interpolating daily measurements to hourly is going to require a significant leap-of-faith that would create so much new (and unreliable) data that I would not trust the results. Accumulating or interpolating hourly data to daily would work.

There are of course other ways to create a synchronized table, and the nearest-neighbour methods will likely be preferable to the 'linear' interpolation method that I used previously. Even considering that, timetables like this are going to be a significant challenge. It may not be possible to work with them.

T3 = readtable('SecondExcelFileName.xlsx')

T3 = 12042×11 table
       Time        SampleTime    KP47GI101IP_ANALOGMAP    Date1_ttCombinedDaily    Lufttemperatur_ttCombinedDaily    Date2_ttCombinedDaily    Nederb_rdsm_ngd_ttCombinedDaily    Date1_ttLufttemperaturDaily    Lufttemperatur_ttLufttemperaturDaily    Date2_ttNederbordsmangdDaily    Nederb_rdsm_ngd_ttNederbordsmangdDaily
    ___________    __________    _____________________    _____________________    ______________________________    _____________________    _______________________________    ___________________________    ____________________________________    ____________________________    ______________________________________

    01-Jan-2021       NaN                 NaN                  01-Jan-2021                        -5                          NaN                           NaN                          01-Jan-2021                              -5                                NaN                                  NaN                  
    02-Jan-2021       NaN                 NaN                  02-Jan-2021                      -8.9                          NaN                           NaN                          02-Jan-2021                            -8.9                                NaN                                  NaN                  
    03-Jan-2021       NaN                 NaN                  03-Jan-2021                     -11.7                          NaN                           NaN                          03-Jan-2021                           -11.7                                NaN                                  NaN                  
    04-Jan-2021       NaN                 NaN                  04-Jan-2021                      -9.6                          NaN                           NaN                          04-Jan-2021                            -9.6                                NaN                                  NaN                  
    05-Jan-2021       NaN                 NaN                  05-Jan-2021                       -10                          NaN                           NaN                          05-Jan-2021                             -10                                NaN                                  NaN                  
    06-Jan-2021       NaN                 NaN                  06-Jan-2021                     -12.4                          NaN                           NaN                          06-Jan-2021                           -12.4                                NaN                                  NaN                  
    07-Jan-2021       NaN                 NaN                  07-Jan-2021                     -16.6                          NaN                           NaN                          07-Jan-2021                           -16.6                                NaN                                  NaN                  
    08-Jan-2021       NaN                 NaN                  08-Jan-2021                     -13.8                          NaN                           NaN                          08-Jan-2021                           -13.8                                NaN                                  NaN                  
    09-Jan-2021       NaN                 NaN                  09-Jan-2021                     -14.1                          NaN                           NaN                          09-Jan-2021                           -14.1                                NaN                                  NaN                  
    10-Jan-2021       NaN                 NaN                  10-Jan-2021                     -10.6                          NaN                           NaN                          10-Jan-2021                           -10.6                                NaN                                  NaN                  
    11-Jan-2021       NaN                 NaN                  11-Jan-2021                       -18                          NaN                           NaN                          11-Jan-2021                             -18                                NaN                                  NaN                  
    12-Jan-2021       NaN                 NaN                  12-Jan-2021                     -15.5                          NaN                           NaN                          12-Jan-2021                           -15.5                                NaN                                  NaN                  
    13-Jan-2021       NaN                 NaN                  13-Jan-2021                     -21.4                          NaN                           NaN                          13-Jan-2021                           -21.4                                NaN                                  NaN                  
    14-Jan-2021       NaN                 NaN                  14-Jan-2021                     -15.8                          NaN                           NaN                          14-Jan-2021                           -15.8                                NaN                                  NaN                  
    15-Jan-2021       NaN                 NaN                  15-Jan-2021                      -1.9                          NaN                           NaN                          15-Jan-2021                            -1.9                                NaN                                  NaN                  
    16-Jan-2021       NaN                 NaN                  16-Jan-2021                      -8.7                          NaN                           NaN                          16-Jan-2021                            -8.7                                NaN                                  NaN                  

LastRows = T3(635:end,:)

LastRows = 11408×11 table
            Time            SampleTime    KP47GI101IP_ANALOGMAP    Date1_ttCombinedDaily    Lufttemperatur_ttCombinedDaily    Date2_ttCombinedDaily    Nederb_rdsm_ngd_ttCombinedDaily    Date1_ttLufttemperaturDaily    Lufttemperatur_ttLufttemperaturDaily    Date2_ttNederbordsmangdDaily    Nederb_rdsm_ngd_ttNederbordsmangdDaily
    ____________________    __________    _____________________    _____________________    ______________________________    _____________________    _______________________________    ___________________________    ____________________________________    ____________________________    ______________________________________

    27-Sep-2022 00:00:00        NaN                 NaN                 27-Sep-2022                      5.8                          44831                            0                          27-Sep-2022                            5.8                                44831                                   0                  
    28-Sep-2022 00:00:00        NaN                 NaN                 28-Sep-2022                      5.2                          44832                          0.5                          28-Sep-2022                            5.2                                44832                                 0.5                  
    29-Sep-2022 00:00:00        NaN                 NaN                 29-Sep-2022                      4.9                          44833                            0                          29-Sep-2022                            4.9                                44833                                   0                  
    30-Sep-2022 00:00:00        NaN                 NaN                 30-Sep-2022                      0.9                          44834                            0                          30-Sep-2022                            0.9                                44834                                   0                  
    01-Oct-2022 00:00:00        NaN                 NaN                 01-Oct-2022                      1.2                          44835                            0                          01-Oct-2022                            1.2                                44835                                   0                  
    01-Oct-2022 16:55:45      44836              523.49                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  
    01-Oct-2022 17:55:45      44836              523.49                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  
    01-Oct-2022 18:55:45      44836              523.49                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  
    01-Oct-2022 19:55:45      44836              523.49                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  
    01-Oct-2022 20:55:45      44836              523.49                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  
    01-Oct-2022 21:55:45      44836              523.48                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  
    01-Oct-2022 22:55:45      44836              523.48                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  
    01-Oct-2022 23:55:45      44836              523.48                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  
    02-Oct-2022 00:00:00        NaN                 NaN                 02-Oct-2022                      4.7                          44836                            0                          02-Oct-2022                            4.7                                44836                                   0                  
    02-Oct-2022 00:55:45      44836              523.48                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  
    02-Oct-2022 01:55:45      44836              523.48                         NaT                      NaN                            NaN                          NaN                                  NaT                            NaN                                  NaN                                 NaN                  

.

[Magnus]

I understand. Meaning I would need to find the best way to reduce the hourly values to exclusively one value to synchronize the time series?

[Star Strider]

Yes. I would either use an interpolant (essentially interpolating the hourly data to the closest time to the daily data sampling clock times), or a nearest-neighbour value if that is appropriate. (I would definitely not interpolate the daily data to hourly data, since it hides any circadian variation, even though that is theoretically possible.)

The problems are going to be with any actual NaN or NaT values (I realise that many of the posted NaN values are to hide the data), since they cannot be interpolated.

[Magnus]

I understand! Thanks for your help and effort!!

[Star Strider]

My pleasure!