ToolsAndTricks - CBU statistics Wiki
location: ToolsAndTricks

# Tools and tricks for manipulating data files

## PivotTables in Excel

If you want to produce cross-tabulated summaries of your data in an Excel spreadsheet, then it is worth exploring the use of Excel's PivotTable Reports.

• Data -> PivotTable Report ...

or the keystroke sequence

• Alt-T Alt-P

There is a special toolbar for pivot tables. It can be switched on via the Menu sequence

• Tools -> Customise ...

## Data Restructure (SPSS 11)

This seems to be the perfect tool for all those occasion when you want ...

1. To combine data from two or more rows (cases) of a data matrix in a single row (case). The Restructure Wizard calls this Restructure selected cases into variables');

2. To split a single row (case) into two or more rows (cases). The Restructure Wizard calls this Restructure selected variables into cases');

3. To swap rows and columns (i.e. simply tanspose the data matrix).
• The Data Restructure procedure in splitting mode (2) will create new variables to tell where the data came from.

For instance, a dataset contains information on the state of 18 structures in 44 MRI brain scans as assessed by 3 raters. The question asked relates to the amount of inter-rater agreement. The original data is organised as 18 rows (cases), 1 per brain scan, and 132 columns (variables), being the 3 ratings for each of the 44 structures. Each Rater gives ratings on 44 x 18 Items (scans x structures), and we want to restructure the data into an Item x Rater matrix.

• Select Restructure selected variables into cases.

• We want to create variables Rater1, Rater2 and Rater3, each of which contain each rater's scores for the items. Each Rater corresponds to a what SPSS calls a variable group. There are thus 3 variable groups that we want to restructure. We can use the case number (Scan number) for SPSS's Case Group Identification.

• We select the 44 Rater 1 variables (scans) as the Variables to be Transposed and call the Target Variable Rater1 instead of the default trans1.

• Now move to Target Variable trans2, rename it Rater2 and select the 44 variables for Rater2 (NB the orders of the variables selected need to correspond).

• Then set up Rater3 the same way.

• Next we create an Index Variable which will indicate which Scan the data correspond to.

There is are additional options which allow for other ('fixed') variables to be replicated across the new split cases.

These pages are maintained by Ian Nimmo-Smith and Peter Watson

Last updated on 2 July 2006

None: ToolsAndTricks (last edited 2013-03-08 10:17:39 by localhost)