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

104 views (last 30 days)
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

Accepted Answer

Walter Roberson
Walter Roberson on 17 Jan 2019
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.

More Answers (2)

Peter Perkins
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
Kristian Opsahl
Kristian Opsahl on 24 Jan 2019
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.


Kevin Chng
Kevin Chng on 17 Jan 2019
Edited: 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',:);

Community Treasure Hunt

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

Start Hunting!