topic Re: Combining two Data files in Discussions

Combining two Data files

tlsimons — Thu, 27 Sep 2018 19:34:26 GMT

Hello Everyone,

I have two separate data files (one for eye movements and one for performance on a task). I want to combine these two data files into one file so that I can analyze them with one another, but am running into troubles on how to get started.

The two data files have nothing but the participant file name in common and have varying amounts of responses per participants (ie. The eye movement data has more responses per participants compared to the task performance file).

I have uploaded the files to this post.

Guidance would be very helpful!!

Re: Combining two Data files

Mark_Bailey — Thu, 27 Sep 2018 19:37:42 GMT

See Help > Books > Using JMP and the topic is the Join command in the Tables menu. You want to be sure not to drop multiple matches. JMP will expand the rows to make sure that all the data for each ID is captured in the join.

Re: Combining two Data files

tlsimons — Thu, 27 Sep 2018 19:42:11 GMT

I tried using the basic "Join" methods in JMP and it multiplies my data and spits out a huge file that is not manageable.

Re: Combining two Data files

Mark_Bailey — Thu, 27 Sep 2018 20:21:02 GMT

Sorry about that. I looked at your variables and I am not sure how one would combine them. I only see a file name in one of the tables. I will leave it to a database guru in the community to come to your rescue!

Re: Combining two Data files

dale_lehman — Thu, 27 Sep 2018 20:41:41 GMT

Are you sure you want to join the tables rather than concatenating them? I'm not clear on what joining them will accomplish - you have multiple rows in each data set - in what way is it meaningful to line them up side by side? If you can explain that, I can probably tell you how to join them. But you might want to concatenate them instead - all the data will be in one file, but only the common column names will be "joined" (vertically).

Re: Combining two Data files

txnelson — Thu, 27 Sep 2018 21:10:52 GMT

I can see a method for joining the data tables, but that you would have to summarize the data down to the Participant. This might be what you want, if the summarization results in meaningful data.

Re: Combining two Data files

tlsimons — Fri, 28 Sep 2018 13:28:51 GMT

I successfully used the concatenating method and have a single file now, however, I have a lot of empty values within my data now in which it will not allow me to run analyses because I am missing values for my main outcome variables.

Re: Combining two Data files

tlsimons — Fri, 28 Sep 2018 13:31:38 GMT

Unfortunately have a summarized version of these results is not going to be meaningful as I want to look at how the eye movements are changing with performance on the task. If i summarize the results I am losing a lot of really valuable data. This method may be something that I have to consider in the long run if what I want to do is too complex though. Could this be done leaving 8 rows per column for each participant? They watched 8 separate videos total and each one should be treated separately for the study design.

Re: Combining two Data files

tlsimons — Fri, 28 Sep 2018 13:32:31 GMT

The subject name is present within each of the files (SESSION_LABEL, or something similar to that).

Re: Combining two Data files

dale_lehman — Fri, 28 Sep 2018 13:40:14 GMT

If the problem you now have is that the outcome variables are in different columns, each of which has much missing data, then you might try this: create new columns that use existing columns when data is missing, otherwise the data that is not missing. In other words, you have an IF statement that says IF(ISmissing(data) then Other Data, else Nonmissing Data. You may need a multilevel IF statement to bring in all the outcome variables into a single column for analysis.

Re: Combining two Data files

tlsimons — Fri, 28 Sep 2018 13:42:32 GMT

So the outcome variable is not present within each data file (deviation from screen center) because it is a measure of eye movement variability, hence making not present in the other data file. I need a way to fill the empty cells with NA or something that will allow for the analyses to be run.

Re: Combining two Data files

dale_lehman — Fri, 28 Sep 2018 13:52:11 GMT

I've attached a simple example showing 2 ideas that might work. Once creates a new column to combine responses and the other is the Tabulate script in the table. See if either of these methods will give you what you want.

Re: Combining two Data files

tlsimons — Fri, 28 Sep 2018 14:09:56 GMT

Creating a new column is not going to work in this case, because I do not have values to fill into the missing data. I do not know where to find the Tabulate Script method that you are referring to.

Re: Combining two Data files

dale_lehman — Fri, 28 Sep 2018 14:12:43 GMT

It is the green arrow at the top left of the data table. Clicking it will run the script, click on the name and edit it to see the script.

Re: Combining two Data files

tlsimons — Fri, 28 Sep 2018 15:53:29 GMT

This method still is taking multiple columns and combining them to make a single column. This may have been poor communication on my part. I don't have the same outcome variables in both files, so that is adding a level of difficulty to my merging of data files.

Re: Combining two Data files

dale_lehman — Fri, 28 Sep 2018 17:43:04 GMT

Can you provide an example (using the variables in your data sets) of the type of question you wish to analyze? If all of the eye movement measures are potential explanatory variables for any of the performance measurements, then I do believe you want to join the data sets without dropping any multiples. Yes, it will explode in size, but that is because you want all of the eye measurements to be available to explain each of the performance measures. Any particular analysis you want to conduct will probably be easier by Tabulating from that huge data set to get just the inputs and outputs you want to study. If there is more structure to your analysis than using all potential eye movements to explain all potential performance measures, then that structure needs to be explained and there may well be an easier way to contstruct the data file.

Re: Combining two Data files

Chris_Kirchberg — Fri, 28 Sep 2018 18:17:57 GMT

The key to joining these two tables is what we call unique keys (no pun intended). There has to be a column or combination of columns that can be used as keys to uniquely identify each row in both tables so that each row can be joined to each other where it makes sense.

For example: If you have chris listed twice or more in a single column, you will need another column such as age and gender to identify each chris as separate people. Then the other table can have chris and the same columns so that teh columns can be joined at the appropriate rows for each unique individual.

To complicated this, I looked at both the data sets provided and there does not seem to be any sets of columns that are obivious to me that would help identify a unqiue row in the tables other than the actual recorded values but they are not the same columns in each table.

To make things worse, if you joined one table into the other, you would get repeating values per session label/ID for many of the columns since they are not the same number of rows.

Take a look at the online help to see different types of examples and this can give you an idea of what you would need in order to join your two tables. Specifically look at the section titled "To join tables with different numbers of rows and different column names" towards the bottom.

https://www.jmp.com/support/help/14/examples-of-joining-data-tables.shtml

JMP Query Builder is a possiblity for another tool to use, but the same problem exists. How to match up the rows from each table so when the columns get joined into a new table, they are appropriately associated with each other.

Ideally, if you had SESSION LABEL and frame index for each row for both tables, then you could match them up with those two columns. Even if there is missing data for each frame index in one table for some of the rows, that would be OK. Or if one table contains a subset of the frame indicies of the other, then that would be OK as well.

I hope that gives some direction.

Re: Combining two Data files

tlsimons — Tue, 02 Oct 2018 16:03:42 GMT

About the only thing that is the same for both datasets is going to be SESSION_LABEL (i know they may not be labeled the same, but they are the same thing), and condition. The other variables are data file specifc

Re: Combining two Data files

tlsimons — Tue, 02 Oct 2018 16:11:09 GMT

Generally I was looking at eye movements (saccade amplitude, fixation duration, deviation from screen center) within different conditions and cognitive load levels. Then with the combined data set I want to use all of these variables just mentioned plus their performance (average) to see if that is affected or affects eye movements.

Re: Combining two Data files

tlsimons — Tue, 02 Oct 2018 16:13:31 GMT

Could this summation method occur in a way that would leave 8 rows per participant? There were 8 different stimuli that were used and I would need to keep them separate.