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

1 ビュー (過去 30 日間)
Samantha Farquhar
Samantha Farquhar 2021 年 9 月 15 日
回答済み: Stephen 2021 年 9 月 21 日
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.

採用された回答

KSSV
KSSV 2021 年 9 月 15 日
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)

その他の回答 (1 件)

Stephen
Stephen 2021 年 9 月 21 日
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

製品


リリース

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by