Extracting 3 columns from a dataset into a new data set

Hi all, can someone please help on how I can combine columns Var1 and Var2 into 1 column named for example VarX then create a new data set that will consist of only varX and Var4?

Var1 Var2 Var3 Var4 Var5 Var6 Var7

__________ ____________ ____ ____ ____ ____ ____

19/09/2020 00:00:00.231 -0.6 -0.6 0.4 12.1 15.2

19/09/2020 00:00:10.220 -0.7 -0.6 0.4 12.1 15.2

19/09/2020 00:00:20.223 -0.6 -0.6 0.4 12.1 15.2

19/09/2020 00:00:30.230 -0.7 -0.6 0.4 12.1 15.2

19/09/2020 00:00:40.232 -0.6 -0.6 0.4 12.1 15.2

Answer 1

[Adam Danz]

Join a column of dates with a column of times - datetime format

Assuming you're working with datetime values in both columns, the safe way to combine the dates and times is to

1. convert the time values in column 2 to durations
2. round the dates in column 1 down to the start of each day since there could be time components in those datetime values that aren't displayed
3. Add the durations to the rounded dates

% Create demo table
rng('default') % for reproducibility
dates = datetime('19/09/2020','Format','dd/MM/yyyy') + zeros(5,1);
times = datetime('00:00:00.231','format','HH:mm:ss.SSS') + seconds(sort(rand(5,1)*10));
T = table(dates, times);

% Convert times to durations & remove any hidden date components
justTime = T.times - dateshift(T.times,'start','day');
% Round the dates down to the start of each day, removing any 
% hidden time components, and add the time durations
fullDateTime = dateshift(T.dates,'start','day') + justTime;
fullDateTime.Format = 'dd/MM/yyyy HH:mm:ss.SSS';

% add to table
T.DateTime = fullDateTime

T = 5×3 table
      dates          times               DateTime        
    __________    ____________    _______________________

    19/09/2020    00:00:01.500    19/09/2020 00:00:01.500
    19/09/2020    00:00:06.554    19/09/2020 00:00:06.554
    19/09/2020    00:00:08.378    19/09/2020 00:00:08.378
    19/09/2020    00:00:09.288    19/09/2020 00:00:09.288
    19/09/2020    00:00:09.364    19/09/2020 00:00:09.364

Join a column of dates with a column of durations

If your time column does not contain datetime value but contains duration values instead, you can skip a step the process above,

% Create demo table
rng('default') % for reproducibility
dates = datetime('19/09/2020','Format','dd/MM/yyyy') + zeros(5,1);
durations = duration('00:00:00.231','format','hh:mm:ss.SSS') + seconds(sort(rand(5,1)*10));
T = table(dates, durations);

% Round the dates down to the start of each day, removing any 
% hidden time components, and add the time durations
fullDateTime = dateshift(T.dates,'start','day') + T.durations;
fullDateTime.Format = 'dd/MM/yyyy HH:mm:ss.SSS';

% add to table
T.DateTime = fullDateTime

T = 5×3 table
      dates        durations             DateTime        
    __________    ____________    _______________________

    19/09/2020    00:00:01.500    19/09/2020 00:00:01.500
    19/09/2020    00:00:06.554    19/09/2020 00:00:06.554
    19/09/2020    00:00:08.378    19/09/2020 00:00:08.378
    19/09/2020    00:00:09.288    19/09/2020 00:00:09.288
    19/09/2020    00:00:09.364    19/09/2020 00:00:09.364

[Remember Samu]

Hi Adam, thank you so much, this resolves the first issue.

[Remember Samu]

Yes thanks, I got that.

[Remember Samu]

Hi Adam, the durations are different . From your output it looks like you are logging in 1-second intervals of which on the data I provided I am logging in 10-second data. Could you please help with that.

[Remember Samu]

Hi Adam, so the following is the code I am using and I am getting an error, I will also attach the data I am using in which I will only require the DNI and the full date as columns of my refernce data. I have attached the txt file im reading the data from .

t = readtable('E:\Data\2020\B220_DT80\20200724T000000.txt');
t(1:8642,:);
height(t);
% Create reference data table
rng('default') % for reproducibility
dates = datetime('23/07/2020','Format','dd/MM/yyyy') + zeros(8642,1);
times = datetime('00:00:00.228','format','HH:mm:ss.SSS') + seconds(sort(rand(8642,1)*10));
T = table(dates, times);
% Convert times to durations & remove any hidden date components
justTime = T.times - dateshift(T.times,'start','day');
% Round the dates down to the start of each day, removing any 
% hidden time components, and add the time durations
fullDateTime = dateshift(T.dates,'start','day') + justTime;
fullDateTime.Format = 'dd/MM/yyyy HH:mm:ss.SSS';
% add to table
T.DateTime = fullDateTime;
T.DNI = t.Var4;
% t.dt = t{:,1}+ t{:,2};
refdata = [T.DateTime, T.DNI];%by number
% dnivec = t.Var4;    %by name

error:
Error using datetime/horzcat (line 1341)
All inputs must be datetimes or date/time character vectors or
date/time strings.

Error in ValidationScriptupdated (line 25)
refdata = [T.DateTime, T.DNI];%by number

[Adam Danz]

Try this instead,

refdata = T(:,{'DateTime','DNI'})

> Hi Adam, the durations are different . From your output it looks like you are logging in 1-second intervals of which on the data I provided I am logging in 10-second data. Could you please help with that.

I don't understand the questions. The solution doesn't depend on the temporal intervals.

[Remember Samu]

Hi Adam, What I meant is there is a difference in the corresponding values to your times column and my Var2 column. as shown below:

your T table>>

dates times DateTime

__________ ____________ _______________________

19/09/2020 00:00:01.500 19/09/2020 00:00:01.500

19/09/2020 00:00:06.554 19/09/2020 00:00:06.554

19/09/2020 00:00:08.378 19/09/2020 00:00:08.378

19/09/2020 00:00:09.288 19/09/2020 00:00:09.288

19/09/2020 00:00:09.364 19/09/2020 00:00:09.364

my table>>

Var1 Var2 Var3 Var4 Var5 Var6 Var7

__________ ____________ ____ ____ ____ ____ ____

19/09/2020 00:00:00.231 -0.6 -0.6 0.4 12.1 15.2

19/09/2020 00:00:10.220 -0.7 -0.6 0.4 12.1 15.2

19/09/2020 00:00:20.223 -0.6 -0.6 0.4 12.1 15.2

19/09/2020 00:00:30.230 -0.7 -0.6 0.4 12.1 15.2

19/09/2020 00:00:40.232 -0.6 -0.6 0.4 12.1 15.2

Could you please help in making your times values to be exactly as Var2 values.

[Adam Danz]

My times values are random. They are for demo purposes. Don't you already have the Var2 data?

If you need to generate duration values,

durations = duration('00:00:00.231','format','hh:mm:ss.SSS')

[Remember Samu]

Thank you so much, I already have Var2 values, please check the attachment in this response. This is the text file im reading from and just trying to extract the date, times and DNI values(Var4).

[Adam Danz]

It looks like you're reading the data correctly using

t = readtable('E:\Data\2020\B220_DT80\20200724T000000.txt');

where t.Var2 are duration values so I still don't understand what the problem is.

[Remember Samu]

yes I am reading the data correctly, but the date and the time are in 2 separate columns of which in the analysis I am doing, i need them to be in one column.

[Adam Danz]

My answer shows how to combine the dates and times. The 3rd column in my example is a combination of the first and second columns. Did you manage to implement this with your data?

[Remember Samu]

Hi Adam, I am failing to implement this with my data, i have seen thats what you did which is exactly I want to do with my data, but im failing to implement, the dateshift function keeps bringing an error, could you please help me with a code that would perform this task using my data.

[Adam Danz]

If this is the error message you're referring to, then you're not failing to implement the code because that error happens after you successfully implement it.

Here's the first few lines of the table when I run your code with your data,

head(T)
ans =
  8×4 table
      dates          times               DateTime            DNI 
    __________    ____________    _______________________    ____
    23/07/2020    00:00:00.228    23/07/2020 00:00:00.228    -0.8
    23/07/2020    00:00:00.230    23/07/2020 00:00:00.230    -0.8
    23/07/2020    00:00:00.230    23/07/2020 00:00:00.230    -0.7
    23/07/2020    00:00:00.231    23/07/2020 00:00:00.231    -0.7
    23/07/2020    00:00:00.231    23/07/2020 00:00:00.231    -0.7
    23/07/2020    00:00:00.231    23/07/2020 00:00:00.231    -0.8
    23/07/2020    00:00:00.233    23/07/2020 00:00:00.233    -0.7
    23/07/2020    00:00:00.234    23/07/2020 00:00:00.234    -0.7

I don't know what this part of your code is supposed to be doing after the table is correctly updated:

T.DNI = t.Var4;
% t.dt = t{:,1}+ t{:,2};
refdata = [T.DateTime, T.DNI];%by number  % ERROR HERE
% dnivec = t.Var4;    %by name

If you're trying to generate a NEW table with just the DateTime and DNI variables, then follow the advice I gave in this comment.

NEW_TABLE = T(:,{'DateTime','DNI'});

And the first few lines of the result,

head(NEW_TABLE)
ans =
  8×2 table
           DateTime            DNI 
    _______________________    ____
    23/07/2020 00:00:00.228    -0.8
    23/07/2020 00:00:00.230    -0.8
    23/07/2020 00:00:00.230    -0.7
    23/07/2020 00:00:00.231    -0.7
    23/07/2020 00:00:00.231    -0.7
    23/07/2020 00:00:00.231    -0.8
    23/07/2020 00:00:00.233    -0.7
    23/07/2020 00:00:00.234    -0.7

[Remember Samu]

My code is as follows and It is not correct I think because it is saying incorect arguments using dateshift:

t = readtable('E:\Data\2020\B220_DT80\20200724T000000.txt');

t(1:8642,:);

height(t);

% Create reference data table

rng('default') % for reproducibility

dates = t.Var1;

times = t.Var2;

T = table(dates, times);

% Convert times to durations & remove any hidden date components

justTime = T.times - dateshift(T.times,'start','day');

% Round the dates down to the start of each day, removing any

% hidden time components, and add the time durations

fullDateTime = dateshift(T.dates,'start','day') + justTime;

fullDateTime.Format = 'dd/MM/yyyy HH:mm:ss.SSS';

% add to table

T.DateTime = fullDateTime;

T.DNI = t.Var4;

refdata = T(:,{'DateTime','DNI'});%by number

[Remember Samu]

this is the error:

Check for missing argument or incorrect argument data type

in call to function 'dateshift'.

Error in ValidationScriptupdated (line 16)

justTime = T.times - dateshift(T.times,'start','day');

>>

[Adam Danz]

That error is very different from the error you shared eariler. It's critical to share the entire error message when you're trying to fix the error. Otherwise, we're spending time on the wrong problems.

I also don't konw what version of Matlab you're using which makes it difficult to rule out any version differences.

It looks like you're trying to shift the dates of duration values rather than datetime values. You're using the first method of my answer which assumes the time values are datetime but instead you should be using the second method in my answer which assumes the time values are durations.

[Remember Samu]

Hi Adam, Thank you so much, I used the second method, It worked!!!