How to combine two tables using sorted common variable?

7 views (last 30 days)
UH
UH on 25 Mar 2023
Edited: Vilém Frynta on 25 Mar 2023
I have 2 datasets "Data_A" and "Data_B"
a = load('data_A.mat'); a = a.data_A;
b = load('data_B.mat'); b = b.data_B;
I have one common variable "time" (the first column in both tables) in ascending order. I want to make another table which has the combined effect of time in data_A and data_B and the corresponding variables sorted in with the indices of the sorted 'time' variable. I tried to make union but union does not allow repitition. I can show what I desired in the screenshot I made in excel sheet below (excel sheet is attached too):
I shall be thankful for the help.

Sign in to answer this question.

Accepted Answer

Stephen23
Stephen23 on 25 Mar 2023
Edited: Stephen23 on 25 Mar 2023
Ran in:
You could use OUTERJOIN:
https://www.mathworks.com/help/matlab/ref/table.outerjoin.html
a = load('data_A.mat'); a = a.data_A
a = 10×5 table
time x y z Eng ______ _____ _____ _____ __________ 723.1 15.75 56.48 120.6 2.931e+06 723.1 15.75 56.48 120.6 2.16e+06 723.1 15.75 56.48 120.6 1.464e+06 723.1 15.75 56.48 120.6 7.816e+05 781.61 14.64 106 82.32 1128 781.61 14.64 106 82.32 601.6 781.61 14.64 106 82.32 627.12 781.61 14.64 106 82.32 230.45 817.52 64.89 100.6 151 4.658e+06 817.52 64.89 100.6 151 8.3076e+05
b = load('data_B.mat'); b = b.data_B
b = 92×5 table
time2 L1 L2 D_legacy str _____ _____ ______ ________ ______ 700 0.856 7.2217 4.38 623.81 701 0.862 7.2723 4.38 629.52 702 0.872 7.3567 4.38 633.33 703 0.87 7.3398 4.419 639.05 704 0.864 7.2892 4.422 639.05 705 0.864 7.2892 4.428 638.1 706 0.866 7.3061 4.449 638.1 707 0.862 7.2723 4.437 638.1 708 0.864 7.2892 4.455 638.1 709 0.862 7.2723 4.455 638.1 711 0.866 7.3061 4.461 638.1 712 0.868 7.323 4.47 639.05 713 0.872 7.3567 4.47 640 714 0.878 7.4073 4.47 643.81 715 0.888 7.4917 4.467 648.57 716 0.896 7.5592 4.467 651.43
T = outerjoin(a,b, 'LeftKeys','time', 'RightKeys','time2', 'MergeKeys',true)
T = 102×9 table
time_time2 x y z Eng L1 L2 D_legacy str __________ ___ ___ ___ ___ _____ ______ ________ ______ 700 NaN NaN NaN NaN 0.856 7.2217 4.38 623.81 701 NaN NaN NaN NaN 0.862 7.2723 4.38 629.52 702 NaN NaN NaN NaN 0.872 7.3567 4.38 633.33 703 NaN NaN NaN NaN 0.87 7.3398 4.419 639.05 704 NaN NaN NaN NaN 0.864 7.2892 4.422 639.05 705 NaN NaN NaN NaN 0.864 7.2892 4.428 638.1 706 NaN NaN NaN NaN 0.866 7.3061 4.449 638.1 707 NaN NaN NaN NaN 0.862 7.2723 4.437 638.1 708 NaN NaN NaN NaN 0.864 7.2892 4.455 638.1 709 NaN NaN NaN NaN 0.862 7.2723 4.455 638.1 711 NaN NaN NaN NaN 0.866 7.3061 4.461 638.1 712 NaN NaN NaN NaN 0.868 7.323 4.47 639.05 713 NaN NaN NaN NaN 0.872 7.3567 4.47 640 714 NaN NaN NaN NaN 0.878 7.4073 4.47 643.81 715 NaN NaN NaN NaN 0.888 7.4917 4.467 648.57 716 NaN NaN NaN NaN 0.896 7.5592 4.467 651.43
Checking the rows of interest:
T(20:29,:)
ans = 10×9 table
time_time2 x y z Eng L1 L2 D_legacy str __________ _____ _____ _____ _________ _____ ______ ________ ______ 721 NaN NaN NaN NaN 0.9 7.5929 4.53 662.86 722 NaN NaN NaN NaN 0.898 7.5761 4.545 661.9 723 NaN NaN NaN NaN 0.898 7.5761 4.53 661.9 723.1 15.75 56.48 120.6 2.931e+06 NaN NaN NaN NaN 723.1 15.75 56.48 120.6 2.16e+06 NaN NaN NaN NaN 723.1 15.75 56.48 120.6 1.464e+06 NaN NaN NaN NaN 723.1 15.75 56.48 120.6 7.816e+05 NaN NaN NaN NaN 724 NaN NaN NaN NaN 0.898 7.5761 4.542 662.86 725 NaN NaN NaN NaN 0.898 7.5761 4.548 661.9 726 NaN NaN NaN NaN 0.898 7.5761 4.557 660.95

More Answers (1)

Vilém Frynta
Vilém Frynta on 25 Mar 2023
Edited: Vilém Frynta on 25 Mar 2023
Ran in:
hi,
i will try. it definitely can be more automated, i made it semi-automated, but it works! hope i helped!
well, i tried. Stephen was faster, his solution is much more elegant, less engineering, more Matlab.
% load the data
A = load("data_A.mat");
B = load("data_B.mat");
A = A.data_A;
B = B.data_B;
% creating a new table, with time only, sorted
t = sort([A.time; B.time2]);
T = table();
T.time = t;
% find indexes of the numbers from table A and add their values
[val, idx] = intersect(T.time,A.time);
T.x(idx) = A.x; % create column X and add values from X to corresponding rows
T.y(idx) = A.y;
T.z(idx) = A.z;
T.Eng(idx) = A.Eng;
% now do the same for table B
[val, idx] = intersect(T.time,B.time2);
T.L1(idx) = B.L1;
T.L2(idx) = B.L2;
T.D_legacy(idx) = B.D_legacy;
T.str(idx) = B.str;
% display the table (scroll-wheel to inspect it all)
disp(T)
time x y z Eng L1 L2 D_legacy str ______ _____ _____ _____ __________ _____ ______ ________ ______ 700 0 0 0 0 0.856 7.2217 4.38 623.81 701 0 0 0 0 0.862 7.2723 4.38 629.52 702 0 0 0 0 0.872 7.3567 4.38 633.33 703 0 0 0 0 0.87 7.3398 4.419 639.05 704 0 0 0 0 0.864 7.2892 4.422 639.05 705 0 0 0 0 0.864 7.2892 4.428 638.1 706 0 0 0 0 0.866 7.3061 4.449 638.1 707 0 0 0 0 0.862 7.2723 4.437 638.1 708 0 0 0 0 0.864 7.2892 4.455 638.1 709 0 0 0 0 0.862 7.2723 4.455 638.1 711 0 0 0 0 0.866 7.3061 4.461 638.1 712 0 0 0 0 0.868 7.323 4.47 639.05 713 0 0 0 0 0.872 7.3567 4.47 640 714 0 0 0 0 0.878 7.4073 4.47 643.81 715 0 0 0 0 0.888 7.4917 4.467 648.57 716 0 0 0 0 0.896 7.5592 4.467 651.43 717 0 0 0 0 0.91 7.6773 4.47 661.9 718 0 0 0 0 0.9 7.5929 4.521 663.81 720 0 0 0 0 0.9 7.5929 4.527 663.81 721 0 0 0 0 0.9 7.5929 4.53 662.86 722 0 0 0 0 0.898 7.5761 4.545 661.9 723 0 0 0 0 0.898 7.5761 4.53 661.9 723.1 15.75 56.48 120.6 2.931e+06 0 0 0 0 723.1 15.75 56.48 120.6 2.16e+06 0 0 0 0 723.1 15.75 56.48 120.6 1.464e+06 0 0 0 0 723.1 15.75 56.48 120.6 7.816e+05 0 0 0 0 724 0 0 0 0 0.898 7.5761 4.542 662.86 725 0 0 0 0 0.898 7.5761 4.548 661.9 726 0 0 0 0 0.898 7.5761 4.557 660.95 727 0 0 0 0 0.9 7.5929 4.56 661.9 728 0 0 0 0 0.906 7.6436 4.557 662.86 730 0 0 0 0 0.916 7.7279 4.557 665.71 731 0 0 0 0 0.928 7.8292 4.557 671.43 732 0 0 0 0 0.934 7.8798 4.557 675.24 733 0 0 0 0 0.928 7.8292 4.611 676.19 734 0 0 0 0 0.926 7.8123 4.602 675.24 735 0 0 0 0 0.93 7.846 4.605 673.33 737 0 0 0 0 0.928 7.8292 4.632 671.43 738 0 0 0 0 0.926 7.8123 4.626 664.76 739 0 0 0 0 0.924 7.7954 4.638 661.9 740 0 0 0 0 0.926 7.8123 4.641 659.05 741 0 0 0 0 0.93 7.846 4.647 654.29 742 0 0 0 0 0.934 7.8798 4.647 653.33 743 0 0 0 0 0.938 7.9135 4.647 653.33 744 0 0 0 0 0.95 8.0148 4.644 654.29 745 0 0 0 0 0.96 8.0991 4.647 650.48 746 0 0 0 0 0.968 8.1666 4.644 638.1 747 0 0 0 0 0.958 8.0823 4.686 611.43 749 0 0 0 0 0.962 8.116 4.71 598.1 750 0 0 0 0 0.956 8.0654 4.689 588.57 751 0 0 0 0 0.954 8.0485 4.698 580.95 752 0 0 0 0 0.958 8.0823 4.713 575.24 753 0 0 0 0 0.954 8.0485 4.719 569.52 754 0 0 0 0 0.958 8.0823 4.719 562.86 755 0 0 0 0 0.956 8.0654 4.731 555.24 756 0 0 0 0 0.96 8.0991 4.734 549.52 757 0 0 0 0 0.964 8.1329 4.737 544.76 758 0 0 0 0 0.97 8.1835 4.737 540.95 759 0 0 0 0 0.978 8.251 4.737 539.05 760 0 0 0 0 0.988 8.3354 4.734 532.38 761 0 0 0 0 0.992 8.3691 4.734 520.95 763 0 0 0 0 0.988 8.3354 4.77 506.67 764 0 0 0 0 0.99 8.3522 4.782 497.14 765 0 0 0 0 0.99 8.3522 4.779 487.62 766 0 0 0 0 0.98 8.2679 4.782 480 767 0 0 0 0 0.986 8.3185 4.803 475.24 768 0 0 0 0 0.988 8.3354 4.818 471.43 769 0 0 0 0 0.984 8.3016 4.824 467.62 770 0 0 0 0 0.986 8.3185 4.824 463.81 772 0 0 0 0 0.99 8.3522 4.821 460.95 773 0 0 0 0 0.992 8.3691 4.824 460 774 0 0 0 0 1 8.4366 4.824 459.05 775 0 0 0 0 1.008 8.5041 4.827 459.05 776 0 0 0 0 1.024 8.6391 4.827 459.05 777 0 0 0 0 1.028 8.6728 4.857 457.14 778 0 0 0 0 1.02 8.6053 4.872 451.43 780 0 0 0 0 1.018 8.5885 4.872 447.62 781 0 0 0 0 1.018 8.5885 4.884 444.76 781.61 14.64 106 82.32 1128 0 0 0 0 781.61 14.64 106 82.32 601.6 0 0 0 0 781.61 14.64 106 82.32 627.12 0 0 0 0 781.61 14.64 106 82.32 230.45 0 0 0 0 782 0 0 0 0 1.018 8.5885 4.884 441.9 783 0 0 0 0 1.018 8.5885 4.896 440.95 784 0 0 0 0 1.018 8.5885 4.902 440 785 0 0 0 0 1.024 8.6391 4.905 439.05 786 0 0 0 0 1.028 8.6728 4.902 439.05 787 0 0 0 0 1.034 8.7234 4.905 440.95 788 0 0 0 0 1.046 8.8247 4.905 448.57 789 0 0 0 0 1.064 8.9765 4.905 449.52 791 0 0 0 0 1.052 8.8753 4.959 439.05 792 0 0 0 0 1.05 8.8584 4.941 434.29 793 0 0 0 0 1.052 8.8753 4.971 431.43 794 0 0 0 0 1.05 8.8584 4.962 428.57 795 0 0 0 0 1.048 8.8416 4.965 424.76 796 0 0 0 0 1.048 8.8416 4.977 422.86 797 0 0 0 0 1.05 8.8584 4.989 421.9 798 0 0 0 0 1.048 8.8416 4.989 420 799 0 0 0 0 1.05 8.8584 4.995 419.05 800 0 0 0 0 1.054 8.8922 4.998 419.05 817.52 64.89 100.6 151 4.658e+06 0 0 0 0 817.52 64.89 100.6 151 8.3076e+05 0 0 0 0

Categories

Find more on Tables in Help Center and File Exchange

Tags

Products


Release

R2023a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!