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

3 views (last 30 days)
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!

Answers (1)

BhaTTa
BhaTTa on 4 Sep 2024
@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);

Categories

Find more on Creating and Concatenating Matrices in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!