Extracting 3 columns from a dataset into a new data set

Question

Remember Samu 2021-5-10

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/826540-extracting-3-columns-from-a-dataset-into-a-new-data-set

评论： Remember Samu 2021-5-12

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Adam Danz 2021-5-10

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/826540-extracting-3-columns-from-a-dataset-into-a-new-data-set#answer_696570

编辑：Adam Danz 2021-5-10

在 MATLAB Online 中打开

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

convert the time values in column 2 to durations
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
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

17 个评论
显示 15更早的评论隐藏 15更早的评论

Remember Samu 2021-5-11

编辑：Remember Samu 2021-5-11

在 MATLAB Online 中打开

20200724T000000.txt

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

Remember Samu 2021-5-11

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 2021-5-11

在 MATLAB Online 中打开

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

Adam Danz 2021-5-11

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 2021-5-12