How to fill-in a smaller table into a larger (empty) table with a sorted variable?

Question

Muha 2024-10-14

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2159525-how-to-fill-in-a-smaller-table-into-a-larger-empty-table-with-a-sorted-variable

移动：Cris LaPierre 2024-10-15

del.mat

I have extracted smaller tables from large data for further analysis. After extraction, the table needs to be sorted and has missing values. To address these two, I have made an empty table with the sorted variable (the channel 'variable'). I just want to shift the values of the extracted tables into the ref_table (empty table).

I have put a partial code for working below:

clc
clear all
close all
load("del.mat")
names = {T1,T2};
for i = 1:2
    ref_table = DummyTable;
    tab = names{i};
    % 1. tab sorted in ascending 'channel' variable and filled-in the reftable
    % 2. In second loop, the values of channel 5 are missing, and should be
    %    left as nan.
end

I can visually explain what I want to do in excel sheet screenshots below:

The red table is sortd first and then filled in the green table as below:

The missing values should be shown as nan, as shown in the next picture

Thank you.

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

请先登录，再进行评论。

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

Answer 1

Cris LaPierre 2024-10-14

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2159525-how-to-fill-in-a-smaller-table-into-a-larger-empty-table-with-a-sorted-variable#answer_1532170

在 MATLAB Online 中打开

del.mat

Joining doesn't appear to selectively replace values in one table. With the assumption that all channels only appear once in the small tables, here's an approach that doesn't use loops.

load del.mat
[~,r] = ismember(T1.channel,DummyTable.channel)
r = 8×1
     1
     2
     7
     3
     4
     8
     5
     6
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>
T1new = DummyTable;
T1new(r,:) = T1
T1new = 8x5 table
        Clock          tot      channel     TOA      Ampdb
    ______________    ______    _______    ______    _____

    {["10:32:14"]}    14.505       1       14.505     88  
    {["10:32:14"]}    14.505       2       14.505     80  
    {["10:32:14"]}    14.505       3       14.505     58  
    {["10:32:14"]}    14.505       4       14.505     70  
    {["10:32:14"]}    14.505       5       14.506     47  
    {["10:32:14"]}    14.505       6       14.506     58  
    {["10:32:14"]}    14.505       7       14.505     75  
    {["10:32:14"]}    14.505       8       14.505     64  
[~,r] = ismember(T2.channel,DummyTable.channel)
r = 7×1
     2
     1
     7
     3
     4
     8
     6
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>
T2new = DummyTable;
T2new(r,:) = T2
T2new = 8x5 table
        Clock          tot      channel     TOA      Ampdb
    ______________    ______    _______    ______    _____

    {["10:32:31"]}    31.962       1       31.962      74 
    {["10:32:31"]}    31.962       2       31.962      85 
    {["10:32:31"]}    31.962       3       31.962      55 
    {["10:32:31"]}    31.962       4       31.962      66 
    {0x0 double  }       NaN       5          NaN     NaN 
    {["10:32:31"]}    31.962       6       31.962      53 
    {["10:32:31"]}    31.962       7       31.962      69 
    {["10:32:31"]}    31.962       8       31.962      57 

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

Muha 2024-10-15

Dear @Cris LaPierre this is very concise. In two lines I can populate the local table into dummy table as I have already assigned dummy table at the start of loop. Thank you for an efficient solution.

请先登录，再进行评论。

Answer 2

Rahul 2024-10-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2159525-how-to-fill-in-a-smaller-table-into-a-larger-empty-table-with-a-sorted-variable#answer_1532200

在 MATLAB Online 中打开

Hi @Muha,

I believe you are trying to take extracted tables (T1, T2) and transfer their values into a reference table (ref_table) sorted by the 'channel' variable, while handling missing values (channels that are missing should remain NaN), here's how you can modify your code:

Sort the extracted table (tab) by the 'channel' variable.
Match the rows of the sorted table (tab) with the 'channel' values of the reference table (ref_table).
Transfer the values from the extracted table into the correct positions in the reference table.

clc
clear all
close all
% Load the data (del.mat contains T1, T2, and DummyTable)
load("del.mat")
% Names of the extracted tables
names = {T1, T2};
for i = 1:2
    % Reference empty table (DummyTable)
    ref_table = DummyTable;  
    
    % Current extracted table (T1 or T2)
    tab = names{i};
    
    % Sort the extracted table by the 'channel' variable in ascending order
    tab = sortrows(tab, 'channel');
    
    % Now, match 'channel' values of 'tab' with 'ref_table'
    for j = 1:height(tab)
        % Find the index of the current 'channel' in the ref_table
        channel_value = tab.channel(j);
        
        % Find the row in the ref_table where the 'channel' matches
        idx = find(ref_table.channel == channel_value);
        
        % If a matching channel is found, transfer the data
        if ~isempty(idx)
            % Transfer the corresponding data from tab to ref_table
            ref_table(idx, :) = tab(j, :);
        end
    end
    
    % Display the updated ref_table for the current table
    disp(['Updated ref_table for T', num2str(i)]);
    disp(ref_table);
end

For more information regarding the functions used in the given code snippet, refer to the folllowing documentation links:

Sort rows of matrix or table using "sortrows" function: https://www.mathworks.com/help/matlab/ref/double.sortrows.html

Best!

2 个评论
显示无隐藏无

Muha 2024-10-14

移动：Cris LaPierre 2024-10-15

Well, it works. Thank you. I thought there is some sort of outer/inner join function to sort things out. But this is great. I will use this as a function. Thank you for such a detailed explanation. If you put your solution in the answer, I will accept the answer. Thank you

Rahul 2024-10-15

移动：Cris LaPierre 2024-10-15

I'm glad you found it useful, thanks @Muha.

请先登录，再进行评论。

How to fill-in a smaller table into a larger (empty) table with a sorted variable?

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

采纳的回答

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

更多回答（1 个）

2 个评论
显示无隐藏无

另请参阅

标签

产品

版本

Community Treasure Hunt

How to fill-in a smaller table into a larger (empty) table with a sorted variable?

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

采纳的回答

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

更多回答（1 个）

2 个评论 显示 无隐藏 无

另请参阅

标签

产品

版本

Community Treasure Hunt

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

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

2 个评论
显示无隐藏无