Conditional Data Merging in Mathematica

Question

I need to merge data from 2 different sources. The tables below illustrate what I have :

And the desired Output :

The idea is:

• Look into the first col. of Table1 (TrialNO)

• Look for it in the first col. of Table2.

• Check that the values in the second col. are equal (in reality those col. won't be located at the same locations in the 2 lists)

• If Check is pass append the values located in col. 3 & 4 (Cond1 & Cond2) to the line in Table2.

• I don't think I will keep the headers in the real structure, so it should not represent an additional problem, but suggestions to deal with headers are welcome (whether to remove them and store them somewhere else or treat them in a special way)

**

EDIT : Giving precision on the shape of my data & my goals

**

I will give a little background on how I get those data to clarify its shape. I am sure it could be described technically in more accurate way. Please don`t hesitate to correct me.

I am recording eye-movements (saccades & fixations) and subjects answer to a task while displaying stimuli on a screen on.

• Each Trial consist of two consecutive displays of 3 seconds each. It is a 2AFC (Two-alternative forced choice).
• Each display consist of presenting a frame (about 1/4 size of the screen) with 8 shapes in it, displayed on 1 out of 4 quadrants of the screen.
• There is 5 conditions of what the frame itself is made of and thus 10 conditions possible for each trial (1 condition of frame against another without repetition).
• There are 2 measures : The choice of the subject & the eye-movements recorded while looking at the stimuli.

I get those data from 2 different sources :

---

• The "display" machine which provide

-Trial number/Display number

-Informations about the screen

-Conditions

-Subject Answer

-X & Y coordinate as well as size of the 11 object composing the stimuli displayed.

In this Matrix, each row is a Display so the DisplayNO Column Would go from 1 to 400 (1,2,3,4,...,400) while the TrialNO Columns actually goes from 1 to 200 (1,1,2,2,..,200,200) since there is 2 displays per trial.

---

• The "Eye-Tracking" machine which provides :

-Some similar info (Display number (1to 400), that will be used to merge the 2, condition number, that can be uses to check the mapping bet. the 2)

Then a massive amount of variables describing the eye-movements :

-Fixations and saccades durations, locations, timing etc. (about 100 columns)

In this Matrix, each row is a fixation. The saccade characteristics are then given in columns (previous and next saccade) And there can be from 1 to 30-50 fixations for each display. As a result, I could have 19 rows of data for the first display and 5 for the second.

---

• The first step is to merge the 2 the 2 data structure to obtain a big one with each rows corresponding to a fixation.

• Will have to do this for every subject then aggregate the subjects data on top of each other.

This is my plan to deal with this monster afterward (And this will explain my needs in the other questions) :

• Extract the Header & columns number.

• Present them by group in nice table form -General Info (Trial ID, Condition, Subject ID...), -Display Info (coordinates of objects on screen), -Fixations info etc...

• Have for each of this variabe a summary of the data type (String, Number, text), the range, how many different values the columns takes and some basic descriptive statistics.

• A system to extract parts of this set conditionally (For Example : Extract the condition number, fixations durations, for chosen display by a particular subject) That way I extract some well defined table I then run my analysis on without touching the original data.

If I used my precise situation to present my problem I believe, this could yield to a nice efficient and graphically easy to use tool to deal with a lot of data set in general.

Answer 1

Here is a possibility:

MergeTables[data1_, data2_, samepos1_, samepos2_] :=
 Cases[data1, 
  x_ :> Block[{y = 
      Cases[data2, z_ /; z[[samepos2]] === x[[samepos1]]]},
    Apply[Sequence, Join[x, Delete[#, Thread[{samepos2}]]] & /@ y]]]

Usage:

MergeTables[data2, data1, {1, 2}, {1, 2}]