Combining two Data filesHello 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 filesthen try this one - I just included the non matches.
Re: Combining two Data filesI need the full 400 rows to make comparisons when the performance task is not present. I will fill in the empty values in the average/video column with NA or something of the like so that I can do my analyses.Re: Combining two Data filesI am still not understanding what you want to do. If only 4 videos have eye movement data and performance data, then those are the only videos for which you can look at how eye movements are impacted by performance. The other 4 videos will not permit you to analyze that - so won't there only be 200 rows you can analyze?
Re: Combining two Data filesThe same 8 videos are used through the experiment. The 4 videos with performance data will also have eye movement data. I want to look at how eye movements are impacted by the performance measure.Re: Combining two Data filesSo, what is the point of combining data from the performance file that does not have the same videos? It seems like only 200 rows will be sutiable for analysis.
Re: Combining two Data filesThe issue is that the performance file will not have as many observations, there is not data for all 8 videos like there is in the fixrep file.Re: Combining two Data filesThen click the "include nonmatches" box - you will get the 400 rows. Apparently 200 did not match, but you can look at them to see why there was no match from the other data set. If that is an error, you may need to try tabulating again to make sure all the videos and subjects appear in both tabulated files.Re: Combining two Data filesI am not sure what is happening with some of the columns here, the naming is quite confusing. I should still have 400 rows though and that number is down to 200. This is one the things I tried because I got something very similar.Re: Combining two Data filesSee if this is what you want. I joined by matching 2 columns: session id and video.
Re: Combining two Data filesSo i have made my two smaller tabulated files. I am having trouble joining them now into one file though.
I basically just need to get the average /video into the fixrep file and I will be good, but I have tried it multiple ways and am not getting the desired output.
Re: Combining two Data filesIn your larger file, create a new column using a formula such as ColMean(whatever measure you want to average by Session Label, by video). In other words, get the average (or sum or standard deviation or whatever measure you want across the videos) by including two By conditions in that Col function - the first identifies the participant and the second identifies the video. Then tabulate the data by participant using the newly created column as the data you want. You can take whatever other columns you want - and you can do something like this in the other data set if you want to join some of the data from there. From Tabulate, make it into a data table (and do the same if you want data from the other table to join to it).
Re: Combining two Data filesI talked to my collaborators and they have agreed that averaging the data together by participant would be fine to do some initial analyses.
Is there a way that I can have a resulting 8 rows for each participant? This would be one row for each of my film clips that were shown.Re: Combining two Data filesI would suggest subsetting a single participant from both files in order to determine exactly how you want to join the two files - if you can get what you want for a single participant, then you can easily do it for all of them. It sounds like you may be able to join on one of those other common columns. When joining, you can always click on Select columns and only select the ones you want - and it sounds like it may be easier to generate the data for a particular question you wish to analyze rather than trying to create the data set for any and all questions you want to analyze (you can always join them a bit differently for a different analysis). But the idea is can you put the data together for one participant? You may have to summarize the data a bit before trying to join them since you may not need all of those rows for the analysis you wish to conduct, so you may want to Tabulate the data from each dataset first, and then try to join the tabulated files.
Re: Combining two Data filesa bare minimum of saccade length, fixation duration, deviation from screen center, condition type, cognitive load, video, participant, average nback performance, and time would be needed. None of these variables besides condition and participant are within the both data sets.
There are such stark differences in the data for each participant between the two files because of the data being collected. In the Eye movement file (2,286 rows) I am recording every single eye movement that they have. Int the nback file (337 rows) I am only getting a response every ~2 seconds because that is how often a letter is presented to them in an auditory manor. I will also have data on 4 of the 8 videos presented to each subject within the nback file, because participants watched half the videos with and half without the nback task to compare performance across the two conditions.
I am looking at eye movement changes based on the manipulations of condition and cognitive load. (fixed effects in my model)
I use participant and video as random effects, so they are still needed to run the analyses.
If I can combine the two files successfully I want to also look at these same variables with the addition of their performance on the nback task (the cognitive load).
Does this help at all?Re: Combining two Data filesI'm still having trouble understanding what you want to do. So, I pulled out a single subject's data from both files (Session label ecem_10c). One file has 337 rows and the other has 2,286 rows. If you join these without dropping multiples you wil get 337x2286 rows - and for the full dataset that is probably too much informaton. But without any other field to join on, what columns would you want to view together in order to do your analysis. If you can tell what those columns are, I think I might be able to help. But I don't understand your data and experimental conditions enough to know which of these columns (30 in one dataset and 27 in the other) make sense to look at together and which do not.
Re: Combining two Data filesCould 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.Re: Combining two Data filesGenerally 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 filesAbout 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 specifcRe: Combining two Data filesThe 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 filesCan 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 filesThis 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 filesIt 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 filesCreating 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 filesI'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 filesSo 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 filesIf 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 filesThe subject name is present within each of the files (SESSION_LABEL, or something similar to that).Re: Combining two Data filesUnfortunately 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 filesI 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 filesI 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 filesAre 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 filesSorry 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 filesI 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 filesSee 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.
