How to input excluded data

So I have recorded behaviours 'Tier_Labels' (e.g. walking, running, sitting) for individual birds (identified by Bird, Pen & Strain). I have aggregated the data to develop a cumulative sum each specific Tier_Label was performed at different habituation times.

%% Aggregation SPSS Function in Matlab
% Creates a table of SUM Total_Duration2 for each Tier Label
G4 = groupsummary(T,{'Bird','Pen','Strain','Habituation','Tier_Labels',},'sum', 'Total_Duration2')

However, some of these behaviours, don't occur for every bird, at every habituation time. I was to create code (potentially a loop) which will say:

For every individual (Bird,Pen,Strain,Habituation),

If 'Behaviours' are missing from the recorded 'Tier_Labels'

Add in a row with that behaviour listed as Total_Duration 0.00

I think a loop is the right way forward but I'm a novice at coding and I'm not sure if there is function that can look at a variable and identify missing labels from a pre-written varibale and then input these into the data set.

Hopefully that makes sense, can anyone help? Or give me some pointers in the right direction?

Thanks :)

[darova]

Can you show how data looks like?

[Justine Pearce]

Yes sorry so my data looks like this:

Bird Pen Strain Habituation Behaviour Count Duration

'OP' 12 'R' '30mins' 'Headshake' 2 0.690000000000000

'OP' 12 'R' '30mins' 'Preen_Standing' 1 3.31000000000000

'OP' 12 'R' '30mins' 'Shuffle' 1 0.840000000000000

'OP' 12 'R' 'Immediate' 'Preen_Standing' 1 1.83000000000000

'OP' 12 'R' 'Immediate' 'Stand_Inactive' 2 7.05500000000000

'OP' 52 'R' '30mins' 'Feather_Ruffle' 1 1.00000000000000

'OP' 52 'R' '30mins' 'Headshake' 1 0.440000000000000

'OP' 52 'R' '30mins' 'Reverse_Behaviour' 2 2.88000000000000

'OP' 52 'R' 'Immediate' 'Hop/Jump' 1 0.561000000000000

'OP' 52 'R' 'Immediate' 'Peck_Accel' 1 0.144000000000000

'OP' 52 'R' 'Immediate' 'Peck_mark' 2 0.256000000000000

I want to be able to look at each individual bird which is identified by Bird, Pen and Strain columns, at individual habituation time and then I want matlab to check the behaviour for that bird, at that time point against a full list of behaviours and any that are missing, I want matlab to add a row for that bird, pen strain & habituation and put 0 in count and 0 in duration.

Hopefully that makes sense - sorry I know it is very specific example. I've tried using a string comparison function but the list of behaviours is longer and I'm not sure how to add a row for a missing behaviour automatically.

[darova]

Can you make a simple example of result you want to see? input data -> data after processing

[Justine Pearce]

Yes of course so:

Input Data:

OP, 12, R, Immediate, Walking, 2.00000

OP, 12, R, Immediate, Running, 1.1000

Output Data:

OP, 12, R, Immediate, Walking, 2.00000

OP, 12, R, Immediate, Running, 1.1000

OP, 12, R, Immediate, Sitting, 0.0000

OP, 12, R, Immediate, Lying, 0.0000

So the inputted data is missing sitting and lying behaviours so I would want the output to add it in and put 0 in the duration column. Thanks for trying to help me!

Answer 1

[darova]

Try this

clc,clear
A = importdata('data.txt','\t');
T = A.textdata;
blist = unique(T(:,end));               % create full list of unique behaviours
k = 1;
for i = 1:size(T,1)
    if ~all( strcmp(T(i,1:4),T(i+1,1:4)) )      % if rows are not similar
        clist = setdiff(blist,T(k:i,5));        % list of lack behaviours
        T1 = repmat(T(i,1:4),[length(clist) 1]);% duplicate rows Bird Pen Strain Habituation
        T = [T; T1 clist];                      % add lack of behaviours
        k = i+1;
    end
end
data1 = A.data;
data = zeros(size(T,1),2);          % zero numerical list (count duration)
data(1:size(data1,1),:) = data1;    % fill first values (rest are zeros)
R = [cell2table(T) table(data)];    % create full table
writetable(R,'data1.txt',...
    'delimiter','\t',...
    'writevariablenames',false)

[Justine Pearce]

This almost worked but was a huge help to understanding what I need to work on so thank you so much for helping me! In the end I transferred to R as I had better 1 to 1 support with that language and I ran it without a loop. I'm going to work on this loop some more though so thank you!

[darova]

my pleasure