Excel can be used to conduct some pretty advanced statistical analyses and data visualisations. My favourite, and most commonly used feature is the Pivot Table. The data in a Pivot Table can be restructured very easily and counts, averages and other calculations can be worked out quickly. It is often desirable to enter data from scientific experiments into a Pivot Table so that different conditions can be compared, some conditions or participants or trials can be excluded, and data can be collapsed or combined in a number of ways. But, for all of their usefulness, getting the data in to a format that can be read by a Pivot Table can be time consuming. Some experiment presentation/management programs, such as E-Prime, generate result files for individual participants. In order to generate a Pivot Table report, all data needs to be entered into a single Excel worksheet.
Here are the steps necessary to generate a Pivot Table in Excel:
1. Export the results files into a format that can be imported into Excel
Some experimental software outputs results in plain text format or as .csv files. These can be imported into Excel without any extra work. However, some software outputs results in a proprietary format that must be converted before Excel can read the data.
E-Prime: Open the .edat2 file in E-DataAid, and export to Excel format. For example, you would export participant1.edat2 as
participant1.edat2.txt.
Hint: If you have many E-Prime data files, open these in E-Merge and create a merge file containing data for all of the participants. Then open the merge file using E-DataAid and export it to Excel format.
2. Open the text file in ExcelOpen the new .txt files in Excel (you can either select File | Open or drag and drop the file into Excel).
3. Go through the Text Import Wizard
In Step 1, select Delimited and click Next.
In Step 2, check the Tab option from the list of Delimiters and click Finish.
4. Delete Row 1
By default, E-Prime outputs the file path in the first line of the .txt file, which becomes row 1 when you import it into Excel. Delete it. This will cause row 2 to move up. Now row 1 contains your column headings which will become your Pivot Table fields.
5. Create a Pivot Table
Select all active columns. To do this, click on column A (by clicking on the A) and hold down the Shift key and click on the column label of the right most column (mine is HZ). This can be done using the keyboard by pressing Command+Shift+right arrow while column A is selected.
Click on the Insert ribbon and then Pivot Table. When the Create Pivot Table dialog appears, click OK.
6. Build your Pivot Table
Drag the fields that you are interested in into the Rows, Columns or Values areas. Subject/participant normally goes into the Rows area. The measures of interest (e.g., accuracy or reaction time data) usually go into Values.
When you first drag a field into Values, Excel will show you the count of how many data points there are. Click on the "i" next to the field within the Values area to show the Pivot Table Field window.
This gives you options to calculate the sum, average, maximum, minimum, standard deviation and so on. For experimental data, most of the time, we are interested in averages.
Spend time playing around with the fields in the Pivot Table Builder. You can explore your data in powerful ways, such as by isolating particular subjects, conditions, or cases.
Clicking on the inverted triangle next to Row Labels brings up the Row window which permits you to sort the rows, add filters, and exclude individual cases. This can be handy for exploring potential outliers (when looking at subjects), or examining performance for particular conditions or stimulus tokens.
Pivot Tables are great tools for exploring your data. They offer a degree of flexibility that is unmatched. They are very useful for answering "What happens if..." questions, and the calculations can be done very quickly.
No comments:
Post a Comment