How to Merge rows inside table where Security and Date are equal into one and add other columns

SECURITY = ["IBM";"IBM";"IBM";"IBM"];
DATE = ["01/01/2020";"01/01/2020";"01/01/2020";"02/01/2020"];
LASTPRICE=[missing;20;missing;missing];
PE = [missing;missing;7;missing];
PRICE = [5;missing;missing;7];
tbl = table(SECURITY,DATE,PRICE,PE,LASTPRICE);
tbl.DATE = datetime(tbl.DATE,'InputFormat',"MM/dd/uuuu")

You could use the groupsummary function. It doesn't quite do what you want, but it might be enough.

groupsummary(tbl,["SECURITY","DATE"],"sum")
ans = 
    SECURITY       DATE        GroupCount    sum_PRICE    sum_PE    sum_LASTPRICE
    ________    ___________    __________    _________    ______    _____________
     "IBM"      01-Jan-2020        3             5          7            20      
     "IBM"      01-Feb-2020        1             7          0             0      

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

请先登录，再进行评论。

Answer 2

Sai Gudlur 2020-6-12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/547314-how-to-merge-rows-inside-table-where-security-and-date-are-equal-into-one-and-add-other-columns#answer_450327

Hello,

Below code might work for u.

A = ['IBM';'IBM';'IBM'];

B = ['01/02/2020';'02/05/2020';'03/06/2020'];

C = [5;6;7];

D = [1;2;3];

E = [5;6;7];

T1 = table(A,B,C,D,E);

T2 = mergevars(T1,[3 4 5]);

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

请先登录，再进行评论。

Answer 3

Mark McGrath 2020-6-12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/547314-how-to-merge-rows-inside-table-where-security-and-date-are-equal-into-one-and-add-other-columns#answer_450342

Hello,

The columns need to NOT be merged. I just need to get rid of duplicate dates and essentially use MAX like in SQL to consolidate into one line. I can't figure out how to do it but new to table structure. I am used to cell ararys.

Also need the dupliacte lines after merged to be removed. So thinking maybe there is a self join functionality ?