Step 3: Setting Cell Values (Python)

There are two primary methods for setting cell values: setting values one cell at a time by specifying the categories that define the cell, or using the SetCellsByRow or SetCellsByColumn method.

Example: Specifying Cells by Their Category Values

This example reproduces the table created in the SimplePivotTable example.

from spss import CellText
table = spss.BasePivotTable("Table Title",
                            "OMS table subtype")

table.Append(spss.Dimension.Place.row,"row dimension")
table.Append(spss.Dimension.Place.column,"column dimension")

row_cat1 = CellText.String("first row")
row_cat2 = CellText.String("second row")
col_cat1 = CellText.String("first column")
col_cat2 = CellText.String("second column")

table[(row_cat1,col_cat1)] = CellText.Number(11)
table[(row_cat1,col_cat2)] = CellText.Number(12)
table[(row_cat2,col_cat1)] = CellText.Number(21)
table[(row_cat2,col_cat2)] = CellText.Number(22)
  • The Append method is used to add a row dimension and then a column dimension to the structure of the table. The table specified in this example has one row dimension and one column dimension. Notice that references to the dimension objects created by the Append method are not saved to variables, contrary to the recommendations in the topic on adding dimensions. When setting cells using the current approach, these object references are not needed.
  • For convenience, variables consisting of CellText objects are created for each of the categories in the two dimensions.
  • Cells are specified by their category values in each dimension. In the tuple (or list) that specifies the category values--for example, (row_cat1,col_cat1)--the first element corresponds to the first appended dimension (what we have named "row dimension") and the second element to the second appended dimension (what we have named "column dimension"). The tuple (row_cat1,col_cat1) then specifies the cell whose "row dimension" category is "first row" and "column dimension" category is "first column."
  • You may notice that the example does not make use of the SetCategories method to define the row and column dimension category values. When you assign cell values in the manner done here--table[(category1,category2)]--the values provided to specify the categories for a given cell are used by the BasePivotTable object to build the set of categories for the table. Values provided in the first element of the tuple (or list) become the categories in the dimension created by the first method call to Append or Insert. Values in the second element become the categories in the dimension created by the second method call to Append or Insert, and so on. Within a given dimension, the specified category values must be unique. The order of the categories, as displayed in the table, is the order in which they are created from table[(category1,category2)]. In the example shown above, the row categories will be displayed in the order "first row," "second row."
  • Cell values must be specified as CellText objects (one of CellText.Number, CellText.String, CellText.VarName, or CellText.VarValue).
  • In this example, Number objects are used to specify numeric values for the cells. Values will be formatted using the table's default format. Instances of the BasePivotTable class have an implicit default format of GeneralStat. You can change the default format using the SetDefaultFormatSpec method, or you can override the default by explicitly specifying the format, as in: CellText.Number(22,spss.FormatSpec.Correlation). See the topic Number Class (Python) for more information.

Example: Setting Cell Values by Row or Column

The SetCellsByRow and SetCellsByColumn methods allow you to set cell values for entire rows or columns with one method call. To illustrate the approach, we will use the SetCellsByRow method to reproduce the table created in the SimplePivotTable example. It is a simple matter to rewrite the example to set cells by column.

Note: You can only use the SetCellsByRow method with pivot tables that have one column dimension and you can only use the SetCellsByColumn method with pivot tables that have one row dimension.

from spss import CellText
table = spss.BasePivotTable("Table Title",
                            "OMS table subtype")

rowdim = table.Append(spss.Dimension.Place.row,"row dimension")
coldim = table.Append(spss.Dimension.Place.column,"column dimension")

row_cat1 = CellText.String("first row")
row_cat2 = CellText.String("second row")
col_cat1 = CellText.String("first column")
col_cat2 = CellText.String("second column")

table.SetCategories(rowdim,[row_cat1,row_cat2])
table.SetCategories(coldim,[col_cat1,col_cat2])

table.SetCellsByRow(row_cat1,[CellText.Number(11),
                              CellText.Number(12)])
table.SetCellsByRow(row_cat2,[CellText.Number(21),
                              CellText.Number(22)])
  • The SetCellsByRow method is called for each of the two categories in the row dimension.
  • The first argument to the SetCellsByRow method is the row category for which values are to be set. The argument must be specified as a CellText object (one of CellText.Number, CellText.String, CellText.VarName, or CellText.VarValue). When setting row values for a pivot table with multiple row dimensions, you specify a list of category values for the first argument to SetCellsByRow, where each element in the list is a category value for a different row dimension.
  • The second argument to the SetCellsByRow method is a list or tuple of CellText objects (one of CellText.Number, CellText.String, CellText.VarName, or CellText.VarValue) that specify the elements of the row, one element for each column category in the single column dimension. The first element in the list or tuple will populate the first column category (in this case, col_cat1), the second will populate the second column category, and so on.
  • In this example, Number objects are used to specify numeric values for the cells. Values will be formatted using the table's default format. Instances of the BasePivotTable class have an implicit default format of GeneralStat. You can change the default format using the SetDefaultFormatSpec method, or you can override the default by explicitly specifying the format, as in: CellText.Number(22,spss.FormatSpec.Correlation). See the topic Number Class (Python) for more information.