How to extract the values of two datasets from the same date/time from two table ?

Question

ehsan 2018-5-25

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/402633-how-to-extract-the-values-of-two-datasets-from-the-same-date-time-from-two-table

编辑： Akira Agata 2018-5-29

Dear all.

I have two big data sets with .mat format, each contains a table (an example of my data can be seen below). My real data are attached.

I would like to extract the values from the same date/time and also when the value of the third column of the second table is 40.

I am getting a little bit confusing to do all steps together.

     date         time       tem      wind            
    __________    ________    ______    ____    
    2015.06.05    05:10:00    16.677    0.74
    2015.06.05    05:20:00    16.773    1.67
    2015.06.05    05:30:00    16.915     1.3
    2015.06.05    05:40:00    17.534    0.93
    2015.06.05    05:50:00      18.2    0.37 
       t            z        height    speed
    __________    ________    ______    _____
    2016-02-01    10:00:00    40        0.93
    2016-02-01    10:00:00    50        NaN  
    2016-02-01    10:00:00    60        0.93
    2016-02-01    10:00:00    70        1.65
    2016-02-01    10:00:00    80        2.03

I know how to extract the data of the second table when the values of the third column is 40.

 height = 40 ; 
 k = sodar_wind{:,3}== height ; 
 date = sodar_wind{:,1}(k);
 time = sodar_wind{:,2}(k); 
 high = sodar_wind{:,3}(k); 
 Wind = sodar_wind{:,4}(k);

I appreciate if you could assist.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Greg 2018-5-27

Try ismember or intersect.

请先登录，再进行评论。

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

Answer 1

Akira Agata 2018-5-29

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/402633-how-to-extract-the-values-of-two-datasets-from-the-same-date-time-from-two-table#answer_322306

编辑：Akira Agata 2018-5-29

在 MATLAB Online 中打开

Looking at your data, year-month-day and hour-minute-second information are stored in different columns. In addition, data format for each table are not unified. So, I would recommend adjusting and unifying datetime data for each table before processing. The following is one example.

% Read the data
load('ZEL01.mat');
load('sodar_data.mat');
% Extract rows where "sodar_data.height == 40"
idx = sodar_data.height == 40;
sodar_data(~idx,:) = [];
% Unify datetime format for each table 
ZEL01.date.Hour = ZEL01.time.Hour;
ZEL01.date.Minute = ZEL01.time.Minute;
ZEL01.date.Second = ZEL01.time.Second;
ZEL01.date.Format = 'yyyy-MM-dd HH:mm:ss';
ZEL01.time = [];
sodar_data.t.Hour = sodar_data.z.Hour;
sodar_data.t.Minute = sodar_data.z.Minute;
sodar_data.t.Second = sodar_data.z.Second;
sodar_data.t.Format = 'yyyy-MM-dd HH:mm:ss';
sodar_data.z = [];
% Extract the target data
T = innerjoin(ZEL01,sodar_data,'LeftKeys','date','RightKeys','t');
idx = T.height == 40;
T(~idx,:) = [];

The result looks like:

>> head(T)
ans =
8×5 table
         date             tem      wind    height    speed
  ___________________    ______    ____    ______    _____
  2016-02-01 10:00:00     6.204    0.56      40       NaN 
  2016-02-01 10:30:00      7.67       0      40       NaN 
  2016-02-01 11:00:00     8.593    0.37      40       NaN 
  2016-02-01 11:30:00    10.907    0.56      40       NaN 
  2016-02-01 12:00:00    13.882    7.79      40       NaN 
  2016-02-01 12:30:00    14.194    7.79      40       NaN 
  2016-02-01 13:00:00    13.882    8.35      40       NaN 
  2016-02-01 13:30:00     13.57    8.53      40       NaN