The SplitByColumn function splits a DataFrame or Matrix of data into a list of submatrices. Data placement into a submatrix is decided on a row by row basis, depending on the value in a particular column (the 'key' column).

This is useful if the values in any one row are related and data in any one column are of the same type. This type of data is often stored in a DataFrame object. For example, data about house sales in a region may be organized in three columns giving the price, number of bedrooms, and surface area of each house, where the three values belonging to any one sale are in the same row. This function allows you to separate your data into submatrices with all data for sales involving one bedroom houses, two bedroom houses, etc. Many other Statistics commands can then find properties for these submatrices individually. For more details and a list of applicable Statistics commands, see Statistics/DataFrames.

Alternatively, if your data is organized into a number of Vectors for each of the types of data (corresponding to the columns in the DataFrame case), you can also submit it as a list of Vectors. In this case, n is the index of the 'key' Vector in the list L of Vectors.

The options argument can contain one or more of the options shown below.

bounds - By default, SplitByColumn creates a separate submatrix for every different value in the key column of M (as determined by n). This may not be what you want, especially if your key data is more or less continuous (such as the house prices in the example above). In this case, you can use the bounds option to specify boundaries of 'bins' into which values are collected. There are several different ways to use this option:

$\mathrm{bounds}=\mathrm{none}$ is the default and leads to a separate 'bin' for every value in the key column.

$\mathrm{bounds}=\left[\mathrm{b0}\,\mathrm{b1}\,\mathrm{...}\,\mathrm{bn}\right]$ means values from the key column will be divided into $n$ bins: from $\mathrm{b0}$ to $\mathrm{b1}$, from $\mathrm{b1}$ to $\mathrm{b2}$, etc. Values less than $\mathrm{b0}$ or greater than $\mathrm{bn}$, and the rows containing them, are discarded.

These intervals are closed on the left and open on the right; that is, if the exact value $\mathrm{b1}$ occurs in the data, then that row goes into the second matrix, not the first one. The rightmost bin is an exception: it represents an interval closed on both sides.

Instead of using a list $\left[\mathrm{b0}\,\mathrm{b1}\,\mathrm{...}\,\mathrm{bn}\right]$, you can also use a Vector with the same entries. This has the same effect.

$\mathrm{bounds}=\mathrm{quantiles}\left(\mathrm{q0}\,\mathrm{q1}\,\mathrm{...}\,\mathrm{qn}\right)$ or $\mathrm{bounds}=\mathrm{deciles}\left(\mathrm{d0}\,\mathrm{d1}\,\mathrm{...}\,\mathrm{dn}\right)$ or $\mathrm{bounds}=\mathrm{percentiles}\left(\mathrm{p0}\,\mathrm{p1}\,\mathrm{...}\,\mathrm{pn}\right)$ or $\mathrm{bounds}=\mathrm{quartiles}\left(\mathrm{q0}\,\mathrm{q1}\,\mathrm{...}\,\mathrm{qn}\right)$ are equivalent ways of specifying bins similar to specifying a list, but instead of specifying absolute numbers for the bounds, you specify which fraction of the data should fall in each of the bins.

For example, specifying $\mathrm{bounds}=\mathrm{percentiles}\left(0\,25\,60\,90\right)$ specifies three bins, the first of which contains the lowest 25% of data, then then next 60% - 25% = 35% of data, and finally the following 90% - 60% = 30% of data. That is, the bounds are at the 0th, 25th, 60th, and 90th Percentile of the data in the key column. This could also be specified as $\mathrm{bounds}=\mathrm{quantiles}\left(0\,0.25\,0.6\,0.9\right)$ using the notion of Quantile instead of Percentile, or $\mathrm{bounds}=\mathrm{deciles}\left(0\,2.5\,6\,9\right)$ using Deciles, or $\mathrm{bounds}=\mathrm{quartile}\left(0\,1\,2.4\,3.6\right)$ using Quartiles.

$\mathrm{bounds}=n$ for a positive integer n means values from the key column will be divided into n bins of equal width.

output = default or vectorlist or matrix can be used to override the output format for each submatrix: a DataFrame/Matrix or a list of column Vectors. The default is to return the same type of data as was submitted. That is, for the calling sequence involving M, SplitByColumn returns a list of matrices by default, and for the calling sequence involving L, it returns a list of lists of Vectors. This can be overridden with this option.

In either case, the internal computations occur with a Matrix, so input and/or output of a list of Vectors costs some time and memory for conversion. (This is usually negligible, though.)

ignore = true or false specifies how undefined values in the key column should be treated. With ignore = true, all undefined values (and the corresponding rows) will be removed from the data set.  With ignore = false (the default), the behavior of SplitByColumn depends on the value of bounds: with bounds = none, any occurring undefined values in the key column will give rise to a submatrix with undefined values. If multiple different values of type,undefined occur, then every value gets its own submatrix. With all other settings of bounds, undefined values will end up in an arbitrary submatrix.

$\mathrm{with}\left(\mathrm{Statistics}\right)\:$

$\mathrm{bedrooms}≔⟨3\,4\,2\,4\,3\,2\,2\,3\,4\,4\,2\,4\,4\,3\,3⟩$

$\mathrm{area}≔⟨1130\,1123\,1049\,1527\,907\,580\,878\,1075\,1040\,1295\,1100\,995\,908\,853\,856⟩$

$\mathrm{price}≔⟨114700\,125200\,81600\,127400\,88500\,59500\,96500\,113300\,104400\,136600\,80100\,128000\,115700\,94700\,89400⟩$

$\mathrm{HouseSalesData}≔\mathrm{DataFrame}\left(\left[\mathrm{bedrooms}\,\mathrm{area}\,\mathrm{price}\right]\,\mathrm{columns}=\left[\mathrm{Bedrooms}\,\mathrm{Area}\,\mathrm{Price}\right]\right)$

$\mathrm{ByRooms}≔\mathrm{SplitByColumn}\left(\mathrm{HouseSalesData}\,\mathrm{Bedrooms}\right)$

$\mathrm{ByRooms}≔\left[\left[\begin{array}{cccc}& \mathrm{Bedrooms}& \mathrm{Area}& \mathrm{Price}\\ 3& 2& 1049& 81600\\ 6& 2& 580& 59500\\ 7& 2& 878& 96500\\ 11& 2& 1100& 80100\end{array}\right]\,\left[\begin{array}{cccc}& \mathrm{Bedrooms}& \mathrm{Area}& \mathrm{Price}\\ 1& 3& 1130& 114700\\ 5& 3& 907& 88500\\ 8& 3& 1075& 113300\\ 14& 3& 853& 94700\\ 15& 3& 856& 89400\end{array}\right]\,\left[\begin{array}{cccc}& \mathrm{Bedrooms}& \mathrm{Area}& \mathrm{Price}\\ 2& 4& 1123& 125200\\ 4& 4& 1527& 127400\\ 9& 4& 1040& 104400\\ 10& 4& 1295& 136600\\ 12& 4& 995& 128000\\ 13& 4& 908& 115700\end{array}\right]\right]$

$\mathrm{PricesByRooms}≔\mathrm{map}\left(m\mapsto m\left[\mathrm{Price}\right]\,\mathrm{ByRooms}\right)\:$

$\mathrm{BoxPlot}\left(\mathrm{PricesByRooms}\,'\mathrm{deciles}=\mathrm{false}'\,'\mathrm{datasetlabels}'=\left[''2\; bdrms''\,''3\; bdrms''\,''4\; bdrms''\right]\,'\mathrm{color}'=\left[''Red''\,''Purple''\,''Blue''\right]\right)$

$\mathrm{plots}\left[\mathrm{display}\right]\left(\mathrm{zip}\left(\left(m\,c\right)\mapsto \mathrm{PointPlot}\left(m\left[\mathrm{Price}\right]\,'\mathrm{xcoords}'=m\left[\mathrm{Area}\right]\,'\mathrm{color}'=c\,'\mathrm{symbolsize}'=20\right)\,\mathrm{ByRooms}\,\left[''Red''\,''Purple''\,''Blue''\right]\right)\right)$

$\mathrm{ByArea}≔\mathrm{SplitByColumn}\left(\mathrm{HouseSalesData}\,\mathrm{Area}\,'\mathrm{bounds}'=\left[700\,950\,1250\right]\right)$

$\mathrm{ByArea}≔\left[\left[\begin{array}{cccc}& \mathrm{Bedrooms}& \mathrm{Area}& \mathrm{Price}\\ 5& 3& 907& 88500\\ 7& 2& 878& 96500\\ 13& 4& 908& 115700\\ 14& 3& 853& 94700\\ 15& 3& 856& 89400\end{array}\right]\,\left[\begin{array}{cccc}& \mathrm{Bedrooms}& \mathrm{Area}& \mathrm{Price}\\ 1& 3& 1130& 114700\\ 2& 4& 1123& 125200\\ 3& 2& 1049& 81600\\ 8& 3& 1075& 113300\\ 9& 4& 1040& 104400\\ 11& 2& 1100& 80100\\ 12& 4& 995& 128000\end{array}\right]\right]$

$\mathrm{PricesByArea}≔\mathrm{map}\left(m\mapsto m\left[\mathrm{Price}\right]\,\mathrm{ByArea}\right)$

$\mathrm{PricesByArea}≔\left[\left[\begin{array}{cc}5& 88500\\ 7& 96500\\ 13& 115700\\ 14& 94700\\ 15& 89400\end{array}\right]\,\left[\begin{array}{cc}1& 114700\\ 2& 125200\\ 3& 81600\\ 8& 113300\\ 9& 104400\\ 11& 80100\\ 12& 128000\end{array}\right]\right]$

$\mathrm{BoxPlot}\left(\mathrm{PricesByArea}\,'\mathrm{deciles}=\mathrm{false}'\,'\mathrm{datasetlabels}'=\left[''700-950''\,''950-1250''\right]\,'\mathrm{color}'=\left[''Green''\,''Cyan''\right]\right)$

$\mathrm{Mean}\left(\mathrm{HouseSalesData}\right)$

$\left[\begin{array}{cc}\mathrm{Bedrooms}& 3.13333333333333\\ \mathrm{Area}& 1021.06666666667\\ \mathrm{Price}& 103706.666666667\end{array}\right]$

$\mathrm{`~`}\left[\mathrm{Mean}\right]\left(\mathrm{ByArea}\right)$

$\left[\left[\begin{array}{cc}\mathrm{Bedrooms}& 3.\\ \mathrm{Area}& 880.400000000000\\ \mathrm{Price}& 96960.\end{array}\right]\,\left[\begin{array}{cc}\mathrm{Bedrooms}& 3.14285714285714\\ \mathrm{Area}& 1073.14285714286\\ \mathrm{Price}& 106757.142857143\end{array}\right]\right]$

$\mathrm{Aggregate}\left(\mathrm{HouseSalesData}\,\mathrm{Bedrooms}\right)$

$\left[\begin{array}{cccc}& \mathrm{Bedrooms}& \mathrm{Area}& \mathrm{Price}\\ 1& 2& 901.750000000000& 79425.\\ 2& 3& 964.200000000000& 100120.\\ 3& 4& 1148.& 122883.333333333\end{array}\right]$

$\mathrm{ByPrice}≔\mathrm{SplitByColumn}\left(\mathrm{HouseSalesData}\,3\,'\mathrm{bounds}=\mathrm{quartiles}\left(0\,1\,2\,3\,4\right)'\,\mathrm{output}=\mathrm{vectorlist}\right)$

$\mathrm{ByPrice}≔\left[\left[\left[\begin{array}{c}2\\ 3\\ 2\\ 2\end{array}\right]\,\left[\begin{array}{c}1049\\ 907\\ 580\\ 1100\end{array}\right]\,\left[\begin{array}{c}81600\\ 88500\\ 59500\\ 80100\end{array}\right]\right]\,\left[\left[\begin{array}{c}2\\ 3\\ 3\end{array}\right]\,\left[\begin{array}{c}878\\ 853\\ 856\end{array}\right]\,\left[\begin{array}{c}96500\\ 94700\\ 89400\end{array}\right]\right]\,\left[\left[\begin{array}{c}3\\ 3\\ 4\\ 4\end{array}\right]\,\left[\begin{array}{c}1130\\ 1075\\ 1040\\ 908\end{array}\right]\,\left[\begin{array}{c}114700\\ 113300\\ 104400\\ 115700\end{array}\right]\right]\,\left[\left[\begin{array}{c}4\\ 4\\ 4\\ 4\end{array}\right]\,\left[\begin{array}{c}1123\\ 1527\\ 1295\\ 995\end{array}\right]\,\left[\begin{array}{c}125200\\ 127400\\ 136600\\ 128000\end{array}\right]\right]\right]$

$\mathrm{AreasByPrice}≔\mathrm{map}\left(x\mapsto x\left[2\right]\,\mathrm{ByPrice}\right)$

$\mathrm{AreasByPrice}≔\left[\left[\begin{array}{c}1049\\ 907\\ 580\\ 1100\end{array}\right]\,\left[\begin{array}{c}878\\ 853\\ 856\end{array}\right]\,\left[\begin{array}{c}1130\\ 1075\\ 1040\\ 908\end{array}\right]\,\left[\begin{array}{c}1123\\ 1527\\ 1295\\ 995\end{array}\right]\right]$

$\mathrm{BoxPlot}\left(\mathrm{AreasByPrice}\,'\mathrm{deciles}=\mathrm{false}'\,'\mathrm{datasetlabels}'=\left[''1st\; quartile''\,''2nd\; quartile''\,''3rd\; quartile''\,''4th\; quartile''\right]\,'\mathrm{color}'=''Red''..''Yellow''\right)$

$\mathrm{data}≔\mathrm{Matrix}\left(\left[\left[1\,2\,3\right]\,\left[4\,5\,\mathrm{undefined}\right]\,\left[7\,\mathrm{undefined}\,9\right]\,\left[10\,11\,\mathrm{undefined}\right]\,\left[13\,8\,3\right]\right]\right)$

$\mathrm{data}≔\left[\begin{array}{ccc}1& 2& 3\\ 4& 5& \mathrm{undefined}\\ 7& \mathrm{undefined}& 9\\ 10& 11& \mathrm{undefined}\\ 13& 8& 3\end{array}\right]$

$\mathrm{SplitByColumn}\left(\mathrm{data}\,3\right)$

$\left[\left[\begin{array}{ccc}1& 2& 3\\ 13& 8& 3\end{array}\right]\,\left[\begin{array}{ccc}7& \mathrm{undefined}& 9\end{array}\right]\,\left[\begin{array}{ccc}4& 5& \mathrm{undefined}\\ 10& 11& \mathrm{undefined}\end{array}\right]\right]$

$\mathrm{SplitByColumn}\left(\mathrm{data}\,3\,'\mathrm{bounds}=\mathrm{quantiles}\left(0\,\frac{1}{2}\,1\right)'\right)$

$\left[\left[\begin{array}{ccc}4& 5& \mathrm{undefined}\\ 10& 11& \mathrm{undefined}\end{array}\right]\,\left[\begin{array}{ccc}1& 2& 3\\ 13& 8& 3\end{array}\right]\right]$

$\mathrm{SplitByColumn}\left(\mathrm{data}\,3\,'\mathrm{bounds}=\mathrm{quantiles}\left(0\,\frac{2}{3}\,1\right)'\,'\mathrm{ignore}'\right)$

$\left[\left[\begin{array}{ccc}1& 2& 3\\ 13& 8& 3\end{array}\right]\,\left[\begin{array}{ccc}7& \mathrm{undefined}& 9\end{array}\right]\right]$

The Statistics[SplitByColumn] command was introduced in Maple 16.

For more information on Maple 16 changes, see Updates in Maple 16.