data analysis

[mrthehoople]

I wonder if someone can help me, I'm sure this is possible.

I have a large bunch of research data in an excel spreadsheet. The experiment involves tracking eye movements of people looking at targets that appear on a screen in various places. Each row entry on the excel spreadsheet basically gives me the x and y co-ordinates of where the eye fixated for a period of time, and the beginning and end time (and duration) of each fixation of the eye. It also gives me the x and y co-ordinates of where the target was. (the participants in the experiment were either healthy elderly people, or elderly people with different visual problems).

Each row is labelled firstly with the participant label (i.e. each partipant in the experiment is assigned a code number) and secondly with the experiment number (which changes every time a new target appears). The problem is that for a given participant and experiment number, there may be anything from 1 to 12 rows of data (i.e. 1 row if their eye fixed only in one place whilst the target was on the screen, but more if their eye fixed in numerous places).

I want to create some variables for each experiment for each participant (i.e. participant 12 doing experiment 3) to include things like what were the co-ordinates of the first eye fixation after the target appears. Where did the eye fix for the longest. What was the location of the fixation that was closest to the target.

Can I do something like this using a pivot table, cross tabulating participant label against experiment number. I can create a pivot table for instance to give me the sum of the x co-ordinates for the eye fixations, but that's as far as I've got. Can I do something clever for the results in the pivot table like put in the x eye co ordinate assocated with the longest duration of fixation, or just the x eye co-ordinate for the first fixation of each experiment

Many thanks for any help with this..
Matt

[Bob Phillips]

Matt,

I am sure you can do what you want to do, but depending upon what you need overall might drive the solution. Also, the amount of data and the technology that you have at your disposal might make a difference.

My immediate thought is a list of participants and experiments and formulae to get the first fixation, the longest and so on.

PS why do you call yourself mrthehoople, why not mattthehoople?

[mrthehoople]

Hi Bob

It is Matt the hoople, but later became Mr the hoople...

I have a massive amount of data - my kit records eye position at 1000 frames per second. But it's quite easy (and I've already done it) to generate excel spreadsheets with entries just for each fixation (and indeed for each eye movement with beginning and end co-ordinates and velocity data etc.). I've also tidied these spreadsheets to remove data from before and after the target appears so all data is relevant..

The excel spreadsheet for the fixations looks like this essentially:

Participant 1, experiment 1, fixation X, fixation Y, start time, end time, duration, target x, target y
Participant 1, experiment 1, fixation X, fixation Y, start time, end time, duration, target x, target y
Participant 1, experiment 2, fixation X, fixation Y, start time, end time, duration, target x, target y
Participant 1, experiment 2, fixation X, fixation Y, start time, end time, duration, target x, target y
Participant 1, experiment 2, fixation X, fixation Y, start time, end time, duration, target x, target y
Participant 1, experiment 3, fixation X, fixation Y, start time, end time, duration, target x, target y
Participant 1, experiment 4, fixation X, fixation Y, start time, end time, duration, target x, target y
Participant 1, experiment 4, fixation X, fixation Y, start time, end time, duration, target x, target y
Participant 2, experiment 1, fixation X, fixation Y, start time, end time, duration, target x, target y

although obviously much much larger (36 participants, 48 experiments per participant and anything from 1 to 13 or 14 rows for each combination of participant and experiment

I want to produce a summary table which has 1 entry for each participant and experiment i.e.

participant 1, experiment 1
participant 1, experiment 2
participant 1, experiment 3
participant 1, experiment 4
participant 2, experiment 1 etc.

with each row containing x and y co-ordinates for the first fixation, the longest fixation and the fixation which was nearest to the target.

Does this make some kind of sense?
All I can think of is a huge bunch of pivot tables, but I'm not sure how to get the results in the pivot table to show things like x co-ordinate of the fixation of longest duration

Many thanks if you can help

Matt

[Bob Phillips]

Matt,

My approach would be to drop all of the data into a SQL Server 2008 R2 database, and use SSAS and Excel 2010 and PowerPivot against. I attended a presentation by Yasmeen Ahmad from the University of Dundee in 2010 where she was analysing a huge swathe of proteomic data using this technique. I blogged about it http://msmvps.com/blogs/xldynamic/ar...g-edge-bi.aspx

But, assuming for now you don't have such tools, I think the best approach would be to enhance the data. Add columns to calculate the participant/experiment ordinal value, the longest fixation, and the closest to target, and add these to the pivot. If you have Excel 2010, you can have Participant and Experiment slicers to help filter your data. Formulae would do that, but they would be array type formulae, and with large data sets this might slow the data down considerably. As such, the best way might be to have a small macro that will work on the activesheet, add these columns, and build the pivot table. Should be quite quick.

[Bob Phillips]

BTW, how do you determine closest with both X and Y co-ordinates?

[mrthehoople]

Hi Bob

I'm making progress with this using array formulae. I've managed to create a table with one row per unique identifier or participant and experiment and then hopefully can then calculate some minimum/maximum values. Unfortunately I'm going to have to abandon this for today and get on with some proper work. I'll return to it on friday.. I'll let you know how I'm doing

Matt

[mrthehoople]

In reply to your query about closest with x and y co-ordinates, I just use simple pythagoras to calculate distance from target. You can do root square of target x - fixation x and the same for y to get the error in each dimension. Then do sqrt error x squared + error y squared