SplitByColumn - Maple Help

Statistics

 SplitByColumn
 split matrix data into submatrices by values of one column

 Calling Sequence SplitByColumn(M, n, options) SplitByColumn(L, n, options)

Parameters

 M - DataFrame or Matrix of data n - index or label of the 'key' column L - list of Vectors or other ordered one-dimensional data sets; all data sets should have the same number of elements options - (optional) equation(s) of the form option=value where option is one of bounds, output, or ignore; specify options for the SplitByColumn function

Description

 • 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.

Options

 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.

Examples

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

We construct a Matrix with housing data. The first column has number of bedrooms, the second has number of square feet, the third has price.

We construct a DataFrame with housing data. The first column has number of bedrooms, the second has the area in square feet, the third has price.

 > $\mathrm{bedrooms}≔⟨3,4,2,4,3,2,2,3,4,4,2,4,4,3,3⟩$
 ${\mathrm{bedrooms}}{≔}\begin{array}{c}\left[\begin{array}{c}{3}\\ {4}\\ {2}\\ {4}\\ {3}\\ {2}\\ {2}\\ {3}\\ {4}\\ {4}\\ {⋮}\end{array}\right]\\ \hfill {\text{15 element Vector[column]}}\end{array}$ (1)
 > $\mathrm{area}≔⟨1130,1123,1049,1527,907,580,878,1075,1040,1295,1100,995,908,853,856⟩$
 ${\mathrm{area}}{≔}\begin{array}{c}\left[\begin{array}{c}{1130}\\ {1123}\\ {1049}\\ {1527}\\ {907}\\ {580}\\ {878}\\ {1075}\\ {1040}\\ {1295}\\ {⋮}\end{array}\right]\\ \hfill {\text{15 element Vector[column]}}\end{array}$ (2)
 > $\mathrm{price}≔⟨114700,125200,81600,127400,88500,59500,96500,113300,104400,136600,80100,128000,115700,94700,89400⟩$
 ${\mathrm{price}}{≔}\begin{array}{c}\left[\begin{array}{c}{114700}\\ {125200}\\ {81600}\\ {127400}\\ {88500}\\ {59500}\\ {96500}\\ {113300}\\ {104400}\\ {136600}\\ {⋮}\end{array}\right]\\ \hfill {\text{15 element Vector[column]}}\end{array}$ (3)
 > $\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{HouseSalesData}}{≔}\begin{array}{c}\left[\begin{array}{cccc}{}& {\mathrm{Bedrooms}}& {\mathrm{Area}}& {\mathrm{Price}}\\ {1}& {3}& {1130}& {114700}\\ {2}& {4}& {1123}& {125200}\\ {3}& {2}& {1049}& {81600}\\ {4}& {4}& {1527}& {127400}\\ {5}& {3}& {907}& {88500}\\ {6}& {2}& {580}& {59500}\\ {7}& {2}& {878}& {96500}\\ {8}& {3}& {1075}& {113300}\\ {9}& {4}& {1040}& {104400}\\ {10}& {4}& {1295}& {136600}\\ {⋮}& {⋮}& {⋮}& {⋮}\end{array}\right]\\ \hfill {\text{15 x 3 DataFrame}}\end{array}$ (4)

We can create box plots of the price for subgroups of sales defined by number of bedrooms.

 > $\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]$ (5)
 > $\mathrm{PricesByRooms}≔\mathrm{map}\left(m↦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)↦\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)$

Looking at this plot, we see that there is one house that is especially small and cheap, and two that are especially large and expensive, and the rest are clustered fairly closely together. Let's split that cluster into two subgroups according to their area. There is a small gap around 950 square feet.

 > $\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]$ (6)
 > $\mathrm{PricesByArea}≔\mathrm{map}\left(m↦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]$ (7)
 > $\mathrm{BoxPlot}\left(\mathrm{PricesByArea},'\mathrm{deciles}=\mathrm{false}','\mathrm{datasetlabels}'=\left["700-950","950-1250"\right],'\mathrm{color}'=\left["Green","Cyan"\right]\right)$

We can compute the means of the columns in an individual data set by using the Mean command. To apply it to the list of DataFrame objects returned by SplitByColumn, we can use the elementwise version of Mean, obtained by appending a tilde.

 > $\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]$ (8)
 > $\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]$ (9)

The same thing can be done in one step with the Aggregate method of the DataFrame object. Since this command is part of the DataFrame object, it does not accept Matrices.

 > $\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]$ (10)

We can also split the data into four roughly equally large groups by price. The output is lists of vectors this time.

 > $\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]$ (11)

Let's examine the areas of these houses.

 > $\mathrm{AreasByPrice}≔\mathrm{map}\left(x↦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]$ (12)
 > $\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)$

There are undefined values in the following Matrix. If we do not supply a bounds argument, then the undefined values in the third column are grouped into a separate bin.

 > $\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]$ (13)
 > $\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]$ (14)

If we do supply a bounds argument, then the result is not well-defined. In this case, the row with value 9 in the key column disappears, and undefined values are placed in the first half of data.

 > $\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]$ (15)

With the ignore option, the undefined values are removed from the key column. Note that the undefined value in the second column remains untouched.

 > $\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]$ (16)

Compatibility

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