Compare dates between a matrix and a given range and read the values

I have some Text file where values (>thousands) are wriiten like this:-

Dates Time Values1 Values2 Values3

31/03/2021 15:01:34 56.45 89.85324 1000.98

31/03/2021 15:06:34 78.34 90.75836 1000.99

1/04/2021 9:01:34 60.29 72.89434 1001.50

2/03/2021 13:01:34 72.56 60.35986 1001.68

..... Upto thousnds of values

I want to check this file with a date range like (31/03/2021 15:01:34 to 31/03/21 to 15:06:34) or (1/04/2021 to 2/03/201)

and if this range is available I read those values from their corresponding tables.

Is there any short method to do this.

Please help me in this matter.

Answer 1

[Adam Danz]

1. read in the text file as a timetable or a regular table. The date values should be read in as datetime values.
2. use boolean operators to determine which rows have dates within your range. That should create a logical vector with one element per row. Alternatively, if you're working with a timetable you can use one of these methods .
3. Use that logical index to get data from other columns.

There's lots of info on each of those steps in the documentation and the forum. If you get stuck update us on where you're at and what the problem is.

[acun67 acu]

ok. I can read those file . I can separte every values with a differnt column and store them in a vector file . like

a=[31/03/2021

31/03/2021

1/04/2021

..... ]

b=[15:01:34

15:06:34

9:01:34

...... ]

c=[56.45

78.34

60.29]

but I could not compare them with a data range and could not take there corresponding values.

[Adam Danz]

Example:

% dt is a vector of datetime values
% a and b are dates
idx = dt>=a & dt<b;

To round datetime values to the nearest hour, minute, day etc, see Dateshift.

[Adam Danz]

How/why did you separate the columns into separate variables? That's not what I advised. See step 1.

[acun67 acu]

I was using idx , isember . not helping

how could I compare one vector with a single value!

should i convert dates to number or character! how could i check date/time is Greater than or less than to previous!

and how could I take the corresponding values if date/times are match.

[acun67 acu]

ok I got your point. I shall take date/time in a table.

after that how could I compare one vector with a single value!

should i convert dates to number or character! how could i check date/time is Greater than or less than to previous!

and how could I take the corresponding values if date/times are match.

[Adam Danz]

> should i convert dates to number or character!

No. As step 1 suggests, the dates should be read in as datetime values.

>how could I compare one vector with a single value!

Shown above

[acun67 acu]

thanks for your answer and time.

last one, how could I take corresponding values from the another table!

[Adam Danz]

Do you mean from another column of the same table?

T = table(datetime(2020,03,01)-days(0:5)',randi(50,6,1), randi(10,6,1))

T = 6×3 table
       Var1        Var2    Var3
    ___________    ____    ____

    01-Mar-2020     31       2 
    29-Feb-2020     38       4 
    28-Feb-2020     28      10 
    27-Feb-2020     18       1 
    26-Feb-2020     27       9 
    25-Feb-2020     33       4 

idx = T.Var1 <= datetime(2020,2,28) & T.Var1 >datetime(2020,2,26)

idx = 6×1 logical array
   0
   0
   1
   1
   0
   0

T.Var2(idx)

ans = 2×1

28 18

T(idx,[2:end])

ans = 2×2 table
    Var2    Var3
    ____    ____

     28      10 
     18       1 

[acun67 acu]

Yes values from same file but store in as a separete vector (like Var2) .

But I can make it as a single table too and date/time in one column and values in another column.

and thank you, I think you solved this problem for me. I want this 'Var2', and 'Var3' only. previouly I was doing it using two three loops.

Answer 2

[DGM]

This is not a real answer, but my preference for very large files. I occasionally have to deal with csv files that are several million lines long. Reading all that into memory and trying to parse date strings is expensive. I find that it's much faster to split the file externally and then only deal with the chunk that's necessary.

You'll need to be familiar with how your file is formatted, but this is an example. My lines start with a date, so:

startdate='02/28/2021'; % include this date
stopdate='03/01/2021'; % exclude this date; set to '' to read up to last line
logfile='/path/to/my/giant/logfile.log';
tempfile='/dev/shm/tempms.log'; % don't need to touch the disk
delimiter=',';

% prepare temporary file
[~,b]=system(['wc -l ' logfile ' | cut -d '' '' -f 1']);
totallc=str2double(b);
[~,b]=system(['grep -n ' startdate ' ' logfile ' 2>/dev/null | head -n 1 | cut -d '':'' -f 1']);
startline=str2double(b)-1;
system(['tail -n ' num2str(totallc-startline) ' ' logfile ' > ' tempfile]);

if ~isempty(stopdate)
	[~,b]=system(['grep -n ' stopdate ' ' tempfile ' 2>/dev/null | head -n 1 | cut -d '':'' -f 1']);
	stopline=str2double(b)-1;
	system(['head -n ' num2str(stopline) ' ' tempfile ' | sponge ' tempfile]);
end

% now you can read the temp file instead of the whole log.  

Of course, this is in bash, but the idea is the same in other environments. I could have made this neater by writing a bash script to do the job and just calling it from within my m-file. You don't have to do everything in Matlab.

[acun67 acu]

Yes this is another way.

But how could i compare date/time from this temp file, and how can i take corresponding values from another temp file.

[DGM]

Matching a time string might be more problematic, as this expects the test string to actually exist in the file. It doesn't parse anything and compare to see if the time entries are within a range. If the entry interval is small, you might be reasonably assured to find an hour or minute match, but that's otherwise a limitation.

[Walter Roberson]

wc implies unix / linux, and if you have that then split can often be useful.

[DGM]

oof. Yeah, that would probably simplify it.