How do I concatenate cell arrays and line up the rows according to matching items in a column?

Hi,

I have a few arrays (results from different tests) that all have the same first column (list of the participant IDs that performed the tests) which I want to combine into one large array. The thing is I dont know how to add them together and make sure that the rows match up according to the participant IDs.

If I use

high_questionnaires = horzcat(high_loc_ID_factors, high_empathy_ID_factors, high_itc_sopi_ID_factors)

then I get a cell array where the rows arent lined up correctly (see "high_concatenated").

Help is much appreciated!

Answer 1

[BhaTTa]

@lil brain,to combine multiple arrays based on a common column (participant IDs in your case), you need to ensure that all arrays are aligned by their participant IDs before concatenation. Follow the below steps:

1. Extract Participant IDs:

• Extract the participant IDs from each array. Ensure these are in a consistent format (e.g., all strings or all numbers).

2. Find Common IDs:

• Determine the intersection of participant IDs across all arrays to ensure that only matching IDs are considered.

3. Align Arrays:

• For each array, align the rows based on the common participant IDs.

4. Concatenate Aligned Arrays:

• Once aligned, concatenate the arrays horizontally using horzcat.

Example Code

Here’s a sample code to illustrate this process:

% Sample data: Replace these with your actual arrays
high_loc_ID_factors = {'ID1', 10; 'ID2', 20; 'ID3', 30};
high_empathy_ID_factors = {'ID2', 5; 'ID1', 15; 'ID3', 25};
high_itc_sopi_ID_factors = {'ID3', 8; 'ID1', 18; 'ID2', 28};

% Extract participant IDs
ids1 = high_loc_ID_factors(:, 1);
ids2 = high_empathy_ID_factors(:, 1);
ids3 = high_itc_sopi_ID_factors(:, 1);

% Find common participant IDs
common_ids = intersect(intersect(ids1, ids2), ids3);

% Initialize aligned arrays
aligned_loc = cell(length(common_ids), size(high_loc_ID_factors, 2));
aligned_empathy = cell(length(common_ids), size(high_empathy_ID_factors, 2));
aligned_itc_sopi = cell(length(common_ids), size(high_itc_sopi_ID_factors, 2));

% Align each array based on common IDs
for i = 1:length(common_ids)
    id = common_ids{i};
    aligned_loc(i, :) = high_loc_ID_factors(strcmp(ids1, id), :);
    aligned_empathy(i, :) = high_empathy_ID_factors(strcmp(ids2, id), :);
    aligned_itc_sopi(i, :) = high_itc_sopi_ID_factors(strcmp(ids3, id), :);
end

% Concatenate aligned arrays
high_concatenated = horzcat(aligned_loc, aligned_empathy(:, 2:end), aligned_itc_sopi(:, 2:end));

% Display the result
disp(high_concatenated);