MATLAB Answers

0

Is it possible to split a table into multiple tables based in ID (a number code) in colum A?

Asked by Kristian Opsahl on 17 Jan 2019
Latest activity Commented on by Kristian Opsahl on 24 Jan 2019
I have a huge .csv file with stock information. Its over 1 million rows. Right now each company has a number ID variable in colum A, and additional daily stock information in 19 columns from B and onwards. What I want is to be able to split the combined table into x amount of seperate tables so that each company are seperated and containing all the 19 variables per day. Is that possible?
I imported the file with readtable
Thank you for any help

  0 Comments

Sign in to comment.

3 Answers

Answer by Walter Roberson
on 17 Jan 2019
 Accepted Answer

Yes, definitely.
filename = 'AppropriateFileName.csv'
T = readtable(filename);
G = findgroups(T{:,1}); %first column
Tc = splitapply( @(varargin) varargin, T, G);
Now Tc is a cell array of table objects, one cell entry for each unique identifier from the first column.
I use a bit of a hack here to do the cell array conversion. When you call a function with arguments, and the function uses the special parameter name varargin, then within the function you can refer to the entire set of arguments from that position onwards, as the cell array varargin, with the first entry being the first parameter there, the second being the second parameter there, and so on. within the @(varargin) anonymous function, whatever was passed in as potentially multiple arguments can be referred to as the cell varargin. And what I return from the function is varargin -- which is to say the cell array of arguments. The effect is the same as
@(var1, var2, var3, ... varN) {var1, var2, var3, ..., varN}
but you do not need to worry about how many arguments there were.
The net result is to wrap each of the partitioned table sections into a cell.

  1 Comment

Thank you for your reply Walter. Helped me very much.

Sign in to comment.


Answer by Peter Perkins
on 23 Jan 2019

Kristian, as Walter demonstrates, it is easy to do. But you may find that splitting it up is less convenient than keeping it together, depending on what you need to do after. There are several different ways to do "grouped calculations" on the not-split-up table, those would be tedious if you had dozens of separate tables.

  1 Comment

Good point indeed Peter, but I want to try and split it regardless incase it would fit my needs better than keeping it as is or in worst case just to have the knowledge of how to do it for future cases.

Sign in to comment.


Answer by Kevin Chng on 17 Jan 2019
Edited by Kevin Chng on 17 Jan 2019

Example
ID = ['A';'A';'A','B';'B'];
Var1 =[1;2;3;4;5];
Var2 =[2;3;4;5;6];
tableCombine = table(ID,Var1,Var2)
How to segregate A nad B out?
tableA = tableCombine(tableCombine.ID=='A',:);
tableB = tableCombine(tableCombine.ID=='B',:);

  0 Comments

Sign in to comment.