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

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

Answer 1

[Cris LaPierre]

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      

Answer 2

[Sai Gudlur]

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]);

Answer 3

[Mark McGrath]

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 ?