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

3 次查看（过去 30 天）
Mark McGrath
Mark McGrath 2020-6-12
回答： Mark McGrath 2020-6-12
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

请先登录，再回答此问题。

采纳的回答

Cris LaPierre
Cris LaPierre 2020-6-12
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

更多回答（2 个）

Sai Gudlur
Sai Gudlur 2020-6-12
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]);
Mark McGrath
Mark McGrath 2020-6-12
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 ?

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2020a

Community Treasure Hunt

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

Start Hunting!

Translated by