how to handle variable time stamped long time series data?

Hello! i am working on time series data of sea water levels. one station data is in zrx format, and other is in excel(xls) format. i have to find the correlation between the two data sets in Matlab. time stamps of both files are not syncronized. and i have to correlate the data on same time stamp. please guide me, should i shift my data to timetable. and then start my analysis? or how should i start it ?

thanks in advance.

Answer 1

[jonas]

1. Load data
2. Put data in two timetables
3. Use synchronize
4. Step 1-3 did not work? Upload the data.

[bushra raza]

Hi, thanx for the reply. i tried timetables. one data file worked fine. other data file's timestamp is like 19991101000100 , which is not understandable to timetable. how to make this value as a datetime format to be understandable for timetable. moreover, 19991101000100 is (yyyy mm dd hh mm ss)

any idea ?

[per isakson]

Try

>> datetime( '19991101000100', 'InputFormat', 'yyyyMMddHHmmss' )
ans = 
  datetime
   01-Nov-1999 00:01:00

[bushra raza]

no its not working in table.

here is the code for creating a table and then using it further

timmTbl= readtable('Timm_Data.xlsx'); % TimmData = table2timetable(timmTbl); head(TimmTbl); the out put is :

    1.9991e+13         480     ''  
    1.9991e+13         480     ''  
    1.9991e+13         481     ''  
    1.9991e+13         481     ''  
    1.9991e+13         481     ''  
    1.9991e+13         481     ''  
    1.9991e+13         481     ''  
    1.9991e+13         481     ''  
here the first column is having datetime like 19991101000100 in original file, but it displays like 1.9991e+13.
when i write:
datetime( timmTbl(:,1), 'InputFormat', 'yyyyMMddHHmmss' );
i got following error:

Error using datetime (line 639) Input data must be a numeric or a date/time string or a cell array or char matrix containing date/time character vectors.

[jonas]

You cant pass double data to datetime like that. You need to first convert the numbers to strings using 'num2string'. After that it should work. Just make sure the strings are formatted correctly without trailing zeros or so.

Best would be to import the data correctly from the get go. Sometimes its enough to just write

opts=detectImportOptions('filename')

And pass it to readtable

readtable(filename, opts)

Upload some sample data if it doesn't work.

[bushra raza]

here is a small sample data file attached.

[jonas]

Importing the data with readtable gives these values

T =

11×2 table

      timestamp       value
    ______________    _____

    19991101000100     480

This line converts the first row to datetime format

T.timestamp = datetime(num2str(T{:,1}),'inputformat','yyyyMMddHHmmss');

You can then convert the table to a timetable.

TT = table2timetable(T(:,2:end),'RowTimes',T.timestamp)

TT =

11×1 timetable

            Time            value
    ____________________    _____

    01-Nov-1999 00:01:00     480

[bushra raza]

thanx, it worked fine for a chunk of the data i sent as in book1.xlsx. but my heavy data file is not picking it.

actually, the data file to be shifted into timetable is a 2 column file in zrx format. it can be viewed in notepad. its initial lines are not s headers rather some unn-necessary text , just above the number data columns, its headers are mentioned. kindly guide is there any way to read such a file as a table and ultimately to a timetable. i am trying to save this zrx file in excel , but due to some data loss message, i am unable to proceed.

i am stuck here.any guidance would be a great help. here is the attached image of the file opened in notepad

[jonas]

Could you upload a sample set that is more difficult to parse? Also, what release are you using? Readtable has tons of importoptions but they keep changing with every release...

One way is to type

Opts = detectImportOptions(filename)

And pass the variable Opts as the second argument of readtable. It tends to help when parsing semi-difficult formats. For more complex formats you need to adapt the importoptions, which is also quite straight forward.

[bushra raza]

i am using Matlab R2017b. the data file is timm.zrx

[jonas]

Can you upload the file with headers and a few rows of data?

[bushra raza]

some of the initial chunk is in this attached file

[jonas]

This works for me

opts = detectImportOptions('data.txt');
T = readtable('data.txt', opts);

T =

43×3 table

         Var1         Var2    Var3
    ______________    ____    ____

    19991101000100    480      ''

it does give an extra variable, which can easily be removed by

T(:,3)=[];

It's quite difficult to give you code for readtable, because in my experience the same code does not necessarily transfer well to other matlab versions. I can give you a solution with textscan if the above solution does not work.

[bushra raza]

wow! it all worked fine on my heavy data file. thanx a lot .

[jonas]

My pleasure!

[bushra raza]

Hi, i have a question regarding timetables i have two timetables, i have synchronized them. both timetables have one column each. now i need to make a data column of their difference. is there any way? here is my code of timetables and synchronize

P_Data = table2timetable(P_Tbl); AP_Data = table2timetable(AP_Tbl);

BothPressure = synchronize(P_Data,AP_Data);

tr1 = timerange('12-Aug-2015','12-Nov-2015'); P = P_Data(tr1,'Pressure'); AP = AP_Data(tr1, 'Air_pressure');

i want to calculate : Pressure - Air_pressure

any advice ?

[jonas]

Sure, this should give you the difference, assuming you have two timetables TT1 and TT2.

TTdiff = TT1{:,1} - TT2{:,1};

You could also add this as a new column in your timetable:

TT1.diff = TT1{:,1} - TT2{:,1};

You can index tables in many different ways... What you need to remember is that accessing the content of a table is done through use of curly braces {}.

Actually, this is a general rule for MATLAB. Curly braces returns the content of whatever you put in whereas normal braces return the same class as you put in.

[bushra raza]

a great help indeed. thank full to you

 can you please also guide me: how can i save this calculation with timestamp in a separate data file or so?

[jonas]

Ive never done this, but you can probably use this

T = timetable2table(TT) 
writetable(T, 'file.txt')

where TT is your timetable.