Linear indexing and hash tables in MATLAB

Let’s consider the following database table:

Date	Id	Amount
29.10.2011	1001	10.2
30.10.2011	1001	15.6
29.10.2011	1002	17.5
30.10.2011	1002	12.2
31.10.2011	1002	7.9
1.11.2011	1001	19.3
1.11.2011	1002	17.9

Suppose that from this table we want to generate the following MATLAB matrix storing amount information.

10.2	15.6	NaN	19.3
17.5	12.2	7.9	17.9

To do that we need to map each date and ID with an index. We use containers.Map objects. See Matlab Map Containers page to learn more.

1001	1
1002	2

29.10.2011	1
30.10.2011	2
31.10.2011	3
1.11.2011	4

Next step is to fill matrix Amount. Consider a m x n matrix A. Suppose we want to use matrix B = (1,1;1 2) for selecting elements of A. Unfortunetely A(B(:,1), B(:,2)) will not work. We should use linear indexing. See Matrix indexing to learn more.

MATLAB code:

T1 = ['29.10.2011'; '30.10.2011'; '29.10.2011'; '30.10.2011'; '31.10.2011'; '1.11.2011'; '1.11.2011'];
T2 = [1001; 1001; 1002; 1002; 1002; 1001; 1002];
T3 = [10.2; 15.6; 17.5; 12.2; 7.9; 19.3; 17;9 ];

keySet = {'29.10.2011', '30.10.2011', '31.10.2011', '01.11.2011'};
valueSet = 1:4;

date_index = containers.Map(keySet,valueSet);

keySet = {1001, 1002};
valueSet = 1:2;

id_index = containers.Map(keySet,valueSet);

Amount = ones(2,4)*NaN;

Amount(sub2ind(size(Amount), values(date_index,T1), values(id_index,T2) )  ) = T3

You can also index dates by using Matlab’s datenum function. This function returns how many days is passed from a reference point (1-Jan-0000). Suppose D matrix contains a list of dates then the following code will also work:

D = {'29.10.2011', '30.10.2011', '29.10.2011', '30.10.2011', '31.10.2011', '01.11.2011'}
datenum(D) - min(datenum(D))

Comments are closed.