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

MATLABmerge rows inside same table

SECURITY DATE PRICE PE LASTPRICE
IBM 01/01/2020 5
IBM 01/01/2020 20
IBM 01/01/2020 7
IBM 02/01/2020 7
How can i combine first three rows into one and leave last one remaing using one table?
so I should see
IBM 01/01/2020 5 7 20
IBM 02/01/2020 7

Best Answer

If you have your table in MATLAB already,
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
Related Question