Need help with table conversion

I have multiple CSV files, consisting of 1 target csv and 50ish source csv.

This is one example of the source csv

id   l_id   date      duration   team   teamB   Win   A1   A2   A3   A4   A5   B1   B2   B3   B4   B5
123  3692   11/2/94   2451       TeamB  3       1     1    46   55   72   85   12   5    50   60   114
124  4255   15/2/94   2355       TeamA  2       0     5    31   24   6    26   99   68   3   111  52
...and so on

This is the target csv I would like to convert the source into

T1  T2  T3  T4  T5  T6  T7  T8  H1  H2  H3  H4  H5  H6  H7  H8  ... H114
0   0   -1   0   0   0   0   0   1   0   0   0   -1  0   0   0   ...  -1
0  -1    0   0   0   0   0   0   0   0  -1   0    1  1   0   0   ...   0
...and so on

How the conversion work, it reads data from source csv. Only column "teamB" and "A1 to B5" will be used. The source column "T1~T8" reads data from column "teamB", while "H1~H114" reads from "A1~B5". On source csv - row 1, "teamB" has a value of 3, so target csv's "T3" will have value of '-1'. On row 2, "teamB" has value of 2, so "T2" have value of -1.

For "A1~B5", numbers will be converted to corresponding "H###", it will have a value of '1' if it's present in "A#" column, value of '-1' if present in the "B#" column, and '0' if it's not present at all on that particular row. Here's a practical example for row 1:

* A1 has a value of 1, so H1 will have a value of 1
  A2 has a value of 46, so H46 will have a value of 1
  A3 has a value of 55, so H55 will have a value of 1
  A4 has a value of 72, so H72 will have a value of 1
  A5 has a value of 85, so H85 will have a value of 1
* B1 has a value of 12, so H12 will have a value of -1
  B2 has a value of 5, so  H5 will have a value of -1
  B3 has a value of 50, so H50 will have a value of -1
  B4 has a value of 60, so B60 will have a value of -1
  B5 has a value of 52, so B52 will have a value of -1
* Numbers not present on A1~B5 will have a value of 0 on the corresponding H1~H114

I'm using Matlab R2015, how can I achieve this? Sorry for the poor choices of wording/sentece, I'm not a fluent English speaker. Also, thanks in advance!

Answer 1

[Guillaume]

Code written on the fly, completely untested. There may be bugs / typos:

filelist = {....}  %built however you want, maybe with dir
outtables = cell(size(filelist));
Avars = sprintfc('A%d', 1:5);  %sprintfc is an undocumented function. In newer matlab use compose which is documented
Bvars = sprintfc('B%d', 1:5);
Tvars = sprintfc('T%d', 1:8);
Hvars = sprintfc('H%d', 1:114);
for fileidx = 1:numel(filelist)
   t = readtable(fullfile('c:\somewhere', filelist{fileidx}));
   teams = zeros(height(t), 8);
   members = zeros(height(t), 114);
   teams(sub2ind(size(teams), (1:height(t))', t.teamB)) = -1;
   members(sub2ind(size(members), repmat((1:height(t))', 1, 5), t{:, Avars})) = 1;
   members(sub2ind(size(members), repmat((1:height(t))', 1, 5), t{:, Bvars})) = -1;
   outtables{fileidx} = array2table([teams, members], 'VariableNames', [Tvars, Hvars]);
end
writetable(vertcat(outtables{:}), fullfile('c:\somewhere', 'outname.csv'));