How to Group data points and add serial numbers within the group ?

6 views (last 30 days)
Namrata Goswami
Namrata Goswami on 6 Nov 2020
Commented: Ameer Hamza on 6 Nov 2020
I have a table with ID and Date columns. I want to group the data by ID and add serial numbers in a new column call Serial No. as seen in the example below. How to do this in Matlab ?
Original table:
ID Date
12 jan
12 feb
12 mar
14 jan
14 feb
14 mar
14 apr
11 jan
11 feb
Expected Output:
ID Date Serial No.
12 jan 1
12 feb 2
12 mar 3
14 jan 1
14 feb 2
14 mar 3
14 apr 4
11 jan 1
11 feb 2
Thanks!

Answers (2)

KSSV
KSSV on 6 Nov 2020
A = [12 1
12 2
12 3
14 1
14 2
14 3
14 4
11 1
11 2] ;
[c,ia,ib] = unique(A(:,1)) ;
n = length(c) ;
iwant = zeros(n,2) ;
for i = 1:n
iwant(i,1) = c(i) ;
iwant(i,2) = sum(A(ib==i,2)) ;
end
iwant

Ameer Hamza
Ameer Hamza on 6 Nov 2020
See this example
ID = [1;1;2;2;2];
Date = {'jan', 'feb', 'jan', 'feb', 'mar'}.';
T = table(ID, Date);
T.Serial_no = month(datetime(T.Date, 'InputFormat', 'MMM'));
Result
>> T
T =
5×3 table
ID Date Serial_no
__ _______ _________
1 {'jan'} 1
1 {'feb'} 2
2 {'jan'} 1
2 {'feb'} 2
2 {'mar'} 3
  2 Comments
Ameer Hamza
Ameer Hamza on 6 Nov 2020
In that case, try this
ID = [1;1;2;2;2];
Date = {'jan', 'feb', 'jan', 'feb', 'mar'}.';
T = table(ID, Date);
T.Serial_no = cell2mat(splitapply(@(x) {(1:numel(x)).'}, T.ID, T.ID));

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by