How to merge two tables, keeping independent variable (x) the same but summing the dependent variable (y)

8 views (last 30 days)
Hello,
I am trying to merge and sum two datasets. I have year and catch data from two different locations and would like to combine them into one dataset where year remains constant but catch is summed for the coresponding year.

Accepted Answer

KSSV
KSSV on 15 Sep 2021
T1 = readtable('Confoss_landings.xls') ;
T2 = readtable('Mainefoss_landings.xls') ;
[idx,ia] = ismember(T2.(1),T1.(1)) ;
T = [T1 ;T2] ;
[year,ia,idx] = unique(T.(1),'stable');
xcatch = accumarray(idx,T.(2),[],@nanmean);
iwant = table(year,xcatch)

More Answers (1)

Stephen23
Stephen23 on 21 Sep 2021
Simpler:
T1 = readtable('Confoss_landings.xls', 'VariableNamingRule','preserve')
T1 = 47×3 table
year catch value ____ _____ _____ 1996 252 32 1995 14044 2108 1994 2000 300 1993 2440 367 1992 3200 480 1991 1200 180 1990 1150 173 1989 1600 240 1988 2100 315 1987 21400 3210 1986 40100 6015 1985 38900 5835 1984 32400 4860 1983 37500 6375 1982 41800 4183 1981 52700 6313
T2 = readtable('Mainefoss_landings.xls', 'VariableNamingRule','preserve')
T2 = 69×3 table
year catch value ____ __________ __________ 2020 NaN NaN 2019 NaN NaN 2018 NaN NaN 2017 NaN NaN 2016 NaN NaN 2015 NaN NaN 2014 NaN NaN 2013 NaN NaN 2012 NaN NaN 2011 NaN NaN 2010 1.3423e+06 2.9038e+05 2009 NaN NaN 2008 1.1705e+06 2.5283e+05 2007 NaN NaN 2006 1.2351e+06 2.3544e+05 2005 3.3677e+05 56196
T3 = outerjoin(T1,T2, 'Keys','year', 'MergeKeys',true);
T3.catch_total = sum([T3.catch_T1,T3.catch_T2],2,'omitnan')
T3 = 71×6 table
year catch_T1 value_T1 catch_T2 value_T2 catch_total ____ __________ ________ __________ ________ ___________ 1950 1.9479e+06 19584 3.1656e+06 28294 5.1135e+06 1951 4.899e+05 5270 3.4792e+06 26067 3.9691e+06 1952 1.0615e+06 14662 2.7832e+06 29763 3.8447e+06 1953 3.403e+05 4238 2.4431e+06 27529 2.7834e+06 1954 9.727e+05 13131 3.2961e+06 26643 4.2688e+06 1955 8.903e+05 12230 3.7786e+06 33024 4.6689e+06 1956 79400 676 4.5879e+06 41833 4.6673e+06 1957 63300 1257 3.9695e+06 32453 4.0328e+06 1958 9500 142 3.0954e+06 31841 3.1049e+06 1959 7800 105 1.6314e+06 18795 1.6392e+06 1960 20000 252 1.4123e+06 17609 1.4323e+06 1961 6000 72 1.8818e+06 26241 1.8878e+06 1962 19000 276 1.6822e+06 20644 1.7012e+06 1963 3400 57 1.4803e+06 21957 1.4837e+06 1964 14800 157 1.4796e+06 23543 1.4944e+06 1965 24100 348 3.1065e+06 43588 3.1306e+06

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!