macros to clean, summarize, and merge reaction time data files?

[bradw09]

Hi folks!

I'm new to the forum and know the basics of Excel but nothing about macros, which I think I need to use to prepare for analysis a large set of individual participants' Excel data files collected using a psychological reaction-time experiment (a modified Stroop task). This experiment was created using a program called Superlab by Cedrus, so I posted an inquiry at the Superlab forum (http://community.cedrus.com/showthread.php?t=693). Their tech support suggested I might need to use macros in Excel but couldn't offer further assistance. I'll try to briefly describe (a) our Stroop task, (b) the operations I'm trying to carry out, and (c) the format of the individual Excel files, in hopes that someone can help me.

Our Stroop task: The participant gets some instructions and practice trials, then a series of stimulus words are presented one word at a time in the center of a computer monitor in one of several colors. Word presentation order is randomized for each participant. The participant must press a key corresponding to the color of each word as quickly as possible. For each keypress (including the spacebar, pressed on instructions screens), the reaction time (in msec) is recorded in their Excel spreadsheet under the 'Reaction Time' variable. The 'Error Code' variable indicates whether the participant pressed the correct (C) or incorrect (E) color key in each trial. There are 8 types of words for a total of 48 trials. (Actually, 4 sets of emotion words and 4 sets of control-animal words, each set composed of 6 words each. The task basically measures how much peoples’ attention is captured by emotionally relevant cues in the environment.)

For each participant's data, I need an efficient way (macros?) to:
(1) clean it [i.e. discard or ignore the instruction & practice trials as well as trials where the participant was too impulsive (reaction time < 100 msec), too distracted (rt > 3000 msec), or gave the wrong color response (Error Code= E)]
(2) determine whether enough valid trials remain for further analysis (>39 trials), and if so:
(3) calculate a mean reaction time score for each of the 8 word types [where denominator for each word type is based on # remaining (non-discarded) trials for that word type]
(4) merge each participant's summarized data as single rows in a common spreadsheet (for importation and further analyses in SPSS), like this:
---------------------------
ID#...... MeanRTW1.......MeanRTW2...... (etc. for word types 3-8)
101.......1056................2013...............
102.......2148................2594...............
103...
---------------------------

Excel File Format: I've attached a sample file, and here's a simplified overview of a spreadsheet (irrelevant columns and some rows removed). First the ID number is recorded (e.g., 393E), then some unnecessary junk (exp name & date, intro, practice, instructions), then data for the 48 actual trials of interest:
-------------------------------------------
........A...............B..................C.................D............
1 393E
2 stroop.xpt
3 Wed Jan 1, 12:33:00 2008
4
5
6 Trial Name.....Trial No..... Error Code.....ReactionTime
7 introduction..... 1................C............561243
8 practice.......... 2................C............... 2062
9 red-worried........14.............C................1001 (*the first practice trial)
10 (9 more practice trial rows, deleted here)
19 instructions........3..............C................5000
20 red-chicken......19..............E............... 1205
21 blue-tense.......32...............C.................782
* (46 more actual trial rows, deleted here)
--------------------------------------------------

To clarify:
-The variable 'Trial Name' indicates nominally what stimulus was presented for each trial.
-The variable 'Trial No.', indicates the unique numerical marker for each stimulus word. (It probably should have been labeled 'Word Type'. It doesn't reflect the order of stimulus presentation. E.g., the word "tense" is always 'Trial No.' 32 for all participants, regardless of when it presents.)
-A subset of actual trial words (e.g., worried) were also used for the 10 practice trials. Thus, the 10 practice trials must first somehow be discarded or flagged to be ignored before calculating average reaction times for the remaining 48 actual trials.

I look forward to any assistance you can offer!

Thanks so much,
Brad W

[StephenR]

Brad - I think it might help if you could attach an example of your data as is, i.e. as it will be when the macro is run rather than with any simplifications.

Also, can you clarify how different people's records will be fed through? Are they listed in one sheet one after the other or one worksheet each or one workbook each?

Lastly, it would help if you could illustrate precisely your desired output corresponding to the starting dataset.

[bradw09]

Thanks, Stephen. I've attached an example of a participant's data file (1001E.xls). Each participant's data is in a separate workbook (e.g., 1001E.xls, 1002E.xls, etc) on the first spreadsheet.

I've also attached a workbook illustrating the desired output format. I'll try to clarify below precisely how I wish the input to be determined (using macros?) for each variable. Starting with Excel spreadsheet row 20 thru 67 (after the practice trials), there are reaction times recorded for 64 words (actual trials). There are 8 sets of words for which I wish to have a program (macros formula?) compute mean reaction times for the 8 sets of words from which the 64 derive. These 8 Word Type sets are: (1) Depression words, (2) Depression control words (3) Anxiety words, (4) Anxiety control words, (5) ADHD words, (6) ADHD controls, (7) ODD/CD words, and (8) ODD/CD controls. Each control set is made up of animal words. Every word is assigned a numerical value that is recorded under the "Trial No." variable (see attached file titled Stroop Words).

Desired output format:
---------------------------
ID#...... MRTDep.......MRTDepCon......MRTAnx.....MRTAnxCon (etc. for word types 3-8)
1001....... X................Y...............
1002....... .............. ...............
---------------------------

X= mean Reaction Time for Depression Words (MRTDep)for participant 1001 (i.e. sum of Reaction Time values for spreadsheet rows 20-60 with corresponding the 'Trial No.' variable values of 12, 13, 74, 53, 54, and 31, divided by the total number of reaction times for this word type (should usually be 6 but could be less if trials for this wordtype get discarded due to impulsive, distracted, or erroneous responding - as defined above and presumably filtered out earlier by a macro).

Y = Mean Reaction Time for Depression Control Words (MRTDepCon) for participant 1001 (i.e., corresponding to Trial No.s 58, 38, 39, 40, 80, 81).

and so on for the remaining 6 word type sets:

MRTAnx (Trial Nos. 75, 78, 14, 32, 55, 72)

MRTAnxCon (Trial Nos. 59, 18, 19, 60, 41, 82)

MRTADHD (Trial Nos. 33, 56, 34, 57, 78, 15)

MRTADHDCon (Trial Nos. 42, 43, 61, 20, 21, 62)

MRTODD (Trial Nos. 16, 35, 17, 36, 37, 79)

MRTODDCon (Trial Nos. 63, 64, 65, 22, 44, 83)

Hope this makes things clearer, and thanks for any help you can provide!
BradW

[bradw09]

This sample participant data file apparently didn't attach in my last post; sorry.

[StephenR]

Brad - that looks helpful but I think you forgot to attach the participant file.

[StephenR]

Brad,
I've made a stab, which churns out some figures although I've no idea if they're correct!

It loops through every file in a specified folder (change the LookIn line to your folder) and then does the cleaning before producing the averages. Place the code in the output workbook.

I'm sure there'll be plenty of gremlins/queries so come back as necessary.

Please Login or Register  to view this content.

[bradw09]

Stephen,

Thanks so much for your generous assistance! I was able to paste in the formula you provided and it worked brilliantly! I'm wondering if you can help with a couple small modifications though. Would it be possible to:

1) show the full participant ID# for participants with different variants of the same number (e.g., 1001E, 1001E2, 1001E3)?

2) instead of counting as 'insufficient records' participants with 39 or fewer valid trials (as I'd initially requested), arrange it so that less than 5 valid trials for each of the 8 word types would result in 'insufficient records' for that particular word type?

3) add a variable after each of the 8 word type mean scores indicating how many of the 6 trials were valid (after deleting those that were <100msec, >3000msec, or E error code)?

Again, I greatly appreciate all of your help!

Warm regards,
BradW

[StephenR]

Will look at this later.

Incidentally, not sure it matters but I guess this should be in the programming forum.

EDIT: see revised code below, and attachment to see how I envisaged your request. I wasn't able to address 1 as I can't see where the full ID is located. Your example merely has 1001E.

Please Login or Register  to view this content.

[bradw09]

Stephen,

Sorry for the delayed reply, and thank you so much for all the help! I was able to use the modified code you suggested with just a slight adjustment to the Excel output file. I just added columns to show the number of trials passing all exclusion criteria (Dep#, DepConT#, etc), see attached ("StephenR Sample Excel Output 2").

If I may make one last request, can you please show me how to add one column for each word type indicating how many trials were coded as correct (Error Code C rather than E)? I have attached an output file indicating the desired format ("Output Sample 3" with added variables Dep#C, DepConT#C, etc.).

Also, presuming my colleagues and I are able to publish findings from our data in a professional psychology journal as we hope, we want to formally acknowledge the technical assistance. If that sounds okay, please let me know how to reference you or how to reach you to get that info (usually first & last name and sometimes place of employment are included in the acknowledgements).

Regards,
BradW

[StephenR]

Brad,

Here is some slightly revised code. All the entries in your sample are error code "C" so it's a repeat of the previous column, but perhaps that is just a feature of your sample.

Please Login or Register  to view this content.

That's a very gracious offer, thank you.

[bradw09]

Hi Stephen,

Thank you so much for helping further with the code. I see though from the new code that what I'd asked for wasn't quite what I needed; sorry! I hope you can help me revise it one last time.

What I realized I need is for the number of Error trials (E, not C) to be recorded in the new columns (DepT#C, DepConT#C, etc.) for each word type (regardless of how long or short the RT was), before these E trials and those with excessively long and short RTs (<100 and >3000) are deleted for the calculations of RT means.

The sample data file I'd previously sent had no trials with Es, so I've attached an additional data file that includes some with both Es and Cs and range of RTs.

Thanks!
Brad

[QUOTE=StephenR][QUOTE=bradw09]
http://www.excelforum.com/excel-work...ml#post2022146

I recently asked about a modification I hope to make with the program: adding columns for each of the 8 word types to indicate # of correct "C" trials (vs. excluded error "E" trials. I posted a sample output file with the desired format).

[StephenR]

Hi Brad,

Just to be on the safe side, it would be helpful if you could post a revised copy of your desired output file (so as to make sure I'm working on the right one as it's a while since I last looked), and add to it your expected results based on your last sample input file (22222).

I recently asked about a modification I hope to make with the program: adding columns for each of the 8 word types to indicate # of correct "C" trials (vs. excluded error "E" trials. I posted a sample output file with the desired format)

Not quite sure what this means - is it covered above?

Steve

[bradw09]

Hi Steve,

Sorry, I think that bit of text got carried over from one of my earlier messages; it doesn't go with my revised request.

I have reattached the desired output file format.

Thanks for all your help,
Brad

[StephenR]

Brad - please could you also add to it your expected results based on your last sample input file (22222)?
Steve

[bradw09]

Hi Steve,

I've attached a modified version of the output format file, Sample Excel Output (3)b, with values inserted for the sample file showing expected counts for the total # valid trials (those passing the 'E' and short/long RT exclusion criteria) for just several word types (DepT#, DepConT#, AnxT#, AnxConT#), as well as expected counts of "E" trials for these word types (DepT#C, DepConT#C, AnxT#C, AnxConT#C). Hopefully I did these correctly by hand to reflect the values that should show in those columns if the program runs the way I'd like it to. (I didn't bother calculating the RT values as I'm running late to an appt. :-)

I should mention also, the data file for 22222 is just a hand-made sample and I noticed I made a typo at one point, inserting a lower case 'c' rather than uppercase 'C'. Hopefully that didn't create a glitch. I've attached a corrected version here.

Thanks!
Brad

[StephenR]

Brad,
Try this. I've reworked it somewhat. My figures don't completely agree with yours so please doublecheck I've understood correctly.

Please Login or Register  to view this content.