= 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');'' 1. To split a single row (case) into two or more rows (cases). The Restructure Wizard calls this '''Restructure selected variables into cases');'' 1. 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. ---- [[CbuStatistics|Return to Statistics main page]] [[http://www.mrc-cbu.cam.ac.uk/|Return to CBU main page]] These pages are maintained by [[mailto:ian.nimmo-smith@mrc-cbu.cam.ac.uk|Ian Nimmo-Smith]] and [[mailto:peter.watson@mrc-cbu.cam.ac.uk|Peter Watson]] ~- Last updated on 2 July 2006 -~