MATLAB: Splitting a Table at Every N Rows

splittingtable

I have a single table with 58 years worth of tax data for 75 different countries. It is currently organized with the countries stacked on top of each other alphabetically. Hence, I have 58 years of data for Argentina, followed by 58 years of data for Austria, and so on. I would like to split it at every 58th row, so that I can store each country's data in its own separate table. Is there a quick way I can do this?

Best Answer

You can use this script to split your table as mentioned in the question. Replace myTable with the name of your table. It will create a cell array seperatedCountries.

nunberCountries = 75;
noumberYears = 58;
rowOndexVector = repelem(1:nunberCountries, noumberYears);
seperatedCountries = splitapply(@(x) {x}, cell2mat(myTable{:,:}), findgroups(rowOndexVector));

seperatedCountries{1} will contain data about first country (i.e. Argentina). Similarly seperatedCountries{1} will contain data about second country (i.e. Austria) as so on.

Related Solutions

MATLAB: Splitting one column into multiple arrays

z=xlsread('data.csv');
ids = unique(z(:,1),'stable');
houses = cell(length(ids),1);
house = struct();
for i = 1 : length(ids)
    houses{i,1} =  [ 'house' num2str(i) ];
    house.houses{i,1} = z(find(z(:,1)==ids(i)),:);
end

The data of all houses are stored in struct house.

MATLAB: Save in a string the values obtained after varfun and grouping

If you want to create a new table with two new variables, one for the number of unique countries and one for the list of unique countries then a) you have to use rowfun instead of varfun, and b) you can't use an anonymous function (since you need a function with 2 outputs) but a normal m function:

function [count, list] = uniquecountries(countries)
   %inputs: 
   %    countries: a list of countries (categorical)
   %    count: the number of unique countries (double)
   %    list: the list of unique countries, joined by a ', ' (scalar cell array of a char vector)
   %Note: list is returned as a scalar cell array instead of a char vector so that rowfun can concatenate the output of uniquecountries
   validateattributes(countries, {'categorical'}, {});
   list = unique(countries);
   count = numel(list);
   list = {strjoin(cellstr(list), ', ')};
end

Then:

sections = rowfun(@uniquecountries, fullTable, ...
                  'GroupingVariables', {'ID1','Date'}, ...
                  'InputVariables', 'Country', ...
                  'NumOutputs', 2, ...
                  'OutputVariableNames', {'Country_count', 'Country_list'})

Alternatively, if you don't want a separate m function, you can have two calls to rowfun or varfun to create two tables that you then join. It will be much slower however:

sectionscount = rowfun(@(v) numel(unique(v)), fullTable, ...
                       'GroupingVariables', {'ID1','Date'}, ...
                       'InputVariables', 'Country', ...
                       'OutputVariableNames', 'Country_count');
sectionslist = rowfun(@(v) {strjoin(cellstr(unique(v)), ', ')}, fullTable, ...
                       'GroupingVariables', {'ID1','Date'}, ...
                       'InputVariables', 'Country', ...
                       'OutputVariableNames', 'Country_list');
sections = join(sectionscount, sectionslist)

Best Answer

Related Solutions

MATLAB: Splitting one column into multiple arrays

MATLAB: Save in a string the values obtained after varfun and grouping

Related Question